C# Export to Excel format

Question

ActionResult:

var strLawTable = new StringBuilder();

strLawTable.Append("<thead>");
strLawTable.Append("<tr>");

strLawTable.Append("<th style=\"text-align: right\">Dollar</th>");

strLawTable.Append("</tr>");
strLawTable.Append("</thead>");   

strLawTable.Append("<tbody>");

foreach (Currency currency in Model.List)
{
strLawTable.Append("<tr>");

strLawTable.Append("<th style=\"text-align: right\">=\"" + GetExcellFormatString(Currency.USD) + "\"</th>");

strLawTable.Append("</tr>");
}

strLawTable.Append("</tbody>");


string headerTable = "<table>" + strLawTable + "</table>";      

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=TestFile.xls");
Response.ContentType = "application/ms-excel";

Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

System.IO.StringWriter sw = new System.IO.StringWriter();
sw.Write(headerTable);

System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);

Response.Write(sw.ToString());
Response.End();

GetExcellFormatString method:

public string GetExcellFormatString(double doubleAmount) 
{
            if (doubleAmount < 1000)
                return doubleAmount.ToString("0.00");
            else if (doubleAmount < 1000000)
                return doubleAmount.ToString("0000.00");
            else if (doubleAmount < 1000000000)
                return doubleAmount.ToString("0000,000.00");
            else if (doubleAmount < 1000000000000)
                return doubleAmount.ToString("0000000000.00");
            else return doubleAmount.ToString();
}

My question:

After I exported the data into an Excel file, I have values in my Excel table below,

Exported Excel table

Dollar

50.0

35.0

40.0

60.0


etc..

If I press to "ctrl" and click with mouse 35.0 and 40.0 Excel can not sum 35.0 and 40.0 because 35.0 is string displays "35.0" and 40.0 is string displays "40.0".

The problem is about sum selected rows however I can not sum because values are string not numeric

How can I remove " " out of string number in Excel table by C# code ?

Thanks.

What if you try using another decimal separator, , instead of .? — Dmitry Popov
– Dmitry Popov, Commented Dec 23, 2014 at 13:56
why not just remove the quotes from your string? change strLawTable.Append("<th style=\"text-align: right\">=\"" + GetExcellFormatString(Currency.USD) + "\"</th>"); to -> strLawTable.Append("<th style=\"text-align: right\">=" + GetExcellFormatString(Currency.USD) + "</th>"); — chancea
– chancea, Commented Dec 23, 2014 at 14:58
@chancea your answer is the best of the best. it worked.this was what i looked for all day today.. Please answer and i will accept it best answer thank you so much — Richard
– Richard, Commented Dec 23, 2014 at 15:05

chancea · Accepted Answer · 2014-12-23 15:16:13Z

1

To remove the quotes change this line:

strLawTable.Append("<th style=\"text-align: right\">=\"" + GetExcellFormatString(Currency.USD) + "\"</th>");
                                                     ^^remove                                     ^^remove

to this:

strLawTable.Append("<th style=\"text-align: right\">=" + GetExcellFormatString(Currency.USD) + "</th>");

edited Dec 23, 2014 at 15:16

answered Dec 23, 2014 at 15:09

chancea

5,9683 gold badges31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arie · Accepted Answer · 2014-12-23 15:03:58Z

If you're not opposed to using third party libraries, there is this solution: http://www.codeproject.com/Articles/692092/A-free-Export-to-Excel-Csharp-class-using-OpenXML

It uses OpenXML library (available through nuget).

It works like a charm - well, if there are only integers and strings in your data. It can be customized, though. The method to look at is WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart), where you'll have to take care of different data types (Double, Decimal, DateTime...). I also recomend using number.ToString(CultureInfo.InvariantCulture) when getting string values of numeric columns. Otherwise there will be the same problem that you have: the column won't be recognized as a correct number (at best it will be a text column, or there will be error in the cell).

You can also set up custom formatting of numbers in your column (if you really have to use specific formatting for your numbers): http://lateral8.com/articles/2010/6/11/openxml-sdk-20-formatting-excel-values.aspx

Collectives™ on Stack Overflow

C# Export to Excel format

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related