POI简介
Apache POI是Apache软件基金会(http://baike.baidu.com/view/7044910.htm)的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
网址
Apache POI(http://poi.apache.org/)
简单实现
操作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());
- else
- cell.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() {
- @Override
- public 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();
- }
- }
- }