1.vue 前台配置
<el-button
v-waves
class="filter-item"
style="margin-left: 10px"
type="success"
icon="el-icon-edit"
@click="handleExport"
>
导出excel
</el-button>
methods: {
handleExport() {
const loading = this.$loading({
lock: true,
text: "正在导出,请稍候。。。",
spinner: "el-icon-loading",
background: "rgba(255,255,255,0.7)",
});
exportGongGong(this.listQuery)
.then((response) => {
saveAs(
response,
`xxxxxxx表${moment().format("YYYYMMDDHHmmss")}.xlsx`
);
loading.close();
})
.catch((_) => {
loading.close();
});
},
}
2.js 配置
export function exportGongGong(query) {
return request({
url: '/xxxx/xxxx',
method: 'get',
responseType: 'blob',
params: query
})
}
3.java 后台
方法中引用了一个 ExcelExportStyleUtil
@Resource
private ExcelExportStyleUtil excelExportStyleUtil;
//导出excel
@Override
public ResponseEntity<byte[]> exportGongGong(HttpServletRequest request, GonggongData data) {
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
UserInfoData userInfoData = toolUtil.getRedisUser(request);
String job=userInfoData.getJob();
QueryWrapper<shengchanData> wrapper = new QueryWrapper<>();
if (StringUtils.isNotBlank(data.getGgJbGcmc())) {
wrapper.like("a.gcmc", data.getGgJbGcmc());
}
List<GonggongData> DataList = Gonggong01Mapper.ExcelGonggongList(wrapper);
XSSFSheet sheet = workbook.createSheet();
//标题
XSSFRow xssfRow0 = sheet.createRow(0);
xssfRow0.setHeightInPoints(30);
CellRangeAddress cellHeader = new CellRangeAddress(0, 0, 0, 49);
//sheet.addMergedRegion(cellHeader);
sheet.addMergedRegionUnsafe(cellHeader);
XSSFCell head = xssfRow0.createCell(0);
XSSFCellStyle headerStyle = excelExportStyleUtil.setHeader(workbook);
head.setCellValue( "xxxxxxxxx");
head.setCellStyle(headerStyle);
XSSFCellStyle xssfCellStyle = excelExportStyleUtil.cellStyleBody(workbook, true);
xssfCellStyle.setWrapText(true);
XSSFRow xssfRow2 = sheet.createRow(1);
excelExportStyleUtil.createXSSFCell(xssfRow2, 0, xssfCellStyle).setCellValue("xxx");
excelExportStyleUtil.createXSSFCell(xssfRow2, 1, xssfCellStyle).setCellValue("xxx");
excelExportStyleUtil.createXSSFCell(xssfRow2, 2, xssfCellStyle).setCellValue("xx");
excelExportStyleUtil.createXSSFCell(xssfRow2, 3, xssfCellStyle).setCellValue("xx");
excelExportStyleUtil.createXSSFCell(xssfRow2, 4, xssfCellStyle).setCellValue("xx");
excelExportStyleUtil.createXSSFCell(xssfRow2, 5, xssfCellStyle).setCellValue("xx");
int rowNumber = 2;
int i = 1;
for (GonggongData sheng : DataList) {
XSSFRow xssfRow = sheet.createRow(rowNumber);
excelExportStyleUtil.createXSSFCell(xssfRow, 0, xssfCellStyle).setCellValue(sheng.getSysId());
excelExportStyleUtil.createXSSFCell(xssfRow, 1, xssfCellStyle).setCellValue(sheng.getSysGcxxdjh());
excelExportStyleUtil.createXSSFCell(xssfRow, 2, xssfCellStyle).setCellValue(sheng.getSysMfgcbh());
excelExportStyleUtil.createXSSFCell(xssfRow, 3, xssfCellStyle).setCellValue(sheng.getGgJbGcmc());
excelExportStyleUtil.createXSSFCell(xssfRow, 4, xssfCellStyle).setCellValue(sheng.getGgJbGcdz());
excelExportStyleUtil.createXSSFCell(xssfRow, 5, xssfCellStyle).setCellValue(getZhi("是否跨区",sheng.getGgJbKqx()));
i++;
rowNumber++;
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] barray = os.toByteArray();
HttpHeaders headers = new HttpHeaders();
String downloadFileName = new String("xxxxxx.xlsx".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
headers.setContentDispositionFormData("attachment", downloadFileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
PoitlIOUtils.closeQuietlyMulti(workbook, os);
return new ResponseEntity<>(barray, headers, HttpStatus.CREATED);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
3.使用的工具类
package com.bokun.hfsafety.tool;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import java.io.File;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import static org.apache.poi.ss.usermodel.BorderStyle.THIN;
@Component
public class ExcelExportStyleUtil {
/**
* 设置单元格样式
*
* @param hssfFont 字体
* @return
*/
private HSSFCellStyle createCellStyle(HSSFFont hssfFont, HSSFWorkbook hssfWorkbook) {
HSSFCellStyle hssfCellStyle2 = hssfWorkbook.createCellStyle();
//粗体
hssfFont.setBold(true);
//设置字体名称
hssfFont.setFontName("华文行楷");
//设置字体大小
hssfFont.setFontHeightInPoints((short) 15);
//字体颜色
hssfFont.setColor(IndexedColors.RED.getIndex());
hssfCellStyle2.setFont(hssfFont);
//水平居中
hssfCellStyle2.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
hssfCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
//设置图案样式
hssfCellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置图案颜色
hssfCellStyle2.setFillForegroundColor(IndexedColors.YELLOW.index);
return hssfCellStyle2;
}
public File getTempFileDir(String id) {
File tempFileDir = new File("E:/Temp", "temp/" + id + "");
if (!(tempFileDir.isDirectory() && tempFileDir.exists())) {
tempFileDir.mkdirs();
}
return tempFileDir;
}
public Sheet workbookCreateSheet(Workbook wb, Integer indexSheet) {
Sheet sheet;
try {
sheet = wb.getSheetAt(indexSheet);
if (sheet == null) {
sheet = wb.createSheet("Sheet" + indexSheet);
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return sheet;
}
public Row sheetCreateRow(Sheet sheet, Integer rownum, Float rowHeight) {
Row row;
try {
row = sheet.getRow(rownum);
if (row == null) {
row = sheet.createRow(rownum);
}
if (rowHeight != null) {
row.setHeightInPoints(rowHeight);
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return row;
}
public Cell rowCreateCell(Row row, Integer column, Object val, CellStyle styles) {
Cell cell;
// int cellType = Cell.CELL_TYPE_BLANK;
try {
cell = row.getCell(column);
if (cell == null) {
cell = row.createCell(column);
cell.setCellStyle(styles);
}
if (val != null) {
cell.setCellStyle(styles);
if (val instanceof Integer
|| val instanceof Float
|| val instanceof Double
|| val instanceof java.math.BigInteger
|| val instanceof java.math.BigDecimal) {
cell.setCellValue(Double.parseDouble(val.toString()));
} else if (val instanceof String) {
String valStr = val.toString();
if (valStr.startsWith("=")) {
cell.setCellFormula(valStr.substring(1, valStr.length()));
} else {
cell.setCellValue(valStr);
}
} else if (val instanceof Boolean) {
cell.setCellValue((Boolean) val);
} else if (val instanceof Date) {
cell.setCellValue((Date) val);
} else {
cell.setCellValue(val.toString());
}
} else {
cell.setBlank();
//cell.setCellType(cellType);
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return cell;
}
/**
* 设置样式
*
* @param wb
* @return
*/
public Map<String, CellStyle> createStyles(
Workbook wb
) throws Exception {
Map<String, CellStyle> styles = new HashMap<>();
try {
CellStyle percent = wb.createCellStyle();
//设置边框样式
percent = createBorderedStyle(wb);
Font font11 = wb.createFont();
font11.setFontHeightInPoints((short) 11);//字号
font11.setColor(IndexedColors.BLACK.getIndex());//字体颜色
font11.setFontName("宋体");//字体
percent.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
percent.setWrapText(true);//自动换行 设置换行"\n";
percent.setFont(font11);
percent.setAlignment(HorizontalAlignment.CENTER);//居中
styles.put("styleNormal", percent);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return styles;
}
/**
* 创建边框样式
*
* @param wb
* @return
*/
public CellStyle createBorderedStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setBorderRight(THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
public XSSFCellStyle createBorderedStyleXSSF(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
public XSSFCellStyle cellStyleBody(XSSFWorkbook workbook, Boolean border) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
if (border) {
cellStyle = createBorderedStyleXSSF(workbook);
}
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont xssfFont = workbook.createFont();
xssfFont.setFontName("宋体");
xssfFont.setFontHeightInPoints((short) 11);
xssfFont.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(xssfFont);
return cellStyle;
}
public XSSFCellStyle cellStyleN(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont xssfFont = workbook.createFont();
xssfFont.setFontName("宋体");
xssfFont.setFontHeightInPoints((short) 11);
xssfFont.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(xssfFont);
return cellStyle;
}
public XSSFCellStyle cellStyleRight(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont xssfFont = workbook.createFont();
xssfFont.setFontName("宋体");
xssfFont.setFontHeightInPoints((short) 11);
xssfFont.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(xssfFont);
return cellStyle;
}
public XSSFCellStyle setHeader(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont xssfFont = workbook.createFont();
xssfFont.setBold(true);
xssfFont.setFontName("宋体");
xssfFont.setFontHeightInPoints((short) 22);
xssfFont.setColor(IndexedColors.BLACK.index);
cellStyle.setFont(xssfFont);
return cellStyle;
}
public XSSFCell createXSSFCell(XSSFRow xssfRow, Integer column, XSSFCellStyle xssfCellStyle) {
XSSFCell cell = xssfRow.createCell(column);
cell.setCellStyle(xssfCellStyle);
return cell;
}
public void setRegionCell(XSSFSheet sheet, String val, XSSFCellStyle bodyStyle, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol, IndexedColors bodyColor) {
XSSFRow xssfRowOver = sheet.getRow(firstRow);
if (xssfRowOver == null) {
xssfRowOver = sheet.createRow(firstRow);
}
xssfRowOver.setHeightInPoints(30);
XSSFCell xssfCell = createXSSFCell(xssfRowOver, firstCol, bodyStyle);
xssfCell.setCellValue(val);
// if (firstCol == 12) {
// XSSFCellStyle style = sheet.getWorkbook().getCellStyleAt(firstCol);
// style.setAlignment(HorizontalAlignment.LEFT);
// style.setVerticalAlignment(VerticalAlignment.CENTER);
// style.setWrapText(true);
// style.setBorderBottom(THIN);
// xssfCell.setCellStyle(style);
// }
CellRangeAddress cra_project =new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); // 起始行, 终止行, 起始列, 终止列
if (firstRow != lastRow || firstCol != lastCol) {
if (bodyStyle != null) {
RegionUtil.setBorderBottom(BorderStyle.THIN, cra_project, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra_project, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra_project, sheet);
if (bodyColor != null) {
RegionUtil.setTopBorderColor(bodyColor.getIndex(), cra_project, sheet);
RegionUtil.setRightBorderColor(bodyColor.getIndex(), cra_project, sheet);
RegionUtil.setBottomBorderColor(bodyColor.getIndex(), cra_project, sheet);
RegionUtil.setLeftBorderColor(bodyColor.getIndex(), cra_project, sheet);
}
}
sheet.addMergedRegionUnsafe(cra_project);
}
}
}