使用openXML 不用插件导出excel
- 2019 年 10 月 10 日
- 笔记
注释很详细，不做解释了，有疑问可以提问
using System.IO; using System.Text; namespace iLIS.Common { /// <summary> /// 生成Excel文档内容 /// 存入工作流 /// </summary> public class ExcelDocumentx { private readonly StreamWriter _streamWriter; public ExcelDocumentx(Stream stream) { _streamWriter = new StreamWriter(stream, Encoding.UTF8); } /// <summary> /// 写入Excel文件头 /// </summary> public void Begin() { const string excelHeader = @"<?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>Hitek</Author> <Company>HitekSoft(C) Ltd.,</Company> <Version>12.00</Version> </DocumentProperties> <Styles> <Style ss:ID='sH'> <Alignment ss:Vertical='Center' ss:WrapText='1'/> <Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> </Borders> <Font ss:FontName='宋体' x:CharSet='134' ss:Size='11' ss:Color='#000000' ss:Bold='1'/> <Interior ss:Color='#F2F2F2' ss:Pattern='Solid'/> <NumberFormat/> <Protection/> </Style> <Style ss:ID='sBD'> <Alignment ss:Vertical='Center' ss:WrapText='1'/> <Borders> <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> </Borders> </Style> </Styles>"; _streamWriter.WriteLine(excelHeader); } /// <summary> /// 添加工作表 /// </summary> /// <param name="name">表单名称</param> /// <param name="defaultRowHeight">默认行高</param> /// <param name="defaultColumnWidth">默认列宽</param> public void BeginSheet(string name, double defaultRowHeight = 0, double defaultColumnWidth = 0) { _streamWriter.WriteLine("<Worksheet ss:Name='" + name + "'>"); _streamWriter.Write("<Table"); //默认行高 if (defaultRowHeight > 0.0001) _streamWriter.Write(string.Format(" ss:DefaultRowHeight='{0}'", defaultRowHeight)); //默认列宽 if (defaultColumnWidth > 0.0001) _streamWriter.Write(string.Format(" ss:ss:DefaultColumnWidth='{0}'", defaultColumnWidth)); _streamWriter.WriteLine(">"); } /// <summary> /// 添加标题行 /// </summary> /// <param name="colNames">标题行的名称</param> /// <param name="colWidths">标题行的列宽</param> public void AddHeaderRow(string[] colNames, double[] colWidths = null) { //列宽 if (colWidths != null && colWidths.Length > 0) { for (int i = 0; i < colWidths.Length; i++) { if (colWidths[i] > 0.0001) _streamWriter.WriteLine(string.Format("<Column ss:Index='{0}' ss:AutoFitWidth='0' ss:Width='{1}'/>", i + 1, colWidths[i])); } } AddRow(colNames, "sH"); } /// <summary> /// 添加一行 /// </summary> /// <param name="styleName">样式名称</param> /// <param name="vals"></param> public void AddRow(object[] vals, string styleName = null) { if (string.IsNullOrEmpty(styleName)) styleName = "sBD"; _streamWriter.WriteLine("<Row>"); foreach (var val in vals) { string strval = val == null ? "" : val.ToString() .Replace("<", "<") .Replace(">", ">"); _streamWriter.WriteLine("<Cell ss:StyleID='{0}'><Data ss:Type='String'>{1}</Data></Cell>n", styleName, strval); } _streamWriter.WriteLine("</Row>"); } /// <summary> /// 完成表单 /// </summary> public void EndSheet() { _streamWriter.WriteLine("</Table>"); _streamWriter.WriteLine("</Worksheet>"); } /// <summary> /// 写入Excel文件结束 完成导出 /// </summary> public void End() { _streamWriter.WriteLine("</Workbook>"); _streamWriter.Close(); } } }
