前段时间公司项目需要实现一个在web端将查询到的数据导出Excel表格并下载的功能,于是,便有了这篇文章,嘻嘻~
demo环境:idea,java8,springboot,maven
首先,当然是最基本的导包啦:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
导包成功后,先写一个ExcelUtil工具类,这个工具类里面有两个方法,第一个主要是用来创建Excel文档,并且定义一些行宽列宽,字体大小,单元格格式等属性:
/**
* 创建excel文档
*
* @param list
* @param keys 列名称集合
* @param columnNames 列名
* @return
*/
public static HSSFWorkbook createWorkBook(List<Map<String, Object>> list, String[] keys, String[] columnNames) {
// 1. 创建excel工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// 2. 创建第一个sheet页,并命名
HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 3. 设置每列的宽
for (int i = 0; i < keys.length; i++) {
sheet.setColumnWidth((short) i, (short) (50 * 60));
}
// 4. 创建第一行,设置其单元格格式,并将数据放入
HSSFRow row = sheet.createRow((short) 0);
row.setHeight((short) 500);
// 4.1 设置单元格格式
HSSFCellStyle cs = wb.createCellStyle();
HSSFFont f = wb.createFont();
f.setFontName("宋体");
f.setFontHeightInPoints((short) 10);
f.setBold(true);
cs.setFont(f);
// 水平居中
cs.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
cs.setVerticalAlignment(VerticalAlignment.CENTER);
cs.setLocked(true);
//自动换行
cs.setWrapText(true);
// 4.2 设置列名(取出列名集合进行创建)
for (int i = 0; i < columnNames.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
// 5. 设置首行外,每行每列的值(Row和Cell都从0开始)
for (short i = 1; i < list.size(); i++) {
HSSFRow row1 = sheet.createRow((short) i);
String flag = "";
// 5.1 在Row行创建单元格
for (short j = 0; j < keys.length; j++) {
HSSFCell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
}
// 5.2 设置该行样式
HSSFFont f2 = wb.createFont();
f2.setFontName("宋体");
f2.setFontHeightInPoints((short) 10);
// 5.3 设置单元格样式
HSSFCellStyle cs2 = wb.createCellStyle();
cs2.setFont(f2);
// 左右居中
cs2.setAlignment(HorizontalAlignment.CENTER);
// 上下居中
cs2.setVerticalAlignment(VerticalAlignment.CENTER);
cs2.setLocked(true);
//自动换行
cs2.setWrapText(true);
for (int m = 0; m < keys.length; m++) {
HSSFCell hssfCell = row1.getCell(m);
hssfCell.setCellStyle(cs2);
}
}
return wb;
}
第二个方法主要是用来将数据写入创建好的Excel文档中,并装载缓冲输出流输出文档,供前端下载:
/**
* 生成并下载Excel
*
* @param list
* @param keys
* @param columnNames
* @param fileName
* @param response
* @throws IOException
*/
public static void downloadWorkBook(List<Map<String, Object>> list,
String[] keys,
String[] columnNames,
String fileName,
HttpServletResponse response) throws IOException {
// 1. 声明字节输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
// 2. 生成excel文件并写入输出流
ExcelUtil.createWorkBook(list, keys, columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
// 3. 将输出流转换成byte[] 数组
byte[] content = os.toByteArray();
// 4. 将数组放入输入流中
InputStream is = new ByteArrayInputStream(content);
// 5. 设置response参数
response.reset(); // 重置response的设置
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
// 6. 创建Servlet 输出流对象
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
// 6.1装载缓冲输出流
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// 6.2 输出内容
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
}
}
写完了工具类,那就再写一个student实体类吧,以这个实体类为Excel表格数据的载体(ps:这个实体类用了lombok插件,这个插件可以通过@Data注释,省去了写get(),set()方法的麻烦):
@Data
public class Students {
private String name;
private int years;
private String gender;
private int number;
private String birthday;
}
做完了所有的准备工作,可以开始写service了,service中也有两个方法,第一个方法createExcelRecord是辅助性的,主要是用来创建Excel表中一条一条的数据记录,把这些记录用list和map存起来;第二个方法toExcel是Controller要调用的方法,里面定义了文件名,并且把列名映射出来,最后导出Excel:
@Service
public class ToExcelServiceImpl implements ToExcelService {
public String toExcel(HttpServletResponse response, List<Students> mList) {
try {
//文件名
String fileName="text";
List<Map<String,Object>> list=createExcelRecord(mList);
//列名
String columnNames[] = {"姓名","年龄","性别","学号","生日"};
//map中的key:对应student类中的字段 映射列名
String keys[] = {"name","years","gender","number","birthday"};
//导出Excel表
ExcelUtil.downloadWorkBook(list,keys,columnNames,fileName,response);
} catch (IOException e) {
e.printStackTrace();
}
return "true";
}
/**
* 创建Excel表中的记录
* @param stuList
* @return
*/
private List<Map<String, Object>> createExcelRecord(List<Students> stuList){
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
try {
Map<String, Object> map = new HashMap<String, Object>(16);
//工作表1
map.put("sheetName", "sheet1");
listmap.add(map);
//创建表中数据记录
for (int j = 0; j < stuList.size(); j++) {
Students students=stuList.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>(16);
mapValue.put("name",students.getName());
mapValue.put("years",students.getYears());
mapValue.put("gender", students.getGender());
mapValue.put("number",students.getNumber());
mapValue.put("birthday",students.getBirthday());
listmap.add(mapValue);
}
} catch (Exception e) {
e.printStackTrace();
}
return listmap;
}
}
service写完了,马上要大功告成了,最后一步,写Controller,在Controller中,需要做两个工作:①造数据(ps:造数据过程中生成日期时间用到了hutool工具包,不要忘了导包),②导出Excel表格。
@Controller
public class ToExcelController {
@Autowired
private ToExcelService toExcelService;
@GetMapping("/toExcel")
public void toExcel(HttpServletResponse response){
//造数据
List<Students> mList = new ArrayList<Students>();
for (int i = 0 ; i < 5 ; i++){
Students students = new Students();
students.setName("text"+ i);
students.setGender(i % 2 == 0 ? "男" : "女");
students.setYears(i + 10);
students.setNumber(i + 1);
students.setBirthday(DateUtil.today());
mList.add(students);
}
//导出Excel
toExcelService.toExcel(response,mList);
}
}
好了,万事俱备,只欠东风了,最后测试一下,在浏览器中输入:http://localhost/toExcel
回车,导出数据如下:
大功告成!
如各位需要源代码的话,本人也有提供,以下为
白嫖通道:https://gitee.com/DazhiSYM/toexcel/tree/toexcelv1.0/
若各位看官觉得本人这篇文章写的不错,想要赏小弟几个积分的话,也可以走下面的土豪通道,谢过:
土豪通道:https://download.csdn.net/download/JBQYMKY/12839109