excel工具类

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

             

    }

}