环境：JDK1.8、Maven3.6.3、Mysql8.0、poi4.1、Mybatis-plus3.4.3
话不多说，先上代码。后续需要自己整理一下分层，保持代码的干净整洁从自己做起。
Controller:如下`
package com.example.testexcel.controller;
import com.example.testexcel.dao.userDao;
import com.example.testexcel.entity.client;
import com.example.testexcel.util.ResponseUtil;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
@RestController
public class excelController {
@Autowired
userDao userdao;
@RequestMapping("excel_down")
public String Excel_Down(HttpServletResponse response){
String table_url = this.getClass().getClassLoader().
getResource("static/webapp/client_table.xls").getPath();
// System.out.println(table_url);
List list = userdao.selectList(null);
Workbook workbook = fillExcelDataWithTemplate(list,table_url);
try {
ResponseUtil.export(response,workbook,"客户表.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
return "成功";
}
private Workbook fillExcelDataWithTemplate(List list, String templateUrl) {
Workbook wb = null;
try {
POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(templateUrl));
wb = new HSSFWorkbook(fileSystem);
//wb = new XSSFWorkbook(String.valueOf(fileSystem));
//取得模板的第一个首页
Sheet sheet = wb.getSheetAt(0);
//拿到sheet有多少列
//int cellNums = sheet.getRow(0).getLastCellNum();
//从第二行开始，下标是1
int rowIndex = 1;
Row row;
for(client c : list){
row = sheet.createRow(rowIndex);
rowIndex++;
row.createCell(0).setCellValue(c.getId());
row.createCell(1).setCellValue(c.getNumber());
row.createCell(2).setCellValue(c.getUsername());
row.createCell(3).setCellValue(c.getPhone());
row.createCell(4).setCellValue(c.getNote());
row.createCell(5).setCellValue(DateFormatUtils.format(c.getCreattime(),"yyyy-MM-dd HH:mm:ss"));
}
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
}
DAO:
package com.example.testexcel.dao; import com.baomidou.mybatisplus.core.mapper.baseMapper; import com.example.testexcel.entity.client; import org.apache.ibatis.annotations.Mapper; @Mapper public interface userDao extends baseMapper{ }
entity:
package com.example.testexcel.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;
@Data
@TableName(value = "sys_excel_test")
public class client {
private Integer id;
//@ExcelColumn(value="编号",col = 2)
private String number;
private String username;
private String phone;
private String note;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date creattime;
}
util:
package com.example.testexcel.util;
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
public class ResponseUtil {
public static void write(HttpServletResponse response,Object o) throws Exception{
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.println(o.toString());
out.flush();
out.close();
}
public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}
Pom:
4.0.0 org.springframework.boot spring-boot-starter-parent 2.6.2 com.example testexcel 0.0.1-SNAPSHOT testexcel Demo project for Spring Boot 1.8 org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test org.apache.poi poi 4.1.0 org.apache.poi poi-ooxml 4.1.0 org.apache.poi poi-ooxml-schemas 4.1.0 org.apache.commons commons-lang3 3.9 com.alibaba druid-spring-boot-starter 1.2.8 mysql mysql-connector-java runtime org.projectlombok lombok org.springframework.boot spring-boot-starter-thymeleaf mysql mysql-connector-java runtime com.baomidou mybatis-plus-boot-starter 3.4.3.4 org.springframework.boot spring-boot-maven-plugin
到这里为止直接启动Application即可。在浏览器里面访问http://localhost:8080/excel_down这个地址就可以下载出来了。
不过有个小问题，如果我们在实际应用中出现一种情况–即客户想使用自己上传的模板进行导出数据呢。
这里要注意一个点，就是客户传进来的模板表是xls还是xlsx，一个代表2003一个代表2007版，这里容易出现一个问题，就是版本问题无法识别。错误如下：
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
我在CSDN上找到一个觉得挺有帮助的一段话。
所以我们在做这个功能的时候，应该先加一个判断判断所选择的模板是什么类型，再决定调用哪个方法。
以下是使用xlsx模板的代码（和上面代码是同一个项目结构，放在一起的）。没有和上面整合，下面是在代码里面直接写死了下载的位置，执行即直接下载到该位置。可以自己改成自己的位置。一般写桌面位置。
util包下Xlsx类：
package com.example.testexcel.util;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Xlsx {
//标题，内容，下表名
public XSSFWorkbook getWorkBook(List titleList, List> contentlist, String sheetName) {
XSSFWorkbook xwk = new XSSFWorkbook();
XSSFDataFormat format = xwk.createDataFormat();
XSSFCellStyle cellStyle = xwk.createCellStyle();
XSSFSheet xssfSheet = xwk.createSheet(sheetName);
cellStyle.setDataFormat(format.getFormat("@"));//文本格式
int j = 0;
createHeader(xssfSheet, cellStyle, titleList, j);
int size = contentlist.size();
for (j = 0; j < size; j++) {
List oneRow = contentlist.get(j);
createContent(xssfSheet, cellStyle, oneRow, j);
oneRow = null;
}
return xwk;
}
private void createHeader(XSSFSheet xssfSheet, XSSFCellStyle cellStyle, List titleList, int j) {
XSSFRow rowTitle = xssfSheet.createRow(j);
for (int cellTitle = 0; cellTitle < titleList.size(); cellTitle++) {
Cell cellIndex = rowTitle.createCell(cellTitle);
cellIndex.setCellStyle(cellStyle);
cellIndex.setCellValue(titleList.get(cellTitle));
}
}
private void createContent(XSSFSheet xssfSheet, XSSFCellStyle cellStyle, List oneRow, int j) {
XSSFRow rowContent = xssfSheet.createRow(j + 1);
for (int cellContent = 0; cellContent < oneRow.size(); cellContent++) {
Cell cellIndex = rowContent.createCell(cellContent);
cellIndex.setCellStyle(cellStyle);
cellIndex.setCellValue(oneRow.get(cellContent));
}
}
}
测试类：
package com.example.testexcel;
import com.example.testexcel.util.Xlsx;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.io.FileNotFoundException;
import java.util.List;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@SpringBootTest
class TestexcelApplicationTests {
@Test
void contextLoads() throws IOException {
List titleList = new ArrayList();
titleList.add("NO");
titleList.add("ID");
titleList.add("Name");
titleList.add("Age");
List content = null;
List> contentsList = new ArrayList>();
content = new ArrayList();
content.add("1");
content.add("180001");
content.add("Jame");
content.add("18");
contentsList.add(content);
content = new ArrayList();
content.add("1");
content.add("180002");
content.add("Lucky");
content.add("18");
contentsList.add(content);
XSSFWorkbook workBook = null;
FileOutputStream output = null;
String sheetName = "student";
String fileName = "D:/student.xlsx";
if (contentsList.size() > 0) {
try {
Xlsx eu = new Xlsx();
workBook = eu.getWorkBook(titleList, contentsList, sheetName);
output = new FileOutputStream(fileName);
workBook.write(output);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally{
if(output != null){
output.flush();
output.close();
}
if (workBook != null) {
workBook.close();
}
}
}
}
}
判断的话，之前看到过一个好的判断，没找到了，是直接判断表类型的。
先用着下面这个叭。
//获得文件名 String fileName =file.getOriginalFilename();
//判断文件是否是excel文件 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx))
