使用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;
}
}
