Java解析Excel文件（apache-poi）

大飞机杰森

已于 2022-09-10 09:11:03 修改

阅读量334

点赞数

文章标签： java 开发语言

于 2022-09-10 00:52:42 首次发布

本文链接：https://blog.csdn.net/qq_45839663/article/details/126791020

版权

首先封装一个工具类

public class ParseExcelTest {
    public static String getCellValueForStr(HSSFCell cell){
        String res;
        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
            res = cell.getStringCellValue();
        }else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){
            res =  cell.getBooleanCellValue() + "";
        }else if (cell.getCellType() == HSSFCell.LAST_COLUMN_NUMBER){
            res =  cell.getNumericCellValue() + "";
        }else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
            res =  cell.getCellFormula();
        }else {
            return "";
        }
        return res;
    }
}

2、配置文件上传解析器

<!-- 6、配置文件上传解析器 -->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8"></property>
        <property name="maxUploadSize" value="#{1024*1024*5}"></property>
    </bean>

3、操作示例

@RequestMapping("/workbench/activity/importActivitiesByList.do")
    public @ResponseBody Object importActivity(MultipartFile activityFile ,HttpSession session){
        User user = (User) session.getAttribute(Contants.SESSION_USER);
        ReturnObject returnObject = new ReturnObject();
        try {
//            //把上传的Excel文件写入到磁盘
//            String fileName = activityFile.getOriginalFilename();
//            String suffixName = fileName.substring(fileName.lastIndexOf("."));
//            File file = new File("C:\\out\\out\\" + suffixName);
//            activityFile.transferTo(file);
//            //解析Excel文件，获取文件中数据，封装成activityList
//            InputStream is = new FileInputStream("C:\\out\\out\\" + suffixName);
//           直接以流的形式创建出来，不通过磁盘读写
            InputStream is = activityFile.getInputStream();
            HSSFWorkbook workbook = new HSSFWorkbook(is);
            HSSFSheet sheet = workbook.getSheetAt(0);
            //根据sheet获取HSSFRow对象，封装了一行所有的信息
            HSSFRow row = null;
            HSSFCell cell= null;
            Activity activity = null;
            List<Activity> activities = new ArrayList<>();
            for (int i = 1; i < sheet.getLastRowNum(); i++){
                row = sheet.getRow(i);
                activity = new Activity();
                activity.setId(UUIDUtils.getUUID());
                activity.setOwner(user.getId());
                activity.setCreateTime(DateUtils.formatDate(new Date()));
                activity.setCreateBy(user.getId());

                for (int j = 0; j < row.getLastCellNum(); j++){
                    //根据row获取HSSFCell对象，封装了一列的信息
                    cell = row.getCell(j);
                    //获取列中数据
                    String cellValue = HSSFUtils.getCellValueForStr(cell);
                    if (j == 0){
                        activity.setName(cellValue);
                    }else if (j == 1){
                        activity.setStartDate(cellValue);
                    }else if (j == 2){
                        activity.setEndDate(cellValue);
                    }else if (j == 3){
                        activity.setDescription(cellValue);
                    }
                }
                activities.add(activity);
            }
            int ret = activityService.saveActivityByList(activities);
            returnObject.setCode(Contants.RETURN_OBJECT_CODE_SUCCESS);
            returnObject.setReturnData(ret);
        } catch (IOException e) {
            e.printStackTrace();
            returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
            returnObject.setMessages("系统反面，稍后重试....");
        }
        return returnObject;
    }