介绍两种方法
一、导出到服务器本地,返回下载地址到前台让用户自己下载
首先定义几个常量
public static final String EXPORT_EXCEL_FILE_PATH="D:/files/";//生成的Excel文件存放的磁盘路径
public static final String EXPORT_EXCEL_FILE_SUBFIX=".xls";//文件后缀
public static final String EXPORT_EXCEL_FILE_MAPPING="excel/";//磁盘映射路径
上面是定义在代码里,不容易修改,也可以定义在配置文件里。如下
export:
#生成的Excel文件存放的磁盘路径
file-path:D:/files/
#文件后缀
subfix:.xls
- 磁盘映射配置类
@Component
public class WebMvcConfiguration implements WebMvcConfigurer {
/**
* 虚拟路径配置
* @param registryd
*/
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler("/files/**").addResourceLocations("file:/d:/files/");
WebMvcConfigurer.super.addResourceHandlers(registry);
}
}
创建生成excel工具类
package com.common.utils;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
/**
*
* @param titleList 标题名称list
* @param zdlist 字段list
* @param dataList 数据list
* @return
*/
public class CreatExcelFile {
//以下常量均可在application.yml里配置,这里为了测试方便写成了常量。建议在配置文件做配置,方便修改
public static final String bathPath = "http://localhost:8080/";
public static final String EXPORT_EXCEL_FILE_PATH = "D:/files/";//生成的Excel文件存放的磁盘路径
public static final String EXPORT_EXCEL_FILE_SUBFIX = ".xls";//文件后缀
public static final String EXPORT_EXCEL_FILE_MAPPING = "excel/";//磁盘映射路径
public static String creatFile(List<String> titleList, List<String> zdlist, JSONArray dataList){
String downloadxls = "";
try {
//创建HSSFWorkbook对象,excel文档对象
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet表
HSSFSheet sheet = wb.createSheet("sheet1");
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个数字
HSSFRow row0 = sheet.createRow(0);
//添加表头
for (int i = 0;i < titleList.size();i++){
row0.createCell(i).setCellValue(titleList.get(i));
}
//添加表中内容
for (int row = 0; row < dataList.size();row++){
//创建新行
HSSFRow newrow = sheet.createRow(row);
//获取该行的数据
Map<String,Object> map = (Map<String, Object>) dataList.get(row);
for (int col = 0;col < zdlist.size();col++){
//数据从第一列开始
//创建单元格并且放入数据
newrow.createCell(col).setCellValue(map!=null&&map.get(zdlist.get(col))!=null?String.valueOf(map.get(zdlist.get(col))):"");
}
}
//判断是否存在目录,不存在则创建
isChartPathExist(EXPORT_EXCEL_FILE_PATH);
FileOutputStream output = new FileOutputStream(EXPORT_EXCEL_FILE_PATH+"test"+EXPORT_EXCEL_FILE_SUBFIX);
wb.write(output);
output.close();
downloadxls = bathPath+EXPORT_EXCEL_FILE_MAPPING+EXPORT_EXCEL_FILE_PATH+"test"+EXPORT_EXCEL_FILE_SUBFIX;//http://localhost:8080/excel/text.xls 返回给前端一个下载地址即可。
}catch (Exception e){
downloadxls = "";
e.printStackTrace();
}
return downloadxls;
}
/**
* 判断文件夹是否存在,如果不存在则创建一个
* @param dirPath
*/
public static void isChartPathExist(String dirPath){
File file = new File(dirPath);
if(!file.exists()){
file.mkdirs();
}
}
}
二、直接输出到浏览器并下载
package com.common.utils;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
/**
*
*@param filename 输出的文件名称
* @param titleList 标题名称list
* @param zdlist 字段list
* @param dataList 数据list
* @return
*/
public class CreatExcelFile {
public static String creatFile(HttpServletRequest request,HttpServletResponse response,String filename,List<String> titleList, List<String> zdlist, JSONArray dataList){
request.setCharacterEncoding('UTF-8');
response.setCharacterEncoding('UTF-8');
response.setContentType(MdiaType.APPLICATION_OCTET_STREAM_VALUE);
response.addHeader("Content-disposition","attachment;filename="+URLEncoding.encode(filename,"UTF-8"));
response.setHeader("Cache-Control","No-cache");
response.flushBuffer();
try {
//创建HSSFWorkbook对象,excel文档对象
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet表
HSSFSheet sheet = wb.createSheet("sheet1");
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个数字
HSSFRow row0 = sheet.createRow(0);
//添加表头
for (int i = 0;i < titleList.size();i++){
row0.createCell(i).setCellValue(titleList.get(i));
}
//添加表中内容
for (int row = 0; row < dataList.size();row++){
//创建新行
HSSFRow newrow = sheet.createRow(row);
//获取该行的数据
Map<String,Object> map = (Map<String, Object>) dataList.get(row);
for (int col = 0;col < zdlist.size();col++){
//数据从第一列开始
//创建单元格并且放入数据
newrow.createCell(col).setCellValue(map!=null&&map.get(zdlist.get(col))!=null?String.valueOf(map.get(zdlist.get(col))):"");
}
}
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}catch (Exception e){
e.printStackTrace();
throw new IOException("导出异常!!!")
}
return downloadxls;
}
}
程序中在try/chatch中直接调用此方法即可,直接输出到浏览器下载列表了。
纯手敲的,加深记忆。