POI excel导出的通用util

基于Apache POI实现excel的通用util,通过反射和泛型实现,

使用方法:

   @Test
    public void exportExcel(){
        List<Model> models = new ArrayList<Model>();
        Model model = new Model("value1",1,1.1,new Date(), Calendar.getInstance());
        Model model1  = new Model("value2",2,2.22,new Date(), Calendar.getInstance());
        Model model2  = new Model("value3",3,3.33,new Date(), Calendar.getInstance());
        models.add(model);
        models.add(model1);
        models.add(model2);
        String[] head = {"String","Integer","Double","Date","Calendar"};
        String[] fields = {"valueString","valueInt"};
        ExcelUitl excelUitl = new ExcelUitl();
        Map map = excelUitl.exportHSSFExcel("C:\\",null,models,null,null,head);
        ExcelUitl excelUitl2 = new ExcelUitl();
        Map map1 = excelUitl2.exportHSSFExcel("C:\\","fields",models,fields,"表1",null);



//        Assert.assertEquals("200",map.get("statuscode"));
        Assert.assertEquals("200",map1.get("statuscode"));

    }

源码:

public class ExcelUitl {


    //头部cell
    private final static int TITLE_STYLE = 0;
    //正常
    private final static int NORMAL_STYLE = 1;


    private int rowNumber = 0;
    private int colNumber = 0;

    public int getRowNumber() {
        return rowNumber;
    }

    public void setRowNumber(int rowNumber) {
        this.rowNumber = rowNumber;
    }

    public int getColNumber() {
        return colNumber;
    }

    public void setColNumber(int colNumber) {
        this.colNumber = colNumber;
    }

    private void nextRow() {
        this.rowNumber++;
    }


    /**
     *
     * @param filePath 文件目录
     * @param filename 文件名
     * @param objects 数据
     * @param fields 需要输出的属性名
     * @param sheetstitle sheetname
     * @param head toubu
     * @return
     */
    public Map exportHSSFExcel(String filePath,String filename, List<? extends Object> objects, String[] fields, String sheetstitle, String[] head) {


        setColNumber(0);
        setRowNumber(0);
        Sheet sheet = null;
        Map map = new HashMap();
        FileOutputStream out = null;
        try {

            if (objects != null && objects.size() > 0) {

                Workbook wb = new HSSFWorkbook();
                sheet = creatSheet(wb,sheetstitle);
                creatHeadCell(wb,sheet,head,0);
                creatRow(wb,sheet,objects,fields);
                creatFile(wb,filePath,filename,out,map);
            }else {
                map.put("statuscode", "500");
                map.put("info","暂无数据");
            }
        } catch (IllegalAccessException e) {
            map.put("info", "object属性错误:列:" + getColNumber() + "行:" + getRowNumber());
            map.put("statuscode", "500");
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            map.put("statuscode", "500");
            map.put("info", "文件夹不存在:列:" + getColNumber() + "行:" +  getRowNumber());
            e.printStackTrace();
        } catch (IOException e) {
            map.put("info", "IO异常:列:" + getColNumber() + "行:" +  getRowNumber());
            map.put("statuscode", "500");
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            map.put("statuscode", "500");
            map.put("info", "反射异常:列:" + getColNumber() + "行:" +  getRowNumber());
            e.printStackTrace();
        } catch (IntrospectionException e) {
            map.put("statuscode", "500");
            map.put("info", "反射异常:列:" + getColNumber() + "行:" +  getRowNumber());
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        }

        return map;

    }

    /**
     * @param obj   对象
     * @param field 属性名
     * @return 通过getset 获得属性值
     */
    private Object method(Object obj, String field) throws IntrospectionException, InvocationTargetException, IllegalAccessException {

        Class clazz = obj.getClass();

        PropertyDescriptor pd = new PropertyDescriptor(field, clazz);
        Method getMethod = pd.getReadMethod();//获得get方法
        Object o = getMethod.invoke(obj);//执行get方法返回一个Object
        return o;

    }

