poi 打印 excel 生成工作表

package com.wondersgroup.wdls.common.excel;
 
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
 
public class ExcelPrintA4Utils {
 
    // 显示的导出Excel表的标题（通常第一行）
    private String title;
    // 导出Excel表的列名（通常第二行）
    private String[] rowName;
    // 导出Excel表的文件名
    private String fileName;
 
    // 导出Excel表的文件主题内容（通常从第三行往下的内容）
    // 需要注意的是：主要内容的格式为第一列为数字（序号），从第二列开始都是字符串
    // 每一行为Object[]类型，很多行用List包装起来
    private List<Object[]> dataList = new ArrayList<Object[]>();
    // 用于下载的response
    private HttpServletResponse response;
    
    // 构造方法，传入要导出的数据
    public ExcelPrintA4Utils(String title, String[] rowName, List<Object[]> dataList, HttpServletResponse response, String fileName) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.response = response;
        this.fileName = fileName;
    }
 
    /**
     * @Description 设置单元格样式
     * @Author zhulei
     * @Date 2021/1/26 14:57
     * @Param wb:工作薄
     * @Return 单元格样式
     */
    public static CellStyle getCellStyle(Workbook wb){
        // 设置字体
        Font font = wb.createFont();
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        CellStyle style = wb.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
 
        return style;
    }
 
    /**
     * @Description 单元格合并
     * @Author zhulei
     * @Date 2021/1/26 15:07
     * @Param
     * @Return
     */
    public static Sheet mergeCell(Sheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
        CellRangeAddress region = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);//获取区域从第firstRow行-lastRow行，firstCol列-lastCol列
        sheet.addMergedRegion(region);// 合并region的区域
        return sheet;
    }
    
    /**
     * @Description 标题样式
     * @Author zhulei
     * @Date 2021/1/26 15:35
     * @Param 
     * @Return 
     */
    public CellStyle getTitleStyle(Workbook wb) {
 
        // 设置字体
        Font font = wb.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 24);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
    /**
     * @Description 列头样式
     * @Author zhulei
     * @Date 2021/1/27 15:34
     * @Param
     * @Return
     */
    public CellStyle getColumnTopStyle(Workbook wb) {
 
        // 设置字体
        Font font = wb.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        CellStyle style = wb.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
    /**
     * @Description A4纸打印
     * @Author zhulei
     * @Date 2021/1/26 16:02
     * @Param
     * @Return
     */
    public static void printA4(Sheet sheet) {
        // 设置打印
        HSSFPrintSetup printSetup = (HSSFPrintSetup) sheet.getPrintSetup();
        printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
 
        printSetup.setVResolution((short)600);
        printSetup.setScale((short)50);//自定义缩放，此处100为无缩放
 
        sheet.setHorizontallyCenter(true);//设置打印页水平居中
        sheet.setVerticallyCenter(false); //设置打印页面为垂直居中
        sheet.setAutobreaks(false);//true设置sheet适应于一页,false设置sheet不一定适应于一页
 
    }
 
    public static void main(String[] args) throws IOException {
        Workbook wb = new HSSFWorkbook();//定义一个新的工作薄
        Sheet sheet = wb.createSheet("sheet");//创建一个sheet
 
        //设置不显示excel网格线
        sheet.setDisplayGridlines(false);//其中sheet是Sheet对象
        printA4(sheet);//设置A4打印
 
        for(int i=0;i<30;i++){
            Row row = sheet.createRow(i);//创建行
            Cell cell = row.createCell(0);//获取行单元格
            cell.setCellValue("测试excle"+i);//给行单元格设置值
 
            //设置每pagingRow（15）行分页打印
            if ((i+1) % 5 == 0){
                sheet.setRowBreak(i);//設置每15行分頁打印
            }
 
            //设置单元格边框
            cell.setCellStyle(getCellStyle(wb));
 
        }
 
        //合并单元格
//        merge(sheet,2,1);
 
 
        //4.设置页脚的页数
        HSSFFooter footer = (HSSFFooter )sheet.getFooter();
        footer.setCenter( "第" + HSSFFooter.page() + "页/共" + HSSFFooter.numPages()+"页");
 
        FileOutputStream fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试工作薄.xls");
        wb.write(fos);
        fos.close();
    }
 
    // 下载Excel的方法
    public void downloadExcel() {
        try {
            Workbook workbook = new HSSFWorkbook();// 创建工作簿对象
            Sheet sheet = workbook.createSheet(title);// 创建工作表
 
            //设置不显示excel网格线
            sheet.setDisplayGridlines(false);//其中sheet是Sheet对象
 
            //设置页脚的页数
            HSSFFooter footer = (HSSFFooter )sheet.getFooter();
            footer.setCenter( "第" + HSSFFooter.page() + "页/共" + HSSFFooter.numPages()+"页");
 
            CellStyle columnTopStyle = getColumnTopStyle(workbook);//获取列头样式对象
            CellStyle titleStyle = getTitleStyle(workbook);//获取标题样式
 
 
            /*单元格处理*/
            CellStyle style = getCellStyle(workbook);//单元格样式对象
 
 
            int listCount = dataList.size();//数据数量
            int ys = listCount % 10;//余数
            int page = listCount/10;//页数
            int allrowCount = 0;
            if(ys==0){
                allrowCount = dataList.size()+(page*5);
            }else {
                allrowCount = dataList.size()+((page+1)*5);
            }
            int index = 0;//初始化行Index
 
            // 定义列头所需列数
            int columnNum = rowName.length;
            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < listCount; i++) { //遍历每个对象
 
                if (index>=allrowCount) break;
 
                Object[] obj = dataList.get(i);
 
                printA4(sheet);//设置A4打印
                //设置每pagingRow（14）行分页打印
                if (index!=0&&index % 14== 0){
                    sheet.setRowBreak(index-1);//設置每14行分頁打印
                }
 
                //设置每页前四行的值
                if(index%14==0){
                    /*标题处理*/
                    // 产生表格标题行（占用两行）
                    Row rowm = sheet.createRow(index);
                    rowm.setHeight((short)(47.25*20));
                    Cell cell = rowm.createCell(0);
 
                    //合并标题占用的两行
                    mergeCell(sheet,index,index+2,0,(rowName.length - 1));
 
                    cell.setCellStyle(titleStyle);//标题赋样式
                    cell.setCellValue(title);//标题赋值
                    index = index+2;//完成两行数据设置值，给行index+2
 
                    //标题下的年度号
                    Row row2 = sheet.createRow(index);
                    cell = row2.createCell(0);
                    cell.setCellValue("font");
                    cell.setCellStyle(titleStyle);
                    index = index+1;//完成一行数据设置值，给行index+1
 
 //                  将列头设置到sheet的单元格中(占一行)
                    Row rowRowName = sheet.createRow(index);// 在索引2的位置创建行(最顶端的行开始的第二行)
                    rowRowName.setHeight((short)(30*20));
                    for (int n = 0; n < columnNum; n++) {
                        Cell cellRowName = rowRowName.createCell(n);//创建列头对应个数的单元格
                        cellRowName.setCellType(CellType.STRING); //设置列头单元格的数据类型
                        HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                        cellRowName.setCellValue(text);  //设置列头单元格的值
                        cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
                    }
 
                    index = index+1;//完成一行数据设置值，给行index+1
                }
 
                Row row = sheet.createRow(index);//创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    row.setHeight((short)(62.5*20));
                    Cell cell2 = null;  //设置单元格的数据类型
                    if (j == 0) {
                        cell2 = row.createCell(j,CellType.NUMERIC); //第一列为数字
                        cell2.setCellValue(i + 1);
                    } else {
                        cell2 = row.createCell(j,CellType.STRING); //第一列为字符
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell2.setCellValue(obj[j].toString()); //设置单元格的值
                        }else{
                            cell2.setCellValue("");
                        }
                    }
                    cell2.setCellStyle(style);  //设置单元格样式
                }
                index = index+1;//完成一行数据设置值，给行index+1
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    Row currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        Cell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellTypeEnum() == CellType.STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    if(columnWidth > 255){
                        columnWidth = 255;
                    }
                    sheet.setColumnWidth(colNum, columnWidth* 256);
                }
            }
//            printA4(sheet);
 
            if (workbook != null) {
                try {
                    if (response != null) {
                        response.setContentType("application/vnd.ms-excel;charset=utf-8");
                        response.setHeader("Content-Disposition", "attachment;filename=\""+new String(fileName.getBytes("gb2312"),"ISO8859-1"));
                        OutputStream out = response.getOutputStream();
                        workbook.write(out);
                        out.close();
                    } else {
                        FileOutputStream outputStream = new FileOutputStream("C:/"+fileName);
                        workbook.write(outputStream);
                        outputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
}