本机excel版本大于
maven引入jar包配置
org.apache.poi
poi-ooxml
3.9
dom4j
dom4j
代码controller层
MemberSchoolController.java
package com.ztesoft.zsmart.zcm.monitor.controller;
import javax.servlet.http.HttpServletResponse;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;
import java.util.*;
@RestController
@RequestMapping(value="/memberschool")
public class MemberSchoolController {
/**
* @description:导出数据
* @param:[schoolId, response]
* @return:void
* @date:/02/21
* @author:tangyj
* @remark:
* */
@RequestMapping(value = "/download/data/{schoolId}", method = RequestMethod.GET,consumes = "application/octet-stream",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void download(
@PathVariable(value="schoolId",required = true)Long schoolId,
HttpServletResponse response) throws Exception{
//临时生成测试数据
String fileName = "test7.xlsx";
String headTitle = "test_title";
int colunmNum = 10;
ListheadTitleList = new ArrayList();
for(int i=0;i<10; i++){
headTitleList.add("first "+(i+1)+"xxx");
}
List> dataList = new ArrayList>();
for(int i=0;i<5; i++){
Listdatas = new ArrayList<>();
for(int j=0;j<10; j++){
datas.add("yyy "+(i+1)+"yyyy"+(j+1)+"yyy");
}
dataList.add(datas);
}
//1-创建一个HSSFWorkbook
ExcelObject excel = new ExcelObject("test_data");
//2-写入头标题
excel.createHeadTile(colunmNum,headTitle);//头标默认写在第一行
//3-写入行标题
excel.createRowTitle(headTitleList,1);
//4-写入具体数据
excel.createDataByRow(2,dataList);//因为没有行标题，所以从第二行开始
//5-生成excel文件
excel.buildExcelFile(fileName);
//6-浏览器下载excel
excel.buildExcelDocument(fileName,response);
}
}
逻辑层
ExcelObject.java
package com.ztesoft.zsmart.zcm.monitor.controller;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import .URLEncoder;
import java.util.List;
public class ExcelObject {
// private HSSFWorkbook workbook;
// private HSSFSheet sheet;
private XSSFWorkbook workbook;
private XSSFSheet sheet;
public ExcelObject() {
this.workbook = new XSSFWorkbook();
this. sheet = this.workbook.createSheet("sheet1");
}
public ExcelObject(String sheetName) {
this.workbook = new XSSFWorkbook();
this. sheet = this.workbook.createSheet(sheetName);
sheet.setDefaultRowHeight((short) (2 * 256)); //设置默认行高，表示2个字符的高度
sheet.setDefaultColumnWidth(17); //设置默认列宽，实际上回多出2个字符，不知道为什么
}
/**
* @description:设置头标题格式
* @param:[workbook, sheet]
* @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
* @date:/02/21
* @author:tangyj
* @remark:
* */
private XSSFCellStyle getHeadTitleStyle(){
//设置为居中加粗
XSSFCellStyle style = this.workbook.createCellStyle();
XSSFFont font = this.workbook.createFont();
font.setFontHeightInPoints((short)24);
font.setBold(true);
style.setAlignment( HorizontalAlignment.CENTER);
style.setFont(font);
style.setWrapText(true);//设置自动换行
return style;
}
/**
* @description:设置行标题格式
* @param:[workbook, sheet]
* @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
* @date:/02/21
* @author:tangyj
* @remark:
* */
private XSSFCellStyle getRowTitleStyle(){
//设置为居中加粗
XSSFCellStyle style = this.workbook.createCellStyle();
XSSFFont font = this.workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setBold(true);
style.setAlignment( HorizontalAlignment.CENTER);
style.setFont(font);
style.setWrapText(true);//设置自动换行
return style;
}
/**
* @description:设置普通单元格格式
* @param:[workbook, sheet]
* @return:org.apache.poi.hssf.usermodel.HSSFCellStyle
* @date:/02/21
* @author:tangyj
* @remark:
* */
private XSSFCellStyle getNormalCellTitleStyle(){
//设置为居中加粗
XSSFCellStyle style = this.workbook.createCellStyle();
XSSFFont font = this.workbook.createFont();
font.setFontHeightInPoints((short)12);
style.setAlignment( HorizontalAlignment.CENTER);
style.setFont(font);
style.setWrapText(true);//设置自动换行
return style;
}
/**
* @description:1-写入头标题
* @param:[workbook, sheet, column, headTitle]
* @return:void
* @date:/02/21
* @author:tangyj
* @remark:
* */
public void createHeadTile(int column, String headTitle){
//创建行(默认第一行)
XSSFRow row = sheet.createRow(0);
//合并单元格
int lastColIndex = column > 0 ? (column-1):0;
sheet.addMergedRegion(new CellRangeAddress(0,0,0,lastColIndex));
//单元格居中加粗格式
XSSFCellStyle style = getHeadTitleStyle();
//写入标题
XSSFCell cell = row.createCell(0);//获取当前行的第一列
cell.setCellValue(headTitle);//写入数据
cell.setCellStyle(style);//设置单元格格式
}
/**
* @description:2-写入行标题
* @param:[workbook, sheet, rowTitleList, firstRowIndex]
* @return:void
* @date:/02/21
* @author:tangyj
* @remark:
* */
public void createRowTitle(ListrowTitleList,int firstRowIndex){
//创建行
XSSFRow row = sheet.createRow(firstRowIndex);
//设置为居中加粗
XSSFCellStyle style = getRowTitleStyle();
XSSFCell cell;
for(int i=0; i< rowTitleList.size(); i++){
cell = row.createCell(i);//获取当前列
cell.setCellValue(rowTitleList.get(i));//写入数据
cell.setCellStyle(style);//设置单元格格式
}
}
/**
* @description:3-写入具体的单元格数据
* @param:[firstRowIndex, lineDataList]
* @return:void
* @date:/02/21
* @author:tangyj
* @remark:
* */
public void createDataByRow(int firstRowIndex,List> lineDataList){
//i代表行，j代表列
XSSFCellStyle style = getNormalCellTitleStyle();
for(int i=0; i< lineDataList.size(); i++){
ListlineDatas = lineDataList.get(i);
XSSFRow row = sheet.createRow(firstRowIndex + i);//行
for(int j=0; j
请求
接口请求通过swagger
如果觉得《java导出excel 打不开 poi 通过浏览器导出excel文件打不开》对你有帮助，请点赞、收藏，并留下你的观点哦！
