Java XSSFRow.getLastCellNum方法代碼示例

import org.apache.poi.xssf.usermodel.XSSFRow; //導入方法依賴的package包/類
/**
 * @param newSheet the sheet to create from the copy.
 * @param sheet the sheet to copy.
 * @param copyStyle true copy the style.
 */
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        XSSFRow srcRow = sheet.getRow(i);
        XSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
   //Util.copyPictures(newSheet,sheet) ;
}
 

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 void readHeader(XSSFRow firstRow, int startCell,
		List<Cell> headerList, List<Cell> keyList) {
	for(int i = startCell; i < firstRow.getLastCellNum();  i++) {
		if(firstRow.getCell(i)!=null){
			String header = trim(firstRow,i ); 
			if(header!= null && !header.isEmpty()){
				if(isKey(header)){
					keyList.add(new Cell(i, header)); 
				}
				else{
					headerList.add(new Cell(i,header)); 
					}
				}
			}
		}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入方法依賴的package包/類
public List<HashMap<String, String>> getAllData(String testName) {
	List<HashMap<String, String>> allTestData = new ArrayList<HashMap<String, String>>();

	XSSFRow nameRow = testDataSheet.getRow(0);
	for (int row = 1; row <= rowEnd; row++) {

		XSSFRow currentRow = testDataSheet.getRow(row);

		if (getCellDataAsString(currentRow.getCell(0)).equalsIgnoreCase(
				testName)) {
			int cols = currentRow.getLastCellNum();
			HashMap<String, String> map = new HashMap<String, String>();
			for (int col = 0; col < cols; col++) {
				map.put(getCellDataAsString(nameRow.getCell(col)),
						getCellDataAsString(currentRow.getCell(col)));
			}
			allTestData.add(map);
		}
	}
	logger.debug("Returning test data for " + testName + " Data -> "
			+ allTestData.toString());
	logger.trace("Returning test data for " + testName + " Data -> "
			+ allTestData.toString());
	return allTestData;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入方法依賴的package包/類
private String fromXSSFRowtoCSV(XSSFRow row){
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i=0;i<l;i++){
        XSSFCell cell = row.getCell((short)i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }

        csvRow.append(cellValue);

        if (i<l){
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();
}
 

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 ArrayList<String> readHeaders(XSSFRow row, short from) {
    ArrayList<String> headers = new ArrayList<>();
    short maxColIx = row.getLastCellNum();
    for (short colIx = this.firstColumn ; colIx < maxColIx ; colIx++) {
        XSSFCell cell = row.getCell(colIx);
        if (cell == null) {
            break;
        }
        String value = asString(cell);
        if (value == null || value.isEmpty())
            break;
        headers.add(value);
    }
    return headers;
}
 

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包/類
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 sheet to another sheet at a specific (row, column) position
 * 
 * @param parentSheet the sheet to copy into
 * @param parentSheetRow the row inside parentSheet where we start to copy
 * @param parentSheetColumn the column inside parentSheet where we start to copy
 * @param sheet the sheet that is copied
 * @param copyStyle true to copy the style
 * @return column number
 */
public static int copyToSheet(XSSFSheet parentSheet, int parentSheetRow, int parentSheetColumn, XSSFSheet sheet, boolean copyStyle) {
	int maxColumnNum = 0;
	Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;
	for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
		XSSFRow srcRow = sheet.getRow(i);
		XSSFRow destRow;
		// subreport is not the first cell in a row
		if ((parentSheetColumn > 0) && (i == sheet.getFirstRowNum())) {
			destRow = parentSheet.getRow(parentSheetRow);
		} else {
			destRow = parentSheet.getRow(parentSheetRow+i);
			if (destRow == null) {
				destRow = parentSheet.createRow(parentSheetRow + i);
			}
		}
		if (srcRow != null) {
			copyRow(sheet, parentSheet, parentSheetRow, parentSheetColumn, srcRow, destRow, styleMap);
			if (srcRow.getLastCellNum() > maxColumnNum) {
				maxColumnNum = srcRow.getLastCellNum();
			}
		}
	}
	for (int i = 0; i <= maxColumnNum; i++) {
		parentSheet.setColumnWidth(i, sheet.getColumnWidth(i));
	}
	return maxColumnNum;
}
 

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包/類
public static ArrayList<ArrayList<ArrayList<Object>>>  readExcel2007(File file){
	try{
		ArrayList<ArrayList<ArrayList<Object>>> sheetArray = new ArrayList<ArrayList<ArrayList<Object>>> ();
		XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
		for(int sheetNum = 0;sheetNum < wb.getNumberOfSheets();sheetNum++){
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFSheet sheet = wb.getSheetAt(sheetNum);
			XSSFRow row;
			XSSFCell cell;
			Object value;
			for(int i = 0 , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if(row == null){
					//����ȡ��Ϊ��ʱ
					if(i != sheet.getPhysicalNumberOfRows()){//�ж��Ƿ������һ��
						rowList.add(colList);
					}
					continue;
				}else{
					rowCount++;
				}
				for( int j = 0 ; j <= row.getLastCellNum() ;j++){
					cell = row.getCell(j);
					if(cell == null ){
						//���õ�Ԫ��Ϊ��
						if(j != row.getLastCellNum()){//�ж��Ƿ��Ǹ��������һ����Ԫ��
							colList.add("");
						}
						continue;
					}
					switch(cell.getCellType()){
					 case XSSFCell.CELL_TYPE_STRING:  
		                    value = cell.getStringCellValue();  
		                    break;  
		                case XSSFCell.CELL_TYPE_NUMERIC:  
		                    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:  
		                    value = Boolean.valueOf(cell.getBooleanCellValue());
		                    break;  
		                case XSSFCell.CELL_TYPE_BLANK:  
		                    value = "";  
		                    break;  
		                default:  
		                    value = cell.toString();  
					}// end switch
					colList.add(value);
				}//end for j
				rowList.add(colList);
			}//end for i
			sheetArray.add(rowList);
		}// end sheetNum
		return sheetArray;
	}catch(Exception e){
		return null;
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入方法依賴的package包/類
private void readWorksheet() {
	worksheet = new Worksheet();
	worksheets.addWorksheet(worksheet);

	worksheet.setName(poiWorksheet.getSheetName());

	cells = new Cells();
	worksheet.setCells(cells);

	int firstRowNum = poiWorksheet.getFirstRowNum();
	int lastRowNum = poiWorksheet.getLastRowNum();

	// 讀取列寬信息
	int maxColumnNum = -1;
	for (int i = firstRowNum; i <= lastRowNum; i++) {
		XSSFRow row = poiWorksheet.getRow(i);
		if (row != null) {
			int lastCellNum = row.getLastCellNum();
			if (lastCellNum > maxColumnNum) {
				maxColumnNum = lastCellNum;
			}
		}
	}
	int[] columnWidths = new int[maxColumnNum + 1];
	for (int i = 0; i < maxColumnNum; i++) {
		columnWidths[i] = poiWorksheet.getColumnWidth(i);
		columnWidths[i] = (int) (columnWidths[i]
				* XSSFWorkbook.DEFAULT_CHARACTER_WIDTH / 256);
	}
	cells.setColumnWidths(columnWidths);

	// 讀取sheet中的行信息
	rowCollection = new RowCollection();
	cells.setRowCollection(rowCollection);
	for (int i = 0; i < firstRowNum; i++) {
		row = new Row();
		row.setHeight(poiWorksheet.getDefaultRowHeight());
		rowCollection.addRow(row);
	}
	for (int i = firstRowNum; i <= lastRowNum; i++) {
		poiRow = poiWorksheet.getRow(i);
		if (poiRow == null) {
			row = new Row();
			row.setHeight(poiWorksheet.getDefaultRowHeight());
		} else {
			row = readRow(poiRow, i);
		}
		rowCollection.addRow(row);
	}

	// 讀取合並單元格信息
	readMergedCells();
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入方法依賴的package包/類
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);
       worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false);
       XSSFRow newRow = worksheet.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        newCell.setCellStyle(oldCell.getCellStyle());

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                    )),
                    cellRangeAddress.getFirstColumn(),
                    cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    
    newRow.setHeight(sourceRow.getHeight());
    
    return newRow;
}