WinForm控件设计：DataGridView导出数据
做WinForm开发离不开一些基本的控件，作为数据列表显示控件中，其中最为重要的要数 DataGridView，以前用的是一些第三方控件，提供了比较灵活和方便的功能，比如：根据所见即所得导出数据到Excel 或者 文本，没办法自力更生才是生存之道。
DataGridView的数据导出功能在网络上搜索后，有一些同仁实现过，但有些是需要依赖Excel ,这种方式不但耦合性强，性能也差，为了一劳永逸的解决这个问题，特改写了代码，在此提供给各位同仁，欢迎斧正。
1、首先，看使用方法：
dgvProjectList.ExportToExcel();
或者
dgvProjectList.ExportToExcel("项目列表");
如果不使用数据导出功能，不会对现有 DateGridView控件产生任何负作用，也不占用内存；
2、扩展方法定义：
代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> /// <summary>
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv)
{
ExportToExcel(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到csv表格文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToExcel(this DataGridView dgv, string fileName)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "表格数据文件(*.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>
/// 将表格数据导出到文本文件中
/// </summary>
/// <param name="dgv"></param>
public static void ExportToText(this DataGridView dgv)
{
ExportToText(dgv, "表格数据");
}
/// <summary>
/// 将表格数据导出到文本文件中
/// </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、导出类定义：
1 //导出辅助类
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>
/// 应用程序类型
/// </summary>
public enum ApplicationType : int
{
WindowsForm,
Web
}
/// <summary>
/// 导出SmartGridView的数据源的数据为Excel
/// </summary>
// <param name="dt">数据源</param>
/// <param name="fileName">文件名</param>
/// <param name="ApplicationType">应用宿主类型</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");
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>
/// 导出SmartGridView的数据源的数据
/// </summary>
/// <param name="DetailsTable">数据源</param>
/// <param name="columnNameList">导出的列的列名数组</param>
/// <param name="sHeaders">导出的列标题数组</param>
/// <param name="FormatType">导出文件的格式</param>
/// <param name="FileName">输出文件名</param>
/// <param name="ApplicationType">应用宿主类型</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
{
// xsl:stylesheet
string ns = "http://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.WriteString("\"");
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>
/// 根据数据列的列名取数据列的列索引
/// </summary>
/// <param name="dcc">数据列集合</param>
/// <param name="columnName">数据列的列名</param>
/// <returns></returns>
public static int GetColumnIndexByColumnName(DataColumnCollection dcc, string columnName)
{
int result = -1;
for (int i = 0; i < dcc.Count; i++)
{
if (dcc[i].ColumnName.ToLower() == columnName.ToLower())
{
result = i;
break;
}
}
return result;
}
#endregion // WriteStylesheet
}
浙公网安备 33010602011771号