POI操作EXCEL

POI简介

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

网址

Apache POI

简单实现

操作Excle

public class ExcelUtil {public static XSSFWorkbook getWorkbook(String[] titles,String[] propertys,        List<?> datas,TransformRule transformRule){ExcelUtil excelUtil = new ExcelUtil();XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet();excelUtil.setTitle(titles, sheet);if(null!=datas&&datas.size()>0){excelUtil.setBodyRow(propertys, datas, sheet,transformRule);}return workbook;}/*设置表头*/private void setTitle(String[] titles,XSSFSheet sheet) {XSSFRow row = sheet.createRow(0);int c = 0;for (String title :titles) {XSSFCell cell = row.createCell(c);cell.setCellValue(title);c++;}}/*填充表格*/private void setBodyRow(String[] propertys,        List<?> datas, XSSFSheet sheet,TransformRule transformRule){XSSFRow row;int c;int r = 1;for (Object obj : datas) {c = 0;row = sheet.createRow(r);for (String key : propertys) {Object val = getAttributeValue(obj,key,transformRule,c);XSSFCell cell = row.createCell(c);if(null!=val)cell.setCellValue(val.toString());elsecell.setCellValue("");c++;}r++;}}/*通过反射获取对象属性值*/private Object getAttributeValue(Object obj,String attrName,        TransformRule transformRule,int index){Class<? extends Object> cl = obj.getClass();String attr = String.format("get%1$s%2$s",                       attrName.substring(0, 1).toUpperCase(),                      attrName.substring(1));try {Method method = cl.getDeclaredMethod(attr);Object val = method.invoke(obj);if(null==transformRule){return val;}else{return transformRule.form(obj,attrName, val,index);}} catch (NoSuchMethodException | SecurityException e) {e.printStackTrace();}catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {e.printStackTrace();}return null;}}

属性值转义抽象接口：用来自定义值的转义规则

public abstract class TransformRule {/** * 值转义接口 * @param obj 数据对象 * @param attribute 属性名称 * @param val 值 * @param indx 列下标 * @return */public abstract String form(Object obj,String attribute,Object val,int indx);public Object getAttributeValue(Object obj,String attrName){Class<? extends Object> cl = obj.getClass();try {Method method = cl.getDeclaredMethod(attrName);Object val = method.invoke(obj);return val;} catch (NoSuchMethodException | SecurityException e) {e.printStackTrace();}catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {e.printStackTrace();}return null;}public String objectToString(Object obj){if(null!=obj){return obj.toString();}else{return "";}}}

测试类

public class User {private Integer id;private String name;private Integer sex;public User(Integer id,String name,Integer sex){setId(id);setName(name);setSex(sex);}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getSex() {return sex;}public void setSex(Integer sex) {this.sex = sex;}public static void main(String[] args) {List<User> datas = new ArrayList<User>();datas.add(new User(1, "李连杰",1));datas.add(new User(1, "成龙", 1));datas.add(new User(1, "舒淇", 0));/*转换规则*/TransformRule transformRule = new TransformRule() {@Overridepublic String form(Object obj, String attribute, Object val,int indx) {if("sex".equals(attribute)){if (val.equals(1)) {return "男";}else if (val.equals(0)) {return "女";}}return objectToString(val);}};String[] propertys = {"id","name","sex"};String[] titles = {"演员编号","名称","性别"};XSSFWorkbook bodyRow = getWorkbook(titles,propertys, datas,transformRule);FileOutputStream out;try {out = new FileOutputStream(new File("C:/Users/Administrator/Desktop/test.xlsx"));bodyRow.write(out);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}