将Excel上传到服务器，读取Excel数据写入到数据库

方法一：使用LinqToExcel.dll

1、上传Excel文件到服务器上

            HttpPostedFileBase mypostfile = Request.Files[0];            string myfile = MyPathEx.myfileName(@"事业单位准考证上传");            myfile = myfile + ".xlsx";            myfile = MyPathEx.saveFile(mypostfile, Server, @"/ImportFile/", myfile);//保存上载文件的内容,并返回上传的路径+文件名            ExamineeOp_1.SaveExcelToDB(myfile);

 

        /// <summary>        /// 返回一个当有日期组成的文件名        /// </summary>        /// <param name="myPerFix">可能要加的文件名前缀</param>        /// <returns></returns>        public static string myfileName(string myPerFix = "")        {            string mys = DateTime.Now.ToString("yyyyMMddHHmmss");            return myPerFix + mys;        }

 

 

2、读取Excel文件

        /// <summary>        /// 导入设置好的考生信息Excel数据至数据库        /// </summary>        /// <param name="excelfile">本地Excel数据文件</param>        /// <param name="mysheet"></param>        public static void SaveExcelToDB(string excelfile, string mysheet = "sheet1")        {            //  string myfile = MyPathEx.myfileName(@"/ImportFile/") +excelfile;            var excelv = new ExcelQueryFactory(excelfile);            IEnumerable<string> Mysh = excelv.GetWorksheetNames();           //下载 AccessDatabaseEngine           //http://www.microsoft.com/zh-cn/download/details.aspx?id=13255            var result = (from c in excelv.Worksheet<ExamineeC_1_Dto>(Mysh.First<string>()) select c).ToList();            DalFactory.GetManager().GetProvider<IExaminee_1_Dal>(mydal_link).ExcelImportSave(result);        }

 

 

    public class ExamineeC_1_Dto    {        public int 报名序号 { get; set; }        public string 姓名 { get; set; }        public string 身份证号 { get; set; }        public string 报考单位 { get; set; }        public string 报考岗位 { get; set; }        public string 报考专业 { get; set; }        public string 最高学历 { get; set; }        public string 报考职位 { get; set; }        public string 准考证号 { get; set; }        public string 考试科目A { get; set; }        public string 考试地点A { get; set; }        public string 考试日期A { get; set; }        public string 考试时间A { get; set; }        public string 考场号A { get; set; }        public string 座位号A { get; set; }        public string 考试科目B { get; set; }        public string 考试地点B { get; set; }        public string 考试日期B { get; set; }        public string 考试时间B { get; set; }        public string 考场号B { get; set; }        public string 座位号B { get; set; }    }

 

3、更新数据库

        public void ExcelImportSave(List<ExamineeC_1_Dto> myexcel)        {            using (var ctx = new PersonnelExamEntities())            {                var updatas = ctx.Ex_Examinee.ToList();                foreach (var item in updatas)                {                    var impd = myexcel.Where(o => o.报名序号 == item.ExamineeID).FirstOrDefault();                    if (impd != null)                    {                        item.ExamineeCode = impd.准考证号;                        item.Item_6 = impd.考试科目A;                        item.ExamAddress = impd.考试地点A;                        item.ExamDate = impd.考试日期A;                        item.ExamTime = impd.考试时间A;                        item.ExamPost = impd.考场号A;                        item.ExamPostNumber = impd.座位号A;                        item.Item_8 = impd.考试科目B;                        item.ExamAddress1 = impd.考试地点B;                        item.ExamDate1 = impd.考试日期B;                        item.ExamTime1 = impd.考试时间B;                        item.ExamPost1 = impd.考场号B;                        item.ExamPostNumber1 = impd.座位号B;                    }                }                ctx.SaveChanges();            }        }

 

