c# DataTable针对xml、excel、csv导入和导出
来源:互联网 发布:宿舍收纳整理知乎 编辑:程序博客网 时间:2024/05/17 04:19
此段代码是针对DataTable 对xml、excel、csv 对文件的导入和导出功能,记录一下,以供以后使用。
一定要导入excel 并添加引用Microsoft.Office.Interop.Excel 11.0版本。
Default.aspx.cs文件
- 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();
- }
//FORM XML
- 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
- 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", "");
- }
- 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", "");
- }
- 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" >
- c# DataTable针对xml、excel、csv导入和导出
- c# DataTable针对xml、excel、csv导入和导出
- c# DataTable 针对xml、excel、csv导入和导出
- DataTable针对xml、excel、csv导入和导出
- DataTable 对xml、excel、csv 对文件的导入和导出功能
- C#读取Excel和CSV为DataTable
- qt导入导出excel和csv
- C# 导出和导入excel
- C# 导出和导入excel
- C# 导入和导出EXCEL
- C# DataTable导出Excel
- datatable导入导出excel文件
- C#导出EXCEL(DataTable导出EXCEL)
- C#导出EXCEL(DataTable导出EXCEL)
- C#导出EXCEL(DataTable导出EXCEL)
- C#导出EXCEL(DataTable导出EXCEL)
- C#导出EXCEL(DataTable导出EXCEL)
- C#实现 EXCEL CSV导出
- VS2005 简单水晶报表的实现方式
- 初学Android
- Oracle+9i&10g编程艺术:深入数据库体系结构(读书笔记二)
- PowerDesigner 的四种模型文件
- sdfsdfsdf
- c# DataTable针对xml、excel、csv导入和导出
- PeekMessage 与 GetMessage 区别
- Welogic的Log4j日志设置
- 大侠周鸿祎——腾讯,你丫动手吧!
- 添加内核模块
- vfp中一些语法、关键字和控件属性
- VS2008中解决没有找到MSVCP90D.DLL的问题
- 使用基于GPU的Geometry Clipmap渲染地形(上)
- 本博客-----停更了....