新建aspx文件、代码大概如下：
<!--导入Excel文件--> <table width="99%" border="0" align="center" cellpadding="0" id="searchtable" cellspacing="0" bgcolor="#CCCCCC"> <tr> <td bgcolor="#FFFFFF"> <table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC" class="table"> <tr class="table-item"> <td width="12%" height="20" bgcolor="#E7E7E7" align="center"> <asp:Button ID="btn_ExportReceivable" Text="导出Excel" OnClick="btn_excel_Click1" runat="server"/> </td> <td width="48%" bgcolor="#FFFFFF"> <input id="fileExportExcelReceivable" style="width:100%" type="file" runat="server"/> </td> <td width="10%" bgcolor="#E7E7E7" align="center"> <asp:Button ID="btn_excelReceivable" Visible="true" Text="导入Excel" OnClick="btn_excel_Click2" runat="server"/> </td> </tr> </table> </td> </tr> </table> <!--导入结束-->aspx.cs中代码如下：
using NPOI.HSSF.UserModel; using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using XBase.Model.Office.PurchaseManager; using XBase.Common; using XBase.Business.Office.PurchaseManager; using System.Collections.Generic; using System.Data.OleDb; using System.IO; using System.Data; public partial class Pages_Office_FinanceManager_ReceivablePayable : System.Web.UI.Page { HSSFWorkbook hssfworkbook; protected void Page_Load(object sender, EventArgs e) { } /// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_excel_Click1(object sender, EventArgs e) { XBase.Model.Office.SellReport.AccountsRP AccountsRP = new XBase.Model.Office.SellReport.AccountsRP(); DataTable dt = XBase.Business.Office.PurchaseManager.PurchaseOrderBus.GetAccountsRP(); OutputToExecl.ExportToTableFormat(this, dt, new string[] { "订单编号", "类型", "供应商", "发票号", "应付金额", "已付金额", "付款金额" }, new string[] { "SalesOrderNo", "type", "CustName", "InvoiceNum", "ComSendMoney", "ReceivedPrepaid", "fk" }, "应付报表"); } protected void btn_excel_Click2(object sender, EventArgs e) { string strcompanycd = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; string struid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeNum; try { /*文件名*/ string FileName = string.Empty; /*上传后完整的文件路径包含文件名*/ string FileNewUrl = string.Empty; #region 上传验证 /*获取公司的上传路径*/ string FileUrl = XBase.Business.Office.SupplyChain.ProductInfoBus.GetCompanyUpFilePath(strcompanycd); /*验证该公司路径是否存在 不存在则创建*/ DirectoryInfo dir = new DirectoryInfo(FileUrl); if (!dir.Exists) { try { dir.Create(); } catch (Exception ex) { this.lbl_resultReceivable.Text = ex.ToString(); return; } } /*验证是否选择了文件*/ if (string.IsNullOrEmpty(fileExportExcelReceivable.PostedFile.FileName)) { this.lbl_resultReceivable.Text = "请选择需要导入的Excel文件"; return; } /*验证文件类型*/ string FileExtension = fileExportExcelReceivable.PostedFile.FileName.Split('.')[1].ToUpper(); if (FileExtension != "XLS" && FileExtension != "XLSX") ErrorMsg += "文件错误，请上传正确的Excel文件\\n"; /*判断是否存在异常*/ if (!string.IsNullOrEmpty(ErrorMsg)) { this.lbl_resultReceivable.Text = ErrorMsg; return; } /*上传文件*/ string strID = Guid.NewGuid().ToString(); FileName = strID + "." + FileExtension.ToLower(); FileNewUrl = FileUrl + "\\" + FileName; try { fileExportExcelReceivable.PostedFile.SaveAs(FileNewUrl); } catch (Exception ex) { this.lbl_resultReceivable.Text = ex.ToString(); } #endregion DataSet ds = ToDataTable(FileNewUrl); foreach (DataRow dr in ds.Tables["应付报表$"].Rows) { try { string[] listADD = new string[1]; string order = dr[0].ToString(); int money = int.Parse(dr[6].ToString()); string sql = @"UPDATE officedba.AccountsRP SET ReceivedPrepaid = (SELECT ReceivedPrepaid+" + money + " FROM officedba.AccountsRP where SalesOrderNo='" + order + "') WHERE SalesOrderNo='" + order + "'"; listADD[0] = sql; XBase.Business.Office.SellManager.SellSendBus.UpdatestrSql(listADD); } catch (Exception ex) { continue; } } } catch (Exception ex) { this.lbl_resultReceivable.Text = "导入失败" + ex.ToString(); return; } } public static DataSet ToDataTable(string filePath) { string connStr = ""; string fileType = System.IO.Path.GetExtension(filePath); if (string.IsNullOrEmpty(fileType)) return null; if (fileType == ".xls") connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null; OleDbDataAdapter da = null; DataTable dtSheetName = null; DataSet ds = new DataSet(); try { // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); // 获取数据源的表定义元数据 string SheetName = ""; dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); // 初始化适配器 da = new OleDbDataAdapter(); for (int i = 0; i < dtSheetName.Rows.Count; i++) { SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"]; if (!SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$")) { continue; } //sql_F = "Select * FROM [{0}] "; if (SheetName.Contains("应付报表$")) { sql_F = "Select * FROM [{0}] where F3 <> '' and F6 <> '已付金额' "; } da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn); DataSet dsItem = new DataSet(); da.Fill(dsItem, SheetName); ds.Tables.Add(dsItem.Tables[0].Copy()); } } catch (Exception ex) { } finally { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); da.Dispose(); conn.Dispose(); } } return ds; } } 查询数据的sql语句： SELECT a.SalesOrderNo, CASE a.ExtField1 WHEN 1 THEN '采购' WHEN 2 THEN '销售' ELSE '委外' END AS type, b.CustName, a.InvoiceNum, a.ComSendMoney, a.ReceivedPrepaid, isnull(null,0) as fk FROM officedba.AccountsRP a ,officedba.ProviderInfo b WHERE a.IsAll = 1 and a.Dept=b.ID and a.Status=1
