我是怎么封装poi工具类的
今天我封装了一下POI工具类
可以用这个工具类解析xls并生成List
可以通过list和标题来生成xls文件
首先是生成xls文件的部分
public static <T> void generateXls(OutputStream os,String title, Map<Integer,String> titleMap, List<T> list) throws IOException, IllegalAccessException {
//首先生成标题
HSSFWorkbook book=new HSSFWorkbook();
generateTitle(book,title,titleMap.size());
generateTableTitle(book,titleMap);
generateData(book,list);
book.write(os);
}
这里先创建一个workbook
然后调用生成表头,标题以及数据的方法
private static void generateTitle(HSSFWorkbook book, String title,int col) {
//准备样式
HSSFCellStyle cellStyle = book.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillForegroundColor(IndexedColors.RED.index);
//创建字体,这里还能设置字体呢,不光能设置大小
HSSFFont font = book.createFont();
font.setFontHeight((short) 440);
cellStyle.setFont(font);
book.createSheet();
HSSFSheet sheetAt = book.getSheetAt(0);
HSSFRow row = sheetAt.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(title);
sheetAt.addMergedRegion(new CellRangeAddress(0,0,0,col));
cell.setCellStyle(cellStyle);
}
生成标题的话就先创建样式,然后设置字体大小,以及创建单元格,然后设置文字
生成标题的部分代码在这里
private static void generateTableTitle(HSSFWorkbook book, Map<Integer, String> titleMap) {
HSSFRow row = sheet.createRow(1);
for(int i=0;i<titleMap.size();i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(titleMap.get(i));
}
}
先创建了一个工作簿,然后创建一行,接着就遍历每个存放标题文字的map然后创建单元格,并设置进去文字
接下来是处理数据的部分
private static <T>void generateData(HSSFWorkbook book, List<T> list) throws IllegalAccessException {
HSSFSheet sheetAt = book.getSheetAt(0);
for(int i=0;i<list.size();i++){
HSSFRow row = sheetAt.createRow(i+2);
writeToRow(row,list.get(i));
}
}
获得第一个工作簿,然后遍历每一行,这里从第三行开始(因为第一行是标题,第二行是表头)
然后写入每一行数据
private static void writeToRow(HSSFRow row, Object o) throws IllegalAccessException {
Class<?> aClass = o.getClass();
int index=0;
for(Field f:aClass.getDeclaredFields()){
HSSFCell cell = row.createCell(index++);
f.setAccessible(true);
cell.setCellValue(f.get(o).toString());
}
}
我们来运行下
List<Student> objects = new ArrayList<>();
objects.add(new Student("zhangjun",18,"密码"));
objects.add(new Student("lisi",88,"你懂得"));
Map<Integer,String> map=new HashMap<>();
map.put(0,"用户名");
map.put(1,"年龄");
map.put(2,"密码");
generateXls(new FileOutputStream("d:/xls/c.xls"),"我的信息",map,objects);
生成到了 d:/xls/c.xls
接下来是解析xls的代码部分
public static <T> List<T> getData(File file, Map<Integer,String> map,Class c,int startIndex) throws IOException{
List<T> result=new ArrayList<T>();
Workbook book = WorkbookFactory.create(file);
Iterator<Sheet> sheetIterator = book.sheetIterator();
while(sheetIterator.hasNext()){
Sheet sheet = sheetIterator.next();
for(int i=0;i<=sheet.getLastRowNum();i++){
try {
System.out.println(i);
if(i<startIndex)
continue;
//这里就是每一行,遍历然后添加数据到list
handleRow(map, c, result, sheet.getRow(i));
}catch (Exception e){
e.printStackTrace();
}
}
}
return result;
}
从指定file创建workbook对象,然后遍历每个sheet,然后再遍历每个sheet,然后处理每一行数据
private static <T> void handleRow(Map<Integer, String> map, Class c, List<T> result, Row row) throws IllegalAccessException, InstantiationException, NoSuchFieldException {
Object o = c.newInstance();
for(int i=0;i<row.getLastCellNum();i++){
//这里获得字段名
String fieldName = map.get(i);
setVal(o, fieldName, row.getCell(i));
}
result.add((T) o);
}
这里对传过来的class进行初始化,然后遍历每一个单元格,把值通过反射注入到对象里
private static void setVal(Object o, String fieldName, Cell cell) throws NoSuchFieldException, IllegalAccessException {
Class<?> aClass = o.getClass();
Field field = aClass.getDeclaredField(fieldName);
if(field==null)
return;
//这里说明有那个字段,就下来就把单元格里面的值拿出来并做类型转换并赋值进去
field.setAccessible(true);
field.set(o,getVal(field,cell));
}
有那个字段的话就从单元格获得,并且进行类型转换,然后再注入进去
private static Object getVal(Field field, Cell cell) {
cell.setCellType(CellType.STRING);
if(field.getType()==Integer.class||field.getType()==int.class){
return Integer.parseInt(cell.getStringCellValue());
}
if(field.getType()==Double.class||field.getType()==double.class){
return Double.parseDouble(cell.getStringCellValue());
}
if(field.getType()== Date.class){
return cell.getDateCellValue();
}
if(field.getType()==String.class){
return cell.getStringCellValue();
}
if(field.getType()==boolean.class||field.getType()==Boolean.class){
return cell.getBooleanCellValue();
}
return null;
}
这是一些简单的判断
有个很坑的地方,获得单元格数据前需要把单元格的类型设置成String
我们来调用把
Map<Integer,String> mapp=new HashMap<>();
mapp.put(0,"name");
mapp.put(1,"age");
mapp.put(2,"password");
List<Student> data = getData(new File("d:/xls/c.xls"), mapp, Student.class,2);
for(Student s:data){
System.out.println(s.toString());
}
输出结果