Java XSSFRow.getFirstCellNum方法代码示例

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
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;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public XSLXSource(Resource resource, XSSFWorkbook workbook) {
    this.resource = resource;
    this.workbook = workbook;
    String fragment = resource.getFragment();
    XSSFSheet sheet;
    if (fragment == null) {
        sheet = workbook.getSheetAt(0);
    } else {
        sheet = workbook.getSheet(fragment);
        if (sheet == null) {
            throw new IllegalArgumentException("Unable to find Sheet named '" + fragment + "'");
        }
    }
    this.sheet = sheet;
    this.rows = this.sheet.iterator();
    if (!rows.hasNext()) {
        throw new IllegalArgumentException(sheet.getSheetName() + " is empty");
    }
    XSSFRow row = nextRow();
    this.firstColumn = row.getFirstCellNum();
    this.headers = readHeaders(row, this.firstColumn);

}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * @param srcSheet the sheet to copy.
 * @param destSheet the sheet to create.
 * @param srcRow the row to copy.
 * @param destRow the row to create.
 * @param styleMap -
 */
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a merged zone  
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row  
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        if(j<0){
        }else{
            XSSFCell oldCell = srcRow.getCell(j);   // ancienne cell  
        XSSFCell newCell = destRow.getCell(j);  // new cell   
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell  
            copyCell(oldCell, newCell, styleMap);
                  // copy les informations de fusion entre les cellules  
            //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());  
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                //System.out.println("Selected merged region: " + mergedRegion.toString());  
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                //System.out.println("New merged region: " + newMergedRegion.toString());  
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
        }
        
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private Row readRow(XSSFRow poiRow, int rowNo) {
	row = new Row();

	row.setHeight((int) poiRow.getHeightInPoints());
	int firstCellNum = poiRow.getFirstCellNum();
	if (firstCellNum >= 0) {
		for (int i = 0; i < firstCellNum; i++) {
			Cell defaultCell = createDefaultCell(rowNo, i);
			row.addCell(defaultCell);
		}

		int lastCellNum = poiRow.getLastCellNum();
		for (int i = firstCellNum; i < lastCellNum; i++) {
			poiCell = poiRow.getCell(i);
			if (poiCell == null) {
				cell = createDefaultCell(rowNo, i);
			} else {
				cell = readCell(poiCell);
			}
			row.addCell(cell);
		}
	}

	return row;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
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;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static void copySheets2CSV(XSSFSheet sheet, String csvfile) {
        int maxColumnNum = 0;
        Map<Integer, XSSFCellStyle> styleMap = null;

        try {
            FileWriter fw = new FileWriter(csvfile);

            String str = "";
            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                XSSFRow srcRow = sheet.getRow(i);
                if (srcRow != null) {
                    System.out.println(srcRow.getLastCellNum());
                    System.out.println(srcRow.getFirstCellNum());
//                    System.out.println(srcRow.getCell(srcRow.getLastCellNum()).toString());
                    for (int j = srcRow.getFirstCellNum(); j < srcRow.getLastCellNum(); j++) {
                        
                        if (srcRow.getCell(j)!=null&&j != srcRow.getLastCellNum()-1) {
                            srcRow.getCell(j).setCellType(1);
                            
                            str = str +srcRow.getCell(j).getReference()+ ",";
                        } else if(srcRow.getCell(j)!=null){
                            srcRow.getCell(j).setCellType(1);
                            
                            str = str +srcRow.getCell(j).getStringCellValue()+ "\r\n";
                        }
//
                    }
                    fw.append(str);
                }
                str = "";
            }

            fw.flush();
            fw.close();
        } catch (IOException ex) {

        }//Util.copyPictures(newSheet,sheet) ;
    }
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * 解析导出的视频excel文件
 */
