java实现Excel文件读写操作

雨点打透心脏的1/2处 2021-06-24 16:09 511阅读 0赞

#### 首先导入Excel操作相关依赖 ####

<dependency>
                <groupId>net.sourceforge.jexcelapi</groupId>
                <artifactId>jxl</artifactId>
                <version>2.6.10</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.9</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>3.9</version>
            </dependency>

#### Excel文件读取操作的方法 ####

/**
         * 去读Excel的方法
         * @param filePath 要读取的excel文件路径
         * @return
         */
        public List readExcel(String filePath) {
            try {
                File file = new File(filePath);
                // 创建输入流，读取Excel
                InputStream is = new FileInputStream(file.getAbsolutePath());
                // jxl提供的Workbook类
                Workbook wb = Workbook.getWorkbook(is);
                // Excel的页签数量
                int sheet_size = wb.getNumberOfSheets();
                List<List<List>> result = new ArrayList<>();
                for (int index = 0; index < sheet_size; index++) {
                    List<List> outerList = new ArrayList<>();
                    // 每个页签创建一个Sheet对象
                    Sheet sheet = wb.getSheet(index);
                    // sheet.getRows()返回该页的总行数
                    for (int i = 0, m = 0; i < sheet.getRows(); i++, m++) {
                        List<String> innerList = new ArrayList();
                        // sheet.getColumns()返回该页的总列数
                        for (int j = 0; j < sheet.getColumns(); j++) {
                            String cellinfo = sheet.getCell(j, i).getContents();
                            innerList.add(cellinfo);
                        }
                        outerList.add(m, innerList);
                    }
                    result.add(outerList);
                }
                //返回Excel全部数据的集合
                return result;
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }

#### Excel创建并写入数据的方法 ####

public void writeExcel(List<List<List>> excelAllList, String finalXlsxPath) throws WriteException, IOException {
            WritableWorkbook workbook;
            WritableSheet sheet;
            OutputStream os;
            File finalXlsxFile = new File(finalXlsxPath);
            //文件若不存在就新建一个
            if (!finalXlsxFile.exists()) {
                finalXlsxFile.createNewFile();
            }
            //创建输出流
            os = new FileOutputStream(dataFile);
            //创建工作薄
            workbook = Workbook.createWorkbook(os);
            int num = 1;
            for(List<List> excelList : excelAllList) {
                //创建新的一页
                sheet = workbook.createSheet("Sheet"+num, num-1);
                num++;
                for (int i = 0; i < excelList.size(); i++, r++) {
                    List list = excelList.get(i);
                    //迭代列，默认从0开始
                    for (int j = 0; j < list.size(); j++) {
                        //创建单元格并添加到页中
                        String cellStr = list.get(j).toString();
                        Label label = new Label(j, r,cellStr);
                        sheet.addCell(label);
                    }
                }
            }
            //把创建的内容写入到输出流中，并关闭输出流
            workbook.write();
            workbook.close();
            os.close();
        }

#### 读写Excel方法示例 ####

/**
         *
         * @param inPath 输入的文件路径    如：C:\\Users\\Administrator\\Desktop\\data.xls
         * @param outPath 输出的文件路径
         * @throws IOException
         * @throws WriteException
         */
        public  void domain(String inPath,String outPath) throws IOException, WriteException {
            ReadExcel obj = new ReadExcel();
            List<List<List>> excelAllList = obj.readExcel(inPath);
            /**
             * 这里可以对数据集合进行自定义处理然后再输出
             * dosomthing(excelAllList)
             */
            writeExcel(excelAllList,outPath);
        }

代码已经过本人测试，请放心使用。目前仅支持.xls结尾的excel文件。