com.gccloud.gcpaas.ooxml.excel.ExcelWriter Maven / Gradle / Ivy
The newest version!
package com.gccloud.gcpaas.ooxml.excel;
import com.gccloud.gcpaas.ooxml.excel.bean.ExcelExportParam;
import com.gccloud.gcpaas.ooxml.excel.bean.ExcelSheet;
import com.gccloud.gcpaas.ooxml.excel.bean.WaterMark;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.List;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;
/**
* 基于Open XML 规范些Excel
*/
public class ExcelWriter {
private static final Logger log = LoggerFactory.getLogger(ExcelWriter.class);
/**
* 定义列名，格式： A1,B1,C1.....AA1,AB1,AC1...BA1,BB1,BC1....
*/
private static final String[] COL_NAMES = new String[702];
/**
* 字符串共享map，key：唯一字符串，val: 该值在 xl/sharedStrings.xml文件中的索引
*/
private Map sharedStrMap = new HashMap<>();
/**
* 记录 sharedStrMap 中 val 的值索引
*/
private Integer sharedStrIndex = 0;
/**
* 字符串共享有序集合，按照顺序写入 xl/sharedStrings.xml 文件中
*/
private List sharedList = new ArrayList<>();
/**
* 待写入的sheet
*/
private Map sheetMap = new HashMap<>();
/**
* 缓存工作目录
*/
private String tmpDir;
/**
* 水印
*/
private WaterMark waterMark;
/**
* 操作写入 sharedStrings.xml 文件
*/
private FileWriter sharedStringsXmlWriter;
/**
* excel参数
*/
private ExcelExportParam excelParam;
static {
// 初始化列
char[] alpha = new char[26];
char c = 'A';
for (int i = 0; i < 26; i++) {
alpha[i] = c;
c++;
COL_NAMES[i] = alpha[i] + "";
}
int index = 26;
for (int i = 0; i < 26; i++) {
for (int j = 0; j < 26; j++) {
COL_NAMES[index] = alpha[i] + "" + alpha[j];
index++;
}
}
}
public ExcelWriter(ExcelExportParam excelParam, WaterMark waterMark) {
Assert.isTrue(excelParam != null, "excelParam不允许为空");
this.excelParam = excelParam;
this.waterMark = waterMark;
}
/**
* 写入一行数据
*
* @param sheetIndex sheet索引，从1开始
* @param singleRow 按照列的顺序存放
* @throws IOException
*/
public void write(int sheetIndex, String[] singleRow) throws IOException {
if (tmpDir == null) {
// 复制模板
tmpDir = excelParam.getTemplateExcelDir() + File.separator + UUID.randomUUID();
log.info("导出的缓存目录为【{}】", tmpDir);
File templateExcelFile = new File(excelParam.getTemplateExcelDir() + File.separator + "template.xlsx");
if (!templateExcelFile.exists()) {
// 从resource目录下复制
try (InputStream is = ExcelWriter.class.getClassLoader().getResourceAsStream("template.xlsx")) {
// 修改复制的路径
FileOutputStream fos = new FileOutputStream(templateExcelFile);
// 直接复制
IOUtils.copy(is, fos);
} catch (Exception e) {
log.error(ExceptionUtils.getStackTrace(e));
}
}
FileUtils.copyFile(templateExcelFile, new File(tmpDir + File.separator + "template.xlsx"));
}
ExcelSheet currentSheet = sheetMap.get(sheetIndex);
if (currentSheet == null) {
// 新建一个sheet
currentSheet = new ExcelSheet();
currentSheet.setIndex(sheetIndex);
currentSheet.setName("sheet" + sheetIndex);
sheetMap.put(sheetIndex, currentSheet);
String sheetFileName = tmpDir + File.separator + "sheet" + sheetIndex + ".xml";
File sheetFile = new File(sheetFileName);
if (!sheetFile.exists()) {
sheetFile.createNewFile();
}
FileWriter fileWriter = new FileWriter(sheetFileName);
currentSheet.setSheetXmlWriter(fileWriter);
fileWriter.write("\n" +
"");
}
StringBuilder rowBuilder = new StringBuilder();
rowBuilder.append(" ");
for (int col = 0; col < singleRow.length; col++) {
String cellVal = singleRow[col];
if (currentSheet.getCurrentRowIndex() == 1) {
// 设置表头
rowBuilder.append(" ");
} else {
rowBuilder.append(" ");
}
Integer index = sharedStrMap.computeIfAbsent(cellVal, (k) -> {
sharedList.add(cellVal);
return sharedStrIndex++;
});
rowBuilder.append("" + index + " ");
}
currentSheet.resetMaxCol(singleRow.length);
currentSheet.incrementCurrentRowIndex();
rowBuilder.append("
");
currentSheet.getSheetXmlWriter().write(rowBuilder.toString());
}
/**
* 清空对象
*/
public void clear() {
sharedStrMap.clear();
sharedList.clear();
sheetMap.clear();
}
/**
* 结束，最后必须调用，将数据写入Excel
*
* @return 导出的excel文件
* @throws IOException
*/
public File finish() throws IOException {
if (waterMark != null && StringUtils.isNotBlank(waterMark.getText())) {
// 添加水印
generateWaterMarkImg();
// 遍历所有的sheet、写入水印
for (ExcelSheet sheet : sheetMap.values()) {
sheet.getSheetXmlWriter().write(" ");
sheet.getSheetXmlWriter().close();
log.info("生成【{}】文件", tmpDir + File.separator + "sheet" + sheet.getIndex() + ".xml");
}
}
String sharedStringsFileName = tmpDir + File.separator + "sharedStrings.xml";
File sharedStringsFile = new File(sharedStringsFileName);
if (!sharedStringsFile.exists()) {
sharedStringsFile.createNewFile();
}
long time1 = System.currentTimeMillis();
sharedStringsXmlWriter = new FileWriter(sharedStringsFileName);
// 计算多个sheet总单元格个数
Integer totalCellCount = 0;
for (ExcelSheet sheet : sheetMap.values()) {
int count = (sheet.getTotalRow() - 1) * sheet.getMaxCol();
totalCellCount += count;
}
sharedStringsXmlWriter.write("\n");
for (String val : sharedList) {
sharedStringsXmlWriter.write("" + val + " ");
sharedStringsXmlWriter.close();
log.info("生成【sharedStrings.xml】文件耗时: {} ms", System.currentTimeMillis() - time1);
// 写入压缩包
archive();
// 删除生成的缓存文件
new File(tmpDir + File.separator + "template.xlsx").deleteOnExit();
new File(tmpDir + File.separator + "sharedStrings.xml").deleteOnExit();
// 删除缓存的sheet.xml
for (Integer sheetIndex : sheetMap.keySet()) {
new File(tmpDir + File.separator + "sheet" + sheetIndex + ".xml").deleteOnExit();
}
new File(tmpDir + File.separator + "image1.png").deleteOnExit();
// 删除缓存数据
sharedList.clear();
sharedStrMap.clear();
return new File(tmpDir + File.separator + excelParam.getOutFileName() + ".xlsx");
}
private void archive() {
long time1 = System.currentTimeMillis();
try {
// 读取Excel模板文件
ZipInputStream templateExcelZipIs = new ZipInputStream(new FileInputStream(tmpDir + File.separator + "template.xlsx"));
// 待写入的Excel文件
ZipOutputStream outputExcelOs = new ZipOutputStream(new FileOutputStream(new File(tmpDir + File.separator + excelParam.getOutFileName() + ".xlsx")));
// 写入文件的缓存大小
byte[] buffer = new byte[1024 * 1024 * 10];
ZipEntry entry;
while ((entry = templateExcelZipIs.getNextEntry()) != null) {
long compressStart = System.currentTimeMillis();
if (entry.getName().equals("xl/media/image1.png")) {
if (waterMark != null && StringUtils.isNotBlank(waterMark.getText())) {
// 写入水印
ZipEntry imageEntry = new ZipEntry("xl/media/image1.png");
outputExcelOs.putNextEntry(imageEntry);
FileInputStream imageFileInputStream = new FileInputStream(tmpDir + File.separator + "image1.png");
int len;
while ((len = imageFileInputStream.read(buffer)) > 0) {
outputExcelOs.write(buffer, 0, len);
}
imageFileInputStream.close();
}
} else if (entry.getName().startsWith("xl/worksheets/sheet")) {
// 循环写入sheet
for (ExcelSheet sheet : sheetMap.values()) {
ZipEntry sheet1Entry = new ZipEntry("xl/worksheets/sheet" + sheet.getIndex() + ".xml");
outputExcelOs.putNextEntry(sheet1Entry);
int len;
FileInputStream sheetFis = new FileInputStream(tmpDir + File.separator + "sheet" + sheet.getIndex() + ".xml");
boolean head = true;
while ((len = sheetFis.read(buffer)) > 0) {
if (head) {
String line = new String(buffer, 0, len, "utf-8");
// 解决预先不知道数据有多少行和列问题
String newLine = line.replace("#DIMENSION#", "");
int end = content.indexOf(" ");
String oldSheet = content.substring(start, end);
String newSheet = "";
for (ExcelSheet sheet : sheetMap.values()) {
newSheet += "", start) + "/>".length();
String oldStr = content.substring(start, end);
if (oldStr.contains("worksheets/sheet")) {
// 添加sheet关联
String newStr = "";
for (ExcelSheet sheet : sheetMap.values()) {
newStr += "";
}
String newContent = content.replaceAll(oldStr, newStr);
outputExcelOs.write(newContent.getBytes("utf-8"));
break;
}
start = end;
}
} else if (entry.getName().startsWith("xl/worksheets/_rels/sheet")) {
// 循环写入sheet
for (ExcelSheet sheet : sheetMap.values()) {
ZipEntry sheet1Entry = new ZipEntry("xl/worksheets/_rels/sheet" + sheet.getIndex() + ".xml.rels");
outputExcelOs.putNextEntry(sheet1Entry);
String content = "";
content += "";
content += " ";
outputExcelOs.write(content.getBytes("utf-8"));
}
} else {
copyCurrentFileToZip(entry.getName(), templateExcelZipIs, outputExcelOs);
}
log.info("文件【{}】读取及插入压缩包耗时 {} ms", entry.getName(), System.currentTimeMillis() - compressStart);
outputExcelOs.closeEntry();
}
buffer = null;
outputExcelOs.close();
} catch (IOException e) {
log.error("写入压缩包失败", e);
}
log.info("生成压缩包 【{}.xlsx】 文件耗时 {} ms", excelParam.getOutFileName(), System.currentTimeMillis() - time1);
}
/**
* 从一个压缩包中复制文件到另一个压缩包中
*
* @param fileName
* @param zis
* @param zos
* @throws IOException
*/
private void copyCurrentFileToZip(String fileName, ZipInputStream zis, ZipOutputStream zos) throws IOException {
ZipEntry zipEntry = new ZipEntry(fileName);
zos.putNextEntry(zipEntry);
int len;
byte[] buffer = new byte[1024 * 1024 * 10];
while ((len = zis.read(buffer)) > 0) {
zos.write(buffer, 0, len);
}
buffer = null;
}
/**
* 读取压缩包中当前选中的文件内容
*
* @param zis
* @return
* @throws IOException
*/
private String readCurrentFileFromZip(ZipInputStream zis) throws IOException {
byte[] buffer = new byte[1024 * 1024 * 10];
int len;
String str = "";
while ((len = zis.read(buffer)) > 0) {
str = str + new String(buffer, 0, len, "utf-8");
}
return str;
}
/**
* 生成水印图
*
* @throws IOException
*/
private void generateWaterMarkImg() throws IOException {
long start = System.currentTimeMillis();
int width = waterMark.getWidth();
int height = waterMark.getHeight();
// 获取bufferedImage对象
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
// 获取Graphics2d对象
Graphics2D g2d = image.createGraphics();
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
// 设置字体颜色和透明度，最后一个参数为透明度
g2d.setColor(waterMark.getFontColor());
// 设置字体类型 加粗 大小
g2d.setStroke(new BasicStroke(1));
// 设置字体
Font font = new Font("微软雅黑", Font.BOLD, waterMark.getFontSize());
g2d.setFont(font);
// 设置倾斜度
g2d.rotate(-0.3, (double) image.getWidth() / 2, (double) image.getHeight() / 2);
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(waterMark.getText(), context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
g2d.drawString(waterMark.getText(), (int) x, (int) baseY);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
ImageIO.write(image, "png", new File(tmpDir + File.separator + "image1.png"));
log.info("文本【{}】生成水印图耗时 {} ms", waterMark.getText(), System.currentTimeMillis() - start);
}
}