将 EXCEL 的上传与下载
首先必须下载 Aspose.Cells.dll 组件，引用
ASPX页面的控件为：
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="姓名">
<ItemTemplate>
<asp:TextBox ID="txtB_Name" runat="server" Text='<%# Eval("name")%>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="年龄">
<ItemTemplate>
<asp:TextBox ID="txtB_Age" runat="server" Text='<%# Eval("age") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="地址">
<ItemTemplate>
<asp:TextBox ID="txtB_Address" runat="server" Text='<%# Eval("address") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "修改">
<ItemTemplate>
<asp:Button runat="server" ID="btn_Edit" CommandName="edit" Text="编辑" OnClientClick="return confirm('确定要删除嘛？')" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:FileUpload ID="flUpload_Detail" runat="server" />
<asp:Button ID="btn_Upload" runat="server" OnClick="btn_Upload_Click" Text="上 传" />
<asp:Label ID="lb_Message" runat="server"></asp:Label>
<asp:Button ID="btn_Download" runat="server" OnClick="btn_Download_Click" Text="下 载" />
</form>
</body>
先在CS中定义属性：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Aspose.Cells;
public partial class test_DataToExcel061219 : System.Web.UI.Page
{
public DataTable dtExcel
{
set
{
this.ViewState["dtExcel"] = value;
}
get
{
if (this.ViewState["dtExcel"] == null)
return null;
else
return (DataTable)this.ViewState["dtExcel"];
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
InitPage();
}
}
private void InitPage()
{
InitTable();
}
protected void btn_Upload_Click(object sender, EventArgs e)
{
if (flUpload_Detail.PostedFile.FileName.ToUpper().EndsWith(".XLS"))
{
this.Response.Write("<script>alert('error!')</script>");
return;
}
else
{
Workbook wb = new Workbook();
string xlsName = System.IO.Path.GetFileNameWithoutExtension(flUpload_Detail.PostedFile.FileName.Trim()) + DateTime.Now.ToFileTime() + ".xls";
string savePath = Server.MapPath(xlsName);
flUpload_Detail.PostedFile.SaveAs(savePath);
wb.Open(savePath);
Cells cell = wb.Worksheets[0].Cells;
DataTable dtExcel = new DataTable();
dtExcel = cell.ExportDataTable(0, 0, cell.MaxRow + 1, 7);
if (dtExcel.Rows[0]["column1"].ToString() != "姓名")
{
this.Response.Write("<script>alert('请按正确的Excel格式上传！')</script>");
dtExcel = null;
InitTable();
}
else
{
dtExcel.Rows.RemoveAt(0);
dtExcel.Columns["column1"].ColumnName = "name";
dtExcel.Columns["column2"].ColumnName = "age";
dtExcel.Columns["column3"].ColumnName = "address";
dtExcel.Columns["column4"].ColumnName = "beizu";
try
{
this.GridView1.DataSource = dtExcel.DefaultView;
this.GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write("<script>alert("+ex.Message+")</script>");
}
GC.Collect();
System.IO.File.Delete(savePath);
}
}
}
protected void btn_Download_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
Aspose.Cells.Style style = workbook.DefaultStyle;
style.Font.Name = "Tahoma";
workbook.DefaultStyle = style;
CreateStaticData(workbook);
//CreateCellsFormatting(workbook);
workbook.Save("Information.xls", FileFormatType.Default, SaveType.OpenInExcel, this.Response);
}
/// <summary>
/// create table and bind gridview
/// </summary>
private void InitTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("name", typeof(System.String));
dt.Columns.Add("age", typeof(System.String));
dt.Columns.Add("address", typeof(System.String));
dt.Columns.Add("beizu", typeof(System.String));
dtExcel = dt;
if (dtExcel.Rows.Count < 1)
{
//handly table style
}
else
{
GridView1.DataSource = dtExcel.DefaultView;
GridView1.DataBind();
}
}
/// <summary>
/// create Excel
/// </summary>
/// <param name="workbook"></param>
private void CreateStaticData(Workbook workbook)
{
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "资料";
Cells cells = worksheet.Cells;
try
{
DataTable dt = dtExcel;
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { "姓名", "年龄", "地址" };
dt.Rows.InsertAt(dr, 0);
cells.ImportDataTable(dtExcel, false, 0, 0);
}
catch (Exception ex)
{
Response.Write("<script>alert(" + ex.Message + ")</script>");
}
}
}
另一种方式，不加DLL ，用OLEDB来操作：
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
OleDbDataAdapter objAda = new OleDbDataAdapter(objCmd);
DataSet ds = new DataSet();
objCmd.Connection = objConn;
objConn.Open();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string TableName;
if (dt.Rows.Count != 0)
{
dtExcel.Clear();
TableName = dt.Rows[0]["table_name"].ToString();
objCmd.CommandText = "select * from [" + TableName+"]";
objAda.Fill(dtExcel);
}
objConn.Close();
ASPX页面的控件为：
<body> <form id="form1" runat="server"> <div> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"> <Columns> <asp:TemplateField HeaderText="姓名"> <ItemTemplate> <asp:TextBox ID="txtB_Name" runat="server" Text='<%# Eval("name")%>'></asp:TextBox> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="年龄"> <ItemTemplate> <asp:TextBox ID="txtB_Age" runat="server" Text='<%# Eval("age") %>'></asp:TextBox> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="地址"> <ItemTemplate> <asp:TextBox ID="txtB_Address" runat="server" Text='<%# Eval("address") %>'></asp:TextBox> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText = "修改"> <ItemTemplate> <asp:Button runat="server" ID="btn_Edit" CommandName="edit" Text="编辑" OnClientClick="return confirm('确定要删除嘛？')" /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:FileUpload ID="flUpload_Detail" runat="server" /> <asp:Button ID="btn_Upload" runat="server" OnClick="btn_Upload_Click" Text="上 传" /> <asp:Label ID="lb_Message" runat="server"></asp:Label> <asp:Button ID="btn_Download" runat="server" OnClick="btn_Download_Click" Text="下 载" /> </form></body>先在CS中定义属性：
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Aspose.Cells;public partial class test_DataToExcel061219 : System.Web.UI.Page{
public DataTable dtExcel {
set { this.ViewState["dtExcel"] = value; } get { if (this.ViewState["dtExcel"] == null) return null; else return (DataTable)this.ViewState["dtExcel"]; } } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { InitPage(); } } private void InitPage() { InitTable(); } protected void btn_Upload_Click(object sender, EventArgs e) { if (flUpload_Detail.PostedFile.FileName.ToUpper().EndsWith(".XLS")) { this.Response.Write("<script>alert('error!')</script>"); return; } else { Workbook wb = new Workbook(); string xlsName = System.IO.Path.GetFileNameWithoutExtension(flUpload_Detail.PostedFile.FileName.Trim()) + DateTime.Now.ToFileTime() + ".xls"; string savePath = Server.MapPath(xlsName); flUpload_Detail.PostedFile.SaveAs(savePath); wb.Open(savePath); Cells cell = wb.Worksheets[0].Cells; DataTable dtExcel = new DataTable(); dtExcel = cell.ExportDataTable(0, 0, cell.MaxRow + 1, 7); if (dtExcel.Rows[0]["column1"].ToString() != "姓名") { this.Response.Write("<script>alert('请按正确的Excel格式上传！')</script>"); dtExcel = null; InitTable(); } else { dtExcel.Rows.RemoveAt(0); dtExcel.Columns["column1"].ColumnName = "name"; dtExcel.Columns["column2"].ColumnName = "age"; dtExcel.Columns["column3"].ColumnName = "address"; dtExcel.Columns["column4"].ColumnName = "beizu"; try { this.GridView1.DataSource = dtExcel.DefaultView; this.GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert("+ex.Message+")</script>"); } GC.Collect(); System.IO.File.Delete(savePath); } } } protected void btn_Download_Click(object sender, EventArgs e) { Workbook workbook = new Workbook(); Aspose.Cells.Style style = workbook.DefaultStyle; style.Font.Name = "Tahoma"; workbook.DefaultStyle = style; CreateStaticData(workbook); //CreateCellsFormatting(workbook); workbook.Save("Information.xls", FileFormatType.Default, SaveType.OpenInExcel, this.Response); } /// <summary> /// create table and bind gridview /// </summary> private void InitTable() { DataTable dt = new DataTable(); dt.Columns.Add("name", typeof(System.String)); dt.Columns.Add("age", typeof(System.String)); dt.Columns.Add("address", typeof(System.String)); dt.Columns.Add("beizu", typeof(System.String)); dtExcel = dt; if (dtExcel.Rows.Count < 1) { //handly table style } else { GridView1.DataSource = dtExcel.DefaultView; GridView1.DataBind(); } } /// <summary> /// create Excel /// </summary> /// <param name="workbook"></param> private void CreateStaticData(Workbook workbook) { Worksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "资料"; Cells cells = worksheet.Cells; try { DataTable dt = dtExcel; DataRow dr = dt.NewRow(); dr.ItemArray = new object[] { "姓名", "年龄", "地址" }; dt.Rows.InsertAt(dr, 0); cells.ImportDataTable(dtExcel, false, 0, 0); } catch (Exception ex) { Response.Write("<script>alert(" + ex.Message + ")</script>"); } }}另一种方式，不加DLL ，用OLEDB来操作：
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); OleDbCommand objCmd = new OleDbCommand(); OleDbDataAdapter objAda = new OleDbDataAdapter(objCmd); DataSet ds = new DataSet(); objCmd.Connection = objConn; objConn.Open(); DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string TableName; if (dt.Rows.Count != 0) { dtExcel.Clear(); TableName = dt.Rows[0]["table_name"].ToString(); objCmd.CommandText = "select * from [" + TableName+"]"; objAda.Fill(dtExcel); } objConn.Close(); 
