使用C#将数据导出到Excel时出现问题 [英] Problem Exporting Data Into Excel using C#
问题描述
我正在使用数据表将数据导出到Excel.它工作正常，并在Excel中提供了许多列.但是，我想添加一些自定义列并使其加粗，这些自定义列应出现在Excel中的数据表列上方.
如果我可以更改其颜色，请告诉我.请参见下面的代码.
Hi,
I am exporting data into Excel using a datatable. It is working fine and giving me a number of columns in Excel. However, I want to add and make bold some custom column which should appear above the datatable column in Excel.
If I can change their color then please let me know. See code below.
public void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
string ColValue;
string ColName = "";
for (int i = 0; i < table.Columns.Count; i++)
{
ColName = table.Columns[i].ColumnName.ToString() + "\t";
context.Response.Write(ColName);
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
//row[i] = row[i].ToString().Trim().Replace("\t", string.Empty) ;
ColName = table.Columns[i].ColumnName.ToString();
DateTime tempDate = new DateTime();
if (DateTime.TryParse(row[i].ToString().Trim(), out tempDate) == true)
ColValue = tempDate.ToString("dd/MM/yyyy");
else
{
ColValue = row[i].ToString();
}
ColValue = ColValue.ToString().Replace(",", string.Empty) + "\t";
ColValue = ColValue.ToString().Replace(Environment.NewLine, " ");
ColValue = ColValue.ToString().Replace("\n", " ");
ColValue = ColValue.ToString().Replace(" ", "");
ColValue = ColValue.ToString().Replace("-Select-;", "");
if (table.Columns[i].DataType.Name == "Boolean")
{
ColValue = ColValue.ToString().Replace("True", "Yes");
ColValue = ColValue.ToString().Replace("False", "No");
}
context.Response.Write(ColValue);
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "application/ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename = " + name + ".xls");
context.Response.End();
}
推荐答案
在没有自动化Excel的情况下，您实际上不应使用服务器端代码来做到这一点(无论如何也可能会遇到权限问题)，这是一种实现这是使用上面类似的代码，但是输出有效的HTML并以.xls扩展名保存.
这将使您可以加粗某些列并应用常规的HTML样式.
例如，在此处查看示例
http://forums.asp.net/t/1214938.aspx [
Without automating Excel, which you shouldn''t really do using server side code (and may run into permissions issues anyway), a way to achieve this is to use the similar code above but output valid HTML and save with the .xls extension.
This will let you bold certain columns + apply general HTML styling.
e.g look at examples here
http://forums.asp.net/t/1214938.aspx[^]
For example, create a new text file on your computer. Paste the following HTML into it & save the file. Then change the file extension to .xls & open it in Excel. Notice we''ve got bold headers + a ''red'' style.
<HTML>
<HEAD>
<style type='text/css'>
table
{
cellpadding: 0px;
font-size: xx-small;
border-collapse:collapse;
}
td.some-style
{
border: 1px solid black;
color: red;
}
</style>
</HEAD>
<BODY>
<TABLE border=0>
<TR>
<Td></Td>
<Td></Td>
<Td></Td>
<Td class="some-style">This is an example</Td>
<Td></Td>
</TR>
<thead>
<TR>
<Th> ProductID</Th>
<Th> Product Name</Th>
<Th> Sales Person</Th>
<Th> Date Closed</Th>
<Th> Sale Amount</Th>
</TR>
</thead>
<TR>
<TD>1</TD>
<TD>Code Project</TD>
<TD>Dylan Morley</TD>
<TD>10/03/2010</TD>
<TD>50.00</TD>
</TR>
<TR>
<TD>2</TD>
<TD>Microsoft</TD>
<TD>Dylan Morley</TD>
<TD>10/03/2010</TD>
<TD>50.00</TD>
</TR>
</TABLE>
</BODY>
</HTML>
您可以在导出例程中轻松创建此HTML
注意:这仅是对于安装了Excel 2003或更高版本的计算机的有效解决方案，我认为这是何时首次引入HTML功能?
You could create this HTML quite easily in your export routine
NB: This would only be a valid solution for machines with Excel 2003 or greater installed, which I think is when then first introduced HTML capabilities?
这篇关于使用C#将数据导出到Excel时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
