使用POI工具包操作excel文件-上传下载

痛定思痛。 2023-06-16 05:57 39阅读 0赞

# 1、POI下载Excel文件 #

### maven配置 ###

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

### 主要代码 ###

public static void exportExcel(HttpServletResponse response) throws IOException { 
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("模板");
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
    
            HSSFRow headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellStyle(cellStyle);
            headerRow.createCell(0).setCellValue("姓名");
            headerRow.createCell(1).setCellStyle(cellStyle);
            headerRow.createCell(1).setCellValue("年龄");
            headerRow.createCell(2).setCellStyle(cellStyle);
            headerRow.createCell(2).setCellValue("编号");
            headerRow.createCell(3).setCellStyle(cellStyle);
            headerRow.createCell(3).setCellValue("住址");
    
            HSSFRow row;
    
            for (int i = 0; i < 5; i++) { 
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellStyle(cellStyle);
                row.createCell(0).setCellValue("数据" + i);
                row.createCell(1).setCellStyle(cellStyle);
                row.createCell(1).setCellValue("年龄" + i);
                row.createCell(2).setCellStyle(cellStyle);
                row.createCell(2).setCellValue("编号" + i);
                row.createCell(3).setCellStyle(cellStyle);
                row.createCell(3).setCellValue("住址" + i);
            }
    
            response.setCharacterEncoding("UTF-8"); //防止乱码
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("模版".getBytes("UTF-8"), "ISO-8859-1") + ".xls"); //防止乱码
            workbook.write(response.getOutputStream());
        }

# 2、POI解析上传的Excel文件 #

### maven配置如上 ###

### 主要代码 ###

public void importExcel(InputStream inputStream) throws IOException { 
            Workbook workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            Sheet sheet = workbook.getSheetAt(0);
            int rowLength = sheet.getLastRowNum()+1; //获取总行数
            int colLength = sheet.getRow(0).getLastCellNum();  //总列数
            for(int i=0;i<rowLength;i++){ 
                Row row = sheet.getRow(i);
                for(int j=0; j<colLength; j++){ 
                    Cell cell = row.getCell(j);
                    if(cell !=null){ 
    // cell.getCellType() //获取类型
                        System.out.println(cell.getStringCellValue()+j+i); //注意类型
                    }
                }
            }
        }