Controller代码:
/**
* Excel文件导出
* @param res
* @param list
* @throws Exception
*/
public void export(HttpServletResponse res, List<Map> list, String name) throws Exception {
//获取导出的数据 导出的样式
XSSFWorkbook wb = new POIExcel().zxExprotExcelXLSX(list,name);
//将excel的数据写入文件
ByteArrayOutputStream fos = null;
byte[] retArr = null;
try {
fos = new ByteArrayOutputStream();
wb.write(fos);
retArr = fos.toByteArray();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
OutputStream os = res.getOutputStream();
try {
String contexttype = "text/plain;charset=utf-8";
res.reset();
res.setHeader("Content-Disposition", "attachment; filename=agent_book.xls");//要保存的文件名
res.setContentType("application/octet-stream; charset=utf-8");
res.setHeader("Access-Control-Allow-Origin", "*");
res.setHeader("content-type", contexttype);
os.write(retArr);
os.flush();
} finally {
if (os != null) {
os.close();
}
}
}
/**
* 查询系统用户信息
*/
@ApiOperation(value = "查询系统用户信息", httpMethod = "POST")
@RequestMapping(value = "/users/getUser", method = RequestMethod.POST)
@CrossOrigin
public String getuser(@RequestParam(value = "userid",required = true)String userid, @RequestParam(value = "username",required = true)String username,HttpServletResponse res) throws Exception {
R r_user = new R(0, null, "");
r_user= SysUserService.getuser(userid,username);
String strv = JSONObject.toJSONString(r_user.getData());
//userid = "daochu"说明是请求导出数据
if ("daochu".equals(userid)){
List<Map> list = (List<Map>) r_user.getData();
//调用导出Excel方法 list表示需要导出的数据
export(res,list,"用户表");
}
return strv;
}
工具类:
package com.zhongjia.system.util;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class POIExcel {
/**
* 生成Excel
*/
public XSSFWorkbook zxExprotExcelXLSX(List<Map> Listmap,String name) {
//有多少字段 就定义多少列
int fieldNumber = Listmap.get(0).size();
//数据大小 有多少条数据
int dataNumber = Listmap.size();
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("0");
for (int i = 0; i < fieldNumber; i++) {
sheet.setColumnWidth(i, 4300);
}
/**
* 单元格 样式
*/
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
/**
* 标题样式 样式
*/
XSSFFont titleFont = wb.createFont();
titleFont.setFontHeight(24);
titleFont.setBold(true);
CellStyle titleCellStyle = wb.createCellStyle();
titleCellStyle.setBorderTop(BorderStyle.THIN);
titleCellStyle.setBorderBottom(BorderStyle.THIN);
titleCellStyle.setBorderLeft(BorderStyle.THIN);
titleCellStyle.setBorderRight(BorderStyle.THIN);
titleCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
titleCellStyle.setFont(titleFont);
/**
* 主 标题 在这里插入主标题
*/
Row titleRow;
Cell titleCell;
sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 2, (short) 0, (short) fieldNumber-1));
for (int i = 0; i <= 2; i++) {
titleRow = sheet.createRow(i);
for (int j = 0; j < Listmap.get(0).size(); j++) {
titleCell = titleRow.createCell(j);
titleCell.setCellType(CellType.STRING);
titleCell.setCellStyle(titleCellStyle);
titleCell.setCellValue(name);
}
}
/**
* 列 标题 在这里插入标题
*/
Row rowLabel;
Cell cellLabel;
for (int i = 3; i < 4; i++) {
rowLabel = sheet.createRow(i);
Iterator iter = Listmap.get(0).entrySet().iterator(); //获取key和value的set
int jjj=0;
while(iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next(); //把hashmap转成Iterator再迭代到entry
Object key = entry.getKey(); //从entry获取key
cellLabel = rowLabel.createCell(jjj);
cellLabel.setCellType(CellType.STRING);
cellLabel.setCellStyle(cellStyle);
cellLabel.setCellValue(" " + key);
jjj++;
}
}
/**
* 列 数据 在这里插入数据
*/
Row rowCheck;
Cell cellCheck;
for (int i = 3; i < dataNumber+3; i++) {
rowCheck = sheet.createRow((i + 1));
Iterator iter = Listmap.get(i-3).entrySet().iterator(); //获取key和value的set
int jjj=0;
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next(); //把hashmap转成Iterator再迭代到entry
Object val = entry.getValue(); //从entry获取value
cellCheck = rowCheck.createCell(jjj);
cellCheck.setCellType(CellType.STRING);
cellCheck.setCellStyle(cellStyle);
cellCheck.setCellValue("" + val);
jjj++;
}
}
/**
* 页脚
*/
setExcelFooterName("测试", 0, wb);
return wb;
}
/**
* 设置Excel页脚
*/
public void setExcelFooterName(String customExcelFooterName, int setExcelFooterNumber, XSSFWorkbook wb) {
wb.setSheetName(setExcelFooterNumber, customExcelFooterName);
}
public static void main(String[] args) {
//new POIExcel().zxExprotExcelXLSX();
}
}
依赖:
<!-- JAVA POI 导出Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<!-- JAVA POI 导出Excel-->
方法不一定是最好的,但可以参考,代码复制直接用