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)