做WinForm開發(fā)離不開一些基本的控件，作為數(shù)據(jù)列表顯示控件中，其中最為重要的要數(shù) DataGridView，以前用的是一些第三方控件，提供了比較靈活和方便的功能，比如：根據(jù)所見即所得導(dǎo)出數(shù)據(jù)到Excel 或者 文本，沒辦法自力更生才是生存之道。

　　DataGridView的數(shù)據(jù)導(dǎo)出功能在網(wǎng)絡(luò)上搜索后，有一些同仁實(shí)現(xiàn)過，但有些是需要依賴Excel ,這種方式不但耦合性強(qiáng)，性能也差，為了一勞永逸的解決這個問題，特改寫了代碼，在此提供給各位同仁，歡迎斧正。

1、首先，看使用方法：

dgvProjectList.ExportToExcel();

或者

dgvProjectList.ExportToExcel("項(xiàng)目列表");

如果不使用數(shù)據(jù)導(dǎo)出功能，不會對現(xiàn)有 DateGridView控件產(chǎn)生任何負(fù)作用，也不占用內(nèi)存；

2、擴(kuò)展方法定義：

/// <summary>
/// 將表格數(shù)據(jù)導(dǎo)出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv)
{
ExportToExcel(dgv, "表格數(shù)據(jù)");
}
/// <summary>
/// 將表格數(shù)據(jù)導(dǎo)出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "表格數(shù)據(jù)文件(*.csv)|*.csv";
sfd.FileName = string.Format("{0}.csv", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.CSV, fileName, ExportHelper.ApplicationType.WindowsForm);
 

}
}
/// <summary>
/// 將表格數(shù)據(jù)導(dǎo)出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv)
{
ExportToText(dgv, "表格數(shù)據(jù)");
}

/// <summary>
/// 將表格數(shù)據(jù)導(dǎo)出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "文本文件(*.txt)|*.txt";
sfd.FileName = string.Format("{0}.txt", fileName);
if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
ExportHelper.ExportDetails(dgv.GetDataTable(), ExportHelper.ExportFormat.TXT, fileName, ExportHelper.ApplicationType.WindowsForm);
}
}

3、導(dǎo)出類定義：

//導(dǎo)出輔助類

