解析Excel

解析上传的Excel文件。

需要引入第三方程序集【下载地址】

  
      /// <summary>
        /// 解析Excel表格
        /// </summary>
        /// <param name="virthualPath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static int AnalysisExcel(string virthualPath, out string msg)
        {
            try
            {
                var filePath = HttpContext.Current.Server.MapPath(virthualPath);
                if (!File.Exists(filePath))
                {
                    msg = "找不到上传的Excel文件，请重新上传！";
                    return 0;
                }

                var fileInfo = new FileInfo(filePath);
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

                    int colStart = worksheet.Dimension.Start.Column;
                    int colEnd = worksheet.Dimension.End.Column;
                    int rowStart = worksheet.Dimension.Start.Row;
                    int rowEnd = worksheet.Dimension.End.Row;
                    List<TempClass> excelList = new List<TempClass>();

                    //从第二行开始
                    for (int row = rowStart + 1; row <= rowEnd; row++)//从第二行开始，第一行是表头
                    {
                        TempClass item = new TempClass();

                        item.Id = worksheet.Cells[row, 1].GetValue<int>();
                        item.Name = worksheet.Cells[row, 2].GetValue<string>();
                        item.Number = worksheet.Cells[row, 3].GetValue<int>();
                        excelList.Add(item);
                    }

                    if (excelList.Count <= 0)
                    {
                        msg = "您还没有新创建的行！";
                        DeleteExcel(filePath);
                        return 0;
                    }

                  try
                    {
                        int result = DAL.DealExcel(excelList);
                        if (result > 0)
                        {
                            msg = "导入成功";
                        }
                        else
                        {
                            msg = "导入失败";
                        }
                    }
                    catch (Exception ex)
                    {
                        msg = "导入数据库时出错";
                        DeleteExcel(filePath);
                        return 0;
                    }

                    return 0;
                }
            }
            catch (Exception ex)
            {
                msg = "无法解析该Excel文件！";
            }
            return 0;
        }

        /// <summary>
        /// 删除Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        public static void DeleteExcel(string filePath)
        {
            if (File.Exists(filePath))
                File.Delete(filePath);
        }