将excel列格式化为十进制从c#导出 [英] Format excel column to decimal doing export from c#
本文介绍了将excel列格式化为十进制从c#导出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下方法将数据库导出为
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();
但我无法以十进制格式导出 Excel 中的实际估计价格、批准估计价格
but I am not able to export Actual Estimated Price, Aprroved Estimated Price in excel as decimal format
值变为 5 而不是显示 5.00
如何从 C# 端将某些 excel 列格式化为十进制格式
更新
如何在 EPPPlus 中合并列标题合并
How can I merge column header merge in EPPPlus
我想要两个标题名称
CustomerName
Mitesh Jain
推荐答案
给你,一个完整的方法.只需发送一个 DataTable 和一个文件名,剩下的就完成了.此代码段还会使标题行变为灰色并带有粗体文本,并会自动适应各列.
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();
}
这篇关于将excel列格式化为十进制从c#导出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文