Java使用Apache POI生成与读取excel表格

一、Apache POI是什么？

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
																							---摘自百度

二、利用POI生成excel表格：

注意：此例仅支持生成xls文件。

/**
 * 下载模板
 */
@RequestMapping(value = "/downloadTemplate")
public void downloadTemplate() {
    // 文件名
    String fileName = "fileName.xlsx";
    try {
    	// 第一行为合并列，
        String upper = "注意：1.请谨慎操作\n" +
                "      2.带(*)号的是必填项\n" +
                "      3.字段说明： \n" +
                "        字段1：选填，不填则默认1\t\t\t\t\t\t\t\n";
        // 列名
        Map<Integer, String> titles = new HashMap<>();
        titles.put(1, "column1");
        titles.put(2, "column2");
        ······
        titles.put(8, "column8");
        ExcelUtil.generateTemp(upper, fileName, titles, response);
    } catch (Exception e) {
        logger.error("downloadTemplate error", e);
    }
}

/**
 * 生成模板
 */
public static void generateTemp(String upper, String fileName, Map<Integer, String> header,
                                        HttpServletResponse response) throws IOException {
    XSSFWorkbook book = new XSSFWorkbook();
    OutputStream out = null;
    try {
        addRowAndCols(upper, book, header);
        response.reset();
        response.addHeader("content-disposition", "attachment;filename="+ new String(fileName.getBytes("utf-8"), "ISO-8859-1"));
        response.setContentType("application/x-excel;charset=UTF-8");
        out = response.getOutputStream();
        book.write(out);
        out.flush();
    } catch (Exception e) {
        throw new ServiceException("generateTemp error");
    } finally {
        book.close();
        if (out != null) {
            out.close();
        }
    }
}

// 创建sheet
private static void addRowAndCols(String upper, XSSFWorkbook workBook, Map<Integer, String> header) {
    XSSFSheet sheet = workBook.createSheet();
    // 第一行为备注
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0 ,7));
    Cell cell0 = XSSFWorkUtil.createCell(sheet, 0, 0);
    Row row0 = cell0.getRow();
    // 设置第一行行高
    row0.setHeight((short) 3500);
    // 设置第一行内容
    cell0.setCellValue(upper);
    // 设置第一行(备注)样式
    CellStyle cellStyle0 = workBook.createCellStyle();
    cellStyle0.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    // 自动换行
    cellStyle0.setWrapText(true);
    cellStyle0.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cell0.setCellStyle(cellStyle0);

    Row row = sheet.createRow(1);
    CellStyle cellStyle = workBook.createCellStyle();
    // 设置表头字体及大小
    Font font = workBook.createFont();
    font.setFontName("黑体");
    font.setFontHeightInPoints((short) 12);
    cellStyle.setFont(font);
    cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    DataFormat dataFormat = workBook.createDataFormat();
    cellStyle.setDataFormat(dataFormat.getFormat("@"));
    int colNum = 0;
    for (int i = 0; i < header.size(); i++) {
        Cell cell = row.createCell(colNum++);
        cell.setCellValue(header.get(i + 1));
        sheet.setColumnWidth(i, 20 * 256);
        cell.setCellStyle(cellStyle);
        sheet.setDefaultColumnStyle(i, cellStyle);
    }
}

生成表格如下：

若需增加下拉框，需增加如下代码：

 public static void generateTemplate(String fileName, Map<Integer, String> header, HttpServletResponse response) throws IOException {
	······
	// 下拉框的值
	String[] textList = {"test1", "test2", "test3"};
	// 增加下拉框
	addDropdown(1, 500, 2, 2, sheet, textList);
	······
}

// 设置第firstCol列至lastCol列的第firstRow行至lastRow行下拉框
private static void addDropdown(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet, String[] data) {
	// 设置第firstCol列至lastCol列的第firstRow行至lastRow行下拉框
	CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
	//生成下拉框内容
	DVConstraint constraint = DVConstraint.createExplicitListConstraint(data);
	//绑定下拉框和作用区域
	HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
	//sheet页生效
	sheet.addValidationData(dataValidation);
}

三、利用POI读取excel表格：

@RequestMapping(value = "/readExcel")
@ResponseBody
public DSResponse readExcel(@RequestParam("importFile") CommonsMultipartFile mFile) {
    DSResponse ds = new DSResponse();
    ······
    try {
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=utf-8");
        if (PoiUtil.isVaildExcel(mFile)) {
            return ds.setResponse(DSStatus.IO_ERROR, "文件类型错误。");
        }
        // 业务参数处理······
        boolean result = myTestService.saveExcelData(mFile);
        if (result) {
            ds.setStatus(DSStatus.SUCCESS);
        } else {
            ds.setStatus(DSStatus.FAILURE);
        }
    } catch (Exception e) {
        logger.error("readExcel error", e);
        ds.setResponse(DSStatus.UNKNOWN_ERROR, "readExcel error！");
    }
    return ds;
}

@Override
public boolean saveExcelData(CommonsMultipartFile file) {
	try {
		Workbook workbook = PoiUtil.getWorkbook(file);
        // 获取第一张表
        int sheetNums = workbook.getNumberOfSheets();
        try {
            for (int i = 0; i < sheetNums; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                // 获取数据的行数与列数
                int rowNum = sheet.getPhysicalNumberOfRows();
                // 循环开始值和最大值由模板文件决定
                for (int j = 1; j <= rowNum; j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    // 获取模板列数
                    int cellNums = row.getPhysicalNumberOfCells();
                    // 循环开始值和最大值由模板文件决定
                    for (int k = 0; k < cellNums-1; k ++) {
                        Cell cell0 = row.getCell(k);
                    }

                }
            }
        } catch (Exception e) {
            logger.error("saveExcelData error", e);
        }
        // 业务处理省略······
}