Format Excel Column to Decimal Doing Export from C#

Format excel column to decimal doing export from c#

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();
}

How do I set excel format 0.00% to 0.0000 decimal value

Read it into a double, then format and write it back. I don't get any errors that way.

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
Dim d As Double

d = ws.Range("C14").Value
ws.Range("C14").NumberFormat = "0.00%"
ws.Range("C14").Value = d

I'm not clear if your data is already in the worksheet or not. If it is not then put the data into the double from the source.

d = Val("0.00000000000000000")
ws.Range("C14").NumberFormat = "0.00%"
ws.Range("C14").Value = d

Exporting data from a datagirdview to an excel sheet with decimals retained

10.000 IS 10. Excel is using "general" formatting by default which doesn't show any trailing zeros. If you want to format the number on the excel side you'll have to explicitly do that.

indexWorkSheet.Cells[i + 10, j + 4] = dataGridViewIndex.Rows[i].Cells[j].Value.ToString();
indexWorkSheet.Cells[i + 10, j + 4].NumberFormat = "#,##0.000";  
// or whatever format is appropriate.

However I would make one other change:

• Work with ranges instead of cells. Every cell access is a COM call which adds significant overhead to the process. If you ran a decent profiles against it I would be willing to bet that the vast majority of your time is spent on the cell access.

Put your data into an array and set the value in one call (and set the format for the range as well):

int[,] data = new int[rows,cols];

... loop
    data[i,j] = value;

var range = indexworksheet.get_Range(...);
range.Value = data;
range.NumberFormat = "#,##0.000"; 

Issue with decimal value 0.00 after exporting to Excel using c#

If you right-click on the cell it is being exported to and choose 'Format cells...' what number type does it come up with? If it is 'General' then that makes sense as to why is had no decimal spaces. If you select 'Number' then you can define how many decimal spaces will be displayed. Is this what you were looking for?

Export double to Excel - decimal separator issue

With comma as a decimal separator, you aren't using en-US Regional settings. You will need to check to make sure that Excel knows this, or convert the number into en-US settings before sending it to excel. There are functions inside the .ToString() overloads that you can use.

i.ToString(new System.Globalization.CultureInfo("en-US").NumberFormat);

Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000

Resolved this issue as follows. I lost track of an helpful article that suggested these steps.

1. on RowDataBound of GridView, I added Class attribute to the desired column

   protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e)
   {

       if (e.Row.RowType == DataControlRowType.DataRow)
       {
           e.Row.Cells[22].Attributes.Add("class", "cost");
       }
   } 
   

2. And made some minor changes to the code as below. Well, the issue is resolved for now.

       try
       {
           DataSet ds = new DataSet();
           ds = businesscase.services.Actuals.GetActualsGridData(bcid, cmd);
           using (System.IO.StringWriter sw = new System.IO.StringWriter())
           {
               using (HtmlTextWriter htw = new HtmlTextWriter(sw))
               {
                   // instantiate a datagrid 
                   GridView gvExcel = new GridView();
                   gvExcel.RowDataBound += new GridViewRowEventHandler(this.gvExcel_RowDataBound);
   
                   gvExcel.DataSource = ds.Tables[0];
                   gvExcel.DataBind();
                   gvExcel.RenderControl(htw);
   
                   response.Write("<style> .cost{mso-number-format:\"\\#\\#0\\.00\";} </style>");
   
                   //response.Write(style);
                   response.Write(sw.ToString());
                   response.End();
               }
           }
       }
       catch
       {
   
       }
   

---

Related Topics