C# 在不安装Office的情况下使用NPOI实现EXCEL导出

环境

VS2019+NPOI 2.6.0

导入NPOI包

NPOI.XSSF =》 Excel 2007(xlsx)格式读写库
实现数据组包并导出数据，传入参数和数据根据需要自己组包成DataTable

private void OriDataToExcel(List<SelectData> dataList)
        {
            if (dataList == null || dataList.Count <= 0)
            {
                MessageBox.Show("至少选择一条数据");
                return;
            }

            #region 组包DataTable数据
            List<double> angleKeys = new List<double>();
            foreach (SelectData data in dataList)
            {
                List<double> temp = data.PointDict.Keys.ToList();
                angleKeys.AddRange(temp);
                angleKeys = angleKeys.Distinct().ToList();
            }
            angleKeys.Sort();
            List<string> headString = new List<string>() {"雷达名称","频点(GHz)","频段","极化方向","角度范围","损耗补偿(dBm)"};
            foreach(double key in angleKeys)
            {
                headString.Add(key + "°");
            }
            //数据表
            DataTable dtHead = new DataTable();
            //表头
            foreach (string str in headString)
                dtHead.Columns.Add(str);
            //数据
            foreach (SelectData data in dataList)
            {
                List<object> list = new List<object>() 
                { 
                    data.RadarName,
                    data.FreqValue,
                    data.FreqSection,
                    data.Direction,
                    $"({data.StartAngle}°,{data.EndAngle}°)",
                    data.LossValue,
                };
                foreach(double key in angleKeys)
                {
                    double ang = 0;
                    if (data.PointDict.TryGetValue(key, out ang))
                        list.Add(ang);
                    else
                        list.Add("");
                }
                dtHead.Rows.Add(list.ToArray()); 
            }
            #endregion 组包DataTable数据
            HSSFWorkbook mybook = null;//Excel工作簿实例
            HSSFSheet mysheet = null;//Excel表单实例
            int rowIndex = 0;//行下标
            string SavePath1 = ParamsRead.SavePath+"\\OriginDataExcel\\";//根据模板1生成的文件路径
            if (!Directory.Exists(SavePath1))
                Directory.CreateDirectory(SavePath1);
            try
            {
                mybook = new HSSFWorkbook();
                mysheet = (HSSFSheet)mybook.CreateSheet();

                mysheet.SetActive(true);// = "测试记录";
                mybook.SetSheetName(0, DateTime.Now.ToString("yyyy-MM-dd") + "_研发暗箱原始数据");//设置表单名称
                #region 右击文件 属性信息
                {
                    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                    dsi.Company = "NPOI";
                    mybook.DocumentSummaryInformation = dsi;
                    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                    si.Author = "文件作者信息"; //填加xls文件作者信息
                    si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
                    si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
                    si.Comments = "作者信息"; //填加xls文件作者信息
                    si.Title = "天线图原始数据"; //填加xls文件标题信息
                    si.Subject = "天线图原始数据";//填加文件主题信息
                    si.CreateDateTime = System.DateTime.Now;
                    mybook.SummaryInformation = si;
                }
                #endregion
                ICellStyle cellStyle = mybook.CreateCellStyle();  //首先建单元格格式
                                                                  //文字水平和垂直对齐方式  
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                //是否换行  
                //cellStyle.WrapText = true;  //若字符串过大换行填入单元格
                //缩小字体填充  
                //cellStyle.ShrinkToFit = true;//若字符串过大缩小字体后填入单元格

                //新建一个字体样式对象
                IFont font = mybook.CreateFont();
                //设置字体加粗样式
                font.IsBold = true;
                font.FontName = "宋体";
                font.FontHeightInPoints = 14;
                font.Color = HSSFColor.White.Index;
                cellStyle.SetFont(font);
                HSSFPalette palette = mybook.GetCustomPalette();
                palette.SetColorAtIndex((short)8, (byte)79, (byte)129, (byte)189);
                HSSFColor hssFColor = palette.FindColor((byte)79, (byte)129, (byte)189);
                cellStyle.FillPattern = FillPattern.SolidForeground;
                cellStyle.FillForegroundColor = hssFColor.Indexed;

                HSSFRow firstRow = (HSSFRow)mysheet.CreateRow(rowIndex);
                firstRow.HeightInPoints = 50;

                HSSFCell cellFirstRow = (HSSFCell)firstRow.CreateCell(0);
                cellFirstRow.CellStyle = cellStyle;
                cellFirstRow.SetCellValue("第一行");
                mysheet.AutoSizeColumn(0);
                rowIndex++;

                // 设置表头
                HSSFRow headerRow = (HSSFRow)mysheet.CreateRow(rowIndex);
                headerRow.HeightInPoints = 30;

                // 设置表头名称
                for (int i = 0; i < dtHead.Columns.Count; i++)
                {
                    HSSFCell cell = (HSSFCell)headerRow.CreateCell(i);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(dtHead.Columns[i].ColumnName);
                    mysheet.AutoSizeColumn(i);
                }

                rowIndex++;

                for (int row = 0; row < dtHead.Rows.Count; row++)
                {
                    HSSFRow recordRow = (HSSFRow)mysheet.CreateRow(rowIndex);
                    recordRow.HeightInPoints = 30;
                    for (int i = 0; i < dtHead.Columns.Count; i++)
                    {
                        HSSFCell cell = (HSSFCell)recordRow.CreateCell(i);
                        ICellStyle cellStyle2 = mybook.CreateCellStyle();  //首先建单元格格式
                                                                           //文字水平和垂直对齐方式  
                        cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        cellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                        //是否换行  
                        //cellStyle.WrapText = true;  //若字符串过大换行填入单元格
                        //缩小字体填充  
                        //cellStyle.ShrinkToFit = true;//若字符串过大缩小字体后填入单元格
                        //新建一个字体样式对象
                        IFont font2 = mybook.CreateFont();
                        //设置字体加粗样式
                        font2.IsBold = true;
                        font2.FontName = "宋体";
                        font2.FontHeightInPoints = 14;
                        cellStyle2.SetFont(font2);
                        cell.SetCellValue(dtHead.Rows[row][i] + "");
                        cell.CellStyle = cellStyle2;
                        mysheet.AutoSizeColumn(i);

                    }
                    rowIndex++;
                }

                //设置自适应宽度
                for (int columnNum = 0; columnNum <= 20; columnNum++)
                {
                    int columnWidth = mysheet.GetColumnWidth(columnNum) / 256;
                    for (int rowNum = 1; rowNum <= mysheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow = mysheet.GetRow(rowNum);
                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                            if (columnWidth < length)
                            {
                                columnWidth = length;
                            }
                        }
                    }
                    mysheet.SetColumnWidth(columnNum, (columnWidth + 5) * 256);
                }
				//写入文件流
                using (MemoryStream ms = new MemoryStream())
                {
                    mybook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;

                    //mysheet.Dispose();该版本不知为何不支持此释放，暂未找到原因
                    mybook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                    using (FileStream fs = new FileStream($"{SavePath1}{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xls", FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }
            catch(Exception ex)
            {

            }
            finally
            {

            }
        }

使用上述代码块，可自定义一个DataTable运行看效果，导出风格比较随意；

个人推荐使用的原因

传统Excel：
	1、需要设定权限允许.NET访问COM+；可能会因为Excel版本问题而导致无法使用；
	2、对数据类型识别不完全
NPOI：
	1、完全免费；
	2、Excel有的单元格样式设计，公式大部分都有，而且数据格式的识别更为有效；
	3、无需安装Excel即可操作文件的读写