个人博客导航页(点击右侧链接即可打开个人博客):大牛带你入门技术栈
1. 模版文件在 resources 下:
2. 操作Excel并下载
private static final String STUDENT_ANSWER_EXCEL_FIEL = "template/templateStudentAnswerOfExamTable.xlsx";
@Override
public void exportStudentAnswerToExcelByExamId(HttpServletResponse response, long examId) {
// 1. 获取Excel模版文件
// 2. 获取要插入的数据list集合
// 3. 将数据插入到Excel模版中
// 4. 导出操作后到Excel文件
String fileName = "studentAnswerOfExam";
File newFile = createNewFile(STUDENT_ANSWER_EXCEL_FIEL);
// 新文件写入数据,并下载*****************************************************
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook,
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
logger.error("export budget.details.table error" + e1);
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
StudentExamResultBO studentExamResult = examService.getStudentExamResult(examId);
setData(sheet, studentExamResult);
// ===
workbook.write(fos);
fos.flush();
fos.close();
// 下载
InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.setContentType("text/html;charset=UTF-8");
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/x-msdownload");
String newName = URLEncoder.encode(fileName + System.currentTimeMillis() + ".xlsx", "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=\"" + newName + "\"");
response.addHeader("Content-Length", "" + newFile.length());
toClient.write(buffer);
toClient.flush();
} catch (Exception e) {
logger.error("error:", e);
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
logger.error("error:", e);
}
}
}
// 删除创建的新文件
this.deleteFile(newFile);
}
/**
* 读取excel模板,并复制到新文件中供写入和下载
*
* @return
*/
public File createNewFile(String templatePath) {
// 读取模板,并赋值到新文件************************************************************
// 文件模板路径
String path = (getSispPath() + templatePath);
File file = new File(path);
// 保存文件的路径
String realPath = (getSispPath() + "template");
// 新的文件名
String newFileName = System.currentTimeMillis() + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
logger.error("error:", e);
}
return newFile;
}
/**
* 下载成功后删除
*
* @param files
*/
private void deleteFile(File... files) {
for (File file : files) {
if (file.exists()) {
file.delete();
}
}
}
private String getSispPath() {
String classPaths = this.getClass().getResource("/").getPath();
return classPaths;
}
/**
* 复制文件
*
* @param s 源文件
* @param t 复制到的新文件
*/
public void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
logger.error("error:", e);
}
}
private void setData(XSSFSheet sheet, StudentExamResultBO studentExamResult) {
XSSFRow row0 = sheet.getRow(0);
if (null == row0) {
row0 = sheet.createRow(0);
}
XSSFCell examName = row0.getCell(1);
if (null == examName) {
examName = row0.createCell(1);
}
examName.setCellValue(studentExamResult.getName());
}
2. 模版直接下载
/**
* 下载导入模板
* @param request
* @param response
*/
@GetMapping("downloadTemplate")
public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
InputStream in = null;
ServletOutputStream out = null;
String fileName = "导入模板.xlsx";
try {
in = this.getClass().getClassLoader().getResourceAsStream("import_template/template.xlsx");
response.setContentType("application/octet-stream");
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
out = response.getOutputStream();
int b = 0;
byte[] buffer = new byte[1024];
while ((b = in.read(buffer)) != -1) {
out.write(buffer, 0, b);
}
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(out);
IOUtils.closeQuietly(in);
}
}
附Java/C/C++/机器学习/算法与数据结构/前端/安卓/Python/程序员必读/书籍书单大全:
(点击右侧 即可打开个人博客内有干货):技术干货小栈
=====>>①【Java大牛带你入门到进阶之路】<<====
=====>>②【算法数据结构+acm大牛带你入门到进阶之路】<<===
=====>>③【数据库大牛带你入门到进阶之路】<<=====
=====>>④【Web前端大牛带你入门到进阶之路】<<====
=====>>⑤【机器学习和python大牛带你入门到进阶之路】<<====
=====>>⑥【架构师大牛带你入门到进阶之路】<<=====
=====>>⑦【C++大牛带你入门到进阶之路】<<====
=====>>⑧【ios大牛带你入门到进阶之路】<<====
=====>>⑨【Web安全大牛带你入门到进阶之路】<<=====
=====>>⑩【Linux和操作系统大牛带你入门到进阶之路】<<=====天下没有不劳而获的果实,望各位年轻的朋友,想学技术的朋友,在决心扎入技术道路的路上披荆斩棘,把书弄懂了,再去敲代码,把原理弄懂了,再去实践,将会带给你的人生,你的工作,你的未来一个美梦。