springboot poi 3 导入导出Excel记录

一：依赖

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

二：导入

@GetMapping("/importXlsx")
public void importXlsx() throws Exception {
    String path = "C:\\Users\\Administrator\\Desktop\\tycyerr";//路径
    File fileDir = new File(path);//读取文件夹
    File[] files = fileDir.listFiles();
    System.err.println("文件数量" + files.length);
    for (int f = 0; f < files.length; f++) {
        String name = files[f].getName();
        System.out.println(name);
        if (name.endsWith(".xlsx")) {
            FileInputStream in = new FileInputStream(new File(path+"\\" + name));
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            XSSFSheet sheet = null;
            //for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
            sheet = workbook.getSheetAt(0);//获取第一个sheet
            System.err.println("行数" + sheet.getLastRowNum());
for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
    XSSFRow row = sheet.getRow(j);
    for (int k = 1; k < row.getPhysicalNumberOfCells(); k++) {// 获取每个单元格
        XSSFCell cell = row.getCell(k);
        if (null != cell) {
           cell.setCellType(CellType.STRING);
           String value=cell.getStringCellValue().trim()
        }
    }
}

}

三：导出

public CommonResult selectWork(String year, String month, Integer unitId, HttpServletResponse response) {
    XSSFWorkbook workbook = new XSSFWorkbook();//创建excel
    XSSFSheet sheet = workbook.createSheet("自主择业一次性补贴汇总表");//创建sheet
    Map<String, Object> param = new HashMap<>();//将来要获取的数据
    //第一行
    XSSFRow row = sheet.createRow(0);//创建第1行
    XSSFCell cell = row.createCell(0);//创建第一行的第一列
    cell.setCellValue("附件11");//第一行第一列设置值
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 26);//合并单元格
    sheet.addMergedRegion(region);

       

//导出到本地
try {
    FileOutputStream fileOutputStream = new FileOutputStream("D:\\img\\test单元格.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
} catch (Exception e) {
    e.printStackTrace();
}
//显示到页面下载
try {
    String fileName = "test" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
    fileName = URLEncoder.encode(fileName, "UTF8");
    response.setContentType("application/vnd.ms-excel;chartset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
    ServletOutputStream out = response.getOutputStream();
    workbook.write(out);
    out.flush();
    out.close();
} catch (
        Exception e) {
    e.printStackTrace();
}

总结：总是记不住，写下来记录一下