POI实现Java生成并打印excel报表

常用模块形式

HSSFPrintSetupprintSetup=sheet.getPrintSetup();
//打印质量600点
printSetup.setVResolution((short)600);
//A4纸张打印
printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
printSetup.setLandscape(true);

常用参数设置方法
1、页面设置

1.1、方向：

纵向(T)：HSSFPrintSetup#setLandscape(false); [默认状态]

横向(L)：HSSFPrintSetup#setLandscape(true);

1.2、缩放：

缩放比例(A)：HSSFPrintSetup#setScale((short) 100); [默认状态]

调整(F): 页宽 HSSFPrintSetup#setFitWidth((short) 1);

页高 HSSFPrintSetup#setFitHeight((short) 0);

1.3、纸张大小(Z)：HSSFPrintSetup#setPageSize(HSSFPrintSetup.LETTER_PAPERSIZE);

纸张大小定义说明：

public static final short LETTER_PAPERSIZE = 1;

public static final short LEGAL_PAPERSIZE = 5;

public static final short EXECUTIVE_PAPERSIZE = 7;

public static final short A4_PAPERSIZE = 9;

public static final short A5_PAPERSIZE = 11;

public static final short ENVELOPE_10_PAPERSIZE = 20;

public static final short ENVELOPE_DL_PAPERSIZE = 27;

public static final short ENVELOPE_CS_PAPERSIZE = 28;

public static final short ENVELOPE_MONARCH_PAPERSIZE = 37;

1.4、打印质量(Q): HSSFPrintSetup#setVResolution((short) 300);

1.5、起始页码®: HSSFPrintSetup#setPageStrart((short) 0); [默认状态]

2、页面距

2.1、上(T): HSSFSheet#setMargin(HSSFSheet.TopMargin,(short) 0.6);

2.2、下(B): HSSFSheet#setMargin(HSSFSheet.BottomMargin,(short) 0.6);

2.3、左(L): HSSFSheet#setMargin(HSSFSheet.LeftMargin,(short) 0.6);

2.4、右®: HSSFSheet#setMargin(HSSFSheet.RightMargin,(short) 0.2);

2.5、页眉(A): HSSFPrintSetup#setHeaderMargin((double) 0.2);

2.6、页脚(F): HSSFPrintSetup#setFooterMargin((double) 0.6);

2.7、居中对齐方式: 水平(Z) HSSFSheet#setHorizontallyCenter(false);

垂直(V) HSSFSheet#setVerticallyCenter(false);

3、页眉/页脚

3.1、页眉：HSSFHeader#setLeft（HSSFHeader.date();

说明：首先获得HSSFHeader对象，确定页眉的显示位置(如：左边显示页眉HSSFHeader#setLeft(显示内容))

可用：HSSFHeader#setLeft,setCenter,setRight

3.2、页脚: HSSFFotter#setLeft（HSSFFotter.page()+”/”+HSSFFotter.numPages()）

说明同页眉

4、工作表

4.1、打印区域

HSSFWorkbook#setPrintArea((int) sheetIndex, (int) startColumn, (int) endColumn, (int) startRow, (int) endRow);

参数说明：

sheetIndex–从0开始的sheet的索引编号
startColumn-打印区域的开始列号
endColumn- 打印区域的结束列号
startRow-打印区域的开始行号
endRow- 打印区域的结束行号

4.2、打印标题
//设置列标题
HSSFSheet .setRepeatingColumns(CellRangeAddress columnRangeRef),
//设置行标题
HSSFSheet.setRepeatingRows(CellRangeAddress columnRangeRef)

4.3、打印

网格线 (G)：HSSFSheet#setPrintGridlines(false);

单色打印(B)HSSFPrintSetup#setNoColor(false);

按草稿方式（Q）：HSSFPrintSetup#setDraft(false)；

行号列标(L):

批注(M):

错误单元格打印为(E):

4.4、打印顺序

HSSFPrintSetup#setLeftToRight(false)；
程序示例:

package com.chcontrol;



import java.io.FileOutputStream;
import java.io.IOException;

import com.chcontrol.core.model.vo.MultiLoadEnergyConsumptionReportFromVO;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

/**
 * @author Administrator daiyungang
 * @date 2020-03-30  20:40
 */
public class ExcelTest {

    public static void main(String[] args) throws IOException {

        // create a new file
        FileOutputStream out = new FileOutputStream("D:/workbook.xls");
        // create a new workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet sheet = wb.createSheet();

        //2.model
        for (int i = 0; i < 50; i++) {
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j <50; j++) {
                HSSFCell cell = row.createCell(j);
                if (j==0||j==1||j==2){
                    cell.setCellValue("标题"+i+j);
                }else {
                    cell.setCellValue("内容"+i+j);
                }

            }

        }


        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 4000);
        HSSFCell cell = sheet.getRow(0).getCell(0);
        cell.setCellStyle(getTileStyle(wb));
        CellRangeAddress region2 = new CellRangeAddress(0, 0, 0, 50 - 1);
        sheet.addMergedRegion(region2);

        //3.output
        sheet.setDisplayGridlines(true);
        sheet.setPrintGridlines(true);
        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        //A4纸
        printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        wb.setPrintArea(0,0,49,0,49);
        
//        sheet.setRepeatingRows(region2);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 49, 0, 2);
        sheet.setRepeatingColumns(cellRangeAddress);
        wb.write(out);
        out.close();
    }

    private static HSSFCellStyle getTileStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //设置上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名称
//        font.setFontName("华文行楷");
        //设置字号
        font.setFontHeightInPoints((short) 14);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);
        //设置字体颜色
//        font.setColor(IndexedColors.RED.index);
        cellStyle.setFont(font);
        return cellStyle;
    }
}