How to delete the message "Number Stored in Text" when exporting to Excel using EPPLUS?

Question

I exported gridview data for Excel using EPPLUS, but, however, for several columns I have this green label "Number Stored in Text" after opening the exported excel file.

How can I encode my export to excel codes to change the data type / convert to a number for a specific column, starting from the second row of the specified column, since the first row is the header?

Answer 1

I updated your code here.

Check the added code ..
EDITED

I changed the variable name for your DataTable , considering what you are doing. GridView1 is the GridView UI Control , and you put the DataTable Name as GridView1 , the code can be confused on what GridView1

 protected void EXPORT_BUTTON_Click(object sender, EventArgs e) { // Added Code int parseValue; bool isInt; ExcelPackage package = new ExcelPackage(); ExcelWorksheet Grid = package.Workbook.Worksheets.Add("ORSA ASSESSMENTS"); DataTable dt = new DataTable(); for (int i = 0; i < dt.Columns.Count; i++) { // Update Type dt.Columns.Add("column" + i.ToString(), typeof(int)); } foreach (GridViewRow row in GridView1.Rows) { DataRow dr = dt.NewRow(); for (int j = 0; j < GridView1.Columns.Count; j++) { row.Cells[j].Text = row.Cells[j].Text.Replace("&nbsp;", " "); // Added Code isInt = int.TryParse(row.Cells[j].Text.Trim(), out parseValue); // Added Code if (isInt) dr["column" + j.ToString()] = parseValue; } dt.Rows.Add(dr); } Grid.Cells["A1"].LoadFromDataTable(dt, true); using (ExcelRange rng = Grid.Cells["A1:Z1"]) { rng.Style.Font.Bold = true; } Grid.Cells[ORSA.Dimension.Address].AutoFitColumns(); var FolderPath = ServerName + DirectoryLocation + DirectoryFolder + ExportsFolder; var filename = ExcelName + @"_" + ".xlsx"; var filepath = new FileInfo(Path.Combine(FolderPath, filename)); Response.Clear(); package.SaveAs(filepath); Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";"); Response.Charset = ""; Response.ContentType = "application/vnd.xlsx"; Response.TransmitFile(filepath.FullName); Response.End(); } 

This is a sample that I mocked and is a working example.

 protected void Page_Load(object sender, EventArgs e) { // Check if (!IsPostBack) { DataTable dt = new DataTable(); // Create Column for(int i = 0; i < 5; i++) dt.Columns.Add("column" + i, typeof(int)); for (int i = 0; i < 10; i++) dt.Rows.Add(i, i+1, i+2, i+3, i+4); GenerateExcel(dt); } } private void GenerateExcel(DataTable dt) { using (ExcelPackage pkg = new ExcelPackage()) { ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Sheet1"); ws.Cells[1, 1].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light18); pkg.SaveAs(new FileInfo("C:\\Test.xlsx")); } } 

Answer 2

You must save the string as int not a string . Whenever you want your cell to be a number, just enter your cell input as int .

This means that when filling out your data, be sure to specify it as an int .

Answer 3

I had similar problems. As Ordel Eraki said: don't pull together the meaning:

 workSheet.Cells[1, 1].Value = 2; // Value is object 

workSheet.Cells [1, 1] .Value = 2.ToString ()