/**
* 导出excel
*
* @param request
* @param hmInfo
*/
@RequestMapping(value = "/exportXls")
public void exportXls(HttpServletRequest request, HttpServletResponse response,
HmInfo hmInfo) throws IOException{
try {
int a = 0;
int s = 1;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
Date date = new Date();
String dateString = DateUtils.formatDate(date);
// 设置要导出的文件的名字
String fileName = "电能表更换表单" + dateString + ".xls";
// 新增数据行,并且设置单元格数据
int rowNum = 1;
//这里是你要导出表格第一行的标题,要几个自己加上去
String[] headers = { "标题","旧表条码", "表计类型", "剩余余额照片", "剩余余额", "有功总照片", "有功总示数", "有功峰照片", "有功峰示数", "有功平照片",
"有功平示数","有功谷照片","有功谷示数","新表条码","新表照片","提交人","提交时间"};
/*String[] headers = { "旧表条码", "表计类型", "余额照片"};*/
// 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, 4300);
sheet.setColumnWidth(2, 4300);
sheet.setColumnWidth(8, 5000);
sheet.setColumnWidth(7, 7000);
//数据库查询出数据
LoginUser user = (LoginUser) SecurityUtils.getSubject().getPrincipal();
if (!user.getUsername().equals("admin")){
hmInfo.setUserid(user.getId());
}
List<HmInfo> list = hmInfoService.findList(hmInfo);
List<HmInfo> exportList = null;
// 过滤选中数据
String selections = request.getParameter("selections");
if (oConvertUtils.isNotEmpty(selections)) {
List<String> selectionList = Arrays.asList(selections.split(","));
exportList = list.stream().filter(item -> selectionList.contains(getId(item))).collect(Collectors.toList());
} else {
exportList = list;
}
// 在表中存放查询到的数据放入对应的列
for (HmInfo hmInfo1 : exportList) {
HSSFRow row1 = sheet.createRow(rowNum);
// 设置行高
row1.setHeight((short) 1500);
//第1列
row1.createCell(0).setCellValue(hmInfo1.getTitle());
//第2列
row1.createCell(1).setCellValue(hmInfo1.getOldBarcode());
//第3列
row1.createCell(2).setCellValue(hmInfo1.getType());
//row1.createCell(2).setCellValue(hmInfo1.getPowerfile());
/* 图片处理,数据库存图片路径,如果导出的时候在服务器没有该图片,
则导出时候显示的我们自己默认的图片nullPhoto.jpg,
如果默认图片也不存在,则导出文字。相应逻辑可以自己修改。
如果不需要导出图片,删掉这部分即可*/
//第4列(图片)
File path = new File(ResourceUtils.getURL("classpath:").getPath());
File file;
file = new File("D://opt//upFiles//"+hmInfo1.getYuefile());
setPhoto(workbook, sheet, a+3, file,s);
//第5列
row1.createCell(4).setCellValue(hmInfo1.getYue().toString());
//第6列(图片)
path = new File(ResourceUtils.getURL("classpath:").getPath());
file = new File("D://opt//upFiles//"+hmInfo1.getPowerfile());
setPhoto(workbook, sheet, a+5, file,s);
//第7列
row1.createCell(6).setCellValue(hmInfo1.getPower().toString());
//第8列(图片)
path = new File(ResourceUtils.getURL("classpath:").getPath());
file = new File("D://opt//upFiles//"+hmInfo1.getPowerffile());
setPhoto(workbook, sheet, a+7, file,s);
//第9列
row1.createCell(8).setCellValue(hmInfo1.getPowerf().toString());
//第10列(图片)
path = new File(ResourceUtils.getURL("classpath:").getPath());
file = new File("D://opt//upFiles//"+hmInfo1.getPowerpfile());
setPhoto(workbook, sheet, a+9, file,s);
//第11列
row1.createCell(10).setCellValue(hmInfo1.getPowerp().toString());
//第12列(图片)
path = new File(ResourceUtils.getURL("classpath:").getPath());
file = new File("D://opt//upFiles//"+hmInfo1.getPowergfile());
setPhoto(workbook, sheet, a+11, file,s);
//第13列
row1.createCell(12).setCellValue(hmInfo1.getPowerg().toString());
//第14列
row1.createCell(13).setCellValue(hmInfo1.getNewBarcode());
//第15列(图片)
path = new File(ResourceUtils.getURL("classpath:").getPath());
file = new File("D://opt//upFiles//"+hmInfo1.getNewbfile());
setPhoto(workbook, sheet, a+14, file,s);
//第16列
row1.createCell(15).setCellValue(hmInfo1.getCreateBy());
//第17列
Date time = hmInfo1.getCreateTime();
SimpleDateFormat sdf = new SimpleDateFormat( " yyyy年MM月dd日 " );
String str = sdf.format(time);
row1.createCell(16).setCellValue(str);
rowNum++;
s++;
}
// 写入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/json;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,int s) {
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);
/*关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下:
dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离;
dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离;
dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离;
dy2:终止单元格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离;
col1:起始单元格列序号,从0开始计算;
row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1;
col2:终止单元格列序号,从0开始计算;
row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3;*/
// anchor主要用于设置图片的属性
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) a, s, (short) a, s );
anchor.setAnchorType(3);
// 插入图片
patriarch.createPicture(anchor,
workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
// System.out.println("----Excle文件已生成------");
} catch (Exception e) {
e.printStackTrace();
}
}
07-19
5449
12-26
2439