NPOI导出excel文档

最新推荐文章于 2024-06-29 08:55:53 发布
爱摸鱼的猫毛
最新推荐文章于 2024-06-29 08:55:53 发布
阅读量199
点赞数
文章标签： excel
本文链接：https://blog.csdn.net/llwr1314/article/details/129296429
版权
导出excel
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using DevieceSystem.Model.Dtos;
using Microsoft.AspNetCore.Hosting;
using MySqlX.XDevAPI.Common;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using static DevieceSystem.Model.PointInfo;

namespace ModuleChartInfo.Entity
{
    public class NpoiExcelOperationService
    {
        private static IHostingEnvironment _environment;

        public NpoiExcelOperationService(IHostingEnvironment iEnvironment)
        {
            _environment = iEnvironment;
        }

        public NpoiExcelOperationService() { }


        /// <summary>
        /// Excel数据导出简单示例
        /// </summary>
        /// <param name="resultMsg">导出结果</param>
        /// <param name="excelFilePath">保存excel文件路径</param>
        /// <returns></returns>
        public bool ExcelDataExport( string excelFilePath)
        {
            var result = true;
            excelFilePath = "";
            //resultMsg = "successfully";
            //Excel导出名称
            string excelName = "人才培训课程表";
            try
            {
                //首先创建Excel文件对象
                var workbook = new HSSFWorkbook();

                //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
                var sheet = workbook.CreateSheet("人才培训课程表");

                //sheet.DefaultColumnWidth = 20;//默认列宽

                sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）

                #region table 表格内容设置

                #region 标题样式

                //设置顶部大标题样式
                var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
                    FontUnderlineType.None, FontSuperScript.None, false);

                //第一行表单
                var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);

                var cell = row.CreateCell(0);
                //合并单元格 例：第1行到第2行 第3列到第4列围成的矩形区域

                //TODO:关于Excel行列单元格合并问题
                /**
                  第一个参数：从第几行开始合并
                  第二个参数：到第几行结束合并
                  第三个参数：从第几列开始合并
                  第四个参数：到第几列结束合并
                **/
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
                sheet.AddMergedRegion(region);

                cell.SetCellValue("人才培训课程表");//合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
                cell.CellStyle = cellStyleFont;

                //二级标题列样式设置
                var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
                FontUnderlineType.None, FontSuperScript.None, false);

                //表头名称
                var headerName = new[] { "课程类型", "序号", "日期", "课程名称", "内容概要", "讲师简介" };

                row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行
                for (var i = 0; i < headerName.Length; i++)
                {
                    cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);

                    //设置单元格宽度
                    if (headerName[i] == "讲师简介" || headerName[i] == "内容概要")
                    {
                        sheet.SetColumnWidth(i, 10000);
                    }
                    else

