java excel导出（基于注解）

最新推荐文章于 2024-07-15 02:05:06 发布

weixin_30409849

最新推荐文章于 2024-07-15 02:05:06 发布

阅读量416

点赞数

文章标签： java runtime

原文链接：http://www.cnblogs.com/xxpandong/p/10690400.html

版权

　　小白，做日志只是为了方便自己查看，能帮到别人当然更好，不喜勿喷。

　　上代码

　　依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

　注解，使用了俩个注解，一个是sheet公用属性，以及单元格属性，只是简单的几个属性，可自行扩展。

　sheet公用注解：

package com.authorize.utils.excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 
 * @filename ExcelBookAnnotation.java
 * @pakage com.authorize.utils.excel
 * @descption TODO(用一句话表述类的作用)
 * @author Pandong
 * @date 2019年4月8日
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelBookAnnotation {

    /**
     * 标题
     * @return
     */
    String title();
    
}

单元格注解：

package com.authorize.utils.excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import org.apache.poi.hssf.util.HSSFColor;

/**
 * 
 * @filename ExcelAnnotation.java
 * @pakage com.authorize.utils.excel
 * @descption TODO(用一句话表述类的作用)
 * @author Pandong
 * @date 2019年4月8日
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColAnnotation {

    
    /**
     * 列名
     * @return
     */
    String text() default "";
    
    /**
     * 列宽
     * @return
     */
    int colWidth() default 6000;
    
    /**
     * 字体颜色，默认黑色
     * @return
     */
    short color() default HSSFColor.BLACK.index;
    
    /**
     * 导出是是否忽略该字段,默认不忽略
     * @return
     */
    int ignore() default 0;
    
}

实体类，其中使用了lombok，感兴趣可以百度一下，不用可以不相关注解删掉，自己写get/set等方法。

package com.authorize.utils.excel;

import java.io.Serializable;

import org.apache.poi.hssf.util.HSSFColor;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 
 * @filename ExcelBean.java
 * @pakage com.authorize.utils.excel
 * @descption TODO(用一句话表述类的作用)
 * @author Pandong
 * @date 2019年4月8日
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelBookAnnotation(title = "日常工作表")
public class ExcelBean implements Serializable{

    @ExcelColAnnotation( ignore = 1 )
    private static final long serialVersionUID = 4248622093488850427L;
    
    @ExcelColAnnotation(colWidth = 8000, text = "姓名",color = HSSFColor.RED.index)
    private String name;
    @ExcelColAnnotation( text = "年龄", colWidth = 2000)
    private int age;
    @ExcelColAnnotation( text = "地址", colWidth = 12000)
    private String addr;
    
}

最后就是excel导出的工具类了：

package com.authorize.utils.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.authorize.utils.CommonUtils;
import com.authorize.utils.exception.CustomException;

public class ExcelUtils<T> {

    private static final Log log = LogFactory.getLog(ExcelUtils.class);
    
    /**
     * 行高
     */
    private int rowHeight = 400;
    /**
     * 列宽
     */
    private int colWidth = 8500;
    /**
     * 起始位置
     */
    private int rowIndex = 0;
    /**
     * 默认标题
     */
    private String title = "defaultExcel";
    
    private Workbook workbook;
    private Sheet sheet;
    /**
     * 公共列样式
     */
    private CellStyle cellStyle;
    /**
     * 操作的实体类
     */
    private T obj;
    /**
     * 列属性集合
     */
    private List<Map<String,Object>> colList = new ArrayList<>();
    /**
     * 列样式集合
     */
    private List<CellStyle> styleList = new ArrayList<>();
    
    private ClassUtils util = new ClassUtils();
    
    public ExcelUtils( T obj ) {
        this.obj = obj;
        initWorkbook();
    }
    
