图片:
控制层:
@ResponseBody
@RequestMapping(value = "queryInfoBydateAndvillageName")
public Map<String, Object> queryInfoBydateAndvillageName(HttpServletRequest request, String sDate, String eDate, String villageName, Authentication authentication) {
Map<String, Object> result = new HashMap<String, Object>();
SessionUser sessionUser = (SessionUser) authentication.getPrincipal();
//ljCheckInstanceM是实体类
List<ljCheckInstanceM> resultPhotoList = dailyInService.queryPhotoInfoBydateAndvillageName(sDate, eDate, villageName, sessionUser.getLevel(), sessionUser.getId());
//导出汇总记录
Map<String, String> fileQD = ExportExcelUtil.putInto2Excel(request.getServletContext(), null, resultPhotoList);
String excelPathQD = fileQD.get("path");
String excelNameQD = fileQD.get("name");
//导出图片
Map<String, String> fileInfoPicture = ExportExcelUtil.putIntoExcel(request.getServletContext(), null, resultPhotoList);
String excelPathP = fileInfoPicture.get("path");
String excelNameP = fileInfoPicture.get("name");
result.put("resultList", resultPhotoList);//结果集
result.put("excelPathQD", "../" + excelPathQD);//路径
result.put("excelNameQD", excelNameQD);//Excel名称
result.put("excelPathPicture", "../" + excelPathP);//路径
result.put("excelNamePicture", excelNameP);//Excel名称
return result;
}
生成Excel工具类:
package com.cyl.util;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.imageio.ImageIO;
import javax.servlet.ServletContext;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.cyl.model.ljCheckBodyTarget;
import com.cyl.model.ljCheckInstanceM;
public class ExportExcelUtil {
private static Logger log = Logger.getLogger(ExportExcelUtil.class);
/**
* 导出Excel,纯导出数据
*
* @param context
* 上下文会话对象
* @param dicument
* 生成文件的目錄文件名,參數為空時默認為excel
* @param list
* Unallowable类数据集
* @return path返回路徑;name文件名;
*/
public static Map<String, String> putInto2Excel(ServletContext context, String dicument,
List<ljCheckInstanceM> list) {
Map<String, String> result = new HashMap<String, String>();
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow(0);
FileOutputStream out = null;
String fileName = TUtil.format("yyyy_MM_dd_HHmmssSSSSSS");
fileName += ".xlsx";
String filePath = null;
try {
row = sheet.createRow(0);
//表头
row.createCell(0).setCellValue("ID");
row.createCell(1).setCellValue("检查频率");
row.createCell(2).setCellValue("检查月份");
row.createCell(3).setCellValue("检查时间");
row.createCell(4).setCellValue("小区名称");
row.createCell(5).setCellValue("镇/街道");
row.createCell(6).setCellValue("评分结果");
row.createCell(7).setCellValue("提交人");
// 日期格式转为字符串输出
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
int rowIndex = 0;//写入行下标
int idMark = Integer.MIN_VALUE;//标记写到了哪个小区
for (int i = 1; i <= list.size(); i++) {
ljCheckInstanceM data = list.get(i - 1);
if (data.getId() != idMark) {//数据去重处理
rowIndex++;
row = sheet.createRow(rowIndex);
row.createCell(0).setCellValue(data.getId());
row.createCell(1).setCellValue(data.getFrequency());
row.createCell(2).setCellValue(data.getSeq());
String endTIme = sdf.format(data.getCheckDate());
row.createCell(3).setCellValue(endTIme);
row.createCell(4).setCellValue(data.getAttr1());
row.createCell(5).setCellValue(data.getAttr2());
row.createCell(6).setCellValue(data.getTotalScore());
row.createCell(7).setCellValue(data.getAttr3());
}
idMark = data.getId();
}
String mes = context.getRealPath("/");
String relpath = (StringUtils.isBlank(dicument) ? "excel" : dicument) + "/";
filePath = mes + relpath;// 文件存放路径
File fileDir = new File(filePath);
if (!(fileDir.exists() && fileDir.isDirectory())) {
new File(filePath).mkdirs();
}
result.put("path", relpath);
result.put("name", fileName);
out = new FileOutputStream(filePath + fileName);
wb.write(out);
} catch (FileNotFoundException e) {
e.printStackTrace();
log.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
log.error(e.getMessage());
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return result;// 返回下载结果
}
/**
* 导出Excel, 导出数据+图片
*
* @param context
* 上下文会话对象
* @param dicument
* 生成文件的目錄文件名,參數為空時默認為excel
* @param list
* Unallowable类数据集
* @return path返回路徑;name文件名;
*/
public static Map<String, String> putIntoExcel(ServletContext context, String dicument,
List<ljCheckInstanceM> list) {
Map<String, String> result = new HashMap<String, String>();
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow(0);
FileOutputStream out = null;
String fileName = TUtil.format("yyyy_MM_dd_HHmmssSSSSSS");
fileName += ".xlsx";
String mes = context.getRealPath("/");
String filePath = null;
BufferedImage bufferImg = null;
try {
row = sheet.createRow(0);
row.createCell(0).setCellValue("检查频率");
row.createCell(1).setCellValue("检查月份");
row.createCell(2).setCellValue("检查时间");
row.createCell(3).setCellValue("小区名称");
row.createCell(4).setCellValue("镇/街道");
row.createCell(5).setCellValue("图片");
// 日期格式转为字符串输出
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
int rowIndex = 0;//写入行下标
int imgColIndex = 5;//写入照片列
int idMark = Integer.MIN_VALUE;//标记写到了哪个小区
for (int i = 1; i <= list.size(); i++) {
ljCheckInstanceM data = list.get(i - 1);
if(data.getId() != idMark) {
rowIndex++;
imgColIndex = 5;
row = sheet.createRow(rowIndex);
//表头
row.createCell(0).setCellValue(data.getFrequency());
row.createCell(1).setCellValue(data.getSeq());
String endTIme = sdf.format(data.getCheckDate());
row.createCell(2).setCellValue(endTIme);
row.createCell(3).setCellValue(data.getAttr1());
row.createCell(4).setCellValue(data.getAttr2());
row.setHeight((short)1500);//设置行高度
}
//塞入图片
if (data.getAttr4() != null && !data.getAttr4().equals("")) {
// 先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
try {
String file = mes + data.getAttr4();//获取地址
bufferImg = ImageIO.read(new File(file));
} catch (IOException e) {
e.printStackTrace();
}
try {
ImageIO.write(bufferImg, "png", byteArrayOut);
} catch (IOException e) {
e.printStackTrace();
}
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
//8个参数解释: 下面说的坐标是cell内坐标, cell中左上角为(x,y)->(0,0)位置
//dx1 图片左上角所在x坐标 (起始cell)
//dy1 图片左上角所在y 坐标 (起始cell)
//dx2 图片右下角所在x坐标 (结束cell)
//dy2 图片右下角所在y坐标 (结束cell)
//col1 图片起始cell所在的列
//row1 图片起始cell所在的行
//col2 图片结束cell所在的列
//row2 图片结束cell所在的行
//下面图片左上角位于是第i行第7列, 右下角位于第i+1行第8列
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, imgColIndex, rowIndex, imgColIndex + 1, rowIndex + 1);
// 插入图片
patriarch.createPicture(anchor,
wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
sheet.setColumnWidth(imgColIndex, 256 * 20);//列宽
imgColIndex++;
}
idMark = data.getId();
}
String relpath = (StringUtils.isBlank(dicument) ? "excel" : dicument) + "/";
filePath = mes + relpath;// 文件存放路径
File fileDir = new File(filePath);
if (!(fileDir.exists() && fileDir.isDirectory())) {
new File(filePath).mkdirs();
}
result.put("path", relpath);
result.put("name", fileName);
out = new FileOutputStream(filePath + fileName);
wb.write(out);
} catch (FileNotFoundException e) {
e.printStackTrace();
log.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
log.error(e.getMessage());
} finally {
try {
if (out != null)
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return result;// 返回下载结果
}
}
基础类:
package com.cyl.util;
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.StringTokenizer;
import org.apache.log4j.Logger;
/**
* 基础类
*
* @author Qiang1_Zhang
*/
public class TUtil {
static Logger log = Logger.getLogger(TUtil.class);
/**
* 日期转换函数
*
* @param format
* 需要转换的格式
* @return 转换后的日期
*/
public static String format(String format) {
return new SimpleDateFormat(format).format(new Date());
}
/**
* 日期转换函数
*
* @param format
* 需要转换的格式
* @return 转换后的日期
*/
public static String format(Date date, String format) {
return new SimpleDateFormat(format).format(date);
}
/**
* 打印函数
*
* @param str
* 对象类型
*/
public static void print(Object str) {
System.out.println(str);
}
/**
* 计算距今指定天数的日期
*
* @param day
* 相差的天数,可为负数
* @return 计算之后的日期
*/
public static String GetDay(int day) {
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cal.setTime(new Date());// 设置日历时间
cal.add(Calendar.DAY_OF_MONTH, day);// 天数
String strDate = sdf.format(cal.getTime());// 得到你想要的天数
return strDate;
}
/**
* 获取报表模板路径
*
* @return
*/
public static String getURL() {
String dir = System.getProperty("user.dir");
print("dir=" + dir);
dir = dir.substring(0, dir.lastIndexOf("\\"));
String filePath = dir;
return filePath;
}
/**
* String类型日期转换为长整型
*
* @param date
* String类型日期
* @param format
* 日期格式
* @return long
*/
public static long strDateToLong(String date, String... format) {
String format1 = null;
if (format.length != 0) {
format1 = format[0];
} else {
format1 = "yyyy-MM-dd HH:mm:ss";
}
String sDt = date;
SimpleDateFormat sdf = new SimpleDateFormat(format1);
long lTime = 0;
try {
Date dt2 = sdf.parse(sDt);
lTime = dt2.getTime();
print(lTime);
} catch (ParseException e) {
e.printStackTrace();
}
return lTime;
}
public static void longToString(long l) {
format("");
}
/**
* 获取文件创建时间
*
* @param file
* 文件目录
*/
public static String getCreateTime(File file) {
// file = new File("e:/1.xls");
String date = "";
// file.lastModified();
try {
Process process = Runtime.getRuntime().exec(
"cmd.exe /c dir " + file.getAbsolutePath() + "/tc");
InputStream is = process.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(is));
for (int i = 0; i < 5; i++) {// 前五行是其他的信息
br.readLine();
}
String createDateLine = br.readLine();
StringTokenizer tokenizer = new StringTokenizer(createDateLine);
date = tokenizer.nextToken() + " " + tokenizer.nextToken();
br.close();
// print(date);
} catch (IOException e) {
log.error("" + e.getMessage());
}
return date;
}
/**
* 获取文件最后修改时间
*
* @param filePath
* 文件目录
*/
public static void getLastModifyTime(File filePath) {
filePath = new File(
"\\\\10.131.18.8\\rt3生產機種\\ProductionReprot\\TraceAlterReprot-reprot");
File[] list = filePath.listFiles();
// for(File file : list){
// print(file.getAbsolutePath()+"\tcreate time:"+getCreateTime(file));
// }
for (File file : list) {
Date date = new Date(file.lastModified());
print(format(date, "yyyy-MM-dd"));
}
}
public static void getFile() {
String root = "\\\\10.131.18.8\\rt3生產機種\\ProductionReprot";
File filePath = new File(root);
File[] list = filePath.listFiles();
for (File file : list) {
print(file.getName()
+ "\t"
+ new File(file.getAbsolutePath() + "\\"
+ TUtil.format("yyyy-MM-dd") + ".xls").exists());
}
}
static void test() {
String today = TUtil.format("yyyy-MM-dd");
String dest = ReadProperties.ReadProprety("server.report.path")
+ "TraceAlterReprot-reprot" + "\\" + today + "\\";
print(dest);
File dir = new File(dest);// 创建当天目录
if (!dir.exists()) {
dir.mkdir();
}
}
public static void getTimeDifference() {
try {
Date d1 = new SimpleDateFormat("yyyy-MM-dd").parse("2014-09-15");
Date d2 = new SimpleDateFormat("yyyy-MM-dd").parse("2014-09-14");
print((d2.getTime() - d1.getTime()) / 1000 / 60 / 60 / 24);
} catch (ParseException e) {
e.printStackTrace();
}
}
public List<String> distinctList(List<String> list) {
HashSet<String> h = new HashSet<String>(list);
list.clear();
list.addAll(h);
return list;
}
public List<Object> removeDuplicate(List<Object> list) {
HashSet<Object> h = new HashSet<Object>(list);
list.clear();
list.addAll(h);
return list;
}
/**
* 获取四舍五入的整数
* @param input 乘数
* @param rate 比率
* @return 取整后的结果
*/
public double getRound(int input,double rate){
double tmp = input * rate;
return Math.round(tmp);
}
/**
* 获取四舍五入的整数
* @param input 乘数
* @param rate 比率
* @return 取整后的结果
*/
public double ceil(int input,double rate){
double tmp = input * rate;
return Math.ceil(tmp);
}
}
前端
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
</head>
<body>
<!-- 片段 -->
<div class="cl pd-5 bg-1 bk-gray mt-20">
<span class="l">
<a class="btn btn-primary radius" data-title="导出汇总记录" id="downloadExcelQueryData" href="javascript:;"><i class="Hui-iconfont"></i> 导出汇总记录</a>
</span>
<span class="l" style="margin-left: 10px">
<a class="btn btn-primary radius" data-title="导出明细图片" id="downloadExcelPicture" href="javascript:;"><i class="Hui-iconfont"></i> 导出图片</a>
</span>
<span class="r" id="flushTime"></span>
</div>
</body>
<script>
/**
* 提供片段
*
*/
function queryInfo() {
//省略获取参数...
//请求
$.ajax({
type: 'post',
url: '<%=basePath%>inspection/queryInfoBydateAndvillageName.cyl',
dataType: 'json',
data: {
sDate: logmin,
eDate: logmax,
villageName: compname
},
success: function(data) {
console.log(data)
setDownloadButton("downloadExcelQueryData",data.excelPathQD, data.excelNameQD);//设置下载地址
setDownloadButton("downloadExcelPicture", data.excelPathPicture, data.excelNamePicture);//设置下载地址
},
error: function() {
layer.alert("查询失败");
}
});
}
//下载地址
function setDownloadButton(id, path, name) {
var downloadButton = $("#"+ id)[0];
if (path == null || name == null || typeof (path) == "undefined"
|| typeof (name) == "undefined")
downloadButton.setAttribute("href", "#");
else
downloadButton.setAttribute("href", ""+ path +""+ name +"");
}
</script>
</html>