How to use poi to generate excel in java-javaTutorial-php.cn

How to use poi to generate excel in java

WBOY

Release： 2023-04-30 13:46:06

forward

2122 people have browsed it

使用poi生成excel通常包含一下几个步骤

创建一个工作簿
创建一个sheet
创建一个Row对象
创建一个cell对象(1个row+1个cell构成一个单元格)
设置单元格内容
设置单元格样式. 字体字体大小是否加粗
保存
关闭流对象

生成一个工作簿

2010以上格式使用XSSFWorkBook对象, 2003格式使用HSSFWorkBook对象, 其他对象操作基本一样.

生成2003格式

public void test1() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    
    Font font = workbook.createFont();
    font.setFontName("宋体"); 
    font.setFontHeightInPoints((short) 12);
    cellStyle.setFont(font);
   
    HSSFSheet sheet = workbook.createSheet("Sheet1");
    //设置单元格宽度
    sheet.setColumnWidth(0, 30 * 256);
    sheet.setColumnWidth(1, 30 * 256);
    sheet.setColumnWidth(2, 30 * 256);
    
    Row row0 = sheet.createRow(0);
    Cell cell0 = row0.createCell(0);
    cell0.setCellValue("序号");
    cell0.setCellStyle(cellStyle);
    
    Cell cell1 = row0.createCell(1);
    cell1.setCellValue("姓名");
    
    Cell cell2 = row0.createCell(2);
    cell2.setCellValue("成绩");
    
    OutputStream os = null;
    try {
        os = new FileOutputStream("d:\\测试生成2003.xls");
        workbook.write(os);
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Copy after login

生成2010以上格式

@Test
public void test2() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    
    Font font = workbook.createFont();
    font.setFontName("宋体");
    font.setFontHeightInPoints((short) 12);
    cellStyle.setFont(font);
    
    
    XSSFSheet sheet = workbook.createSheet("Sheet1");
    Row row0 = sheet.createRow(0);
    Cell cell0 = row0.createCell(0);
    cell0.setCellValue("序号");
    cell0.setCellStyle(cellStyle);
    
    Cell cell1 = row0.createCell(1);
    cell1.setCellValue("姓名");
    
    Cell cell2 = row0.createCell(2);
    cell2.setCellValue("成绩");
    
    OutputStream os = null;
    try {
        os = new FileOutputStream("d:\\测试生成2010.xlsx");
        workbook.write(os);
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Copy after login

合并单元格

合并单元格在生成excel中算常见的一个场景, 通常先合并单元, 单元格内容居中,并设置单元格边框.
poi合并单元格使用CellRangeAddress类, 构造函数包括4个参数firstRow, lastRow, firstCol, lastCol根据自己需要传入行和列.

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
}

Copy after login

合并单元格后设置边框poi已提供了RegionUtil静态类, 可直接使用.

CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
sheet.addMergedRegion(region);

RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

Copy after login

设置单元格样式

左右居中上下居中自动换行

cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);

Copy after login

使用SpringMVC/SpringBoot导出excel

@Controller
@GetMapping("/excel2003")
public void excel2003(HttpServletResponse httpServletResponse){
    try {
        //2010格式设置
        //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //2003格式设置
        response.setContentType("application/vnd.ms-excel");
        httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("学生成绩单.xls", "utf-8"));

        ServletOutputStream outputStream = httpServletResponse.getOutputStream();

        HSSFWorkbook workbook = new HSSFWorkbook();

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);

        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        HSSFSheet sheet = workbook.createSheet("Sheet1");
        Row row0 = sheet.createRow(0);
        Cell cell0 = row0.createCell(0);
        cell0.setCellValue("序号");
        cell0.setCellStyle(cellStyle);

        Cell cell1 = row0.createCell(1);
        cell1.setCellValue("姓名");

        Cell cell2 = row0.createCell(2);
        cell2.setCellValue("成绩");

        workbook.write(outputStream);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Copy after login

The above is the detailed content of How to use poi to generate excel in java. For more information, please follow other related articles on the PHP Chinese website!