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