页面代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExeclControl.aspx.cs" Inherits="ExeclTest.ExeclControl" %> <!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"> <div> <p style="font-size:large; font-weight:bold;">下面是对Execl操作的详细步骤</p> <p style=" font-size:small; font-weight:bold;">第一步:选择一个本地Execl文件,点击导入。</p> <p style=" font-size:small">请选择一个本地Execl文件:<asp:FileUpload ID="firstFileUpload" runat="server" Height="25px" /> <asp:Button ID="btn_firstFileUpload" Text="导入" OnClick="btn_firstFileUpload_OnClick" runat="server" Height="25px" ></asp:Button> <asp:Label ID="labresult" runat="server"></asp:Label> </p> <p style=" font-size:small">第一步导入的表格:</p> <p> <asp:GridView ID="gv_firstExecl" runat="server" AllowPaging="True" onpageindexchanging="gv_firstExecl_PageIndexChanging" Width="100%"> </asp:GridView> </p> <p style=" font-size:small; font-weight:bold;">第二步:选择另外一个Execl文件,点击导入。</p> <p style=" font-size:small">请选择一个本地Execl文件:<asp:FileUpload ID="secondFileUpload" runat="server" Height="25px" /> <asp:Button ID="btn_secondFileUpload" Text="导入" OnClick="btn_secondFileUpload_OnClick" runat="server" Height="25px" ></asp:Button> <asp:Label ID="labresult2" runat="server"></asp:Label> </p> <p style=" font-size:small">第二步导入的表格:</p> <p> <asp:GridView ID="gv_secondExecl" runat="server" AllowPaging="True" onpageindexchanging="gv_secondExecl_PageIndexChanging" Width="100%"> </asp:GridView> </p> <p style=" font-size:small; font-weight:bold;">第三步:选择第一个表格的列和第二个表格匹配的列,合并。</p> <p style=" font-size:small">第一个Execl列:<asp:DropDownList ID="firstColumns" runat="server"></asp:DropDownList>, 第二个Execl列:<asp:DropDownList ID="secondColumns" runat="server"></asp:DropDownList> <asp:Button ID="btn_Merger" Text="合并" OnClick="btn_Merger_OnClick" runat="server" Height="25px" ></asp:Button> <asp:Label ID="lab_mergerMsg" runat="server"></asp:Label> </p> <p style=" font-size:small">合并后的表格:</p> <p> <asp:GridView ID="gv_merger" runat="server" AllowPaging="True" onpageindexchanging="gv_merger_PageIndexChanging" Width="100%"> </asp:GridView> </p> <p style=" font-size:small; font-weight:bold;">第四步:导出合并后的表格。</p> <p style="font-size:small"><asp:Button ID="btn_Export" Text="导出" OnClick="btn_Export_OnClick" runat="server" Height="25px" ></asp:Button></p> </div> </form> </body> </html>
后台代码:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using ExeclTest.Common; using System.Data; namespace ExeclTest { public partial class ExeclControl : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } #region first execl protected void btn_firstFileUpload_OnClick(object sender, EventArgs e) { Boolean fileOk = false; string path = Server.MapPath("~/upload/"); //判断是否已经选取文件 if (firstFileUpload.HasFile) { //取得文件的扩展名,并转换成小写 string fileExtension = System.IO.Path.GetExtension(firstFileUpload.FileName).ToLower(); //限定只能上传jpg和gif图片 string[] allowExtension = { ".xlsx", ".xls" }; //对上传的文件的类型进行一个个匹对 for (int i = 0; i < allowExtension.Length; i++) { if (fileExtension == allowExtension[i]) { fileOk = true; break; } } if (fileOk) { labresult.Text = "要上传的文件类型不对!"; } //对上传文件的大小进行检测,限定文件最大不超过2M if (firstFileUpload.PostedFile.ContentLength > 2048000) { fileOk = false; } //最后的结果 if (fileOk) { try { firstFileUpload.PostedFile.SaveAs(path + firstFileUpload.FileName); string tablename = Execl.GetExcelFirstTableName(path + firstFileUpload.FileName); string TSql = "SELECT * FROM [" + tablename + "]"; DataSet firstData = Execl.ExcelToDataSet(path + firstFileUpload.FileName, TSql); Session["firstData"] = firstData; gv_firstExecl.DataSource = firstData; gv_firstExecl.DataBind(); firstColumns.Items.Clear(); for (int i = 0; i < firstData.Tables[0].Columns.Count;i++ ) { ListItem item = new ListItem(); item.Text = firstData.Tables[0].Columns[i].Caption; item.Value = i.ToString(); firstColumns.Items.Add(item); } labresult.Text = "上传成功"; } catch { labresult.Text = "上传失败"; } } else { labresult.Text = "文件类型或者文件大小超出2M或者文件类型不对"; } } } protected void gv_firstExecl_PageIndexChanging(object sender, GridViewPageEventArgs e) { gv_firstExecl.PageIndex = e.NewPageIndex; gv_firstExecl.DataSource = Session["firstData"]; gv_firstExecl.DataBind(); } #endregion #region second execl protected void btn_secondFileUpload_OnClick(object sender, EventArgs e) { Boolean fileOk = false; string path = Server.MapPath("~/upload/"); //判断是否已经选取文件 if (secondFileUpload.HasFile) { //取得文件的扩展名,并转换成小写 string fileExtension = System.IO.Path.GetExtension(secondFileUpload.FileName).ToLower(); //限定只能上传jpg和gif图片 string[] allowExtension = { ".xlsx", ".xls" }; //对上传的文件的类型进行一个个匹对 for (int i = 0; i < allowExtension.Length; i++) { if (fileExtension == allowExtension[i]) { fileOk = true; break; } } if (fileOk) { labresult.Text = "要上传的文件类型不对!"; } //对上传文件的大小进行检测,限定文件最大不超过2M if (secondFileUpload.PostedFile.ContentLength > 2048000) { fileOk = false; } //最后的结果 if (fileOk) { try { secondFileUpload.PostedFile.SaveAs(path + secondFileUpload.FileName); string tablename = Execl.GetExcelFirstTableName(path + secondFileUpload.FileName); string TSql = "SELECT * FROM [" + tablename + "]"; DataSet secondData = Execl.ExcelToDataSet(path + secondFileUpload.FileName, TSql); Session["secondData"] = secondData; gv_secondExecl.DataSource = secondData; gv_secondExecl.DataBind(); secondColumns.Items.Clear(); for (int i = 0; i < secondData.Tables[0].Columns.Count; i++) { ListItem item = new ListItem(); item.Text = secondData.Tables[0].Columns[i].Caption; item.Value = i.ToString(); secondColumns.Items.Add(item); } labresult2.Text = "上传成功"; } catch { labresult2.Text = "上传失败"; } } else { labresult2.Text = "文件类型或者文件大小超出2M或者文件类型不对"; } } } protected void gv_secondExecl_PageIndexChanging(object sender, GridViewPageEventArgs e) { gv_secondExecl.PageIndex = e.NewPageIndex; gv_secondExecl.DataSource = Session["secondData"]; gv_secondExecl.DataBind(); } #endregion protected void btn_Merger_OnClick(object sender, EventArgs e) { DataSet firstData = (DataSet)Session["firstData"]; DataSet secondData = (DataSet)Session["secondData"]; DataTable newdata = firstData.Tables[0].Clone(); foreach (DataColumn col in secondData.Tables[0].Columns) { if (!col.Caption.Equals(secondColumns.SelectedItem.Text)) { newdata.Columns.Add(col.Caption); } } int firstColValue= int.Parse(firstColumns.SelectedValue); int secondColValue = int.Parse(secondColumns.SelectedValue); object[] obj = new object[newdata.Columns.Count]; for (int i = 0; i < firstData.Tables[0].Rows.Count; i++) { bool equ = false; int j = 0; for (; j < secondData.Tables[0].Rows.Count; j++) { if(firstData.Tables[0].Rows[i].ItemArray[firstColValue].Equals(secondData.Tables[0].Rows[j].ItemArray[secondColValue])) { equ = true; break; } } if (equ) { firstData.Tables[0].Rows[i].ItemArray.CopyTo(obj, 0); newdata.Rows.Add(obj); for (int k = 0, t=firstData.Tables[0].Columns.Count; k < secondData.Tables[0].Rows[j].ItemArray.Count(); k++) { if(k!=secondColValue) newdata.Rows[i][t] = secondData.Tables[0].Rows[j].ItemArray[k].ToString(); } secondData.Tables[0].Rows[j].Delete(); secondData.AcceptChanges(); } else { firstData.Tables[0].Rows[i].ItemArray.CopyTo(obj, 0); newdata.Rows.Add(obj); } } for (int i = 0; i < secondData.Tables[0].Rows.Count; i++) { int showSecond = 0; object[] row =new object[firstData.Tables[0].Columns.Count + secondData.Tables[0].Columns.Count - 1]; for (int j = 0; j < firstData.Tables[0].Columns.Count + secondData.Tables[0].Columns.Count ; j++) { if (j < firstData.Tables[0].Columns.Count) { if (j != firstColValue) { row[j] = ""; } else { row[j] = secondData.Tables[0].Rows[i].ItemArray[secondColValue]; } } else { if (j - firstData.Tables[0].Columns.Count != secondColValue) { row[j-showSecond] = secondData.Tables[0].Rows[i].ItemArray[j - firstData.Tables[0].Columns.Count]; } else { showSecond = 1; } } } newdata.Rows.Add(row); } gv_merger.DataSource = newdata; Session["mergerData"] = newdata; gv_merger.DataBind(); } protected void gv_merger_PageIndexChanging(object sender, GridViewPageEventArgs e) { gv_merger.PageIndex = e.NewPageIndex; gv_merger.DataSource = Session["mergerData"]; gv_merger.DataBind(); } protected void btn_Export_OnClick(object sender, EventArgs e) { gv_merger.AllowPaging = false;//取消分页,使GridView显示全部数据. gv_merger.DataSource = Session["mergerData"]; gv_merger.DataBind();//重新绑定. Response.Clear(); Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename= " + Server.UrlEncode("导出.xls")); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件. this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); System.IO.StringWriter stringWrite = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); gv_merger.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); gv_merger.AllowPaging = true; } public override void VerifyRenderingInServerForm(Control control) { } } }