java使用poi生成excel的步驟

更新時間：2022年04月16日 12:36:14 作者：歪頭兒在帝都

2010以上格式使用XSSFWorkBook對象, 2003格式使用HSSFWorkBook對象, 其他對象操作基本一樣，本文重點給大家介紹java使用poi生成excel的相關(guān)知識，感興趣的朋友一起看看吧

使用poi生成excel通常包含一下幾個步驟

創(chuàng)建一個工作簿
創(chuàng)建一個sheet
創(chuàng)建一個Row對象
創(chuàng)建一個cell對象(1個row+1個cell構(gòu)成一個單元格)
設(shè)置單元格內(nèi)容
設(shè)置單元格樣式. 字體字體大小是否加粗
保存
關(guān)閉流對象

生成一個工作簿

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");
    //設(shè)置單元格寬度
    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();
    }
}

生成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();
    }
}

合并單元格

合并單元格在生成excel中算常見的一個場景, 通常先合并單元, 單元格內(nèi)容居中,并設(shè)置單元格邊框.
poi合并單元格使用CellRangeAddress類, 構(gòu)造函數(shù)包括4個參數(shù)firstRow, lastRow, firstCol, lastCol根據(jù)自己需要傳入行和列.

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

合并單元格后設(shè)置邊框poi已提供了RegionUtil靜態(tài)類, 可直接使用.

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);

設(shè)置單元格樣式

左右居中上下居中自動換行

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

使用SpringMVC/SpringBoot導出excel

@Controller
@GetMapping("/excel2003")
public void excel2003(HttpServletResponse httpServletResponse){
    try {
        //2010格式設(shè)置
        //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //2003格式設(shè)置
        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();
    }
}

總結(jié)

如果你在web項目中導出excel后,打開excel文件時提示文件已損壞,但是文件還可以打開, 則需要在HttpServletResponse上設(shè)置響應頭, 2003和2010設(shè)置方式不同
2003
response.setContentType("application/vnd.ms-excel");
2010
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

下載文件名如果包含中文的話需要編碼
httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("學生成績單.xls", "utf-8"));
這種設(shè)置在瀏覽器里下載文件中文是沒問題的, 只是如果你使用Swagger或者Postman測試的話,文件名還是經(jīng)過編碼的, 這個沒問題說明文件下載已經(jīng)沒問題.

到此這篇關(guān)于java使用poi生成excel的文章就介紹到這了,更多相關(guān)java生成excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: