ASP.NET操作Excel表格

1.OleDB

using System;
using System.Collections.Generic;
using System.Web.UI;
using System.IO;
using System.Collections;
using System.Data.OleDb;
using System.Data;
using sunyiding.callcenterProject.SFL;

namespace sunyiding.callcenterProject.UIL.Tmcustmor
{
    public partial class Input : System.Web.UI.Page
    {
        string filepath = "";
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button4_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(FileUpload1.FileName))
            {
                ClientScript.RegisterStartupScript(typeof(Page), "aa", "alert('请选择导入文件！')", true);
                return;
            }
            filepath = FileUpload1.PostedFile.FileName.ToLower().ToString();
            if (Path.GetExtension(filepath) != ".xls" && Path.GetExtension(filepath) != ".xlsx")
            {
                ClientScript.RegisterStartupScript(typeof(Page), "aa", "alert('请选择excel文件！')", true);
                return;
            }
            if (Directory.Exists(Server.MapPath("~/Temp")) == false)//判断文件夹是否存在,若不存在则创建
            {
                Directory.CreateDirectory(Server.MapPath("~/Temp"));
            }
            string UploadFilePath = Server.MapPath("~/Temp\\");
            string fullName = FileUpload1.PostedFile.FileName;
            string newName = DateTime.Now.Ticks.ToString() + fullName.Substring(fullName.LastIndexOf("."));
            FileUpload1.SaveAs(UploadFilePath + newName);
            dataInput(UploadFilePath + newName);
            File.Delete(UploadFilePath + newName);
        }
        private void dataInput(string parm)
        {
            if (File.Exists(parm))
            {
                inputdata(parm);
            }
            else
            {
                Response.Write("<script language=javascript>alert('导入失败，请重新操作！');</script>");
                return;
            }
        }
        private void inputdata(string filename)
        {
            try
            {
                ArrayList al = new ArrayList();
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=2;'";
                if (Path.GetExtension(filepath) == ".xls" || Path.GetExtension(filepath) == ".xlsx")
                {
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    DataTable sheetNames = conn.GetOleDbSchemaTable
                        (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    conn.Close();
                    foreach (DataRow dr in sheetNames.Rows)
                    {
                        al.Add(dr[2]);
                    }
                    if (al.Count > 0)
                    {
                        for (int i = 0; i < al.Count; i++)
                        {
                            DataSet myDataSet = new DataSet();   //得到自己的DataSet对象     
                            string StrSql = "select   *   from   [" + al[i] + "]";
                            conn.Open();
                            OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, conn);   //创建一个   DataSet对象     
                            myCommand.Fill(myDataSet, "[" + al[i] + "]");
                            myCommand.Dispose();
                            DataTable DT = myDataSet.Tables["[" + al[i] + "]"];
                            conn.Close();
                            if (DT.Rows.Count != 1 || DT.Columns.Count != 1)
                            {
                                updatadb(DT);
                            }
                        }
                    }
                    else
                    {
                        Response.Write("<script language=javascript>alert('文件打开失败，请检查原文件或将原文件另存为新的excel文件，重新操作！');</script>");
                        return;
                    }
                }
                else
                {
                    Response.Write("<script language=javascript>alert('文件打开失败，请检查原文件或将原文件另存为新的excel文件，重新操作！');</script>");
                    return;
                }
            }
            catch
            {
            }
        }
        private void updatadb(DataTable ddt)
        {
            List<string> li = new List<string>();
            for (int i = 0; i < ddt.Rows.Count; i++)
            {
                int isnull = 0;
                for (int k = 0; k < ddt.Columns.Count; k++)
                {
                    if (!ddt.Rows[i].IsNull(k))
                    {
                        isnull = 1;
                    }
                }
                if (isnull == 1)
                {
                    li.Add("insert into  tb_customer(Number,TheName,Gender,DateOfBirth,TheHouseholdRegistrationCategory,ThePermanentAddress,IdCardNumber,ContactPhoneNumber,OtherContactInformation,BelongsToTheStreets,BelongsToTheStreetCall,EmergencyContact,RelationshipWithTheOwner,EmergencyContactPhone,TermLivingIn,AssessmentLevel,IdentityCategories,FixedServiceIntention) values ('" + ddt.Rows[i]["优惠券编号"].ToString() + "','" + ddt.Rows[i]["姓名"].ToString() + "','" + ddt.Rows[i]["性别"].ToString() + "','" + ddt.Rows[i]["出生日期"].ToString() + "','" + ddt.Rows[i]["户籍类别"].ToString() + "','" + ddt.Rows[i]["常住家庭地址"].ToString() + "','" + ddt.Rows[i]["身份证号码"].ToString() + "','" + ddt.Rows[i]["固定电话"].ToString() + "','" + "手机号码：" + ddt.Rows[i]["手机号码"].ToString() + "，   老人手机号码：" + ddt.Rows[i]["老人手机号码"].ToString() + "','" + ddt.Rows[i]["分区队列（所属分区）"].ToString() + "','" + ddt.Rows[i]["街道电话"].ToString() + "','" + ddt.Rows[i]["紧急联系人姓名"].ToString() + "','" + ddt.Rows[i]["与机主关系"].ToString() + "','" + ddt.Rows[i]["紧急联系人电话"].ToString() + "','" + ddt.Rows[i]["长期居住状况"].ToString() + "','" + ddt.Rows[i]["评估等级"].ToString() + "','" + ddt.Rows[i]["身份类别"].ToString() + "','" + ddt.Rows[i]["固定服务意向"].ToString() + "')");
                }
            }
            int result = SQLHelp.ExecuteSqlTran(li);
            if (result == 0)
            {
            }
            else
            {
                Label1.Text = "导入成功!";
            }
        }
    }
}

    

2. Microsoft.Office.Interop.Excel.dll

//读取EXCEL的方法    (用范围区域读取数据)     
       private void OpenExcel(string strFileName)  
       {  
           object missing = System.Reflection.Missing.Value;  
           Application excel = new Application();//lauch excel application     
           if (excel == null)  
           {  
               Response.Write("<script>alert('Can't access excel')</script>");  
           }  
           else  
           {  
               excel.Visible = false; excel.UserControl = true;  
               // 以只读的形式打开EXCEL文件     
               Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,  
                missing, missing, missing, true, missing, missing, missing, missing, missing);  
               //取得第一个工作薄     
               Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);  
               //取得总记录行数    (包括标题列)     
               int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数     
               //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数     
               //取得数据范围区域   (不包括标题列)       
               Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);   //item     
               Range rng2 = ws.Cells.get_Range("K2", "K" + rowsint);  //Customer     
               object[,] arryItem = (object[,])rng1.Value2;   //get range's value     
               object[,] arryCus = (object[,])rng2.Value2;  
               //将新值赋给一个数组     
               string[,] arry = new string[rowsint - 1, 2];  
               for (int i = 1; i <= rowsint - 1; i++)  
               {  
                   //Item_Code列     
                   arry[i - 1, 0] = arryItem[i, 1].ToString();  
                   //Customer_Name列     
                   arry[i - 1, 1] = arryCus[i, 1].ToString();  
               }  
               Response.Write(arry[0, 0] + "   /   " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + "   /   " + arry[rowsint - 2, 1]);  
           }  
           excel.Quit(); excel = null;  
           Process[] procs = Process.GetProcessesByName("excel");  
           foreach (Process pro in procs)  
           {  
               pro.Kill();//没有更好的方法,只有杀掉进程     
           }  
           GC.Collect();  
       }  

3.NPOI