Using EPPlus, how can I create a table where numbers are numbers, not text

Question

I am creating a table from List<object[]> using LoadFromArrays

The first entry in the array is the title, other entries, possibly numbers, text, or dates (but the same for each array in the list).

The generated Excel worksheet has a green triangle warning that the numbers are formatted as text.

I scroll through all the cells and set their format to Number like this: ws.Cells[i, j].Style.Numberformat.Format = "0";

However, the problem remains, and I still see a green warning, even if the number format is set to a number when I look in the Format Cell... dialog.

What are my options here? Maybe I know a little more about what type in each column, but how do I set the column heading?

Is there a better solution than EPPlus? or any additional email spreadsheet processing that I can do before downloading it?

Answer 1

Since you use arrays of objects, they can contain numbers and strings that look like numbers that you need to go through each object and determine its type:

 [TestMethod] public void Object_Type_Write_Test() { //http://stackoverflow.com/questions/31537981/using-epplus-how-can-i-generate-a-spreadsheet-where-numbers-are-numbers-not-text var existingFile = new FileInfo(@"c:\temp\temp.xlsx"); if (existingFile.Exists) existingFile.Delete(); //Some data var list = new List<Object[]> { new object[] { "111.11", 111.11, DateTime.Now } }; using (var package = new ExcelPackage(existingFile)) { var ws = package.Workbook.Worksheets.Add("Sheet1"); ws.Cells[1, 1, 2, 2].Style.Numberformat.Format = "0"; ws.Cells[1, 3, 2, 3].Style.Numberformat.Format = "[$-F400]h:mm:ss\\ AM/PM"; //This will cause numbers in string to be stored as string in excel regardless of cell format ws.Cells["A1"].LoadFromArrays(list); //Have to go through the objects to deal with numbers as strings for (var i = 0; i < list.Count; i++) { for (var j = 0; j < list[i].Count(); j++) { if (list[i][j] is string) ws.Cells[i + 2, j + 1].Value = Double.Parse((string) list[i][j]); else if (list[i][j] is double) ws.Cells[i + 2, j + 1].Value = (double)list[i][j]; else ws.Cells[i + 2, j + 1].Value = list[i][j]; } } package.Save(); } } 

With the above, you see the image below as output. Pay attention to the upper left corner cell with the green arrow, because it was a line that was written by LoadFromArray , which looks like a number:

Answer 2

I created an extension method LoadFormulasFromArray based on EPPlus LoadFromArray . The method assumes that all objects in the list should be considered as formulas (unlike LoadFromArray ). The big picture is that both the Value and Formula properties take a string instead of a specific type. I consider this a mistake because there is no way to distinguish if the string is Text or Formula . Implementing the Formula type allows you to enable overloading and type checking, which allows you to always do the right thing.

 // usage: ws.Cells[2,2].LoadFormulasFromArrays(MyListOfObjectArrays) public static class EppPlusExtensions { public static ExcelRangeBase LoadFormulasFromArrays(this ExcelRange Cells, IEnumerable<object[]> Data) { //thanx to Abdullin for the code contribution ExcelWorksheet _worksheet = Cells.Worksheet; int _fromRow = Cells.Start.Row; int _fromCol = Cells.Start.Column; if (Data == null) throw new ArgumentNullException("data"); int column = _fromCol, row = _fromRow; foreach (var rowData in Data) { column = _fromCol; foreach (var cellData in rowData) { Cells[row, column].Formula = cellData.ToString(); column += 1; } row += 1; } return Cells[_fromRow, _fromCol, row - 1, column - 1]; } } 

Answer 3

The trick is not to pass numbers as "unprocessed objects" in EPPlus, but to correctly list them.

Here's how I did it in the export method from DataTable to Excel, which I did using EPPlus:

 if (dc.DataType == typeof(int)) ws.SetValue(row, col, !r.IsNull(dc) ? (int)r[dc] : (int?)null); else if (dc.DataType == typeof(decimal)) ws.SetValue(row, col, !r.IsNull(dc) ? (decimal)r[dc] : (decimal?)null); else if (dc.DataType == typeof(double)) ws.SetValue(row, col, !r.IsNull(dc) ? (double)r[dc] : (double?)null); else if (dc.DataType == typeof(float)) ws.SetValue(row, col, !r.IsNull(dc) ? (float)r[dc] : (float?)null); else if (dc.DataType == typeof(string)) ws.SetValue(row, col, !r.IsNull(dc) ? (string)r[dc] : null); else if (dc.DataType == typeof(DateTime)) { if (!r.IsNull(dc)) { ws.SetValue(row, col, (DateTime)r[dc]); // Change the following line if you need a different DateTime format var dtFormat = "dd/MM/yyyy"; ws.Cells[row, col].Style.Numberformat.Format = dtFormat; } else ws.SetValue(row, col, null); } 

IMPORTANT : It is worth noting that DateTime values ​​will require more work for proper processing, since we would like to format it in a certain way AND, possibly, support NULL values ​​in the column: the above method fulfills both of these requirements.

I posted a complete code example (DataTable to Excel file with EPPlus) in this blog post .