/// <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>
/// 读入Excel文件。一个Sheet一个DataTable。
/// </summary>
/// <param name="fileName"></param>
/// <param name="firstRowasColumnName">第一行是不是座位DataTable的ColumnName</param>
/// <returns></returns>
public static IList <DataTable> ReadExcel(string fileName, bool firstRowasColumnName)
{
int k = fileName.LastIndexOf('.');
string excelType = fileName.Substring(k);
if (excelType != null)
{
if (excelType == ".xml")
{
return(ExcelXmlHelper.ReadExcelXml(fileName, firstRowasColumnName));
}
else if (excelType == ".xlsx")
{
return(ReadExcel(fileName, ExcelFileType.Xlsx, firstRowasColumnName));
}
else if (excelType == ".xls")
{
return(ReadExcel(fileName, ExcelFileType.Xls, firstRowasColumnName));
}
else
{
throw new NotSupportedException("Not supported file format!");
}
}
throw new NotSupportedException("Not supported file format!");
}
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>
/// 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();
}
/// <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();
}