POI实现Excel导入、导出

àì夳堔傛蜴生んèń 2023-06-18 15:56 34阅读 0赞

**截取的代码片段，部分代码有简单替换（如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);