How to Format Excel Columns as Decimals and Merge/Format Headers During C# Export?

Formatting Excel Columns to Decimal During Export from C#

Question:

While exporting database data to Excel using C#, numbers in specific columns are displayed as integers instead of decimals (e.g., 5 instead of 5.00). How can I programmatically format these columns in decimal format on the C# side?

Additionally, I would like to merge specific header cells and format them as bold, gray with uppercase text.

Answer:

To format specific Excel columns as decimals during export, you can use the following code:

        Response.Write("<td>");
        Response.Write(String.Format("{0:0.0#}", dr["EstimatedPriceTotal"].ToString()));
        Response.Write("</td>");

The code above uses the String.Format method to format the EstimatedPriceTotal column using the 0.0# decimal format specifier. This will ensure that the value is displayed with two decimal places.

To merge and format header cells, you can use the following code:

        Response.Write("<tr>");
        Response.Write("<th>");
        Response.Write("<span>CustomerName</span>");
        Response.Write("</th>");
        Response.Write("<th>");
        Response.Write("<span>Mitesh Jain</span>");
        Response.Write("</th>");
        Response.Write("</tr>");

This code creates a header row with two cells merged together. The text in the merged cells is wrapped in a tag to allow it to be formatted separately.

Finally, here's a complete example that includes both the decimal formatting and the header formatting:

        Response.Write("<tr>");
        Response.Write("<th>");
        Response.Write("<span>Actual Estimated Price</span>");
        Response.Write("</th>");
        Response.Write("<th>");
        Response.Write("<span>Aprroved Estimated Price </span>");
        Response.Write("</th>");
        Response.Write("<th>");
        Response.Write("<span>Actual Price</span>");
        Response.Write("</th>");
        Response.Write("<th>");
        Response.Write("<span>Aprroved Actual Price </span>");
        Response.Write("</th>");
        Response.Write("<th>");
        Response.Write("TransactionID </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>");

The above is the detailed content of How to Format Excel Columns as Decimals and Merge/Format Headers During C# Export?. For more information, please follow other related articles on the PHP Chinese website!