Java Poi导出报表

//表格头部demo类 begin

package com.zgxcw.export.util;

public class ExcelHead {
/*
* 对应导出字段
* */
private String code;
/*
* 对应导出表头显示的中文
* */
private String name;


/*
* 需要转换显示
* 说明如下：
* 1.日期转换：LongToDateString
* 2.code转换汉字：
* 例如 "1:待结算:2:已结算:3:结算失败;" 这样存储会到导出类里面进行解析
* 3.不需要转换就不赋值。
*/
private String transfer;

public String getCode() {
return code;
}

public void setCode(String code) {
this.code = code;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getTransfer() {
return transfer;
}

public void setTransfer(String transfer) {
this.transfer = transfer;
}

public ExcelHead(String code, String name, String transfer) {
super();
this.code = code;
this.name = name;
this.transfer = transfer;
}

public ExcelHead(String code, String name) {
super();
this.code = code;
this.name = name;
}

}

//表格头部demo类 end  

 

//exportExcel工具类 begin

package com.zgxcw.export.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportExcel<T> {

private static final int SHEET_SIZE = 60000;// excel中一个sheet的最大容量

/**
* 不用弹窗导出的请使用这个方法
* @param headMap
* 表头对应关系map
* @param dataset
* 待导出数据
* @param out
* 输出流
* @param sheetName
* 页签名称
*/
public Workbook exportExcel(List<ExcelHead> head, Collection<T> dataset,
OutputStream out, String sheetName, HttpServletResponse response) {
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader("Content-disposition", "attachment;filename="
+ new String(sheetName.getBytes("gb2312"), "iso8859-1")
+ ".xlsx");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}

createWorkbookHead(head, workbook, sheet);
createWorkbookBody(dataset, workbook, sheet, head);

// try {
// workbook.write(out);
// } catch (IOException e) {
// e.printStackTrace();
// }

return workbook;
}

/**
* 最新版本的导出使用，老版本不带弹出导出的请勿用
*
* @param headMap
* 表头对应关系map
* @param dataset
* 待导出数据
* @param out
* 输出流
* @param sheetName
* 页签名称
*/
public void exportExcel4Record(List<ExcelHead> head, Collection<T> dataset,
String sheetName, HttpServletRequest request,
HttpServletResponse response) {
String fileServerPath = (String) request.getAttribute("fileServerPath");
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(fileServerPath);
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader(
"Content-disposition",
"attachment;filename="
+ new String(sheetName.getBytes("gb2312"),
"iso8859-1") + ".xlsx");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
createWorkbookHead(head, workbook, sheet);
createWorkbookBody(dataset, workbook, sheet, head);

workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
if(fileOut != null){
try {
fileOut.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}
}

/**
* @param headMap
* 表头对应关系map
* @param dataset
* 待导出数据
* @param out
* 输出流
* @param sheetName
* 页签名称
*/
public Workbook exportExcel4BigDataSize(List<ExcelHead> head,
List<T> dataList, OutputStream out, String sheetName,
HttpServletResponse response) {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-disposition", "attachment;filename="
+ new String(sheetName.getBytes("gb2312"), "iso8859-1")
+ ".xlsx");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}

// 声明一个工作薄
Workbook workbook = new SXSSFWorkbook(2000);
int dataSize = dataList.size();
int queryTimes = dataSize % SHEET_SIZE == 0 ? dataSize / SHEET_SIZE
: (dataSize / SHEET_SIZE) + 1;
for (int i = 1; i <= queryTimes; i++) {
int start = (i - 1) * SHEET_SIZE;
int end = i * SHEET_SIZE;
if (i == queryTimes) {
end = dataSize;
}
Sheet sheet = workbook.createSheet();
createWorkbookHead(head, workbook, sheet);
createWorkbookBody(dataList.subList(start, end), workbook, sheet,
head);
}
return workbook;
}

