/// <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();
}
