.net中将excel导入sqlserver(worksheet专版)

写给默默奋斗的程序员们...worksheet专版   

 

 

        命名空间导入using Excel;BIN中在com组件中加入MS EXCEL5.0组件
        string path = Request.MapPath("upfile/");
        string filepath = path + "xx.xls";
        bool j = false;
        Application ObjExcel = new Application();
        Workbook ObjWorkBook;
        Worksheet ObjWorkSheet = null;
        ObjWorkBook = ObjExcel.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        ObjWorkSheet = (Worksheet)ObjWorkBook.Sheets[1];
        for (int i = 0; i < ObjWorkSheet.UsedRange.Rows.Count; i++)
        {
            ExcelInfo excelInfo = new ExcelInfo(); 
                 Range cellB = (Range)ObjWorkSheet.Cells[i + 2 , 1];

            string B = cellB.Value2.ToString();

            if (B != "")

            {

                B = B.Substring(0, B.Length - 1);

             // B列是时间格式，但是不知道为什么，这种方式只能读取到最后的天数，所以在天数后加了一个特殊符号，所以要进行截取，及excel日期格式为1900-1-1`

                try

                {

                    excelInfo.B = Convert.ToDateTime(B);

                }

                catch (Exception ex)

                {

                    this.Page.RegisterStartupScript("false", "<script>alert('时间格式不正确');</script>");

                    return;

                }

                Range cellC = (Range)ObjWorkSheet.Cells [i + 2, 2];

                string C = cellC.Value2.ToString();

                excelInfo.C = C;

                Range cellD = (Range)ObjWorkSheet.Cells [i + 2, 3];

                string D = cellD.Value2.ToString();

                excelInfo.D = D;

                j=shangchuanInfo.worksuccess(excelInfo);

            }

            else
            {
                break;
            }
        }
        if (j)
            this.Page.RegisterStartupScript("true", "<script>alert('上传数据成功');</script>");
        else
        {
            this.Page.RegisterStartupScript("false", "<script>alert('无数据');</script>");
            return;
        }
        ObjExcel.Workbooks.Close();


    CS文件中方法
    /// <summary>
    /// 插入excel数据
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    public static Boolean worksuccess(ExcelInfo excelInfo)
    {
        int ok = 0;
        string str = "insert into TB_excel(b,c,d)values('" + excelInfo.B + "','" + excelInfo.C + "','" + excelInfo.D + "')";
        using (SqlConnection sqlCon = new SqlConnection(Database.conStr))
        {
            using (SqlCommand sqlCmd = new SqlCommand(str, sqlCon))
            {
                sqlCon.Open();
                ok = sqlCmd.ExecuteNonQuery();
                return ok > 0;
            }
        }
    }