package com.product.module.data.service;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONArray;
|
import com.alibaba.fastjson.JSONObject;
|
import com.google.common.collect.Lists;
|
import com.product.core.config.CoreConst;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.entity.RequestParameterEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.module.data.config.CmnCode;
|
import com.product.module.data.config.CmnConst;
|
import com.product.module.data.utli.*;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.context.request.RequestContextHolder;
|
import org.springframework.web.context.request.ServletRequestAttributes;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.*;
|
import java.util.*;
|
|
/**
|
* @Author cheng
|
* @Description 数据导出service
|
* @Date 2021/6/10 15:02
|
* @Version 1.0
|
*/
|
@Service
|
public class SystemDataExportService extends AbstractBaseService {
|
@Autowired
|
SystemApiToMethods systemApiToMethods;
|
|
@Override
|
public BaseDao getBaseDao() {
|
return super.getBaseDao();
|
}
|
|
/**
|
* 数据报表导出
|
*
|
* @param reqp
|
* @return 返回Base64 EXCEL.XLSX
|
* @throws BaseException
|
* @throws IOException
|
*/
|
public String reportDataExport(RequestParameterEntity reqp) throws BaseException {
|
|
FieldSetEntity formData = reqp.getFormData();
|
//总列数
|
Integer totalColumn = formData.getInteger("totalColumn");
|
//总行数
|
Integer totalRows = formData.getInteger("totalRows");
|
//头部区域行数
|
Integer headCount = formData.getInteger("headCount");
|
Map<String, File> files = reqp.getFiles();
|
//固定文件名
|
File portExcel = files.get("portExcel.xlsx");
|
try (InputStream inputStream = new FileInputStream(portExcel); XSSFWorkbook wb = new XSSFWorkbook(inputStream)) {
|
XSSFSheet sheet = wb.getSheetAt(0);
|
//获取头部样式
|
XSSFCellStyle headStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), true, (short) 15, new java.awt.Color(191, 191, 191));
|
//获取数据区样式
|
XSSFCellStyle dataStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), false, (short) 13, new java.awt.Color(255, 255, 255));
|
//所有同列最宽
|
Map<Integer, Integer> m = new HashMap<>();
|
for (int i = 0; i < totalRows; i++) {
|
XSSFRow row = sheet.getRow(i);
|
XSSFCellStyle style = null;
|
if (i < headCount) {
|
//头部区域样式
|
style = headStyle;
|
} else {
|
//数据区样式
|
style = dataStyle;
|
}
|
//遍历每一个单元格
|
for (int j = 0; j < totalColumn; j++) {
|
//获取单元格对象
|
XSSFCell cell = row.getCell(j);
|
if (cell == null) {
|
//填充空的单元格
|
cell = row.createCell(j, CellType.STRING);
|
}
|
//设置单元格样式
|
cell.setCellStyle(style);
|
//获取单元格值
|
String val = cell.getStringCellValue();
|
if (val != null) {
|
//根据字节计算大致宽度
|
int i2 = val.getBytes().length * 256;
|
//宽度不能超过 256*256
|
if (i2 > 256 * 256) {
|
i2 = 256 * 255;
|
}
|
if (m.get(j) == null) {
|
m.put(j, i2);
|
} else {
|
//当前行的列是否比其他行的列宽
|
Integer width = m.get(j);
|
if (width < i2) {
|
m.put(j, i2);
|
}
|
}
|
}
|
|
}
|
}
|
//设置每列的宽度
|
for (Map.Entry<Integer, Integer> v : m.entrySet()) {
|
sheet.setColumnWidth(v.getKey(), v.getValue());
|
}
|
//将workbook转换为字节流
|
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
|
//将excel输出到字节流
|
wb.write(byteArrayOutputStream);
|
//将字节流转为字节
|
byte[] bytes = byteArrayOutputStream.toByteArray();
|
//将字节转换为Base64
|
String encode = Base64.getEncoder().encodeToString(bytes);
|
//删除传入的文件
|
if (portExcel != null && portExcel.exists()) {
|
portExcel.delete();
|
}
|
//返回Base64字符串 拼接XLSX文件格式前缀
|
return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + encode;
|
} catch (Exception e) {
|
throw new BaseException(CmnCode.EXPORT_REPORT_DATA_FIAL.getValue(),CmnCode.EXPORT_REPORT_DATA_FIAL.getValue()+e.getMessage());
|
}
|
}
|
|
/**
|
* 单元格样式
|
*
|
* @param workbook
|
* @param color 字体颜色
|
* @param bold 是否加粗
|
* @param fontSize 字体大小
|
* @param bgc 背景色
|
* @return
|
*/
|
public XSSFCellStyle getReportStyle(XSSFWorkbook workbook, short color, boolean bold, short fontSize, java.awt.Color bgc) {
|
XSSFCellStyle cellStyle = workbook.createCellStyle();
|
//左右居中
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
//上下居中
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
// 下边框
|
cellStyle.setBorderBottom(BorderStyle.THIN);
|
// 左边框
|
cellStyle.setBorderLeft(BorderStyle.THIN);
|
// 上边框
|
cellStyle.setBorderTop(BorderStyle.THIN);
|
// 右边框
|
cellStyle.setBorderRight(BorderStyle.THIN);
|
//设置背景色
|
// cellStyle.setFillForegroundColor(new XSSFColor());
|
//填充模式
|
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
XSSFFont font = workbook.createFont();
|
//字体颜色
|
font.setColor(color);
|
//加粗
|
font.setBold(bold);
|
//字体大小
|
font.setFontHeightInPoints(fontSize);
|
//字体样式
|
font.setFontName("微软雅黑");
|
//自动换行
|
cellStyle.setWrapText(true);
|
cellStyle.setFont(font);
|
return cellStyle;
|
}
|
|
|
/**
|
* 通用列表导出
|
*
|
* @param fse
|
* @throws BaseException
|
*/
|
public void generalListDataExport(FieldSetEntity fse) throws BaseException {
|
//导出文件名称
|
String file_name = fse.getString(CmnConst.FILE_NAME);
|
//数据接口
|
String upload_api_url = fse.getString(CmnConst.UPLOAD_API_URL);
|
//数据接口所需参数
|
FieldSetEntity export_param = fse.getSubDataTable(CmnConst.EXPORT_PARAM).getFieldSetEntity(0);
|
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
|
RequestParameterEntity requestParameterEntity = (RequestParameterEntity) request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
|
FieldSetEntity fs = new FieldSetEntity();
|
fs.setTableName(fse.getString(CmnConst.TABLE_NAME));
|
//开始页
|
int start_cpage = export_param.getInteger(CmnConst.START_CPAGE);
|
//结束页
|
int end_cpage = export_param.getInteger(CmnConst.END_CPAGE);
|
int pagesize = export_param.getInteger(CmnConst.PAGESIZE);
|
export_param.setValue(CmnConst.CPAGE, ((start_cpage) * pagesize) / pagesize);
|
export_param.setValue(CmnConst.PAGESIZE, pagesize * ((end_cpage - start_cpage) + 1));
|
export_param.remove(CmnConst.START_CPAGE);
|
export_param.remove(CmnConst.END_CPAGE);
|
Map<Object, Object> values = export_param.getValues();
|
for (Map.Entry<Object, Object> v : values.entrySet()) {
|
fs.setValue(v.getKey().toString(), v.getValue());
|
}
|
requestParameterEntity.setFormData(fs);
|
String result = (String) systemApiToMethods.run(upload_api_url, 1);
|
|
if (JSON.isValidObject(result)) {
|
JSONObject resultJson = JSON.parseObject(result);
|
if (200 == resultJson.getInteger("code")) {
|
//获取数据成功
|
Object data = resultJson.get("data");
|
if (data instanceof JSONObject) {
|
} else if (data instanceof JSONArray) {
|
DataTableEntity export_field = export_param.getSubDataTable("export_field");
|
try {
|
writeExcel((JSONArray) data, export_field, file_name);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
} else {
|
throw new BaseException(resultJson.getString("code"), resultJson.getString("msg"));
|
}
|
} else {
|
System.out.println("未知的数据类型");
|
}
|
}
|
|
/**
|
* 输出excel
|
*
|
* @param data
|
* @param fieldInfo
|
* @param main_title
|
* @throws IOException
|
*/
|
public void writeExcel(JSONArray data, DataTableEntity fieldInfo, String main_title) throws IOException {
|
//标题
|
List<List<String>> headTitles = Lists.newArrayList();
|
List<String> fields = Lists.newArrayList();
|
//导出的数据集
|
List<List<Object>> exportData = Lists.newArrayList();
|
List<String> titles = Lists.newArrayList();
|
List<String> titleTemplate = Lists.newArrayList();
|
titleTemplate.add(main_title);
|
for (int i = 0; i < fieldInfo.getRows(); i++) {
|
titles.add(fieldInfo.getString(i, CmnConst.FIELD_DESC));
|
fields.add(fieldInfo.getString(i, CmnConst.FIELD_NAME));
|
}
|
List<List<Object>> lists = contentData(data, fields);
|
titles.forEach(title -> {
|
List<String> secondTitle = Lists.newArrayList(titleTemplate);
|
secondTitle.add(title);
|
headTitles.add(secondTitle);
|
});
|
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
|
response.setContentType("multipart/form-data");
|
response.setCharacterEncoding("utf-8");
|
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
|
// 这里需要设置不关闭流
|
EasyExcel.write(response.getOutputStream()).
|
registerWriteHandler(new Custemhandler()).head(headTitles).sheet(main_title).doWrite(lists);
|
}
|
|
/**
|
* 组装数据
|
*
|
* @param dataArray
|
* @param fields
|
* @return
|
*/
|
private static List<List<Object>> contentData(JSONArray dataArray, List<String> fields) {
|
List<List<Object>> contentList = Lists.newArrayList();
|
dataArray.forEach(data -> {
|
JSONObject dataJson = (JSONObject) data;
|
List<Object> content = Lists.newArrayList();
|
fields.forEach(field -> {
|
content.add(dataJson.get(field));
|
});
|
contentList.add(content);
|
});
|
return contentList;
|
}
|
|
|
}
|
