我有使用字符串将DataTable导出到XLS的简单方法。列数是5-30，行数可能是1到1000。有时性能有问题，我请教我可以更改代码的建议。我正在使用.net 4.0
public string FormatCell(string columnName, object value)
{
StringBuilder builder = new StringBuilder();
string formattedValue = string.Empty;
string type = "String";
string style = "s21";
if (!(value is DBNull) && columnName.Contains("GIS"))
formattedValue = Convert.ToDouble(value).ToString("##.00000000°");
else if (value is DateTime)
{
style = "s22";
type = "DateTime";
DateTime date = (DateTime)value;
formattedValue = date.ToString("yyyy-MM-ddTHH:mm:ss.fff");
}
else if (value is double || value is float || value is decimal)
{
formattedValue = Convert.ToDecimal(value).ToString("#.00").Replace(',', '.');
type = "Number";
}
else if (value is int)
{
formattedValue = value.ToString();
type = "Number";
}
else
formattedValue = value.ToString();
builder.Append(string.Format("<Cell ss:StyleID=\"{0}\"><Data ss:Type=\"{1}\">", style, type));
builder.Append(formattedValue);
builder.AppendLine("</Data></Cell>");
return builder.ToString();
}
public string ConvertToXls(DataTable table)
{
StringBuilder builder = new StringBuilder();
int rows = table.Rows.Count + 1;
int cols = table.Columns.Count;
builder.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
builder.AppendLine("<?mso-application progid=\"Excel.Sheet\"?>");
builder.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
builder.AppendLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
builder.AppendLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
builder.AppendLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
builder.AppendLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
builder.AppendLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">;");
builder.AppendLine(" <Author>Author</Author>");
builder.AppendLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
builder.AppendLine(" <Company>Company</Company>");
builder.AppendLine(" <Version>1.0</Version>");
builder.AppendLine(" </DocumentProperties>");
builder.AppendLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
builder.AppendLine(" <WindowHeight>8955</WindowHeight>");
builder.AppendLine(" <WindowWidth>11355</WindowWidth>");
builder.AppendLine(" <WindowTopX>480</WindowTopX>");
builder.AppendLine(" <WindowTopY>15</WindowTopY>");
builder.AppendLine(" <ProtectStructure>False</ProtectStructure>");
builder.AppendLine(" <ProtectWindows>False</ProtectWindows>");
builder.AppendLine(" </ExcelWorkbook>");
builder.AppendLine(" <Styles>");
builder.AppendLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
builder.AppendLine(" <Alignment ss:Vertical=\"Bottom\"/>");
builder.AppendLine(" <Borders/>");
builder.AppendLine(" <Font/>");
builder.AppendLine(" <Interior/>");
builder.AppendLine(" <Protection/>");
builder.AppendLine(" </Style>");
builder.AppendLine(" <Style ss:ID=\"s21\">");
builder.AppendLine(" <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
builder.AppendLine(" </Style>");
builder.AppendLine(" <Style ss:ID=\"s22\">");
builder.AppendLine(" <NumberFormat ss:Format=\"Short Date\"/>");
builder.AppendLine(" </Style>");
builder.AppendLine(" </Styles>");
builder.AppendLine(" <Worksheet ss:Name=\"Export\">");
builder.AppendLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
builder.AppendLine(" x:FullRows=\"1\">");
//generate title
builder.AppendLine("<Row>");
foreach (DataColumn eachColumn in table.Columns) // you can write a half columns of table and put the remaining columns in sheet2
{
if (eachColumn.ColumnName != "ID")
{
builder.Append("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
builder.Append(eachColumn.ColumnName.ToString());
builder.AppendLine("</Data></Cell>");
}
}
builder.AppendLine("</Row>");
//generate data
foreach (DataRow eachRow in table.Rows)
{
builder.AppendLine("<Row>");
foreach (DataColumn eachColumn in table.Columns)
{
if (eachColumn.ColumnName != "ID")
{
builder.AppendLine(FormatCell(eachColumn.ColumnName, eachRow[eachColumn]));
}
}
builder.AppendLine("</Row>");
}
builder.AppendLine(" </Table>");
builder.AppendLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
builder.AppendLine(" <Selected/>");
builder.AppendLine(" <Panes>");
builder.AppendLine(" <Pane>");
builder.AppendLine(" <Number>3</Number>");
builder.AppendLine(" <ActiveRow>1</ActiveRow>");
builder.AppendLine(" </Pane>");
builder.AppendLine(" </Panes>");
builder.AppendLine(" <ProtectObjects>False</ProtectObjects>");
builder.AppendLine(" <ProtectScenarios>False</ProtectScenarios>");
builder.AppendLine(" </WorksheetOptions>");
builder.AppendLine(" </Worksheet>");
builder.AppendLine(" <Worksheet ss:Name=\"Sheet2\">");
builder.AppendLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
builder.AppendLine(" <ProtectObjects>False</ProtectObjects>");
builder.AppendLine(" <ProtectScenarios>False</ProtectScenarios>");
builder.AppendLine(" </WorksheetOptions>");
builder.AppendLine(" </Worksheet>");
builder.AppendLine(" <Worksheet ss:Name=\"Sheet3\">");
builder.AppendLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
builder.AppendLine(" <ProtectObjects>False</ProtectObjects>");
builder.AppendLine(" <ProtectScenarios>False</ProtectScenarios>");
builder.AppendLine(" </WorksheetOptions>");
builder.AppendLine(" </Worksheet>");
builder.AppendLine("</Workbook>");
return builder.ToString();
}
使用这个：
string xlsData= ConvertToXls(someTable)
System.CodeDom.Compiler.TempFileCollection fileCollection = new System.CodeDom.Compiler.TempFileCollection();
string tempFileName = fileCollection.AddExtension("xls", true);
if (File.Exists(tempFileName))
File.Delete(tempFileName);
using (StreamWriter writer = new StreamWriter(tempFileName, false, Encoding.UTF8))
writer.Write(xlsData);
最佳答案
您可以做的最简单的事情是声明StringBuilder的容量不是默认值，例如
StringBuilder builder = new StringBuilder(100000);
默认分配为16个字节，每次需要重新分配时都会增加一倍。这意味着如果使用默认值，它将被多次重新分配。
除非您的系统内存不足，或者这确实非常庞大，否则我怀疑直接像以前建议的那样对其进行流传输会带来很大的不同。我怀疑这实际上可能会使情况变得更糟，因为我怀疑文件流写入的开销要比向已分配的StreamBuilder对象添加数据的开销少（假设不需要经常重新分配！）
最佳解决方案可能是将stringbuilder输出增长到某个大小（根据系统的内存）（如果大于10或20兆字节），则将其定期发送到流中。这样，您将避免内存问题，并避免与对输出流进行多次小写操作有关的任何潜在开销。
更新-测试说明：
我进行了一些测试，创建了非常大的字符串（> 50 MB），并且预先分配内存的差异很小。
但更重要的是，使用最简单的形式创建这样的字符串所需的时间：
for (int i = 0; i < 10000000; i++)
{
builder.AppendLine("a whole bunch of text designed to see how long it takes to build huge strings ");
}
几乎无关紧要。我可以在几秒钟内填满台式机的所有内存。
这意味着StringBuilder的开销完全不是您的问题。也可以从中得出结论，切换到流写入绝对不会帮助您。
相反，您需要查看正在执行数千或数万次的某些操作。这个循环：
foreach (DataRow eachRow in table.Rows)
{
builder.AppendLine("<Row>");
foreach (DataColumn eachColumn in table.Columns)
{
if (eachColumn.ColumnName != "ID")
{
builder.AppendLine(FormatCell(eachColumn.ColumnName, eachRow[eachColumn]));
}
}
builder.AppendLine("</Row>");
}
取消检查
ColumnName！=“ ID”通过删除
从您选择的
FormatCell将为每个单个数据元素运行一次。效率的微小变化可能会产生巨大影响
以前没有考虑过，但是如果您的DataTable来自SQL数据源，请直接使用DataReader而不是内存中的DataTable
改进FormatCell的建议：
预先为每列的数据类型建立一个索引，这样您就不必每次都进行昂贵的类型比较
设置类型和样式的字符串值，然后根据数据类型进行更改很昂贵。改用枚举，然后根据枚举值使用硬编码字符串输出值。
将FormatCell中的任何变量移到主类，这样就不必在每次调用该过程时都创建/分配它们
要构建索引，我认为最有效的方法是将列号映射到定义每一列类型的数组，如下面的代码，然后在FormatCell中，只需使用预先构建的列号到数据类型的映射即可。
enum DataTypes
{
DateTime = 1,
Float = 2,
Int = 3,
String = 4
}
DataTypes[] types = new DataTypes[tbl.Columns.Count];
for (int col=0;i<tbl.Columns.Count;col++) {
object value = tbl.Rows[0][col];
if (value is double || value is float || value is decimal) {
types[col]=DataTypes.Float;
} else if (value is DateTime) {
types[col]=DataTypes.DateTime;
} else if (value is int) {
types[col]=DataTypes.Int;
} else {
types[col]=DataTypes.String;
}
}
然后将FormatCell传递给columnumber，它可以从数组中查找数据类型，并仅需进行开关检查：
switch(types[colNumber]) {
case DataTypes.DateTime:
...
break;
case DataTypes.Int:
...
/// and so on
}
我认为这会减少很多开销。