使用apache的poi来实现数据导出到excel的功能——方式二

此次，介绍利用poi与layui table结合导出excel。这次不需要从数据库中查询出来的数据进行每一行的拼接那么麻烦，我们这次将标题定义一个id值，对应从数据库中查找出来的字段名即可。

1、pom.xml中引入所需要的依赖

<!-- 处理Excel xlsx -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis.generator</groupId>
      <artifactId>mybatis-generator-core</artifactId>
      <version>1.3.5</version>
    </dependency>

2、准备好工具文件——方法

package com.test.util;

import com.test.entity.common.OutPutModel;
import java.io.OutputStream;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
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.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * 
 * 报表导出公用方法
 */
public class ExportExcel {
    public static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
    //显示的导出表的标题
    private String title;

    private String sheetName;

    private String fileName;
    //导出表的列名
    private String[] rowName ;

    private List<Object[]> dataList = new ArrayList<>();

    public ExportExcel(String title,String[] rowName,List<Object[]>  dataList,String fileName,String sheetName){
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.fileName = fileName;
        this.sheetName = sheetName;
    }
    /*
     * 导出数据
     * */
    public void export(OutputStream out) throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(sheetName);                  // 创建工作表


            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                  //单元格样式对象

      int startIndex = 0;
      if(StringUtils.isNotBlank(title)){
        // 产生表格标题行
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(title);

        startIndex += 2;
      }

            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(startIndex++);                // 在索引2的位置创建行(最顶端的行开始的第二行)

