How to Format Excel Decimal Columns Correctly When Exporting Data Using C#?

Formatting Excel Column to Decimal Using C# on Export

When exporting data from a database to Excel using C#, it is possible to encounter issues with numeric columns not being formatted correctly. In particular, decimal values may appear as integers instead of showing the decimal places.

To resolve this issue and ensure that decimal columns are exported correctly, you can use the following method to add decimal formatting to specific columns within the Excel file:

private static void ExportToExcel(DataTable dt, string FileName)
{
    // Create an ExcelPackage object.
    using (ExcelPackage excelPackage = new ExcelPackage())
    {
        // Add a new worksheet to the package.
        ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(FileName);

        // Load the data from the DataTable into the worksheet.
        ws.Cells["A1"].LoadFromDataTable(dt, true);

        // Autofit the columns to accommodate the data.
        ws.Cells[ws.Dimension.Address].AutoFitColumns();

        // Iterate through the columns and apply decimal formatting to the desired ones.
        for (int col = 1; col <= ws.Dimension.End.Column; col++)
        {
            // Get the cell in the first row (header row) of the column.
            var cell = ws.Cells[1, col];

            // If the column contains numeric data, apply decimal formatting to it.
            var columnType = dt.Columns[col - 1].DataType;
            if (columnType == typeof(decimal) || columnType == typeof(double))
            {
                // Set the number format to two decimal places.
                cell.Style.Numberformat.Format = "0.00";
            }
        }

        // Convert the ExcelPackage object to a byte array.
        byte[] bin = excelPackage.GetAsByteArray();

        // Send the byte array to the browser for download.
        Response.ClearHeaders();
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-length", bin.Length.ToString());
        Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + ".xlsx\"");
        Response.OutputStream.Write(bin, 0, bin.Length);
        Response.Flush();

        // Complete the request and clean up.
        HttpContext.Current.ApplicationInstance.CompleteRequest();
    }
}

This method accepts a DataTable and a file name as parameters. It creates an ExcelPackage object and loads the data into it. The code then iterates through each column and checks if it contains numeric data. If it does, the number format is set to "0.00" to ensure that two decimal places are displayed. Finally, the ExcelPackage is converted to a byte array and sent to the browser as an attachment.

The above is the detailed content of How to Format Excel Decimal Columns Correctly When Exporting Data Using C#?. For more information, please follow other related articles on the PHP Chinese website!