Java Code Examples for org.apache.poi.ss.usermodel.Row#getLastCellNum()

private List<Map<Integer, Cell>> uploadAndReadExcel(MultipartFile multipartFile) throws IOException {
    List<Map<Integer, Cell>> list = new ArrayList<>();
    Workbook workbook = ExcelUtil.getWorkbook(Objects.requireNonNull(multipartFile.getOriginalFilename()), multipartFile.getInputStream());
    if (workbook != null) {
        Sheet sheet = workbook.getSheetAt(0);
        int rowIndex;
        for (rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            int cellIndex;
            Map<Integer, Cell> map = new HashMap<>(1);
            for (cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                map.put(cellIndex, row.getCell(cellIndex));
            }
            list.add(map);
        }
    }
    return list.isEmpty() ? null : list;
}
 

public static Integer getResultColumnNum(Sheet sheet) {
	Row row = sheet.getRow(sheet.getFirstRowNum());
	if (null != row) {
		Cell cell = null;
		for (int i = row.getFirstCellNum() + 1; i < row.getLastCellNum(); i++) {
			cell = row.getCell(i);
			if (null != cell) {
				String str = cell.getStringCellValue();
				if (StringUtils.isEmpty(str) || StringUtils.equals(str, "结果")
						|| StringUtils.equals(str, "result")) {
					return i;
				}
			}
		}
		return (int) row.getLastCellNum();
	}
	return null;
}
 

@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 

@Override
@SuppressWarnings("unchecked")
public R read(Row row) {
    if (row == null) {
        return null;
    }
    R result = getResultSupplier().get();
    if (result == null) {
        return null;
    }
    BiConverter<? super R, Object, ? extends R> cellParser = getCellParser();
    if (cellParser == null) {
        return result;
    }
    for (int cellIndex = row.getFirstCellNum(), lastCellIndex = row.getLastCellNum(); cellIndex < lastCellIndex; cellIndex++) {
        Object cellValue = null;
        Cell cell = row.getCell(cellIndex);
        ICellReader cellReader = getCellReader(cellIndex);
        if (cellReader != null) {
            cellValue = cellReader.read(cell);
        }
        result = cellParser.apply(result, new Object[]{cell, cellIndex, cellValue});
    }
    return result;
}
 

private static void mappingColumns(List<MappingItem> list, Sheet sheet) {
	Row row = sheet.getRow(sheet.getFirstRowNum());
	if (null != row) {
		Cell cell = null;
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			cell = row.getCell(i);
			if (null != cell) {
				String str = cell.getStringCellValue();
				for (MappingItem o : list) {
					if (StringUtils.equalsIgnoreCase(o.getName(), str)) {
						o.setColumn(i);
					}
				}
			}
		}
	}
}
 

/***
 * 向List里面继续添加元素
 * 
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {
	Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(), object.getClass()).invoke(object, new Object[] {});
	Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
	String picId;
	boolean isUsed = false;// 是否需要加上这个对象
	for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);
		String titleString = (String) titlemap.get(i);
		if (param.getExcelParams().containsKey(titleString)) {
			if (param.getExcelParams().get(titleString).getType() == 2) {
				picId = row.getRowNum() + "_" + i;
				saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
			} else {
				saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
			}
			isUsed = true;
		}
	}
	if (isUsed) {
		collection.add(entity);
	}
}
 

public int getColumns() {
  int minRowIndex = sheet.getFirstRowNum();
  int maxRowIndex = sheet.getLastRowNum();
  int maxColumnsIndex = 0;
  for (int i = minRowIndex; i <= maxRowIndex; i++) {
    Row row = sheet.getRow(i);
    if (row == null) {
      continue;
    }
    int column = row.getLastCellNum();
    if (column > maxColumnsIndex) {
      maxColumnsIndex = column;
    }
  }
  return maxColumnsIndex;
}
 

private int addLangCell(Row header, TmxSegement segment) {
		int CellNum = header.getLastCellNum();
		if (-1 == CellNum) {
			CellNum = 0;
		}
		Cell createCell = header.createCell(CellNum);
		//CellStyle cellStyle = wb.createCellStyle();
		//XSSFFont headerFont = (XSSFFont) wb.createFont();
		//headerFont.setBold(true);
//		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//		cellStyle.setFont(headerFont);
		createCell.setCellValue(segment.getLangCode());
//		createCell.setCellStyle(cellStyle);
//		sh.setColumnWidth(CellNum, (100 * 7 + 5) / 7 * 256);
		return CellNum;
	}
 

/**
 * 行复制功能（空白行的复制，即只复制格式和固定文字，不填充数据）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-07-03
 * @version     v1.0
 *
 * @param i_RTemplate      模板
 * @param i_TemplateRow    模板中的行对象
 * @param i_DataWorkbook   数据工作薄
 * @param io_RTotal        将数据写入Excel时的辅助统计信息。
 * @param io_RSystemValue  系统变量信息
 * @param i_DataRow        数据中的行对象
 * @param i_Datas          本行对应的数据
 * 
 * @return                 返回本方法内一共生成多少新行。
 */