            // 将列头设置到sheet的单元格中
            for(int n=0;n<columnNum;n++){
                HSSFCell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text);                                 //设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式
            }

            if (dataList != null && dataList.size() > 0) {
                //将查询出的数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {

                    Object[] obj = dataList.get(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(i + startIndex);//创建所需的行数

                    for (int j = 0; j < obj.length; j++) {
                        HSSFCell cell;   //设置单元格的数据类型
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());                       //设置单元格的值
                        }
                        //这段代码会导致第一列数据为数字排序
//                    if(j == 0){
//                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
//                        cell.setCellValue(i+1);
//                    }else{
//                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
//                        if(!"".equals(obj[j]) && obj[j] != null){
//                            cell.setCellValue(obj[j].toString());                       //设置单元格的值
//                        }
//                    }
                        cell.setCellStyle(style);                                   //设置单元格样式
                    }
                }
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 150;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    //这段代码会导致列宽过宽或过窄，现使用标准宽度
//                    if (currentRow.getCell(colNum) != null) {
//                        HSSFCell currentCell = currentRow.getCell(colNum);
//                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//                            //如果页面的字段和查询结果的字段名称不匹配，这里会报空指针
//                            //如果字段为null也会空指针
//                                int length = currentCell.getStringCellValue().getBytes().length;
//                            if (columnWidth < length) {
//                                columnWidth = length;
//                            }
//                        }
//                    }
                }
                if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                }else{
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                }
            }
            workbook.write(out);
        }catch(Exception e){
            e.printStackTrace();
        }

    }

    /*
     * 列头单元格样式
     */
    private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    /*
     * 列数据信息单元格样式
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    /**
     * controller中调用的方法
     */
    public static void controllerDownload(OutPutModel outPutModel, HttpServletResponse response, List<Object[]> list){
        //查询结果字段
        List<Map<String,String>> fieldList = outPutModel.getFieldList();
        //excel字段
        String[] rowName = null;
        //获取中文名称
        if(fieldList !=null && fieldList.size()>0) {
            rowName = new String[fieldList.size()];
            int i = 0;
            for (Map<String, String> map : fieldList) {
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    if("colcn".equals(entry.getKey())){
                        Array.set(rowName,i,entry.getValue());
                        i++;
                    }
                }
            }
        }
        String fileName = "";
        if(StringUtils.isNotBlank(outPutModel.getFileName())){
            //excel文件名称,设置编码格式防止乱码
          fileName = StringUtil.changeEncode(outPutModel.getFileName()+".xls","ISO8859-1");
        }else{
            fileName = StringUtil.changeEncode(outPutModel.getTitle()+".xls","ISO8859-1");
        }

        if(StringUtils.isBlank(outPutModel.getSheetName())){
          outPutModel.setSheetName(outPutModel.getTitle());
        }

        String headStr = "attachment; filename="" + fileName+ """;
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", headStr);
        OutputStream out;
        try {
            out = response.getOutputStream();
      ExportExcel ex = new ExportExcel(outPutModel.getTitle(), rowName, list,outPutModel.getFileName(), outPutModel.getSheetName());
            ex.export(out);
        } catch (Exception e) {
            logger.info(outPutModel.getTitle()+"下载出错");
        }
    }


    public static<T> List<Object[]> serviceDownload(OutPutModel outPutModel,List<T> list){

        List<Object[]> listData = new ArrayList<>();
        Object[] objects;
        List<Map<String,String>> fieldList = outPutModel.getFieldList();
        //excel字段
        String[] rowName = null;
        //获取英文名称
        if(fieldList !=null && fieldList.size()>0) {
            rowName = new String[fieldList.size()];
            int i = 0;
            for (Map<String, String> map : fieldList) {
                //遍历map，将英文名获取并添加到数组中
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    if("colen".equals(entry.getKey())){
                        String value = entry.getValue();
                        Array.set(rowName,i,value);
                        i++;
                    }
                }
            }
        }

        if(list !=null && list.size() > 0 && rowName != null){
            for(T t : list){
                //存放结果值
                objects = new Object[rowName.length];
                //获取查询结果对象的字段集合
                Field[] fields1 = t.getClass().getDeclaredFields();
                //获取查询结果对象父类的字段集合
                Field[] fields2 = t.getClass().getSuperclass().getDeclaredFields();
                //合并数组
                Field[] fields = ArrayUtils.addAll(fields1, fields2);
                for (Field field : fields) {
                    //获取属性的名字
                    String name = field.getName();
                    for(int j=0;j<rowName.length;j++){
                        String rowNamei = rowName[j];
                        if (rowNamei.equals(name)) {
                            //将属性的首字符大写，方便构造get，set方法
                            name = name.substring(0, 1).toUpperCase() + name.substring(1);
                            try {
                                //获得get方法
                                Method method = t.getClass().getMethod("get" + name);
                                //通过
                                Array.set(objects,j, method.invoke(t)==null ||"".equals(method.invoke(t))?" ":method.invoke(t));
                            } catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
                                logger.info("获取get方法失败/n"+e.toString());
                            }
                        }
                    }
                }
                listData.add(objects);
            }
        }
        return listData;
    }

    /**
     * servieImpl中调用的下载方法，输入值为Map类型
     * @param outPutModel 导出字段model类
     * @param list<Map>类型的 导出结果
     * @return 处理后的数据
     */
    public static List<Object[]> serviceDownloadByMap(OutPutModel outPutModel,List<Map<String,Object>> list){

        List<Object[]> listData = new ArrayList<>();
        Object[] objects;
        List<Map<String,String>> fieldList = outPutModel.getFieldList();
        //excel字段
        String[] rowName = null;
        //获取英文名称
        if(fieldList !=null && fieldList.size()>0) {
            rowName = new String[fieldList.size()];
            int i = 0;
            for (Map<String, String> map : fieldList) {
                //遍历map，将英文名获取并添加到数组中
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    if("colen".equals(entry.getKey())){
                        String value = entry.getValue();
                        Array.set(rowName,i,value);
                        i++;
                    }
                }
            }
        }

        if(list !=null && list.size() > 0 && rowName != null){
            for(Map<String,Object> map : list){
                //存放结果值
                objects = new Object[rowName.length];

                    for(int j=0;j<rowName.length;j++){
                        String rowNamei = rowName[j];
                        for (Map.Entry<String, Object> entry : map.entrySet()) {
//                            System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
                            if (rowNamei.equals(entry.getKey())) {
                                    Array.set(objects,j, null == entry.getValue()|| "".equals(entry.getValue()) ? " ":entry.getValue() );
                            }
                        }

                    }
                listData.add(objects);
            }
        }
        return listData;
    }
}

准备好工具文件——属性

package com.test.entity.common;

import java.util.List;
import java.util.Map;

/**
 * Created by Test on 
 * 导出model
 */
public class OutPutModel {
    //表头
    private String title;
    //文件名
    private String fileName;
    //工作薄名字
  private String sheetName;
    private String resultList;

