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(); } } }