/* 日志导出功能
* @author
* @param
* @return
*/
@RequestMapping("exportLog.do")
public void exportLog(HttpServletResponse response,HttpServletRequest request,MainLogVo queryMainLog) throws Exception{
//设置顺序
queryMainLog.setOrder("INSERTTIME");
//设置分页条件
DataPageVo<MainLogVo> dataPageVo=new DataPageVo<MainLogVo>();
dataPageVo.setNowPage(0);
dataPageVo.setPageSize(AppConst.DATA_NUM);
//获取查询出来的日志对象
logService.getQueryLogs(queryMainLog,dataPageVo);
//放入表头
List<String> headName = new ArrayList<String>();
headName.add("字段名称");
headName.add("字段老值");
headName.add("字段新值");
//放入表体
List<List<String>> cellList = new ArrayList<List<String>>();
for(int i=0;i<dataPageVo.getDataList().size();i++){
List<String> list = new ArrayList<String>();
MainLogVo mainLog= (MainLogVo)dataPageVo.getDataList().get(i);
list.add(mainLog.getColumncname());
list.add(mainLog.getOldvalue());
list.add(mainLog.getNewvalue());
cellList.add(list);
}
OutputStream os = null;
try {
os = response.getOutputStream();
//调用导出Excel的方法
os = response.getOutputStream();
response.reset();
//生成文件名
String date = new DateTime(DateTime.current(),DateTime.YEAR_TO_SECOND).toString();
response.setContentType("application/x-download");
response.setHeader("Cache-Control","max-age=0");
response.setHeader("Content-Disposition", "attachment; filename=\"" + "DATA_" + date + ".xls\"");
LOGGER.info("设置response结束。");
ByteArrayOutputStream out = new ByteArrayOutputStream();
HSSFWorkbook workbook = ExportUtil.excelMainLog(headName, cellList);
workbook.write(out);
byte[] byteArray = out.toByteArray();
response.setContentLength(byteArray.length);
os.write(byteArray);
LOGGER.info("导出完成。");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
LOGGER.info("导出失败。");
} catch (IOException e) {
e.printStackTrace();
}
try {
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//ExportUtil类
/**
* 导出核心日志信息到Excel-changhongqiang-2018-05-30
* @param headName
* @param cellList
* @return
* @throws Exception
*/
public static HSSFWorkbook excelMainLog(List<String> headName,List<List<String>> cellList){
//创建excel对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = wb.createSheet("核心日志信息");
//创建第一行
HSSFRow rowHead = sheet.createRow(0);
if(!AppUtil.isEmptyCollection(headName)){
for(int i=0;i<headName.size();i++){
createHeadCell(wb, rowHead, (short)(i), headName.get(i));
}
}
if(!AppUtil.isEmptyCollection(cellList)){
for(int j=0;j<cellList.size();j++){
HSSFRow row = sheet.createRow(j+1);
for(int k=0;k<cellList.get(j).size();k++){
createCell(wb, row, (short)(k), cellList.get(j).get(k)+"");
}
}
}
//设置自动调整列宽
for(int i = 0; i < headName.size(); i++){
sheet.autoSizeColumn((short)i);
}
return wb;
}
/**
* 创建Excel主体样式
* @param wb 操作对象
* @param row 行单元
* @param col 列单元
* @param val 单元格值
*/
public static void createCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
LOGGER.info("HSSFWorkbook wb:"+wb);
LOGGER.info("HSSFRow row:"+row);
// 创建单元格
HSSFCell cell = row.createCell(col);
// 填充值
cell.setCellValue(val);
LOGGER.info("填充单元格值完成");
// 创建样式
HSSFCellStyle cellstyle = wb.createCellStyle();
//设置表格居中
cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
cellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平
cell.setCellStyle(cellstyle);
LOGGER.info("设置单个单元格样式完成");
}
/**
* 创建Excel表头样式
* @param wb 操作对象
* @param row 行单元
* @param col 列单元
* @param val 单元格值
*/
public static void createHeadCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
// 创建单元格
HSSFCell cell = row.createCell(col);
// 填充值
cell.setCellValue(val);
LOGGER.info("设置表头列值完成");
// 创建样式
HSSFCellStyle cellstyle = wb.createCellStyle();
//设置单元格样式居中
cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //水平
//设置背景色
cellstyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
//粗体显示
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体大小
font.setFontHeightInPoints((short) 12);
//设定需要用到的字体格式
cellstyle.setFont(font);
//设定边框
cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell.setCellStyle(cellstyle);
}
3、需要的jar包
链接地址:https://pan.baidu.com/s/1LWksKROcUGombVcAJ6yUgA
密码:i5yh