    //查询结果
    private List<Map<String,String>> fieldList;
    //查询条件
    private Map<String,Object> queryList;


  public String getSheetName() {
    return sheetName;
  }

  public void setSheetName(String sheetName) {
    this.sheetName = sheetName;
  }

  public String getResultList() {
    return resultList;
  }

  public void setResultList(String resultList) {
    this.resultList = resultList;
  }

  public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public List<Map<String, String>> getFieldList() {
        return fieldList;
    }

    public void setFieldList(List<Map<String, String>> fieldList) {
        this.fieldList = fieldList;
    }

    public Map<String, Object> getQueryList() {
        return queryList;
    }

    public void setQueryList(Map<String, Object> queryList) {
        this.queryList = queryList;
    }
}

其中，属性类中的内容可以自定义的。

3、jsp中使用的是layui，我们可以通过layui table的属性获取其中的标题集合，然后调用导出接口，实现导出。

 //=====================导出操作
                                //1.获取标题头部内容
                                var cols = [];
                                $('.layui-table-header tr th').each(function(i,ths){
                                    if(i>0){
                                        var title = $(this).find('span:first').text();
                                        var filed = ths.getAttribute('data-field');
                                        if(filed != "customerId"){
                                            cols.push({colcn:title, colen:filed});
                                        }

                                    }
                                })
                                //2.组装
                                var data = {
                                    title: '测试导出表格首行标题名',//不配置title的话，导出的excel文件没有文件头，从首行起就是列名
                                    fileName:'测试文件下载文件名',
　　　　　　　　　　　　　　　　　　　　　 sheetName:'导出excel的工作薄名称',
                                    fieldList: cols,
                                    resultList: JSON.stringify(judgeData)
                                };
                                //3.导出
                                postHref("${pageContext.request.contextPath}/testCtrl/outPutOrder",data);
                            },

4、后台接口controller层书写

 /**
   * 导出
   */
  @RequestMapping("/outPutCheckedOrder")
  @ResponseBody
  public String outPutCheckedOrder(String param, HttpServletResponse response){
    //json字符串转为javabean
    OutPutModel outPutModel = JSON.parseObject(param, OutPutModel.class);
    //获得查询结果
    String result = outPutModel.getResultList();
    List<Map<String,Object>> resultList = (List)JSONArray.parseArray(result);
    //利用工具将数据转型执行导出下载
    List<Object[]> list = ExportExcel.serviceDownloadByMap(outPutModel,resultList);
    ExportExcel.controllerDownload(outPutModel,response,list);
    return null;
  }

完毕！

注意：其中judgeData是在layui table中勾选过的数据，不需要在数据库中查询。

那么，我们要是导出excel的标题自定义的怎么办呢？只需要在jsp中定义好标题即可。

var cols = [
                          {colcn:'第一列', colen:'firstCol'}
                      ];

package com.yjl.util;

import com.yjl.entity.common.OutPutModel;
import java.io.OutputStream;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
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.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * Created by YJL on 2017/9/29.
 * 报表导出公用方法
*/
public class ExportExcel {
 public static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
 //显示的导出表的标题
private String title;

 private String sheetName;

 private String fileName;
 //导出表的列名
private String[] rowName ;

 private List<Object[]> dataList = new ArrayList<>();

 //构造方法，传入要导出的数据
public ExportExcel(String title,String[] rowName,List<Object[]>  dataList){
  this.dataList = dataList;
  this.rowName = rowName;
  this.title = title;
 }

