Excel处理笔记

POI处理Excel

poi对象：
工作簿：HSSWorkbook 03版，XSSFWorkbook 07版，SXSSFWorkbook 07spuer版
工作表：
行：
列：

环境搭建

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>

代码实例

下载创建：

    /*
    *POI处理Excel
    */
    @InJect
    @RequestMapping("poi")
    public void downLoadPoiExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //默认值是100
        //xls/03版,xlsx/07版,
        //创建07版super实例
        SXSSFWorkbook workbook=new SXSSFWorkbook();
        //创建页表 可以添加名字，没有名字默认名字是 Sheet0 且只有1个页表
         Sheet sheet = workbook.createSheet();
        //创建行数；
        for (int i = 0; i < 60000; i++) {
             Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                 row.createCell(j).setCellValue(j);
            }
        }
        System.out.println("数据填充：over");

        // 设置响应体相关编码信息
        response.setCharacterEncoding("utf-8");
        //设置返回值类型
        response.setContentType("application/vnd.ms-excel");
        //文件名 编码utf-8
        String fileName = URLEncoder.encode("用户信息文档","utf-8");
        //告诉浏览器这个文件的名字和类型，attachment：作为附件下载；inline：直接打开
        response.setHeader("Content-disposition","attachment;filename="+ fileName + ".xlsx");
        //输出   xls/03版,xlsx/07版,
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
            outputStream.flush();
        }finally {
            workbook.dispose();
        }


    }

上传读取：

    @InJect
    @RequestMapping("upload")//MultipartFile file
    public void upLoadPoiExcel(){
        //可以有名字判断使用那种类型的对象来解析Excel ，
        //final String name = file.getName();
        //模拟路径 D:\chromelog
        String dirFileName="D:\\chromelog\\用户信息文档.xlsx";
        try (FileInputStream fileInputStream = new FileInputStream(dirFileName)) {
            //可以有名字判断使用那种类型的对象来解析Excel xls/03版HSSFWorkbook,xlsx/07版/XSSFWorkbook,
            //Workbook workbook=new HSSFWorkbook(fileInputStream);//xls/03版
           Workbook workbook=new XSSFWorkbook(fileInputStream);//xlsx/07版
            //获取sheet的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            workbook.getSheetAt(0);//得到对应的sheet页 从0开始
            System.out.println("sheet的数量: "+numberOfSheets);

            //由Excel的Sheet 的默认名字得到Sheet
            Sheet sheet = workbook.getSheet("Sheet0");
            //获取到sheet的Row行数;获取到最后一行的行数 (Row的index是从0开始的)
            //获取的最后一个rowNum是下标，得到的数字应该加1
            //physicalNumberOfRows 这个方法更好一些
            int lastRowNum = sheet.getLastRowNum();
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            System.out.println("获取到row的行数"+lastRowNum+" physicalNumberOfRows"+physicalNumberOfRows);

            for (int i = 0; i < physicalNumberOfRows; i++) {
                Row row = sheet.getRow(i);
                //如果有一行为空就跳过,否则会报空指针异常
                if (row==null)
                    continue;
                //获取到最后一个cell数的下标
                short lastCellNum = row.getLastCellNum();
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();

                System.out.println("获取到call的数"+lastCellNum+"  physicalNumberOfCells: "+physicalNumberOfCells);
                for (int j = 0; j < lastCellNum; j++) {
                    Cell cell = row.getCell(j);
                    //非空处理
                    if (cell!=null)
                        System.out.println("第"+i+"row的"+"第"+j+"个: "+cell.getNumericCellValue());
                }
                //在这里就可以持久化或处理数据了
            }

        } catch (IOException e) {
        }


    }