                    {
                        sheet.SetColumnWidth(i, 5000);
                    }

                }
                #endregion


                #region 单元格内容信息

                //单元格边框样式
                var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);

                //左侧列单元格合并 begin
                //TODO:关于Excel行列单元格合并问题（合并单元格后，只需对第一个位置赋值即可）
                /**
                  第一个参数：从第几行开始合并
                  第二个参数：到第几行结束合并
                  第三个参数：从第几列开始合并
                  第四个参数：到第几列结束合并
                **/
                CellRangeAddress leftOne = new CellRangeAddress(2, 7, 0, 0);

                sheet.AddMergedRegion(leftOne);

                CellRangeAddress leftTwo = new CellRangeAddress(8, 11, 0, 0);

                sheet.AddMergedRegion(leftTwo);

                //左侧列单元格合并 end

                var currentDate = DateTime.Now;

                string[] curriculumList = new[] { "艺术学", "设计学", "材料学", "美学", "心理学", "中国近代史", "管理人员的情绪修炼", "高效时间管理", "有效的目标管理", "沟通与协调" };

                int number = 1;

                for (var i = 0; i < 10; i++)
                {
                    row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                    switch (number)
                    {
                        case 1:
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "公共类课程");
                            break;
                        case 7:
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "管理类课程");
                            break;
                    }

                    //创建单元格列公众类课程
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, number.ToString());
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, currentDate.AddDays(number).ToString("yyyy-MM-dd"));
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, curriculumList[i]);
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, "提升，充实，拓展自己综合实力");
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, "追逐时光_" + number + "号金牌讲师！");

                    number++;
                }
                #endregion

                #endregion

                string folder = DateTime.Now.ToString("yyyyMMdd");


                //保存文件到静态资源文件夹中（wwwroot）,使用绝对路径
                var uploadPath = _environment.WebRootPath + "/UploadFile/" + folder + "/";

                //excel保存文件名
                string excelFileName = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

                //创建目录文件夹
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }

                //Excel的路径及名称
                string excelPath = uploadPath + excelFileName;

                //使用FileStream文件流来写入数据（传入参数为：文件所在路径，对文件的操作方式，对文件内数据的操作）
                var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);

                //向Excel文件对象写入文件流，生成Excel文件
                workbook.Write(fileStream);

                //关闭文件流
                fileStream.Close();

                //释放流所占用的资源
                fileStream.Dispose();

                //excel文件保存的相对路径，提供前端下载
                var relativePositioning = "/UploadFile/" + folder + "/" + excelFileName;

                excelFilePath = relativePositioning;
            }
            catch (Exception e)
            {
                result = false;
                //resultMsg = e.Message;
            }
            return result;
        }


        public static bool ExportExcel(string exportPath, List<PointInfoDto> pts,out string errMsg)
        {
            errMsg = "";
            var result = true;
            //Excel导出名称
            string excelName = "车机耐久测试";
            try
            {
                //首先创建Excel文件对象
                var workbook = new XSSFWorkbook();

                //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)

                //sheet.DefaultColumnWidth = 20;//默认列宽
                //sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）
                CreateSheet("电流", workbook, pts.Where(t=>t.DataType.Equals(DataTypeEnum.Electricity)).ToList());
                CreateSheet("电压",workbook, pts.Where(t => t.DataType.Equals(DataTypeEnum.Voltage)).ToList());
                CreateSheet("温度",workbook, pts.Where(t => t.DataType.Equals(DataTypeEnum.Temperature)).ToList());
                string excelPath = exportPath;
                var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                workbook.Write(fileStream);
                fileStream.Close();

                //释放流所占用的资源
                fileStream.Dispose();
            }
            catch (Exception e)
            {
                result = false;
                errMsg = $"{e.Message},{e.StackTrace}";
            }
            return result;
        }

        private static ISheet CreateSheet(string sheetName, XSSFWorkbook workbook, List<PointInfoDto> pts) 
        {
            if (pts==null)
            {
                return null;
            }
            //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
            var sheet = workbook.CreateSheet(sheetName);
            //sheet.DefaultColumnWidth = 20;//默认列宽
            sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效（非必须）
            #region table 表格内容设置
            #region 标题样式
            //设置顶部大标题样式
            var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
                FontUnderlineType.None, FontSuperScript.None, false);
            //第一行表单
            var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);
            var cell = row.CreateCell(0);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(region);
            cell.SetCellValue("车机耐久测试");//合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
            cell.CellStyle = cellStyleFont;
            //二级标题列样式设置
            var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
            FontUnderlineType.None, FontSuperScript.None, false);
            //表头名称
            var headerName = new[] { "序号", "数据类型", "数值", "通道", "创建时间" };
            row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行
            for (var i = 0; i < headerName.Length; i++)
            {
                cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);
                sheet.SetColumnWidth(i, 5000);
            }

            #endregion


            #region 单元格内容信息
            //单元格边框样式
            var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);
            var currentDate = DateTime.Now;
            int number = 1;

            for (var i = 0; i < pts.Count; i++)
            {
                row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, number.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, pts[i].DataType.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, pts[i].X.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, pts[i].Channel.ToString());
                cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, pts[i].CreateTime.ToString());
                number++;
            }

            #endregion

            #endregion
            return sheet;
        }
    }
}