 public ExportExcel(String title,String[] rowName,List<Object[]>  dataList,String fileName,String sheetName){
  this.dataList = dataList;
  this.rowName = rowName;
  this.title = title;
  this.fileName = fileName;
  this.sheetName = sheetName;
 }
 /*
  * 导出数据
  * */
public void export(OutputStream out) throws Exception{
  try{
   HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet(sheetName);                  // 创建工作表


//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
HSSFCellStyle style = this.getStyle(workbook);                  //单元格样式对象

int startIndex = 0;
      if(StringUtils.isNotBlank(title)){
        // 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
        cellTiltle.setCellStyle(columnTopStyle);
        cellTiltle.setCellValue(title);

        startIndex += 2;
      }

   // 定义所需列数
int columnNum = rowName.length;
   HSSFRow rowRowName = sheet.createRow(startIndex++);                // 在索引2的位置创建行(最顶端的行开始的第二行)

   // 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
    HSSFCell cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
    cellRowName.setCellValue(text);                                 //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式
}

   if (dataList != null && dataList.size() > 0) {
    //将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {

     Object[] obj = dataList.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i + startIndex);//创建所需的行数

for (int j = 0; j < obj.length; j++) {
      HSSFCell cell;   //设置单元格的数据类型
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
      if (!"".equals(obj[j]) && obj[j] != null) {
       cell.setCellValue(obj[j].toString());                       //设置单元格的值
}
      //这段代码会导致第一列数据为数字排序
//     if(j == 0){
//      cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
//      cell.setCellValue(i+1);
//     }else{
//      cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
//      if(!"".equals(obj[j]) && obj[j] != null){
//       cell.setCellValue(obj[j].toString());                       //设置单元格的值
//      }
//     }
cell.setCellStyle(style);                                   //设置单元格样式
}
    }
   }
   //让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
    int columnWidth = sheet.getColumnWidth(colNum) / 150;
    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
     HSSFRow currentRow;
     //当前行未被使用过
if (sheet.getRow(rowNum) == null) {
      currentRow = sheet.createRow(rowNum);
     } else {
      currentRow = sheet.getRow(rowNum);
     }
     //这段代码会导致列宽过宽或过窄，现使用标准宽度
//     if (currentRow.getCell(colNum) != null) {
//      HSSFCell currentCell = currentRow.getCell(colNum);
//      if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//       //如果页面的字段和查询结果的字段名称不匹配，这里会报空指针
//       //如果字段为null也会空指针
//        int length = currentCell.getStringCellValue().getBytes().length;
//       if (columnWidth < length) {
//        columnWidth = length;
//       }
//      }
//     }
}
    if(colNum == 0){
     sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
    }else{
     sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
    }
   }
   workbook.write(out);
  }catch(Exception e){
   e.printStackTrace();
  }

 }

 /*
  * 列头单元格样式
  */
private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

  // 设置字体
HSSFFont font = workbook.createFont();
  //设置字体大小
font.setFontHeightInPoints((short)11);
  //字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  //设置字体名字
font.setFontName("Courier New");
  //设置样式;
HSSFCellStyle style = workbook.createCellStyle();
  //设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  //设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
  //设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  //设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
  //设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
  //设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
  //设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
  //设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
  //在样式用应用设置的字体;
style.setFont(font);
  //设置自动换行;
style.setWrapText(false);
  //设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  //设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

  return style;

 }

 /*
  * 列数据信息单元格样式
  */
private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
  // 设置字体
HSSFFont font = workbook.createFont();
  //设置字体大小
//font.setFontHeightInPoints((short)10);
  //字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  //设置字体名字
font.setFontName("Courier New");
  //设置样式;
HSSFCellStyle style = workbook.createCellStyle();
  //设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  //设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
  //设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  //设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
  //设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
  //设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
  //设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
  //设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
  //在样式用应用设置的字体;
style.setFont(font);
  //设置自动换行;
