话不多说,直接上代码:
FileInputStream in = null;
OutputStream out = null;
XSSFWorkbook wb = null;
try {
HttpServletRequest request = this.getHttpRequest();
String path = ContextUtil.getAbsolutePath() + "\\download\\导入模板.xlsx"; //excel文件路径
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 取得文件的后缀名。
String userAgent = request.getHeader("USER-AGENT");
filename = responseFileName(filename, userAgent);//解决浏览器问题
// 以流的形式下载文件。
HttpServletResponse response = this.getHttpResponse();
response.reset();
response.setContentType(request.getSession().getServletContext().getMimeType(filename));
response.setHeader("Content-Disposition", "attachment;filename="+filename);
//往excel中追加数据核心代码
in = new FileInputStream(file);
wb = ExportExcel.exportBIMInfoToExcel(in,list,project,unitproject); //根据需求添加相关参数,这里list、project、unitproject都是之前处理好的数据,不重要,故不详述。
out = response.getOutputStream();
out.flush();
wb.write(out);
}catch (Exception ex) {
ex.printStackTrace();
}
finally{
//用完之后记得关闭;
if(in != null){
try{
in.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
if(out != null){
try{
out.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}
}
ExportExcel.java文件:
public class ExportExcel {
public static XSSFWorkbook exportBIMInfoToExcel( FileInputStream in, List<Object[]> list, String project, String unitproject) throws IOException {
XSSFWorkbook wb=new XSSFWorkbook(in);
XSSFSheet sheet=wb.getSheetAt(0); //获取到工作表,因为一个excel可能有多个工作表
XSSFRow row;
//根据相关需求,添加数据
for(int i = 0;i<list.size();i++) {
row=sheet.createRow(i+1); //在现有行号后追加数据,我的excel模板中只保留了标题,所以从第一行开始追加数据,如果不是第一行,可以先获取sheet表的最后一行,sheet.getLastRowNum(),获取之后,再追加
Object[] o = list.get(i);
//给需要添加数据的列赋值;这里因为我知道列数,所以直接使用了列号
row.createCell(0).setCellValue(o[0].toString()); //设置第一个(从0开始)单元格的数据
row.createCell(1).setCellValue(o[1].toString()); //设置第二个(从0开始)单元格的数据
row.createCell(2).setCellValue(o[2].toString());
row.createCell(4).setCellValue(project);
row.createCell(5).setCellValue(unitproject);
row.createCell(16).setCellValue(o[3].toString());
row.createCell(17).setCellValue(o[4].toString());
}
return wb;
}
}
处理不同浏览器问题
public static String responseFileName(String fileName, String agent) {
try {
if (null != agent) {
if (-1 != agent.indexOf("MSIE") || -1 != agent.indexOf("Trident") || -1 != agent.indexOf("Edge")) {// ie
fileName = URLEncoder.encode(fileName, "UTF-8");
} else if (-1 != agent.indexOf("Mozilla")) {// 火狐,chrome等
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
}
} catch (IOException e) {
}
return fileName;
}
参考:
https://blog.csdn.net/anlian523/article/details/72268347