/**
* 创建头部信息
*/
public void createWorkbookHead(List<ExcelHead> headList, Workbook workbook,
Sheet sheet) {
Row row = sheet.createRow(0);
// 生成一个样式
CellStyle headerStyle = workbook.createCellStyle();
// 设置这些样式
headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
// 居中
headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
Cell HeaderCell = null;
int i = 0;
for (ExcelHead head : headList) {
sheet.setColumnWidth(i, 8500);
HeaderCell = row.createCell((i));
HeaderCell.setCellStyle(headerStyle);
HeaderCell.setCellType(XSSFCell.CELL_TYPE_STRING);
HeaderCell.setCellValue(head.getName());
i++;
}
}

/**
* 查询并写入记录.
*/
public void createWorkbookBody(Collection<T> returnDataList,
Workbook workbook, Sheet sheet, List<ExcelHead> headCellNameList) {
// 写记录
// 生成一个样式
CellStyle bodyStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
// 设置这些样式
bodyStyle.setFillForegroundColor(HSSFColor.WHITE.index);
bodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
bodyStyle.setWrapText(true);// 设置自动换行
// 这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧
DataFormat format = workbook.createDataFormat();
bodyStyle.setDataFormat(format.getFormat("@"));

// 设置日期格式
CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setFillForegroundColor(HSSFColor.WHITE.index);
dateStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
dateStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(
"yyyy/mm/dd HH:mm:ss"));

Row row = null;

Cell cell = null;

Iterator<T> it = returnDataList.iterator();
Map<String, Object> returnDataMap = new LinkedHashMap<String, Object>();
int rownum = 0; // 行

