导出datatable为CSV或者XlS格式
// ---------------------------------------------------------
// Rama Krishna's Export class
// Copyright (C) 2004 Rama Krishna. All rights reserved.
// ---------------------------------------------------------
![](/Images/OutliningIndicators/None.gif)
# region Includes![](https://www.cnblogs.com/Images/dot.gif)
using System;
using System.Data;
using System.Web;
using System.Web.SessionState;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
using System.Threading;
![](/Images/OutliningIndicators/None.gif)
# endregion // Includes![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
namespace DMD.Web.Module
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
# region Summary
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// Exports datatable to CSV or Excel format.
/// This uses DataSet's XML features and XSLT for exporting.
/// </summary>
/// <example>
/// C#.Net Example to be used in WebForms
/// -------------------------------------
/// using MyLib.ExportData;
///
/// private void btnExport_Click(object sender, System.EventArgs e)
/// {
/// try
/// {
/// // Declarations
/// DataSet dsUsers = ((DataSet) Session["dsUsers"]).Copy( );
/// MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web");
/// string FileName = "UserList.csv";
/// int[] ColList = {2, 3, 4, 5, 6};
/// oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.ExportFormat.CSV, FileName);
/// }
/// catch(Exception Ex)
/// {
/// lblError.Text = Ex.Message;
/// }
/// }
///
/// VB.Net Example to be used in WindowsForms
/// -----------------------------------------
/// Imports MyLib.ExportData
///
/// Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
///
/// Try
///
/// 'Declarations
/// Dim dsUsers As DataSet = (CType(Session("dsUsers"), DataSet)).Copy()
/// Dim oExport As New MyLib.ExportData.Export("Win")
/// Dim FileName As String = "C:\\UserList.xls"
/// Dim ColList() As Integer = New Integer() {2, 3, 4, 5, 6}
/// oExport.ExportDetails(dsUsers.Tables(0), ColList, Export.ExportFormat.CSV, FileName)
///
/// Catch Ex As Exception
/// lblError.Text = Ex.Message
/// End Try
///
/// End Sub
/// </example>
![](/Images/OutliningIndicators/InBlock.gif)
# endregion // Summary
![](/Images/OutliningIndicators/InBlock.gif)
public class Export
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public enum ExportFormat : int
{CSV = 1, Excel = 2}; // Export format enumeration
System.Web.HttpResponse response;
private string appType;
public Export()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
appType = "Web";
response = System.Web.HttpContext.Current.Response;
}
![](/Images/OutliningIndicators/InBlock.gif)
public Export(string ApplicationType)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
appType = ApplicationType;
if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
if (appType == "Web") response = System.Web.HttpContext.Current.Response;
}
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
ExportDetails OverLoad : Type#1#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
![](/Images/OutliningIndicators/InBlock.gif)
public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = dtExport.Columns[i].ColumnName;
}
![](/Images/OutliningIndicators/InBlock.gif)
if(appType == "Web")
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
else if(appType == "Win")
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
catch(Exception Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
![](/Images/OutliningIndicators/InBlock.gif)
#endregion // ExportDetails OverLoad : Type#1
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
ExportDetails OverLoad : Type#2#region ExportDetails OverLoad : Type#2
![](/Images/OutliningIndicators/InBlock.gif)
// 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
![](/Images/OutliningIndicators/InBlock.gif)
public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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);
![](/Images/OutliningIndicators/InBlock.gif)
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++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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] = dtExport.Columns[ColumnList[i]].ColumnName;
}
![](/Images/OutliningIndicators/InBlock.gif)
if(appType == "Web")
Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
else if(appType == "Win")
Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
catch(Exception Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
#endregion // ExportDetails OverLoad : Type#2
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
ExportDetails OverLoad : Type#3#region ExportDetails OverLoad : Type#3
![](/Images/OutliningIndicators/InBlock.gif)
// 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
![](/Images/OutliningIndicators/InBlock.gif)
public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType,
string FileName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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);
![](/Images/OutliningIndicators/InBlock.gif)
if(ColumnList.Length != Headers.Length)
throw new Exception("ExportColumn List and Headers List should be of same length");
else if(ColumnList.Length > dtExport.Columns.Count || Headers.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++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
throw new Exception("ExportColumn Number should not exceed Total Columns Range");
sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;
}
![](/Images/OutliningIndicators/InBlock.gif)
if(appType == "Web")
Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
else if(appType == "Win")
Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
}
catch(Exception Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
#endregion // ExportDetails OverLoad : Type#3
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
Export_with_XSLT_Web#region Export_with_XSLT_Web
![](/Images/OutliningIndicators/InBlock.gif)
// Function : Export_with_XSLT_Web
// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
// Purpose : Exports dataset into CSV / Excel format
![](/Images/OutliningIndicators/InBlock.gif)
private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Appending Headers
response.Clear();
response.Buffer= true;
if(FormatType == ExportFormat.CSV)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
response.ContentType = "text/csv";
response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
response.ContentType = "application/vnd.ms-excel";
response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
}
![](/Images/OutliningIndicators/InBlock.gif)
// 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);
![](/Images/OutliningIndicators/InBlock.gif)
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
//Writeout the Content
response.Write(sw.ToString());
sw.Close();
writer.Close();
stream.Close();
response.End();
}
catch(ThreadAbortException Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string ErrMsg = Ex.Message;
}
catch(Exception Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
#endregion // Export_with_XSLT
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
Export_with_XSLT_Windows#region Export_with_XSLT_Windows
![](/Images/OutliningIndicators/InBlock.gif)
// Function : Export_with_XSLT_Windows
// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
// Purpose : Exports dataset into CSV / Excel format
![](/Images/OutliningIndicators/InBlock.gif)
private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 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);
![](/Images/OutliningIndicators/InBlock.gif)
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
//Writeout the Content
StreamWriter strwriter = new StreamWriter(FileName);
strwriter.WriteLine(sw.ToString());
strwriter.Close();
sw.Close();
writer.Close();
stream.Close();
}
catch(Exception Ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
#endregion // Export_with_XSLT
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
CreateStylesheet#region CreateStylesheet
![](/Images/OutliningIndicators/InBlock.gif)
// Function : WriteStylesheet
// Arguments : writer, sHeaders, sFileds, FormatType
// Purpose : Creates XSLT file to apply on dataset's XML file
![](/Images/OutliningIndicators/InBlock.gif)
private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 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","/");
![](/Images/OutliningIndicators/InBlock.gif)
// xsl:value-of for headers
for(int i=0; i< sHeaders.Length; i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw Ex;
}
}
#endregion // WriteStylesheet
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
posted on
2005-12-27 17:17
RubyPDF
阅读(
3472)
评论()
编辑
收藏
举报