导出 不使用模板
效果展示
代码展示:
public AjaxResult exportTableData(UserColumn userColumn) {
// 获取总列数
List<UserColumn> userColumns = userColumnMapper.getCloumn(userColumn);
try {
//开始创建excel
SXSSFWorkbook workBook = new SXSSFWorkbook();
//加粗、垂直
CellStyle boldStyle = workBook.createCellStyle();
Font font = workBook.createFont();
font.setBold(true);
boldStyle.setFont(font);
//加边框
CellStyle boldLeftStyle = workBook.createCellStyle();
boldLeftStyle.setBorderBottom(BorderStyle.MEDIUM); //下边框
boldLeftStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
boldLeftStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
boldLeftStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
//头部颜色
CellStyle headStyle = workBook.createCellStyle();
headStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());//设置单元格的背景颜色
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充单元格
headStyle.setWrapText(true);// 指定当单元格内容显示不下时自动换行
Font fontTxt = workBook.createFont();
fontTxt.setColor(HSSFColor.WHITE.index);
fontTxt.setFontHeightInPoints((short) 18);
fontTxt.setFontName("微软雅黑");
//垂直、加粗、居中
CellStyle centerStyle = workBook.createCellStyle();
Font center_font = workBook.createFont();
center_font.setBold(true);
centerStyle.setFont(center_font);
//centerStyle.setAlignment(CellStyle.ALIGN_CENTER);
centerStyle.setAlignment(HorizontalAlignment.CENTER);
//centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置sheet
Sheet sheet = workBook.createSheet("个性化报表");
//设置总列
for (int z = 0; z < userColumns.size(); z++) {
sheet.setColumnWidth(z, 5500);
}
//设置第一行
Row row0 = sheet.createRow(0);
row0.setHeight((short) 1000);
//第一行第一列
row0.createCell(0).setCellStyle(headStyle);
//第一行第二列
Cell cell = row0.createCell(1);
String head = "上海XXX股份有限公司上海分公司" + "\r\n" + "China Telecom Corporation Limited Shanghai Branch";
cell.setCellValue(head);
headStyle.setFont(fontTxt);
cell.setCellStyle(headStyle);
//合并单元格CellRangeAddress有4个参数:起始行号,终止行号, 起始列号,终止列号
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, userColumns.size()-1));
//向excel中插入logo图片
ByteArrayOutputStream baos = new ByteArrayOutputStream();
String picPaht = "C:\\Users\\Bt_202003\\Desktop\\202210\\fsdownloadtempFile\\heng.png";
File imageFile = new File(picPaht);
BufferedImage bi = ImageIO.read(imageFile);
ImageIO.write(bi, "png", baos);
Drawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);
drawing.createPicture(anchor, workBook.addPicture(baos.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_JPEG));
//设置第二行
Row row1 = sheet.createRow(1);
// 设置列
for (int i = 0; i < userColumns.size(); i++) {
cell = row1.createCell(i);
cell.setCellValue(userColumns.get(i).getFieldName());
cell.setCellStyle(boldLeftStyle);
}
// 填充表数据
List<KcExcelTable> tables = mapper.getData(userColumns.get(0).getTaskId());
for (int i = 0; i < tables.size(); i++) {
KcExcelTable kcExcelTable1 = tables.get(i);
// 创建行
Row row = sheet.createRow((i + 2));
for (int j = 0; j < userColumns.size(); j++) {
String entityName = userColumns.get(j).getEntityName();
//通过传入实体类字段名,获取字段值方法
if (null == (kcExcelTable1.get(entityName))) {
cell = row.createCell(j);
cell.setCellValue("");
if (!(null == kcExcelTable1.get(entityName))) {
cell.setCellStyle(boldLeftStyle);
}
} else {
cell = row.createCell(j);
cell.setCellValue(kcExcelTable1.get(entityName));
cell.setCellStyle(boldLeftStyle);
// cell.setCellStyle(boldStyle);
}
}
}
// 创建写入文件流
OutputStream os = null;
// // 创建这个文件夹
// try {
// File execlSaveFile = new File(exportUrl);
// if (!execlSaveFile.exists()) {
// execlSaveFile.mkdirs();
// }
// } catch (Exception e) {
// e.printStackTrace();
// }
// 导出地址
String exportUrl = "C:/Users/Bt_202003/Desktop/202210/fsdownload" + FileUtils.getTempPath() + "excel导出" + UUID.randomUUID().toString() + ".xlsx";
FileUtils.createFile(exportUrl);//创建文件
//写入文件
try {
os = new FileOutputStream(exportUrl);
workBook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != os) {
os.close();
}
}
} catch (IOException e) {
e.printStackTrace();
}
return AjaxResult.success("导出成功");
}
```java
//数据表实体类,通过传入实体类字段名,获取字段值方法
public String get(String name){
String str = "";
//如果传入的实体类名字与case里面的字符串相等,则调用相应的get方法获取值。
switch (name){
case "deviceNo":
str = getDeviceNo();
break;
case "billingCycle":
str = getBillingCycle();
break;
}
return str;
}