Java tutorial
/** * Copyright (c) 2014 http://www.lushapp.wang * * Licensed under the Apache License, Version 2.0 (the "License"); */ package com.lushapp.common.excel; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.commons.lang3.ArrayUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import com.lushapp.common.excel.annotation.Excel; /** * Excel. * @author honey.zhao@aliyun.com * @date 2014-6-11 ?10:57:15 * @version 1.0 */ public class ExportExcel<T> { /** * * @param title Sheet?? * @param pojoClass ExcelClass * @param dataSet Excel?List * @param out ? */ public void exportExcel(String title, Class<T> pojoClass, Collection<T> dataSet, OutputStream out) { // userModel?excel10??OOM exportExcelInUserModel(title, pojoClass, dataSet, out); // eventModel???? } private void exportExcelInUserModel(String title, Class<T> pojoClass, Collection<T> dataSet, OutputStream out) { try { // ?? if (dataSet == null || dataSet.size() == 0) { throw new Exception("??"); } if (title == null || out == null || pojoClass == null) { throw new Exception("???"); } // Workbook workbook = new HSSFWorkbook(); // ? Sheet sheet = workbook.createSheet(title); // List<String> exportFieldTitle = new ArrayList<String>(); List<Integer> exportFieldWidth = new ArrayList<Integer>(); // ???get List<Method> methodObj = new ArrayList<Method>(); Map<String, Method> convertMethod = new HashMap<String, Method>(); // Field fileds[] = pojoClass.getDeclaredFields(); // ??filed for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; Excel excel = field.getAnnotation(Excel.class); // annottion if (excel != null) { // exportFieldTitle.add(excel.exportName()); // exportFieldWidth.add(excel.exportFieldWidth()); // ? String fieldname = field.getName(); // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth()); StringBuffer getMethodName = new StringBuffer("get"); getMethodName.append(fieldname.substring(0, 1).toUpperCase()); getMethodName.append(fieldname.substring(1)); Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {}); methodObj.add(getMethod); if (excel.exportConvert() == true) { StringBuffer getConvertMethodName = new StringBuffer("get"); getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); getConvertMethodName.append(fieldname.substring(1)); getConvertMethodName.append("Convert"); Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {}); convertMethod.put(getMethodName.toString(), getConvertMethod); } } } int index = 0; // Row row = sheet.createRow(index); for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { Cell cell = row.createCell(i); // cell.setCellStyle(style); RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i)); cell.setCellValue(text); } // ? for (int i = 0; i < exportFieldWidth.size(); i++) { // 256=65280/255 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); } Iterator its = dataSet.iterator(); // ?? while (its.hasNext()) { // index++; row = sheet.createRow(index); Object t = its.next(); for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { Cell cell = row.createCell(k); Method getMethod = methodObj.get(k); Object value = null; if (convertMethod.containsKey(getMethod.getName())) { Method cm = convertMethod.get(getMethod.getName()); value = cm.invoke(t, new Object[] {}); } else { value = getMethod.invoke(t, new Object[] {}); } cell.setCellValue(value == null ? "" : value.toString()); } } workbook.write(out); } catch (Exception e) { e.printStackTrace(); } } /** * * @param title Sheet?? * @param pojoClass ExcelClass * @param dataSet Excel?List */ public HSSFWorkbook exportExcel(String title, Class<T> pojoClass, Collection<T> dataSet) { // userModel?excel10??OOM return exportExcelInUserModel2File(title, pojoClass, dataSet); } private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, Collection<T> dataSet) { // HSSFWorkbook workbook = null; try { // workbook = new HSSFWorkbook(); // ? Sheet sheet = workbook.createSheet(title); // List<String> exportFieldTitle = new ArrayList<String>(); List<Integer> exportFieldWidth = new ArrayList<Integer>(); // ???get List<Method> methodObj = new ArrayList<Method>(); Map<String, Method> convertMethod = new HashMap<String, Method>(); Class superClazz = null; Field fileds[] = new Field[0]; boolean flag = true; while (flag) { if (superClazz != null) { superClazz = superClazz.getSuperclass(); } else { superClazz = pojoClass.getSuperclass(); } if (superClazz.isInstance(Object.class)) { flag = false; } else { Field[] sf = superClazz.getDeclaredFields(); if (sf != null && sf.length > 0) { for (int m = 0; m < sf.length; m++) { fileds = ArrayUtils.addAll(fileds, sf[m]); } } } } // Field cfileds[] = pojoClass.getDeclaredFields(); if (cfileds != null && cfileds.length > 0) { for (int n = 0; n < cfileds.length; n++) { fileds = ArrayUtils.addAll(fileds, cfileds[n]); } } // ??filed for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; Excel excel = field.getAnnotation(Excel.class); // annottion if (excel != null) { // exportFieldTitle.add(excel.exportName()); // exportFieldWidth.add(excel.exportFieldWidth()); // ? String fieldname = field.getName(); // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth()); StringBuffer getMethodName = new StringBuffer("get"); getMethodName.append(fieldname.substring(0, 1).toUpperCase()); getMethodName.append(fieldname.substring(1)); Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {}); methodObj.add(getMethod); if (excel.exportConvert() == true) { //---------------------------------------------------------------- //update-begin--Author:Quainty Date:20130524 for[8]excel // get/setXxxxConvert??? ?Entity? StringBuffer getConvertMethodName = new StringBuffer("convertGet"); getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); getConvertMethodName.append(fieldname.substring(1)); //getConvertMethodName.append("Convert"); //update-end--Author:Quainty Date:20130524 for[8]excel //---------------------------------------------------------------- // System.out.println("convert: "+getConvertMethodName.toString()); Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {}); convertMethod.put(getMethodName.toString(), getConvertMethod); } } } int index = 0; // Row row = sheet.createRow(index); row.setHeight((short) 450); CellStyle titleStyle = getTitleStyle(workbook); for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { Cell cell = row.createCell(i); // cell.setCellStyle(style); RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i)); cell.setCellValue(text); cell.setCellStyle(titleStyle); } // ? for (int i = 0; i < exportFieldWidth.size(); i++) { // 256=65280/255 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); } Iterator its = dataSet.iterator(); // ?? while (its.hasNext()) { // index++; row = sheet.createRow(index); row.setHeight((short) 350); Object t = its.next(); for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { Cell cell = row.createCell(k); Method getMethod = methodObj.get(k); Object value = null; if (convertMethod.containsKey(getMethod.getName())) { Method cm = convertMethod.get(getMethod.getName()); value = cm.invoke(t, new Object[] {}); } else { value = getMethod.invoke(t, new Object[] {}); } cell.setCellValue(value == null ? "" : value.toString()); if (index % 2 == 0) cell.setCellStyle(getTwoStyle(workbook)); else cell.setCellStyle(getOneStyle(workbook)); } } } catch (Exception e) { e.printStackTrace(); } return workbook; } /** * excel? * @param workbook * @return */ public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) { // Excel HSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); //? titleStyle.setBorderLeft((short) 2); // titleStyle.setBorderRight((short) 2); //? titleStyle.setBorderTop((short) 2); // titleStyle.setBorderBottom((short) 2); //? titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // return titleStyle; } public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook) { // Excel HSSFCellStyle style = workbook.createCellStyle(); style.setBorderLeft((short) 1); // style.setBorderRight((short) 1); //? style.setBorderBottom((short) 1); style.setBorderTop((short) 1); style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // return style; } public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook) { // Excel // Excel HSSFCellStyle style = workbook.createCellStyle(); style.setBorderLeft((short) 1); // style.setBorderRight((short) 1); //? style.setBorderBottom((short) 1); style.setBorderTop((short) 1); return style; } }