直接附上代码
根据Springboot框架简单封装了一下,进行数据处理,正常情况下工具类可以直接使用,需要修改Controller的获取数据办法即可。
1、pom.xml引入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15-beta2</version>
</dependency>
2、创建数据实体类 (注明:一定要有实体类,Excel工具类会通过实体类自动生成每列信息)
public class ErpItem {
private String orgCode;
private String itemNum;
private String itemDesc;
private String itemUom;
private String itemType;
private String isActive;
public ErpItem(){
}
public ErpItem(String orgCode, String itemNum, String itemDesc, String itemUom, String itemType, String isActive) {
this.orgCode = orgCode;
this.itemNum = itemNum;
this.itemDesc = itemDesc;
this.itemUom = itemUom;
this.itemType = itemType;
this.isActive = isActive;
}
public String getOrgCode() {
return orgCode;
}
public void setOrgCode(String orgCode) {
this.orgCode = orgCode;
}
public String getItemNum() {
return itemNum;
}
public void setItemNum(String itemNum) {
this.itemNum = itemNum;
}
public String getItemDesc() {
return itemDesc;
}
public void setItemDesc(String itemDesc) {
this.itemDesc = itemDesc;
}
public String getItemUom() {
return itemUom;
}
public void setItemUom(String itemUom) {
this.itemUom = itemUom;
}
public String getItemType() {
return itemType;
}
public void setItemType(String itemType) {
this.itemType = itemType;
}
public String getIsActive() {
return isActive;
}
public void setIsActive(String isActive) {
this.isActive = isActive;
}
@Override
public String toString() {
return "ErpItem{" +
"orgCode='" + orgCode + '\'' +
", itemNum='" + itemNum + '\'' +
", itemDesc='" + itemDesc + '\'' +
", itemUom='" + itemUom + '\'' +
", itemType='" + itemType + '\'' +
", isActive='" + isActive + '\'' +
'}';
}
2、通常我们回来网页点击下载,浏览器弹出弹窗,然后我们选择路径保存,前端请求控制代码如下:
@RequestMapping("overdueMaterial")
public boolean overdueMaterial(HttpServletResponse response){
boolean flag = false;
//报表文件名称
String fileName = "物料信息";
//设置标题 Excel每列的名称
List listTitle = new ArrayList();
listTitle.add("组织");
listTitle.add("物料编码");
listTitle.add("物料描述");
listTitle.add("单位");
listTitle.add("物料模板");
listTitle.add("是否有效");
//设置报表内容
//List<Student> listContent = OutExcelUtil.getStudent();
//erpItemService.getErpItems() 该方法即为在mybatis执行sql获取要输出的数据内容
List<ErpItem> listItem = erpItemService.getErpItems();
//生成并输出报表 参数依次:文件名称(String) 响应内容(HttpServletResponse) 报表题头(List) 报表内容List<T>
OutExcelUtil.setExcelProperty(fileName, response, listTitle, listItem);
}
3、工具类 (具体表格样式可以根据注释处自行调整)
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class OutExcelUtil {
public static <T> void setExcelProperty(String fileNameParam, HttpServletResponse response, List listTitle, List<T> listContent){
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
OutputStream os = null;
XSSFWorkbook xWorkbook = null;
try {
//定义表格导出时默认文件名 时间戳
//String fileName = df.format(new Date()) + ".xlsx";
String fileName = fileNameParam + ".xlsx";
os = response.getOutputStream();
response.reset();
//作用:在前端作用显示为调用浏览器下载弹窗
response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode(fileName, "UTF-8"));
/*response.setHeader("Content-disposition", "attachment; filename = " + new String(fileName.getBytes(fileName), "ISO8859-1"));*/
response.setContentType("application/octet-streem");
//创建表格工作空间
xWorkbook = new XSSFWorkbook();
//创建一个新表格
XSSFSheet xSheet = xWorkbook.createSheet(fileNameParam);
//set Sheet页头部
setSheetHeader(xWorkbook, xSheet, listTitle);
//set Sheet页内容
setSheetContent(xWorkbook, xSheet, listContent);
xWorkbook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != os) {
try {
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != xWorkbook) {
try {
xWorkbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 配置Excel表格的顶部信息,如:学号 姓名 年龄 出生年月
* @param xWorkbook
* @param xSheet
*/
private static void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet, List listTitle) {
//设置表格的宽度 xSheet.setColumnWidth(0, 20 * 256); 中的数字 20 自行设置为自己适用的
/*xSheet.setColumnWidth(0, 20 * 256);
xSheet.setColumnWidth(1, 15 * 256);
xSheet.setColumnWidth(2, 15 * 256);
xSheet.setColumnWidth(3, 20 * 256);*/
//创建表格的样式
CellStyle cs = xWorkbook.createCellStyle();
//设置水平、垂直居中
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置字体
Font headerFont = xWorkbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
/*headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);*/
headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("宋体");
cs.setFont(headerFont);
cs.setWrapText(true);//是否自动换行
//创建一行
XSSFRow xRow0 = xSheet.createRow(0);
for(int i = 0; i < listTitle.size(); i++){
//设置每一列
XSSFCell xCell0 = xRow0.createCell(i);
xCell0.setCellStyle(cs);
xCell0.setCellValue(listTitle.get(i).toString());
}
}
/**
* 配置(赋值)表格内容部分
* @param xWorkbook
* @param xSheet
* @param listContent
* @throws Exception
*/
private static <T> void setSheetContent(XSSFWorkbook xWorkbook, XSSFSheet xSheet, List<T> listContent) throws Exception {
//创建内容样式(头部以下的样式)
CellStyle cs = xWorkbook.createCellStyle();
cs.setWrapText(true);
//设置水平垂直居中
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
if (null != listContent && listContent.size() > 0) {
try {
for (int i = 0; i < listContent.size(); i++) {
XSSFRow xRow = xSheet.createRow(i + 1);
//获取类属性
Field[] fields = listContent.get(i).getClass().getDeclaredFields();
Method method;
for (int j = 0; j < fields.length; j++){
String str = fields[j].getName();
System.out.println(str);
//获取完成get方法 首字母大写如:getId
method = listContent.get(i).getClass().getMethod("get" + str.substring(0,1).toUpperCase() + str.substring(1));
String value = (String) method.invoke(listContent.get(i));
System.out.println("value" + value);
//循环设置每列的值
XSSFCell xCell = xRow.createCell(j);
xCell.setCellStyle(cs);
xCell.setCellValue(value);
}
}
}catch (NoSuchMethodException e){
System.out.println("检查类set、get命名是否正确:" + e.getStackTrace());
}catch (InvocationTargetException e){
System.out.println(e.getMessage());
}catch (IllegalAccessException e){
System.out.println(e.getMessage());
}
}
}
}
实现效果为浏览器弹窗保存文件: