This project has moved. For the latest updates, please go here.

How do I stop getting the "number stored as text" message?

Jan 30, 2014 at 5:39 PM
I've tried many different methods, but I've had no luck. This is the code I'm using to add the value to the cell
ws.Cell(baseCell).Value = NumberValue;
ws.Cell(baseCell).Style.NumberFormat.SetNumberFormatId(37);
ws.Cell(baseCell).SetDataType(XLCellValues.Number);
I've tried it with several datatypes for "NumberValue" including double, decimal, and float.
I've also tried changing the cell datatype from number to text.

No matter what I do I always get the message. The data I'm using to create the document is all numeric values, so the entire sheet has flags.
Jan 30, 2014 at 5:44 PM
Please disregard or delete this discussion. User error. I was changed the cell type further down in the code.
Feb 13, 2014 at 8:11 AM
Edited Feb 13, 2014 at 8:12 AM
hello,
I have a simmilar problem. I'm using a DataTable as source to export data to Excel File (xls)
When I opening this file all digits I have to convert from text to digit.
What Can I change in my code to solve problem "number stored as text" in my exported data in EXCEL file ??
When I exporting to XLSX I have the same problem and second problem - numeric digit has dot instead of comma. (XLS format has no problem with commas)

 using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt);

                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "";
                    //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("content-disposition", "attachment;filename=File1.xls");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();
                        Response.End();
                    }
                }
Thank you,
Br.
Dec 17, 2014 at 4:33 PM
Edited Dec 17, 2014 at 4:34 PM
I am having a similar issue. I have an Oracle datasource that I'm importing into a datatable (via ODP.NET).

I can create the spreadsheet, but I always get the "number stored as text" message. I have been using a workaround, but now I have a data set that's just too big.

Here is how I'm currently trying to solve the issue: (formatCols is a list of NameActionDictionary, which contains column name, position and format)
       foreach (NameActionDictionary numCol in formatCols)
       {
            ws.Cell(1, numCol.ColLoc).Value = 0;
            ws.Column(numCol.ColLoc).DataType = (numCol.ColType == "date") ? XLCellValues.DateTime : XLCellValues.Number;
            if (numCol.ColType != "date" && numCol.ColType != "number")
            {
                ws.Column(numCol.ColLoc).Style.NumberFormat.Format = getNumberFormatType(numCol.ColType);
            }
            ws.Cell(1, numCol.ColLoc).DataType = XLCellValues.Text;
            ws.Cell(1, numCol.ColLoc).Value = numCol.ColHead;
        }
Is there anyway I can solve this problem? I want dates and numbers to be stored as such.

Thanks.
Coordinator
Dec 17, 2014 at 5:01 PM
The thing is that your data table is the one that defines the data type. The best thing would be to fix the data table so it stores numbers as numbers and not as strings. That may not be possible for many reasons so what you're doing is pretty much your only option.
Dec 17, 2014 at 8:18 PM
Do you have any ideas for reducing the amount of space that's used up (increasing efficiency)? My dataset is 40 columns by 800 rows. I haven't been able to create the file with proper formatting.

Thanks.