C# 使用NPOI生Excel

 public bool CreateGradeTjToExcel(string filePath, Dictionary<string, List<AgeToNum>> nandic, Dictionary<string, List<AgeToNum>> nvdic, List<AgeToNum> hejiList)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("保健对象健康体检人数");

            //单元格合并  该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));//年龄段/岁
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, nandic.Count));//男
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, nandic.Count + 1, nvdic.Count + nandic.Count));//女

            sheet1.SetColumnWidth(0, 30 * 150);

            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);//创建行
            ICell icell1top1 = row1.CreateCell(0);//创建列
            row1.Height = 30 * 20;
            icell1top1.CellStyle = Getcellstyle(workbook, stylexls.默认);
            icell1top1.SetCellValue("年龄段/岁");
            icell1top1 = row1.CreateCell(1);//创建列
            icell1top1.CellStyle = Getcellstyle(workbook, stylexls.默认);
            icell1top1.SetCellValue("男");
            icell1top1 = row1.CreateCell(nandic.Count + 1);//创建列
            icell1top1.CellStyle = Getcellstyle(workbook, stylexls.默认);
            icell1top1.SetCellValue("女");
            icell1top1 = row1.CreateCell(nvdic.Count + nandic.Count + 1);//创建列
            icell1top1.CellStyle = Getcellstyle(workbook, stylexls.默认);
            icell1top1.SetCellValue("小计");


            //行集合
            List<string> rowList = new List<string>();
            rowList.Add("40岁以下");
            rowList.Add("41～50岁");
            rowList.Add("51～60岁");
            rowList.Add("61～70岁");
            rowList.Add("71～80岁");
            rowList.Add("81～90岁");
            rowList.Add("90岁以上");
            rowList.Add("合计");


            for (int i = 0; i < rowList.Count; i++)
            {
                IRow row2 = sheet1.CreateRow(i + 2);//创建行
                ICell icell1top2 = row2.CreateCell(0);//创建列
                row2.Height = 30 * 20;
                icell1top2.CellStyle = Getcellstyle(workbook, stylexls.默认);
                icell1top2.SetCellValue(rowList[i]);


            }

            using (FileStream file = File.OpenWrite(filePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                workbook.Write(file);
                file.Close();
                return true;
            }

        }

 

 

 

 /// <summary>
        /// 定义单元格常用到样式
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();
           

            //定义几种字体  
            //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的  
            IFont font12 = wb.CreateFont();
            font12.FontHeightInPoints = 12;
            font12.FontName = "宋体";
            //设置字体加粗样式
            font12.Boldweight = short.MaxValue;

            IFont font = wb.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 10;


            IFont fontcolorblue = wb.CreateFont();
            fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
            fontcolorblue.IsItalic = true;//下划线  
            fontcolorblue.FontName = "宋体";


            边框  
            //cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            //cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            边框颜色  
            //cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
            //cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
            //cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
            //cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;

            //背景图形，我没有用到过。感觉很丑  
            //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            //cellStyle.FillForegroundColor = HSSFColor.White.Index;
            // cellStyle.FillPattern = FillPatternType.NO_FILL;  
            // cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;

            //水平对齐  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.Top;

            //自动换行  
            cellStyle.WrapText = true;

            //缩进;当设置为1时，前面留的空白太大了。
            cellStyle.Indention = 0;

            //上面基本都是设共公的设置  
            //下面列出了常用的字段类型  
            switch (str)
            {
                case stylexls.top2:
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                    cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
                    cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
                    cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
                    cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
                    cellStyle.SetFont(font12);
                    break;
                case stylexls.top:
                    // cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Blue.Index;
                    cellStyle.FillForegroundColor = GetXLColour(wb, LevelOneColor);
                    cellStyle.FillPattern = FillPattern.SolidForeground;
                    cellStyle.SetFont(font12);
                    break;
                case stylexls.头:

                    cellStyle.SetFont(font12);
                    break;
                case stylexls.时间:
                    IDataFormat datastyle = wb.CreateDataFormat();

                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.数字:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.钱:
                    IDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("￥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.url:
                    //fontcolorblue.Underline = 1;
                    cellStyle.SetFont(fontcolorblue);
                    break;
                case stylexls.百分比:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.中文大写:
                    IDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.科学计数法:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.默认:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;


        }

        /// <summary>
        /// 设置单元格背景颜色
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="SystemColour"></param>
        /// <returns></returns>
        public short GetXLColour(IWorkbook workbook, System.Drawing.Color SystemColour)
        {
            short s = 0;
            //XSSFPictureData XlPalette = ((XSSFWorkbook)workbook).getc();
            //HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);


            HSSFPalette XlPalette = ((HSSFWorkbook)workbook).GetCustomPalette();
            HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);

            if (XlColour == null)
            {
                if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
                {
                    if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
                    {
                        //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
                        //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
                        XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
                    }
                    else
                    {
                        XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
                    }

                    s = XlColour.GetIndex();
                }

            }
            else
                s = XlColour.GetIndex();

            return s;
        }


        /// <summary>
        /// Excel转换
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public DataTable GetExcel(string filePath)
        {
            HSSFWorkbook hssfworkbook;

            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //hssfworkbook = new XSSFWorkbook(file);
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();
            rows.MoveNext();
            HSSFRow row = (HSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());  
                //将第一列作为列表头  
                dt.Columns.Add(row.GetCell(j).ToString());
            }
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }