/**
* 循环创建数据行 可设置从第几行开始
*
* @param workbook
* @param sheet
* @param dataList
* @param beanPropertys
* @param rowIndex
* @param <T>
*/
public static <T> void createDateRow(Workbook workbook, Sheet sheet, List<T> dataList, String[] beanPropertys, int rowIndex) {
if (workbook == null || sheet == null || ObjectUtils.isEmpty(dataList) || ObjectUtils.isEmpty(beanPropertys)) {
return;
}
int rowCount = rowIndex;
int maxRow = 65500;//65536
if (workbook instanceof SXSSFWorkbook) {
maxRow = 1048500;//1048576
}
for (int i = 0; i < dataList.size(); i++) {
T t = dataList.get(i);
Row row = sheet.createRow(rowCount++);
populateRow(row, t, beanPropertys);
//超过最大行时写入提示信息
if (i > maxRow) {
populatePromptRow(row, beanPropertys);
break;
}
}
String sheetName = sheet.getSheetName();
if(sheetName.equals("资源使用情况明细报表sheet")){
mergedRegion(workbook,sheet);
}
}
private static void mergedRegion(Workbook wb,Sheet sheet) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//开始查找的行
int currnetRow = 1;
// 总行数
int totalRows = sheet.getPhysicalNumberOfRows();
//总行数去掉标题
int totalRow = totalRows -1;
//记录跨行数
List<Integer> spanRows = new ArrayList<>();
//合并结束行
int t = 1;
//合并开始行
int k = 1;
for (int p = currnetRow; p <= totalRow; p++) {
Cell currentCell = sheet.getRow(p).getCell(0);
String current = getStringCellValue(currentCell);
Cell nextCell = null;
String next = "";
if(p < totalRow+2){
Row nextRow = sheet.getRow(p+1);
if(nextRow != null){
nextCell = nextRow.getCell(0);
next = getStringCellValue(nextCell);
}else{
next = "";
}
}else{
next = "";
}
if(current.equals(next)){
// t++;
continue;
}else{
// spanRows.add(t+1);
//要合并的列
if(k != p){
for(int j = 0;j < 11 ;j++){
// //for(Map.Entry<Integer, Integer> entry:positions.entrySet()){
// // 起始行, 终止行, 起始列, 终止列
// CellRangeAddress cra =new CellRangeAddress(k,t+1, j, j);
// sheet.addMergedRegion(cra);
// //}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(k, p, j, j));
}
}
//currnetRow = p + 1;
k = p + 1;
}
}
// Map<Integer,Integer> positions = transPositionMap(spanRows, 1);
// for(int j = 0;j < 11 ;j++){
// for(Map.Entry<Integer, Integer> entry:positions.entrySet()){
// // 起始行, 终止行, 起始列, 终止列
// CellRangeAddress cra =new CellRangeAddress(entry.getKey(),entry.getValue(), j, j);
// sheet.addMergedRegion(cra);
// }
// }
//隐藏第一列数据
//sheet.setColumnHidden((short)0, true);
}
/**
* 根据跨行数来转化 合并单元格的 firstRow 与 lastRow
* @param list
* @param minRow
* @return
*/
private static Map<Integer,Integer> transPositionMap(List<Integer> list,Integer minRow){
if(PublicUtil.isNotEmpty(list)){
Map<Integer,Integer> linkedMap = new LinkedHashMap<>();
List<Integer> xPositionList = new ArrayList<>();
xPositionList.add(minRow);
for(Integer pos:list){
if(pos == 1 || pos == 0){
pos = 1;
Integer xPosition = Collections.max(xPositionList);
xPositionList.add(xPosition+pos);
continue;
}
Integer xPosition = Collections.max(xPositionList);
linkedMap.put(xPosition, xPosition+pos-1);
xPositionList.add(xPosition+pos);
}
return linkedMap;
}
return Collections.emptyMap();
}
/**
* 获取单元格值
* @param cell
* @return
*/
private static String getStringCellValue(Cell cell) {
String strCell = "";
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
}
return strCell;
}
Java操作POI根据条件合并单元格
最新推荐文章于 2024-03-25 16:47:44 发布