java poi 对excel的读写

傷城~ 2022-07-14 01:50 236阅读 0赞

package com.ming.poi;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    
    public class ToExcel {
        public static void main(String[] args) throws Exception {
    //      ToExcel.writeExcel();
            ToExcel.readExcel();
        }
    
        public static List<Student> getStudent() throws Exception {
            Student student = null;
            List<Student> list = new ArrayList<Student>();
            for (int i = 0; i < 10; i++) { 
                student = new Student(i + "", "小明" + i, 80 + i + "");
                list.add(student);
            }
            return list;
        }
    
        public static void writeExcel() throws Exception {
            // 1.创建一个webbook，对应一个excel
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 2.在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = workbook.createSheet();
            // 3.创建表头
            HSSFRow row = sheet.createRow(0);
            // 4.创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 单元格居中
            HSSFCell cell = null;
            String[] header = { "学号", "姓名", "成绩" };
            for (int i = 0; i < header.length; i++) { 
                cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(header[i]);
            }
            List<Student> list = ToExcel.getStudent();
            for (int i = 0; i < list.size(); i++) { 
                row = sheet.createRow(i + 1);
                Student student = list.get(i);
                // 5.创建单元格,存数据
                row.createCell(0).setCellValue(student.getSno());
                ;
                row.createCell(1).setCellValue(student.getSname());
                ;
                row.createCell(2).setCellValue(student.getSgrade());
                ;
            }
            OutputStream stream = new FileOutputStream("e://ming.xls");
            // 6.导出excel
            workbook.write(stream);
            stream.flush();
            stream.close();
        }
    
        public static void readExcel() {
            FileInputStream in;
            List<Student> list = new ArrayList<Student>();
            Student student = null;
            try {
                // 通过流，读取excel文件 ，转化成excel
                in = new FileInputStream("e://ming.xls");
                HSSFWorkbook workbook = new HSSFWorkbook(in);
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) { 
                    HSSFSheet sheet = workbook.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    for (int j = 0; j <=sheet.getLastRowNum(); j++) { 
                        HSSFRow row = sheet.getRow(j);
                        if (row != null) {
                            student = new Student(row.getCell(0)
                                    .getStringCellValue(), row.getCell(1)
                                    .getStringCellValue(), row.getCell(2)
                                    .getStringCellValue());
                            list.add(student);
                        }
                    }
    
                }
                for (Student stu : list) {
                    System.out.println(stu.getSname());
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    }

student

package com.ming.poi;
    
    import java.io.Serializable;
    
    public class Student implements Serializable { 
        private String sno;
        private String sname;
        private String sgrade;
    
        public Student() {
        }
        public Student(String sno, String sname, String sgrade) {
            super();
            this.sno = sno;
            this.sname = sname;
            this.sgrade = sgrade;
        }
        public String getSno() {
            return sno;
        }
        public void setSno(String sno) {
            this.sno = sno;
        }
        public String getSname() {
            return sname;
        }
        public void setSname(String sname) {
            this.sname = sname;
        }
        public String getSgrade() {
            return sgrade;
        }
        public void setSgrade(String sgrade) {
            this.sgrade = sgrade;
        }
    
    }