C # datatable for XML, Excel, CSV Import and Export

This code is used to import and export datatable files in XML, Excel, and CSV files. Record the code for future use.

Be sure to import the Excel file and add a reference to Microsoft. Office. InterOP. Excel 11.0.

Default. aspx. CS File

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. Web;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Data. sqlclient;
Using system. Data;
Using system. xml;
Using system. xml. XSL;
Using system. IO;
Using system. Data. oledb;
Using system. Data. ODBC;
Using system. text;
Using Excel = Microsoft. Office. InterOP. Excel;

Namespace fantest
{
Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
BIND ();
}
Protected void BIND ()
{
This. gridview1.datasource = This. getdatatable ();
This. gridview1.databind ();
}
Private datatable getdatatable ()
{
Dataset DS = new dataset ();
Using (sqlconnection conn = new sqlconnection ("Server =.; uid = sa; Pwd = 123456; database = test "))
{
String SQL = "select * From infotable where 1 = 1 ";
Sqldataadapter DAP = new sqldataadapter (SQL, Conn );
Dap. Fill (DS, "infotable ");
}
Return Ds. Tables ["infotable"];
}
// To XML
Protected void button#click (Object sender, eventargs E)
{
Datatable dt = This. getdatatable ();
Stringbuilder sb = new stringbuilder ();
SB. append ("<" + dt. tablename + "> ");
Foreach (datarow row in DT. Rows)
{
SB. append ("<item> ");

For (INT I = 0; I <DT. Columns. Count; I ++)
{
SB. append ("<" + dt. columns [I]. columnname + ">" + row [I]. tostring () + "</" + dt. columns [I]. columnname + "> ");
}

SB. append ("</item> ");
}
SB. append ("</" + dt. tablename + "> ");
Response. clearheaders ();
Response. appendheader ("content-disposition", "attachment; filename = ss. xml ");
Response. contenttype = "text/CSV ";
Response. Write (sb. tostring ());
Response. End ();
}
// From XML
Protected void button2_click (Object sender, eventargs E)
{
String filepath = server. mappath ("SS. xml ");
If (! File. exists (filepath ))
{
Page. registerclientscriptblock ("MSG", "<SCRIPT> alert ('this file does not exist! ') </SCRIPT> ");
}
Else
{
Stringreader strstream = NULL;
Xmltextreader xmlrdr = NULL;
Try
{
Xmldocument xmldoc = new xmldocument ();
Xmldoc. Load (filepath );
Dataset DS = new dataset ();
DS. readxml (New xmltextreader (New stringreader (xmldoc. innerxml )));
This. gridview2.datasource = Ds. Tables [0];
This. gridview2.databind ();
}
Catch (exception ex)
{
Throw ex;
}
Finally
{
If (xmlrdr! = NULL)
{
Xmlrdr. Close ();
Strstream. Close ();
Strstream. Dispose ();
}
}
}
}
// To excel
Protected void button3_click (Object sender, eventargs E)
{
// Response. charset = "gb2312 ";
// Response. contentencoding = system. Text. encoding. utf7;
// Response. appendheader ("content-disposition", "attachment; filename =" + httputility. urlencode ("ss.xls", encoding. utf8). tostring ());
// Response. contenttype = "application/vnd. MS-excel ";
// This. enableviewstate = false;
// Stringwriter Tw = new stringwriter ();
// Htmltextwriter hW = new htmltextwriter (TW );
// This. gridview1.rendercontrol (HW );
// Response. Write (TW. tostring ());
// Response. End ();

// The Code Annotated above is a stream-based import of Excel. when the data is read from this Excel, an exception is reported, the following method is recommended if you want to write and read data to and from Excel

Datatable dt = This. getdatatable ();
String filepath = httpcontext. Current. server. mappath ("ss.xls ");
Excel. Application xlapp = new excel. Application ();
Excel. workbooks W = xlapp. workbooks;
Excel. Workbook workbook = W. Add (Excel. xlwbatemplate. xlwbatworksheet );
Excel. worksheet = (Excel. worksheet) Workbook. worksheets [1];
// Write Fields
For (INT I = 0; I <DT. Columns. Count; I ++)
{
Worksheet. cells [1, I + 1] = DT. Columns [I]. columnname;
}
// Write Value
For (INT r = 0; r <DT. Rows. Count; r ++)
{
For (INT I = 0; I <DT. Columns. Count; I ++)
{
Worksheet. cells [R + 2, I + 1] = DT. Rows [r] [I];
}
}
Worksheet. Columns. entirecolumn. autofit (); // adaptive column width.
Workbook. Saved = true;
Workbook. savecopyas (filepath );
Xlapp. Quit ();
GC. Collect (); // forcibly destroy
Httpcontext. Current. response. Buffer = true;
Httpcontext. Current. response. Clear ();
Httpcontext. Current. response. contenttype = "application/MS-excel ";
Httpcontext. Current. response. addheader ("content-disposition", "attachment; filename =" + httputility. urlencode (filepath ));
Httpcontext. Current. response. writefile (filepath );
Httpcontext. Current. response. Flush ();
Httpcontext. Current. response. End ();
}
// From Excel
Protected void button4_click (Object sender, eventargs E)
{
// Office 2007 connection string
// String strconn = "provider = Microsoft. Ace. oledb.12.0;" + "Data Source =" + @ path + ";" + "extended properties = Excel 12.0 ;"
// Office 98-2003 connection string (2003 is used in this example)
String filepath = server. mappath ("ss.xls ");
If (! File. exists (filepath ))
{
Page. registerclientscriptblock ("MSG", "<SCRIPT> alert ('this file does not exist! ') </SCRIPT> ");
}
Else
{
String strconn = "provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ filepath +"; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '";
Oledbconnection conn = new oledbconnection (strconn );
Oledbdataadapter Odda = new oledbdataadapter ("select * from [sheet1 $]", Conn );
Dataset DS = new dataset ();
Odda. Fill (DS );
This. gridview2.datasource = Ds. Tables [0];
This. gridview2.databind ();
}
}
// To CSV
Protected void button5_click (Object sender, eventargs E)
{
Datatable dt = This. getdatatable ();
Httpcontext. Current. response. Clear ();
System. Io. stringwriter Sw = new system. Io. stringwriter ();
Int icolcount = DT. Columns. count;
For (INT I = 0; I <icolcount; I ++)
{
Sw. Write ("\" "+ dt. Columns [I] + "\"");
If (I <icolcount-1)
{
Sw. Write (",");
}
}
Sw. Write (SW. newline );
Foreach (datarow DR in DT. Rows)
{
For (INT I = 0; I <icolcount; I ++)
{
If (! Convert. isdbnull (Dr [I])
Sw. Write ("\" "+ Dr [I]. tostring () + "\"");
Else
Sw. Write ("\"\"");

If (I <icolcount-1)
{
Sw. Write (",");
}
}
Sw. Write (SW. newline );
}
Sw. Close ();
Httpcontext. Current. response. addheader ("content-disposition", "attachment; filename=ss.csv ");
Httpcontext. Current. response. contenttype = "application/vnd. MS-excel ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Httpcontext. Current. response. Write (SW );
Httpcontext. Current. response. End ();
}
// From CSV
Protected void button6_click (Object sender, eventargs E)
{
String filepath = server. mappath ("ss.csv ");
If (! File. exists (filepath ))
{
Page. registerclientscriptblock ("MSG", "<SCRIPT> alert ('this file does not exist! ') </SCRIPT> ");
}
Else
{
String strconn = @ "driver = {Microsoft text Driver (*. txt; *. CSV)}; DBQ = ";
Strconn + = "; extensions = ASC, CSV, tab, txt ;";
Odbcconnection objconn = new odbcconnection (strconn );
Dataset DS = new dataset ();
Try
{
String strsql = "select * from" + filepath;
Odbcdataadapter odbccsvdataadapter = new odbcdataadapter (strsql, objconn );
Odbccsvdataadapter. Fill (DS );
This. gridview2.datasource = Ds. Tables [0];
This. gridview2.databind ();
}
Catch (exception ex)
{
Throw ex;
}
}
}
}
}

Default. aspx file:

<% @ Page Language = "C #" enableeventvalidation = "false" autoeventwireup = "true" codebehind = "default. aspx. cs" inherits = "fantest. _ default" %>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Asp: gridview id = "gridview1" runat = "server">
</ASP: gridview>
<Asp: gridview id = "gridview2" runat = "server">
</ASP: gridview>
<Asp: button id = "button1" runat = "server" onclick = "button#click" text = "toxml"/>
<Asp: button id = "button2" runat = "server" onclick = "button2_click"
TEXT = "from XML"/>
<Asp: button id = "button3" runat = "server" onclick = "button3_click"
TEXT = "toexcel"/>
<Asp: button id = "button4" runat = "server" onclick = "button4_click"
TEXT = "fromexcel"/>
<Asp: button id = "button5" runat = "server" onclick = "button5_click" text = "tocsv"/>
<Asp: button id = "button6" runat = "server" onclick = "button6_click"
TEXT = "fromcsv"/>

</Form>
</Body>
</Html>