c# 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" onclick="Button1_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>