关于ASP.NET 将数据导出成Excel 的总结[下]

// ExcelHelper.cs

public class ExcelHelper
{
// Row limits older Excel version per sheet
const int rowLimit = 65000;

private static string getWorkbookTemplate()
{
var sb = new StringBuilder();
sb.Append( " <xml version>\r\n<Workbook
xmlns=\ " urn:schemas-microsoft-com:office:spreadsheet\"\r\n ");
sb.Append( " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n
xmlns:x=\ " urn:schemas- microsoft-com:office:excel\"\r\n
xmlns:ss=\ " urn:schemas-microsoft-com:office:spreadsheet\">\r\n ");
sb.Append( " <Styles>\r\n
<Style ss:ID=\ " Default\" ss:Name=\"Normal\">\r\n
<Alignment ss:Vertical=\ " Bottom\"/>\r\n <Borders/> ");
sb.Append( " \r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n
<Protection/>\r\n </Style>\r\n
<Style ss:ID=\ " BoldColumn\">\r\n <Font ");
sb.Append( " x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n
<Style ss:ID=\ " s62\">\r\n <NumberFormat ");
sb.Append( " ss:Format=\"@\"/>\r\n </Style>\r\n
<Style ss:ID=\ " Decimal\">\r\n
<NumberFormat ss:Format=\ " 0.0000\"/>\r\n </Style>\r\n ");
sb.Append( " <Style ss:ID=\"Integer\">\r\n
<NumberFormat ss:Format=\ " 0\"/>\r\n </Style>\r\n
<Style ss:ID=\ " DateLiteral\">\r\n <NumberFormat ");
sb.Append( " ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n
<Style ss:ID=\ " s28\">\r\n ");
sb.Append( " <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\"
ss:ReadingOrder=\ " LeftToRight\" ss:WrapText=\"1\"/>\r\n ");
sb.Append( " <Font x:CharSet=\"1\" ss:Size=\"9\"
ss:Color=\ " #808080\" ss:Underline=\"Single\"/>\r\n ");
sb.Append( " <Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>

</Style>\r\n</Styles>\r\n { 0}</Workbook> " );
return sb.ToString();
}

private static string replaceXmlChar( string input)
{
input = input.Replace( " & ", " & ");
input = input.Replace( " < ", " < ");
input = input.Replace( " > ", " > ");
input = input.Replace( " \" ", """ );
input = input.Replace( " ' ", " ' ");
return input;
}

private static string getWorksheets(DataSet source)
{
var sw = new StringWriter();
if (source == null || source.Tables.Count == 0)
{
sw.Write( " <Worksheet ss:Name=\"Sheet1\"><Table><Row>

<Cell ss:StyleID=\ " s62\"><Data ss:Type=\"String\"></Data>
</Cell></Row></Table></Worksheet> " );
return sw.ToString();
}
foreach (DataTable dt in source.Tables)
{
if (dt.Rows.Count == 0)
sw.Write( " <Worksheet ss:Name=\" " + replaceXmlChar(dt.TableName) +
" \"><Table><Row><Cell ss:StyleID=\"s62\">

<Data ss:Type=\ " String\"></Data></Cell></Row>
</Table></Worksheet> " );
else
{
// write each row data
var sheetCount = 0;
for ( int i = 0; i < dt.Rows.Count; i++)
{
if ((i % rowLimit) == 0)
{
// add close tags for previous sheet of the same data table
if ((i / rowLimit) > sheetCount)
{
sw.Write( " </Table></Worksheet> ");
sheetCount = (i / rowLimit);
}
sw.Write( " <Worksheet ss:Name=\" " +
replaceXmlChar(dt.TableName) +
(((i / rowLimit) == 0) ? "" :
Convert.ToString(i / rowLimit)) + " \"><Table> ");
// write column name row
sw.Write( " <Row> ");
foreach (DataColumn dc in dt.Columns)
sw.Write(
string.Format(
" <Cell ss:StyleID=\"BoldColumn\">

<Data ss:Type=\ " String\">{0}</Data></Cell> ",
replaceXmlChar(dc.ColumnName)));
sw.Write( " </Row>\r\n ");
}
sw.Write( " <Row>\r\n ");
foreach (DataColumn dc in dt.Columns)
sw.Write(
string.Format(
" <Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">

{ 0}</Data></Cell> " ,
replaceXmlChar
(dt.Rows[i][dc.ColumnName].ToString())));
sw.Write( " </Row>\r\n ");
}
sw.Write( " </Table></Worksheet> ");
}
}

return sw.ToString();
}
public static string GetExcelXml(DataTable dtInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
var worksheets = getWorksheets(ds);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}

public static string GetExcelXml(DataSet dsInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var worksheets = getWorksheets(dsInput);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}

public static void ToExcel
(DataSet dsInput, string filename, HttpResponse response)
{
var excelXml = GetExcelXml(dsInput, filename);
response.Clear();
response.AppendHeader( " Content-Type ", " application/vnd.ms-excel ");
response.AppendHeader
( " Content-disposition ", " attachment; filename= " + filename);
response.Write(excelXml);
response.Flush();
response.End();
}

public static void ToExcel
(DataTable dtInput, string filename, HttpResponse response)
{
var ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
ToExcel(ds, filename, response);
}
}