Excel Import Problem - Trailing 0's

[lefe0102]

Hello Everyone....

I have had this problem for 3 days now and after going through many forums...I have had no luck. I am importing information from MS Access which goes to a data grid which in turn is sent to me in a lovely MS Excel File. This IS working however there is a small issue...in one of my columns they are numbers with decimal places. EG: 1234.5678 or 1234.6600.

1234.5678 is importing just fine however 1234.6600 is importing as 1234.66 which is no good.:(

Below is my function to export to Excel..

private void exportToExcel()
    {
        string fixedName;

        fixedName = manNames[0].Replace(' ', '_');
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=" +
                           fixedName + "_quotes.xls");
        Response.Charset = "";

        // If you want the option to open the Excel file without saving then
        // comment out the line below
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        TestGrid.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

Once the

Response.Write(stringWrite.ToString());

completes, I can see the data in memory showing up as something like:

<td>1234.6600</td><td> ADHESIVE 82X12X3</td><td>SOME COMPANY</td><td>

AS you can see 1234.6600 looks normal however once Response.End() is complete, the Excel file is built and sent to me...but 1234.6600 is displayed as 1234.66.

Any ideas how I can modify this to show as I want?

Thanks in advance.

[kvprajapati]

Prefix ' (single quote) char - Number stored as Text. Eg. ('100.20000)

[lefe0102]

Prefix ' (single quote) char - Number stored as Text. Eg. ('100.20000)

Hi Thanks for your response.....I tried what you said and its a no go. I am not sure if its because its pulling it from Access or not. I tried deliberatly putting the 'single quote' in the Access DB and that didn't work (obviously) so then I tried to modify the string before it was sent to Excel....no go.

Also I can't assume that the item number is always in the 1111.2222 format as the item number format depends on the company. Any other Ideas?

Thanks

[random1970]

I'm not sure if this will help (I'm not a programmer), but if you can specify formulas / formats when you input the data, Excel does have a FIXED(number,[decimals]) formula which allows you to specify the number of decimal places.

e.g. FIXED(C4,4) would display the value in cell C4 to four decimal places. So, if C4 had the value 2.5 in it, the cell with the formula would display 2.5000.

Assuming this can be done, you could set the number of decimal places required for each company before exporting to Excel.

Alternately, you could create the file first, open it, select the data you wish to display with a certain number of decimal places, then use Format Cells, Number, Decimals, and set the number of decimal places you want to be displayed. If the data is in a single column, this would take a minute at best.

Not perfect, but it's a solution of sorts.