while (it.hasNext()) {
rownum++;
returnDataMap.clear();

T t = (T) it.next();
Field[] fields = t.getClass().getDeclaredFields();
// 一般获取到父类的属性就可以了
Field[] superFields = t.getClass().getSuperclass()
.getDeclaredFields();
List<Field> fieldList = Arrays.asList(fields);
List<Field> superFieldList = Arrays.asList(superFields);

List<Field> allFieldList = new ArrayList<Field>();
allFieldList.addAll(fieldList);
allFieldList.addAll(superFieldList);
fields = (Field[]) allFieldList.toArray(new Field[allFieldList
.size()]);

for (short i = 0; i < fields.length; i++) {
// cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
try {
field.setAccessible(true);
Object tValue = field.get(t);
returnDataMap.put(fieldName, tValue);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
row = sheet.createRow(rownum);
// Set<String> set = headCellNameMap.keySet();
int j = 0;
for (ExcelHead headCell : headCellNameList) {
cell = row.createCell((j));
cell.setCellStyle(bodyStyle);

// 列头编号.
String headCode = headCell.getCode();
if (StringUtils.isNotBlank(headCode)) {// 列头编号存在,处理此列.
Object objectValue = returnDataMap.get(headCell.getCode());
if (objectValue != null) {// 属性值存在.
String transfer = headCell.getTransfer();
if (StringUtils.isNotBlank(transfer)) {// 处理值转化器存在的情况.
if ("LongToDateString".equals(transfer)) {// 转换类型是日期
Long longValue = (Long) objectValue;
String dateStringValue = this
.LongToDateString(longValue.toString());
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(bodyStyle);
cell.setCellValue(dateStringValue);
} else if ("StringToDouble".equals(transfer)) {// 转换字符串类型为Double类型.
String stringValue = (String) objectValue;
if (stringValue.matches("\\d+\\.?\\d*")) {// 可转换的数字类型.
Double doubleValue = Double
.parseDouble(stringValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(doubleValue);
}
} else {// 非日期类型
String stringValue = objectValue.toString();
String value = this.decode(transfer).get(
stringValue);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(bodyStyle);
cell.setCellValue(value);
}
} else if (objectValue instanceof BigDecimal) {// BigDecimal类型.
BigDecimal b = (BigDecimal) (objectValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(b.doubleValue());
} else if (objectValue instanceof Double) {// Double类型.
Double b = (Double) (objectValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(b);
} else if (objectValue instanceof Integer) {// Integer类型.
Integer b = (Integer) (objectValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(b.doubleValue());
} else if (objectValue instanceof Long) {// Long类型.
Long b = (Long) (objectValue);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(b.doubleValue());
} else if (objectValue instanceof Date) {// Date类型.
Date dateValue = (Date) objectValue;
cell.setCellStyle(dateStyle);
cell.setCellValue(dateValue);
} else if (objectValue instanceof Float) {// Float类型.
Float value = (Float) objectValue;
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(value);
} else {
String value = objectValue.toString();
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(bodyStyle);
cell.setCellValue(value);
}
} else {// 属性值为null.
}
}

if (rownum == returnDataList.size()) {
// sheet.autoSizeColumn(j);
}
j++;
}
}
}

private String LongToDateString(String date) {
if (date == null || date.equals("") || date.equals("0"))
return "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
java.util.Date dt = new Date(Long.parseLong(date));
String sDateTime = sdf.format(dt);
return sDateTime;
}

private Map<String, String> decode(String codeAndValue) {
if (codeAndValue == null || codeAndValue.equals("")) {
return new HashMap<String, String>();
}
Map<String, String> codeMap = new HashMap<String, String>();
String[] fDimension = codeAndValue.split(";");
for (String x : fDimension) {
String[] sDimension = x.split(":");
codeMap.put(sDimension[0], sDimension[1]);
}
return codeMap;
}
}

//exportExcel工具类 end

 

//ExportExcel4PointMall工具类 begin

package com.zgxcw.export.util;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportExcel4PointMall<T> {

private static final int SHEET_SIZE = 60000;// excel中一个sheet的最大容量

/**
* @param headMap 表头对应关系map
* @param dataset 待导出数据
* @param out 输出流
* @param sheetName 页签名称
*/
public Workbook exportExcel(List<ExcelHead> head, Collection<T> dataset, OutputStream out, String sheetName,
HttpServletResponse response) {
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("gb2312"), "iso8859-1") + ".xlsx");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}

createWorkbookHead(head, workbook, sheet);
createWorkbookBody(dataset, workbook, sheet, head);

// try {
// workbook.write(out);
// } catch (IOException e) {
// e.printStackTrace();
// }

return workbook;
}

/**
* @param headMap 表头对应关系map
* @param dataset 待导出数据
* @param out 输出流
* @param sheetName 页签名称
*/
public Workbook exportExcel4BigDataSize(List<ExcelHead> head, List<T> dataList, OutputStream out, String sheetName,
HttpServletResponse response) {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("gb2312"), "iso8859-1") + ".xlsx");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}

// 声明一个工作薄
Workbook workbook = new SXSSFWorkbook(2000);
int dataSize = dataList.size();
int queryTimes = dataSize % SHEET_SIZE == 0 ? dataSize / SHEET_SIZE : (dataSize / SHEET_SIZE) + 1;
for (int i = 1; i <= queryTimes; i++) {
int start = (i - 1) * SHEET_SIZE;
int end = i * SHEET_SIZE;
if (i == queryTimes) {
end = dataSize;
}
Sheet sheet = workbook.createSheet();
createWorkbookHead(head, workbook, sheet);
createWorkbookBody(dataList.subList(start, end), workbook, sheet, head);
}
return workbook;
}

/**
* 创建头部信息
*/
public void createWorkbookHead(List<ExcelHead> headList, Workbook workbook, Sheet sheet) {
Row row = sheet.createRow(0);
// 生成一个样式
CellStyle headerStyle = workbook.createCellStyle();
// 设置这些样式
headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
Cell HeaderCell = null;
int i = 0;
for (ExcelHead head : headList) {
HeaderCell = row.createCell((i));
HeaderCell.setCellStyle(headerStyle);
HeaderCell.setCellType(XSSFCell.CELL_TYPE_STRING);
HeaderCell.setCellValue(head.getName());
i++;
}
}