public final static int copyRowByBlankSpace(RTemplate i_RTemplate ,Row i_TemplateRow ,RWorkbook i_DataWorkbook ,RTotal io_RTotal ,RSystemValue io_RSystemValue ,Row i_DataRow) 
{
    ExcelHelp.copyRowHeight(i_TemplateRow ,i_DataRow);
    
    int v_CellCount = i_TemplateRow.getLastCellNum();
    
    for (int v_CellIndex=0; v_CellIndex<v_CellCount; v_CellIndex++) 
    {
        Cell v_TemplateCell = i_TemplateRow.getCell(v_CellIndex);
        if ( v_TemplateCell == null )
        {
            continue;
        }
        
        Cell v_DataCell = i_DataRow.getCell(v_CellIndex);
        if ( v_DataCell == null ) 
        {
            v_DataCell = i_DataRow.createCell(v_CellIndex);
        }
        
        copyCellByBlankSpace(i_RTemplate ,v_TemplateCell ,i_DataWorkbook ,v_DataCell ,io_RSystemValue);
    }
    
    return 0;
}
 

private List<String> readHeader(final Row row) {
    final List<String> header = new ArrayList<>();
    for (int i = 0; i < row.getLastCellNum(); i++) {
        header.add(readCellAsString(row.getCell(i)));
    }
    return header;
}
 

/**
 * Adds the government body annual summary to list.
 *
 * @param name
 *            the name
 * @param year
 *            the year
 * @param yearList
 *            the year list
 * @param row
 *            the row
 */
private static void addGovernmentBodyAnnualSummaryToList(final String name, final int year,
		final List<GovernmentBodyAnnualSummary> yearList, final Row row) {
	if (row.getLastCellNum() >= EXPECTED_COLUMN_LENGTH) {

		final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
				year, row);

		if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getMinistry())) {
			yearList.add(governmentBodyAnnualSummary);
		}
	}
}
 

private String[] readRow(int index) {
    Row row = sheet.getRow(index);
    if (row == null || row.getPhysicalNumberOfCells() == 0) {
        return new String[0];
    }

    String[] record = new String[row.getLastCellNum()];
    for (int i = 0; i < record.length; i++) {
        record[i] =  Optional.ofNullable(row.getCell(i)).map(Object::toString).orElse(null);
    }
    return record;
}
 

private static void parseTemplate(Sheet sheet, Map<String, Object> map)
		throws Exception {
	Iterator<Row> rows = sheet.rowIterator();
	Row row;
	while (rows.hasNext()) {
		row = rows.next();
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			setValueForCellByMap(row.getCell(i), map);
		}
	}
}
 

private void printSheetContent(Sheet sheet) {
    //printColumnHeads(sheet);
    MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
    CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    int rowIndex = 1;
    while (rows.hasNext()) {
        Row row = rows.next();
        out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
        //out.format("    <td class='%s'>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
                String content = "&nbsp;";
                CellStyle style = null;
                if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        style = cell.getCellStyle();
                        content = cellValueHelper.getHtmlValue(cell);
                    }
                }
                if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
                    Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
                    out.format("    <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n",
                        rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
                } else {
                    out.format("    <td class='%s'>%s</td>%n", styleName(style), content);
                }
            }

        }
        out.format("  </tr>%n");
        rowIndex++;
    }
    out.format("</tbody>%n");
}
 

public static List<String[]> readExcel(InputStream inputStream, String extension) throws IOException {
    List<String[]> ret        = new ArrayList<>();
    Workbook       excelBook  = extension.equalsIgnoreCase(XLS.name()) ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
    Sheet          excelSheet = excelBook.getSheetAt(0);
    Iterator       itr        = excelSheet.rowIterator();
    Row            headerRow  = (Row) itr.next();

    if (isRowEmpty(headerRow)) {
        return ret;
    }

    while (itr.hasNext()) {
        Row row = (Row) itr.next();

        if (!isRowEmpty(row)) {
            String[] data = new String[row.getLastCellNum()];

            for (int i = 0; i < row.getLastCellNum(); i++) {
                data[i] = (row.getCell(i) != null) ? row.getCell(i).getStringCellValue().trim() : null;
            }

            ret.add(data);
        }
    }

    return ret;
}
 

