Java Code Examples for org.apache.poi.xssf.usermodel.XSSFRow#getCell()

@SuppressWarnings("deprecation")
public void readXLSX(String path, int num) throws InvalidFormatException, IOException {
	File file = new File(path);
	@SuppressWarnings("resource")
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(num);

	int rowstart = xssfSheet.getFirstRowNum();
	int rowEnd = xssfSheet.getLastRowNum();
	for (int i = rowstart; i <= rowEnd; i++) {
		XSSFRow row = xssfSheet.getRow(i);
		if (null == row) {
			continue;
		}
		int cellStart = row.getFirstCellNum();
		int cellEnd = row.getLastCellNum();

		for (int k = cellStart; k <= cellEnd; k++) {
			XSSFCell cell = row.getCell(k);
			if (null == cell) {
				continue;
			}

			switch (cell.getCellTypeEnum()) {
				case NUMERIC: // 数字
					System.out.print(cell.getNumericCellValue() + "   ");
					break;
				case STRING: // 字符串
					System.out.print(cell.getStringCellValue() + "   ");
					break;
				case BOOLEAN: // Boolean
					System.out.println(cell.getBooleanCellValue() + "   ");
					break;
				case FORMULA: // 公式
					System.out.print(cell.getCellFormula() + "   ");
					break;
				case BLANK: // 空值
					System.out.println(" ");
					break;
				case ERROR: // 故障
					System.out.println(" ");
					break;
				default:
					System.out.print("未知类型   ");
					break;
			}

		}
		System.out.print("\n");
	}

}
 

private static List<List<String>> readSheet(XSSFSheet xssfSheet){
	int rows = xssfSheet.getPhysicalNumberOfRows();
	List<List<String>> rowList = new ArrayList<>();
	for(int i=0;i<rows;i++){//遍历每一行
		XSSFRow row = xssfSheet.getRow(i);
		if(row==null){
			continue;
		}
		int cells = row.getPhysicalNumberOfCells();
		List<String> cellList = new ArrayList<>();
		for(int j=0;j<cells;j++){//遍历每一列
			XSSFCell cell = row.getCell(j);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			//new Double("1.0").intValue()
			String cellValue = cell.getStringCellValue();
			cellList.add(cellValue);
		}
		rowList.add(cellList);
	}
	return rowList;
}
 

public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
    if ((row1 == null) && (row2 == null)) {
        return true;
    } else if ((row1 == null) || (row2 == null)) {
        return false;
    }

    int firstCell1 = row1.getFirstCellNum();
    int lastCell1 = row1.getLastCellNum();
    boolean equalRows = true;

    // Compare all cells in a row
    for (int i = firstCell1; i <= lastCell1; i++) {
        XSSFCell cell1 = row1.getCell(i);
        XSSFCell cell2 = row2.getCell(i);
        if (!compareTwoCells(cell1, cell2)) {
            equalRows = false;
            break;
        }
    }
    return equalRows;
}
 

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

/**
 * 读取Excel 2007版，xlsx格式
 *
 * @param is
 * @return
 * @throws IOException
 */
