Using EPPlus I want to Format all cells as TEXT in a spreadsheet

Question

I want to format all cells of the spreadsheet as text before loading it with the datatable.

Here is the sample code I am using

StringBuilder sbitems = new StringBuilder();
sbitems.Append(@"select * from Items");
SqlDataAdapter daitems = null;
DataSet dsitems = null;

daitems = new SqlDataAdapter(sbitems.ToString(), constate);
daitems.SelectCommand.CommandTimeout = 0;
dsitems = new DataSet("Items");
daitems.Fill(dsitems);

app.Workbook.Worksheets.Add("Items").Cells["A1"].LoadFromDataTable(dsitems.Tables[0], true);
Excel.ExcelWorksheet worksheet2 = workBook.Worksheets["Items"];
using (var rngitems = worksheet2.Cells["A1:BH1"])//Giving colour to header
{
    rngitems.Style.Font.Bold = true;
    rngitems.Style.Fill.PatternType = ExcelFillStyle.Solid;
    rngitems.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
    rngitems.Style.Font.Size = 11;
    rngitems.AutoFitColumns();
}

worksheet2.Cells["A1:BH1"].AutoFitColumns();
worksheet2.Cells["A1:BH1"].Style.Font.Bold = true;

app.SaveAs(new System.IO.FileInfo(@"D:\ItemsData\testfileexcelnew.xlsx"));

Possible duplicate of [Force EPPLUS to read as text](http://stackoverflow.com/questions/29429797/force-epplus-to-read-as-text) — par, Aug 27 '16 at 12:47

par · Answer 1 · 2017-11-29T05:13:34.393

35

Try setting Number format as @ ex: rngitems.Style.Numberformat.Format = "@";
@ formats cell as Text.

Reference : Force EPPLUS to read as text
possibly duplicate of above thread.

edited Nov 29 '17 at 05:13

answered Aug 27 '16 at 12:47

par

1,061
1
11
29

Should be rngitems.Style.Numberformat.Format = "@"; <-- you forgot the "Style" object in your solution. – BrianLegg Sep 17 '17 at 01:19
Yes, @BrianLegg i forgot `Style` before Numberformat. added it now. Thanks. – par Nov 29 '17 at 05:14

Using EPPlus I want to Format all cells as TEXT in a spreadsheet

1 Answers1