1、处理合并单元格问题
2、处理$fe循环会把合并行覆盖的问题
3、处理合并单元格中放图片问题
这里用的是easypoi4.1.0
用easypoi的ExcelExportUtil.exportExcel()方法导出文件通过模板解析的方法时 碰到合并单元格就会
TemplateExportParams templateExportParams = new TemplateExportParams(templateFileName);
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, map);
FileOutputStream fos = new FileOutputStream(xlsFileName);
workbook.write(fos);
fos.close();
效果：
重写ExcelExportOfTemplateUtil类的
addListDataToExcel方法改动：
/**
* 利用foreach循环输出数据
*
* @param cell
* @param map
* @param name
* @throws Exception
*/
private void addListDataToExcel(Cell cell, Map<String, Object> map,
String name) throws Exception {
boolean isCreate = !name.contains(FOREACH_NOT_CREATE);
boolean isShift = name.contains(FOREACH_AND_SHIFT);
name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY)
.replace(FOREACH, EMPTY).replace(START_STR, EMPTY);
String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY),
mergedRegionHelper);
if (datas == null) {
return;
}
Iterator<?> its = datas.iterator();
int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
@SuppressWarnings("unchecked")
List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
Row row = null;
int rowIndex = cell.getRow().getRowNum();
//处理当前行
/*if (its.hasNext()) {
Object t = its.next();
setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map,
rowspan, colspan, mergedRegionHelper);
rowIndex += rowspan - 1;
}
//修复不论后面有没有数据,都应该执行的是插入操作
if (isShift && datas.size() * rowspan > 1 && cell.getRowIndex() + rowspan < cell.getRow().getSheet().getLastRowNum()) {
int lastRowNum = cell.getRow().getSheet().getLastRowNum();
cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum, (datas.size() - 1) * rowspan, true, true);
mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan, cell.getRow().getSheet().getLastRowNum());
}
while (its.hasNext()) {
Object t = its.next();
row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan,
colspan, mergedRegionHelper);
rowIndex += rowspan;
}*/
// 定义循环开始行号
int startRowNum = cell.getRow().getRowNum();
// 定义循环结束行号
int endRowNum = 0;
/*while (its.hasNext()) {
Object t = its.next();
row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan,
colspan, mergedRegionHelper, startRowNum);
rowIndex += rowspan;
// 每次创建行后，重新给循环结束行号赋值
endRowNum = row.getRowNum();
}*/
//修复不论后面有没有数据,都应该执行的是插入操作
if (isShift && datas.size() * rowspan > 1 && cell.getRowIndex() + rowspan < cell.getRow().getSheet().getLastRowNum()) {
int lastRowNum = cell.getRow().getSheet().getLastRowNum();
cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum, (datas.size() - 1) * rowspan, true, true);
mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan, cell.getRow().getSheet().getLastRowNum());
}
while (its.hasNext()) {
Object t = its.next();
row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan,
colspan, mergedRegionHelper, startRowNum);
rowIndex += rowspan;
// 每次创建行后，重新给循环结束行号赋值
endRowNum = row.getRowNum();
}
// 如果新创建行了
// 合并循环左侧竖向单元格
if (endRowNum != 0) {
Sheet sheet = cell.getRow().getSheet();
int sheetMergeCount = sheet.getNumMergedRegions();
int columnIndex = cell.getColumnIndex();
for (int i = 0; i < sheetMergeCount; ++i) {
CellRangeAddress ca = sheet.getMergedRegion(i);
//int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
for (int ii = 0; ii < columnIndex; ii++) {
if (firstRow <= startRowNum && lastRow > startRowNum && lastColumn == ii) {
ca.setLastRow(lastRow + (endRowNum - startRowNum));
}
}
}
}
}
setForEeachRowCellValue方法：
private void setForEeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t,
List<ExcelForEachParams> columns, Map<String, Object> map,
int rowspan, int colspan,
MergedRegionHelper mergedRegionHelper, int startRowNum) throws Exception {
//所有的cell创建一遍
for (int i = 0; i < rowspan; i++) {
int size = columns.size();//判断是不是超出设置了
for (int j = columnIndex, max = columnIndex + colspan; j < max; j++) {
if (row.getCell(j) == null) {
row.createCell(j);
CellStyle style = row.getRowNum() % 2 == 0
? getStyles(false,
size <= j - columnIndex ? null : columns.get(j - columnIndex))
: getStyles(true,
size <= j - columnIndex ? null : columns.get(j - columnIndex));
//返回的styler不为空时才使用,否则使用Excel设置的,更加推荐Excel设置的样式
if (style != null) {
row.getCell(j).setCellStyle(style);
}
}
}
if (i < rowspan - 1) {
row = row.getSheet().getRow(row.getRowNum() + 1);
}
}
//填写数据
ExcelForEachParams params;
row = row.getSheet().getRow(row.getRowNum() - rowspan + 1);
for (int k = 0; k < rowspan; k++) {
int ci = columnIndex;
short high = columns.get(0).getHeight();
int n = k;
while (n > 0) {
if (columns.get(n * colspan).getHeight() == 0) {
n--;
} else {
high = columns.get(n * colspan).getHeight();
break;
}
}
row.setHeight(high);
for (int i = 0; i < colspan && i < columns.size(); i++) {
boolean isNumber = false;
params = columns.get(colspan * k + i);
tempCreateCellSet.add(row.getRowNum() + "_" + (ci));
if (params == null) {
continue;
}
if (StringUtils.isEmpty(params.getName())
&& StringUtils.isEmpty(params.getConstValue())) {
row.getCell(ci).setCellStyle(params.getCellStyle());
ci = ci + params.getColspan();
continue;
}
String val = null;
Object obj = null;
//是不是常量
if (StringUtils.isEmpty(params.getName())) {
val = params.getConstValue();
} else {
String tempStr = new String(params.getName());
if (isNumber(tempStr)) {
isNumber = true;
tempStr = tempStr.replaceFirst(NUMBER_SYMBOL, "");
}
map.put(teplateParams.getTempParams(), t);
obj = eval(tempStr, map);
val = obj.toString();
}
if (obj != null && obj instanceof ImageEntity) {
ImageEntity img = (ImageEntity) obj;
row.getCell(ci).setCellValue("");
if (img.getRowspan() > 1 || img.getColspan() > 1) {
img.setHeight(0);
row.getCell(ci).getSheet().addMergedRegion(new CellRangeAddress(row.getCell(ci).getRowIndex(),
row.getCell(ci).getRowIndex() + img.getRowspan() - 1, row.getCell(ci).getColumnIndex(), row.getCell(ci).getColumnIndex() + img.getColspan() - 1));
}
this.createImageCell(row.getCell(ci), img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(), img.getData());
} else if (isNumber && StringUtils.isNotEmpty(val)) {
row.getCell(ci).setCellValue(Double.parseDouble(val));
} else {
try {
row.getCell(ci).setCellValue(val);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
}
}
if (params.getCellStyle() != null) {
row.getCell(ci).setCellStyle(params.getCellStyle());
}
//判断这个属性是不是需要统计
if (params.isNeedSum()) {
templateSumHandler.addValueOfKey(params.getName(), val);
}
//如果合并单元格,就把这个单元格的样式和之前的保持一致
setMergedRegionStyle(row, ci, params);
//合并对应单元格
if ((params.getRowspan() != 1 || params.getColspan() != 1)
// 存在合并单元格时，这个判断出问题了，需要注释
//&& !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)
// 将第二个参数改为：循环开始行号
// 原因：这个方法原先是判断当前行这一列，是否需要合并单元格
// 如果是新创建的行，这个方法恒定返回 false ，判断出现问题
// 所以需要改为：判断循环开始行这一列，是否需要合并单元格
&& PoiCellUtil.isMergedRegion(row.getSheet(), startRowNum, ci)
) {
PoiMergeCellUtil.addMergedRegion(row.getSheet(), row.getRowNum(),
row.getRowNum() + params.getRowspan() - 1, ci,
ci + params.getColspan() - 1);
}
ci = ci + params.getColspan();
}
row = row.getSheet().getRow(row.getRowNum() + 1);
}
}
重写createImageCell方法
@Override
public void createImageCell(Cell cell, double height, int rowspan, int colspan, String imagePath, byte[] data) throws Exception {
if(height > (double)cell.getRow().getHeight()) {
cell.getRow().setHeight((short)((int)height));
}
//获取当前单元格所在的sheet
Sheet sheet = cell.getRow().getSheet();
//获取当前sheet页中的所有合并单元格信息
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
//获取当前单元格的开始列号
int firstColumn = (short)cell.getColumnIndex();
//获取当前单元格的开始行号
int firstRow = cell.getRow().getRowNum();
//获取当前单元格的结束列号
int lastColumn = (short)(cell.getColumnIndex());
//获取当前单元格的结束行号
int lastRow = cell.getRow().getRowNum();
for(CellRangeAddress mergedRegion : mergedRegions){
//判断当前单元格是否包含合并行或和并列 当前单元格的所有行号和列号都包含在合并域内 则认为当前单元格存在合并行或和并列
if(cell.getColumnIndex()>=mergedRegion.getFirstColumn()
&& cell.getColumnIndex()<=mergedRegion.getLastColumn()
&& cell.getRow().getRowNum()>=mergedRegion.getFirstRow()
&& cell.getRow().getRowNum()<=mergedRegion.getLastRow()){
//获取合并域的开始行号
firstRow = mergedRegion.getFirstRow();
//获取合并域的结束行号
lastRow = mergedRegion.getLastRow();
//获取合并域的开始列号
firstColumn = mergedRegion.getFirstColumn();
//获取合并域的结束列号
lastColumn = mergedRegion.getLastColumn();
break;
}
}
Object anchor;
if(this.type.equals(ExcelType.HSSF)) {
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)firstColumn, firstRow, (short)(lastColumn+1), lastRow+1);
} else {
anchor = new XSSFClientAnchor(0, 0, 0, 0, (short)firstColumn, firstRow, (short)(lastColumn+1), lastRow+1);
}
if(StringUtils.isNotEmpty(imagePath)) {
data = ImageCache.getImage(imagePath);
}
if(data != null) {
PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture((ClientAnchor)anchor, cell.getSheet().getWorkbook().addPicture(data, this.getImageType(data)));
}
}
2034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
