package com.unicom.util;
import com.unicom.groupmall.common.constants.Constants;
import com.unicom.groupmall.manager.vo.ExportExcelParamVo;
import org.apache.commons.collections4.map.CaseInsensitiveMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
/**
* @desc 说明 导出数据到Excel文件
*/
public class ExportExcelUtils {
private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
/**
* excel文件导出方法
*
* @param exportExcelParamVo
* @param response
* @throws Exception
*/
public static void export(ExportExcelParamVo exportExcelParamVo, HttpServletResponse response) throws Exception {
String sheetName = exportExcelParamVo.getSheetName();
String fileName = exportExcelParamVo.getFileName();
// 创建一个workBook，即一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 在workbook中添加一个sheet页
HSSFSheet sheet = workbook.createSheet(sheetName);
// 创建表头
createHeadRow(sheet, workbook, exportExcelParamVo);
// 设置导出数据到单元格
createExportDataRow(sheet, workbook, exportExcelParamVo);
// 根据单元格内容自动调整列宽
adjustColumnWidth(sheet, exportExcelParamVo);
// 将文件存到浏览器设置的下载位置
String filename = fileName + ".xls";
// response.addHeader("content-type", "application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(filename, "UTF-8"));
OutputStream out = response.getOutputStream();
try {
// 将数据写出去
workbook.write(out);
logger.info("数据表格导出成功，文件名称：{}", filename);
} catch (Exception e) {
logger.error("数据表格导出失败", e);
} finally {
out.flush();
out.close();
}
// FileOutputStream out = new FileOutputStream("E:/data/" + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()).toString() + ".xls");
// workbook.write(out);
}
/**
* 导出到某指定位置
* @param exportExcelParamVo
* @param filePath
* @throws Exception
*/
public static void export2Path(ExportExcelParamVo exportExcelParamVo, String filePath) throws Exception {
String sheetName = exportExcelParamVo.getSheetName();
String fileName = exportExcelParamVo.getFileName();
// 创建一个workBook，即一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 在workbook中添加一个sheet页
HSSFSheet sheet = workbook.createSheet(sheetName);
// 创建表头
createHeadRow(sheet, workbook, exportExcelParamVo);
// 设置导出数据到单元格
createExportDataRow(sheet, workbook, exportExcelParamVo);
// 根据单元格内容自动调整列宽
adjustColumnWidth(sheet, exportExcelParamVo);
// 将文件存到浏览器设置的下载位置
// String filename = fileName + ".xls";
StringBuffer filename = new StringBuffer(filePath);
filename.append(fileName).append(".xls");
OutputStream out =new FileOutputStream(filename.toString());
try {
// 将数据写出去
workbook.write(out);
logger.info("数据表格导出成功，文件路径名称：{}", filename);
} catch (Exception e) {
logger.error("数据表格导出失败", e);
} finally {
out.flush();
out.close();
}
}
/**
* 导出多sheet页面到某指定位置
* @param exportExcelParamVos 导出对象，多sheet (注意：exportExcelParamVos中多个fileName需一致)
* @param filePath
* @throws Exception
*/
public static void manySheetExport2Path(List<ExportExcelParamVo> exportExcelParamVos, String filePath) throws Exception {
if (Objects.isNull(exportExcelParamVos)||exportExcelParamVos.isEmpty()){
logger.error("无需要导出的数据");
return;
}
// 创建一个workBook，即一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
String fileName=exportExcelParamVos.get(0).getFileName();
for (int i = 0; i < exportExcelParamVos.size(); i++) {
ExportExcelParamVo exportExcelParamVo = exportExcelParamVos.get(i);
String sheetName = exportExcelParamVo.getSheetName();
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(i,sheetName);
// 创建表头
createHeadRowWithTitle(sheet, workbook, exportExcelParamVo);
// 设置导出数据到单元格
createExportDataRow(sheet, workbook, exportExcelParamVo);
// 根据单元格内容自动调整列宽
adjustColumnWidth(sheet, exportExcelParamVo);
}
// 将文件存到浏览器设置的下载位置
StringBuffer filename = new StringBuffer(filePath);
filename.append(fileName).append(".xls");
OutputStream out =new FileOutputStream(filename.toString());
try {
// 将数据写出去
workbook.write(out);
logger.info("多sheet数据表格导出成功，文件路径名称：{}", filename);
} catch (Exception e) {
logger.error("多sheet数据表格导出失败", e);
} finally {
out.flush();
out.close();
}
}
/**
* 导出多sheet页面到某指定位置 带有公司签章
* @param exportExcelParamVos 导出对象，多sheet (注意：exportExcelParamVos中多个fileName需一致)
* @param filePath
* @throws Exception
*/
public static void manySheetExport2PathWithSign(List<ExportExcelParamVo> exportExcelParamVos, String filePath,String merNo,String WYMERNO, String JDMERNO) throws Exception {
if (Objects.isNull(exportExcelParamVos)||exportExcelParamVos.isEmpty()){
logger.error("无需要导出的数据");
return;
}
// 创建一个workBook，即一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
String fileName=exportExcelParamVos.get(0).getFileName();
for (int i = 0; i < exportExcelParamVos.size(); i++) {
ExportExcelParamVo exportExcelParamVo = exportExcelParamVos.get(i);
String sheetName = exportExcelParamVo.getSheetName();
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(i,sheetName);
// 创建表头
createHeadRowWithTitle(sheet, workbook, exportExcelParamVo);
// 设置导出数据到单元格
if (i==0){
//第一页 设置公司名称
if(WYMERNO.equals(merNo)){
createExportDataRowWishSign(sheet, workbook, exportExcelParamVo, Constants.WY_COMPANY_NAME,Constants.CHINAUNICOM_PAY_COMPANY_NAME);
}else{
createExportDataRowWishSign(sheet, workbook, exportExcelParamVo, Constants.JD_COMPANY_NAME,Constants.CHINAUNICOM_PAY_COMPANY_NAME);
}
}else {
createExportDataRow(sheet,workbook,exportExcelParamVo);
}
// 根据单元格内容自动调整列宽
adjustColumnWidthWithSign(sheet, exportExcelParamVo);
}
// 将文件存到浏览器设置的下载位置
StringBuffer filename = new StringBuffer(filePath);
filename.append(fileName).append(".xls");
OutputStream out =new FileOutputStream(filename.toString());
try {
// 将数据写出去
workbook.write(out);
logger.info("多sheet数据表格导出成功，文件路径名称：{}", filename);
} catch (Exception e) {
logger.error("多sheet数据表格导出失败", e);
} finally {
out.flush();
out.close();
}
}
/**
* 创建表头
*
* @param sheet sheet页
* @param workbook 工作表
* @param exportExcelParamVo 入参
*/
private static void createHeadRow(HSSFSheet sheet, HSSFWorkbook workbook, ExportExcelParamVo exportExcelParamVo) {
// 创建表头
HSSFRow row = sheet.createRow(0);
// 设置表头高度
row.setHeightInPoints(37);
// 创建表头字体样式
HSSFFont headerFont = workbook.createFont();
// 字体加粗
headerFont.setBold(true);
// 设置字体类型
headerFont.setFontName("黑体");
// 设置字体大小
headerFont.setFontHeightInPoints((short) 10);
// 创建表头单元格样式
HSSFCellStyle headStyle = workbook.createCellStyle();
// 为标题设置字体样式
headStyle.setFont(headerFont);
// 设置自动换行
// headStyle.setWrapText(true);
headStyle.setAlignment(HorizontalAlignment.CENTER);
// 创建一个居中格式
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
int index = 0;
// 为表头中每一列设置内容
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
HSSFCell cell = row.createCell(index);
cell.setCellValue(entry.getKey());
cell.setCellStyle(headStyle);
index++;
}
}
/**
* 创建带有title的表头
*
* @param sheet sheet页
* @param workbook 工作表
* @param exportExcelParamVo 入参
*/
private static void createHeadRowWithTitle(HSSFSheet sheet, HSSFWorkbook workbook, ExportExcelParamVo exportExcelParamVo) {
if (StringUtils.isBlank(exportExcelParamVo.getTitle())){
createHeadRow(sheet, workbook, exportExcelParamVo);
return;
}
// 创建表头
HSSFRow row0 = sheet.createRow(0);
//创建title
cteateTitle(row0, sheet, workbook, exportExcelParamVo);
// 创建表头
HSSFRow row1 = sheet.createRow(1);
// 设置表头高度
row1.setHeightInPoints(37);
// 创建表头字体样式
HSSFFont headerFont = workbook.createFont();
// 字体加粗
headerFont.setBold(true);
// 设置字体类型
headerFont.setFontName("黑体");
// 设置字体大小
headerFont.setFontHeightInPoints((short) 10);
// 创建表头单元格样式
HSSFCellStyle headStyle = workbook.createCellStyle();
// 为标题设置字体样式
headStyle.setFont(headerFont);
// 设置自动换行
// headStyle.setWrapText(true);
headStyle.setAlignment(HorizontalAlignment.CENTER);
// 创建一个居中格式
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
int index = 0;
// 为表头中每一列设置内容
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
HSSFCell cell = row1.createCell(index);
cell.setCellValue(entry.getKey());
cell.setCellStyle(headStyle);
index++;
}
}
/**
* @param row
* @param sheet
* @param workbook
* @param exportExcelParamVo
*/
private static void cteateTitle(HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook, ExportExcelParamVo exportExcelParamVo) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); //居中
// 创建一个居中格式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
createCell(row,exportExcelParamVo.getTitle(),cellStyle);
//合并单元格
mergeCells(sheet, 0, 0, 0, exportExcelParamVo.getColumnNameMap().size() - 1);
}
/**
* 合并单元格
* @param sheet 所在的sheet
* @param startRow 开始行
* @param endRow 结束行
* @param startCell 开始列
* @param endCell 结束列
*/
public static void mergeCells(Sheet sheet,int startRow,int endRow,int startCell,int endCell){
sheet.addMergedRegion(new CellRangeAddress(startRow,endRow,startCell,endCell));
}
/**
* 补一行
*
* @param row 行数
* @param titleName 表格第一行需要居中显示的文字
* @return
*/
private static HSSFCell createCell(HSSFRow row, String titleName, HSSFCellStyle cellStyle) {
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
fillCellWithValue(cell, titleName);
return cell;
}
/**
* 填用value值填充cell小格子
*
* @param cell 格子
* @param value 待填数据
*/
private static void fillCellWithValue(HSSFCell cell, String value) {
cell.setCellValue(new HSSFRichTextString(value));
}
/**
* 设置导出数据到表格中
*
* @param sheet sheet页
* @param workbook 工作表
* @param exportExcelParamVo 参数
*/
private static void createExportDataRow(HSSFSheet sheet, HSSFWorkbook workbook, ExportExcelParamVo exportExcelParamVo) {
List<Object> dataList = exportExcelParamVo.getDataList();
// 如果导出数据列表为空，直接返回
if (Objects.isNull(dataList) || dataList.isEmpty()) {
return;
}
HSSFDataFormat format = workbook.createDataFormat();
// 为数据内容设置单元格样式 自动换行 上下左右居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
//设置单元格格式为文本格式
cellStyle.setDataFormat(format.getFormat("@"));
// 设置自动换行
// cellStyle.setWrapText(true);
// 上下居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 左右居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置边框
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
Map<Class<?>, CaseInsensitiveMap<String, Field>> objectClassTypeMap = new HashMap<>();
for (int i = 0; i < dataList.size(); i++) {
Object itemObject = dataList.get(i);
if (Objects.isNull(itemObject)) {
continue;
}
HSSFRow row;
// 创建行
if (StringUtils.isBlank(exportExcelParamVo.getTitle())) {
row = sheet.createRow(i + 1);
} else {
row = sheet.createRow(i + 2); //需要写title 所以向下移一行
}
if (itemObject instanceof Map) {
// map转换
Map itemMap = itemMapConvert((Map) itemObject);
int index = 0;
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
// 创建单元格
HSSFCell dataCell = row.createCell(index);
Object valueObj = itemMap.get(entry.getValue());
dataCell.setCellValue(Objects.isNull(valueObj) ? "" : String.valueOf(valueObj));
dataCell.setCellStyle(cellStyle);
index++;
}
// 取消对向前明细的引用，以方便尽快回收内存
dataList.set(i, null);
continue;
}
// 非map的值对象使用反射获取字段值
// 获取当前对象对应类的反射信息
CaseInsensitiveMap<String, Field> fieldMap = objectClassTypeMap.get(itemObject.getClass());
if (Objects.isNull(fieldMap)) {
fieldMap = getFieldMap(itemObject);
objectClassTypeMap.put(itemObject.getClass(), fieldMap);
}
int index = 0;
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
HSSFCell dataCell = row.createCell(index);
dataCell.setCellStyle(cellStyle);
Field field = fieldMap.get(entry.getValue());
if (Objects.isNull(field)) {
// 如果数据明细中没有当前字段，则设置为空
logger.error("获取" + entry.getValue() + "字段值失败");
dataCell.setCellValue("");
continue;
}
try {
Object valueObj = field.get(itemObject);
dataCell.setCellValue(Objects.isNull(valueObj) ? "" : String.valueOf(valueObj));
} catch (Exception e) {
logger.error("获取" + field.getName() + "字段值失败", e);
}
index++;
}
// 取消对向前明细的引用，以方便尽快回收内存
dataList.set(i, null);
}
}
/**
* 设置导出带有签名单数据到表格中
*
* @param sheet sheet页
* @param workbook 工作表
* @param exportExcelParamVo 参数
*/
private static void createExportDataRowWishSign(HSSFSheet sheet, HSSFWorkbook workbook, ExportExcelParamVo exportExcelParamVo, String company1, String company2) {
if (StringUtils.isAnyBlank(company1, company2)) {
createExportDataRow(sheet, workbook, exportExcelParamVo);
return;
}
List<Object> dataList = exportExcelParamVo.getDataList();
// 如果导出数据列表为空，直接返回
if (Objects.isNull(dataList) || dataList.isEmpty()) {
return;
}
HSSFRow lastrow1; //倒数第一行
HSSFRow lastrow2; //倒数第二行
HSSFFont lastFont = workbook.createFont();
HSSFCellStyle lastStyle = workbook.createCellStyle();
lastFont.setBold(true);
lastFont.setFontHeightInPoints((short) 12);
lastStyle.setFont(lastFont);
lastStyle.setAlignment(HorizontalAlignment.CENTER);
lastStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle lastStyle1 = workbook.createCellStyle();
lastStyle1.setFont(lastFont);
lastStyle1.setAlignment(HorizontalAlignment.RIGHT);
lastStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFDataFormat format = workbook.createDataFormat();
// 为数据内容设置单元格样式 自动换行 上下左右居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
//设置单元格格式为文本格式
cellStyle.setDataFormat(format.getFormat("@"));
// 设置自动换行
// cellStyle.setWrapText(true);
// 上下居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 左右居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置边框
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
Map<Class<?>, CaseInsensitiveMap<String, Field>> objectClassTypeMap = new HashMap<>();
for (int i = 0; i < dataList.size(); i++) {
Object itemObject = dataList.get(i);
if (Objects.isNull(itemObject)) {
continue;
}
HSSFRow row;
// 创建行
if (StringUtils.isBlank(exportExcelParamVo.getTitle())) {
row = sheet.createRow(i + 1);
} else {
row = sheet.createRow(i + 2); //需要写title 所以向下移一行
}
if (itemObject instanceof Map) {
// map转换
Map itemMap = itemMapConvert((Map) itemObject);
int index = 0;
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
// 创建单元格
HSSFCell dataCell = row.createCell(index);
Object valueObj = itemMap.get(entry.getValue());
dataCell.setCellValue(Objects.isNull(valueObj) ? "" : String.valueOf(valueObj));
dataCell.setCellStyle(cellStyle);
index++;
}
// 取消对向前明细的引用，以方便尽快回收内存
dataList.set(i, null);
continue;
}
// 非map的值对象使用反射获取字段值
// 获取当前对象对应类的反射信息
CaseInsensitiveMap<String, Field> fieldMap = objectClassTypeMap.get(itemObject.getClass());
if (Objects.isNull(fieldMap)) {
fieldMap = getFieldMap(itemObject);
objectClassTypeMap.put(itemObject.getClass(), fieldMap);
}
int index = 0;
for (Map.Entry<String, String> entry : columnNameMap.entrySet()) {
HSSFCell dataCell = row.createCell(index);
dataCell.setCellStyle(cellStyle);
Field field = fieldMap.get(entry.getValue());
if (Objects.isNull(field)) {
// 如果数据明细中没有当前字段，则设置为空
logger.error("获取" + entry.getValue() + "字段值失败");
dataCell.setCellValue("");
continue;
}
try {
Object valueObj = field.get(itemObject);
dataCell.setCellValue(Objects.isNull(valueObj) ? "" : String.valueOf(valueObj));
} catch (Exception e) {
logger.error("获取" + field.getName() + "字段值失败", e);
}
index++;
}
// 取消对向前明细的引用，以方便尽快回收内存
dataList.set(i, null);
}
if (StringUtils.isBlank(exportExcelParamVo.getTitle())) {
lastrow2 = sheet.createRow(dataList.size() + 2);
lastrow1 = sheet.createRow(dataList.size() + 5);
} else {
lastrow2 = sheet.createRow(dataList.size() + 3); //需要写title 所以向下移一行
lastrow1 = sheet.createRow(dataList.size() + 6);
}
int cellCount = exportExcelParamVo.getColumnNameMap().size(); //每行单元格数
for (int i = 0; i < cellCount; i++) {
HSSFCell lastrow1Cell = lastrow1.createCell(i);
HSSFCell lastrow2Cell = lastrow2.createCell(i);
/*if (i == 0) { //第二个单元格，
lastrow2Cell.setCellValue("公司名称：");
lastrow1Cell.setCellValue("签章");
}
if (i == 1) { //第三个单元格，
lastrow2Cell.setCellValue(company1);
}
if (i == 2) {
mergeCells(sheet, lastrow2.getRowNum(), lastrow2.getRowNum(), 1, 4);
}
if (i == cellCount - 3) {
lastrow2Cell.setCellValue("公司名称：");
lastrow1Cell.setCellValue("签章");
}
if (i == cellCount - 2) {
lastrow2Cell.setCellValue(company2);
}
if (i == cellCount - 1) {
mergeCells(sheet, lastrow2.getRowNum(), lastrow2.getRowNum(), cellCount - 2, cellCount - 1);
}*/
if (i == 0) { //第二个单元格，
// 为数据内容设置单元格样式 自动换行 上下左右居中
/* HSSFCellStyle cellStyle1 = workbook.createCellStyle();
//设置单元格格式为文本格式
cellStyle1.setDataFormat(format.getFormat("@"));
// 设置自动换行
// cellStyle.setWrapText(true);
// 上下居中
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
// 左右居右
cellStyle.setAlignment(HorizontalAlignment.RIGHT);*/
// 设置边框
/* cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);*/
logger.info("进入");
mergeCells(sheet, lastrow2.getRowNum(), lastrow2.getRowNum(), 0, cellCount - 1);
//lastrow2Cell.setCellStyle(cellStyle1);
lastrow2Cell.setCellValue("公司名称：" + company2);
}
lastrow1Cell.setCellStyle(lastStyle);
lastrow2Cell.setCellStyle(lastStyle1);
}
}
/**
* 根据内容调整自动调整列宽
*
* @param sheet sheet页
* @param exportExcelParamVo 入参
*/
private static void adjustColumnWidth(HSSFSheet sheet, ExportExcelParamVo exportExcelParamVo) {
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
for (int i = 0; i < columnNameMap.size(); i++) {
sheet.autoSizeColumn(i,true);
}
}
/**
* 自适应列宽
* @param sheet
* @param exportExcelParamVo
*/
private static void adjustColumnWidthWithSign(HSSFSheet sheet, ExportExcelParamVo exportExcelParamVo) {
LinkedHashMap<String, String> columnNameMap = exportExcelParamVo.getColumnNameMap();
for (int i = 1; i < columnNameMap.size(); i++) {
sheet.autoSizeColumn(i,true);
}
}
/**
* 获取不区key分大小写的map
*
* @param object 入参
* @return 返回值
*/
private static CaseInsensitiveMap<String, Field> getFieldMap(Object object) {
CaseInsensitiveMap<String, Field> fieldMap = new CaseInsensitiveMap<>();
Class<?> clazz = object.getClass();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
fieldMap.put(field.getName(), field);
}
return fieldMap;
}
private static CaseInsensitiveMap<String, Object> itemMapConvert(Map<String, Object> map) {
CaseInsensitiveMap<String, Object> caseInsensitiveMap = new CaseInsensitiveMap<>();
if (Objects.isNull(map)) {
return caseInsensitiveMap;
}
for (Map.Entry<String, Object> entry : map.entrySet()) {
caseInsensitiveMap.put(entry.getKey(), entry.getValue());
}
return caseInsensitiveMap;
}
}
package com.unicom.vo;
import java.util.LinkedHashMap;
import java.util.List;
/**
* @desc 说明
*/
public class ExportExcelParamVo<T> {
/**
* 表格文件名
*/
private String fileName;
/**
* title名字，第一行居中显示
*/
private String title;
/**
* sheet页名称
*/
private String sheetName;
/**
* 表头列名与字段名对应关系
*/
private LinkedHashMap<String, String> columnNameMap;
/**
* 导出数据列表
*/
private List<T> dataList;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public LinkedHashMap<String, String> getColumnNameMap() {
return columnNameMap;
}
public void setColumnNameMap(LinkedHashMap<String, String> columnNameMap) {
this.columnNameMap = columnNameMap;
}
public List<T> getDataList() {
return dataList;
}
public void setDataList(List<T> dataList) {
this.dataList = dataList;
}
}
492
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
