C#导出DataTable到Excel两种方案
方案一：
因为office2007以下版本，只支持65535行，超过后excel就无法创建或打开。所以需要进行分sheet进行
private static string BuildExportHTML(System.Da
{
string result = string.Empty;
int readCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
int pagerecords = 50000;
result = "<?xml version=\"1.0\" encoding=\"gb2312\"?>";
result += "<?mso-application progid=\"Excel.Sheet\"?>";
result += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:o=\"urn:schemas-microsoft-com:office:office\" ";
result += "xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ";
result += "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ";
//以下两部分是可选的
//result += "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">";
//result += "<Author>User</Author>";
//result += "<LastAuthor>User</LastAuthor>";
//result += "<Created>2009-03-20T02:15:12Z</Created>";
//result += "<Company>Microsoft</Company>";
//result += "<Version>12.00</Version>";
//result += "</DocumentProperties>";
//result += "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">";
//result += "<WindowHeight>7815</WindowHeight>";
//result += "<WindowWidth>14880</WindowWidth>";
//result += "<WindowTopX>240</WindowTopX>";
//result += "<WindowTopY>75</WindowTopY>";
//result += "<ProtectStructure>False</ProtectStructure>";
//result += "<ProtectWindows>False</ProtectWindows>";
//result += "</ExcelWorkbook>";
string strTitleRow = "";
//设置每行的标题行
strTitleRow = "<Row ss:AutoFitHeight='0'>";
for (int j = 0; j < colCount; j++)
{
strTitleRow += "<Cell><Da
}
strTitleRow += "</Row>";
StringBuilder strRows = new StringBuilder();
//在变长的字符操作方面stringbuilder的效率比string高得多
int page = 1; //分成的sheet数
int cnt = 1; //输入的记录数
int sheetcolnum = 0; //每个sheet的行数，其实就等于cnt+1
for (int i = 0; i < readCnt; i++)
{
strRows.Append("<Row ss:AutoFitHeight=\"0\">");
for (int j = 0; j < colCount; j++)
{
if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
{
if (dt.Rows[i][j].ToString() != string.Empty)
{
strRows.Append("<Cell><Da
}
else
strRows.Append("<Cell><Da
}
else
{
strRows.Append("<Cell><Da
}
}
strRows.Append("</Row>");
cnt++;
//到设定行数时，要输出一页，防止office打不开，同时要注意string和stringbuilder的长度限制
if (cnt >= pagerecords+1)
{
sheetcolnum = cnt + 1;
result += "<Worksheet ss:Name=\"Sheet" + page.ToString() + "\"><Table ss:ExpandedColumnCount=\"" + colCount.ToString() + "\" ss:ExpandedRowCount=\"" + sheetcolnum.ToString() + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"104\" ss:DefaultRowHeight=\"13.5\">" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet>";
strRows.Remove(0, strRows.Length);
cnt = 1; //下一个sheet重新计数
page++;
}
}
sheetcolnum = cnt + 1;
result = result + "<Worksheet ss:Name='Sheet" + page.ToString() + "'><Table ss:ExpandedColumnCount='" + colCount.ToString() + "' ss:ExpandedRowCount='" + sheetcolnum.ToString() + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='104' ss:DefaultRowHeight='13.5'>" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet></Workbook>";
return result;
}
问题：因为这一种方案是以xml格式生成的excel，所以生成的文件特别大，六万条10列的数据大约有30M，对网络传输不利，在局域网内用还是不错的。
第二种方案：采用格式少的方式生成数据，数据量少，网络下载传输时比较方便，同样以上面六万条10列的数据，只有6M左右。缺点是，超过65535，在office2003上就打不开了。
private static string BuildExportHTML(System.Da
{
string result = string.Empty;
int readCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
int pagerecords = 5000;
string strTitleRow = "";
for (int j = 0; j < colCount; j++)
{
strTitleRow += dt.Columns[j].ColumnName + "\t";
}
strTitleRow += "\r\n";
StringBuilder strRows = new StringBuilder();
int cnt = 1;
for (int i = 0; i < readCnt; i++)
{
//strRows.Append("");
for (int j = 0; j < colCount; j++)
{
if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
{
if (dt.Rows[i][j].ToString() != string.Empty)
{
strRows.Append(Convert.ToDateTime(dt.Rows[i][j].ToString()).ToString("yyyy年MM月dd日") + "\t");
}
else
strRows.Append("\t");
}
else
{
strRows.Append(dt.Rows[i][j].ToString().Trim() + "\t");
}
}
strRows.Append("\r\n");
cnt++;
if (cnt >= pagerecords)
{
result += strRows.ToString();
strRows.Remove(0, strRows.Length);
cnt = 1;
}
}
result = strTitleRow + result + strRows.ToString();
return result;
}
如果谁能把两种方案结合起来的话，告诉我一声，谢谢哦。
送一个调用的方法，可以输出到客户端哦
public void DataTable2Excel(DataTable dt)
{
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";//设置导出文件的名称
HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.Default;
curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + fileName));
curContext.Response.Charset = "";
curContext.Response.Write(BuildExportHTML(dt));
curContext.Response.Flush();
curContext.Response.End();
}
