excel turning my numbers to floats

Question

Hello, i have a bit of asp.net code that exports data in a datagrid into excel but i noticed that it messes up a particular field when exporting. eg ..

i have the value of something like 89234010000725515875 in a column in the datagrid but when exported, it turns it into 89234+19.

Is there any excel formatting that will bring back my original number?

thanks

Answer 1

Excel isn't really messing up the field. Two things are happening:

1. Excel formats large numbers in scientific notation. So "89234010000725515875" becomes "8.9234E+19" or "8.9234 x 10 ^ 19".
2. The size of the number "89234010000725515875" exceeds the precision in which Excel uses to store values. Excel stores your number as "89234010000725500000" so you're losing the last five digits.

Depending on your needs you can do one of two things.

Your first option is to change the formatting from "General" to "0" (Number with zero decimal places.) This will give you "89234010000725500000" so you will have lost precision but you will be able to perform calculcations on the number.

The second option is to format the cell as text "@" or to paste your field with an apostrophe at the beginning of the line to force the value to be text. You'll get all of the digits but you won't be able to do calculations of the value.

I hope this helps.

Answer 2

Below is the C# source code to do this with SpreadsheetGear for .NET. Since the SpreadsheetGear API is similar to Excel's API, you should be able to easily adapt this code to Excel's API to get the same result.

You can download a free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

using System;
using SpreadsheetGear;

namespace Program
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new workbook and get a reference to A1.
            IWorkbook workbook = Factory.GetWorkbook();
            IWorksheet worksheet = workbook.Worksheets[0];
            IRange a1 = worksheet.Cells["A1"];
            // Format A1 as Text using the "@" format so that the text
            // will not be converted to a number, and put the text in A1.
            a1.NumberFormat = "@";
            a1.Value = "89234010000725515875";
            // Show that the formatted value is 
            Console.WriteLine("FormattedValue={0}, Raw Value={1}", a1.Text, a1.Value);
            // Save the workbook.
            workbook.SaveAs(@"c:\tmp\Text.xls", FileFormat.Excel8);
            workbook.SaveAs(@"c:\tmp\Text.xlsx", FileFormat.OpenXMLWorkbook);
        }
    }
}