/// <summary> /// 把DataTables写入Excel(xml格式和xls格式) /// 每个DataTable一个Sheet,Sheet.Name = DataTable.TableName。 /// 类型要一致(例如DataTable是DateTime类型,Excel中列也要是时间类型) /// </summary> /// <param name="dts"></param> /// <param name="fileName"></param> /// <param name="excelType"></param> /// <param name="firstRowasColumnName">第一列是否Column名字。</param> public static void WriteExcel(IList <DataTable> dts, string fileName, ExcelFileType excelType, bool firstRowasColumnName) { switch (excelType) { case ExcelFileType.Xml: using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName)) { ExcelXmlHelper.WriteExcelXmlHead(sw); foreach (DataTable dt in dts) { ExcelXmlHelper.WriteExcelXmlTableHead(sw, dt.TableName); ExcelXmlHelper.WriteExcelXmlRows(dt, sw, firstRowasColumnName); ExcelXmlHelper.WriteExcelXmlTableTail(sw); } ExcelXmlHelper.WriteExcelXmlTail(sw); } break; case ExcelFileType.Xls: case ExcelFileType.Xlsx: foreach (DataTable dt in dts) { Excel.OleHelper.WriteExcel(fileName, dt, excelType, firstRowasColumnName); } break; } }
/// <summary> /// Close(Write all buffer) /// </summary> public void Close() { System.IO.StreamWriter sw = new System.IO.StreamWriter(m_fileName); ExcelXmlHelper.WriteExcelXmlHead(sw); sw.Flush(); foreach (KeyValuePair <string, StreamWriter> kvp in m_streams) { ExcelXmlHelper.WriteExcelXmlTableHead(sw, kvp.Key); sw.Flush(); //byte[] b = new byte[kvp.Value.Length]; //kvp.Value.Read(b, 0, b.Length); (kvp.Value.BaseStream as MemoryStream).WriteTo(sw.BaseStream); sw.Flush(); kvp.Value.Close(); ExcelXmlHelper.WriteExcelXmlTableTail(sw); sw.Flush(); } ExcelXmlHelper.WriteExcelXmlTail(sw); sw.Close(); }