asp.net中打开Excel上传文件,读取数据的方法

代码:

    /// <summary>
    /// 打开Excel文件
    /// </summary>
    /// <param name="ExcelFileName">文件名</param>
    private void OpenExcelFile(string ExcelFileName)
    {
        Excel.Application App = new Excel.Application();
        if (App == null)
        {
            return;  //Excel尚未安装
        }
        Excel.Workbook workbook = App.Workbooks.Open(@ExcelFileName,
            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);
        CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        try
        {
            App.Visible = false;
            App.DisplayAlerts = false;
            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            ProcessExcelData(workbook);
        }
        finally
        {
            App.Quit();
            Marshal.ReleaseComObject(workbook);
            while (Marshal.ReleaseComObject(App) > 0) ;
            workbook = null;
            App = null;
            GC.Collect();
            Thread.CurrentThread.CurrentCulture = CurrentCI;
        }
    }

    /// <summary>
    /// 提取Excel表格数据
    /// </summary>
    /// <param name="InputWorkbook"></param>
    /// <returns></returns>
    private Excel.Workbook ProcessExcelData(Excel.Workbook InputWorkbook)
    {
        Excel.Sheets sheets = InputWorkbook.Worksheets;
        Excel.Worksheet DataSheet = null;
        foreach (Worksheet sheet in sheets)
        {
            if (sheet.Name.ToLower() == "sheet1")
            {
                DataSheet = sheet;
                break;
            }
        }
        if (null == DataSheet)
        {
            return InputWorkbook;
        }

        库存批次表 Bll = new 库存批次表();
        库存批次表Info Model;
        object TmpObj;
        Excel.Range FieldsRange = DataSheet.get_Range("A2", "K7");
        System.Array FieldNames = (System.Array)FieldsRange.Formula;
        for (int i = 0; i < FieldNames.GetLength(0); i++)
        {
            Model = new 库存批次表Info();

            TmpObj = FieldNames.GetValue(i + 1, 1);
            Model.货品ID = Convert.ToDecimal(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 2);
            Model.货品编码 = Convert.ToDecimal(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 3);
            Model.批次号 = Convert.ToString(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 4);
            Model.入库日期 = Convert.ToDateTime(TmpObj.ToString());

            TmpObj = FieldNames.GetValue(i + 1, 5);
            Model.入库单号 = Convert.ToString(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 6);
            Model.入库信息 = Convert.ToString(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 7);
            Model.生产日期 = Convert.ToDateTime(TmpObj.ToString());

            TmpObj = FieldNames.GetValue(i + 1, 8);
            Model.过期日期 = Convert.ToDateTime(TmpObj.ToString());

            TmpObj = FieldNames.GetValue(i + 1, 9);
            Model.入库数量 = Convert.ToDecimal(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 10);
            Model.出库数量 = Convert.ToDecimal(TmpObj);

            TmpObj = FieldNames.GetValue(i + 1, 11);
            Model.结存数量 = Convert.ToDecimal(TmpObj);

            Bll.Insert(Model);
        }
        FieldsRange = null;
        FieldNames = null;
        return InputWorkbook;
    }

 

调用方法:

OpenExcelFile(Server.MapPath(@"../UploadFile/")+strFileURL);