Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.gitee.beiding.template_excel.PoiUtils Maven / Gradle / Ivy
package com.gitee.beiding.template_excel;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.util.*;
public class PoiUtils {
//使用文件输入流创建一个 Excel 文件
// XSSFWorkbook wb = new XSSFWorkbook(fis);
public static void copySheet(XSSFSheet fromsheet, XSSFSheet newsheet) {
try {
if (fromsheet != null && newsheet != null) {
//设置打印参数
newsheet.setMargin(HSSFSheet.TopMargin, fromsheet.getMargin(HSSFSheet.TopMargin));// 页边距（上）
newsheet.setMargin(HSSFSheet.BottomMargin, fromsheet.getMargin(HSSFSheet.BottomMargin));// 页边距（下）
newsheet.setMargin(HSSFSheet.LeftMargin, fromsheet.getMargin(HSSFSheet.LeftMargin));// 页边距（左）
newsheet.setMargin(HSSFSheet.RightMargin, fromsheet.getMargin(HSSFSheet.RightMargin));// 页边距（右
XSSFPrintSetup ps = newsheet.getPrintSetup();
ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)
ps.setVResolution((short) 600);
ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型
}
} catch (Exception e) {
e.printStackTrace();
}
}
//处理合并的单元格
public static void handleMergedRegions(XSSFSheet fromsheet, XSSFSheet newsheet) {
int firstrow = fromsheet.getFirstRowNum();
int lastrow = fromsheet.getLastRowNum();
//拷贝合并的单元格
for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
CellRangeAddress region = fromsheet.getMergedRegion(i);
if ((region.getFirstRow() >= firstrow) && (region.getLastRow() <= lastrow)) {
newsheet.addMergedRegion(region);
}
}
}
//处理列的宽度
public static void handleColumnWidth(XSSFSheet fromsheet, XSSFSheet newsheet) {
int firstrow = fromsheet.getFirstRowNum();
int lastrow = fromsheet.getLastRowNum();
for (int i = firstrow; i <= lastrow; i++) {
XSSFRow fromRow = fromsheet.getRow(i);
if (fromRow != null) {
for (int j = fromRow.getLastCellNum() + 1; j >= fromRow.getFirstCellNum(); j--) {
int colnum = fromsheet.getColumnWidth((short) j);
if (colnum > 100) {
newsheet.setColumnWidth((short) j, (short) colnum);
}
if (colnum == 0) {
newsheet.setColumnHidden((short) j, true);
} else {
newsheet.setColumnHidden((short) j, false);
}
}
break;
}
}
}
private static List emptyList = Collections.emptyList();
/*
自动添加跨行
*/
public static void copySheetDataWithData(XSSFSheet fromsheet, XSSFSheet newsheet, Map>> data) {
if (data == null) {
data = emptyMap;
}
int lastrow = fromsheet.getLastRowNum();
int currentLine = 0;
List addresses = fromsheet.getMergedRegions();
List mergedRegions = newsheet.getMergedRegions();
for (int i = 0; i < mergedRegions.size(); i++) {
newsheet.removeMergedRegion(0);
}
/* for (CellRangeAddress address : addresses) {
System.out.println(address.getFirstRow() + "->" + address.getLastRow());
}
*/
//并没有起到去重的效果
List cellRangeAddresses = new ArrayList<>();
Map> templateCellRangeAddress = handleTemplateCellRangeAddress(mergedRegions);
//上一行的合并
List preList = emptyList;
//上一行的数据
Map pre = null;
// 拷贝行并填充数据
for (int i = 0; i <= lastrow; i++) {//当切换数据源时,null值有必要进行处理吗?
preList = emptyList;
pre = null;
XSSFRow fromRow = fromsheet.getRow(i);
List> maps = data.get(i);
//不需要延伸或平移
if (maps == null || maps.size() == 0) {
currentLine++;
//
preList = emptyList;
pre = null;
//如果模板行存在就只复制样式
if (fromRow != null) {
XSSFRow row = newsheet.createRow(currentLine);
copyStyle(fromRow, row, 0, fromRow.getLastCellNum());
}
continue;
}
//首先找到当前行列
//不需要延伸或平移
Map map = maps.get(0);
//首行判断有误???
int firstRow = currentLine;
int row = currentLine++;
//找到模板行
List template = templateCellRangeAddress.get(i);
if (template == null) {
template = emptyList;
}
XSSFRow newRow = newsheet.createRow(row);
preList = copyRowWithDataOrMerge(fromRow, newRow, map, pre, template, preList, true);
for (CellRangeAddress cellAddresses : preList) {//相同为什么还是能够添加进去????
if (!cellRangeAddresses.contains(cellAddresses)) {
cellRangeAddresses.add(cellAddresses);
}
}
pre = map;
//需要延伸或平移
for (int j = 1; j < maps.size(); j++) {
map = maps.get(j);
moveOrStretch(preList, currentLine);
moveOrStretch(templateCellRangeAddress, currentLine);
row = currentLine++;
newRow = newsheet.createRow(row);
preList = copyRowWithDataOrMerge(fromRow, newRow, map, pre, template, preList, false);
for (CellRangeAddress cellAddresses : preList) {//相同为什么还是能够添加进去????
if (!cellRangeAddresses.contains(cellAddresses)) {
cellRangeAddresses.add(cellAddresses);
}
}
pre = map;
}
}
//TODO 模板行不会添加进所有元素内
//遍历所有跨行单元
for (CellRangeAddress address : cellRangeAddresses) {
newsheet.addMergedRegion(address);
}
}
//精确匹配左上角
private static Map> handleTemplateCellRangeAddress(List all) {
Map> map = new HashMap<>();
for (CellRangeAddress cellAddresses : all) {
//第一行
int firstRow = cellAddresses.getFirstRow();
//最后一行
int lastRow = cellAddresses.getLastRow();
for (int i = firstRow; i <= lastRow; i++) {
List addresses = map.computeIfAbsent(i, k -> new ArrayList<>());
addresses.add(cellAddresses);
}
}
for (List value : map.values()) {
value.sort((a, b) -> {
if (a.getFirstColumn() > b.getFirstRow()) {
return 1;
} else if (a.getFirstRow() < b.getFirstRow()) {
return -1;
}
return 0;
});
}
return map;
}
/*
对现有单元格,采用拓展的方式
*/
private static void moveOrStretch(List addresses, int row) {
for (CellRangeAddress address : addresses) {
if (address.getLastRow() >= row) {
address.setLastRow(address.getLastRow() + 1);
if (address.getFirstRow() > row) {
address.setFirstRow(address.getFirstRow() + 1);
}
}
}
}
private static void moveOrStretch(Map> map, int row) {
map.forEach((r, l) -> {
if (r >= row) {
moveOrStretch(l, row);
}
});
}
/*
模板行的合并单元格是不往目标sheet页中添加的,而是根据模板行进行复制
*/
private static List copyRowWithDataOrMerge(XSSFRow fromRow, XSSFRow newRow, Map data, Map pre, List templateRowCellRangeAddress, List preRowCellRangeAddress, boolean newTemplateLine) {
//设置行高
newRow.setHeight(fromRow.getHeight());
if (data == null) {
data = emptyMap;
}
if (pre == null) {
pre = emptyMap;
}
//本行中所有的跨行元素
List r = new LinkedList<>();
//查找上一行时索引
CellRangeAddressIndex templateIndex = new CellRangeAddressIndex();
CellRangeAddressIndex preIndex = new CellRangeAddressIndex();
//遍历本行中所有列 j
for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
XSSFCell fromCell = fromRow.getCell((short) j);
if (fromCell == null) {
continue;
}
Object cellData = data.get(j);
//进行特殊处理
if (cellData == null) {
//单元格合并问题
continue;
}
// System.out.println(cellData);
//判断是否有必要与上一行相同列的元素进行合并
if (pre.get(j) == cellData) {//如果合并
CellRangeAddress address = null;
//从上一行中查找是否已经存在了合并单元
if (preRowCellRangeAddress.size() > 0) {
findCellRangeAddressByCol(preRowCellRangeAddress, j, preIndex);
address = preIndex.cellAddresses;
}
if (address == null) {//不存在合并单元
//第二行
address = new CellRangeAddress(newRow.getRowNum() - 1, newRow.getRowNum(), j, j);
// System.out.println("值相同,没有在上一行找到需要的合并单元格,创建:" + cellData);
//添加进被返回的结果中
} else {//如果能够找到
// System.out.println("值相同,可以在上一行中找到合并:" + cellData);
//拓展跨行
if (address.getLastRow() < newRow.getRowNum()) {
address.setLastRow(newRow.getRowNum());
}
copyStyle(fromRow, newRow, j, address.getLastColumn());
//跳转指针
//TODO 跳转指针 首先进行样式复制
j = address.getLastColumn();
}
//将找到的或生成的跨行添加进去
r.add(address);
} else {//如果没有出现单元格合并状况
//copyCellWithData(fromCell, newRow.createCell((short) j), cellData);
//如果是新的换行模板
/* if (newTemplateLine) {
} else {
}*/
findCellRangeAddressByCol(templateRowCellRangeAddress, j, templateIndex);
CellRangeAddress cellAddresses = templateIndex.cellAddresses;
//如果模板行索引存在
if (cellAddresses != null) {//如果已经能够在上一行中找到??
findCellRangeAddressByCol(preRowCellRangeAddress, j, preIndex);
if (preIndex.cellAddresses == null) {
//依据模板行单元格合并创建本行的单元格合并
CellRangeAddress address = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum() + cellAddresses.getLastRow() - cellAddresses.getFirstRow(), cellAddresses.getFirstColumn(), cellAddresses.getLastColumn());
r.add(address);
//System.out.println("值不同,但是在模板行中存在单元格合并,但是未在上一行合并中找到合并单元" + cellData);
} else {
//TODO System.out.println("值不同,但是在模板行中存在单元格合并,在上一行中找到单元格合并" + cellData);
if (preIndex.cellAddresses.getLastRow() < newRow.getRowNum()) {
preIndex.cellAddresses.setLastRow(newRow.getRowNum());
}
r.add(preIndex.cellAddresses);
}
copyStyle(fromRow, newRow, j, cellAddresses.getLastColumn());
//TODO 跳转指针
j = cellAddresses.getLastColumn();
}
}
}
return r;
}
private static void copyStyle(XSSFRow fromRow, XSSFRow newRow, int from, int to) {
//样式并没有复制完整
for (int i = from; i <= to; i++) {
XSSFCell fromCell = fromRow.getCell(i);
if (fromCell == null) {
continue;
}
XSSFCell newCell = newRow.getCell(i);
if (newCell == null) {
newCell = newRow.createCell(i);
}
XSSFCellStyle cellStyle = (XSSFCellStyle) fromCell.getCellStyle().clone();
XSSFCellStyle style = newCell.getCellStyle();
try {
field.set(cellStyle, field.get(style));
} catch (Exception e) {
e.printStackTrace();
}
newCell.setCellStyle(cellStyle);
}
}
/*
使用列查找对应元素
*/
private static void findCellRangeAddressByCol(List cellRangeAddresses, int col, CellRangeAddressIndex index) {
index.cellAddresses = null;
//从给定的位置开始
for (int i = index.index; i < cellRangeAddresses.size(); i++) {
CellRangeAddress address = cellRangeAddresses.get(i);
if (address == null) {
continue;
}
index.index = i;
//表明已经超出了给定边界
if (address.getFirstColumn() > col) {
break;
} else if (address.getLastColumn() >= col) {
index.cellAddresses = address;
break;
}
}
}
private static class CellRangeAddressIndex {
private CellRangeAddress cellAddresses;
private int index = 0;
}
/* private static CellRangeAddress findCellRangeAddressByCol(List cellRangeAddresses, int row, int col) {
for (CellRangeAddress address : cellRangeAddresses) {
if ((address.getLastColumn() >= col && col >= address.getFirstColumn()) && (address.getLastRow() >= row && row >= address.getFirstRow())) {
return address;
}
}
return null;
}*/
//赋值一样自定义新行中的数据
public static void copyRowWithData(XSSFRow fromRow, XSSFRow newRow, Map data) {
if (data == null) {
data = emptyMap;
}
newRow.setHeight(fromRow.getHeight());
for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
XSSFCell fromCell = fromRow.getCell((short) j);
if (fromCell == null) {
continue;
}
//copyCellWithData(fromCell, newRow.createCell((short) j), data.get(j));
}
}
//TODO 需要手动处理单元格合并以及列宽
/* //处理单元格合并
handleMergedRegions(fromsheet, newsheet);
//处理列宽
handleColumnWidth(fromsheet, newsheet);
*/
public static void copySheetData(XSSFSheet fromsheet, XSSFSheet newsheet) {
int lastrow = fromsheet.getLastRowNum();
// 拷贝行并填充数据
for (int i = 0; i <= lastrow; i++) {
XSSFRow fromRow = fromsheet.getRow(i);
XSSFRow newRow = newsheet.createRow(i);
if (fromRow == null) {
continue;
}
//TODO i - firstrow 不去除空白行
copyRow(fromRow, newRow);
}
}
private static Map emptyMap = new HashMap();
private static Field field;
static {
Class c = XSSFCellStyle.class;
try {
field = c.getDeclaredField("_stylesSource");
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
field.setAccessible(true);
}
public static void copyRow(XSSFRow fromRow, XSSFRow newRow) {
newRow.setHeight(fromRow.getHeight());
for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
XSSFCell fromCell = fromRow.getCell((short) j);
if (fromCell == null) {
continue;
}
copyCell(fromCell, newRow.createCell((short) j));
}
}
public static void copyCell(XSSFCell fromCell, XSSFCell newCell) {
XSSFCellStyle cellStyle = (XSSFCellStyle) fromCell.getCellStyle().clone();
XSSFCellStyle style = newCell.getCellStyle();
try {
field.set(cellStyle, field.get(style));
} catch (Exception e) {
e.printStackTrace();
}
newCell.setCellStyle(cellStyle);
CellType cType = fromCell.getCellType();
newCell.setCellType(cType);
switch (cType) {
case STRING:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
case NUMERIC:
newCell.setCellValue(fromCell.getNumericCellValue());
break;
case FORMULA:
newCell.setCellFormula(fromCell.getCellFormula());
break;
case BOOLEAN:
newCell.setCellValue(fromCell.getBooleanCellValue());
break;
case ERROR:
newCell.setCellValue(fromCell.getErrorCellValue());
break;
default:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
}
}
//读取行列
public static Map> readSheet(XSSFSheet sheet) {
Map> map = new HashMap<>();
int lastRowNum = sheet.getLastRowNum();
int firstRowNum = sheet.getFirstRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row == null) {
continue;
}
map.computeIfAbsent(i, key -> readRow(row));
}
return map;
}
public static Map readRow(XSSFRow row) {
Map r = new HashMap<>();
short firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
for (int j = firstCellNum; j < lastCellNum; j++) {
XSSFCell fromCell = row.getCell(j);
if (fromCell == null) {
continue;
}
r.put(j, readCell(fromCell));
}
return r;
}
public static Object readCell(XSSFCell fromCell) {
if (fromCell.getCellType() == CellType.STRING) {
return fromCell.getStringCellValue();
} else if (fromCell.getCellType() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(fromCell)) {
Date dateCellValue = fromCell.getDateCellValue();
DateFormat dataFormat = Config.getDataFormat();
return dataFormat.format(dateCellValue);
} else {
return fromCell.getNumericCellValue();
}
} else if (fromCell.getCellType() == CellType.BOOLEAN) {
return fromCell.getBooleanCellValue();
} else if (fromCell.getCellType() == CellType.ERROR) {
return fromCell.getErrorCellString();
} else if (fromCell.getCellType() == CellType.FORMULA) {
return fromCell.getCellFormula();
}
return null;
}
public static XSSFWorkbook read(InputStream inputStream) throws IOException {
return new XSSFWorkbook(inputStream);
}
public static XSSFWorkbook read(File input) throws IOException {
FileInputStream inputStream = new FileInputStream(input);
XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
inputStream.close();
return sheets;
}
public static void write(XSSFWorkbook wb, OutputStream outputStream) throws IOException {
//进行包装
outputStream = new BufferedOutputStream(outputStream);
wb.write(outputStream);
outputStream.flush();
}
public static void write(XSSFWorkbook wb, File out) throws IOException {
FileOutputStream fileOutputStream = new FileOutputStream(out);
write(wb, fileOutputStream);
fileOutputStream.close();
}
}
