MaoBisheng

Asp.Net(C#) & SQL & Oracle

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

方法一:导出CSV格式

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

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

   1: public static StringBuilder AppendCSVFields(StringBuilder argSource, string argFields)
   2: {
   3:     return argSource.Append(argFields.Replace(",", " ").Trim()).Append(",");
   4: }
   5:  
   6: public static void DownloadCSVFile(HttpResponse argResp, StringBuilder argFileStream, string strFileName)
   7: {
   8:     string strResHeader = "attachment; filename=" + Guid.NewGuid().ToString() + ".csv";
   9:     if (!string.IsNullOrEmpty(strFileName))
  10:     {
  11:         strResHeader = "inline; filename=" + strFileName;
  12:     }
  13:     argResp.AppendHeader("Content-Disposition", strResHeader);
  14:     argResp.ContentType = "application/ms-excel";
  15:     argResp.ContentEncoding = Encoding.GetEncoding("GB2312");
  16:     argResp.Write(argFileStream);
  17:     argResp.End();
  18: }

使用示例:

   1: StringWriter swCSV = new StringWriter();
   2: swCSV.WriteLine("MsgType,MsgNo,ShortText,Status,Priority,Processor,Owner,EnteredDate");
   3: foreach (IncidentInfo item in list)
   4: {
   5:     StringBuilder sbText = new StringBuilder();
   6:     sbText = FileHelper.AppendCSVFields(sbText, item.MsgType.ToString());
   7:     sbText = FileHelper.AppendCSVFields(sbText, item.MsgNo.ToString());
   8:     sbText = FileHelper.AppendCSVFields(sbText, item.ShortText.ToString());
   9:     sbText = FileHelper.AppendCSVFields(sbText, item.Status.ToString());
  10:     sbText = FileHelper.AppendCSVFields(sbText, item.Priority.ToString());
  11:     sbText = FileHelper.AppendCSVFields(sbText, item.Processor.ToString());
  12:     sbText = FileHelper.AppendCSVFields(sbText, item.Owner.ToString());
  13:     sbText = FileHelper.AppendCSVFields(sbText, item.EnteredDate.ToString(Consts.DATETIMEFORMAT));
  14:  
  15:     sbText.Remove(sbText.Length - 1, 1);
  16:  
  17:     swCSV.WriteLine(sbText.ToString());
  18: }
  19:  
  20: FileHelper.DownloadCSVFile(Response, swCSV.GetStringBuilder(), buildName + "_MsgReport.csv");
  21: swCSV.Close();

 

方法二:导出xls格式

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

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

   1: public static void DownloadXLSFile(HttpResponse argResp, Dictionary<string, double> cellHeaderList, List<List<string>> cellValueList, string strFileName)
   2: {
   3:     string _xmlStr = @"<?xml version='1.0'?>
   4:   <?mso-application progid='Excel.Sheet'?>
   5:   <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
   6:   xmlns:o='urn:schemas-microsoft-com:office:office'
   7:   xmlns:x='urn:schemas-microsoft-com:office:excel'
   8:   xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
   9:   xmlns:html='http://www.w3.org/TR/REC-html40'>
  10:   <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
  11:   <Author>aa</Author>
  12:   <LastAuthor>aa</LastAuthor>
  13:   <Created>2007-12-07T06:54:38Z</Created>
  14:   <Company>WZKJ</Company>
  15:   <Version>11.5606</Version>
  16:   </DocumentProperties>
  17:   <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
  18:   <WindowHeight>5970</WindowHeight>
  19:   <WindowWidth>7395</WindowWidth>
  20:   <WindowTopX>480</WindowTopX>
  21:   <WindowTopY>60</WindowTopY>
  22:   <ProtectStructure>False</ProtectStructure>
  23:   <ProtectWindows>False</ProtectWindows>
  24:   </ExcelWorkbook>
  25:   <Styles>
  26:   <Style ss:ID='Default' ss:Name='Normal'>
  27:   <Alignment ss:Vertical='Center'/>
  28:   <Borders/>
  29:   <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
  30:   <Interior/>
  31:   <NumberFormat/>
  32:   <Protection/>
  33:   </Style>
  34:   <Style ss:ID='s21'>
  35:   <Alignment ss:Vertical='Center' ss:WrapText='1'/>
  36:   </Style>
  37:   </Styles>
  38:   <Worksheet ss:Name='Sheet1'>
  39:   $Table$
  40:   <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
  41:   <Selected/>
  42:   <Panes>
  43:   <Pane>
  44:   <Number>3</Number>
  45:   <ActiveRow>2</ActiveRow>
  46:   <ActiveCol>3</ActiveCol>
  47:   </Pane>
  48:   </Panes>
  49:   <ProtectObjects>False</ProtectObjects>
  50:   <ProtectScenarios>False</ProtectScenarios>
  51:   </WorksheetOptions>
  52:   </Worksheet>    
  53:   </Workbook>";
  54:  
  55:     StringBuilder sb = new StringBuilder();
  56:     int i = 1;
  57:     int recordCount = 1;
  58:     foreach (var item in cellHeaderList)
  59:     {
  60:         sb.Append(string.Format("<Column ss:Index='{0}' ss:AutoFitWidth='0' ss:Width='{1}'/>", i, item.Value));
  61:         i++;
  62:     }
  63:  
  64:     sb.Append("<Row>");
  65:     foreach (var item in cellHeaderList)
  66:     {
  67:         sb.Append(string.Format("<Cell><Data ss:Type='String'>{0}</Data></Cell>", item.Key));
  68:     }
  69:     sb.Append("</Row>");
  70:  
  71:     foreach (var item in cellValueList)
  72:     {
  73:         recordCount = recordCount + 1;
  74:         sb.Append("<Row ss:StyleID='s21'>");
  75:         foreach (var cellValue in item)
  76:         {
  77:             sb.Append(string.Format("<Cell><Data ss:Type='String'>{0}</Data></Cell>", cellValue));
  78:         }
  79:         sb.Append("</Row>");
  80:     }
  81:  
  82:     string topString = "<Table ss:ExpandedColumnCount='11' ss:ExpandedRowCount='" + recordCount + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='54'>";
  83:     string bottomStr = "</Table>";
  84:     string xxcelXmlStr = _xmlStr.Replace("$Table$", topString + sb.ToString() + bottomStr);
  85:  
  86:     argResp.Clear();
  87:     argResp.Buffer = true;
  88:     argResp.AddHeader("Content-Disposition", "attachment;filename=" + strFileName + ".xls");
  89:     argResp.ContentEncoding = Encoding.GetEncoding("UTF-8");
  90:     argResp.ContentType = "application/ms-excel";
  91:     argResp.Output.Write(xxcelXmlStr);
  92:     argResp.Flush();
  93:     argResp.End();
  94: }

使用示例:

   1: Dictionary<string, double> cellHeaderList = new Dictionary<string, double>();
   2: cellHeaderList.Add("学校编号", 100);
   3: cellHeaderList.Add("学校名称", 200);
   4: cellHeaderList.Add("学校类型", 120);
   5: cellHeaderList.Add("学校区域", 60);
   6: cellHeaderList.Add("学校地址", 200);
   7: cellHeaderList.Add("学校邮编", 90);
   8: cellHeaderList.Add("征书负责人", 90);
   9: cellHeaderList.Add("负责人邮件", 120);
  10: cellHeaderList.Add("负责人电话", 120);
  11: List<List<string>> cellValueList = new List<List<string>>();
  12: foreach (SchoolEntity item in list)
  13: {
  14:     StringBuilder sbText = new StringBuilder();
  15:     List<string> rowList = new List<string>();
  16:     rowList.Add(item.Code);
  17:     rowList.Add(item.Name);
  18:     rowList.Add(item.Type);
  19:     rowList.Add(EnumAttributeHelper<SchoolRegionEnum>.GetDisplayDescriptionByEnum(item.Region));
  20:     rowList.Add(item.Address);
  21:     rowList.Add(item.PostCode);
  22:     rowList.Add(item.Contact);
  23:     rowList.Add(item.Email);
  24:     rowList.Add(item.Phone);
  25:     cellValueList.Add(rowList);
  26: }
  27:  
  28: FileHelper.DownloadXLSFile(Response, cellHeaderList, cellValueList, "学校列表");
posted on 2013-04-06 16:34  MaoBisheng  阅读(834)  评论(3编辑  收藏  举报