/**
* 查询并写入记录
*/
public void createWorkbookBody(Collection<T> returnDataList, Workbook workbook, Sheet sheet, List<ExcelHead> headCellNameList) {
// 写记录
// 生成一个样式
CellStyle bodyStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
// 设置这些样式
bodyStyle.setFillForegroundColor(HSSFColor.WHITE.index);
bodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
// 这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧
DataFormat format = workbook.createDataFormat();
bodyStyle.setDataFormat(format.getFormat("@"));

// 设置日期格式
CellStyle dateStyle = workbook.createCellStyle();
dateStyle.setFillForegroundColor(HSSFColor.WHITE.index);
dateStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
dateStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
dateStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd HH:mm:ss"));

Row row = null;

Cell cell = null;

Iterator<T> it = returnDataList.iterator();
Map<String, Object> returnDataMap = new LinkedHashMap<String, Object>();
int rownum = 0; // 行

while (it.hasNext()) {
rownum++;
returnDataMap.clear();

T t = (T) it.next();
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
// cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
try {
field.setAccessible(true);
Object tValue = field.get(t);
returnDataMap.put(fieldName, tValue);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
row = sheet.createRow(rownum);
int j = 0;
for (ExcelHead headCell : headCellNameList) {
cell = row.createCell((j));
cell.setCellStyle(bodyStyle);
String value = returnDataMap.get(headCell.getCode()) != null ? returnDataMap.get(headCell.getCode()).toString() : "";
String transfer = headCell.getTransfer();

if (transfer != null && !transfer.trim().equals("") && value != null && !value.trim().equals("")) {
if (transfer.equals("LongToDateString")) {
value = this.LongToDateString(value);
} else {
value = this.decode(transfer).get(value);
value = (value == null ? "" : value);
}
}
if (value.matches("\\d+\\.?\\d*")) {
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(bodyStyle);
cell.setCellValue(Double.parseDouble(value));
} else if (StringUtils.isNotEmpty(value) && (returnDataMap.get(headCell.getCode())) instanceof Date) {
cell.setCellStyle(dateStyle);
cell.setCellValue((Date) returnDataMap.get(headCell.getCode()));
} else {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(bodyStyle);
cell.setCellValue(value);
}
if (rownum == returnDataList.size()) {
sheet.autoSizeColumn(j);
}
j++;
}
}
}

private String LongToDateString(String date) {
if (date == null || date.equals("") || date.equals("0"))
return "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
java.util.Date dt = new Date(Long.parseLong(date));
String sDateTime = sdf.format(dt);
return sDateTime;
}

private Map<String, String> decode(String codeAndValue) {
if (codeAndValue == null || codeAndValue.equals("")) {
return new HashMap<String, String>();
}
Map<String, String> codeMap = new HashMap<String, String>();
String[] fDimension = codeAndValue.split(";");
for (String x : fDimension) {
String[] sDimension = x.split(":");
codeMap.put(sDimension[0], sDimension[1]);
}
return codeMap;
}
}

//ExportExcel4PointMall工具类 end

 

 

 

//controller实用代码

 