private List<List<Object>> read2007Excel(InputStream is) throws IOException {
    List<List<Object>> list = new LinkedList<>();
    // 构造 XSSFWorkbook 对象，strPath 传入文件路径
    XSSFWorkbook xwb = new XSSFWorkbook(is);
    int sheetCount = xwb.getNumberOfSheets();
    for (int n = 0; n < sheetCount; n++) {
        XSSFSheet sheet = xwb.getSheetAt(n);
        Object value;
        XSSFRow row;
        XSSFCell cell;
        int counter = 0;
        for (int i = (startReadPos - 1); counter < sheet.getPhysicalNumberOfRows() - (startReadPos - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                value = getCellValue(cell);
                if (value == null || "".equals(value)) {
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
    }
    return list;
}
 

public static boolean isBlankRow(XSSFRow row, int index, int rowCount){
	if(row == null)
		return true;
	for(int i=index; i < rowCount; i++){
		if(row.getCell(i) != null || 
				!"".equals(row.getCell(i).getStringCellValue().trim())){
			return false;
		}
	}
	return true;
}
 

public static boolean isBlankRow(XSSFRow row, int index, int rowCount){
	if(row == null)
		return true;
	for(int i=index; i < rowCount; i++){
		if(row.getCell(i) != null || 
				!"".equals(row.getCell(i).getStringCellValue().trim())){
			return false;
		}
	}
	return true;
}
 

@Override
public String[] read(String sheetName, int index, int headerSize) {

  if (sheetName == null || book == null) {
    return null;
  }

  XSSFSheet sheet = book.getSheet(sheetName);
  if (sheet == null) {
    return null;
  }

  XSSFRow row = sheet.getRow(index);
  if (row == null) {
    return null;
  }

  if (headerSize == 0) {
    headerSize = row.getLastCellNum();
  }

  String[] vals = new String[headerSize];

  for (int i = 0; i < headerSize; i++) {
    Cell cell = row.getCell(i);
    if (cell == null) {
      continue;
    }
    vals[i] = formatter.formatCellValue(cell);
    if (Strings.isNullOrEmpty(vals[i])) {
      vals[i] = null;
    }
  }

  return vals;
}
 

private XSSFCell getOrCreatePoiCell(int rowNum, int col) {
    XSSFRow row = getOrCreatePoiRow(rowNum);
    XSSFCell cell = row.getCell(col);
    if (cell == null) {
        cell = row.createCell(col);
    }
    return cell;
}
 

@Documentation(
    value = "Insert a table from an Excel .xlsx file.",
    params = {
        @Param(name = "uri", value = "The Excel .xlsx file uri, it can be relative to the template"),
        @Param(name = "sheetName", value = "The sheet name"),
        @Param(name = "topLeftCellAdress", value = "The top left cell address"),
        @Param(name = "bottomRightCellAdress", value = "The bottom right cell address"),
        @Param(name = "languageTag", value = "The language tag for the locale"),
    },
    result = "insert the table",
    examples = {
        @Example(expression = "'excel.xlsx'.asTable('Feuil1', 'C3', 'F7', 'fr-FR')", result = "insert the table from 'excel.xlsx'"),
    }
)
// @formatter:on
public MTable asTable(String uriStr, String sheetName, String topLeftCellAdress, String bottomRightCellAdress,
        String languageTag) throws IOException {
    final MTable res = new MTableImpl();

    final URI xlsxURI = URI.createURI(uriStr, false);
    final URI uri = xlsxURI.resolve(templateURI);

    try (XSSFWorkbook workbook = new XSSFWorkbook(uriConverter.createInputStream(uri));) {
        final FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
        final XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new IllegalArgumentException(String.format("The sheet %s doesn't exist in %s.", sheetName, uri));
        } else {
            final Locale locale;
            if (languageTag != null) {
                locale = Locale.forLanguageTag(languageTag);
            } else {
                locale = Locale.getDefault();
            }
            final DataFormatter dataFormatter = new DataFormatter(locale);
            final CellAddress start = new CellAddress(topLeftCellAdress);
            final CellAddress end = new CellAddress(bottomRightCellAdress);
            int rowIndex = start.getRow();
            while (rowIndex <= end.getRow()) {
                final XSSFRow row = sheet.getRow(rowIndex++);
                if (row != null) {
                    final MRow mRow = new MRowImpl();
                    int cellIndex = start.getColumn();
                    while (cellIndex <= end.getColumn()) {
                        final XSSFCell cell = row.getCell(cellIndex++);
                        if (cell != null) {
                            final MStyle style = getStyle(cell);
                            final MElement text = new MTextImpl(dataFormatter.formatCellValue(cell, evaluator),
                                    style);
                            final Color background = getColor(cell.getCellStyle().getFillForegroundColorColor());
                            final MCell mCell = new MCellImpl(text, background);
                            mRow.getCells().add(mCell);
                        } else {
                            mRow.getCells().add(createEmptyCell());
                        }
                    }
                    res.getRows().add(mRow);
                } else {
                    final int length = end.getColumn() - start.getColumn() + 1;
                    res.getRows().add(createEmptyRow(length));
                }
            }

        }
    }

    return res;
}
 

private static List<?> readSheet(XSSFSheet xssfSheet,Map<Integer,String> map,Class<?> objectClass) throws Exception{
	int rows = xssfSheet.getPhysicalNumberOfRows();
	List<Object> rowList = new ArrayList<>();
	for(int i=0;i<rows;i++){//遍历每一行
		Object target = objectClass.newInstance();
		XSSFRow row = xssfSheet.getRow(i);
		if(row==null){
			continue;
		}
		Set<Integer> set = map.keySet();
		for(Integer each:set){
			XSSFCell cell = row.getCell(each);
			if(cell==null){
				continue;
			}
			cell.setCellType(Cell.CELL_TYPE_STRING);
			String fieldName = map.get(each);
			Class<?> fieldType = objectClass.getDeclaredField(fieldName).getType();
			fieldName = fieldName.substring(0,1).toUpperCase()+fieldName.substring(1,fieldName.length());
			Object value = "";
			try{
				if("java.lang.Double".equals(fieldType.getName())){
					value = new Double(cell.toString());
					target.getClass().getDeclaredMethod("set"+fieldName,Double.class).invoke(target, value);
				}else if("java.lang.Integer".equals(fieldType.getName())){
					value = new Double(cell.toString()).intValue();
					target.getClass().getDeclaredMethod("set"+fieldName,Integer.class).invoke(target, value);
				}else if("java.lang.Float".equals(fieldType.getName())){
					value = new Float(cell.toString());
					target.getClass().getDeclaredMethod("set"+fieldName,Float.class).invoke(target, value);
				}else{
					value = cell.toString();
					target.getClass().getDeclaredMethod("set"+fieldName,String.class).invoke(target, value);
				}
			}catch(Exception e){
				//do nothing
			}
		}
		rowList.add(target);
	}
	return rowList;
}
 

/**
 * 读取Office 2007 excel
 */
private List<List<Object>> readExcel2007(File file) throws IOException {
	List<List<Object>> list = new LinkedList<List<Object>>();
	// 构造 XSSFWorkbook 对象，strPath 传入文件路径
	XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
	// 读取第一章表格内容
	XSSFSheet sheet = xwb.getSheetAt(0);
	Object value = null;
	XSSFRow row = null;
	XSSFCell cell = null;
	int counter = 0;
	for (int i = sheet.getFirstRowNum(); counter < sheet
			.getPhysicalNumberOfRows(); i++) {
		row = sheet.getRow(i);
		if (row == null) {
			continue;
		} else {
			counter++;
		}
		List<Object> linked = new LinkedList<Object>();
		for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
			cell = row.getCell(j);
			if (cell == null) {
				continue;
			}
			DecimalFormat df = new DecimalFormat("0");// 格式化 number String
			// 字符
			SimpleDateFormat sdf = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
			DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				System.out.println(i + "行" + j + " 列 is String type");
				value = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				System.out.println(i + "行" + j
						+ " 列 is Number type ; DateFormt:"
						+ cell.getCellStyle().getDataFormatString());
				if ("@".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("General".equals(cell.getCellStyle()
						.getDataFormatString())) {
					value = nf.format(cell.getNumericCellValue());
				} else {
					value = sdf.format(HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue()));
				}
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				System.out.println(i + "行" + j + " 列 is Boolean type");
				value = cell.getBooleanCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				System.out.println(i + "行" + j + " 列 is Blank type");
				value = "";
				break;
			default:
				System.out.println(i + "行" + j + " 列 is default type");
				value = cell.toString();
			}
			if (value == null || "".equals(value)) {
				continue;
			}
			linked.add(value);
		}
		list.add(linked);
	}
	return list;
}
 

