ExportExcel
来源：互联网 发布：淘宝首页怎么建热点 编辑：程序博客网 时间：2024/06/14 22:28
package com.wyj.excel.test;import java.io.FileOutputStream;import java.io.OutputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.GregorianCalendar;import java.util.Iterator;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import com.wyj.annotation.ExcelAnnotation;import com.wyj.bo.InsuranceCosts;import com.wyj.bo.Statistics;import com.wyj.utils.StringUtils;public class ExportExcel<T>{ SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期 /** * @param title * 标题 * @param dataset * 集合 * @param out * 输出流 */ public void exportExcel( String title, Collection<T> dataset, OutputStream out ) { SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期 // 声明一个工作薄 try { // 首先检查数据看是否是正确的 Iterator<T> its = dataset.iterator(); if ( dataset == null || !its.hasNext() || title == null || out == null ) { throw new Exception( "传入的数据不对！" ); } // 取得实际泛型类 T ts = (T)its.next(); Class tCls = ts.getClass(); HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet( title ); // 设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth( 20 ); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置标题样式 style = ExcelStyle.setHeadStyle( workbook, style ); // 得到所有字段 Field filed[] = ts.getClass().getDeclaredFields(); // 标题 List<String> exportfieldtile = new ArrayList<String>(); // 导出的字段的get方法 List<Method> methodObj = new ArrayList<Method>(); // 遍历整个filed for ( int i = 0; i < filed.length; i++ ) { Field f = filed[i]; f.isAnnotationPresent( ExcelAnnotation.class ); ExcelAnnotation excelAnnotation = f .getAnnotation( ExcelAnnotation.class ); // 如果设置了annottion if ( excelAnnotation != null ) { String exprot = excelAnnotation.exportName(); // 添加到标题 exportfieldtile.add( exprot ); // 添加到需要导出的字段的方法 String fieldname = f.getName(); String getMethodName = "get" + fieldname.substring( 0, 1 ).toUpperCase() + fieldname.substring( 1 ); Method getMethod = tCls.getMethod( getMethodName, new Class[] {} ); methodObj.add( getMethod ); } } // 产生表格标题行 HSSFRow row = sheet.createRow( 0 ); for ( int i = 0; i < exportfieldtile.size(); i++ ) { HSSFCell cell = row.createCell( i ); cell.setCellStyle( style ); HSSFRichTextString text = new HSSFRichTextString( exportfieldtile.get( i ) ); cell.setCellValue( text ); } int index = 0; // 循环整个集合 its = dataset.iterator(); while ( its.hasNext() ) { // 从第二行开始写，第一行是标题 index++; row = sheet.createRow( index ); T t = (T)its.next(); for ( int k = 0; k < methodObj.size(); k++ ) { HSSFCell cell = row.createCell( k ); Method getMethod = methodObj.get( k ); Object value = getMethod.invoke( t, new Object[] {} ); cell = (HSSFCell)getValue( value, cell ); } } workbook.write( out ); } catch ( Exception e ) { e.printStackTrace(); } } @SuppressWarnings( {"static-access"} ) private Cell getValue( Object value, Cell cell ) throws ParseException { String textValue = ""; if ( value instanceof Integer ) { int intValue = (Integer)value; cell.setCellValue( intValue ); } else if ( value instanceof Float ) { float fValue = (Float)value; cell.setCellValue( fValue ); } else if ( value instanceof Double ) { double dValue = (Double)value; cell.setCellValue( dValue ); } else if ( value instanceof Long ) { long longValue = (Long)value; cell.setCellValue( longValue ); } else if ( value instanceof Date ) { Date date = (Date)value; SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" ); textValue = sdf.format( date ); cell.setCellValue( textValue ); } else if ( value instanceof String ) { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); cell.setCellValue( textValue ); } else if ( value instanceof Boolean ) { boolean bValue = (Boolean)value; textValue = "是"; if ( !bValue ) { textValue = "否"; } } else if ( value instanceof GregorianCalendar ) { GregorianCalendar calendar = (GregorianCalendar)value; Date d = calendar.getTime(); textValue = sdf.format( d ); } else { textValue = ""; cell.setCellValue( textValue ); } return cell; } @SuppressWarnings( "unchecked" ) public static void main( String[] args ) throws Exception { List list = queryStatistictsList(); // 构造输出对象，可以从response输出，直接向用户提供下载 OutputStream out = null; if ( StringUtils.isNotNullList( list ) ) { if ( list.get( 0 ).getClass().equals( InsuranceCosts.class ) ) { out = new FileOutputStream( "file\\excel\\exportInsuranceCosts.xls" ); } else if ( list.get( 0 ).getClass().equals( Statistics.class ) ) { out = new FileOutputStream( "file\\excel\\exportStatistics.xls" ); } else { out = new FileOutputStream( "file\\excel\\test.xls" ); } // 开始时间 Long l = System.currentTimeMillis(); // 注意 new ExportExcel().exportExcel( "测试", list, out ); out.close(); // 结束时间 Long s = System.currentTimeMillis(); System.out.println( "总共耗时：" + (s - l) ); } } public static List<Statistics> queryStatistictsList() { // 构造一个模拟的List来测试，实际使用时，这个集合是从数据库中查出来的 List<Statistics> list = new ArrayList<Statistics>(); Statistics statistics = new Statistics(); statistics.setName( "手机终端统计" ); statistics.setType( "1" ); statistics.setVendor( "三星" ); statistics.setSubtype( "S201" ); statistics.setSoftver( "安卓4.1.3" ); statistics.setOid( "d100000000" ); statistics.setCardId( "125123456" ); statistics.setStatisticsTime( System.currentTimeMillis() + "" ); statistics.setStatus( "闲置" ); list.add( statistics ); return list; } public static List<InsuranceCosts> queryInsuranceCostsList() { // 构造一个模拟的List来测试，实际使用时，这个集合是从数据库中查出来的 List<InsuranceCosts> list = new ArrayList<InsuranceCosts>(); InsuranceCosts insuranceCosts = new InsuranceCosts(); insuranceCosts.setInsuranceId( "b007" ); insuranceCosts.setInsuranceTypeId( "0" ); insuranceCosts.setVehicleNo( "粤B43F96" ); insuranceCosts.setInsuranceCompany( "深圳保险公司" ); insuranceCosts.setInsuranceType( "车强险" ); insuranceCosts.setContecter( "王经理" ); insuranceCosts.setPhone( "13517474694" ); insuranceCosts.setMoney( 12.0 ); insuranceCosts.setUsetax( 12.0 ); insuranceCosts.setBuyDate( StringUtils.getTextDate( "2010-09-02", "yyyy-MM-dd" ) ); insuranceCosts.setEndDate( StringUtils.getTextDate( "2010-10-01", "yyyy-MM-dd" ) ); list.add( insuranceCosts ); return list; }}
