这次项目的过程中,需要做的的一个模块是导出功能,将一个页面的数据以Excel导出。
详细详细记录下这次的做法;
首先是在HTML处的写法。
<form name="form" method="post" <input type="hidden" name="jsonstr" id="jsonstr"/> </form>
<button class="layui-btn " ng-click="exportInfromation(tes)">导出信息 </button>
然后是在JS中发送请求;
$("#jsonstr").val(JSON.stringify($scope.commit))
document.form.action = "/vehicle/ViolationInformation";
document.form.submit();
前端是通过这种JSON方式发送。
接下来描述后端的写法:
DAO层以下是熟悉的对数据库的操作,不予讲解。
接下来是Service的实现。
@Override
public ResponseEntity<byte[]> responseViolationInfromation(Map map) {
HSSFWorkbook wkb = new HSSFWorkbook();
HSSFSheet cardExcelTemplate = wkb.createSheet("车辆报表信息导出");
cardExcelTemplate.setDefaultColumnWidth(33);
HSSFCellStyle style = wkb.createCellStyle();
HSSFFont font = wkb.createFont();
font.setColor(HSSFColor.RED.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
HSSFRow row2 = cardExcelTemplate.createRow(0);
row2.createCell(0).setCellValue("城市");
row2.createCell(1).setCellValue("车牌号码");
row2.createCell(2).setCellValue("车辆品牌");
row2.createCell(3).setCellValue("车辆类型");
row2.createCell(4).setCellValue("所属仓库");
row2.createCell(5).setCellValue("加油卡账号");
row2.createCell(6).setCellValue("保险时间的到期");
row2.createCell(7).setCellValue("保险状态");
row2.createCell(8).setCellValue("年检有效期止");
row2.createCell(9).setCellValue("年检状态");
row2.createCell(10).setCellValue("行驶里程数");
row2.createCell(11).setCellValue("未处理违章数");
row2.createCell(12).setCellValue("车辆状态");
List<CarAllInfoPO> vehicleInformation = carMapper.listViolationInformation(map);
Date date = new Date();
Calendar cal = Calendar.getInstance();
int yearNow = cal.get(Calendar.YEAR);
int monthNow = cal.get(Calendar.MONTH) + 1;
int result = yearNow * 12 + monthNow;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat simp = new SimpleDateFormat("yyyy-MM");
HSSFRow otherRow = null;
for (int i = 0; i < vehicleInformation.size(); i++) {
otherRow = cardExcelTemplate.createRow(i + 1);
// 0.城市
otherRow.createCell(0).setCellValue(vehicleInformation.get(i).getCity());
// 1.车牌号
otherRow.createCell(1).setCellValue(vehicleInformation.get(i).getCarNumber());
// 2.车辆品牌
otherRow.createCell(2).setCellValue(vehicleInformation.get(i).getBrandModelNumber());
// 3.车辆类型
otherRow.createCell(3).setCellValue(vehicleInformation.get(i).getVehicleType());
// 4.所属仓库
otherRow.createCell(4).setCellValue(vehicleInformation.get(i).getWarehouse());
// 5.加油卡账号
if (null == vehicleInformation.get(i).getViceCardNumber()) {
otherRow.createCell(5).setCellValue("无");
} else {
otherRow.createCell(5).setCellValue(vehicleInformation.get(i).getViceCardNumber());
}
// 6.保险时间
if (null == vehicleInformation.get(i).getStopDate()) {
otherRow.createCell(6).setCellValue("无");
} else {
otherRow.createCell(6).setCellValue(simpleDateFormat.format(vehicleInformation.get(i).getStopDate()));
}
// 7.保险状态
if (null != vehicleInformation.get(i).getStopDate()) {
Long insuranceStopDate = (vehicleInformation.get(i).getStopDate().getTime() - date.getTime()) / (1000 * 60 * 60 * 24);
if (insuranceStopDate != null && insuranceStopDate >= 60) {
otherRow.createCell(7).setCellValue("正常");
}
if (insuranceStopDate != null && insuranceStopDate < 60 && insuranceStopDate >= 0) {
otherRow.createCell(7).setCellValue("即将到期");
}
if (insuranceStopDate != null && insuranceStopDate < 0) {
otherRow.createCell(7).setCellValue("已过期");
}
} else {
otherRow.createCell(7).setCellValue("未上传");
}
// 8.年检有效期止
if (null == vehicleInformation.get(i).getValidityDate()) {
otherRow.createCell(8).setCellValue("无");
} else {
otherRow.createCell(8).setCellValue(simp.format(vehicleInformation.get(i).getValidityDate()));
}
// 9.年检状态
if (null != vehicleInformation.get(i).getValidityDate()) {
String validityDate = simp.format(vehicleInformation.get(i).getValidityDate());
String[] strs = validityDate.split("-");
Integer validityYear = Integer.valueOf(strs[0]);
Integer validityMonth = Integer.valueOf(strs[1]);
Integer validityResult = validityYear * 12 + validityMonth;
Integer ending = validityResult - result;
if (ending >= 2) {
otherRow.createCell(9).setCellValue("正常");
}
if (ending < 2 && ending >= 0) {
otherRow.createCell(9).setCellValue("即将到期");
}
if (ending < 0) {
otherRow.createCell(9).setCellValue("已过期");
}
} else {
otherRow.createCell(9).setCellValue("未上传");
}
// 10.总行驶里程数
if (null == vehicleInformation.get(i).getCurrentMileage()) {
otherRow.createCell(10).setCellValue("无");
} else {
otherRow.createCell(10).setCellValue(new Double(vehicleInformation.get(i).getCurrentMileage().toString()));
}
// 11.未处理违章数
otherRow.createCell(11).setCellValue(vehicleInformation.get(i).getViolationNumber());
// 12:待审核
if (vehicleInformation.get(i).getStatus() == 1) {
otherRow.createCell(12).setCellValue("启用");
}
if (vehicleInformation.get(i).getStatus() == 2) {
otherRow.createCell(12).setCellValue("待审核");
}
if (vehicleInformation.get(i).getStatus() == 0) {
otherRow.createCell(12).setCellValue("停用");
}
}
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
wkb.write(out);
} catch (IOException e) {
LOGGER.info("excel内存文件转换为的字节数组写到输出流中时产生错误," + e);
throw new RuntimeException("excel内存文件转换为的字节数组写到输出流中时产生错误," + e);
}
HttpHeaders headers = new HttpHeaders();
String fileName = null;
try {
fileName = new String("车辆报表信息导出.xls".getBytes("utf-8"), "iso-8859-1");
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("字符编码格式不支持," + e);
}
headers.setContentDispositionFormData("attachment", fileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
byte[] outputStreamByte = out.toByteArray();
ResponseEntity<byte[]> filebyte = new ResponseEntity<byte[]>(outputStreamByte, headers, HttpStatus.CREATED);
try {
out.close();
} catch (IOException e) {
throw new RuntimeException("excel内存文件转换为的字节数组写到输出流中关闭输出流时产生错误," + e);
}
return filebyte;
}
通过这个方法,实现了从数据库中查找出数据,然后插入到Excel中。
在controller中:
/**
* 返回一个车辆报表信息的excel文件 年检、保险即将到期,未处理违章的统计
*/
@RequestMapping(value = "vehicle/ViolationInformation", method = RequestMethod.POST)
public ResponseEntity<byte[]> resoibseVehicleInformation(String jsonstr) {
JSONObject obj = JSONObject.fromObject(jsonstr);
VehicleConditionQueryVO vehicleConditionQueryVO = (VehicleConditionQueryVO) JSONObject.toBean(obj, VehicleConditionQueryVO.class);
Map map = getMap(vehicleConditionQueryVO);
ResponseEntity<byte[]> responseEntity = carService.responseViolationInfromation(map);
return responseEntity;
}
到此,可以实现一个从网页导出到Excel的操作了。