需求
将一个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);
}
}