style.setWrapText(false);
  //设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  //设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

  return style;

 }

 /**
  * controller中调用的方法
*/
public static void controllerDownload(OutPutModel outPutModel, HttpServletResponse response, List<Object[]> list){
  //查询结果字段
List<Map<String,String>> fieldList = outPutModel.getFieldList();
  //excel字段
String[] rowName = null;
  //获取中文名称
if(fieldList !=null && fieldList.size()>0) {
   rowName = new String[fieldList.size()];
   int i = 0;
   for (Map<String, String> map : fieldList) {
    for (Map.Entry<String, String> entry : map.entrySet()) {
     if("colcn".equals(entry.getKey())){
      Array.set(rowName,i,entry.getValue());
      i++;
     }
    }
   }
  }
        String fileName = "";
        if(StringUtils.isNotBlank(outPutModel.getFileName())){
            //excel文件名称,设置编码格式防止乱码
fileName = StringUtil.changeEncode(outPutModel.getFileName()+".xls","ISO8859-1");
        }else{
            fileName = StringUtil.changeEncode(outPutModel.getTitle()+".xls","ISO8859-1");
        }

        if(StringUtils.isBlank(outPutModel.getSheetName())){
          outPutModel.setSheetName(outPutModel.getTitle());
        }

  String headStr = "attachment; filename="" + fileName+ """;
  response.setContentType("application/vnd.ms-excel");
  response.setHeader("Content-Disposition", headStr);
  OutputStream out;
  try {
   out = response.getOutputStream();
      ExportExcel ex = new ExportExcel(outPutModel.getTitle(), rowName, list,outPutModel.getFileName(), outPutModel.getSheetName());
   ex.export(out);
  } catch (Exception e) {
   logger.info(outPutModel.getTitle()+"下载出错");
  }
 }


 public static<T> List<Object[]> serviceDownload(OutPutModel outPutModel,List<T> list){

  List<Object[]> listData = new ArrayList<>();
  Object[] objects;
  List<Map<String,String>> fieldList = outPutModel.getFieldList();
  //excel字段
String[] rowName = null;
  //获取英文名称
if(fieldList !=null && fieldList.size()>0) {
   rowName = new String[fieldList.size()];
   int i = 0;
   for (Map<String, String> map : fieldList) {
    //遍历map，将英文名获取并添加到数组中
for (Map.Entry<String, String> entry : map.entrySet()) {
     if("colen".equals(entry.getKey())){
      String value = entry.getValue();
      Array.set(rowName,i,value);
      i++;
     }
    }
   }
  }

  if(list !=null && list.size() > 0 && rowName != null){
   for(T t : list){
    //存放结果值
objects = new Object[rowName.length];
    //获取查询结果对象的字段集合
Field[] fields1 = t.getClass().getDeclaredFields();
    //获取查询结果对象父类的字段集合
Field[] fields2 = t.getClass().getSuperclass().getDeclaredFields();
    //合并数组
Field[] fields = ArrayUtils.addAll(fields1, fields2);
    for (Field field : fields) {
     //获取属性的名字
String name = field.getName();
     for(int j=0;j<rowName.length;j++){
      String rowNamei = rowName[j];
      if (rowNamei.equals(name)) {
       //将属性的首字符大写，方便构造get，set方法
name = name.substring(0, 1).toUpperCase() + name.substring(1);
       try {
        //获得get方法
Method method = t.getClass().getMethod("get" + name);
        //通过
Array.set(objects,j, method.invoke(t)==null ||"".equals(method.invoke(t))?" ":method.invoke(t));
       } catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
        logger.info("获取get方法失败/n"+e.toString());
       }
      }
     }
    }
    listData.add(objects);
   }
  }
  return listData;
 }

 /**
  * servieImpl中调用的下载方法，输入值为Map类型
* @param outPutModel 导出字段model类
* @param list<Map>类型的 导出结果
* @return 处理后的数据
*/
public static List<Object[]> serviceDownloadByMap(OutPutModel outPutModel,List<Map<String,Object>> list){

  List<Object[]> listData = new ArrayList<>();
  Object[] objects;
  List<Map<String,String>> fieldList = outPutModel.getFieldList();
  //excel字段
String[] rowName = null;
  //获取英文名称
if(fieldList !=null && fieldList.size()>0) {
   rowName = new String[fieldList.size()];
   int i = 0;
   for (Map<String, String> map : fieldList) {
    //遍历map，将英文名获取并添加到数组中
for (Map.Entry<String, String> entry : map.entrySet()) {
     if("colen".equals(entry.getKey())){
      String value = entry.getValue();
      Array.set(rowName,i,value);
      i++;
     }
    }
   }
  }

  if(list !=null && list.size() > 0 && rowName != null){
   for(Map<String,Object> map : list){
    //存放结果值
objects = new Object[rowName.length];

     for(int j=0;j<rowName.length;j++){
      String rowNamei = rowName[j];
      for (Map.Entry<String, Object> entry : map.entrySet()) {
//       System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
if (rowNamei.equals(entry.getKey())) {
         Array.set(objects,j, null == entry.getValue()|| "".equals(entry.getValue()) ? " ":entry.getValue() );
       }
      }

     }
    listData.add(objects);
   }
  }
  return listData;
 }
}