ASP.NET Excel Export formatting question

Question

Hey Guys,

I've been banging my head on the wall over this issue for a couple days now and I need some help.

I'm creating an excel file using ASP.net and everything is going just fine except one column has to be formatted as a decimal to 2 places but when it pulls into excel, excel automatically formats it as a whole number instead of keeping the decimal places.

sw.Write(String.Format("{0:f}", CDbl(dr(14)).ToString("0.00"))) this produces "20.00"

but when I open it in excel it's displayed as 20...if I select the whole column and format it as a number it gets displayed as 20.00 like it's supposed to but I don't want to have to do this (I can't do that) the file is supposed to be automatically picked up and imported into another system that needs the column to be a decimal.

Any suggestions would be greatly appreciated.

Answer 1

Maybe you shouldnt output as string, but as decimal or double? AFAIR, object type is acceptable.

Answer 2

Are you writing the output to CSV, HTML, XML, DOCX or are you using a 3rd party library to generate binary Excel 2003?

The answer varies for each.

After formating the excel document, save in the appropriate format (html, CSV, XML, etc) open the file in notepad and look at what formating codes excel sprinkled around your number.

Part 2 (I couldn't put this in a comment, so I putting my comment in my answer) For text files (which have no metadata) Excel looks at the first 10 or so rows and makes a guess about the data type and applies a default format. Sometimes you can guide the guessing process by making sure there is data in the first few rows and that it is formated in a way that excel can guess the data type. However, AFAIK, you can't set the format.

I recommend switching to exporting HTML tables. Excel understands HTML tables and you can use a well documented model (HTML!) to format the results for color, size, etc and probably number formating as long as excel interprets the column as a string and preserves the formating.

If that fails use a 3rd party library such as http://www.codeplex.com/ExcelPackage to export docx.

Answer 3

If you open a tab delimited file by double-clicking it (basically what you are doing when your site streams it) Excel "guesses" at the format you want. You have to open Excel and then use the file menu to open the file where you are presented with an import wizard that lets you pick the data type for your columns. Then you get the format you want.

You will need to use a different method to generate your file from your site if you want the correct behavior. You will need to actually create an Excel file (.xls) using something like VSTO or third-party library that maintains the formating language.

The other choice is to learn the Excel file format well enough to use an xslt transform on your data to generate the file....or write it out in code fully.

Answer 4

As far as I know, it is not possible to specify a format of "0.00" in CSV. Excel does seem to pick up some formats from CSV, for example $123.00 will be formatted as currency with two decimals.

SrpreadsheetGear for .NET will do it. Here is a simple example:

using System;
using SpreadsheetGear;

namespace Program
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new empty workbook and get a reference to Sheet1!A1.
            var workbook = SpreadsheetGear.Factory.GetWorkbook();
            var a1 = workbook.Worksheets[0].Cells["A1"];
            // Format A1 as numeric with two decimals.
            a1.NumberFormat = "0.00";
            // Put 123 in A1.
            a1.Value = 123;
            // Save as xls.
            workbook.SaveAs(@"c:\tmp\FormattedNumber.xls", FileFormat.Excel8);
            // Save as xlsx.
            workbook.SaveAs(@"c:\tmp\FormattedNumber.xlsx", FileFormat.OpenXMLWorkbook);
        }
    }
}

You can see live ASP.NET Excel Reporting samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 5

Excel is applying it's default number format since the source doesn't hold any formatting information. Other than having the user change the cell format, you will need to supply data with formatting. This will be problem if you want to keep the tab delimited format. I have had good results sending the data as a HTML table with the fields formatted as you desire. Excel seems to respect the various HTML attributes. This post discusses some other formatting questions but will give you an idea how you could use HTML and Excel tags to solve your issue.