springboot 中 poi 操作合集

 /**     *      * (读入excel文件，解析后返回)     * @param file(文件类型)     * @throws IOException     * @return List<String[]>     */    public static List<String[]> readExcel(MultipartFile file)        throws IOException {        // 检查文件        checkFile(file);        // 获得Workbook工作薄对象        Workbook workbook = getWorkBook(file);        // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回        List<String[]> list = new ArrayList<String[]>();        if (workbook != null) {            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++ ) {                // 获得当前sheet工作表                Sheet sheet = workbook.getSheetAt(sheetNum);                if (sheet == null) {                    continue;                }                // 获得当前sheet的开始行                int firstRowNum = sheet.getFirstRowNum();                // 获得当前sheet的结束行                int lastRowNum = sheet.getLastRowNum();                // 循环除了第一行的所有行                for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++ ) {                    // 获得当前行                    Row row = sheet.getRow(rowNum);                    if (row == null) {                        continue;                    }                    // 获得当前行的开始列                    int firstCellNum = row.getFirstCellNum();                    // 获得当前行的列数                    int lastCellNum = row.getLastCellNum();                    String[] cells = new String[row.getLastCellNum()];                    // 循环当前行                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++ ) {                                            Cell cell=row.getCell(cellNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);                        cells[cellNum] = getCellValue(cell);                    }                    list.add(cells);                }            }        }        return list;    }     /**     *      * (判断文件)     * @param file     * @throws IOException     * @return void     */    public static void checkFile(MultipartFile file)        throws IOException {        // 判断文件是否存在        if (null == file) {            throw new FileNotFoundException("文件不存在！");        }        // 获得文件名        String fileName = file.getOriginalFilename();        // 判断文件是否是excel文件        if (!fileName.endsWith(XLS_TYPE) && !fileName.endsWith(XLSX_TYPE)) {            throw new IOException(fileName + "不是excel文件");        }    }     public static String getCellValue(Cell cell) {        String cellValue = "";        if (cell == null) {            return cellValue;        }        // 把数字当成String来读，避免出现1读成1.0的情况        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {            cell.setCellType(Cell.CELL_TYPE_STRING);        }        // 判断数据的类型        switch (cell.getCellType()) {            // 数字            case Cell.CELL_TYPE_NUMERIC:                cellValue = String.valueOf(cell.getNumericCellValue());                break;            // 字符串            case Cell.CELL_TYPE_STRING:                cellValue = String.valueOf(cell.getStringCellValue());                break;            // Boolean            case Cell.CELL_TYPE_BOOLEAN:                cellValue = String.valueOf(cell.getBooleanCellValue());                break;            // 公式            case Cell.CELL_TYPE_FORMULA:                cellValue = String.valueOf(cell.getCellFormula());                break;            // 空值            case Cell.CELL_TYPE_BLANK:                cellValue = "";                break;            // 故障            case Cell.CELL_TYPE_ERROR:                cellValue = "非法字符";                break;            default:                cellValue = "未知类型";                break;        }        return cellValue;    }     /**     *      * (getWorkBook:(创建WorkBook对象))     * @param file     * @return     * @return Workbook     */    public static Workbook getWorkBook(MultipartFile file) {        // 获得文件名        String fileName = file.getOriginalFilename();        // 创建Workbook工作薄对象，表示整个excel        Workbook workbook = null;        try {            // 获取excel文件的io流            InputStream is = file.getInputStream();            // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象            if (fileName.endsWith(XLS_TYPE)) {                // 2003                workbook = new HSSFWorkbook(is);            }            else if (fileName.endsWith(XLSX_TYPE)) {                // 2007                workbook = new XSSFWorkbook(is);            }        }        catch (IOException e) {            e.printStackTrace();        }        return workbook;    }

/**     * 人员导出     *     * @param sysSysUserVO     * @throws IOException     */    @PostMapping("/exportUser")    @ApiOperation(value = "导出人员excel", notes = "导出人员excel")    public void        exportUser(@ApiParam(name = "人员id", value = "人员id", required = false) @RequestBody List<SysUserVO> sysSysUserVO)            throws IOException {        List<SysUserVO> middleList = new ArrayList<>();        // 查询用户详细信息        for (SysUserVO sysUserVO : sysSysUserVO) {            List<SysUserVO> resultListSysUser = sysUserService.querySysUserAll(sysUserVO);            userInfoUtil.completionInformation(resultListSysUser.get(0));            middleList.add(resultListSysUser.get(0));        }        // excle格式        String[] headers = {"用户名", "姓名", "密码", "启动状态", "岗位", "角色", "所属部门", "手机", "邮箱", "身份证号"};        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet = workbook.createSheet();        // 设置列宽        sheet.setDefaultColumnWidth((short)18);        HSSFRow row = sheet.createRow(0);        for (short i = 0; i < headers.length; i++) {            // 创建单元格，每行多少数据就创建多少个单元格            HSSFCell cell = row.createCell(i);            HSSFRichTextString text = new HSSFRichTextString(headers[i]);            // 给单元格设置内容            cell.setCellValue(text);        }        for (int j = 0; j < middleList.size(); j++) {            SysUserVO export = middleList.get(j);            // 从第二行开始填充数据            row = sheet.createRow(j + 1);            List<String> datas = new ArrayList<>();            String userName = export.getUsername();            String trueName = export.getTureName();            String password = export.getPassword();            String status = String.valueOf(export.getIsEnabled());            String postName = export.getPostName();            String roleName = export.getRoleName();            String organName = export.getOrganizationName();            String phone = export.getMobile();            String email = export.getEmail();            String identityCard = export.getIdentityCard();            datas.add(userName);            datas.add(trueName);            datas.add(password);            datas.add(status);            datas.add(postName);            datas.add(roleName);            datas.add(organName);            datas.add(phone);            datas.add(email);            datas.add(identityCard);            for (int k = 0; k < datas.size(); k++) {                String string = datas.get(k);                HSSFCell cell = row.createCell(k);                HSSFRichTextString richString = new HSSFRichTextString(string);                HSSFFont font3 = workbook.createFont();                // 定义Excel数据颜色，这里设置为蓝色                font3.setColor(HSSFColor.BLUE.index);                richString.applyFont(font3);                cell.setCellValue(richString);            }        }        String fileName = "人员导出.xls";        // 导出        HttpServletResponse response =            ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();        RequestAttributes requsetAttributes = RequestContextHolder.currentRequestAttributes();        HttpServletRequest request = ((ServletRequestAttributes)requsetAttributes).getRequest();        // 获得浏览器代理信息        final String userAgent = request.getHeader("USER-AGENT");        // 判断浏览器代理并分别设置响应给浏览器的编码格式        if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident")) {            // IE浏览器            fileName = URLEncoder.encode(fileName, "UTF-8");        } else if (StringUtils.contains(userAgent, "Mozilla")) {            // google,火狐浏览器            fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");        } else {            // 其他浏览器            fileName = URLEncoder.encode(fileName, "UTF-8");// 其他浏览器        }        // 设置HTTP响应头        response.reset();        // 重置 如果不在页面上显示而是下载下来 则放开注释        response.setContentType("application/octet-stream");        response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");        OutputStream os = response.getOutputStream();        workbook.write(os);        os.close();    }

public class BarcodeExportlFlow implements IBarcodeExport {     @Autowired    BarcodeManageBatchSerivce barcodeManageBatchSerivce;     @Override    public OutputStream exportData() throws IOException {         //查询批次码        BarcodeBatchManageBo input = new BarcodeBatchManageBo();        List<BarcodeBatchManageBo> middleList = barcodeManageBatchSerivce.selectBatch(input);        String[] headers = {"id", "条码批次码", "激活状态", "有效状态", "导入人", "导入时间", "激活人", "激活时间", "作废人", "作废时间", "本批次条数"};        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet = workbook.createSheet();        //设置列宽        sheet.setDefaultColumnWidth((short) 18);        HSSFRow row = sheet.createRow(0);        for (short i = 0; i < headers.length; i++) {            //创建单元格，每行多少数据就创建多少个单元格            HSSFCell cell = row.createCell(i);            HSSFRichTextString text = new HSSFRichTextString(headers[i]);            //给单元格设置内容            cell.setCellValue(text);        }         for (int j = 0; j < middleList.size(); j++) {            BarcodeBatchManageBo export = middleList.get(j);            //从第二行开始填充数据            row = sheet.createRow(j + 1);            List<String> datas = new ArrayList<>();            String id = export.getId().toString();            String batchCode = export.getBatchCode();            String activationStatus = export.getActivationStatus();            String effectiveStatus = export.getEffectiveStatus();            datas.add(id);            datas.add(batchCode);            datas.add(activationStatus);            datas.add(effectiveStatus);            for (int k = 0; k < datas.size(); k++) {                String string = datas.get(k);                HSSFCell cell = row.createCell(k);                HSSFRichTextString richString = new HSSFRichTextString(string);                HSSFFont font3 = workbook.createFont();                //定义Excel数据颜色，这里设置为蓝色                font3.setColor(HSSFColor.BLUE.index);                richString.applyFont(font3);                cell.setCellValue(richString);            }        }        FileOutputStream fos = new FileOutputStream("D:/wb.xls");        workbook.write(fos);        fos.close();        return null;    }}