导出Excel动态显示一级或二级表头
1、引人po相关i依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>2、封装导出工具类，具体编写导出工具类操作，导出数据、导出头部数据可以动态传入。
public String generateExcelWithMember() throws Exception {
// XSSFWorkbook workbook = null; //数据量小可以使用，大数据量会导致内存溢出
SXSSFWorkbook workbook = new SXSSFWorkbook(3000);
FileOutputStream output = null;
List<List<String>> dataList = new ArrayList<>();
try {
// 获取平台设置的密码,excel不可编辑密码
String passwordValue = "123456";// 可动态获取
// 获取平台水印文案描述
String watermarkValue = "EXCEL水印文案";//可动态获取
String fileName = "excel_watermark_export20201219160601.xlsx";// 文件名
String filePath = "导出路径";//导出路径
// 检查目录是否存在
File dir = new File(filePath);
FileUtils.forceMkdir(dir);
File file = new File(dir, fileName);
// 标题
Map<String, List<String>> titleMap = new HashMap<>();// 需要动态获取标题
// 内容
dataList = new ArrayList<>;//需要动态获取内容
// 权限数据
Map<Integer, Object> authSet = new HashMap<>();// 可通过数据权限控制标题是否显示
Map<Integer, Integer> sheetLinePage = new HashMap<>();
sheetLinePage.put(1, dataList.size() + 1);
List<ExcelHeader> excelHeaderList = this.getExcelHeader(apiVersion, titleMap, authSet);
CellStyle[][] cellStyleArrays = this.getCellStyle(workbook, dataList, excelHeaderList);
workbook = ExcelHelper.writeDataToExcel(2, excelHeaderList, dataList,
sheetLinePage, null, cellStyleArrays, passwordValue, watermarkValue);
output = new FileOutputStream(file);
workbook.write(output);
return filePath + File.separatorChar + fileName;
} catch (Exception ex) {
log.error(this.getClass().getName(), ex, "EXPORT_ERROR_导出异常");
throw new Exception(ex);
} finally {
CollectionUtils.recycle(dataList);// 可自行封装
try {
if (output != null) {
output.close();
}
} catch (IOException e) {
throw new Exception(e);
}
}
}
/**
* 获取标题某一列的索引
*
* @param headerList 标题
* @param columnName 列名
* @return 索引
*/
private int getHeaderIndex(List<ExcelHeader> headerList, String columnName) {
int index = NumberUtils.INTEGER_ZERO;
for (ExcelHeader excelHeader : headerList) {
List<ExcelHeader> subExcelHeaderList = excelHeader.getSubExcelHeader();
if (CollectionUtils.isNotEmpty(subExcelHeaderList)) {
for (ExcelHeader subExcelHeader : subExcelHeaderList) {
if (subExcelHeader.getName().equals(columnName)) {
return index;
}
++index;
}
} else if (excelHeader.getName().equals(columnName)) {
return index;
} else {
++index;
}
}
return NumberUtils.INTEGER_MINUS_ONE;
}
/**
* 组装单元格样式
*
* @param workbook 工作簿
* @param dataList 数据
* @param headerList 已经组装好的标题
* @return 单元格样式
*/
private CellStyle[][] getCellStyle(SXSSFWorkbook workbook, List<List<String>> dataList, List<ExcelHeader> headerList) {
int headerSize = this.getExcelHeaderColumnSize(headerList);
// 单元格背景色
CellStyle foregroundColorCellStyle = workbook.createCellStyle();
foregroundColorCellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
foregroundColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 字体颜色
CellStyle fontColorCellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex());
font.setFontHeightInPoints((short) 12);
fontColorCellStyle.setFont(font);
// 有加背景色的字段1
int buyingPriceColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.FIRST_BUYING_PRICE.getDesc());
// 有加背景色的字段12
int synthesizeCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.FIRST_SYNTHESIZE_COST.getDesc());
// 有加背景色的字段13
int synthesizeWarehouseCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.SECOND_SYNTHESIZE_WAREHOUSE_COST.getDesc());
// 有加背景色的字段4
int synthesizeSaleCostColumnIndex = this.getHeaderIndex(headerList, ResearchPricingAuthEnum.COST_THREE_SYNTHESIZE_SALE_COST.getDesc());
CellStyle[][] cellStyleArrays = new CellStyle[dataList.size()][headerSize];
if (CollectionUtils.isNotEmpty(dataList)) {
for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
List<String> rowData = dataList.get(rowIndex);
// 标红，1为标红，其他为正常颜色
if (!NumberUtils.INTEGER_MINUS_ONE.equals(buyingPriceColumnIndex)) {
String buyingPriceFlag = rowData.get(rowData.size() - Constants.ONE);
if (NumberUtils.INTEGER_ONE.toString().equals(buyingPriceFlag)) {
cellStyleArrays[rowIndex][buyingPriceColumnIndex] = fontColorCellStyle;
}
}
if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeCostColumnIndex)) {
cellStyleArrays[rowIndex][synthesizeCostColumnIndex] = foregroundColorCellStyle;
}
if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeWarehouseCostColumnIndex)) {
cellStyleArrays[rowIndex][synthesizeWarehouseCostColumnIndex] = foregroundColorCellStyle;
}
if (!NumberUtils.INTEGER_MINUS_ONE.equals(synthesizeSaleCostColumnIndex)) {
cellStyleArrays[rowIndex][synthesizeSaleCostColumnIndex] = foregroundColorCellStyle;
}
// 去掉多余的标红列，仅做字体颜色，不需要导出到excel文件
rowData.remove(rowData.size() - Constants.ONE);
}
}
return cellStyleArrays;
}
/**
* 获取标题列数
*
* @param headerList 标题
* @return 标题列数
*/
private int getExcelHeaderColumnSize(List<ExcelHeader> headerList) {
int headerSize = 0;
for (ExcelHeader excelHeader : headerList) {
headerSize += CollectionUtils.isNotEmpty(excelHeader.getSubExcelHeader()) ? excelHeader.getSubExcelHeader().size() : Constants.ONE;
}
return headerSize;
}
/**
* 获取Excel标题
*
* @return 标题
*/
protected List<ExcelHeader> getExcelHeader( Map<String, List<String>> titleMap, Map<Integer, Object> authSet) {
// 写标题
List<ExcelHeader> titleList = new ArrayList<>();
// 名称
ExcelHeader excelHeader = new ExcelHeader();
excelHeader.setName("名称");
titleList.add(excelHeader);
for (Map.Entry<String, List<String>> entry : titleMap.entrySet()) {
ExcelHeader topHeader = new ExcelHeader();
topHeader.setName(entry.getKey());
if (null != entry.getValue()) {
List<ExcelHeader> subList = new ArrayList<>();
for (String name : entry.getValue()) {
ExcelHeader subHeader = new ExcelHeader();
subHeader.setName(name);
subList.add(subHeader);
}
topHeader.setSubExcelHeader(subList);
}
titleList.add(topHeader);
}
Set<String> authFieldName = new HashSet<>();
List<String> others = Lists.newArrayList( );
// "*" 代码超级管理
Set<String> all = (Set<String>) authSet.get(NumberUtils.INTEGER_ZERO);
// 可通过权限控制列标题显示 略..
others.add(BizTypePricingEnum.GUIDE_RETAIL_PRICE.getName());
List<String> othersOperations = Lists.newArrayList( "备注", "创建人", "创建时间", "修改人", "修改时间");
others.addAll(othersOperations);
for (String name : others) {
excelHeader = new ExcelHeader();
excelHeader.setName(name);
titleList.add(excelHeader);
}
return titleList;
}/**
* @ClassName: ExcelHelper
* @Description: 导出excel文件
* @Author: Aaron-x
* @Date: 2020-12-19 16:14:00
*/
public final class ExcelHelper {
private OnionExcelHelper() {
}
/**
* 导出excel，推荐使用这个，性能较优
*
* @param beginLine 第几行开始
* @param excelHeaderList 标题
* @param dataList 数据
* @param sheetLinePage sheet
* @param sheetMergedRegion 合并区域
* @param cellStyleList 单元格style
* @return 工作簿
* @throws Exception 异常
*/
public static SXSSFWorkbook writeDataToExcel(int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage,
Map<Integer, List<CellRangeAddress>> sheetMergedRegion, List<CellStyle> cellStyleList) throws Exception {
if (MapUtils.isEmpty(sheetLinePage)) {
throw new Exception("导出出错，sheet设置错误");
} else if (CollectionUtils.isEmpty(excelHeaderList)) {
throw new Exception("导出出错，标题为空");
} else if (null == dataList) {
throw new Exception("导出出错，数据为空");
} else if (CollectionUtils.isNotEmpty(cellStyleList) && cellStyleList.size() != excelHeaderList.size()) {
throw new Exception("导出出错，列样式设置错误");
} else {
SXSSFWorkbook wb = new SXSSFWorkbook(5000);
int hasRead = 0;
for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
int sheetNum = entry.getKey();
int lineNumTotal = entry.getValue();
SXSSFSheet sxssfSheet = wb.createSheet("Sheet" + sheetNum);
createHead(excelHeaderList, sxssfSheet, wb);
int dataBeginLineNum = beginLine;
for (int d = hasRead; d < dataList.size(); ++d) {
SXSSFRow dataRow = sxssfSheet.createRow(dataBeginLineNum);
List<String> columnList = dataList.get(d);
for (int c = 0; c < columnList.size(); ++c) {
Cell cell = dataRow.createCell(c);
if (CollectionUtils.isNotEmpty(cellStyleList)) {
cell.setCellStyle(cellStyleList.get(c));
}
cell.setCellValue(columnList.get(c));
}
++hasRead;
if (dataBeginLineNum >= lineNumTotal) {
break;
}
++dataBeginLineNum;
}
if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
sxssfSheet.addMergedRegion(cellRangeAddress);
}
}
sxssfSheet.trackAllColumnsForAutoSizing();
for (short t = 0; t < excelHeaderList.size(); ++t) {
sxssfSheet.autoSizeColumn(t, true);
}
}
return wb;
}
}
/**
* 导出excel，包含excel不可编辑、水印
*
* @param beginLine 第几行开始
* @param excelHeaderList 标题
* @param dataList 数据
* @param sheetLinePage sheet
* @param sheetMergedRegion 合并区域
* @param cellStyleList 单元格style
* @param sheetPassword sheet密码，不可编辑
* @param waterMarkContent 水印文本
* @return 工作簿
* @throws Exception 异常
*/
public static XSSFWorkbook writeDataToExcel(int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage, Map<Integer,
List<CellRangeAddress>> sheetMergedRegion, List<CellStyle> cellStyleList, String sheetPassword, String waterMarkContent) throwsException {
if (apUtils.isEmpty(sheetLinePage)) {
throw new Exception("导出出错，sheet设置错误");
} else if (CollectionUtils.isEmpty(excelHeaderList)) {
throw new Exception("导出出错，标题为空");
} else if (null == dataList) {
throw new Exception("导出出错，数据为空");
} else if (CollectionUtils.isNotEmpty(cellStyleList) && cellStyleList.size() != excelHeaderList.size()) {
throw new Exception("导出出错，列样式设置错误");
} else {
XSSFWorkbook wb = new XSSFWorkbook();
int hasRead = 0;
ByteArrayOutputStream byteArrayOutputStream = null;
try {
for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
int sheetNum = entry.getKey();
int lineNumTotal = entry.getValue();
XSSFSheet sheet = wb.createSheet("Sheet" + sheetNum);
// 设置密码，不可编辑
if (StringUtils.isNotEmpty(sheetPassword)) {
sheet.protectSheet(sheetPassword);
}
// 设置水印
if (StringUtils.isNotEmpty(waterMarkContent)) {
byteArrayOutputStream = createWaterMark(waterMarkContent);
int pictureIdx = wb.addPicture(byteArrayOutputStream.toByteArray(), Workbook.PICTURE_TYPE_PNG);
String relationId = sheet.addRelation(null, XSSFRelation.IMAGES, wb.getAllPictures().get(pictureIdx)).getRelationship().getId();
sheet.getCTWorksheet().addNewPicture().setId(relationId);
}
createHead(excelHeaderList, sheet, wb);
int dataBeginLineNum = beginLine;
for (int d = hasRead; d < dataList.size(); ++d) {
Row dataRow = sheet.createRow(dataBeginLineNum);
List<String> columnList = dataList.get(d);
for (int c = 0; c < columnList.size(); ++c) {
Cell cell = dataRow.createCell(c);
if (CollectionUtils.isNotEmpty(cellStyleList)) {
cell.setCellStyle(cellStyleList.get(c));
}
cell.setCellValue(columnList.get(c));
}
++hasRead;
if (dataBeginLineNum >= lineNumTotal) {
break;
}
++dataBeginLineNum;
}
if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
for (short t = 0; t < excelHeaderList.size(); ++t) {
sheet.autoSizeColumn(t, true);
}
}
} catch (Exception e) {
throw new Exception(e);
} finally {
IOUtils.closeQuietly(byteArrayOutputStream);
}
return wb;
}
}
/**
* 导出excel，包含excel不可编辑、水印
*
* @param wb 工作簿
* @param beginLine 第几行开始
* @param excelHeaderList 标题
* @param dataList 数据
* @param sheetLinePage sheet
* @param sheetMergedRegion 合并区域
* @param cellStyleArray 单元格style
* @param sheetPassword sheet密码，不可编辑
* @param waterMarkContent 水印文本
* @return 工作簿
* @throws Exception 异常
*/
public static SXSSFWorkbook writeDataToExcel(SXSSFWorkbook wb, int beginLine, List<ExcelHeader> excelHeaderList, List<List<String>> dataList, Map<Integer, Integer> sheetLinePage, Map<Integer,
List<CellRangeAddress>> sheetMergedRegion, CellStyle[][] cellStyleArray, String sheetPassword, String waterMarkContent) throws Exception {
if (MapUtils.isEmpty(sheetLinePage)) {
throw new Exception("导出出错，sheet设置错误");
} else if (CollectionUtils.isEmpty(excelHeaderList)) {
throw new Exception("导出出错，标题为空");
} else if (null == dataList) {
throw new Exception("导出出错，数据为空");
} else if (ArrayUtils.isNotEmpty(cellStyleArray) && cellStyleArray.length != dataList.size()) {
throw new Exception("导出出错，列样式设置错误");
} else {
ByteArrayOutputStream byteArrayOutputStream = null;
try {
for (Map.Entry<Integer, Integer> entry : sheetLinePage.entrySet()) {
int sheetNum = entry.getKey();
int lineNumTotal = entry.getValue();
SXSSFSheet sheet = wb.createSheet("Sheet" + sheetNum);
// 设置密码，不可编辑
if (StringUtils.isNotEmpty(sheetPassword)) {
sheet.protectSheet(sheetPassword);
}
// 设置水印
if (StringUtils.isNotEmpty(waterMarkContent)) {
// byteArrayOutputStream = createWaterMark(waterMarkContent);
// int pictureIdx = wb.addPicture(byteArrayOutputStream.toByteArray(), Workbook.PICTURE_TYPE_PNG);
// String relationId = sheet.addRelation(null, XSSFRelation.IMAGES, wb.getAllPictures().get(pictureIdx)).getRelationship().getId();
// sheet.getCTWorksheet().addNewPicture().setId(relationId);
int xCount = Math.max(dataList.size() / 10, 10);
int yCount = Math.max(dataList.get(0).size() / 30, 30);
// 设置水印
putWaterRemarkToExcel(wb, sheet, waterMarkContent, 0, 0, 5, 5, xCount,
yCount, 10, 30);
}
createHead(excelHeaderList, sheet, wb);
int dataBeginLineNum = beginLine;
for (int rowIndex = 0; rowIndex < dataList.size(); ++rowIndex) {
Row dataRow = sheet.createRow(dataBeginLineNum);
List<String> columnList = dataList.get(rowIndex);
for (int columnIndex = 0; columnIndex < columnList.size(); ++columnIndex) {
Cell cell = dataRow.createCell(columnIndex);
if (ArrayUtils.isNotEmpty(cellStyleArray)) {
CellStyle cellStyle = cellStyleArray[rowIndex][columnIndex];
if (null != cellStyle) {
cell.setCellStyle(cellStyle);
}
}
cell.setCellValue(columnList.get(columnIndex));
}
if (dataBeginLineNum >= lineNumTotal) {
break;
}
++dataBeginLineNum;
}
if (MapUtils.isNotEmpty(sheetMergedRegion) && sheetMergedRegion.size() >= sheetNum) {
List<CellRangeAddress> cellRangeAddressList = sheetMergedRegion.get(sheetNum);
for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
sheet.trackAllColumnsForAutoSizing();
for (short t = 0; t < excelHeaderList.size(); ++t) {
sheet.autoSizeColumn(t, true);
}
}
} catch (Exception e) {
throw new Exception(e);
} finally {
IOUtils.closeQuietly(byteArrayOutputStream);
}
return wb;
}
}
/**
* 为Excel打上水印工具函数
* 请自行确保参数值，以保证水印图片之间不会覆盖。
* 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
*
* @param wb Excel Workbook
* @param sheet 需要打水印的Excel
* @param content 水印内容
* @param startXCol 水印起始列
* @param startYRow 水印起始行
* @param betweenXCol 水印横向之间间隔多少列
* @param betweenYRow 水印纵向之间间隔多少行
* @param xCount 横向共有水印多少个
* @param yCount 纵向共有水印多少个
* @param waterRemarkWidth 水印图片宽度为多少列
* @param waterRemarkHeight 水印图片高度为多少行
* @throws Exception 异常
*/
public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String content, int startXCol, int startYRow,
int betweenXCol, int betweenYRow, int xCount, int yCount,
int waterRemarkWidth, int waterRemarkHeight) throws Exception {
ByteArrayOutputStream byteArrayOut = null;
try {
byteArrayOut = createWaterMark(content);
//开始打水印
Drawing drawing = sheet.createDrawingPatriarch();
//按照共需打印多少行水印进行循环
for (int y = 0; y < yCount; y++) {
//按照每行需要打印多少个水印进行循环
for (int x = 0; x < xCount; x++) {
//创建水印图片位置
int xIndexInteger = startXCol + (x * waterRemarkWidth) + (x * betweenXCol);
int yIndexInteger = startYRow + (y * waterRemarkHeight) + (y * betweenYRow);
/*
* 参数定义：
* 第一个参数是（x轴的开始节点）；
* 第二个参数是（是y轴的开始节点）；
* 第三个参数是（是x轴的结束节点）；
* 第四个参数是（是y轴的结束节点）；
* 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
* 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
* 第七个参数是（图片宽度，共多少列）；
* 第8个参数是（图片高度，共多少行）；
*/
ClientAnchor anchor = drawing.createAnchor(0, 0, Short.MAX_VALUE, Integer.MAX_VALUE, xIndexInteger, yIndexInteger, waterRemarkWidth, waterRemarkHeight);
Picture pic = drawing.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
pic.resize();
}
}
} catch (Exception e) {
throw new Exception(e);
} finally {
IOUtils.closeQuietly(byteArrayOut);
}
}
/**
* 表头样式
*
* @param workbook 表格对象
*/
private static CellStyle headerStyle(Workbook workbook) {
CellStyle headStyle = workbook.createCellStyle();
headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 生成一个字体
Font headFont = workbook.createFont();
headFont.setBold(true);
// 把字体应用到当前的样式
headStyle.setFont(headFont);
return headStyle;
}
/**
* 创建2级表头
*
* @param excelHeaderList 表头数据
* @param sheet 表格
* @param workbook 工作簿
*/
private static void createHead(List<ExcelHeader> excelHeaderList, Sheet sheet, Workbook workbook) {
CellStyle headStyle = headerStyle(workbook);
Row row = sheet.createRow(Constants.ZERO);
Row row2 = sheet.createRow(Constants.ONE);
for (int i = 0, n = 0; i < excelHeaderList.size(); i++) {
Cell cell1 = row.createCell(n);
cell1.setCellStyle(headStyle);
// 二级表头
List<ExcelHeader> level2ExcelHeader = excelHeaderList.get(i).getSubExcelHeader();
// 一级表头名字
RichTextString topLevelName = new XSSFRichTextString(excelHeaderList.get(i).getName());
// 只有一级表头
if (ObjectUtils.isEmpty(level2ExcelHeader)) {
CellRangeAddress cra = new CellRangeAddress(0, 1, n, n);
// 单标题
sheet.addMergedRegion(cra);
// 合并表格添加边框
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
cell1.setCellValue(topLevelName);
n++;
continue;
}
cell1.setCellValue(topLevelName);
// 子标题列数大于1才需要合并，不然会报异常
if (level2ExcelHeader.size() > Constants.ONE) {
CellRangeAddress cra = new CellRangeAddress(0, 0, n, n + level2ExcelHeader.size() - 1);
// 创建一级表头标题
sheet.addMergedRegion(cra);
// 合并表格添加边框
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
}
// 创建二级表头
for (ExcelHeader excelHeader : level2ExcelHeader) {
Cell cell2 = row2.createCell(n++);
cell2.setCellStyle(headStyle);
cell2.setCellValue(new XSSFRichTextString(excelHeader.getName()));
}
}
}
/**
* 创建水印
*
* @param content 水印文本
* @return 文件流
* @throws Exception 异常
*/
private static ByteArrayOutputStream createWaterMark(String content) throws Exception {
try {
int width = 800;
int height = 750;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
String fontType = "微软雅黑";
int fontStyle = java.awt.Font.BOLD;
int fontSize = 100;
java.awt.Font font = new java.awt.Font(fontType, fontStyle, fontSize);
// 获取Graphics2d对象
Graphics2D g2d = image.createGraphics();
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
// 设置字体颜色和透明度，最后一个参数为透明度
g2d.setColor(new Color(0, 0, 0, 20));
// 设置字体
g2d.setStroke(new BasicStroke(1));
// 设置字体类型 加粗 大小
g2d.setFont(font);
// 设置倾斜度
g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(content, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double basey = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
g2d.drawString(content, (int) x, (int) basey);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write(image, "png", os);
return os;
} catch (IOException e) {
throw new Exception(e);
}
}
}导出Excel单列及二级表头+不可以编辑+excel水印操作：效果如下
双击excel单元格会弹出需要输入密码才可编辑及背景水印
!(
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