public static String[][] readExcel(String absolutePathToFile) {

		// A Two dimensional array of Strings which represents the data in the
		// sheet
		String[][] data = null;
		try {
			// A Buffered File Input Stream to read the data
			InputStream is = new BufferedInputStream(new FileInputStream(
					absolutePathToFile));
			// Workbook representing the excel file
			XSSFWorkbook wb = new XSSFWorkbook(is);
			// Next a sheet which represents the sheet within that excel file
			XSSFSheet sheet = wb.getSheet("Details");
			// No of rows in the sheet
			int rowNum = sheet.getLastRowNum() + 1;
			// No of columns in the sheet
			int colNum = sheet.getRow(0).getLastCellNum();
			data = new String[rowNum][colNum];
			for (int i = 0; i < rowNum; i++) {
				// Get the row
				XSSFRow row = sheet.getRow(i);
				for (int j = 0; j < colNum; j++) {
					// Get the columns or cells for the first row and keep
					// looping
					// for the other rows
					XSSFCell cell = row.getCell(j);
					// Make a call to the method cellToString which actually
					// converts the cell contents to String
					String value = cellToString(cell);
					data[i][j] = value;
					// Logic for handling the data
					// You can write the logic here, or leave the method as it
					// is to return a two dimensional array
					// representing the excel data
					System.out.println("Value:" + value);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;

	}