Java Excel导入导出

1. jxls导出导入

String template;//模板路径

data;//数据对象

String out;//生成导出路径

XLSTransformer transformer = new XLSTransformer();

transformer.transformXLS(template, datas, out);

1.1.导出模板

2.poi导入，只支持xls

maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

FileInputStream fileIn = new FileInputStream(xlsPath);
// 根据指定的文件输入流导入Excel从而产生Workbook对象
HSSFWorkbook wb0 = new HSSFWorkbook(fileIn);
// 获取Excel文档中的第一个表
HSSFSheet sht0 = wb0.getSheetAt(0);
// 对Sheet中的每一行进行迭代
for (Row r : sht0) {
    // 如果当前行的行号（从0开始）未大于2（第三行）则重新循环
    if (r.getRowNum() < 1) {
        continue;
    }
    if(r.getCell(0)!=null){
        r.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
    }else{
        break;
    }
    }else{
        break;
    }
}

小技巧

/**
 * 根据列内容，查找某一列的下标（查标题下标）
 * @param sheet
 * @param cellContent
 * @return
 */
    private static int findCell (XSSFSheet sheet, String cellContent){
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellTypeEnum() == CellType.STRING) {
                    if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
                        int columnIndex = cell.getColumnIndex();
                        return columnIndex;
                    }
                }
            }
        }
        return 0;
    }

3.poi导出excel,加样式

 try {
    //递归拆数据
    InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("excel/group-keyword.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    XSSFRow row=null;
    XSSFCell cell = null;
    int count=0;
    for (int i = 0; i < results.size(); i++) {
        Map<String, Object> stringObjectMap = results.get(i);
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        //写入数据
        row = (XSSFRow) sheet.createRow(2+ count);
        cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stringObjectMap.get("professionGroup").toString());

        cell = row.createCell(1);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平居左
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stringObjectMap.get("splitKeywordContent").toString());
        count++;
    }
    Date now=new Date();
    SimpleDateFormat sim=new SimpleDateFormat("yyyyMMdd");
    String nowStr=sim.format(now);
    response.setContentType("application/force-download");
    response.setHeader("Content-Disposition", "attachment;filename=\"" + java.net.URLEncoder.encode("专业核心关键词结果表"+nowStr, "UTF-8") + ".xlsx" + "\" ");
		// 写入响应中
    workbook.write(response.getOutputStream());
}catch (Exception e){
    e.printStackTrace();
}