/**
* 列表页生成EXCEl
*
* @param activityName
* @param activityState
* @param response
*/
@ResponseBody @RequestMapping(value = "/exportExchangeRecord")
public void exportExchangeRecord(QueryTelRecordRequest request
,@RequestParam(value = "timeBegin", required = false) String timeBegin
,@RequestParam(value = "timeEnd", required = false) String timeEnd,
HttpServletResponse response,@RequestParam(value = "flag", required = false) String flag) {

//是否有导出数据标志
Boolean haveDate = true;
PageInfoBase<QueryTelRecordResponse> resource=null;
List<CallRecoreDto> list = new ArrayList<CallRecoreDto>();
CallRecoreDto entity = new CallRecoreDto();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 有数据开启
try {

request.setPageIndex(1);
request.setPageSize(Integer.MAX_VALUE);
if (timeBegin!=null &&timeBegin!="") {
request.setCallTimeBegin(DateUtils.stringToLong(timeBegin
+ " 00:00:00", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")));
}
if (timeEnd!=null &&timeEnd!="") {
request.setCallTimeEnd(DateUtils.stringToLong(timeEnd
+ " 23:59:59", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")));
}
if (flag!=null &&flag!=""&&!flag.equals("2")) {
request.setStatus(flag);
}
resource = iTelRecordService.queryTelRecord(request);


if (resource != null && resource.getList() != null && resource.getList().size() > 0) {
Collection<QueryTelRecordResponse> recordList = resource.getList();

for (QueryTelRecordResponse queryTelRecordResponse : recordList) {
entity = new CallRecoreDto();
//拨打时间
if(queryTelRecordResponse.getCallTime()!=null){
//entity.setCallTime(format.format(queryTelRecordResponse.getCallTime()== null ? "" :queryTelRecordResponse.getCallTime()));
entity.setCallTime(format.format(queryTelRecordResponse.getCallTime()));
}

//修理厂名称
entity.setGarName(queryTelRecordResponse.getGarName());
//修理厂登录账号
entity.setGarLoginName(queryTelRecordResponse.getGarLoginName());
//经销商
entity.setDeaName(queryTelRecordResponse.getDeaName());
//经销商登陆账号
entity.setDeaLoginName(queryTelRecordResponse.getDeaLoginName());
//状态
entity.setStatus(queryTelRecordResponse.getStatus());
//原因
entity.setFailReason(queryTelRecordResponse.getFailReason());
list.add(entity);
}

}else{
// 下面这样处理后，excel导出后，就可以打开，否则提示生成失败
resource = new PageInfoBase<QueryTelRecordResponse>();
List<QueryTelRecordResponse> dtoL = new ArrayList<QueryTelRecordResponse>();
QueryTelRecordResponse dto = new QueryTelRecordResponse();
dtoL.add(dto);
resource.setList(dtoL);
haveDate = false;
}
} catch (Exception e) {
log.error(
"拨打记录 exportExchangeRecord--->Exception" + e.getMessage());
// 下面这样处理后，excel导出后，就可以打开，否则提示生成失败
resource = new PageInfoBase<QueryTelRecordResponse>();
List<QueryTelRecordResponse> dtoL = new ArrayList<QueryTelRecordResponse>();
QueryTelRecordResponse dto = new QueryTelRecordResponse();
dtoL.add(dto);
resource.setList(dtoL);
haveDate = false;
}

// 配置excel表头
List<ExcelHead> excelHead = new LinkedList<>();
excelHead.add(new ExcelHead("callTime", "拨打时间"));
excelHead.add(new ExcelHead("garName", "修理厂"));
excelHead.add(new ExcelHead("garLoginName", "修理厂登录账号"));
excelHead.add(new ExcelHead("deaName", "经销商"));
excelHead.add(new ExcelHead("deaLoginName", "经销商登录账号"));
excelHead.add(new ExcelHead("status", "结果"));
excelHead.add(new ExcelHead("failReason", "原因"));

// 开始写excel
ExportExcel<CallRecoreDto> excel = new ExportExcel<>();
String now = new SimpleDateFormat("yyyyMMdd").format(new Date());
OutputStream out = null;
try {
out = response.getOutputStream();
excel.exportExcel4BigDataSize(excelHead, list, out, "拨打记录_" + now, response).write(out);
out.flush();
} catch (IOException e) {
log.error("---- 拨打记录记录导出异常 ...", e.getMessage());
throw new ServiceException("拨打记录导出异常");
} finally {
if (out != null)
try {
out.close();
} catch (IOException e) {
log.error("---- 关闭拨打记录导出out异常 ...", e);
throw new ServiceException("关闭拨打记录导出out异常");
}
}
log.debug("---- 拨打记录导出 exportExchangeRecord end ...");
}