public class ExportHelper
{
/// <summary>
/// Export format enumeration
/// </summary>
public enum ExportFormat : int
{
/// <summary>
/// CSV
/// </summary>
CSV,
/// <summary>
/// DOC
/// </summary>
DOC,
/// <summary>
/// TXT
/// </summary>
TXT
};

/// <summary>
/// 應(yīng)用程序類型
/// </summary>
public enum ApplicationType : int
{
WindowsForm,
Web
}

/// <summary>
/// 導(dǎo)出SmartGridView的數(shù)據(jù)源的數(shù)據(jù)為Excel
/// </summary>
// <param name="dt">數(shù)據(jù)源</param>
/// <param name="fileName">文件名</param>
/// <param name="ApplicationType">應(yīng)用宿主類型</param>
public static void ExportDetails(DataTable dt, string fileName, ApplicationType ApplicationType)
{
ExportDetails(dt, ExportFormat.CSV, fileName, ApplicationType);
}

#region ExportDetails OverLoad : Type#1

// Function : ExportDetails
// Arguments : DetailsTable, FormatType, FileName
// Purpose : To get all the column headers in the datatable and
// exorts in CSV / Excel format with all columns

public static void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
{
try
{
if (DetailsTable.Rows.Count == 0)
throw new Exception("There are no details to export.");

// Create Dataset
DataSet dsExport = new DataSet("Export");
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = "Values";
dsExport.Tables.Add(dtExport);

// Getting Field Names
string[] sHeaders = new string[dtExport.Columns.Count];
string[] sFileds = new string[dtExport.Columns.Count];

for (int i = 0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
}

if (ApplicationType == ApplicationType.Web)
{
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (ApplicationType == ApplicationType.WindowsForm)
{
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}

#endregion // ExportDetails OverLoad : Type#1

#region ExportDetails OverLoad : Type#2

// Function : ExportDetails
// Arguments : DetailsTable, ColumnList, FormatType, FileName
// Purpose : To get the specified column headers in the datatable and
// exorts in CSV / Excel format with specified columns

public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName,
ApplicationType ApplicationType)
{
try
{
if (DetailsTable.Rows.Count == 0)
throw new Exception("There are no details to export");

// Create Dataset
DataSet dsExport = new DataSet("Export");
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = "Values";
dsExport.Tables.Add(dtExport);

if (ColumnList.Length > dtExport.Columns.Count)
throw new Exception("ExportColumn List should not exceed Total Columns");

// Getting Field Names
string[] sHeaders = new string[ColumnList.Length];
string[] sFileds = new string[ColumnList.Length];

for (int i = 0; i < ColumnList.Length; i++)
{
if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
throw new Exception("ExportColumn Number should not exceed Total Columns Range&quot;);

sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
}

if (ApplicationType == ApplicationType.Web)
{
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (ApplicationType == ApplicationType.WindowsForm)
{
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}

#endregion // ExportDetails OverLoad : Type#2

#region ExportDetails OverLoad : Type#3

// Function : ExportDetails
// Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
// Purpose : To get the specified column headers in the datatable and
// exorts in CSV / Excel format with specified columns and
// with specified headers

public static void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] sHeaders, ExportFormat FormatType,
string FileName, ApplicationType ApplicationType)
{
try
{
if (DetailsTable.Rows.Count == 0)
throw new Exception("There are no details to export");

// Create Dataset
DataSet dsExport = new DataSet("Export");
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = "Values";
dsExport.Tables.Add(dtExport);

if (ColumnList.Length != sHeaders.Length)
throw new Exception("ExportColumn List and Headers List should be of same length");
else if (ColumnList.Length > dtExport.Columns.Count || sHeaders.Length > dtExport.Columns.Count)
throw new Exception("ExportColumn List should not exceed Total Columns");

// Getting Field Names
string[] sFileds = new string[ColumnList.Length];

for (int i = 0; i < ColumnList.Length; i++)
{
if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
throw new Exception("ExportColumn Number should not exceed Total Columns Range");

sFileds[i] = ReplaceSpecialChars(dtExport.Columns[ColumnList[i]].ColumnName);
}

if (ApplicationType == ApplicationType.Web)
{
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (ApplicationType == ApplicationType.WindowsForm)
{
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}

/// <summary>
/// 導(dǎo)出SmartGridView的數(shù)據(jù)源的數(shù)據(jù)
/// </summary>
/// <param name="DetailsTable">數(shù)據(jù)源</param>
/// <param name="columnNameList">導(dǎo)出的列的列名數(shù)組</param>
/// <param name="sHeaders">導(dǎo)出的列標(biāo)題數(shù)組</param>
/// <param name="FormatType">導(dǎo)出文件的格式</param>
/// <param name="FileName">輸出文件名</param>
/// <param name="ApplicationType">應(yīng)用宿主類型</param>
public static void ExportDetails(DataTable DetailsTable, string[] columnNameList, string[] sHeaders,
ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
{
List<int> columnIndexList = new List<int>();
DataColumnCollection dcc = DetailsTable.Columns;

foreach (string s in columnNameList)
{
columnIndexList.Add(GetColumnIndexByColumnName(dcc, s));
}

ExportDetails(DetailsTable, columnIndexList.ToArray(), sHeaders, FormatType, FileName, ApplicationType);
}

#endregion // ExportDetails OverLoad : Type#3

#region ExportDetails OverLoad : Type#3

// Function : ExportDetails
// Arguments : DetailsTable, FormatType, FileName
// Purpose : To get all the column headers in the datatable and
// exorts in CSV / Excel format with all columns
public void ExportDetails(DataTableCollection DetailsTables, ExportFormat FormatType, string FileName, ApplicationType ApplicationType)
{
try
{
string NewFileName;

foreach (DataTable DetailsTable in DetailsTables)
{
if (DetailsTable.Rows.Count == 0)
throw new Exception("There are no details to export.");

NewFileName = FileName.Substring(0, FileName.LastIndexOf("."));
NewFileName += " - " + DetailsTable.TableName;
NewFileName += FileName.Substring(FileName.LastIndexOf("."));

// Create Dataset
DataSet dsExport = new DataSet("Export");
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = "Values";
dsExport.Tables.Add(dtExport);

// Getting Field Names
string[] sHeaders = new string[dtExport.Columns.Count];
 string[] sFileds = new string[dtExport.Columns.Count];

for (int i = 0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = ReplaceSpecialChars(dtExport.Columns[i].ColumnName);
}

if (ApplicationType == ApplicationType.Web)
{
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (ApplicationType == ApplicationType.WindowsForm)
{
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
}
catch (Exception Ex)
{
throw Ex;
}
}

#endregion //ExportDetails OverLoad : Type#4

#region Export_with_XSLT_Web

// Function : Export_with_XSLT_Web
// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
// Purpose : Exports dataset into CSV / Excel format
private static void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
// Appending Headers
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = String.Format("text/{0}", FormatType.ToString().ToLower());
HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.{1}", FileName, FormatType.ToString().ToLower()));
//HttpContext.Current.Response.ContentEncoding = encoding;
 

// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);

CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
stream.Seek(0, SeekOrigin.Begin);

XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslCompiledTransform xslTran = new XslCompiledTransform();
xslTran.Load(new XmlTextReader(stream));

System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw);

//Writeout the Content
HttpContext.Current.Response.Write(sw.ToString());
sw.Close();
writer.Close();
stream.Close();
HttpContext.Current.Response.End();
}
catch (ThreadAbortException Ex)
{
string ErrMsg = Ex.Message;
}
catch (Exception Ex)
{
throw Ex;
}
}

#endregion // Export_with_XSLT

#region Export_with_XSLT_Windows

// Function : Export_with_XSLT_Windows
// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
// Purpose : Exports dataset into CSV / Excel format
private static void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds,
ExportFormat FormatType, string FileName)
{

try
{
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
stream.Seek(0, SeekOrigin.Begin);

XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslCompiledTransform xslTran = new XslCompiledTransform();
xslTran.Load(new XmlTextReader(stream));

System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw);

//Writeout the Content
StreamWriter strwriter = new StreamWriter(FileName, false, Encoding.Default);
strwriter.WriteLine(sw.ToString());
strwriter.Close();
sw.Close();
writer.Close();
stream.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}

#endregion // Export_with_XSLT

#region CreateStylesheet

// Function : WriteStylesheet
// Arguments : writer, sHeaders, sFileds, FormatType
// Purpose : Creates XSLT file to apply on dataset's XML file
private static void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
{
try
379 {
// xsl:stylesheet
string ns = "//www.w3.org/1999/XSL/Transform";
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("xsl", "stylesheet", ns);
writer.WriteAttributeString("version", "1.0");
writer.WriteStartElement("xsl:output");
writer.WriteAttributeString("method", "text");
writer.WriteAttributeString("version", "4.0");
writer.WriteEndElement();

// xsl-template
writer.WriteStartElement("xsl:template");
writer.WriteAttributeString("match", "/");

// xsl:value-of for headers
for (int i = 0; i < sHeaders.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
}

// xsl:for-each
writer.WriteStartElement("xsl:for-each");
writer.WriteAttributeString("select", "Export/Values");
writer.WriteString("\r\n");

// xsl:value-of for data fields
for (int i = 0; i < sFileds.Length; i++)
{
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", sFileds[i]);
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
}

writer.WriteEndElement(); // xsl:for-each
writer.WriteEndElement(); // xsl-template
writer.WriteEndElement(); // xsl:stylesheet
writer.WriteEndDocument();
}
catch (Exception Ex)
{
throw Ex;
}
}

public static string ReplaceSpecialChars(string input)
{
// space -> _x0020_
// % -> _x0025_
// # -> _x0023_
// & -> _x0026_
// / -> _x002F_

input = input.Replace(" ", "_x0020_")
.Replace("%", "_x0025_")
.Replace("#", "_x0023_")
.Replace("&", "_x0026_")
.Replace("/", "_x002F_");

return input;
}
/// <summary>
/// 根據(jù)數(shù)據(jù)列的列名取數(shù)據(jù)列的列索引
/// </summary>
/// <param name="dcc">數(shù)據(jù)列集合</param>
/// <param name="columnName">數(shù)據(jù)列的列名</param>
/// <returns></returns>
public static int GetColumnIndexByColumnName(DataColumnCollection dcc, string columnName)
{
int result = -1;

for (int i = 0; i < dcc.Count; i++)
{
{
break;
}
}

return result;
}
#endregion // WriteStylesheet

}