How to change excel sheet column format in c #?

Question

I create an excel sheet dynamically and paste the values ​​into the same thing. But the value in some cells gets up in the wrong format. Next is my excel sheet the value in the selected cell should be 0002-9343 , but it is inserted as Feb-43 . This is due to the wrong format for this column (column-D) in Excel worksheet. I need to change the entire column-D (all cells under the heading D "data2") to "Text format" before entering the data. Below is the code to create excel sheet and insert data

excel = new Application(); excel.Visible = false; wb = (_Workbook)(excel.Workbooks.Add(System.Reflection.Missing.Value)); sheet = wb.Sheets.Add(); sheet.Name = "TestSheet1"; sheet.Cells[1, "A"].Value2 = "Id"; sheet.Cells[1, "B"].Value2 = "Name"; sheet.Cells[1, "C"].Value2 = "Data1"; sheet.Cells[1, "D"].Value2 = "Data2"; sheet.Cells[1, "E"].Value2 = "Data3"; for (int i = 0; i < 10; i++) { id = i; result = object; data = JsonConvert.DeserializeObject(result); name = (data != null) ? data.name : string.Empty; data1 = (data != null) ? data.data1 : string.Empty; data2 = (data != null) ? data.data2 : string.Empty; data3 = (data != null) ? data.data3 : string.Empty; sheet.Cells[i + 2, "A"].Value2 = name; sheet.Cells[i + 2, "B"].Value2 = data1; sheet.Cells[i + 2, "C"].Value2 = data2; sheet.Cells[i + 2, "D"].Value2 = data3; } string ExcelPath = Some_path; wb.SaveAsExcelPath,XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null, null); wb.Close(true); excel.Quit(); 

Now, before the loop, I need to change the format of the cells in column-D to text format. How to do the same from code in C #?

Answer 1

Now, before the loop, I need to change the format of the cells in column-D to text format. How to do the same from code in C #?

To change the format of the entire column, use this

 // 4 is for Col D sheet.Cells[1, 4].EntireColumn.NumberFormat = "@"; 

Remember to format Col D before trying to write to it. Using .EntireColumn ensures that you do not have to change the format of Excel cells separately :)

Answer 2

This is not the answer to your question, but I would recommend using Open XML Api instead of using excel via Interop. There you can define the data type for any cell that you add to your spreadsheet.

 Cell c = new Cell(); c.DataType = CellValues.InlineString; 

Answer 3

Have you tried using NumberFormat?

 sheet.Cells[i + 2, "D"].NumberFormat = "@"; sheet.Cells[i + 2, "A"].Value2 = name; sheet.Cells[i + 2, "B"].Value2 = data1; sheet.Cells[i + 2, "C"].Value2 = data2; sheet.Cells[i + 2, "D"].Value2 = data3; 

Would recommend looking for simpler libraries in the future. The interactive approach is fully valid, but sometimes it’s more difficult to work the way you want.