/// <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; } }
private void btnExportData_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtDbTableName.Text)) { MessageForm.ShowWarning("Table Name不能为空"); return; } SaveFileDialog dlg = new SaveFileDialog(); dlg.RestoreDirectory = true; dlg.Filter = "Excel Xml(*.xml)|*.xml"; //saveFileDialog1.Title = "保存"; if (dlg.ShowDialog() == DialogResult.OK) { System.IO.StreamWriter sw = new System.IO.StreamWriter(dlg.FileName, false, Encoding.UTF8); ExcelXmlHelper.WriteExcelXmlHead(sw); string[] ss = txtDbTableName.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); foreach (string s in ss) { ExcelXmlHelper.WriteExcelXml(Feng.Data.DbHelper.Instance.ExecuteDataTable("SELECT * FROM " + s), sw, s); } ExcelXmlHelper.WriteExcelXmlTail(sw); sw.Close(); } }
/// <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(); }