    public ExcelUtils(T obj,int rowHeight, int colWidth, int rowIndex, String title) {
        this(obj);
        this.rowHeight = rowHeight;
        this.colWidth = colWidth;
        this.rowIndex = rowIndex;
        this.title = title;
    }

    /**
     * 默认文档设置文档
     */
    private void initWorkbook() {
        if ( CommonUtils.isEmpty(this.obj) ) {
            throw new CustomException("未指定导出实体类,无法进行导出操作");
        }
        util.parseBookAnnotation();
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(this.title);    //    创建工作页
        sheet.setDefaultColumnWidth(colWidth);    //    设置默认列宽
        cellStyle = createCellStyle();
        cellStyle.setFont(createFont(null, (short)0, (short)0));
        titleSetting();
    }
    
    /**
     * 标题、列名相关设置
     */
    private void titleSetting() {
        Row topRow = createRow((short)600);
        mergedRegion(0, 0, 0, this.colList.size()-1);    //    合并标题行
        Cell cell = createCell(topRow,0,cellStyle);
        cell.setCellValue(this.title);
        Row textRow = createRow((short)0);
        for ( int i = 0; i < colList.size(); i++ ) {
            Map<String,Object> fieldMap = colList.get(i); 
            sheet.setColumnWidth(i, Integer.parseInt(fieldMap.get("width").toString()));
            Cell cell1 =  createCell(textRow,i,cellStyle);
            cell1.setCellValue(fieldMap.get("text").toString());
            addColStlye(null, (short)0, Short.parseShort(fieldMap.get("color").toString()));
        }
    }
    
    /**
     * 创建字体对象
     * @param fontName
     *             字体库名称
     * @param fontSize
     *             字体大小-传0默认14
     * @param color
     *             字体颜色参考{@link HSSFColor.BLACK.index}
     * @return
     */
    private Font createFont(String fontName, short fontSize,short color) {
        Font font = this.workbook.createFont();
        if ( CommonUtils.isEmpty(fontName) ) {
            fontName = "宋体";
        }
        if ( fontSize == 0 ) {
            fontSize = (short)14;
        }
        if ( color == 0 ) {
            color = HSSFColor.BLACK.index;
        }
         font.setFontName("宋体");        //设置为宋体字
         font.setFontHeightInPoints(fontSize);        //设置字体大小
         font.setColor(color);
         return font;
    }
    
    
    
    /**
     * 创建列样式
     * @param alignment
     * @param vertical
     * @return
     */
    private CellStyle createCellStyle(short ...alignments) {
        CellStyle style = this.workbook.createCellStyle();
        short alignment = HSSFCellStyle.ALIGN_CENTER_SELECTION,vertical = HSSFCellStyle.VERTICAL_CENTER;
        if ( alignments.length > 0 ) {
            alignment = alignments[0];
            if ( alignments.length > 1 ) {
                vertical = alignments[1];
            }
        }
        //水平居中
        style.setAlignment(alignment);
        //垂直居中
        style.setVerticalAlignment(vertical);
        return style;
    }
    
    /**
     * 合并行、列
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    private void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress region = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);    //    合并行
        sheet.addMergedRegion(region);
    }
    
    /**
     * 创建单元格
     * @param row
     * @param index
     * @param style
     * @return
     */
    private Cell createCell ( Row row,int index,CellStyle style ) {
        Cell cell = row.createCell(index);
        if ( CommonUtils.isNotEmpty(style) ) {
            cell.setCellStyle(style);
        }
        return cell;
    }
    
    /**
     * 创建行
     * @return
     */
    private Row createRow ( short rowHeight ) {
        Row row = sheet.createRow(this.rowIndex);
        
        if ( rowHeight == 0 ) {
            rowHeight = (short)this.rowHeight;
        }
        row.setHeight(rowHeight);
        this.rowIndex ++;
        return row;
    }
    
