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

最新推荐文章于 2024-06-21 18:49:43 发布

dz45693

最新推荐文章于 2024-06-21 18:49:43 发布

阅读量3.4k

点赞数 1

分类专栏： ASP.NET 文章标签： excel csv xml c# database null

ASP.NET 专栏收录该内容

103 篇文章 1 订阅

订阅专栏

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

view plain copy to clipboard print ?

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 plain copy to clipboard print ?

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 plain copy to clipboard print ?

　 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 plain copy to clipboard print ?

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 plain copy to clipboard print ?

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 plain copy to clipboard print ?

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();
}