public static void parse(File file) {
	try {
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet sheet = xwb.getSheetAt(0);

		System.out.println(String.format("准备解析：%s, 表格：%s", file.getAbsolutePath(), sheet.getSheetName()));

		XSSFRow row = null;
		String cell = null;

		// 遍历行(从0开始)
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);

			// 忽略空行:真实
			if (row.getFirstCellNum() < 0) {
				continue;
			}

			// 遍历行-列
			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
				if (row.getCell(j) == null) {
					continue;
				}
				cell = row.getCell(j).toString();
				if (StringUtil.isEmpty(cell)) {
					continue;
				}
				System.out.println(String.format("解析 行 %d 列 %d\t>>\t%s", i + 1, j, cell));
				appendSql(new Vi(cell));
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public List<ValidationResult> validateSheet(XSSFSheet sheet) {
	List<ValidationResult> results = new LinkedList<ValidationResult>();

	List<XSSFDataValidation> dataValidations = sheet.getDataValidations();
	for (XSSFDataValidation xssfDataValidation : dataValidations) {
		DataValidationConstraint validationConstraint = xssfDataValidation.getValidationConstraint();
		CellRangeAddressList regions = xssfDataValidation.getRegions();
		CellRangeAddress[] cellRangeAddresses = regions.getCellRangeAddresses();
		Validator validator = buildValidator(sheet, validationConstraint);
		for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
			int firstRow = Math.max(cellRangeAddress.getFirstRow(), sheet.getFirstRowNum());
			int lastRow = Math.min(cellRangeAddress.getLastRow(), sheet.getLastRowNum());

			for (int i = firstRow; i <= lastRow; i++) {
				XSSFRow row = sheet.getRow(i);
				if (row == null || row.getFirstCellNum() < 0) {
					continue;
				}

				int firstColumn = Math.max(cellRangeAddress.getFirstColumn(), row.getFirstCellNum());
				int lastColumn = Math.min(cellRangeAddress.getLastColumn(), row.getLastCellNum());

				for (int j = firstColumn; j <= lastColumn; j++) {
					XSSFCell cell = row.getCell(j);
					if (cell == null) {
						continue;
					}
					boolean inRange = cellRangeAddress.isInRange(cell.getRowIndex(), cell.getColumnIndex());
					if (inRange) {
						ValidationResult result = validator.validate(cell);
						if (result != null) {
							results.add(result);
						}
					}
				}
			}
		}
	}
	return results;
	/*
	 * TODO think about splitting this function into 2 parts: 1.
	 * getAllValidators 2. validate with all validators
	 */
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private void readExcelDocument() {
	try {
		FileInputStream fs = new FileInputStream(f);
		XSSFWorkbook wb = new XSSFWorkbook(fs);
		XSSFSheet sh;
		String text = "";
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			sh = wb.getSheetAt(i);
			for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) {
				XSSFRow currRow = sh.getRow(j);
				if (currRow == null || currRow.getFirstCellNum() == -1) {
					continue;
				} else {
					for (int k = currRow.getFirstCellNum(); k < currRow
							.getLastCellNum(); k++) {
						if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) {
							continue;
						} else {
							text += currRow.getCell(k) + "; ";
						}
					}
					text += System.lineSeparator();
				}
			}
		}
		fs.close();
		wb.close();
		String[] xlsxLines = text.split(System.lineSeparator());
		for (String line : xlsxLines) {
			lines.add(line);
		}
	} catch (IOException e) {
		JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument",
				"Fehler", JOptionPane.ERROR_MESSAGE);
		e.printStackTrace();
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * Copy a row from a sheet to another sheet
 * 
 * 
 * @param srcSheet the sheet to copy
 * @param destSheet the sheet to copy into
 * @param parentSheetRow the row inside destSheet where we start to copy
 * @param parentSheetColumn the column inside destSheet where we start to copy
 * @param srcRow the row to copy
 * @param destRow the row to create
 * @param styleMap style map
 *       
 */
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, int parentSheetRow, int parentSheetColumn, XSSFRow srcRow, XSSFRow destRow,
		Map<Integer, CellStyle> styleMap) {
	// manage a list of merged zone in order to not insert two times a
	// merged zone
	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	destRow.setHeight(srcRow.getHeight());
	// pour chaque row
	for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
		XSSFCell oldCell = srcRow.getCell(j); // ancienne cell			
		if (oldCell != null) {				
			XSSFCell newCell = destRow.createCell(parentSheetColumn + j);				
			copyCell(oldCell, newCell, styleMap);
			
			CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {
				
				CellRangeAddress newMergedRegion = new CellRangeAddress(parentSheetRow + mergedRegion.getFirstRow(),
						parentSheetRow + mergedRegion.getLastRow(), 
						parentSheetColumn + mergedRegion.getFirstColumn(), 
						parentSheetColumn + mergedRegion.getLastColumn());
				
				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * 解析Excel-2007 参考：http://kxjhlele.iteye.com/blog/321392
 * 
 */
public static void main(String[] args) {
    String excelPath = "D:/Download/全国汇编（物化）.xlsx";

    try {
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(excelPath));
        XSSFSheet sheet = xwb.getSheetAt(0);

        XSSFRow row = null;
        String cell = null;

        Map<String, String> peOrderMap = new TreeMap<String, String>();
        int porder = 0, eorder = 0;
        // 遍历所有行
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            // 遍历每一行的所有列
            for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                cell = row.getCell(j).toString();
                if (j == 0 && !cell.isEmpty()) { // 试卷序号
                    porder = new Double(Double.parseDouble(cell)).intValue();
                }
                if (j == 2) { // 试卷下试题序号
                    eorder = new Double(Double.parseDouble(cell)).intValue();
                }
                if (j == 3) {// 录制老师姓名
                    peOrderMap.put(porder + "#" + eorder, cell);
                }
                System.out.print(cell + "\t");
            }
            System.out.println("");
        }

        for (Entry<String, String> en : peOrderMap.entrySet()) {
            System.out.println(en.getKey() + " - " + en.getValue());
        }

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

}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * 解析考勤Excel表
 * 
 * @param file
 *            考勤文件
 */
public static void parseKaoQinExcel(File file) {
	try {
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet sheet = xwb.getSheetAt(0);

		System.out.println(String.format("准备解析：%s, 表格：%s",
				file.getAbsolutePath(), sheet.getSheetName()));

		XSSFRow dayRow = null; // 日期-周几
		XSSFRow timeRow = null;// 打卡时间

		String dayCell = null;
		String timeCell = null;

		// 遍历行(从0开始)，从第3行开始
		for (int i = 9; i < sheet.getPhysicalNumberOfRows(); i = i + 2) {
			dayRow = sheet.getRow(i);
			timeRow = sheet.getRow(i + 1);

			// 忽略空行:真实
			if (dayRow.getFirstCellNum() < 0) {
				continue;
			}

			// 遍历行-列
			for (int j = dayRow.getFirstCellNum(); j < dayRow
					.getPhysicalNumberOfCells(); j++) {
				if (dayRow.getCell(j) == null) {
					continue;
				}
				dayCell = dayRow.getCell(j).toString();
				timeCell = timeRow.getCell(j).toString();
				if (StringUtil.isEmpty(dayCell)) {
					continue;
				}
				System.out.println(String.format(
						"解析 行=%d 列=%d\t>>\t日期=%s\t打卡=%s", i + 1, j,
						dayCell, timeCell));
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}

}