基于asp.net的excel导入导出

新建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