    /**
     * 创建每一列的样式
     * @param fontName
     * @param fontSize
     * @param color
     * @param alignments
     */
    private void addColStlye(String fontName, short fontSize,short color,short ...alignments) {
        Font ft = createFont(fontName, fontSize, color);
        CellStyle style = createCellStyle(alignments);
        style.setFont(ft);
        styleList.add(style);
    }
    
    /**
     * 生成Excel表
     * @param list
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     */
    public void createExcel(List<T> list,String parentPath) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        for ( T temp : list ) {
            Row row = createRow((short)0);
            for (int i = 0; i < colList.size(); i ++ ) {
                Map<String,Object> cl = colList.get(i);
                Cell cell = createCell(row,i,styleList.get(i));
                String methodName = cl.get("methodName").toString();
                Object obj = util.valueToGet(temp, methodName);
                cell.setCellValue(obj.toString());
            }
        }
        File file = new File(parentPath);
        if (!file.exists()){
            file.createNewFile();
        }
        FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();
    }
    
    
    public List<ExcelBean> getListBean(){
        List<ExcelBean> list = new ArrayList<>();
        ExcelBean bean = null;
        Random random = new Random();
        for ( int i = 0; i < 100; i++ ) {
            bean = new ExcelBean();
            bean.setAddr(UUID.randomUUID().toString().substring(0, 15));
            bean.setAge(random.nextInt(100));
            bean.setName("张三"+(i + 1) +"号");
            list.add(bean);
        }
        return list;
    }
    
    class ClassUtils{
        
        
        private ClassUtils() {}
        
        
        /**
         * 反射获取value
         * @param object
         * @return
         * @throws SecurityException 
         * @throws NoSuchMethodException 
         * @throws InvocationTargetException 
         * @throws IllegalArgumentException 
         * @throws IllegalAccessException 
         */
        private Object valueToGet( Object object, String methodName ) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
                Method method = object.getClass().getDeclaredMethod(methodName);
                return method.invoke(object);
        }
        
        /**
         * 首字母大写
         * @param fieldName
         * @return
         */
        public String convertMethodName( String fieldName ) {
            String newField = fieldName.substring(1, fieldName.length());
            return fieldName.substring(0,1).toUpperCase()+newField;
        }
        
        /**
         * 通过注解获取导出sheet相关注解属性
         */
        public void parseBookAnnotation( ) {
            Annotation[] ans = ExcelUtils.this.obj.getClass().getAnnotations();
            for ( Annotation temp : ans ) {
                if ( temp instanceof ExcelBookAnnotation) {
                    String title = ((ExcelBookAnnotation) temp).title();
                    ExcelUtils.this.title = title;
                    parseFielAnnotation( );
                }
            }
        }
        
         /**
         * 通过注解获取列相关注解属性
         */
        public void parseFielAnnotation( ) {
            Field [] fiels = ExcelUtils.this.obj.getClass().getDeclaredFields();
            for ( Field temp : fiels ) {
                Annotation[] ans = temp.getAnnotations();
                for ( Annotation tempAn : ans ) {
                    if ( tempAn instanceof ExcelColAnnotation ) {
                        ExcelColAnnotation col = ((ExcelColAnnotation) tempAn);
                        int ignore = col.ignore();
                        if ( ignore == 0 ) {
                            Map<String,Object> fieldMap = new HashMap<>();
                            fieldMap.put("width", col.colWidth());
                            fieldMap.put("color", col.color());
                            fieldMap.put("text", col.text());
                            fieldMap.put("methodName", "get"+convertMethodName(temp.getName()));
                            ExcelUtils.this.colList.add(fieldMap);
                        }
                    }
                }
            }
        }
        
    }
    
    
    public static void main(String[] args) throws IOException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        ExcelUtils<ExcelBean> excel = new ExcelUtils<ExcelBean>( new ExcelBean());
        List<ExcelBean> list = excel.getListBean();
        excel.createExcel(list, "C:\\Users\\Administrator\\Desktop\\test_bak\\test.xlsx");
    }
    
}