package cn.com.qmhd.iicp.crm.util;import java.io.OutputStream;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.Collection;import java.util.Date;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;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.hssf.util.HSSFColor;import org.apache.poi.ss.formula.functions.T;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Drawing;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFDrawing;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import cn.com.qmhd.iicp.crm.modal.Callrecords;import cn.com.qmhd.iicp.crm.modal.TelephoneBook;import cn.com.qmhd.iicp.crm.result.CallResult;/** * * @author 李东坡 * @version 1.0 * @时间 2016年4月22日 * @描述 */public class ExcelExportUtil<T> { /** * 导出excel工具类 * @title exportExcel * @author 李东坡 2016年4月22日 * @param title sheet名称 * @param headers 表头 * @param columns 列 * @param result 数据集 * @param out * @throws Exception * @description */ public void exportExcel(String title, String[] headers, String[] columns, List<Map<String, String>> result, OutputStream out) throws Exception { // 声明一个工作薄 @SuppressWarnings("resource") HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth(25); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.GOLD.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); // font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 指定当单元格内容显示不下时自动换行 style.setWrapText(true); sheet.createDrawingPatriarch(); // 产生表格标题行 // 表头的样式 HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式对象 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中 titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 // 设置字体 HSSFFont titleFont = workbook.createFont(); // 创建字体对象 titleFont.setFontHeightInPoints((short) 15); // 设置字体大小 titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体 // titleFont.setFontName("黑体"); // 设置为黑体字 titleStyle.setFont(titleFont); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (headers.length - 1)));// 指定合并区域 HSSFRow rowHeader = sheet.createRow(0); HSSFCell cellHeader = rowHeader.createCell(0); // 只能往第一格子写数据，然后应用样式，就可以水平垂直居中 HSSFRichTextString textHeader = new HSSFRichTextString(title); cellHeader.setCellStyle(titleStyle); cellHeader.setCellValue(textHeader); HSSFRow row = sheet.createRow(1); for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据，产生数据行 if (result != null) { int index = 2; for (Map<String, String> m : result) { row = sheet.createRow(index); index++; for (int i = 0; i < columns.length; i++) { Cell cell = row.createCell(i, Cell.CELL_TYPE_BLANK); String text = m.get(columns[i]); HSSFRichTextString richString = new HSSFRichTextString(text); cell.setCellValue(richString); } } } workbook.write(out); }// @Deprecated// public void expoortExcelx(String title, String[] headers, String[] columns,// Collection<T> result, OutputStream out, String pattern) throws NoSuchMethodException, Exception{// //创建工作薄// XSSFWorkbook workbook=new XSSFWorkbook();// //创建表格// Sheet sheet=workbook.createSheet("title");// //设置默认宽度// sheet.setDefaultColumnWidth(25);// //创建样式// XSSFCellStyle style=workbook.createCellStyle();// //设置样式// style.setFillForegroundColor(IndexedColors.GOLD.index);// style.setFillPattern(CellStyle.SOLID_FOREGROUND);// style.setBorderBottom(CellStyle.BORDER_THIN);// style.setBorderLeft(CellStyle.BORDER_THIN);// style.setBorderRight(CellStyle.BORDER_THIN);// style.setBorderTop(CellStyle.BORDER_THIN);// //生成字体// XSSFFont font=workbook.createFont();// font.setColor(IndexedColors.VIOLET.index);// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// //应用字体// style.setFont(font);// // //自动换行// style.setWrapText(true);// //声明一个画图的顶级管理器// Drawing drawing=(XSSFDrawing) sheet.createDrawingPatriarch();// //表头的样式// XSSFCellStyle titleStyle=workbook.createCellStyle();//样式对象// titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);//水平居中// titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// //设置字体// XSSFFont titleFont=workbook.createFont();// titleFont.setFontHeightInPoints((short)15);// titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体// titleStyle.setFont(titleFont);// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length-1));// //指定合并区域// Row rowHeader = sheet.createRow(0);// //XSSFRow rowHeader=sheet.createRow(0);// Cell cellHeader=rowHeader.createCell(0);// XSSFRichTextString textHeader=new XSSFRichTextString(title);// cellHeader.setCellStyle(titleStyle);// cellHeader.setCellValue(textHeader);// // Row row=sheet.createRow(1);// for(int i=0;i<headers.length;i++){// Cell cell=row.createCell(i);// cell.setCellStyle(style);// XSSFRichTextString text=new XSSFRichTextString(headers[i]);// cell.setCellValue(text);// }// //遍历集合数据，产生数据行// if(result!=null&&result.size()>0){// int index=2;// for(T t:result){// row=sheet.createRow(index);// index++;// for(short i=0;i<columns.length;i++){// Cell cell=row.createCell(i);// String filedName=columns[i];// String getMethodName="get"+filedName.substring(0,1).toUpperCase()// +filedName.substring(1);// Class tCls=t.getClass();// Method getMethod=tCls.getMethod(getMethodName,new Class[]{});// Object value=getMethod.invoke(t, new Class[]{});// String textValue=null;// if(value==null){// textValue="";// }else if(value instanceof Date){// Date date=(Date)value;// SimpleDateFormat sdf = new SimpleDateFormat(pattern);// textValue = sdf.format(date);// }else if(value instanceof byte[]){// row.setHeightInPoints(80);// sheet.setColumnWidth(i, 35*100);// byte[] bsValue=(byte[])value;// XSSFClientAnchor anchor=new XSSFClientAnchor(0,0,1023,255,6,index,6,index);// anchor.setAnchorType(2);// drawing.createPicture(anchor, workbook.addPicture(bsValue, XSSFWorkbook.PICTURE_TYPE_JPEG));// }else{// // 其它数据类型都当作字符串简单处理// textValue=value.toString();// }// // if(textValue!=null){// Pattern p = Pattern.compile("^//d+(//.//d+)?$");// Matcher matcher = p.matcher(textValue);// if (matcher.matches()) {// // 是数字当作double处理// cell.setCellValue(Double.parseDouble(textValue));// } else {// XSSFRichTextString richString = new XSSFRichTextString(// textValue);// // HSSFFont font3 = workbook.createFont();// // font3.setColor(HSSFColor.BLUE.index);// // richString.applyFont(font3);// cell.setCellValue(richString);// }// }// // }// }// }// workbook.write(out); // } /** * 导出excel工具类 * @title expoortExcelx * @param title sheet名称 * @param headers 表头 * @param columns 列 * @param list 数据 * @param out * @param pattern * @throws NoSuchMethodException * @throws Exception * @description */ public void expoortExcelx(String title, String[] headers, String[] columns, List<T> list, OutputStream out, String pattern) throws NoSuchMethodException, Exception{ //创建工作薄 XSSFWorkbook workbook=new XSSFWorkbook(); //创建表格 Sheet sheet=workbook.createSheet("title"); //设置默认宽度 sheet.setDefaultColumnWidth(25); //创建样式 XSSFCellStyle style=workbook.createCellStyle(); //设置样式 style.setFillForegroundColor(IndexedColors.GOLD.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); //生成字体 XSSFFont font=workbook.createFont(); font.setColor(IndexedColors.VIOLET.index); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); //应用字体 style.setFont(font); //自动换行 style.setWrapText(true); //声明一个画图的顶级管理器 Drawing drawing=(XSSFDrawing) sheet.createDrawingPatriarch(); //表头的样式 XSSFCellStyle titleStyle=workbook.createCellStyle();//样式对象 titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);//水平居中 titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //设置字体 XSSFFont titleFont=workbook.createFont(); titleFont.setFontHeightInPoints((short)15); titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体 titleStyle.setFont(titleFont); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length-1)); //指定合并区域 Row rowHeader = sheet.createRow(0); //XSSFRow rowHeader=sheet.createRow(0); Cell cellHeader=rowHeader.createCell(0); XSSFRichTextString textHeader=new XSSFRichTextString(title); cellHeader.setCellStyle(titleStyle); cellHeader.setCellValue(textHeader); Row row=sheet.createRow(1); for(int i=0;i<headers.length;i++){ Cell cell=row.createCell(i); cell.setCellStyle(style); XSSFRichTextString text=new XSSFRichTextString(headers[i]); cell.setCellValue(text); } //遍历集合数据，产生数据行 if(list!=null&&list.size()>0){ int index=2; for(T t:list){ row=sheet.createRow(index); index++; for(short i=0;i<columns.length;i++){ Cell cell=row.createCell(i); String filedName=columns[i]; String getMethodName="get"+filedName.substring(0,1).toUpperCase() +filedName.substring(1); Class tCls=t.getClass(); Method getMethod=tCls.getMethod(getMethodName,new Class[]{}); Object value=getMethod.invoke(t, new Class[]{}); String textValue=null; if(value==null){ textValue=""; }else if(value instanceof Date){ Date date=(Date)value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date); }else if(value instanceof byte[]){ row.setHeightInPoints(80); sheet.setColumnWidth(i, 35*100); byte[] bsValue=(byte[])value; XSSFClientAnchor anchor=new XSSFClientAnchor(0,0,1023,255,6,index,6,index); anchor.setAnchorType(2); drawing.createPicture(anchor, workbook.addPicture(bsValue, XSSFWorkbook.PICTURE_TYPE_JPEG)); }else{ // 其它数据类型都当作字符串简单处理 textValue=value.toString(); } if(textValue!=null){ Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { XSSFRichTextString richString = new XSSFRichTextString( textValue); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLUE.index); // richString.applyFont(font3); cell.setCellValue(richString); } } } } } workbook.write(out); } public void expoortExcelx1(String title, String[] headers, String[] columns, List<CallResult> list, OutputStream out, String pattern) throws NoSuchMethodException, Exception{ //创建工作薄 XSSFWorkbook workbook=new XSSFWorkbook(); //创建表格 Sheet sheet=workbook.createSheet("title"); //设置默认宽度 sheet.setDefaultColumnWidth(25); //创建样式 XSSFCellStyle style=workbook.createCellStyle(); //设置样式 style.setFillForegroundColor(IndexedColors.GOLD.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); //生成字体 XSSFFont font=workbook.createFont(); font.setColor(IndexedColors.VIOLET.index); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); //应用字体 style.setFont(font); //自动换行 style.setWrapText(true); //声明一个画图的顶级管理器 Drawing drawing=(XSSFDrawing) sheet.createDrawingPatriarch(); //表头的样式 XSSFCellStyle titleStyle=workbook.createCellStyle();//样式对象 titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);//水平居中 titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //设置字体 XSSFFont titleFont=workbook.createFont(); titleFont.setFontHeightInPoints((short)15); titleFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体 titleStyle.setFont(titleFont); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length-1)); //指定合并区域 Row rowHeader = sheet.createRow(0); //XSSFRow rowHeader=sheet.createRow(0); Cell cellHeader=rowHeader.createCell(0); XSSFRichTextString textHeader=new XSSFRichTextString(title); cellHeader.setCellStyle(titleStyle); cellHeader.setCellValue(textHeader); Row row=sheet.createRow(1); for(int i=0;i<headers.length;i++){ Cell cell=row.createCell(i); cell.setCellStyle(style); XSSFRichTextString text=new XSSFRichTextString(headers[i]); cell.setCellValue(text); } //遍历集合数据，产生数据行 if(list!=null&&list.size()>0){ int index=2; for(CallResult t:list){ row=sheet.createRow(index); index++; for(short i=0;i<columns.length;i++){ Cell cell=row.createCell(i); String filedName=columns[i]; String getMethodName="get"+filedName.substring(0,1).toUpperCase() +filedName.substring(1); Class tCls=t.getClass(); Method getMethod=tCls.getMethod(getMethodName,new Class[]{}); Object value=getMethod.invoke(t, new Class[]{}); String textValue=null; if(value==null){ textValue=""; }else if(value instanceof Date){ Date date=(Date)value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date); }else if(value instanceof byte[]){ row.setHeightInPoints(80); sheet.setColumnWidth(i, 35*100); byte[] bsValue=(byte[])value; XSSFClientAnchor anchor=new XSSFClientAnchor(0,0,1023,255,6,index,6,index); anchor.setAnchorType(2); drawing.createPicture(anchor, workbook.addPicture(bsValue, XSSFWorkbook.PICTURE_TYPE_JPEG)); }else{ // 其它数据类型都当作字符串简单处理 textValue=value.toString(); } if(textValue!=null){ Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { XSSFRichTextString richString = new XSSFRichTextString( textValue); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLUE.index); // richString.applyFont(font3); cell.setCellValue(richString); } } } } } workbook.write(out); }}