Java 用POI定制化导出的Excel

前言

相信很多小伙伴，都碰到过导出Excel这样的需求，Apache的POI是比较成熟的解决方案，但是这篇博客不教你怎么用POI导出excel，因为网上资料太多了，一搜一大堆，我是想说一下怎么定制化导出的Excel。

正文

其实POI官网有详细的教程供我们参考，具体详情看https://poi.apache.org/components/spreadsheet/index.html。这里教程不仅仅有Excel，还有ppt、word等。

回归正题，Excel相关的examples都在这里https://poi.apache.org/components/spreadsheet/examples.html。官网提供了很多案例，像日历、计算、字体样式、单元格样式、图片等等，还分别提供了HSSF和XSSF的案例。

这里挑几个常用的说一下：

页眉和页脚

            //在页脚设置页数
            Footer footer = sheet.getFooter();
            //&P == 当前页数
            //&N == 总页数
            footer.setRight("Page &P of &N");


            Header firstHeader = ((XSSFSheet) sheet).getFirstHeader();
            //&F == 工作簿文件名
            firstHeader.setLeft("&F ......... first header");

            for (int i = 0; i < 100; i = i + 10) {
                sheet.createRow(i).createCell(0).setCellValue(123);
            }

            XSSFSheet sheet2 = (XSSFSheet) wb.createSheet("odd header-even footer");
            Header oddHeader = sheet2.getOddHeader();
            //&B == 加粗
            //&E == 双下划线
            //&D == 日期
            oddHeader.setCenter("&B &E oddHeader     &D ");

            Footer evenFooter = sheet2.getEvenFooter();
            evenFooter.setRight("even footer &P");
            sheet2.createRow(10).createCell(0).setCellValue("Second sheet with an oddHeader and an evenFooter");

            for (int i = 0; i < 200; i = i + 10) {
                sheet2.createRow(i).createCell(0).setCellValue(123);
            }

            XSSFSheet sheet3 = (XSSFSheet) wb.createSheet("odd header- odd footer");
            sheet3.createRow(10).createCell(0).setCellValue("Third sheet with oddHeader and oddFooter");
            Header oddH = sheet3.getOddHeader();
            //&C == 居中
            oddH.setCenter("centered oddHeader");
            oddH.setLeft("left ");
            oddH.setRight("right ");

            Footer oddF = sheet3.getOddFooter();
            oddF.setLeft("Page &P");
            oddF.setRight("Pages &N ");

填充和颜色

            // 创建一行(Row)，并建几个单元格(Cell). 行(Row)是从0开始计算的
            Row row = sheet.createRow(1);

            // 水绿色背景
            CellStyle style = wb.createCellStyle();
            style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
            style.setFillPattern(FillPatternType.BIG_SPOTS);
            Cell cell = row.createCell(1);
            cell.setCellValue(new XSSFRichTextString("X"));
            cell.setCellStyle(style);

            // Orange "foreground", foreground being the fill foreground not the font color.
            style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell = row.createCell(2);
            cell.setCellValue(new XSSFRichTextString("X"));
            cell.setCellStyle(style);

边框

     // 在单元格周围设置边框样式. 具体的样式可以在代码中点进去看，选择自己需要的样式
     CellStyle style = wb.createCellStyle();
     style.setBorderBottom(BorderStyle.THIN);
     style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
     style.setBorderLeft(BorderStyle.THIN);
     style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
     style.setBorderRight(BorderStyle.THIN);
     style.setRightBorderColor(IndexedColors.BLUE.getIndex());
     style.setBorderTop(BorderStyle.MEDIUM_DASHED);
     style.setTopBorderColor(IndexedColors.BLACK.getIndex());
     cell.setCellStyle(style);

表格

            XSSFWorkbook wb = new XSSFWorkbook()
            XSSFSheet sheet = wb.createSheet();

            // 设置表格位置
            AreaReference reference = wb.getCreationHelper().createAreaReference(
                    new CellReference(0, 0), new CellReference(2, 2));

            // 创建表格
            XSSFTable table = sheet.createTable(reference);
            table.setName("Test");
            table.setDisplayName("Test_Table");

            // For now, create the initial style in a low-level way
            table.getCTTable().addNewTableStyleInfo();
            table.getCTTable().getTableStyleInfo().setName("TableStyleMedium2");

            // 设置表格样式
            XSSFTableStyleInfo style = (XSSFTableStyleInfo) table.getStyle();
            style.setName("TableStyleMedium2");
            style.setShowColumnStripes(false);
            style.setShowRowStripes(true);
            style.setFirstColumn(false);
            style.setLastColumn(false);
            style.setShowRowStripes(true);
            style.setShowColumnStripes(true);

            // 填充数据
            XSSFRow row;
            XSSFCell cell;
            for (int i = 0; i < 3; i++) {
                // 创建行 (row)
                row = sheet.createRow(i);
                for (int j = 0; j < 3; j++) {
                    // 创建单元格(cell)
                    cell = row.createCell(j);
                    if (i == 0) {
                        cell.setCellValue("Column" + (j + 1));
                    } else {
                        cell.setCellValue((i + 1.0) * (j + 1.0));
                    }
                }
            }

图片

        //创建工作簿
        Workbook wb = new XSSFWorkbook()
            CreationHelper helper = wb.getCreationHelper();

            //添加图片
            // 方式 1 添加网络图片
            URL url = new URL(filePath);
        	InputStream is = url.openStream();
        	byte[] bytes = IOUtils.toByteArray(is);
        	int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
         	
         	// 方式2 添加本地图片文件
            InputStream is = new FileInputStream(args[0]);
            byte[] bytes = IOUtils.toByteArray(is);
            is.close();
            int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);

            //创建sheet
            Sheet sheet = wb.createSheet();

            //创建 drawing
            Drawing<?> drawing = sheet.createDrawingPatriarch();

            //设置图片样式
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(1);
            anchor.setRow1(1);
            Picture pict = drawing.createPicture(anchor, pictureIdx);

            //自适应图片
            pict.resize(2);
            

合并单元格

			Sheet sheet = wb.createSheet("new sheet");

            Row row = sheet.createRow((short) 1);
            Cell cell = row.createCell((short) 1);
            cell.setCellValue(new XSSFRichTextString("This is a test of merging"));

            sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

字体样式

 Font font0 = wb.createFont();
            font0.setColor(IndexedColors.BROWN.getIndex());
            CellStyle style0 = wb.createCellStyle();
            style0.setFont(font0);

            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 14);
            font1.setFontName("Courier New");
            font1.setColor(IndexedColors.RED.getIndex());
            CellStyle style1 = wb.createCellStyle();
            style1.setFont(font1);