/**
 * Given a sheet, this method deletes a column from a sheet and moves all
 * the columns to the right of it to the left one cell.
 * 
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
	int maxColumn = 0;
	for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
		Row row = sheet.getRow(r);

		// if no row exists here; then nothing to do; next!
		if (row == null)
			continue;

		// if the row doesn't have this many columns then we are good; next!
		int lastColumn = row.getLastCellNum();
		if (lastColumn > maxColumn)
			maxColumn = lastColumn;

		if (lastColumn < columnToDelete)
			continue;

		for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
			Cell oldCell = row.getCell(x - 1);
			if (oldCell != null)
				row.removeCell(oldCell);

			Cell nextCell = row.getCell(x);
			if (nextCell != null) {
				Cell newCell = row.createCell(x - 1, nextCell.getCellType());
				cloneCell(newCell, nextCell);
			}
		}
	}

	// Adjust the column widths
	for (int c = 0; c < maxColumn; c++) {
		sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
	}
}
 

@Override
public DataRecordMetadata createMetadata() {
	if (workbook == null) {
		return null;
	}

	String sheetName = workbook.getSheetName(sheetCounter);

	DataRecordMetadata xlsMetadata = new DataRecordMetadata(DataRecordMetadata.EMPTY_NAME, DataRecordMetadata.DELIMITED_RECORD);
	xlsMetadata.setLabel(sheetName);
	xlsMetadata.setFieldDelimiter(DEFAULT_FIELD_DELIMITER);
	xlsMetadata.setRecordDelimiter(DEFAULT_RECORD_DELIMITER);

	Row namesRow = null;
	if((metadataRow > -1)) {
	namesRow = sheet.getRow(metadataRow);
	} else {
		namesRow = sheet.getRow(firstRow);
	}
	
         
	
       Row dataRow = sheet.getRow(firstRow);
         
       if(dataRow == null) {
       	for(int i = 0 ; i < 100; i++) {
       		dataRow =  sheet.getRow(i);
       		if(dataRow != null) break;
       	}
       }
       
       
       int maxNumberOfColumns = Math.max(namesRow.getLastCellNum(), dataRow.getLastCellNum());

	for (int i = 0; i < maxNumberOfColumns; i++) {
		Cell nameCell = (i < namesRow.getLastCellNum()) ? namesRow.getCell(i) : null;
		Cell dataCell = (i < dataRow.getLastCellNum()) ? dataRow.getCell(i) : null;

		int cellType = (dataCell != null) ? dataCell.getCellType() : Cell.CELL_TYPE_STRING;

		if (namesRow != dataRow
				&& (nameCell == null || nameCell.getCellType() == Cell.CELL_TYPE_BLANK)
				&& (dataCell == null || cellType == Cell.CELL_TYPE_BLANK)) {
			continue;
		}

		String cellName = (metadataRow > -1 && nameCell != null) ? 
				dataFormatter.formatCellValue(nameCell) : XLSFormatter.getCellCode(i);

		DataFieldMetadata dataField = null;

		if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DataFieldMetadata.BOOLEAN_FIELD, null);
		} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
			dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DateUtil.isCellDateFormatted(dataCell)
					? DataFieldMetadata.DATE_FIELD : DataFieldMetadata.NUMERIC_FIELD, null);
			String formatString = dataCell.getCellStyle().getDataFormatString();

			if (formatString != null && !formatString.equals(XLSXDataFormatter.GENERAL_FORMAT_STRING)) {
				dataField.setFormatStr(formatString);
			}
		} else {
			dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DataFieldMetadata.STRING_FIELD, null);
		}

		dataField.setLabel(cellName);
		xlsMetadata.addField(dataField);
	}

	xlsMetadata.normalize();

	return xlsMetadata;
}
 

public void apply(Row row, ReportItemMethod reportItemMethod, Object methodReturnValue) {
    short lastCellNum = row.getLastCellNum();
    Cell cell = row.createCell(lastCellNum == -1 ? 0 : lastCellNum);

    format(reportItemMethod.value(), methodReturnValue, cell);
}
 

@Override
protected int getTotalColNum(Row row) {
    return row.getLastCellNum();
}
 

/**
 * Adds the government body annual summary to map.
 *
 * @param name
 *            the name
 * @param map
 *            the map
 * @param year
 *            the year
 * @param row
 *            the row
 */
private static void addGovernmentBodyAnnualSummaryToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
		final int year, final Row row) {
	if (row.getLastCellNum() >= EXPECTED_COLUMN_LENGTH) {

		final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
				year, row);
		if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
			map.put(year, governmentBodyAnnualSummary);
		}
	}
}