一、先准备一个excel 模版,里面把需要的数据写好
二、模板在工程中的存放路径
三、引入poi的相关jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency> /**
* 导出单条机械基本信息和检测信息数据
*/
@ApiOperation(value = "导出单条机械基本信息和检测信息数据", notes = "导出单条机械基本信息和检测信息数据")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query", name = "unitId", value = "账号等级", required = true, dataType = "Long"),
@ApiImplicitParam(paramType = "query", name = "userId", value = "用户Id", required = true, dataType = "Long"),
@ApiImplicitParam(paramType = "query", name = "id", value = "id", required = true, dataType = "Long"),
@ApiImplicitParam(paramType = "query", name = "uniqueNumber", value = "环保唯一号码", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "category", value = "所有人/单位名称", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "peopleContact", value = "联系人", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "contact", value = "联系方式", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "entrustUnit", value = "委托单位", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "startTime", value = "检测开始时间", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "endTime", value = "检测结束时间", required = false, dataType = "String"),
@ApiImplicitParam(paramType = "query", name = "testSite", value = "检测地点", required = false, dataType = "String")
})
@GetMapping("/exportTestMechanicalDataToExcel")
public void exportTestMechanicalDataToExcel(@RequestParam("unitId")Long unitId, @RequestParam("userId") Long userId, Long id,
String uniqueNumber, String category, String peopleContact, String contact,
String entrustUnit, String startTime, String endTime, String testSite,
HttpServletRequest request,HttpServletResponse response) throws Exception {
List<Long> longList = unitService.selectUnitLongList(unitId);
TestMechanicalInformation testMechanicalInf = testingInformationService.selectDetectionToExcel(longList,uniqueNumber,category,
peopleContact,contact,entrustUnit,startTime,endTime,testSite,id);
String dateTime = LocalDateTime.now().toString().substring(0, 19).replaceAll(":","").replaceAll("T","");
//这么写打包之后获取不到文件
//ClassPathResource resource = new ClassPathResource("excel" + File.separator + "temple.xlsx");
// 获取文件
// File file = resource.getFile();
// FileInputStream in =new FileInputStream(file);
//解决服务器获取模板路径问题
//获取文件
ClassPathResource resource = new ClassPathResource("excel" + File.separator + "temple.xlsx");
InputStream in = resource.getInputStream();
//读取excel模板
XSSFWorkbook wb = new XSSFWorkbook(in);
//读取了模板内所有sheet内容
XSSFSheet sheet = wb.getSheetAt(0);
//如果这行没有了，整个公式都不会有自动计算的效果的
sheet.setForceFormulaRecalculation(true);
fileService.setTestMechanicalInfCellValue(testMechanicalInf,sheet);
// 保存文件的路径
String realPath = PathUtil.getTestMechanicalExcelPath(userId, id);
String newFileName = "检测数据-" +
testMechanicalInf.getUnitName() +
"-" +testMechanicalInf.getUniqueNumber() +
"-" + dateTime + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
//修改模板内容导出新模板
FileOutputStream out = new FileOutputStream(realPath + newFileName);
wb.write(out);
out.close();
//返回文件给前端
fileService.downloadFiles(request,response, realPath + newFileName);
}
四、读取模板文件,这里读取的是xlsx方式的
ClassPathResource resource = new ClassPathResource("excel" + File.separator + "temple.xlsx");
// 获取文件
File file = resource.getFile();
FileInputStream in =new FileInputStream(file);
//读取excel模板
XSSFWorkbook wb = new XSSFWorkbook(in);
//读取了模板内所有sheet内容
XSSFSheet sheet = wb.getSheetAt(0);
//如果这行没有了，整个公式都不会有自动计算的效果的
sheet.setForceFormulaRecalculation(true);如果是xls格式的，就改为：
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
//读取excel模板
HSSFWorkbook wb = new HSSFWorkbook(fs);
//读取了模板内所有sheet内容
HSSFSheet sheet = wb.getSheetAt(0);五、找到相应的数据行，进行数据填充，要从0开始计数，excel中的第1行，读取的时候是从0开始的
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM");
SimpleDateFormat dfDate = new SimpleDateFormat("yyyy年MM月dd日");
//委托单位
sheet.getRow(1).getCell(1).setCellValue(testMechanicalInf.getEntrustUnit());
//联系人/电话
sheet.getRow(1).getCell(5).setCellValue(testMechanicalInf.getPeopleContact());
//使用单位
sheet.getRow(2).getCell(1).setCellValue(testMechanicalInf.getCategory());
//样品编号
sheet.getRow(2).getCell(5).setCellValue(testMechanicalInf.getSampleNumber());
//检测时间
String testTime =df.format(df.parse(testMechanicalInf.getTestingTime()));
sheet.getRow(3).getCell(1).setCellValue(testTime);
//检测地点
sheet.getRow(3).getCell(5).setCellValue(testMechanicalInf.getTestSite());
//检测依据
sheet.getRow(4).getCell(1).setCellValue(testMechanicalInf.getDetectionBased());
//检测仪器设备
sheet.getRow(5).getCell(1).setCellValue(testMechanicalInf.getTestingEquipment());
//机械设备名称
sheet.getRow(7).getCell(1).setCellValue(testMechanicalInf.getCarTypeName());
//环保唯一号码
sheet.getRow(7).getCell(5).setCellValue(testMechanicalInf.getUniqueNumber());
//生产单位
sheet.getRow(8).getCell(1).setCellValue(testMechanicalInf.getMachineryManufacturer());
//出厂日期
String time =df.format(df.parse(testMechanicalInf.getManufactureTime()));
sheet.getRow(9).getCell(1).setCellValue(time);
//所有人/单位名称
sheet.getRow(9).getCell(5).setCellValue(testMechanicalInf.getCategory());
//其他永久性号码
sheet.getRow(10).getCell(1).setCellValue(testMechanicalInf.getPermanentNumbers());
//发动机型号
sheet.getRow(10).getCell(5).setCellValue(testMechanicalInf.getEngineModel());
//发动机额定功率(KW)
sheet.getRow(11).getCell(1).setCellValue(testMechanicalInf.getEngineRating());
//发动机出厂年月
sheet.getRow(11).getCell(5).setCellValue(testMechanicalInf.getEnManufactureTime());
//发动机制造厂名称
sheet.getRow(12).getCell(1).setCellValue(testMechanicalInf.getEngineManufacturer());
//车辆类型
sheet.getRow(13).getCell(1).setCellValue(testMechanicalInf.getCarTypeName());
//所属区域
sheet.getRow(13).getCell(5).setCellValue(testMechanicalInf.getUniqueNumber());
//试验用燃料牌号
sheet.getRow(14).getCell(1).setCellValue(testMechanicalInf.getFuelType());
//排放阶段
sheet.getRow(14).getCell(5).setCellValue(testMechanicalInf.getEngDischargeStage());
//是否加装尾气处理装置
sheet.getRow(15).getCell(1).setCellValue(testMechanicalInf.getTreatmentDevice());
//污染控制装置技术
sheet.getRow(15).getCell(5).setCellValue(testMechanicalInf.getControlTechnology());
//主要使用地点
sheet.getRow(16).getCell(1).setCellValue(testMechanicalInf.getPlaceUse());
//烟度值(光吸收系数)实测数据(m-1)
String[] result = testMechanicalInf.getMeasuredValue().split(";");
sheet.getRow(19).getCell(1).setCellValue(result[0]);
sheet.getRow(19).getCell(2).setCellValue(result[1]);
sheet.getRow(19).getCell(3).setCellValue(result[2]);
//烟度值(光吸收系数)标准要求(m-1)
sheet.getRow(19).getCell(4).setCellValue("");
//烟度值(光吸收系数)备注
sheet.getRow(19).getCell(5).setCellValue("");
//检验结论实测数据(m-1)
sheet.getRow(20).getCell(1).setCellValue("");
//检验结论标准要求(m-1)
sheet.getRow(20).getCell(4).setCellValue("");
//检验结论备注
sheet.getRow(20).getCell(5).setCellValue("");
//签发日期
String timeDate =dfDate.format(new Date());
sheet.getRow(23).getCell(5).setCellValue(timeDate);setCellValue("")可以不用设置
六、保存文件
// 保存文件的路径
String realPath = PathUtil.getTestMechanicalExcelPath(userId, id);
String newFileName = "检测数据-" +
testMechanicalInf.getUnitName() +
"-" +testMechanicalInf.getUniqueNumber() +
"-" + dateTime + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
//修改模板内容导出新模板
FileOutputStream out = new FileOutputStream(realPath + newFileName);
wb.write(out);
out.close();
/**
* 存储路径
*/
public static String getTestMechanicalExcelPath(long unitId, Long id) {
StringBuilder firmImagePathBuilder = new StringBuilder();
firmImagePathBuilder.append("/testMechanicalExcel/");
firmImagePathBuilder.append(unitId);
firmImagePathBuilder.append("/");
firmImagePathBuilder.append(id);
firmImagePathBuilder.append("/");
//分隔符使用系统分隔符替换
String firmImagePath = firmImagePathBuilder.toString().replace("/",
seperator);
return firmImagePath;
}七、将文件路径返回给前端，直接给前端一个文件的url链接，让他自己location.href跳转就可以拿到文件了
map.put("url",realPath+"/"+newFileName);
或者也可以使用response返回
//返回文件给前端
FileUtil.downloadFiles(response, realPath+newFileName);
public void downloadFiles(HttpServletRequest request,HttpServletResponse response,
String filePath) {
response.setContentType("application/octet-stream");
response.setCharacterEncoding("UTF-8");
FileInputStream fs = null;
BufferedInputStream buff = null;
OutputStream myout = null;
try {
File file = new File(filePath.trim());
if (file.exists()) {
String fileName = file.getName();
fs = new FileInputStream(file);
String agent = request.getHeader("USER-AGENT");
if(agent != null && agent.toLowerCase().indexOf("firefox") > 0)
{
fileName = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?=";
} else {
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
buff = new BufferedInputStream(fs);
byte[] b = new byte[1024];
long k = 0;
myout = response.getOutputStream();
while (k < file.length()) {
int j = buff.read(b, 0, 1024);
k += j;
myout.write(b, 0, j);
}
buff.close();
} else {
PrintWriter os = response.getWriter();
os.write("文件不存在");
os.close();
}
if (myout != null) {
myout.flush();
myout.close();
}
if (fs != null) {
fs.close();
}
file.delete();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (myout != null) {
try {
myout.flush();
myout.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
---------------长按二维码关注程序媛小姐姐公众号有更多彩蛋哦---------------
1653
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
