Format excel column to decimal doing export from c#

Question

Hi I am exporting database to excel with below method as

        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=" + FileName);
        Response.ContentType = "application/vnd.ms-excel";
        EnableViewState = false;
        Response.Write("<style> TABLE { border:dotted 1px #999; } TH { border:dotted 1px #D5D5D5; text-align:center } TD { border:dotted 1px #D5D5D5; } </style>");
        Response.Write("<table>");
        Response.Write("<tr>");          
        Response.Write("<th>Actual Estimated Price</th>");
        Response.Write("<th>Aprroved Estimated Price </th>");
        Response.Write("<th>Actual Price</th>");
        Response.Write("<th>Aprroved Actual Price </th>");
        Response.Write("<th>TransactionID </th>");            
        Response.Write("<th>Created On</th>");
        Response.Write("</tr>");
        foreach (DataRow dr in dt.Rows)
        {
            Response.Write("<tr>");
            Response.Write("<td>");
            Response.Write(String.Format("{0:0.0#}", dr["EstimatedPriceTotal"].ToString()));
            Response.Write("</td>");
            Response.Write("<td>");
            Response.Write(String.Format("{0:0.0#}", dr["ApprovedEstimatedPriceTotal"].ToString()));
            Response.Write("</td>");
            Response.Write("<td>");
            Response.Write(String.Format("{0:0.0#}", dr["ActualPriceTotal"].ToString()));
            Response.Write("</td>");
            Response.Write("<td>");
            Response.Write(String.Format("{0:0.0#}", dr["ApprovedActualPriceTotal"].ToString()));
            Response.Write("</td>");
            Response.Write("<td>");
            Response.Write(dr["TransactionID"].ToString());
            Response.Write("</td>"); 
            Response.Write("<td>");    
            Response.Write(Convert.ToDateTime(dr["CreatedOn"].ToString()));
            Response.Write("</td>");
            Response.Write("</tr>");
        }
        Response.Write("</table>");
        Response.End();

but I am not able to export Actual Estimated Price, Aprroved Estimated Price in excel as decimal format

The value is coming as 5 instead of showing 5.00

How can I format some column of excel to decimal format from c# side

Update

How can I merge column header merge in EPPPlus

I want both header name as

CustomerName
Mitesh Jain

Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus) for example. All you are doing now is creating a HTML page with an .xls extension. Then you can also specify the data type — VDWWD, Nov 14 '17 at 18:20
@VDWWD no link is opening on click and also I dont want to chnage the code. Is there any simplest way? — Xtremcool, Nov 14 '17 at 18:23
nope, since you are not creating a xls but a html page. But why not change the code. you can do it in less lines of code. You could convert it all to a string but you'll lose localization. — VDWWD, Nov 14 '17 at 18:31
See my answer here https://stackoverflow.com/a/44330799/5836671 or this: https://stackoverflow.com/a/37543745/5836671 — VDWWD, Nov 14 '17 at 18:36
@VDWWD not able to understand the code clearly, can u replace that with my given columns and data? — Xtremcool, Nov 14 '17 at 18:38
Added an answer. Don't forget to download the `EPPlus.dll` library — VDWWD, Nov 14 '17 at 18:51

score 0 · Accepted Answer · edited Nov 08 '18 at 21:47

0

Here you go, one complete method. Just send a DataTable and a file name and this does the rest. This snippet will also make the header row gray with bold text and will auto fit the columns.

using OfficeOpenXml;
using OfficeOpenXml.Style;

public void ExportToExcel(DataTable dt, string FileName)
{
    //create a new byte array       
    byte[] bin;

    //create a new excel document
    using (ExcelPackage excelPackage = new ExcelPackage())
    {
        //create a new worksheet
        ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(FileName);

        //add the contents of the datatable to the excel file
        ws.Cells["A1"].LoadFromDataTable(dt, true);

        //auto fix the columns
        ws.Cells[ws.Dimension.Address].AutoFitColumns();

        //loop all the columns
        for (int col = 1; col <= ws.Dimension.End.Column; col++)
        {
            //make all columns just a bit wider, it would sometimes not fit
            ws.Column(col).Width = ws.Column(col).Width + 1;

            var cell = ws.Cells[1, col];

            //make the text bold
            cell.Style.Font.Bold = true;

            //make the background of the cell gray
            var fill = cell.Style.Fill;
            fill.PatternType = ExcelFillStyle.Solid;
            fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF"));

            //make the header text upper case
            cell.Value = ((string)cell.Value).ToUpper();
        }

        //convert the excel package to a byte array
        bin = excelPackage.GetAsByteArray();
    }

    //clear the buffer stream
    Response.ClearHeaders();
    Response.Clear();
    Response.Buffer = true;

    //set the correct contenttype
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    //set the correct length of the data being send
    Response.AddHeader("content-length", bin.Length.ToString());

    //set the filename for the excel package
    Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + ".xlsx\"");

    //send the byte array to the browser
    Response.OutputStream.Write(bin, 0, bin.Length);

    //cleanup
    Response.Flush();
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

edited Nov 08 '18 at 21:47

marc_s

732,580
175
1,330
1,459

answered Nov 14 '17 at 18:51

VDWWD

35,079
22
62
79

Excel Package culd not be found, DO WE NEED TO ADD ANY DLL OR NAMESPACE? – Xtremcool Nov 14 '17 at 18:54
Did you add the library to the bin folder, added the `using` to the top of the page and added a reference in your project? This is the easiest i can possibly make it... – VDWWD Nov 14 '17 at 18:56
Suppose I want by D row to be coming in decimal format and E as datatime format – Xtremcool Nov 14 '17 at 19:08
I have tried adding if(col==11) { ws.Cells[1, col].Style.Numberformat.Format = "0.00"; } code in foreach loop but I am not getting this format – Xtremcool Nov 14 '17 at 19:26
See my post here for number formats: https://stackoverflow.com/questions/40209636/epplus-number-format/40214134#40214134 – VDWWD Nov 14 '17 at 19:28
A25 means col=25 right? And I have to wrote this before for loop start – Xtremcool Nov 14 '17 at 19:30
Yes, and rows and columns start at `1`, not `0` like all other arrays – VDWWD Nov 14 '17 at 19:33
How to write ws.Cells["k1:k#"].Style.Numberformat.Format = "0.00"; I mean k as infinity – Xtremcool Nov 14 '17 at 19:36
See my loop: `ws.Dimension.End.Column` – VDWWD Nov 14 '17 at 19:39
Either fix that in the source data or `cell.Value`. – VDWWD Nov 14 '17 at 19:44
Can u give example to merge header cell and rename it – Xtremcool Nov 14 '17 at 19:46

Format excel column to decimal doing export from c#

1 Answers1

Linked