C# winform 從datatable導出到excel的XML格式
原來是參考這裡:
http://stackoverflow.com/questions/2401067/export-gridview-into-excel-in-windowsformsapplication
應用之後,發現一些問題,有一些BUG,以致導出不成功,或是一些日期或數字格式不對.
改後的代碼如下:
1.類代碼:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
namespace ExcelXml
{
public class ExcelWriter : IDisposable
{
private XmlWriter _writer;
public enum CellStyle { General, Number, Currency, DateTime, ShortDate };
public void WriteStartDocument()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
_writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyles();
}
public void WriteEndDocument()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
}
private void WriteExcelStyleElement(CellStyle style)
{
_writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteEndElement();
}
private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
{
_writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
_writer.WriteEndElement();
_writer.WriteEndElement();
}
private void WriteExcelStyles()
{
_writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");
WriteExcelStyleElement(CellStyle.General);
WriteExcelStyleElement(CellStyle.Number, "General Number");
WriteExcelStyleElement(CellStyle.DateTime, "General Date");
WriteExcelStyleElement(CellStyle.Currency, "Currency");
WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");
_writer.WriteEndElement();
}
public void WriteStartWorksheet(string name)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
_writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
}
public void WriteEndWorksheet()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public ExcelWriter(string outputFileName)
{
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
_writer = XmlWriter.Create(outputFileName, settings);
}
public void Close()
{
if (_writer == null) throw new NotSupportedException("Already closed.");
_writer.Close();
_writer = null;
}
public void WriteExcelColumnDefinition(int columnWidth)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteValue(columnWidth);
_writer.WriteEndAttribute();
_writer.WriteEndElement();
}
public void WriteExcelUnstyledCell(string value)
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
_writer.WriteValue(value);
_writer.WriteEndElement();
_writer.WriteEndElement();
}
public void WriteStartRow()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
}
public void WriteEndRow()
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteEndElement();
}
public void WriteExcelStyledCell(object value, CellStyle style)
{
try
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
_writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
_writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
_writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
switch (style)
{
case CellStyle.General:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
break;
case CellStyle.Number:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number"); // 與原例子不同,自己加的,未驗證
break; // 與原例子不同,自己加的,未驗證
case CellStyle.Currency:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
break;
case CellStyle.ShortDate:
case CellStyle.DateTime:
_writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
break;
}
_writer.WriteValue(value);
// tag += String.Format("{1}\"><ss:Data ss:Type=\"DateTime\">{0:yyyy\\-MM\\-dd\\THH\\:mm\\:ss\\.fff}</ss:Data>", value,
_writer.WriteEndElement();
_writer.WriteEndElement();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
}
public void WriteExcelAutoStyledCell(object value)
{
try
{
if (_writer == null) throw new NotSupportedException("Cannot write after closing.");
//write the <ss:Cell> and <ss:Data> tags for something
if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
{
WriteExcelStyledCell(value, CellStyle.Number);
}
else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
{
//WriteExcelStyledCell(value, CellStyle.Currency); //原例子
WriteExcelStyledCell(value, CellStyle.Number); // 與原例子不同,自己改的,未驗證
}
else if (value is DateTime)
{
//check if there's no time information and use the appropriate style
WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
}
else
{
WriteExcelStyledCell(value, CellStyle.General);
}
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
}
#region IDisposable Members
public void Dispose()
{
if (_writer == null)
return;
_writer.Close();
_writer = null;
}
#endregion
}
}
2.應用代碼:
/// <summary>
/// 生成EXCEL報表
/// </summary>
///
public static void ExcelExport(DataTable data, String fileName, bool openAfter)
{
//export a DataTable to Excel
DialogResult retry = DialogResult.Retry;
object value = null;
DateTime d = new DateTime();
int i = 0;
bool isdate = false;
string field_name = "";
var formats = new[] { "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss", "yyyy-M-d", "yyyy-M-d HH:mm:ss", };
//嚴格匹配日期格式.
//string s = "2012-4-5-6";
//if (DateTime.TryParseExact(s, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
// MessageBox.Show(d.ToString("yyyy-MM-dd HH:mm:ss"));
//else MessageBox.Show("Error date!");
//string da = "1:12";
////(([0-1][0-9])|([2][0-3])):([0-5][0-9]) (([0-1][0-9])|([2][0-3])):([0-5][0-9])
//System.Text.RegularExpressions.Regex dateRegex = new System.Text.RegularExpressions.Regex(@"^(([0-1]{0,1}[0-9])|([2][0-3])):([0-5][0-9])");
////System.Text.RegularExpressions.Regex dateRegex = new System.Text.RegularExpressions.Regex(@"^(([0-1][0-9])|([2][0-3])):([0-5][0-9])");
//if (dateRegex.IsMatch(da))
//{
// MessageBox.Show("It's Time!");
//}
while (retry == DialogResult.Retry)
{
try
{
using (ExcelWriter writer = new ExcelWriter(fileName))
{
writer.WriteStartDocument();
// Write the worksheet contents
writer.WriteStartWorksheet("Sheet1");
//Write header row
writer.WriteStartRow();
foreach (DataColumn col in data.Columns)
writer.WriteExcelUnstyledCell(col.Caption);
writer.WriteEndRow();
//write data
foreach (DataRow row in data.Rows)
{
i = 0;
writer.WriteStartRow();
foreach (object o in row.ItemArray)
{
field_name = row.Table.Columns[i].ToString();
if (field_name.Contains("date"))
isdate = true;
else
isdate = false;
i++;
Type b = o.GetType();
if (b.Name == "DBNull")
value = "";
else if (b.Name == "String" && o.ToString().Contains("\0"))
value = o.ToString().Replace("\0","");
//else if (DateTime.TryParse(o.ToString(), out d))
else if ( isdate && DateTime.TryParseExact(o.ToString(), formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
{
if (d.Year >= 1900)
{
if (o.ToString().Length > 10)
value = d;
else if (o.ToString().Length < 10 && o.ToString().Contains(":"))
//value = d.TimeOfDay; //导出到EXCEL的格式有问题
value = o;
else if (isdate)
value = d.Date;
else
value = o;
}
else
value = o;
}
else
value = o;
string c = o.ToString();
writer.WriteExcelAutoStyledCell(value);
}
writer.WriteEndRow();
}
// Close up the document
writer.WriteEndWorksheet();
writer.WriteEndDocument();
writer.Close();
if (openAfter)
OpenFile(fileName);
retry = DialogResult.Cancel;
}
}
catch (Exception myException)
{
retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
}
}
}
關於日期格式,按字段名中,包含”"date”字符串的,才轉換.
時間沒有處理.
