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;
@Table(name = "TD_API_USER")
@Getter
@Setter
@AllArgsConstructor
@Builder
@Data
public class UserInfo implements Serializable {
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;
@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;
@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;
private int num = 0;
private String name = "测试excel";
private String sheetName = "sheet名称";
private String headerCenter = "测试headerCenter";
private String[] tableHeader = new String[] { "测试数据" };
private List<Object[]> sheetData;
private Map<Integer, ExcelBean> sheets = null;
private List<Integer[]> colRow;
private Integer startRow;
private Integer[] colWidth;
private List<short[]> widthList;
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;
}
public Integer[] getColWidth() {
return 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() {}
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);
OutputStream sops = response.getOutputStream();
excel.write(sops);
sops.flush();
sops.close();
}
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;
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++) {
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.addMergedRegion(cra);
}
}
autoSizeColumn(sheet, data);
}
return excel;
}
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;
}
}
}
}
public static void toErrorPage(HttpServletResponse response, HttpServletRequest request, String toPage) {
try {
response.sendRedirect(request.getContextPath() + toPage);
} catch (IOException e) {
log.warn("{}", e);
}
}
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);
}
}
}
public static Font createFont(Workbook workbook, String fontName) {
Font font = workbook.createFont();
if (fontName != null) {
font.setFontName(fontName);
}
return font;
}
public static Font createFont(Workbook workbook, Short fontSize) {
Font font = workbook.createFont();
if (fontSize != null) {
font.setFontHeightInPoints(fontSize);
}
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;
}
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);
}
public static Integer[] getColWidth(int num) {
Integer[] ints = new Integer[num];
for (int i = 0; i < num; i++) {
ints[i] = 50 * 110;
}
return ints;
}
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);
}
}
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.文件的返回都是通过流的方式,流在使用完后一定要关闭,否则会额外占用内存空间。