Java中生成excel文件返回数据

1.需求描述

开发中遇到过很多查询界面，有很多的筛选条件，选择或输入筛选条件后，点击查询，后台查询出符合条件的数据，前台做展示。最近遇到新的需求，需要将条件查询的结果做excel导出，即一键查询导出。

2.代码实现

2.1.创建相关的控制类与服务类

UserController

package com.minhai.boot.excelDemo.controller;
import com.minhai.boot.excelDemo.entity.ErrorCode;
import com.minhai.boot.excelDemo.entity.ResponseVO;
import com.minhai.boot.excelDemo.entity.UserInfo;
import com.minhai.boot.excelDemo.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@RestController
@RequestMapping(value = "/user",method = {RequestMethod.GET,RequestMethod.POST})
public class UserController {
private static final Logger LOGGER = LoggerFactory.getLogger(UserController.class);
@Autowired
private UserService userService;
@RequestMapping("downloadExcel")
public ResponseVO downloadUserInfoExcel(UserInfo userInfo, HttpServletResponse response){
LOGGER.info("start UserController downloadUserInfoExcel");
long startTime = System.currentTimeMillis();
HSSFWorkbook excel = userService.downloadUserInfoExcel(userInfo);
try {
ServletOutputStream out = response.getOutputStream();
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xlsx");
excel.write(out);
out.flush();
out.close();
} catch (IOException e) {
return new ResponseVO<>(ErrorCode.ERR906020);
}
LOGGER.info("end UserController downloadUserInfoExcel, timeConsume={}", System.currentTimeMillis() - startTime);
return new ResponseVO<>(ErrorCode.SUCCESS);
}
}

UserService

package com.minhai.boot.excelDemo.service;
import com.minhai.boot.excelDemo.entity.UserInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public interface UserService {
HSSFWorkbook downloadUserInfoExcel(UserInfo user);
}

UserServiceImpl

package com.minhai.boot.excelDemo.service.impl;
import com.minhai.boot.excelDemo.entity.ExcelBean;
import com.minhai.boot.excelDemo.entity.UserInfo;
import com.minhai.boot.excelDemo.mapper.UserInfoMapper;
import com.minhai.boot.excelDemo.service.UserService;
import com.minhai.boot.excelDemo.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
private static final Logger LOGGER = LoggerFactory.getLogger(UserServiceImpl.class);
@Autowired
private UserInfoMapper userInfoMapper;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
@Override
public HSSFWorkbook downloadUserInfoExcel(UserInfo userInfo) {
LOGGER.info("start UserServiceImpl downloadUserInfoExcel,userInfo is [{}]",userInfo);
// 条件查询所有满足条件的用户信息
List<UserInfo> userInfoList = userInfoMapper.selectByCondition(userInfo);
ExcelBean bean = new ExcelBean();
List<Object[]> dat = new ArrayList<>();
Object[] data;
// 设置表头
bean.setTableHeader(new String[]{"序号","用户id","用户名称","密码","用户状态","用户联系人","用户地址","用户电话","用户邮箱","创建时间","修改人","修改时间"});
for(int i = 0 ; i < userInfoList.size(); i++){
UserInfo userInfoSelect = userInfoList.get(i);
data =new Object[]{ i+1, userInfoSelect.getUserId(),userInfoSelect.getUserName(),userInfoSelect.getPwd(),userInfoSelect.getUserStatus(),
userInfoSelect.getContactName(),userInfoSelect.getAddress(),userInfoSelect.getPhone(),userInfoSelect.getMail(),
simpleDateFormat.format(userInfoSelect.getCreateTime()),userInfoSelect.getModifyBy(), simpleDateFormat.format(userInfoSelect.getModifyTime())};
dat.add(data);
}
bean.setSheetData(dat);
Object[] b = { bean };
return ExcelUtil.createExcel(b);
}
}

UserInfoMapper

package com.minhai.boot.excelDemo.mapper;
import com.minhai.boot.excelDemo.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface UserInfoMapper{
List<UserInfo> selectByCondition(UserInfo userInfo);
}

UserInfoMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.minhai.boot.excelDemo.mapper.UserInfoMapper">
<sql id="Base_Column_List">
USER_ID,
USER_NAME,
PWD,
USER_STATUS,
CONTACT_NAME,
ADDRESS,
PHONE,
MAIL,
CREATE_TIME,
MODIFY_BY,
MODIFY_TIME,
DELETED
</sql>
<select id="selectByCondition" resultType="com.minhai.boot.excelDemo.entity.UserInfo">
SELECT
<include refid="Base_Column_List"/>
FROM TD_API_USER
WHERE 1 = 1
<if test="userId != null and userId !=''">
AND USER_ID = #{userId}
</if>
<if test="userName != null and userName !=''">
AND USER_NAME = #{userName}
</if>
<if test="userStatus != null and userStatus !=''">
AND USER_STATUS = #{userStatus}
</if>
<if test="contactName != null and contactName !=''">
AND CONTACT_NAME = #{contactName}
</if>
<if test="address != null and address !=''">
AND ADDRESS = #{address}
</if>
<if test="phone != null and phone !=''">
AND PHONE = #{phone}
</if>
<if test="mail != null and mail !=''">
AND MAIL = #{mail}
</if>
</select>
</mapper>

