POI实现Excel导入、导出

截取的代码片段，部分代码有简单替换（如ResponseModel、自定义异常代码被替换），仅为自己的笔记，所以只能参考

/**
 * 导出Excel
 *
 * @param sheetName sheet名称
 * @param title 标题
 * @param values 内容
 * @param wb HSSFWorkbook对象
 * @return HSSFWorkbook
 */
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
    // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
    if (wb == null) {
        wb = new HSSFWorkbook();
    }

    // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    HSSFSheet sheet = wb.createSheet(sheetName);
    // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    HSSFRow row = sheet.createRow(0);
    // 第四步，创建单元格样式
    CellStyle style = wb.createCellStyle();
    //设置背景颜色
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    //字体居中
    style.setAlignment(HorizontalAlignment.CENTER);
    //设置背景色必须加上这一句
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

 

//设置第一列、第二列单元格格式为文本
//    DataFormat format = wb.createDataFormat();
//    style.setDataFormat(format.getFormat("@"));
//    sheet.setDefaultColumnStyle(0,style);
//    sheet.setDefaultColumnStyle(1,style);

    //声明列对象
    HSSFCell cell = null;
    //创建标题
    for (int index = 0; index < title.length; index++) {
        //设置列宽（第几列【从零开始】，列宽【20个字符：20*256】）
        sheet.setColumnWidth(index,20*256);
        cell = row.createCell(index);
        cell.setCellStyle(style);
        cell.setCellValue(title[index]);
    }
    //创建内容
    for (int i = 0; i < values.length; i++) {
        row = sheet.createRow(i + 1);
        for (int j = 0; j < values[i].length; j++) {
            row.createCell(j).setCellValue(values[i][j]);
        }
    }
    return wb;
}

/**
 * 发送响应流
 *
 * @param response HttpServletResponse
 * @param fileName 文件名
 */
public static void setResponseHeader(HttpServletResponse response, String fileName, HSSFWorkbook wb) {
    try {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/force-download");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        ServletOutputStream outputStream = response.getOutputStream();

        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

 

/**
 * 导出excel模版
 * @param response
 */
public void excelTemplateExport(HttpServletResponse response){

//excel标题
String[] title = {"姓名", "身份证号"};
//sheet名称
String sheetName = "ExcelTemplate" + System.currentTimeMillis() + ".xls";

getHSSFWorkbook(sheetName,title,new String[0][],null);
    //响应到客户端
    setResponseHeader( response, fileName, wb);
}

 

 

/**
 * 导入excel
 * @param inputStream
 * @param originalFilename
 * @return
 */
public List<DongJianGaoDO> readExcel(InputStream inputStream, String originalFilename) {
    Workbook workbook = null;
    try {
        workbook =
                originalFilename.endsWith(".xlsx") ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
    } catch (IOException e) {
        logger.info("POI实例化Workbook对象异常", e);
    }
    Sheet sheetAt = workbook.getSheetAt(0);

//获取总行数
    int totalRows = sheetAt.getPhysicalNumberOfRows();
    if (totalRows < 1 || sheetAt.getRow(0) == null) {
        return null;
    }

//获取总列数
    int totalCell = sheetAt.getRow(0).getPhysicalNumberOfCells();

    List<DongJianGaoDO> dongJianGaoDOS = new ArrayList<>();
    for (int r = 2; r < totalRows; r++) {
        Row row = sheetAt.getRow(r);
        if (row == null) {
            continue;
        }
        DongJianGaoDO dongJianGaoDO = new DongJianGaoDO();
        for (int c = 0; c < totalCell; c++) {
            Cell cell = row.getCell(c);
            if (cell != null && !StringUtils.isEmpty(cell.getStringCellValue())) {
                String stringCellValue = cell.getStringCellValue();
                switch (c) {
                    case 0:
                        dongJianGaoDO.setName(stringCellValue);
                        break;
                    case 1:
                        dongJianGaoDO.setCardNo(stringCellValue);
                        break;
                    case 2:
                        dongJianGaoDO.setTellNum(stringCellValue);
                        break;
                }
            }
        }
        dongJianGaoDOS.add(dongJianGaoDO);
    }
    return dongJianGaoDOS;
}

 

 

/**
 * 导入excel
 * @param response
 * @param file
 * @throws IOException
 */
@PostMapping("/excelImport")
public void batchImport(HttpServletResponse response,MultipartFile file) throws IOException {
    FinancingAssert.isTrue(file!=null,"请选择excel文件！");
    String originalFilename = file.getOriginalFilename();
    if(!(originalFilename.endsWith(".xlsx")||originalFilename.endsWith(".xls")){

         throw new RuntimeException ("上传文件格式错误！");
    }   

List<DongJianGaoDO> list = readExcel(response,file.getInputStream(),originalFilename);

}