/// <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> /// Write /// </summary> /// <param name="dt"></param> /// <param name="sheetName"></param> public void WriteExcelXml(DataTable dt, string sheetName) { bool first = false; if (!m_streams.ContainsKey(sheetName)) { m_streams[sheetName] = new StreamWriter(new MemoryStream()); first = true; } ExcelXmlHelper.WriteExcelXmlRows(dt, m_streams[sheetName], first); m_streams[sheetName].Flush(); }