UserInfo实体类

package com.minhai.boot.excelDemo.entity;
import lombok.*;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
// @Entity
@Table(name = "TD_API_USER")
@Getter
@Setter
@AllArgsConstructor
@Builder
@Data
public class UserInfo implements Serializable {
/**
* serialVersionUID 描述此常量
*/
private static final long serialVersionUID = 5652292724109869806L;
/**
* 主键
*/
@Id
@Column(name = "USER_ID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long userId;
/**
* 用户名称
*/
@Column(name = "USER_NAME")
private String userName;
/**
* 密码
*/
@Column(name = "PWD")
private String pwd;
/**
* 用户状态（0：正常；1：禁用）
*/
@Column(name = "USER_STATUS")
private Integer userStatus;
/**
* 负责人姓名
*/
private String contactName;
/**
* 地址
*/
private String address;
/**
* 联系方式
*/
private String phone;
/**
* 邮箱
*/
private String mail;
/**
* 创建时间
*/
@Column(name = "CREATE_TIME")
private Date createTime;
/**
* 修改人
*/
@Column(name = "MODIFY_BY")
private Long modifyBy;
/**
* 修改时间
*/
@Column(name = "MODIFY_TIME")
private Date modifyTime;
/**
* 逻辑删除标记（0：正常，1：已删除）
*/
@Column(name = "DELETED")
private Integer deleted;
}

2.2.创建相关的excel工具类

ExcelBean实体类

package com.minhai.boot.excelDemo.entity;
import java.io.Serializable;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelBean implements Comparable<ExcelBean> ,Serializable{
private static final long serialVersionUID = 4239102851746458603L;
/**
*
当前sheet的编号,唯一
*/
private int num = 0;
/**
* excel的名字
*/
private String name = "测试excel";
/**
* 当前sheet的名称
*/
private String sheetName = "sheet名称";
/**
* headerCenter
*/
private String headerCenter = "测试headerCenter";
/**
* 当前sheet的表头
*/
private String[] tableHeader = new String[] { "测试数据" };
/**
* 当前sheet的数据
*/
private List<Object[]> sheetData;
/**
* 多个sheet情况下使用，如果只有一个sheet，那么请输入为null
*/
private Map<Integer, ExcelBean> sheets = null;
/**
*
合并单元格的行和列{起始行，列，结束行，列}
*/
private List<Integer[]> colRow;
/**
* 填充数据起始行
*/
private Integer startRow;
/**
* {列宽(50*100),列宽，列宽..}
*/
private Integer[] colWidth;
/**
* 列,宽度
*/
private List<short[]> widthList;
/**
* 字体颜色地图，key： 行_列，value color
*/
private Map<String,Short> fontColorMap = new HashMap<>();
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public String[] getTableHeader() {
return tableHeader;
}
public void setTableHeader(String[] tableHeader) {
this.tableHeader = tableHeader;
}
public Map<Integer, ExcelBean> getSheets() {
return sheets;
}
public void setSheets(Map<Integer, ExcelBean> sheets) {
this.sheets = sheets;
}
public List<Object[]> getSheetData() {
return sheetData;
}
public void setSheetData(List<Object[]> sheetData) {
this.sheetData = sheetData;
}
@Override
public int compareTo(ExcelBean o) {
return num - o.num;
}
public String getHeaderCenter() {
return headerCenter;
}
public void setHeaderCenter(String headerCenter) {
this.headerCenter = headerCenter;
}
public List<Integer[]> getColRow() {
return colRow;
}
public void setColRow(List<Integer[]> colRow) {
this.colRow = colRow;
}
public Integer getStartRow() {
return startRow;
}
public void setStartRow(Integer startRow) {
this.startRow = startRow;
}
public List<short[]> getWidthList() {
return widthList;
}
public void setWidthList(List<short[]> widthList) {
this.widthList = widthList;
}
/** {列宽(50*100),列宽，列宽..}
* @return
*/
public Integer[] getColWidth() {
return colWidth;
}
/** {列宽(50*100),列宽，列宽..}
* @param colWidth
*/
public void setColWidth(Integer[] colWidth) {
this.colWidth = colWidth;
}
public Map<String, Short> getFontColorMap() {
return fontColorMap;
}
public void setFontColorMap(Map<String, Short> fontColorMap) {
this.fontColorMap = fontColorMap;
}
}

ExcelUtil excel生成工具类

package com.minhai.boot.excelDemo.utils;
import com.minhai.boot.excelDemo.entity.ExcelBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.Arrays;
import java.util.List;
public class ExcelUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 构造私有
*/
private ExcelUtil() {}
/**
* 不在服务器上保存文件， 写回xls文件给用户 导出操作
*
* @param response
* @param excelData
* @throws IOException
*/
public static void download(HttpServletResponse response, ExcelBean excelData)
throws IOException {
Object[] datas = null;
String fileName = null;
if (null != excelData.getSheets() && !excelData.getSheets().isEmpty()) {
datas = excelData.getSheets().values().toArray();
Arrays.sort(datas);
} else {
datas = new Object[] { excelData };
}
HSSFWorkbook excel = ExcelUtil.createExcel(datas);
// 生成文件
HSSFSheet sheet = excel.getSheetAt(0);
HSSFFooter footer = sheet.getFooter();
footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
response.setContentType("application/x-msdownload;charset=iso-8859-1");
fileName = new String(excelData.getName().trim().getBytes("GBK"), "iso-8859-1") + ".xls";
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// 不同类型的文件对应不同的MIME类型
OutputStream sops = response.getOutputStream();
excel.write(sops);
sops.flush();
sops.close();
}
/**
* 创建excel
*
* @param datas
* @return
*/
public static HSSFWorkbook createExcel(Object[] datas) {
HSSFWorkbook excel = new HSSFWorkbook();
/**
* 单元格样式
*/
HSSFCellStyle cellStyle = excel.createCellStyle();
HSSFCellStyle cellStyleColor = excel.createCellStyle();
/**
* 字体
*/
Font font = excel.createFont();
/**
* 文本格式
*/
HSSFDataFormat format = excel.createDataFormat();
for (Object o : datas) {
ExcelBean data = (ExcelBean) o;
// 创建sheet
HSSFSheet sheet = excel.createSheet(data.getSheetName());
// 创建表头
HSSFHeader header = sheet.getHeader();
header.setCenter(data.getHeaderCenter());
HSSFRow headerRow = sheet.createRow(0);
// 设置单元格类型，水平布局：居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 垂直布局：居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
for (int i = 0; i < data.getTableHeader().length; i++) {
HSSFCell headerCell = headerRow.createCell(i);
// 设置单元格样式
headerCell.setCellStyle(cellStyle);
headerCell.setCellValue(data.getTableHeader()[i].trim());
}
// 创建数据
int rowIndex = 1;
for (Object[] sheetData : data.getSheetData()) {
HSSFRow row = sheet.createRow(rowIndex);
for (int i = 0; i < sheetData.length; i++) {
// 创建第i个单元格
HSSFCell cell = row.createCell(i);
Short color = data.getFontColorMap().get(rowIndex + "_" + i);
if (color != null) {
// 有颜色的单元格
// 水平布局：居中
cellStyleColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 垂直布局：居中
cellStyleColor.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyleColor.setWrapText(true);
font.setColor(color);
cellStyleColor.setFont(font);
/**
* 设置文本格式
*/
cellStyleColor.setDataFormat(format.getFormat("@"));
// 设置单元格样式
cell.setCellStyle(cellStyleColor);
} else {
// 设置文本格式
cellStyleColor.setDataFormat(format.getFormat("@"));
// 设置单元格样式
cell.setCellStyle(cellStyle);
}
if (sheetData[i] == null) {
cell.setCellValue("");
} else {
cell.setCellValue(sheetData[i] + "");
}
}
rowIndex++;
}
// 合并单元格
if (data.getColRow() != null) {
for (int i = 0; i < data.getColRow().size(); i++) {
Integer[] colr = data.getColRow().get(i);
CellRangeAddress cra = new CellRangeAddress(colr[0], colr[1], colr[2], colr[3]);
// 在sheet里增加合并单元格
sheet.addMergedRegion(cra);
}
}
autoSizeColumn(sheet, data);
}
return excel;
}
/**
* 列度自适应
*
* @param sheet
*/
private static void autoSizeColumn(HSSFSheet sheet, ExcelBean data) {
if (data.getTableHeader() != null && data.getTableHeader().length > 0) {
int length = data.getTableHeader().length - 1;
Integer[] widths = data.getColWidth();
if (widths == null) {
while (length >= 0) {
// 调整列宽度
sheet.autoSizeColumn((short) length);
--length;
}
} else {
while (length >= 0) {
sheet.setColumnWidth(length, widths[length]);
--length;
}
}
}
}
/**
* 控制层跳转错误页面(/500)
*
* @param response
* @param request
* @param toPage
*/
public static void toErrorPage(HttpServletResponse response, HttpServletRequest request, String toPage) {
try {
response.sendRedirect(request.getContextPath() + toPage);
} catch (IOException e) {
log.warn("{}", e);
}
}
/**
* 批量设置单元格格式
*
* @param sheet 表sheet对象
* @param cellstyle 设置的style
* @param startRow 开始的行号
* @param startCell 开始的列号
* @param endRow 结束的行号
* @param endCell 结束的列号 void
*/
public static void setCellStyle(Sheet sheet, CellStyle cellstyle, int startRow, int startCell, int endRow,
int endCell) {
for (int i = startRow; i <= endRow; i++) {
Row row = sheet.getRow(i);
for (int j = startCell; j <= endCell; j++) {
Cell cell = row.getCell(j);
cell.setCellStyle(cellstyle);
}
}
}
/**
* 创建一个Font
*
* @param workbook 表空间
* @param fontName 字体
* @return Font
*/
public static Font createFont(Workbook workbook, String fontName) {
Font font = workbook.createFont();
if (fontName != null) {
font.setFontName(fontName);
}
return font;
}
/**
* 创建一个Font
*
* @param workbook 表空间
* @param fontSize 字体大小
* @return Font
*/
public static Font createFont(Workbook workbook, Short fontSize) {
Font font = workbook.createFont();
if (fontSize != null) {
font.setFontHeightInPoints(fontSize);
}
return font;
}
/**
* 创建一个Font
*
* @param workbook 表空间
* @param fontName 字体
* @param fontSize 字体大小
* @param color 字体颜色
* @return Font
*/
public static Font createFont(Workbook workbook, String fontName, Short fontSize, Short color) {
Font font = workbook.createFont();
if (fontName != null) {
font.setFontName(fontName);
}
if (fontSize != null) {
font.setFontHeightInPoints(fontSize);
}
if (color != null) {
font.setColor(color);
}
return font;
}
/**
* 添加批注
*
* @param workbook
* @param sheet
* @param cell 单元格
* @param text 批注 void
*/
public static void addComment(Workbook workbook, Sheet sheet, Cell cell, String text) {
CreationHelper newFactory = workbook.getCreationHelper();
RichTextString str = newFactory.createRichTextString(text);
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = newFactory.createClientAnchor();
anchor.setCol1(0);
anchor.setCol2(3);
anchor.setRow1(0);
anchor.setRow2(6);
Comment comment = drawing.createCellComment(anchor);
comment.setString(str);
cell.setCellComment(comment);
}
/**
* 返回col 及 width
*
* @param num
* @return
*/
public static Integer[] getColWidth(int num) {
Integer[] ints = new Integer[num];
for (int i = 0; i < num; i++) {
ints[i] = 50 * 110;
}
return ints;
}
/**
* 页面输出信息
*
* @param response
*/
public static void responseMsg(HttpServletResponse response, String msg) {
try {
PrintWriter pw = response.getWriter();
pw.write(msg);
pw.flush();
pw.close();
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
/**
*
* @Title: getExcelBean @Description: 得到头部数据 @param: @param tableHeads @param: @param
*
name @param: @param sheetData @param: @return @return: ExcelBean @author：李杰 @throws
*/
public static ExcelBean getExcelHeadBean(String[] tableHeads, String name, List<Object[]> sheetData) {
ExcelBean excelBean = new ExcelBean();
// 文件名
excelBean.setName(name);
excelBean.setSheetName(name);
excelBean.setHeaderCenter(name + "列表");
excelBean.setTableHeader(tableHeads);
excelBean.setColWidth(ExcelUtil.getColWidth(tableHeads.length));
excelBean.setSheetData(sheetData);
return excelBean;
}
}

3.代码分析

1.此套代码逻辑为先按传入的条件进行查询，对查询的结果进行封装，封装成字符串数组， 然后设置表头（header），设置内容（sheetData），生成excel文件；然后在response的header里设置文件名称（filename），最后以流的方式返回生成的excel文件。

2.一个excel文件做多存放65535条数据，多了会报错：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)。

3.在设置每一个ExcelBean的sheetData时，可以通过设置colRow来合并单元格。

4.文件的返回都是通过流的方式，流在使用完后一定要关闭，否则会额外占用内存空间。

最后

以上就是俊逸音响为你收集整理的Java中生成excel文件返回数据Java中生成excel文件返回数据的全部内容，希望文章能够帮你解决Java中生成excel文件返回数据Java中生成excel文件返回数据所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。