    private   List<Object> getFields(Object obj) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
        Class clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        List<Object> strings = new ArrayList<Object>();
        for (int i = 0; i < fields.length; i++) {
            PropertyDescriptor pd = new PropertyDescriptor(fields[i].getName(), clazz);
            Method getMethod = pd.getReadMethod();//获得get方法
            Object o = getMethod.invoke(obj);//执行get方法返回一个Object
            strings.add(o);
        }
        return  strings;
    }
    /**
     * @param wb
     * @param row    行
     * @param column 列
     * @param value  值
     * @param type   类型 ,
     */
    private void createCell(Workbook wb, Row row, short column, Object value, int type) {

        Cell cell = row.getCell(column);
        if (cell == null) {
            cell = row.createCell(column);
        }
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue(Integer.toString((Integer) value));
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        } else if (value instanceof RichTextString) {
            cell.setCellValue((RichTextString) value);
        }


        setStyle(wb, cell, type);

    }

    /**
     * 设置cell属性
     *
     * @param wb
     * @param cell
     * @param type
     */
    private void setStyle(Workbook wb, Cell cell, int type) {

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //居中代码
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //边框代码
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setWrapText(true);
        Font font = wb.createFont();
        font.setFontName("宋体");//字体
        if (type == TITLE_STYLE) {
            font.setFontHeightInPoints((short) 12);
            font.setBold(true);
        }

        cellStyle.setFont(font);

        cell.setCellStyle(cellStyle);
    }

    private void creatHeadCell(Workbook wb, Sheet sheet, String[] head, int row) {

        if (head != null && head.length > 0) {
            Row row0 = sheet.createRow((short) (row));
            for (int y = 0; y < head.length; y++) {
                //头部.list.get(0);
                createCell(wb, row0, (short) (y), head[y], 0);
            }

            nextRow();
        }
    }

    private Sheet creatSheet(Workbook wb, String sheetstitle) {
        Sheet sheet = null;
        if (sheetstitle != null && sheetstitle.length() > 0) {
            sheet = wb.createSheet(sheetstitle);
        }else {
            sheet =  wb.createSheet();
        }

        return sheet;
    }


    private void creatRow(Workbook wb, Sheet sheet,List<? extends Object> objects,String[] fields) throws InvocationTargetException, IllegalAccessException, IntrospectionException {
        if(fields==null){
            creatRow(wb,sheet,objects);
            return;
        }
        for (; rowNumber < objects.size(); rowNumber++) {
            Object object = objects.get(rowNumber);
            Row rowi = sheet.createRow((short) (rowNumber));

            for (colNumber = 0; colNumber < fields.length; colNumber++) {
                createCell(wb, rowi, (short) (colNumber), method(object, fields[colNumber]), 1);
            }
//            sheet.autoSizeColumn((short) (rowNumber));
        }
    }

    private void creatRow(Workbook wb, Sheet sheet,List<? extends Object> objects) throws InvocationTargetException, IllegalAccessException, IntrospectionException {


        for (; rowNumber < objects.size(); rowNumber++) {
            //数据列
            Object object = objects.get(rowNumber);
            Row rowi = sheet.createRow((short) (rowNumber));
            List<Object> fields  = getFields(objects.get(rowNumber));
            for (colNumber = 0; colNumber < fields.size(); colNumber++) {
                createCell(wb, rowi, (short) (colNumber), fields.get(colNumber), 1);
            }
//            sheet.autoSizeColumn((short) (rowNumber));
        }

    }

    private void creatFile(Workbook wb,String filePath,String filename,FileOutputStream out,Map map) throws IOException {
        File file1 = new File(filePath);
        if (!file1.exists()) {
            file1.mkdirs();
        }
        long time = Calendar.getInstance().getTimeInMillis();
        String file = filePath + "/" + time +(filename!=null?filename:"")+ ".xls";
        out = new FileOutputStream(file);
        wb.write(out);
        map.put("filename", time +(filename!=null?filename:"")+ ".xls");
        map.put("statuscode", "200");
    }

}

github:https://github.com/keygod/simpleExportExcel