【Java】导入导出Excel表格

Z时代
2024-01-10
分类：综合

java

1、将excel导入到内存

1、调用工作簿Workbook的静态方法getWorkbook(),获得工作簿Workbook对象

　　InputStream in = new FileInputStream(file);  　　Workbook wb = Workbook.getWorkbook(in);

2、获取Excel表中的工作表格Sheet

3、获取行、列

　　sheet.getRows();

　　sheet.getColumns();

4、读取单元格内容

　　String result = cell.getContents();

5、关闭工作簿Workbook

　　wb.close();

代码演示

    // 实现读学生文件，将读出的信息存放于student集合中
    public List<Student> ReadFromExcel(String fileName) {
        List<Student> list = new ArrayList<Student>();
        File file = new File(fileName);
        try {
            InputStream in = new FileInputStream(file);
            Workbook wb = Workbook.getWorkbook(in);
            Sheet s = wb.getSheet(0);
            for(int i = 1; i < s.getRows(); i++)   //第一行不要
            {
                Cell[] row = s.getRow(i);           
                Student student = new Student(row[0].getContents(), row[1].getContents(),   //填充数据
                        row[2].getContents(), Float.parseFloat(row[3].getContents()),
                        Float.parseFloat(row[4].getContents()), Float.parseFloat(row[5].getContents()));
                //由于读取的数据全部都是String 类型所以要转换成Float类型 
                student.setTotalScore(student.getEnglish()+student.getJava()+student.getMath());
                student.setAverage(student.getTotalScore()/3);
                list.add(student);                      
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

2、将数据写到excel表格中

1、获取可写入工作簿WritableWorkbook对象

　　WritableWorkbook wwb = Workbook.createWorkbook(filename);

2、创建工作表格Sheet名称

　　WritableSheet sheet = book.createSheet("Sheet1",0);

3、将内容放入对应的行和列

　　sheet.addCell(new Label(j, i, info);//j表示列，i表示行，info表示写入的内容

　　//在Excel中第一个参数是列，第二个参数是行，如A1

4、写入并关闭工作簿Workbook

　　wwb.write();　　wwb.close();

代码演示

    // 将集合中的数据写入到excel文件中
    public void WriteExcel(List<Student> list, String fileName) {
        File file = new File(fileName);
        try {
            OutputStream out = new FileOutputStream(file);
            WritableWorkbook wwb = Workbook.createWorkbook(out);
            WritableSheet ws = wwb.createSheet("Sheet1", 0);
            String info[] = {"id","name","gender","java","english","math"};
            for(int j=0;j<6;j++){
                Label label = new Label(j, 0, info[j]);
                ws.addCell(label);
            }
            for(int i = 0;i < list.size();i++)
            {
                Label l = new Label(0, i+1, list.get(i).getId());//在Excel中，第一个参数表示列，第二个表示行
                Label l2 = new Label(1, i+1, list.get(i).getName());
                Label l3 = new Label(2, i+1, list.get(i).getGender());
                Label l4 = new Label(3, i+1, String.valueOf(list.get(i).getJava()));
                Label l5 = new Label(4, i+1, String.valueOf(list.get(i).getEnglish()));
                Label l6 = new Label(5, i+1, String.valueOf(list.get(i).getMath()));
                ws.addCell(l);
                ws.addCell(l2);
                ws.addCell(l3);
                ws.addCell(l4);
                ws.addCell(l5);
                ws.addCell(l6);
            }
            wwb.write();//从内存中写入文件中
            wwb.close();//关闭资源，释放内存
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }