这里是上传excel保存数据库
添加依赖
<!-- 导出excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
<form class="form-inline" action="/temperatureRecordExcelDownloads" method="get">
<button type="submit" class="btn btn-success">导出数据</button>
</form>
/**
* @Description:导出excel @param @return @throws
*/
// @ResponseBody
@RequestMapping(value = "temperatureRecordExcelDownloads", method = RequestMethod.GET)
public void UserExcelDownloads(HttpServletResponse response, HttpSession session) throws IOException {
try {
int a = 0;
List<TemperatureRecord> temperatureRecordLists = new ArrayList<>();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
Date date = new Date();
String dateString = GetDateUtil.getDateTime(date);
// 设置要导出的文件的名字
String fileName = "测温数据" + dateString + ".xls";
// 新增数据行,并且设置单元格数据
int rowNum = 1;
//这里是你要导出表格第一行的标题,要几个自己加上去
String[] headers = { "用户姓名", "性别", "体温(摄氏度)", "体温是否正常", "测温时间", "运营场所", "任务名称", "测温地点", "位置坐标(x,y,z)",
"测温图片" };
// headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
// 在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 单独设置列宽,第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(9, 4300);
sheet.setColumnWidth(5, 7000);
sheet.setColumnWidth(4, 4800);
sheet.setColumnWidth(3, 3000);
sheet.setColumnWidth(2, 3000);
sheet.setColumnWidth(8, 5000);
sheet.setColumnWidth(7, 7000);
//数据库查询出数据
temperatureRecordLists = temperatureMapper.getAll();
// 在表中存放查询到的数据放入对应的列
for (TemperatureRecord temperatureRecord : temperatureRecordLists) {
HSSFRow row1 = sheet.createRow(rowNum);
// 设置行高
row1.setHeight((short) 1500);
row1.createCell(0).setCellValue(temperatureRecord.getName());
row1.createCell(1).setCellValue(temperatureRecord.getSex());
row1.createCell(2).setCellValue(temperatureRecord.getTemperature());
row1.createCell(3).setCellValue(temperatureRecord.getIsNormal());
row1.createCell(4).setCellValue(GetDateUtil.getDateTime(temperatureRecord.getCreateTime()));
row1.createCell(5).setCellValue(temperatureRecord.getOperation());
row1.createCell(7).setCellValue(temperatureRecord.getPointName());
if (temperatureRecord.getFloor() == null) {
row1.createCell(6).setCellValue("");
} else {
row1.createCell(6).setCellValue(temperatureRecord.getFloor());
}
row1.createCell(8).setCellValue(temperatureRecord.getxPoint() + ":" + temperatureRecord.getyPoint()
+ ":" + temperatureRecord.getzPoint());
/* 图片处理,数据库存图片路径,如果导出的时候在服务器没有该图片,
则导出时候显示的我们自己默认的图片nullPhoto.jpg,
如果默认图片也不存在,则导出文字。相应逻辑可以自己修改。
如果不需要导出图片,删掉这部分即可*/
File l = new File(fileName);
String path = ClassUtils.getDefaultClassLoader().getResource("static").getPath()
+ temperatureRecord.getPhotoUrl();
String nullpath = ClassUtils.getDefaultClassLoader().getResource("static").getPath()
+ "/img/pic/nullPhoto.jpg";
File file;
// 正常情况下path为数据库图片在服务器的路径,一定存在。
if (path == null || path.length() < 0) {
file = new File(nullpath);
if (file.exists()) {
setPhoto(workbook, sheet, a, file);
} else {
row1.createCell(9).setCellValue("暂无图片");
}
} else {
file = new File(path);
// 二次判断,判断文件是否存在
if (file.exists()) {
setPhoto(workbook, sheet, a, file);
} else {
file = new File(nullpath);
if (file.exists()) {
setPhoto(workbook, sheet, a, file);
} else {
row1.createCell(9).setCellValue("暂无图片");
}
}
}
rowNum++;
a++;
}
// 写入excel文件
// 设置生成的Excel的文件名,并以中文进行编码
response.reset();
String codedFileName = new String(("测温数据" + dateString).getBytes("gbk"), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xls");
// 响应类型,编码
response.setContentType("application/octet-stream;charset=UTF-8");
// 形成输出流
OutputStream osOut = response.getOutputStream();
// 将指定的字节写入此输出流
workbook.write(osOut);
// 刷新此输出流并强制将所有缓冲的输出字节被写出
osOut.flush();
// 关闭流
osOut.close();
/*
* dispose of temporary files backing this workbook on disk 处理在磁盘上备份此工作簿的临时文件
* SXSSF分配临时文件,您必须始终清除显式,通过调用dispose方法
*/
} catch (Exception e) {
e.printStackTrace();
response.sendRedirect("error.action");
}
}
/**
* @Title: @Description: excel生成图片工具 @param @return @throws
*/
public void setPhoto(HSSFWorkbook workbook, HSSFSheet sheet, int a, File file) {
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
BufferedImage bufferImg = null;
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
try {
bufferImg = ImageIO.read(file);
// bufferImg = ImageIO.read(new File(ClassUtils.getDefaultClassLoader().getResource("static").getPath()
// + temperatureRecord.getPhotoUrl()));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
// anchor主要用于设置图片的属性
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 9, a + 1, (short) 9, a + 1);
anchor.setAnchorType(3);
// 插入图片
patriarch.createPicture(anchor,
workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
// System.out.println("----Excle文件已生成------");
} catch (Exception e) {
e.printStackTrace();
}
}
导出的时候会发现不能选择路径,这个我测试了很多次,写法没问题,是因为跟浏览器有关,有时候可以有时候不行。至于导出的文件在哪儿,找不到的话可以去浏览器默认的下载文件路径找。