ASP.NET导出Excel

方法一：导出CSV格式

优点：各种Excel版本下都不会提示版本兼容问题

缺点：不能设置每列的宽度，另外有中文的情况下会乱码，试了改变输出的编码，还是不管用。

public static StringBuilder AppendCSVFields(StringBuilder argSource, string argFields)
{
    return argSource.Append(argFields.Replace(",", " ").Trim()).Append(",");
}

public static void DownloadCSVFile(HttpResponse argResp, StringBuilder argFileStream, string strFileName)
{
    string strResHeader = "attachment; filename=" + Guid.NewGuid().ToString() + ".csv";
    if (!string.IsNullOrEmpty(strFileName))
    {
        strResHeader = "inline; filename=" + strFileName;
    }
    argResp.AppendHeader("Content-Disposition", strResHeader);
    argResp.ContentType = "application/ms-excel";
    argResp.ContentEncoding = Encoding.GetEncoding("GB2312");
    argResp.Write(argFileStream);
    argResp.End();
}

使用示例：

StringWriter swCSV = new StringWriter();
swCSV.WriteLine("MsgType,MsgNo,ShortText,Status,Priority,Processor,Owner,EnteredDate");
foreach (IncidentInfo item in list)
{
    StringBuilder sbText = new StringBuilder();
    sbText = FileHelper.AppendCSVFields(sbText, item.MsgType.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.MsgNo.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.ShortText.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.Status.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.Priority.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.Processor.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.Owner.ToString());
    sbText = FileHelper.AppendCSVFields(sbText, item.EnteredDate.ToString(Consts.DATETIMEFORMAT));

    sbText.Remove(sbText.Length - 1, 1);

    swCSV.WriteLine(sbText.ToString());
}

FileHelper.DownloadCSVFile(Response, swCSV.GetStringBuilder(), buildName + "_MsgReport.csv");
swCSV.Close();

 

方法二：导出xls格式

优点：可以灵活设置各列的宽度

缺点：为了保证客户端只安装了office2003的用户也能打开，设置为xls格式，在高版本的情况下，打开Excel会出现个提示框，降低了用户体验性

public static void DownloadXLSFile(HttpResponse argResp, Dictionary<string, double> cellHeaderList, List<List<string>> cellValueList, string strFileName)
{
    string _xmlStr = @"<?xml version='1.0'?>
  <?mso-application progid='Excel.Sheet'?>
  <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
  xmlns:o='urn:schemas-microsoft-com:office:office'
  xmlns:x='urn:schemas-microsoft-com:office:excel'
  xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
  xmlns:html='http://www.w3.org/TR/REC-html40'>
  <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
  <Author>aa</Author>
  <LastAuthor>aa</LastAuthor>
  <Created>2007-12-07T06:54:38Z</Created>
  <Company>WZKJ</Company>
  <Version>11.5606</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
  <WindowHeight>5970</WindowHeight>
  <WindowWidth>7395</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
  <Style ss:ID='Default' ss:Name='Normal'>
  <Alignment ss:Vertical='Center'/>
  <Borders/>
  <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
  <Interior/>
  <NumberFormat/>
  <Protection/>
  </Style>
  <Style ss:ID='s21'>
  <Alignment ss:Vertical='Center' ss:WrapText='1'/>
  </Style>
  </Styles>
  <Worksheet ss:Name='Sheet1'>
  $Table$
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
  <Selected/>
  <Panes>
  <Pane>
  <Number>3</Number>
  <ActiveRow>2</ActiveRow>
  <ActiveCol>3</ActiveCol>
  </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  </Worksheet>
  </Workbook>";

    StringBuilder sb = new StringBuilder();
    int i = 1;
    int recordCount = 1;
    foreach (var item in cellHeaderList)
    {
        sb.Append(string.Format("<Column ss:Index='{0}' ss:AutoFitWidth='0' ss:Width='{1}'/>", i, item.Value));
        i++;
    }

    sb.Append("<Row>");
    foreach (var item in cellHeaderList)
    {
        sb.Append(string.Format("<Cell><Data ss:Type='String'>{0}</Data></Cell>", item.Key));
    }
    sb.Append("</Row>");

    foreach (var item in cellValueList)
    {
        recordCount = recordCount + 1;
        sb.Append("<Row ss:StyleID='s21'>");
        foreach (var cellValue in item)
        {
            sb.Append(string.Format("<Cell><Data ss:Type='String'>{0}</Data></Cell>", cellValue));
        }
        sb.Append("</Row>");
    }

    string topString = "<Table ss:ExpandedColumnCount='11' ss:ExpandedRowCount='" + recordCount + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='54'>";
    string bottomStr = "</Table>";
    string xxcelXmlStr = _xmlStr.Replace("$Table$", topString + sb.ToString() + bottomStr);

    argResp.Clear();
    argResp.Buffer = true;
    argResp.AddHeader("Content-Disposition", "attachment;filename=" + strFileName + ".xls");
    argResp.ContentEncoding = Encoding.GetEncoding("UTF-8");
    argResp.ContentType = "application/ms-excel";
    argResp.Output.Write(xxcelXmlStr);
    argResp.Flush();
    argResp.End();
}

使用示例：

Dictionary<string, double> cellHeaderList = new Dictionary<string, double>();
cellHeaderList.Add("学校编号", 100);
cellHeaderList.Add("学校名称", 200);
cellHeaderList.Add("学校类型", 120);
cellHeaderList.Add("学校区域", 60);
cellHeaderList.Add("学校地址", 200);
cellHeaderList.Add("学校邮编", 90);
cellHeaderList.Add("征书负责人", 90);
cellHeaderList.Add("负责人邮件", 120);
cellHeaderList.Add("负责人电话", 120);
List<List<string>> cellValueList = new List<List<string>>();
foreach (SchoolEntity item in list)
{
    StringBuilder sbText = new StringBuilder();
    List<string> rowList = new List<string>();
    rowList.Add(item.Code);
    rowList.Add(item.Name);
    rowList.Add(item.Type);
    rowList.Add(EnumAttributeHelper<SchoolRegionEnum>.GetDisplayDescriptionByEnum(item.Region));
    rowList.Add(item.Address);
    rowList.Add(item.PostCode);
    rowList.Add(item.Contact);
    rowList.Add(item.Email);
    rowList.Add(item.Phone);
    cellValueList.Add(rowList);
}

FileHelper.DownloadXLSFile(Response, cellHeaderList, cellValueList, "学校列表");