一、创建文件导出的工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;
/**
* @author jln
* <p>
* 生成指定样式的excel，并将数据渲染到excel
*/
public class ExportExcelUtilsTwo {
public static byte[] biliExport(String sheetTitle, List<Object> dataList) {
/** 第一步，创建一个Workbook，对应一个Excel文件 */
HSSFWorkbook wb = new HSSFWorkbook();
/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */
HSSFSheet sheet = wb.createSheet(sheetTitle);
/** 第三步，设置样式以及字体样式*/
// HSSFCellStyle titleStyle = createTitleCellStyle(wb);
HSSFCellStyle headerStyle = createHeadCellStyle(wb);
HSSFCellStyle contentStyle = createContentCellStyle(wb);
/** 第四步，创建标题 ,合并标题单元格 */
// 行号
int rowNum = 0;
// 创建第一页的第一行，索引从0开始
HSSFRow row0 = sheet.createRow(rowNum++);
// 设置行高
row0.setHeight((short) 800);
String[] row0_first = new String[]{};
row0_first = new String[]{"UP主昵称", "主页链接", "达人分类", "达人等级", "视频数", " 粉丝数 ", "平均播放量", "平均互动量",
"平均互动率", "平均点赞量", "平均评论量", "平均收藏量", "平均投币数", "平均分享数", "预计cpm", "预计cpe", "SCI指数",
"粉丝性别占比", "",
"粉丝年龄占比", "", "", "",
"粉丝地域占比", "", "", "", "",
"粉丝设备占比", "", "", "", "", ""};
for (int i = 0; i < row0_first.length; i++) {
HSSFCell tempCell = row0.createCell(i);
tempCell.setCellStyle(headerStyle);
tempCell.setCellValue(row0_first[i]);
}
sheet = getMergedRegionSheet(sheet);
// 第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short) 800);
String[] row1_first = new String[]{};
row1_first = new String[]{"", "", "", "", "", " ", "", "", "", "", "", "", "", "", "", "", "",
"男", "女", "小于18岁", "18-24岁", "25-30岁", "大于30岁",
"TOP1", "TOP2", "TOP3", "TOP4", "TOP5", "huawei", "apple", "xiaomi", "vivo", "oppp", "其他"};
for (int a = 0; a < row1_first.length; a++) {
HSSFCell tempCell = row1.createCell(a);
tempCell.setCellValue(row1_first[a]);
tempCell.setCellStyle(headerStyle);
}
byte result[] = null;
ByteArrayOutputStream out = null;
try {
//创建表格数据
Field[] fields;
int i = 1;
for (Object obj : dataList) {
fields = obj.getClass().getDeclaredFields();
HSSFRow rowBody = sheet.createRow(rowNum++);
//rowBody.setHeightInPoints(34);
int j = 0;
// 显示的列是你所要封装的实体类的每个对象，而且顺序是你创建对象的顺序
for (Field f : fields) {
f.setAccessible(true);
HSSFCell hc = rowBody.createCell(j);
if (f.get(obj) != null) {
hc.setCellValue(f.get(obj).toString());
} else {
hc.setCellValue("");
}
hc.setCellStyle(contentStyle);
j++;
}
i++;
}
out = new ByteArrayOutputStream();
wb.write(out);
result = out.toByteArray();
} catch (Exception ex) {
throw new RuntimeException("报表导出异常" + ex.getMessage());
} finally {
try {
if (null != out) {
out.close();
}
} catch (IOException ex) {
throw new RuntimeException("报表导出异常" + ex.getMessage());
}
}
return result;
}
/**
* 创建标题样式
*
* @param wb
* @return
*/
private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setBold(true); //字体加粗
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
*
* @param wb
* @return
*/
private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setBold(true); //字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建内容样式
*
* @param wb
* @return
*/
private static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
// 生成12号字体
HSSFFont font = wb.createFont();
font.setColor((short) 8);
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
return cellStyle;
}
public static HSSFSheet getMergedRegionSheet(HSSFSheet sheet) {
// 第一行合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 0));
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 9, 9));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 10, 10));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 11, 11));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 12, 12));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 13, 13));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 14, 14));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 15, 15));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 16, 16));
//粉丝性别占比
sheet.addMergedRegion(new CellRangeAddress(0, 0, 17, 18));
//粉丝年龄占比
sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 22));
//粉丝地域占比
sheet.addMergedRegion(new CellRangeAddress(0, 0, 23, 27));
//粉丝设备占比
sheet.addMergedRegion(new CellRangeAddress(0, 0, 28, 33));
return sheet;
}
}二 、封装好的工具类
import com.yuepu.common.config.YuePuConfig;
import com.yuepu.common.core.domain.AjaxResult;
import org.apache.commons.io.FileUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
/**
* @author yuepu
* 导出收藏账号-上传/下载excel文件
*/
public class ExportOrImportExcel {
public static File intoExcel(String sheetTitle, List<Object> dataList, String suffix) {
String filePath = YuePuConfig.getDownloadPath() + sheetTitle + suffix;
byte[] b = ExportExcelUtils.biliExport(sheetTitle, dataList);
File f = new File(filePath);
try {
if (f.exists()) {
f.delete();
f.createNewFile();
// 使用commons-io-2.0.1.jar下的方法实现文件上传
FileUtils.writeByteArrayToFile(f, b, true);
} else {
f.createNewFile();
FileUtils.writeByteArrayToFile(f, b, true);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据生成excel程序异常" + e.getMessage());
}
return f;
}
public static AjaxResult exportExcel(String fileName, File file, HttpServletResponse response) {
try {
response.setCharacterEncoding("UTF-8");
//设置输出文件类型为excel文件
response.setContentType("application/vnd.ms-excel");
//通知浏览器下载文件而不是打开
response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Pragma", java.net.URLEncoder.encode(fileName, "UTF-8"));
long size = file.length();
byte[] buffer = new byte[(int) size];
FileInputStream fis = null;
BufferedInputStream bis = null;
OutputStream os = null;
try {
os = response.getOutputStream();
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
int len = 0;
while ((len = bis.read(buffer)) > -1) {
//并不是每次都能读到1024个字节，所有用len作为每次读取数据的长度，否则会出现文件损坏的错误
os.write(buffer, 0, len);
}
os.flush();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("模板文件读取失败" + e.getMessage());
} finally {
bis.close();
fis.close();
os.close();
}
// file.delete();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("调用文件上传下载方法程序异常" + e.getMessage());
}
return AjaxResult.success();
}
}3、controller类
@PostMapping("/exportBiliCollection")
public AjaxResult exportBiliCollection( HttpServletResponse response) throws Exception{
AjaxResult ajaxResult = null;
// 获取系统时间时间戳
String timeMillis = String.valueOf(System.currentTimeMillis());
// 先查询出已收藏的账号 获取数据集
List<Object> objectList=new ArrayList<>();
AccountBilibili accountBilibili=new AccountBilibili();
objectList.add(accountBilibili);
String sheetTitle = "B站收藏账号列表" + timeMillis;
// 第一步 生成excel文件并返回文件名
File file = ExportOrImportExcel.intoExcel(sheetTitle, objectList, ".xlsx", PlatformDict.PLATFORM_BILIBILI.code);
String fileName = file.getName();
// 第二步 下载生成excel文件
ajaxResult = ExportOrImportExcel.exportExcel(fileName, file, response);
return ajaxResult;
}4 所需要导入的工具包
5、在pom.xml中增加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>6、vue 下载文件到客户端
<el-form-item>
<el-button @click="exportexcel()" type="primary">导出文件</el-button>
</el-form-item>
// 导出
exportexcel() {
this.$http({
url: this.$http.adornUrl("/generator/doorlog/checkoutlog"),
method: "get",
// 如果用blob对象来接收，此处一定要设置responseType:'blob',不然会报文件已损坏
responseType:'blob',
params: this.$http.adornParams({
shopname: this.dataForm.shopname,
nickname: this.dataForm.nickname,
phone: this.dataForm.phone,
userid: this.$store.state.user.id //获取当前登录用户的id ,用户名：this.$store.state.user.name
})
})
.then(function(res) {
// 将后台返回的数据二进制流转化为base64
let blob = new Blob([res.data], {type: "application/vnd.ms-excel"});
let objectUrl = URL.createObjectURL(blob);
//window.location.href = objectUrl;
var alink = document.createElement("a");
alink.href = objectUrl;
alink.download = '进出店日志'; //日志名
alink.click();
})
}
}//获取文件格式
String fileType=ftpName.substring(ftpName.lastIndexOf(".")+1,ftpName.length());
response.setHeader("Content-Disposition",
"attachment;filename=" +
new String( (ftpName ).getBytes(),
"iso-8859-1"));
if("doc".equals(fileType)||"docx".equals(fileType)){
response.setContentType("application/msword");
}else if("xls".equals(fileType)||"xlsx".equals(fileType)){
response.setContentType("application/msexcel");
}else if("jpg".equals(fileType)||"docx".equals(fileType)){
response.setContentType("image/jpg");
}else if("png".equals(fileType)||"docx".equals(fileType)){
response.setContentType("image/png");
}else{
response.setContentType("application/"+fileType);生成的单元格
注意：
如果前端解析文件失败， 可将工具类中的HSSF改为XSSF，原因是
HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
XSSF是POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
