Java + vue 使用poi导出excel列表
简单易懂
pom.xml 代码片
.
<!-- POI导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
java后台代码 代码片
.
/**
* 导出 excel列表
*/
@PostMapping("/toExcel")
@ApiOperation(value = "导出excel表格")
public void toExcel(@ApiParam(value = "条件", required = true) @RequestBody RiskInvestigationAndRiskInvestigationUnitDTO riskInvestigation,
HttpServletResponse response) throws IOException {
//获得数据库当中的数据,这里是已经写好的一个方法,返回的是一个select * 的list集合。
List<RiskInvestigationAndRiskInvestigationUnitVO> list =riskInvestigationMapper.findInverstigationAndUnit(riskInvestigation);
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个表,是指一个xsl文件可以创建三个表其中的一个表的表名,设置为user info
HSSFSheet sheet = workbook.createSheet("excel的左下角名称");
//设置一行(第一行) 表头
HSSFRow row =null;
row = sheet.createRow(0); //创建第一个单元格
row.setHeight((short)(26.5 * 20)); //设置行高,在这里26.5就是这一行的行高
row.createCell(0).setCellValue("表头title"); //这个单元格的值
//设置单元格合并,单元格合并的位置,0到0行,0到6列
//excel表也是从0行0列开始算起,注意自己设置合并的大小,避免已经设置值了的单元格也被合并导致代码报错
CellRangeAddress rowRegion = new CellRangeAddress(0,0,0,6);
sheet.addMergedRegion(rowRegion); //进行合并
//另起一行,也就是第二行
row =sheet.createRow(1);
row.setHeight((short)(22.5*20));
row.createCell(0).setCellValue("事件编号");//给单元格进行赋值
row.createCell(1).setCellValue("事件来源");
row.createCell(2).setCellValue("排查单位");
row.createCell(3).setCellValue("主题");
row.createCell(4).setCellValue("提交时间");
row.createCell(5).setCellValue("提交人");
row.createCell(6).setCellValue("处置状态");
//对前面得到的list集合进行遍历
for (int i =0 ;i<list.size();i++){
row = sheet.createRow(i+2); //重新找到这一行数据的行数,也就是在前端的基础上再另起一行
row.setHeight((short)(22.5*20));
RiskInvestigationAndRiskInvestigationUnitVO unitVO =list.get(i); //得到集合遍历的每一行数据
row.createCell(0).setCellValue(unitVO.getInvestigationTaskNumber()); //将值给到单元格
row.createCell(1).setCellValue(unitVO.getSourceName()); //unitVO是一个实体类对象,获取get方法进行赋值
row.createCell(2).setCellValue(unitVO.getRectificationUnitName());
row.createCell(3).setCellValue(unitVO.getSubjectName());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String createDate = sdf.format(unitVO.getCreateDate());
row.createCell(4).setCellValue(createDate);
row.createCell(5).setCellValue(unitVO.getCreateUserName());
String statusName = "";
if("1".equals(unitVO.getStatus().toString())){
statusName = "待验证";
}else{
statusName = "已完成";
}
row.createCell(6).setCellValue(statusName);
}
//设置自适应宽度
for (int i =0;i<=13 ;i++){
sheet.autoSizeColumn(i);
}
//response进行相应数据
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
//这里进行设置了一个文件名,其实也可以不要设置了,
//在前端进行下载的时候需要重新给定一个文件名进行下载
response.setHeader("Content-disposition","attachment;filename=User.xls");
workbook.write(os);
os.flush();
os.close();
}
vue前端导出按钮 代码片
.
<el-button v-access:add class="btn-default btn1" icon="el-icon-circle-plus-outline" :disabled="loading" size="small" @click="exportExcel()">导出数据</el-button>
vue前端请求地址 代码片
.
export const toExcel = params => {
return axios({
method: 'post',
url: 'riskInvestigation/toExcel',
data: params,
responseType: 'blob',
})
}
vue前端请求地址 代码片
.
import { toExcel} from '@/api/investigation/investigation.js'
//导出excel表
exportExcel() {
this.downloadFiles();
},
downloadFiles() {
let _this = this;
let params = {};
Object.assign(params, this.formData);
let _createDate = this.createDate;
if (_createDate && _createDate.length > 0) {
let beginDate = (_createDate[0]).Format("yyyy-MM-dd");
let endDate = (_createDate[1]).Format("yyyy-MM-dd");
params.beginDate = beginDate;
params.endDate = endDate;
}
params.orgId = _this.orgId;
this.loading = true;
toExcel(params).then((result) => {
this.loading = false;
let blob = new Blob([result.data], {
type: result.data.type
});
let filename = result.headers.filename && decodeURI(result.headers.filename) || '列表.xls';
if ('download' in document.createElement('a')) {
const link = document.createElement("a");
link.style.display = "none";
link.href = URL.createObjectURL(blob);
link.setAttribute("download", filename);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
} else {
navigator.msSaveBlob(blob, filename);
}
}).catch((err) => {
this.loading = false;
_this.$alert(err, _this.$t('cm.tips'));
});
},