c# DataTable针对xml、excel、csv导入和导出

此段代码是针对DataTable 对xml、excel、csv 对文件的导入和导出功能，记录一下，以供以后使用。
一定要导入excel 并添加引用Microsoft.Office.Interop.Excel 11.0版本。
Default.aspx.cs文件

view plaincopy to clipboardprint?
　　Default.aspx.cs文件  
　　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"];  
　　} 
　　Default.aspx.cs文件
　　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

view plaincopy to clipboardprint?
protected void Button1_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();  
　　} 
protected void Button1_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();
　　}
 

//FORM XML

view plaincopy to clipboardprint?
　protected void Button2_Click(object sender, EventArgs e)  
　　{  
　　string filepath = Server.MapPath("ss.xml");  
　　if (!File.Exists(filepath))  
　　{  
　　Page.RegisterClientScriptBlock("msg", "<mce:script type="text/javascript"><!--  
alert('该文件不存在！')  
// --></mce: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();  
　　}  
　　}  
　　}  
　　} 
　protected void Button2_Click(object sender, EventArgs e)
　　{
　　string filepath = Server.MapPath("ss.xml");
　　if (!File.Exists(filepath))
　　{
　　Page.RegisterClientScriptBlock("msg", "<mce:script type="text/javascript"><!--
alert('该文件不存在！')
// --></mce: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
view plaincopy to clipboardprint?
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();   
//上面注释的代码是一种以流的方式导入excel的，当数据在从此excel读取时会报一个异常，如果要对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 worksheet = (Excel.Worksheet)workbook.Worksheets[1];   
//写入字段   
for (int i = 0; i < dt.Columns.Count; i++)   
{   
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;   
}   
//写入数值   
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();//列宽自适应。   
workbook.Saved = true;   
workbook.SaveCopyAs(filepath);   
xlApp.Quit();   
GC.Collect();//强行销毁   
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();   
}  
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();
//上面注释的代码是一种以流的方式导入excel的，当数据在从此excel读取时会报一个异常，如果要对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 worksheet = (Excel.Worksheet)workbook.Worksheets[1];
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//写入数值
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();//列宽自适应。
workbook.Saved = true;
workbook.SaveCopyAs(filepath);
xlApp.Quit();
GC.Collect();//强行销毁
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
view plaincopy to clipboardprint?
protected void Button4_Click(object sender, EventArgs e)   
{   
//Office 2007 连接字符串   
//string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel 12.0;"   
//Office 98-2003 连接字符串(此示例使用2003)   
string filepath = Server.MapPath("ss.xls");   
if (!File.Exists(filepath))   
{   
Page.RegisterClientScriptBlock("msg", "");   
}   
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();   
}   
}  
protected void Button4_Click(object sender, EventArgs e)
{
//Office 2007 连接字符串
//string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel 12.0;"
//Office 98-2003 连接字符串(此示例使用2003)
string filepath = Server.MapPath("ss.xls");
if (!File.Exists(filepath))
{
Page.RegisterClientScriptBlock("msg", "");
}
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
view plaincopy to clipboardprint?
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();   
}  
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

 view plaincopy to clipboardprint?
protected void Button6_Click(object sender, EventArgs e)   
{   
string filepath = Server.MapPath("ss.csv");   
if (!File.Exists(filepath))   
{   
Page.RegisterClientScriptBlock("msg", "");   
}   
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;   
}   
}   
}   
}   
}  
protected void Button6_Click(object sender, EventArgs e)
{
string filepath = Server.MapPath("ss.csv");
if (!File.Exists(filepath))
{
Page.RegisterClientScriptBlock("msg", "");
}
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文件:

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
http://www.w3.org/1999/xhtml" >

 

 

 

 

Text="from xml" />
Text="toexcel" />
Text="fromexcel" />

Text="fromcsv" />

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xuejiayue1105/archive/2010/08/02/5782085.aspx