前几周，划水划的正嗨，客户突然说要加个数据批量导出成excel表格，二话不说，为了不影响我的快乐时光，只能动起脑子动起手~ 这次也是写一个excel表格导出详细教程的博客用来巩固，俗话说的好：好记性不如烂笔头
前端使用的是layui框架，后台是springboot框架，部署在阿里云linux服务器上。
导入maven 依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-examples</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>4.1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>4.1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-math3 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-math3</artifactId> <version>3.6.1</version> </dependency>1.首先创建工作簿，相当于整体excel文件,是整个文件的骨架
private HSSFWorkbook workbook = new HSSFWorkbook();2.再通过workbook 对象 创建sheet对象
private HSSFSheet sheet = workbook.createSheet();对应于excel表格的工作空间 3.接下来就到数据单元的绘制 可以分为两个部分： 表头：数据是固定的 列数据：是动态数据 每个数据都由一个单元格组成，一行可以有多个单元格
controller层代码
@PostMapping("/export") public Object export(@RequestParam String appointment){ HashMap result = new HashMap(); try { ObjectMapper mapper = new ObjectMapper(); List list = mapper.readValue(appointment,List.class); String fileName = new ExcelUtil().export(list, mapper); if (!fileName.isEmpty()) { //必须将路径返回给前端，才能配合前端下载到本地 result.put("msg","excel/"+fileName); result.put("code", 200); result.put("desc", "导出成功"); return result; } } catch (Exception e) { logger.error(e.toString()); } result.put("code", 500); result.put("desc", "导出失败"); return result; }业务层代码
package com.hniu.zs_manage.utils; import com.fasterxml.jackson.databind.ObjectMapper; import com.hniu.zs_manage.entity.appointment.Appointment; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.slf4j.Logger; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Component; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.*; /** * @Description: excel 导出工具类 * @Author: songbiao */ @Component public class ExcelUtil { private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"}; private static FileOutputStream FILE_OUTPUT_STREAM = null; private static Logger logger = LogUtil.getLOG(ExcelUtil.class); private static final File FILE_URL = new File("/cbim/ManagerSystem/excel"); private static String fileName = ""; private HSSFWorkbook workbook = new HSSFWorkbook(); private HSSFSheet sheet = workbook.createSheet(); private static HSSFFont headFont = null; private static HSSFCellStyle headStyle = null; private static HSSFFont dataFont = null; private static HSSFCellStyle dataStyle = null; private static HSSFRow headRow = null; public ExcelUtil() { //头字体样式 HSSFFont headFont = workbook.createFont(); headFont.setFontName("宋体"); headFont.setFontHeightInPoints((short) 18); //列头 HSSFCellStyle headStyle = workbook.createCellStyle(); headStyle.setFont(headFont); // headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); //新版本FillPatternType headStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headStyle.setAlignment(HorizontalAlignment.CENTER); headStyle.setVerticalAlignment(VerticalAlignment.CENTER); //数字字体样式 HSSFFont dataFont = workbook.createFont(); dataFont.setFontName("宋体"); dataFont.setFontHeightInPoints((short) 16); //数据样式 HSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setFont(dataFont); dataStyle.setAlignment(HorizontalAlignment.LEFT); dataStyle.setVerticalAlignment(VerticalAlignment.CENTER); //===========表头操作===================== for (int i = 0; i < HEAD_NAME.length; i++) { //设置宽度 sheet.setColumnWidth(i,4000); if (i == 0) { //创建一行表头 headRow = sheet.createRow(i); headRow.setHeight((short) 800); } //创建单元格数据 HSSFCell cell = headRow.createCell(i); //给单元格赋值 cell.setCellValue(HEAD_NAME[i]); } } public String export(List appointment,ObjectMapper objectMapper) throws IOException { FILE_OUTPUT_STREAM = new FileOutputStream(fileCheck(FILE_URL)); //===========数据操作===================== for (int i = 0; i < appointment.size(); i++) { HSSFRow row = sheet.createRow(i+1); //为每个数据创建一个单元格 Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class); HSSFCell cell = row.createCell(0); cell.setCellStyle(dataStyle); cell.setCellValue(value.getAId()); HSSFCell cell2 = row.createCell(1); cell2.setCellStyle(dataStyle); cell2.setCellValue(value.getName()); HSSFCell cell3 = row.createCell(2); cell3.setCellStyle(dataStyle); cell3.setCellValue(value.getCompany()); HSSFCell cell4 = row.createCell(3); cell4.setCellStyle(dataStyle); cell4.setCellValue(value.getPost()); HSSFCell cell5 = row.createCell(4); cell5.setCellStyle(dataStyle); cell5.setCellValue(value.getPhone()); HSSFCell cell6 = row.createCell(5); cell6.setCellStyle(dataStyle); cell6.setCellValue(value.getEmail()); HSSFCell cell7 = row.createCell(6); cell7.setCellStyle(dataStyle); cell7.setCellValue(value.getMessage()); } workbook.write(FILE_OUTPUT_STREAM); FILE_OUTPUT_STREAM.flush(); FILE_OUTPUT_STREAM.close(); workbook.close(); logger.debug("文件流读写关闭成功...."); return fileName; } public static File fileCheck(File file) throws IOException { if (!file.getPath().endsWith(".xls")) { if (!file.exists()) { file.mkdirs(); } fileName = "cbim_" + System.currentTimeMillis() + ".xls"; file = new File(file.getPath(), fileName); file.createNewFile(); } else if (!file.exists()) { //是文件且路径不对时直接返回异常信息 throw new IllegalStateException("路径异常"); } return file; } }因为项目是部署在linux服务器上，所以通过流是无法写到客户端的。 我的思路是将文件写在服务器上，通过nginx 搭建文件服务器来实现下载到本地 没有学习过nginx的同学赶紧度娘，nginx十分重要！！！
在nginx.conf下配置（找到自己对应的nginx配置文件）
location /myfiles { alias /export/share/test/; # 文件存放目录，注意要以 '/' 结尾； autoindex on; # 自动列出目录下的文件； autoindex_exact_size off; # 文件大小按 G、M 的格式显示，而不是 Bytes； }配置成功如下: 点击文件即可下载，或者通过http请求在myfiles后加上该文件名
发送ajax请求controller得到的文件路径，再进行http请求
success:function (res) { if (res.code == 200) { //下载到本地 window.location.href(res.msg); } alert(res.desc); layer.close(layer.index); }最终结果演示:
