C# winform datatable export to excel xml format

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”字符串的,才轉換.

時間沒有處理.