一、pom.xml配置
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi fx-->
<!-- 使用xls格式时、只要导入poi-version-yyyymmdd.jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<!-- 使用xlsx格式 要导入poi-ooxml-version-yyyymmdd.jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
二、后端部分代码
1、数据组装:
1.1 controller层:
//导出excel -- 将页面表格中数据导出到excel文档
@RequestMapping(value = "/exportFile", method = RequestMethod.POST)
@ResponseBody
public void exportFile(HttpServletRequest request, HttpServletResponse response, ExportFileRequest exportFileRequest) {
try {
layerSpecialService.exportFile(response, exportFileRequest);
} catch (Exception e) {
logger.error("数据导出excel异常", e);
}
}
1.2 service实现层
@Override
public void exportFile(HttpServletResponse response,ExportFileRequest exportFileRequest) throws Exception {
if(EmptyUtil.isEmpty(exportFileRequest)){
throw new Exception("数据导出功能数据为空");
}
//获取数据
//excel标题
String[] title = {};
//excel文件名
Date date = new Date();
SimpleDateFormat fileNameSdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = fileNameSdf.format(date);
String sheetName = "";
String[][] content = {};
int[] width = {};
if(EmptyUtil.isNotEmpty(exportFileRequest.getUserList())){//测试数据
List<User> list = exportFileRequest.getUserList();
title = new String[]{"序号","编号","时间","等级","类型","备注"};
fileName = "导出测试单"+fileName+".xls";
//sheet名
sheetName = "导出测试单";
content = new String[list.size()][title.length];
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < list.size(); i++) {
User user = list.get(i);
//if(EmptyUtil.isNotEmpty(user ))
content[i][0] = String.valueOf(user .getId());
content[i][1] = user .getCode();
if(EmptyUtil.isEmpty(user .getFindTime())){
content[i][2] = "";
}else{
content[i][2] = sdf.format(user .getFindTime());
}
content[i][3] = user .getAccLevel();
content[i][4] = user .getAccType();
content[i][5] = accidentInfo.getRemark().toString();
}
width = new int[]{1000,6100,5600,4300,4300,8000};
}
//创建HSSFWorkbook
//HSSFWorkbook wb = commonService.getHSSFWorkbook(sheetName, title, content, null);
XSSFWorkbook wb = commonService.getXSSFWorkbook(sheetName, title, content, null,width);
//响应到客户端
try{
if(wb instanceof XSSFWorkbook){
fileName = fileName + "x";
}
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
响应流发送
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
公用接口:
public XSSFWorkbook getXSSFWorkbook(String sheetName, String[] title, String[][] values, XSSFWorkbook book,int[] width) {
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if(book == null){
book = new XSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = book.createSheet(sheetName);
for(int i = 0;i<width.length;i++){
sheet.setColumnWidth(i,width[i]);
}
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
XSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
//HSSFCellStyle style = book.createCellStyle();
//声明列对象
XSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
row.setHeightInPoints(27);//目的是想把行高设置成27px
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(getXSSFColumnTopStyle(book));
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
row.setHeightInPoints(22);//目的是想把行高设置成22px
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
cell = row.createCell(j);
cell.setCellValue(values[i][j]);
cell.setCellStyle(getXSSFColumnStyle(book));
}
}
return book;
}
// Hssf表头样式
public XSSFCellStyle getXSSFColumnTopStyle(XSSFWorkbook workbook) {
// 设置字体
XSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("微软雅黑");
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.LIGHT_BLUE.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
//列单元格样式
public XSSFCellStyle getXSSFColumnStyle(XSSFWorkbook workbook) {
// 设置字体
XSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)10);
//设置字体名字
font.setFontName("微软雅黑");
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(BorderStyle.THIN);
//设置底边框颜色;
style.setBottomBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置左边框;
style.setBorderLeft(BorderStyle.THIN);
//设置左边框颜色;
style.setLeftBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置右边框;
style.setBorderRight(BorderStyle.THIN);
//设置右边框颜色;
style.setRightBorderColor(IndexedColors.LIGHT_BLUE.index);
//设置顶边框;
style.setBorderTop(BorderStyle.THIN);
//设置顶边框颜色;
style.setTopBorderColor(IndexedColors.LIGHT_BLUE.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
//设置水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
三、前端部分
注意:前端不能用ajax传参,可以用from表单传参。
js参考:
//导出表格数据
function exportFile(){
var selection = $("#userManage").bootstrapTable('getSelections');
if(selection.length == 0){
addErrBoxTwo("请至少选中一行");
}else{
var param = "<input type='hidden' name='fileType' value='0'/>";
for(var i = 0; i < selection.length; i++){
var type = selection[i].type;
var level = selection[i].level;
param += "<input type='hidden' name='userList["+i+"].id' value='"+(i+1)+"'/>"+
"<input type='hidden' name='userList["+i+"].code' value='"+selection[i].code+"'/>";
if(selection[i].time != null){
var temper = selection[i].time;
var time = new Date(temper.replace(/-/,"/"));
param += "<input type='hidden' name='userList["+i+"].findTime' value='"+time+"'/>";
}
param += "<input type='hidden' name='userList["+i+"].level' value='"+level+"'/>"+
"<input type='hidden' name='userList["+i+"].type' value='"+type+"'/>"+
param += "<input type='hidden' name='userList["+i+"].remark' value='"+selection[i].remark+"'/>";
}
$("<form id='downloadform'>").attr({
"action" : "/xm/xx/exportFile.htm",
"method" : "POST",
})
.append(param)
.appendTo(document.body).submit();
}
}
html:
<p onclick="exportFile()" class="btn btn-default" style="margin-left: 8px;">导出</p>