本站木有非理性广告和有害内容,请大度地将本站加入广告屏蔽白名单吧~~~

EPPlus读写Excel

:: 代码生涯 泥塘塘主 672℃ 0评论

这篇文章介绍了NPOI读写Excel,这里介绍EPPlus读写Excel,EPPlus只能读写xlsx,不能读写xls。

帮助类:

public class EPPlusHelper
    {
        /// <summary>
        /// 读取excel文件到List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelFilePath"></param>
        /// <returns></returns>
        public static List<T> ReadExcel<T>(string excelFilePath) where T : class
        {
            JArray ja = new JArray();
            FileStream fs = System.IO.File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            try
            {
                
                ExcelPackage package = new ExcelPackage(fs);
                ExcelWorksheet sheet = package.Workbook.Worksheets[1]; // 默认取将一个sheet

                for (int row = sheet.Dimension.Start.Row + 1; row <= sheet.Dimension.End.Row; row++)
                {

                    JObject json = new JObject();
                    for (int col = sheet.Dimension.Start.Column; col <= sheet.Dimension.End.Column; col++)
                    {
                        try
                        {
                            object p = sheet.GetValue(sheet.Dimension.Start.Row, col);
                            string propertyName = p == null ? string.Empty : p.ToString().Trim(); // 取第一行作为表头
                            object v = sheet.GetValue(row, col);
                            string value = v == null ? string.Empty : v.ToString().Trim();
                            if (!propertyName.Equals(string.Empty))
                            {
                                if (typeof(T).GetProperties().Length <= 1 && value.Equals(string.Empty))
                                {
                                    continue;
                                }

                                json.Add(propertyName, value);
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                    if (json.Count > 0)
                    {
                        ja.Add(json);
                    }



                }

                fs.Close();

                return ja.ToObject<List<T>>();
            }
            catch(Exception ex)
            {
                fs.Close();
                return null;
            }


            
        }

        /// <summary>
        /// 将List<T>写入到excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelFilePath"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static bool SaveExcel<T>(string excelFilePath, List<T> data)
        {
            try
            {
                var fi = new FileInfo(excelFilePath);
                if (fi.Exists)
                    fi.Delete();

                using (ExcelPackage package = new ExcelPackage(fi))
                {
                    
                    ExcelWorksheet sheet = package.Workbook.Worksheets.Add("公司基本信息");
                    sheet.Cells.LoadFromCollection(data, true);
                    package.Save();
                   
                }

                    return true;
            }
            catch(Exception ex)
            {
                return false;
            }
        }



    }

 

调用例子:

string excelFilePath = @"files/重抓公司.xlsx";
            List<ExcelDataModel> o = EPPlusHelper.ReadExcel<ExcelDataModel>(excelFilePath);
            List<CompanyInfoModel> companys = new List<CompanyInfoModel>();
            foreach (ExcelDataModel model in o)
            {
                try
                {
                    CompanyInfoModel company = CrawlCompanyInfo(model.单位);
                    if(string.IsNullOrEmpty(company.companyName))
                    {
                        company.companyName = model.单位;
                        
                    }

                    companys.Add(company);
                }
                catch (Exception ex)
                {
                    AppendExceptionMessage(model.单位+"抓取失败", ex);
                }
            }

            string saveFilePath = @"files/公司信息.xlsx";
            if (EPPlusHelper.SaveExcel(saveFilePath,companys))
            {
                AppendMessage("公司信息保存成功");
            }

 

 

 

 

转载请注明:泥塘·物语 » EPPlus读写Excel

喜欢 (3)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址