/*--------------------------------------导出开始--------------------------------------------------*/
@Override
public void downloadFile(HttpServletRequest request, HttpServletResponse response, Map<String, Object> map) {
List<EquipmentVO> safeCommonManagers = this.baseMapper.selectEquipmentList4Excel(map);
try{
//1、创建导出excel必要对象
//创建XSSFWorkbook对象
XSSFWorkbook wkb = new XSSFWorkbook();
XSSFCellStyle style= wkb.createCellStyle();
setDefaultStyle(style);
XSSFFont font = wkb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)12);
style.setFont(font);
XSSFCellStyle styleTitle = wkb.createCellStyle();
setDefaultStyle(styleTitle);
XSSFFont fontTitle = wkb.createFont();
fontTitle.setFontName("微软雅黑");
fontTitle.setFontHeightInPoints((short)12);
fontTitle.setColor(XSSFFont.COLOR_RED);
styleTitle.setFont(fontTitle);
//创建XSSFSheet对象
XSSFSheet sheet = wkb.createSheet("设备台账表");
// //1、第一行表头:箱动态报表(报表时间:2020-04-30 08:00) 合并1行,61列
// sheetMerageRegion(sheet,0,0,0,10);
// XSSFRow title = sheet.createRow(0);
// XSSFCell title10Cel0 = title.createCell(0, XSSFCell.CELL_TYPE_STRING);
// title10Cel0.setCellStyle(styleTitle);
// title10Cel0.setCellValue("设备台账表");
// //创建空cell
// createVoidCell(title,1,10,styleTitle);
//3、设置第二行表头
String [] titleStr = {"设备类型","规格型号","发动机参数","变速箱参数","整机参数","吊具参数","臂架参数",
"使用单位","资产单位","负责人","生产厂家","供应商","出场时间"};
XSSFRow title2 = sheet.createRow(0);
//创建单元格并设置单元格内容;设置小表头
for(int i=0 ; i<titleStr.length ; i++ ){
XSSFCell temp = title2.createCell(i, XSSFCell.CELL_TYPE_STRING);
temp.setCellValue(titleStr[i]);
temp.setCellStyle(styleTitle);
}
//设置内容
//1、定义起始行、结束行
int startRow = 1;
for(EquipmentVO bean : safeCommonManagers){
XSSFRow row = sheet.createRow(startRow);
//列数
int j = 13;
XSSFCell[] cell = new XSSFCell[j];
cell = setValue(cell,j,row,style);
int n = 0;
//设备类型
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getDeviceTypeName())?"":bean.getDeviceTypeName());
//规格型号
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getDeviceSpec())?"":bean.getDeviceSpec());
//发动机参数
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getEngineParam())?"":bean.getEngineParam());
//变速箱参数
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getTransmissionParam())?"":bean.getTransmissionParam());
//整机参数
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getCompleteParam())?"":bean.getCompleteParam());
//吊具参数
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getSpreaderParam())?"":bean.getSpreaderParam());
//臂架参数
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getBoomParam())?"":bean.getBoomParam());
//使用单位
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getCompanyUseName())?"":bean.getCompanyUseName());
//资产单位
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getCompanyHaveName())?"":bean.getCompanyHaveName());
//负责人
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getLeaderName())?"":bean.getLeaderName());
//生产厂家
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getManufacturer())?"":bean.getManufacturer());
//供应商
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getSupplier())?"":bean.getSupplier());
//出场时间
cell[n++].setCellValue(ObjectUtils.isEmpty(bean.getProductionTime())?"":dateFormat(bean.getProductionTime()));
startRow ++;
}
String fileName = "excel导出";
setSizeColumn(sheet,61,2);
//3、输出excel对象
//输出Excel文件
OutputStream output = response.getOutputStream();
String userAgent = request.getHeader("USER-AGENT");
fileName = getFileName(userAgent, fileName);
response.reset();
//导出xls格式
// response.setContentType("application/msexcel; charset=UTF-8");
//导出为xlsx格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setCharacterEncoding("UTF-8");
wkb.write(output);
output.close();
}catch(Exception e){
log.error("设备台账表excel出错:",e);
}
}
private void setDefaultStyle(XSSFCellStyle style) {
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
// style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
// style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
}
private void sheetMerageRegion(XSSFSheet sheet, int startrow, int overrow, int startcol, int overcol) {
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}
private void createVoidCell(XSSFRow title, int start, int count, XSSFCellStyle style){
for(int i=start;i<=count;i++){
XSSFCell cell = title.createCell(i, XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style);
cell.setCellValue("");
}
}
private void setSizeColumn(XSSFSheet sheet, int size, int rowNumStart) {
for (int columnNum = 0; columnNum < size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = rowNumStart; rowNum < sheet.getLastRowNum(); rowNum++) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
public String getFileName(String userAgent, String fileName) throws UnsupportedEncodingException {
// IE浏览器
if (StringUtils.contains(userAgent, "MSIE")) {
fileName = URLEncoder.encode(fileName, "UTF8");
// google,火狐浏览器
} else if (StringUtils.contains(userAgent, "Mozilla")) {
fileName = new String(fileName.getBytes(), "ISO8859-1");
// 其他浏览器
} else {
fileName = URLEncoder.encode(fileName, "UTF8");
}
return fileName;
}
public String dateFormat(Date date){
try{
if(ObjectUtils.isEmpty(date)){
return "";
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(date);
}catch (Exception e){
return "";
}
}
private XSSFCell[] setValue(XSSFCell[] cell,int j,XSSFRow row,XSSFCellStyle style){
for(int a=0;a<j;a++){
cell[a] = row.createCell(a,XSSFCell.CELL_TYPE_STRING);
cell[a].setCellStyle(style);
}
return cell;
}
/*--------------------------------------导出结束--------------------------------------------------*/
此为输出xlsx格式。
如果需要xls格式。将所有XSSF改为HSSF,并修改response.setContentType为被注释掉的代码,以及前端按钮方法的type为type: "application/vnd.ms-excel"
//前端
//导出按钮方法
onExport() {
ptExport().then(res => {
const link = document.createElement('a');
let blob = new Blob([res.data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
let objectUrl = URL.createObjectURL(blob);
link.href = objectUrl;
link.download = '普通管理人员统计';
link.click();
URL.revokeObjectURL(objectUrl);
})
},
// 接口
export const ptExport = (data) => {
return request({
url: '/api/XXX-XXX/XXXXX/export',
method: 'get',
responseType: 'blob',
data
})
}
参考代码