excel文件导入或导出Java代码示例_java excel导出接口

1、excel文件导入

controller层接口内容

 service层代码

 serviceImpl内代码内容

 @Override
    @Transactional(rollbackFor = Exception.class)
    public void importCheckItemExcel(MultipartFile file, Long checkPkgId) throws Exception {
        if (file.isEmpty()){
            throw new IOException("请选择上传文件");
        }
        Workbook work = this.getWorkbook(file);
        if (null == work) {
            throw new HolliException(DeviceExceptionEnum.EXCEL_IS_NULL_EXCEPTION);
        }
        Iterator<Sheet> sheetIterator = work.sheetIterator();
        while (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            if (sheet == null) {
                return;
            }
            //先获取首列标题
            Map<String, Integer> validColumns = new HashMap<>();
            List<String> headerCells = getFirstRow(sheet, 0);
            if (headerCells == null) {
                return;
            }
            for (int i = 0; i < headerCells.size(); i++) {
                Object header = headerCells.get(i);
                if (header == null) {
                    continue;
                }
                if (ObjectUtil.contains(header, "修程")) {
//                    validColumns.put("checkLevel", i);//数字
                    validColumns.put("checkLevelName", i);//含义
                }else if (ObjectUtil.contains(header, "工作项目")) {
                    validColumns.put("checkName", i);
                }else if (ObjectUtil.contains(header, "单位")) {
//                    validColumns.put("checkUnit", i);//数字
                    validColumns.put("checkUnitName", i);//含义
                }else if (ObjectUtil.contains(header, "周期")) {
                    validColumns.put("checkCycle", i);
                }else if (ObjectUtil.contains(header, "工作内容及标准")) {
                    validColumns.put("checkContent", i);
                }else if (ObjectUtil.contains(header, "ATP型号")) {
                    validColumns.put("atpModel", i);
                }
            }
//标题集合
            if (validColumns.size() == 0) {
                return;
            }
            Map<String, Object> map = new HashMap<String, Object>();
            //遍历当前sheet中的所有行
            //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                //读取一行
                List<Object> rows = getRow(sheet, j);
                if (rows == null) {
                    continue;
                }
                Set<String> keySet = validColumns.keySet();
                for (String key : keySet) {
                    int col_index = validColumns.get(key);
                    if (col_index >= rows.size()) {
                        continue;
                    }
                    Object cell = rows.get(col_index);
                    map.put(key, cell);
                }
//取出对应属性及值内容 以便存储数据库
 //业务逻辑，数据存储
                DevCheckItemVo devCheckItemVo = new DevCheckItemVo();
                BeanUtil.copyProperties(map, devCheckItemVo);
                this.insertDevCheckItem(checkPkgId, devCheckItemVo, j + 1);
            }
        }
    }
 
 

 判断excel的格式，同时兼容2003和2007

 
    protected final static String excel2003L = ".xls";    //2003- 版本的excel
    protected final static String excel2007U = ".xlsx";   //2007+ 版本的excel
 
 
/**
     * 描述：根据文件后缀，自适应上传文件的版本
     */
    public static Workbook getWorkbook(MultipartFile file) throws Exception {
        Workbook wb = null;
        InputStream is = file.getInputStream();
        String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new XSSFWorkbook(is);  //2007+
//            因HSSFWorkbook报（You need to call a different part of POI to process this data (eg XSSF instead of HSSF)），故直接使用XSSFWorkbook处理
//            wb = new HSSFWorkbook(is);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(is);  //2007+
        } else {
            throw new IOException("解析的文件格式有误！");
        }
        return wb;
    }
 

 获取行数据

/**
     * 获取行数据
     * @param sheet
     * @param rowIndex
     * @return
     */
    public List<Object> getRow(Sheet sheet, int rowIndex) {
        List<Object> li = new ArrayList<Object>();
        if (sheet == null) {
            return null;
        }
        Row row = null;
        Cell cell = null;
        //读取一行
        row = sheet.getRow(rowIndex);
        //去掉空行和表头
        if (row == null) {
            return null;
        }
        /**为去掉空行，若第一列为空，则直接跳过该行*/
        if (ObjectUtil.isEmpty(row.getCell(0))) {
            return null;
        }
        //遍历所有的列
        for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
            cell = row.getCell(y);
            li.add(this.getCellFormatValue(cell));
 
        }
        return li;
    }

 //获取excel列表内的对应数据格式

 
