Java读写Excel文件

基本思路

使用第三方Jar包, Apache poi来进行有关Excel的操作.

使用实体类Student的各个属性作为内存变量.

JavaBean.png

这里只放部分核心的代码, 有注释!

读取Excel文件

    // 实现读学生文件，将读出的信息存放于student集合中
    public List<Student> ReadFromExcel(String fileName) {

        List<Student> list = new ArrayList<>();
        FileInputStream in = null;
        try {
            in = new FileInputStream(fileName);
            HSSFWorkbook book = new HSSFWorkbook(in);
            // 得到第一个Sheet页
            HSSFSheet sheet = book.getSheetAt(0);
            HSSFRow row;

            for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Student student = new Student();

                row = sheet.getRow(i);
                int j = row.getFirstCellNum();
                student.setId(row.getCell(j).toString());
                student.setName(row.getCell(j + 1).toString());
                student.setGender(row.getCell(j + 2).toString());
                student.setJava(Float.parseFloat(row.getCell(j + 3).toString()));
                student.setEnglish(Float.parseFloat(row.getCell(j + 4).toString()));
                student.setMath(Float.parseFloat(row.getCell(j + 5).toString()));
                student.setTotalScore();
                student.setAverage();

                list.add(student);
            }
        } catch (java.io.IOException e) {
            e.printStackTrace();
        }

        return list;
    }

导出Excel文件

    /*
     * 将List集合数据写入excel（单个sheet）
     *
     * @param excelTitle    文件表头
     * @param list          要写入的数据集合
     * @param sheetName     sheet名称
     */
    // 将集合中的数据写入到excel文件中
    public void WriteExcel(List<Student> list, String fileName) {
        Workbook workbook = new HSSFWorkbook();

        //create sheet
        String sheetName = "student";
        String[] excelTitle = {"学号", "姓名", "性别", "总分", "平均分"};
        Sheet sheet = workbook.createSheet(sheetName);
        int rowIndex = 0; // 标识位，用于标识sheet的行号
        // 遍历数据集，将其写入excel中
        try {
            // 写表头数据
            Row titleRow = sheet.createRow(rowIndex);
            for (int i = 0; i < excelTitle.length; i++) {
                // 创建表头单元格,填值
                titleRow.createCell(i).setCellValue(excelTitle[i]);
            }
            rowIndex++;
            // 循环写入主表数据
            for (Iterator<Student> iterator = list.iterator();
                 iterator.hasNext(); ) {
                Student student = iterator.next();
                // create sheet row
                Row row = sheet.createRow(rowIndex);
                // create sheet column(单元格)
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(student.getId());
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(student.getName());
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(student.getGender());
                Cell cell3 = row.createCell(3);
                cell3.setCellValue(student.getTotalScore());
                Cell cell4 = row.createCell(4);
                cell4.setCellValue(student.getAverage());
                rowIndex++;
            }
            FileOutputStream fos = new FileOutputStream(fileName);
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

参考

http://blog.csdn.net/itsonglin/article/details/52459874
http://blog.csdn.net/lmb55/article/details/64542690

如需要, 源码(里面含第三方依赖Jar包)下载

https://github.com/menglanyingfei/Java/blob/master/CodeCollection/JavaSEProjects/excelIo/excelIo.zip