你带酒来,我有故事

利用NPOI导入导出Excel

:: 代码生涯 二十画生 1564℃ 0评论

利用NPOI导入导出Excel

 

数据来源不确定,但只要有表头的excel,就可以轻松导入。

思路:excel数据 ——-> Json数据 ———> List<T>

写了两个通用辅助函数 ReadDataFromExcel 与 Export2Excel 用于excel 的导入导出

        /// <summary>
        /// 读取excel数据到List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        private List<T> ReadDataFromExcel<T>(string excelPath)
        {
            try
            {
                using (FileStream fs = System.IO.File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    IWorkbook mybook = null;
                    if (Path.GetExtension(excelPath) == ".xlsx")
                    {
                        mybook = new XSSFWorkbook(fs);
                    }
                    else if (Path.GetExtension(excelPath) == ".xls")
                    {
                        mybook = new HSSFWorkbook(fs);
                    }

                    ISheet mysheet = mybook.GetSheetAt(0);//mybook.GetSheet("Sheet1");


                    JArray ja = new JArray();
                    int titleRowIndex = 0;
                    IRow titleRow = mysheet.GetRow(titleRowIndex);
                    bool isTitleFlag = true;
                    do
                    {
                        // 认为第一个所有列不为空的为表头
                        for (int j = 0; j < titleRow.Cells.Count; j++)
                        {
                            if (string.IsNullOrEmpty((titleRow.Cells)[j].ToString()))
                            {
                                isTitleFlag = false;
                                titleRowIndex++;
                                titleRow = mysheet.GetRow(titleRowIndex);
                                continue;
                            }
                        }

                        isTitleFlag = true;
                    }
                    while (!isTitleFlag);

                    for (int i = ++titleRowIndex; i <= mysheet.LastRowNum; i++)
                    {
                        IRow row = mysheet.GetRow(i);
                        JObject json = new JObject();
                        for (int j = 0; j < titleRow.Cells.Count; j++)
                        {
                            ICell cell = (row == null) ? null : row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK);
                            json.Add((titleRow.Cells)[j].ToString(), cell == null ? string.Empty : cell.ToString());
                        }
                        ja.Add(json);

                    }
                    fs.Close();

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

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="savePath"></param>
        /// <returns></returns>
        private bool Export2Excel<T>(List<T> data, string savePath)
        {
            try
            {
                IWorkbook mybook = new XSSFWorkbook();
                ISheet mysheet = mybook.CreateSheet("Sheet1");
                PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
                int rowIndex = 0;
                int colIndex = 0;
                IRow myrow = mysheet.CreateRow(rowIndex++);

                if (props.Count > 0)
                {
                    // 输出标头           
                    foreach (PropertyDescriptor prop in props)
                    {
                        myrow.CreateCell(colIndex++).SetCellValue(prop.DisplayName);
                    }

                    // 输出内容
                    foreach (T item in data)
                    {
                        myrow = mysheet.CreateRow(rowIndex++);
                        colIndex = 0;
                        foreach (PropertyDescriptor prop in props)
                        {
                            myrow.CreateCell(colIndex++).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
                        }
                    }
                }
                else // 针对 T 为 【Object】 类型
                {
                    JArray ja = JArray.FromObject(data);
                    JObject jo = JObject.FromObject(ja.First);
                    IEnumerable<JProperty> props2 = jo.Properties();

                    // 输出标头           
                    foreach (JProperty prop in props2)
                    {
                        myrow.CreateCell(colIndex++).SetCellValue(prop.Name);
                    }

                    // 输出内容
                    foreach (JObject item in ja)
                    {
                        myrow = mysheet.CreateRow(rowIndex++);
                        colIndex = 0;
                        foreach (JProperty prop in item.Properties())
                        {
                            if (string.IsNullOrEmpty(prop.Value.ToString()))
                            {
                                myrow.CreateCell(colIndex++).SetCellType(CellType.Blank);
                            }
                            else
                            {
                                myrow.CreateCell(colIndex++).SetCellValue(prop.Value.ToString());
                            }
                        }
                    }


                }


                FileStream fileCreate = new FileStream(savePath, FileMode.Create);
                mybook.Write(fileCreate);
                fileCreate.Close();

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


        }

 

 

源码下载

转载请注明:二十画生 » 利用NPOI导入导出Excel

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

表情

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

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. 源码项目中excel文档请进行脱敏处理,避免相关人员个人资料的泄露!!!
    匿名2020-01-28 16:56 回复
    • 好的,谢谢
      泥塘塘主2020-03-11 17:57 回复