利用poi解析excle并导出

迈不过友情╰ 2022-05-20 10:21 238阅读 0赞

*  需求
    
    > 将一个excle中的数据，依据某个规则，拆分成不同的excle，并导出
 *  思路
    
    > 依次解析excle，分别将符合规则的数据，放到list中，然后将list中的数据写进新建的excle
 *  需要的jar包

dom4j-1.6.1.jar
    poi-3.7.jar
    poi-ooxml-3.7.jar
    poi-ooxml-schemas-3.7.jar
    xmlbeans-2.3.0.jar

*  代码实现

> 实体类

package poi;
    
    public class Bank { 
        private String id;
        private String name;
        private String empName;
        public String getEmpName() {
            return empName;
        }
        public void setEmpName(String empName) {
            this.empName = empName;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public String getId() {
            return id;
        }
        public void setId(String id) {
            this.id = id;
        }
        public Bank(String id, String name, String empName) {
            super();
            this.id = id;
            this.name = name;
            this.empName = empName;
        }
        @Override
        public String toString() {
            return "Bank [id=" + id + ", name=" + name + ", empName=" + empName + ", getEmpName()=" + getEmpName()
                    + ", getName()=" + getName() + ", getId()=" + getId() + ", getClass()=" + getClass() + ", hashCode()="
                    + hashCode() + ", toString()=" + super.toString() + "]";
        }
    
    }

> 代码

package poi;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    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.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    
    public class AnalyzeExcle {
    
        public static void main(String[] args) throws Exception {
            //得到原始文件
            File file = new File("E:\\study\\code\\self\\eclipse\\demo\\src\\demo.xlsx");
            Workbook workbook = null;//相当于一个excle文件
            Sheet sheet1 = null;
            //以此种方式新建workbook可以兼容excle2003和excle20007及以后的，用后缀名方式判断经测试，不行
            workbook = WorkbookFactory.create(new FileInputStream(file));
            sheet1 = workbook.getSheetAt(0);//excle中的sheet
    
            List<Bank> list1234 = new ArrayList<>();
            List<Bank> list1235 = new ArrayList<>();
            List<Bank> list1236 = new ArrayList<>();
            List<Bank> list1237 = new ArrayList<>();
    
            for (Row row : sheet1) {//遍历sheet
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//第一列全是数字，如果直接读取，会报错，需先设置类型
                String id = row.getCell(0).getStringCellValue();//读取第一个单元格的值的值
                String name = row.getCell(1).getStringCellValue();
                String empName = row.getCell(2).getStringCellValue();
                Bank bank = new Bank(id, name, empName);
                if (id.equals("1234")) {
                    list1234.add(bank);//放到list中
                } else if (id.equals("1235")) {
                    list1235.add(bank);
                } else if (id.equals("1236")) {
                    list1236.add(bank);
                } else if (id.equals("1237")) {
                    list1237.add(bank);
                }
    
            }
            write2Excle(list1234,"1234");
            write2Excle(list1235,"1235");
            write2Excle(list1236,"1236");
            write2Excle(list1237,"1237");
        }
    
        public static void write2Excle(List<Bank> list, String orgnCode) throws Exception {
            HSSFWorkbook workbook = new HSSFWorkbook();//新建excle
            HSSFSheet sheet = workbook.createSheet();//新建一个sheet
            HSSFRow row = sheet.createRow(0);//第一行
            row.createCell(0).setCellValue("支行号");//设置第一行第一列的值
            row.createCell(1).setCellValue("网点名称");
            row.createCell(2).setCellValue("员工姓名");
    
            for (Bank bank : list) {//遍历list
                //依次建立一行
                HSSFRow r = sheet.createRow(sheet.getLastRowNum()+1);
                r.createCell(0).setCellValue(bank.getId());
                r.createCell(1).setCellValue(bank.getName());
                r.createCell(2).setCellValue(bank.getEmpName());
            }
    
            //得到输出流
            OutputStream out = new FileOutputStream("e:\\" + orgnCode + ".xlsx");
            //写出数据
            workbook.write(out);
        }
    
    
    }