Import data from the DataGrid to excel or CSV

I have been developing a new software for my sister company over the past few days for their convenience. This software was built using C # + SQL Server, basically completed, but today they proposed a function for data export, I searched online, then codeporject finds the source code for rklib import and export.

 

Garbled characters occurred when exporting Chinese data. It was estimated that the encoding problem was encountered during data writing, so I thought of the encoding change. So I changed a code and exported the supported Chinese characters.

Rklib. Export source code:

//---------------------------------------------------------
// Rama Krishna's export class
// Copyright (c) 2004 Rama Krishna. All rights reserved.
//---------------------------------------------------------

# Region regiondes...

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;

# Endregion // regiondes...

Namespace letbetter
{
# Region Summary

/// <Summary>
/// Exports datatable to CSV or Excel format.
/// This uses dataset's XML features and XSLT for exporting.
///
/// 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
///
/// 'Clarations
/// 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
///
/// </Summary>

# Endregion // Summary

Public class export
{
Public Enum exportformat: int {CSV = 1, Excel = 2}; // export format Enumeration
System. Web. httpresponse response;
Private string apptype;

Public Export ()
{
Apptype = "Web ";
Response = system. Web. httpcontext. Current. response;
}

Public Export (string applicationtype)
{
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;
}

# Region exportdetails overload: Type #1

// Function: exportdetails
// Arguments: detailstable, formattype, filename
// Purpose: to get all the column headers in the datatable and
// Exports in CSV/Excel format with all columns

Public void exportdetails (datatable detailstable, exportformat formattype, string filename)
{
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] = dtexport. Columns [I]. columnname;
}

If (apptype = "Web ")
Export_with_effect_web (dsexport, sheaders, sfileds, formattype, filename );
Else if (apptype = "win ")
Export_with_effect_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 void exportdetails (datatable detailstable, int [] columnlist, exportformat formattype, string filename)
{
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 shocould 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 shocould not exceed total columns range ");

Sheaders [I] = dtexport. Columns [columnlist [I]. columnname;
Sfileds [I] = dtexport. Columns [columnlist [I]. columnname;
}

If (apptype = "Web ")
Export_with_effect_web (dsexport, sheaders, sfileds, formattype, filename );
Else if (apptype = "win ")
Export_with_effect_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 void exportdetails (datatable detailstable, int [] columnlist, string [] headers, exportformat formattype,
String filename)
{
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! = Headers. length)
Throw new exception ("exportcolumn list and headers list shocould be of same length ");
Else if (columnlist. length> dtexport. Columns. Count | headers. length> dtexport. Columns. Count)
Throw new exception ("exportcolumn list shocould 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 shocould not exceed total columns range ");

Sfileds [I] = dtexport. Columns [columnlist [I]. columnname;
}

If (apptype = "Web ")
Export_with_effect_web (dsexport, headers, sfileds, formattype, filename );
Else if (apptype = "win ")
Export_with_effect_windows (dsexport, headers, sfileds, formattype, filename );
}
Catch (exception ex)
{
Throw ex;
}
}

# Endregion // exportdetails overload: Type #3

# Region export_with_xslt_web

// Function: export_with_effect_web
// Arguments: dsexport, sheaders, sfileds, formattype, filename
// Purpose: exports dataset into CSV/Excel format

Private void export_with_xslt_web (Dataset dsexport, string [] sheaders, string [] sfileds, exportformat formattype, string filename)
{
Try
{
// Appending Headers
Response. Clear ();
Response. Buffer = true;

If (formattype = exportformat. CSV)
{
Response. contenttype = "text/CSV ";
Response. appendheader ("content-disposition", "attachment; filename =" + filename );
}
Else
{
Response. contenttype = "application/vnd. MS-excel ";
Response. appendheader ("content-disposition", "attachment; filename =" + filename );
}

// XSLT to use for transforming this dataset.
Memorystream stream = new memorystream ();
Xmltextwriter writer = new xmltextwriter (stream, new system. Text. unicodeencoding ());

Createstylesheet (writer, sheaders, sfileds, formattype );
Writer. Flush ();
Stream. Seek (0, seekorigin. Begin );

Xmldatadocument xmldoc = new xmldatadocument (dsexport );
Transform transform into TRAN = new transform ();
Using Tran. Load (New xmltextreader (Stream), null, null );

System. Io. stringwriter Sw = new system. Io. stringwriter ();
Using Tran. Transform (xmldoc, null, SW, null );

// Writeout the content
Response. Write (SW. tostring ());
Sw. Close ();
Writer. Close ();
Stream. Close ();
Response. End ();
}
Catch (threadabortexception ex)
{
String errmsg = ex. message;
}
Catch (exception ex)
{
Throw ex;
}
}

# Endregion // export_with_xslt

# Region export_with_effect_windows

// Function: export_with_effect_windows
// Arguments: dsexport, sheaders, sfileds, formattype, filename
// Purpose: exports dataset into CSV/Excel format

Private void export_with_effect_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, system. Text. encoding. getencoding ("gb2312 "));

Createstylesheet (writer, sheaders, sfileds, formattype );
Writer. Flush ();
Stream. Seek (0, seekorigin. Begin );

Xmldatadocument xmldoc = new xmldatadocument (dsexport );
Transform transform into TRAN = new transform ();
Using Tran. Load (New xmltextreader (Stream), null, null );

System. Io. stringwriter Sw = new system. Io. stringwriter ();
Using Tran. Transform (xmldoc, null, SW, null );

// Writeout the content
// Streamwriter strwriter = new streamwriter (filename );
Streamwriter strwriter = new streamwriter (filename, false, system. Text. encoding. getencoding ("gb2312 "));
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 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-");
Writer. writeattributestring ("select", "'" + sheaders [I] + "'");
Writer. writeendelement (); // XSL: value-
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-");
Writer. writeattributestring ("select", sfileds [I]);
Writer. writeendelement (); // XSL: value-
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;
}
}

# Endregion // writestylesheet
}

}

 

For a project in winform, If you need Chinese support, you only need to change one line of code:

In the export_with_cmdt_windows function:

Streamwriter strwriter = new streamwriter (filename );

Changed:
Streamwriter strwriter = new streamwriter (filename, false, system. Text. encoding. getencoding ("gb2312 "));

You can.

Hey, it's easy!

Note that:

1. When using this code, you need to add a reference to system. web in the project;

2. This code is slow when exporting data. In the next version, I want to use multi-threaded operations that I have never used before. Maybe this will change the speed. (Each project has to challenge itself)