DataTable 对xml、excel、csv 对文件的导入和导出功能

此段代码是针对DataTable 对xml、excel、csv 对文件的导入和导出功能，记录一下，以供以后使用。

一定要导入excel 并添加引用Microsoft.Office.Interop.Excel 11.0版本。

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

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

        }

        //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('该文件不存在！')</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();  

                

            //上面注释的代码是一种以流的方式导入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

        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", "<script>alert('该文件不存在！')</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('该文件不存在！')</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文件:

<%@ 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" οnclick="Button1_Click" Text="toxml" />

        <asp:Button ID="Button2" runat="server" οnclick="Button2_Click" 

            Text="from xml" />

        <asp:Button ID="Button3" runat="server" οnclick="Button3_Click" 

            Text="toexcel" />

        <asp:Button ID="Button4" runat="server" οnclick="Button4_Click" 

            Text="fromexcel" />

        <asp:Button ID="Button5" runat="server" οnclick="Button5_Click" Text="tocsv" />

        <asp:Button ID="Button6" runat="server" οnclick="Button6_Click" 

            Text="fromcsv" />

       

    </form>

</body>

</html>