Java基于poi的Excel导入导出

 

1.引入相应的pom文件

		<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>

2.Excel导出模板

 // 创建表头
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, HttpServletResponse response, String[] header, String name ) throws IOException {
        HSSFRow row = sheet.createRow(0);
        // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
//        sheet.setColumnWidth(8, 60 * 256);
//        sheet.setColumnWidth(5, "55".getBytes().length*2*256);
        // 设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        //导出的Excel头部
        String[] headers = header;
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 10);
//        sheet.setColumnWidth(5, "55".getBytes().length*2*256);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }
        String fileName = name + new Date().getTime() + ".xls";
        //清空response
        response.reset();
        //设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename="+ fileName);
        response.setHeader("Content-Disposition","attachment; filename="+new String(name.getBytes("gb2312"),"ISO-8859-1")+".xls");
        OutputStream os = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        //将excel写入到输出流中
        workbook.write(os);
        os.flush();
        os.close();
    }

//调用公用设置表头，导出excel
public static void exportTempalte(HttpServletResponse response)throws IOException{
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个Excel表单,参数为sheet的名字
        HSSFSheet sheet = workbook.createSheet("农村就业模板表");
        //设置excel头部
        String[] headers = {"职位","描述"};
        //excel名称
        String fileName = "码神";
        //创建表头
        setTitle(workbook, sheet, response, headers, fileName);
    }

//调用导出的方法
    @ApiOperation(value = "导出excel模板")
    @GetMapping("/open/export-tempalte")
    public ApiResponse  exportTempalte(HttpServletResponse response) throws IOException {
        try {
            ImportExcelUtils.exportTempalte(response);
            return ApiResponse.success("导出模板成功！");
        } catch (IOException e) {
            return ApiResponse.success(e.getMessage());
        }
    }

3.导入Excel表

public static List<?>  importExcel(MultipartFile file){
        //new 一个对象例如
        xxx x = new xxx();
        String fileName = file.getOriginalFilename(); //获取文件名
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            // 有多少个sheet
            int sheets = workbook.getNumberOfSheets();
            for (int i = 0; i < sheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                // 获取多少行
                int rows = sheet.getPhysicalNumberOfRows();
                TblJobInfoData jobInfoData = null;

                // 遍历每一行，注意：第 0 行为标题
                for (int j = 1; j < rows; j++) {
                     //获取第一行的第0个内容
                    x.name(row.getCell(0).toString());
                    //将对象放入集合中
                    list.add(x);
                }
            }
        } catch (IOException e) {
            e.getMessage();
        }
        //将集合反出去
        return list;
    }