方法二：Excel.Application

        <table cellpadding="1" cellspacing="1" border="1" style="width: 50%; text-align: center">            <tr>                <td>                    选择文件                </td>                <td style="padding-left: 10px; text-align: left">                   <input id="excelCB" type="file" runat="server" style="cursor: hand" />                 </td>            </tr>            <tr>                <td>                    数据来源                </td>                <td style="padding-left: 10px; text-align: left">                    <asp:RadioButtonList ID="radioLX" runat="server">                        <asp:ListItem Text="湖北银行"  Value="0"></asp:ListItem>                        <asp:ListItem Text="银联" Value="1" Selected="True"></asp:ListItem>                        <asp:ListItem Text="手机" Value="2"></asp:ListItem>                    </asp:RadioButtonList>                </td>            </tr>            <tr>                <td>                    账单日期                </td>                <td style="padding-left: 10px; text-align: left">                     <input id="txtTime" type="text" style="width: 115px" onclick="WdatePicker({startDate:'%y-%M-01',dateFmt:'yyyy-MM-dd',alwaysUseStartDate:true})" />                     <%-- <asp:TextBox ID="dd" runat="server"></asp:TextBox>--%>                </td>            </tr>            <tr>                <td colspan="2">                    <asp:Button ID="btnSubmit" runat="server" Text="提交" onclick="btnSubmit_Click" />                </td>            </tr>        </table>

 

        protected void BindUnionPay()        {            GC.Collect();            string strSavePath, strSQL, strFileName, strFileExt;            string A1, A2, A3, A4, A5, A6, A7, A8;            strSavePath = Request.ServerVariables[3] + "Uploads/";//上传文件保存的物理文件夹            strFileName = Path.GetFileName(excelCB.PostedFile.FileName);//上传文件文件名            strFileExt = Path.GetExtension(strFileName);//上传文件扩展名            int intRow = 2;//Excel开始读取的行数            if (strFileExt.ToLower() == ".xls")//如果是Excel            {                strSavePath = strSavePath + "银联" + DateTime.Now.ToShortDateString() + ".xls";                Excel.Application excelApp = new Excel.Application();//申明Excel应用程序                Excel.Workbook excelWorkBook;//申明Excel工作簿                Excel.Worksheet excelWorkSheet;//申明工作表                strSQL = "truncate table tbl_UnionPay";//清空sql数据库中的tbl_UnionPay表                object[] obj = {};                SqlHelper.ExecuteNonQuery(strSQL, obj);                try                {                    if (File.Exists(strSavePath))//如果文件已经上传，删除文件                    {                        FileInfo fi = new FileInfo(strSavePath);                        if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)//判断文件是否只读，若只读，则去掉只读属性                            fi.Attributes = FileAttributes.Normal;                        File.Delete(strSavePath);                    }                    excelCB.PostedFile.SaveAs(strSavePath);//上传文件                    excelWorkBook = excelApp.Workbooks.Open(@strSavePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//打开Excel                    excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[1];//返回第1个工作表                    //循环读取数据                    while (Convert.ToString(excelWorkSheet.get_Range("A" + intRow, Missing.Value).Value) != "" && Convert.ToString(excelWorkSheet.get_Range("A" + intRow, Missing.Value).Value) != null)                    {                        A1 = Convert.ToString(excelWorkSheet.get_Range("A" + intRow, Missing.Value).Value);                        A2 = Convert.ToString(excelWorkSheet.get_Range("B" + intRow, Missing.Value).Value);                        A3 = Convert.ToString(excelWorkSheet.get_Range("C" + intRow, Missing.Value).Value);                        A4 = Convert.ToString(excelWorkSheet.get_Range("D" + intRow, Missing.Value).Value);                        A5 = Convert.ToString(excelWorkSheet.get_Range("E" + intRow, Missing.Value).Value);                        A6 = Convert.ToString(excelWorkSheet.get_Range("F" + intRow, Missing.Value).Value);                        A7 = Convert.ToString(excelWorkSheet.get_Range("G" + intRow, Missing.Value).Value);                        A8 = Convert.ToString(excelWorkSheet.get_Range("H" + intRow, Missing.Value).Value);                        //插入数据库中                        strSQL = "insert into tbl_UnionPay(fld_pid, fld_shh, fld_orderNumber, fld_amount, fld_tradeTime, fld_clearDate, fld_tradeCode, fld_responseCode)";                        strSQL += "values('" + A1 + "','" + A2 + "','" + A3 + "','" + A4 + "','" + A5 + "','" + A6 + "','" + A7 + "','" + A8 + "')";                        SqlHelper.ExecuteNonQuery(strSQL, obj);                        intRow++;                    }                    Response.Write("<script language='javascript'>alert('导入数据表成功');</script>");                }                catch (Exception ex)                {                    Response.Write(ex.Message.ToString());                    if (excelApp != null)                    {                        excelApp.DisplayAlerts = false;                        excelApp.Workbooks.Close();                        excelApp.Quit();                        KillProcess("EXCEL.EXE");                        GC.Collect();                        Response.Write("导入错误");                        return;                    }                }                finally                {                    if (excelApp != null)                    {                        excelApp.DisplayAlerts = false;                        excelApp.Workbooks.Close();                        excelApp.Quit();                        KillProcess("EXCEL.EXE");                        System.GC.Collect();                    }                }            }            else            {                Response.Write("<script language='javascript'>alert('文件类型不正确！')</script>");            }        }        private void KillProcess(string processName)        {            System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");            foreach (System.Diagnostics.Process p in process)            {                if (!string.IsNullOrEmpty(processName))                {                    try                    {                        p.Kill();                    }                    catch { }                }            }        }