0

I am using a C# console application and I want to create an Excel file and insert two lines of text into a single cell in Excel. I want to insert new line character in an Excel cell from my C# program so that I have output in single cell like:

this is line 1 text
this is line 2 text

I tried using

worksheet.Cells[0, 0].Value = "Components";
worksheet.Rows[1].Cells[0].Style.WrapText = true;

worksheet.Cells[1, 0].Value = "this is line 1 and "+Environment.NewLine+" this is line 2";

and also this

worksheet.Cells[0, 0].Value = "Components";
worksheet.Rows[1].Cells[0].Style.WrapText = true;

worksheet.Cells[1, 0].Value = "this is line 1 and \n this is line 2";

but for both it add some extra space to the top of cell. Even if I remove text wrap and only use \n or Environment.NewLine, it still adds this extra space to top of cell.

My complete code in C# is shown below - I am using the gembox.spreadsheet library

using GemBox.Spreadsheet; 

// If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

// Create empty Excel file with a sheet
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.Worksheets.Add("Issues");

worksheet.Cells[0, 0].Value = "Components";
worksheet.Rows[1].Cells[0].Style.WrapText = true;

worksheet.Cells[1, 0].Value = "this is line 1 and "+Environment.NewLine+" this is line 2";

// Save excel file
workbook.Save("E:/.net projects/CSharpToExcel/JsonToExcel.xlsx");

Console.WriteLine("data inserted successfully");
Console.ReadKey();

The output is

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dildar Hussain
  • 21
  • 2
  • 1
    Is that space in the text content (can you erase it?) or is it just the row height? – Hans Kesting Mar 19 '21 at 06:41
  • Are you sure that is your complete code? If you run just that code, do you reproduce this issue? From the screenshot, I see that your column "A" doesn't have a default width but your snippet code doesn't set it so I think you're not showing everything. Is it possible you're explicitly setting that row's height somewhere? – Mario Z Mar 22 '21 at 03:48

1 Answers1

0

The white space you see is not because of space characters inside the cell,
it is the behavior of Excel.
Add this 2 lines at the end of your code and it should do the job:

worksheet.Columns[0].AutoFit();
worksheet.Rows[1].AutoFit();
Jonathan Applebaum
  • 5,738
  • 4
  • 33
  • 52