using System.IO; using System.Text; namespace iLIS.Common { /// /// 生成Excel文檔內容 /// 存入工作流 /// public class ExcelDocumentx { private readonly StreamWri... ...
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(); } } }