package cn.knet.data.untils; import java.awt.Color; import java.io.File; 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.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExportExcelUtil2<T> { public void exportExcel(Collection<T> dataset,String[] headers,String path,String excelName,String sheetName,String title) throws IOException { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet=workbook.createSheet(); // 设置表格默认列宽度为15个字节 //要是提供的sheetName就给第一个表格设置名字 if(StringUtils.isNotBlank(sheetName)) { workbook.setSheetName(0,sheetName); } //设置行的默认值 int row_num=0; if(StringUtils.isNotBlank(title)){ XSSFRow rowtitle = sheet.createRow(row_num); rowtitle.setHeightInPoints(23); XSSFCell cellHead = rowtitle.createCell(0); cellHead.setCellValue(title); //合并标题的单元格 sheet.addMergedRegion(new CellRangeAddress(row_num,row_num,0,headers.length-1));//startRow,endRow,startColumn,endColumn //设置总标题样式============================== //设置字体 XSSFFont font_title = workbook.createFont(); font_title.setColor(new XSSFColor(Color.black).getIndexed()); font_title.setFontHeightInPoints((short) 16); font_title.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); //设置title样式 XSSFCellStyle style_title = workbook.createCellStyle(); style_title.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_title.setBorderBottom(XSSFCellStyle.BORDER_THIN); style_title.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_title.setBorderRight(XSSFCellStyle.BORDER_THIN); style_title.setBorderTop(XSSFCellStyle.BORDER_THIN); style_title.setFont(font_title); //设置cell样式 cellHead.setCellStyle(style_title); //解决合并单元格后加边框问题 for(int i=1;i<headers.length;i++){ cellHead = rowtitle.createCell(i); cellHead.setCellValue(""); cellHead.setCellStyle(style_title); } //行数加1行 row_num=row_num+1; } //设置head样式============================================ // 生成一个字体 XSSFFont font_head = workbook.createFont(); font_head.setColor(new XSSFColor(Color.BLACK).getIndexed()); font_head.setFontHeightInPoints((short) 12); font_head.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 生成head样式 XSSFCellStyle style_head = workbook.createCellStyle(); // 把字体应用到当前的样式 style_head.setFont(font_head); // 设置这些样式 // style_head.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //style_head.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_head.setBorderBottom(XSSFCellStyle.BORDER_THIN); style_head.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_head.setBorderRight(XSSFCellStyle.BORDER_THIN); style_head.setBorderTop(XSSFCellStyle.BORDER_THIN); style_head.setAlignment(XSSFCellStyle.ALIGN_CENTER); //设置head样式======End================================== //设置body样式================================================== // 生成另一个字体 XSSFFont font_body = workbook.createFont(); font_body.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); //生成body样式 XSSFCellStyle style_body = workbook.createCellStyle(); // 把字体应用到当前的样式 style_body.setFont(font_body); // style_body.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); //style_body.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_body.setBorderBottom(XSSFCellStyle.BORDER_THIN); style_body.setBorderLeft(XSSFCellStyle.BORDER_THIN); style_body.setBorderRight(XSSFCellStyle.BORDER_THIN); style_body.setBorderTop(XSSFCellStyle.BORDER_THIN); style_body.setAlignment(XSSFCellStyle.ALIGN_CENTER); style_body.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 添加表格标题行 XSSFRow row_head = sheet.createRow(row_num); row_head.setHeightInPoints(25); for (int i = 0; i < headers.length; i++) { XSSFCell cell = row_head.createCell(i); cell.setCellStyle(style_head); XSSFRichTextString text = new XSSFRichTextString(headers[i]); cell.setCellValue(text); } //添加标题行后行号加1 row_num=row_num+1; //添加body // 遍历集合数据，产生数据行 //Field[] fields = types.getDeclaredFields(); Iterator<T> it = dataset.iterator(); while (it.hasNext()) { System.err.println(row_num); XSSFRow row_body = sheet.createRow(row_num); row_body.setHeightInPoints(20); T t = (T) it.next(); // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { //设置列宽度自适应 sheet.autoSizeColumn(i,true); XSSFCell cell = row_body.createCell(i); cell.setCellStyle(style_body); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1); try { Class<? extends Object> tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName,new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Integer) { int intValue = (Integer) value; cell.setCellValue(intValue); } else if (value instanceof Float) { float fValue = (Float) value; textValue = String.valueOf(fValue); cell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; textValue = String.valueOf(dValue); cell.setCellValue(textValue); } 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("yyy-MM-dd HH:mm:ss"); textValue = sdf.format(date); }else { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); } if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?{1}"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { XSSFRichTextString richString = new XSSFRichTextString(textValue); XSSFFont font3 = workbook.createFont(); font3.setColor(new XSSFColor(Color.BLACK).getIndexed()); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } row_num=row_num+1; } OutputStream os = new FileOutputStream(new File(path+"/"+excelName+".xlsx")); try{ workbook.write(os); } catch(Exception ex){ ex.printStackTrace(); } finally{ os.flush(); os.close(); } } public void exportExcelFast(Collection<T> dataset,String[] headers,Class<T> t,String path,String excelName,String sheetName) throws IOException { FileOutputStream output = new FileOutputStream(new File(path+"/"+excelName+".xlsx")); //读取的文件路径 // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet=workbook.createSheet(sheetName); XSSFRow row_head = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { XSSFCell cell = row_head.createCell(i); XSSFRichTextString text = new XSSFRichTextString(headers[i]); cell.setCellValue(text); } Field[] fields = t.getDeclaredFields(); int row_num=1; for (T tt : dataset) { XSSFRow row_body = sheet.createRow(row_num); for(int i=0;i<fields.length;i++){ XSSFCell cell = row_body.createCell(i); Object v; try { v = BeanUtils.getProperty(tt, fields[i].getName()); String value =v==null?"":v.toString(); cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式 cell.setCellValue(value);//写入内容 } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (InvocationTargetException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (NoSuchMethodException e) { // TODO Auto-generated catch block e.printStackTrace(); }//.getBeanProperty(tt, fields[i].getName()); } row_num++; } workbook.write(output); output.flush(); output.close(); } public static void main(String[] args){ /* List<Flow> list = new ArrayList<Flow>(); for(int i=0;i<50000;i++) { Flow f = new Flow(); f.setCurkey("枯萎奇才霏霏夺标需要奇怪棒棒酩酊大醉大模大样大模大样"+i); f.setFlow(i); list.add(f); } ExportExcelUtil2<Flow> ex = new ExportExcelUtil2<Flow>(); String[] headers= {"KEY","流水"}; try { //ex.exportExcelFast(list, headers,Flow.class, "D:\", "MYExcel", "MySheet"); ex.exportExcel(list, headers, "D:\", "MYExcel", "MySheet",""); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }*/ } }