JAVA导出Excel通用类

来源：互联网发布：软件工程与医疗云计算编辑：程序博客网时间：2024/06/01 19:56

package com.ss.util.excel;

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

/**
* Excel实体BEAN的属性注解
* @author MrGao
* 2010-7-12下午05:10:37
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {

String name();//Excel列名
int width();//Excel列宽
int id();//Excel列ID

}

package com.ss.util.excel;

import java.lang.reflect.Field;
import java.util.Comparator;

@SuppressWarnings("unchecked")
public class FieldComparator implements Comparator {

    public int compare(Object arg0, Object arg1) {
        Field fieldOne = (Field)arg0;
        Field fieldTwo = (Field)arg1;
        ExcelAnnotation annoOne = fieldOne.getAnnotation(ExcelAnnotation.class);
        ExcelAnnotation annoTwo = fieldTwo.getAnnotation(ExcelAnnotation.class);
        return annoOne.id()-annoTwo.id();
    }

}

package com.ss.util.excel;

import java.awt.Color;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Pattern;
import jxl.format.RGB;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
* 导出Excel文件
*
* @author MrGao 2010-7-13下午02:19:51
*/
public class ExcelExport {

   /**
   * 生成Excel
   *
   * @param models
   *            封装需要到处的数据BEAN结合
   * @param className
   *            导成Excel的实体BEAN包名.类名
   * @param tempPath
   *            生成Excel存放的临时路径
   * @param excelName
   *            生成的Excel名
   */
   @SuppressWarnings("unchecked")
   public static void createExcel(List models, String className,
           String tempPath, String excelName) {
       Class clasVo = null;

       try {
           clasVo = Class.forName(className);
           OutputStream os = new FileOutputStream(tempPath + "\\" + excelName
                   + ".xls");
           WritableWorkbook workbook = Workbook.createWorkbook(os);
           WritableSheet sheet = workbook.createSheet(excelName, 0);
           // 用于标题
           WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 17,
                   WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                   jxl.format.Colour.WHITE);
           WritableCellFormat wcf_title = new WritableCellFormat(titleFont);
           wcf_title.setBackground(Colour.TEAL,Pattern.SOLID);
           wcf_title.setBorder(Border.ALL, BorderLineStyle.DOUBLE, Colour.OCEAN_BLUE);
           wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
           wcf_title.setAlignment(Alignment.CENTRE);

           // 用于正文
           WritableFont NormalFont = new WritableFont(WritableFont.TAHOMA, 11);
           WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
           wcf_center.setBorder(Border.ALL, BorderLineStyle.DOUBLE, Colour.GRAY_25);
           wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
           wcf_center.setAlignment(Alignment.CENTRE);
           wcf_center.setWrap(true); // 是否换行

           sheet.addCell(new Label(0, 0, excelName, wcf_title));
           sheet.mergeCells(0, 0, clasVo.getDeclaredFields().length - 1, 0);

           // 获取属性
           Field[] fields = clasVo.getDeclaredFields();
           //按照注解id排序Excel列
           Arrays.sort(fields,new FieldComparator());
           for (int i = 0; i < fields.length; i++) {
               Field field = fields[i];
               if (field.isAnnotationPresent(ExcelAnnotation.class)) {
                   //获取该字段的注解对象
                   ExcelAnnotation anno = field
                           .getAnnotation(ExcelAnnotation.class);
                   sheet.setColumnView(i, anno.width());
                   sheet.addCell(new Label(i, 1, anno.name(), wcf_center));
               }
           }

           int rowId = 2;// 写入第几行第一行为列头数据从第二行开始写
           for (Object ssTopModel : models) {
               int columnId = 0;// 写入第几列第一列为自动计算的行号数据从第二列开始写
               // 获取该类并获取自身方法
               Class clazz = ssTopModel.getClass();
               for (int i = 0; i < fields.length; i++) {
                   Field field = fields[i];
                   if (field.isAnnotationPresent(ExcelAnnotation.class)) {
                       String methodName = "get"+field.getName().substring(0,1).toUpperCase()+field.getName().substring(1);
                       Method method = clazz.getMethod(methodName);
                       try {
                           sheet.addCell(new Label(columnId, rowId, method
                                   .invoke(ssTopModel) == null ? ""
                                   : method.invoke(ssTopModel)
                                           .toString(), wcf_center));
                       } catch (IllegalArgumentException e) {
                           e.printStackTrace();
                       } catch (IllegalAccessException e) {
                           e.printStackTrace();
                       } catch (InvocationTargetException e) {
                           e.printStackTrace();
                       }
                       columnId++;
                   }
               }
               rowId++;
           }

           workbook.write();
           workbook.close();
           os.flush();
           os.close();
       } catch (WriteException e) {
           e.printStackTrace();
       } catch (FileNotFoundException e) {
           e.printStackTrace();
       } catch (IOException e) {
           e.printStackTrace();
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       } catch (SecurityException e) {
           e.printStackTrace();
       } catch (NoSuchMethodException e) {
           e.printStackTrace();
       }
   }
}