Java Code Examples for org.apache.poi.xssf.usermodel.XSSFSheet#getRow()

public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    int firstRow1 = sheet1.getFirstRowNum();
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;
            break;
        }
    }
    return equalSheets;
}
 

public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}
 

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;
}
 

@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;
}
 

@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");
	}

}
 

/**
 * Returns the index of the first empty column
 *
 * @param sheet the sheet to write on
 * @return a column index
 * @see
 */
public int getFirstEmptyColumn(XSSFSheet sheet) {
  Row row = sheet.getRow(0);
  if (row == null)
    return 0;
  int lastcoll = row.getLastCellNum();
  return (lastcoll == -1) ? 0 : lastcoll;
}
 

@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;
}
 

/**
 * Returns the index of the first empty column
 *
 * @param sheet the sheet to write on
 * @return a column index
 * @see
 */
public int getFirstEmptyColumn(XSSFSheet sheet) {
  Row row = sheet.getRow(0);
  if (row == null)
    return 0;
  int lastcoll = row.getLastCellNum();
  return (lastcoll == -1) ? 0 : lastcoll;
}
 

/**
 * Returns the cell of a given sheet at icol;irow. Former values are overwritten.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @return the given cell at icol;irow
 * @see
 */
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
  // try to get row
  Row row = sheet.getRow(irow);
  // if not exist: create row
  if (row == null)
    row = sheet.createRow(irow);
  // get cell
  return row.createCell(icol);
}
 

private int buildMaxColumn(XSSFSheet sheet){
	int rowCount=sheet.getPhysicalNumberOfRows();
	int maxColumnCount=0;
	for(int i=0;i<rowCount;i++){
		XSSFRow row=sheet.getRow(i);
		if(row==null){
			continue;
		}
		int columnCount=row.getPhysicalNumberOfCells();
		if(columnCount>maxColumnCount){
			maxColumnCount=columnCount;
		}
	}
	return maxColumnCount;
}
 

/**
 * 读取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;
}
 

@Test
public void lastRowNumXSSF2() throws IOException {
    String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = "D:\\test\\珠海.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

@Test
public void lastRowNumXSSF() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    XSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
 

/**
 * Returns the cell of a given sheet at icol;irow. Former values are overwritten.
 *
 * @param sheet the sheet to write on
 * @param icol index of column
 * @param irow index of row
 * @return the given cell at icol;irow
 * @see
 */
public Cell getCell(XSSFSheet sheet, int icol, int irow) {
  // try to get row
  Row row = sheet.getRow(irow);
  // if not exist: create row
  if (row == null)
    row = sheet.createRow(irow);
  // get cell
  return row.createCell(icol);
}
 

@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;

	}