//获取excel列表内的对应数据格式
    protected Object getCellFormatValue(Cell cell) {
        Object val = "";
        if (null != cell) {
            if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (DateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 != 0)
                    {
                        val = new BigDecimal(val.toString());
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellType() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellType() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellType() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }
        } else {
            val = "";
        }
        return val;
    }

 2、excel导出

controller层接口内容

  service层代码

  serviceImpl内代码内容

 @Override
    public void exportCheckItemExcel(String strIds, HttpServletResponse response) throws Exception {
        List<DevCheckItem> devCheckItemList = new ArrayList<>();
        long[] itemIdArray = StrUtil.splitToLong(strIds,",");
        //查询检修项id
        for (int i = 0; i< itemIdArray.length; i++){
            DevCheckItem devCheckItem = this.getById(itemIdArray[i]);
            if (ObjectUtil.isNotEmpty(devCheckItem)){
                devCheckItemList.add(devCheckItem);
            }
        }
        if (ObjectUtil.isNotEmpty(devCheckItemList) && devCheckItemList.size() > 0){
            //导出excel
            XSSFWorkbook workbook=new XSSFWorkbook();
            //获得名字为sheet的工作本对象，这个是看你的模板工作表的名字
            XSSFSheet sheet = workbook.createSheet("sheet1");
            // 序号
            int serialNo = 1;
            //行号
            int rowIndex = 1;
 
            //创建表头
            XSSFRow row_excel = sheet.createRow(0);
 
//行业务数据填充
            XSSFCell cell = row_excel.createCell(0);
            cell.setCellValue("序号");
 
            cell = row_excel.createCell(1);
            cell.setCellValue("ATP型号");
 
            cell = row_excel.createCell(2);
            cell.setCellValue("修程");
 
            cell = row_excel.createCell(3);
            cell.setCellValue("工作项目");
 
            cell = row_excel.createCell(4);
            cell.setCellValue("单位");
 
            cell = row_excel.createCell(5);
            cell.setCellValue("周期（小时）");
 
            cell = row_excel.createCell(6);
            cell.setCellValue("工作内容及标准");
 
            //遍历填充数据
            for (DevCheckItem devCheckItem : devCheckItemList){
                //获取当前行
                row_excel = sheet.getRow(rowIndex);
                if (row_excel == null){
                    //创建行
                    row_excel = sheet.createRow(rowIndex);
                }
                //内容填充
                if (ObjectUtil.isNotEmpty(devCheckItem)){
                    XSSFCell cellNew = row_excel.createCell(0);
                    cellNew.setCellValue(serialNo);
 
                    cell = row_excel.createCell(1);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getAtpModel())){
                        cell.setCellValue(devCheckItem.getAtpModel());
                    }
 
                    cell = row_excel.createCell(2);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getCheckLevel())){
                        String levelName = DevCheckLevelEnum.getLevelNameByType(devCheckItem.getCheckLevel());
                        cell.setCellValue(levelName);
                    }
 
                    cell = row_excel.createCell(3);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getCheckName())){
                        cell.setCellValue(devCheckItem.getCheckName());
                    }
 
                    cell = row_excel.createCell(4);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getCheckUnit())){
                        String checkUnit = this.getCheckUnitNameByNum(devCheckItem.getCheckUnit());
                        cell.setCellValue(checkUnit);
                    }
 
                    cell = row_excel.createCell(5);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getCheckCycle())){
                        cell.setCellValue(devCheckItem.getCheckCycle());
                    }
 
                    cell = row_excel.createCell(6);
                    if (ObjectUtil.isNotEmpty(devCheckItem.getCheckContent())){
                        cell.setCellValue(devCheckItem.getCheckContent());
                    }
                }
                serialNo++;
                rowIndex++;
            }
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
 
        }
    }

以上已导出完成。

3、以下为可能会用到的导出实例文件，上传文件服务器的过程 

File格式转换MultipartFile格式的例子

//上传minio服务器
String fileName = "检修工作项目.xlsx";
File cacheFile = new File(fileName);
if (cacheFile.exists()) {
    cacheFile.delete();
}
  
cacheFile.createNewFile();//生成文件
OutputStream out = new FileOutputStream(cacheFile);
workbook.write(out);
---------------------------------------------------------------------------------------（上面为导出实例文件）
//下面为上传文件流格式转换的格式。
FileInputStream fileInputStream = new FileInputStream(cacheFile);
MultipartFile multipartFile = new MockMultipartFile("cacheFile", cacheFile.getName(), "text/plain", IOUtils.toByteArray(fileInputStream));

-------------------------------------以下无正文-----------------------------------------------------------

注:仅供学习，记录问题和参考，共勉！