Java XSSFRow類代碼示例

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
public static void writeXLSXFile() throws IOException {

			// @SuppressWarnings("resource")
			XSSFWorkbook wbObj = new XSSFWorkbook();
			XSSFSheet sheet = wbObj.createSheet(sheetName);
			for (int row = 0; row < tableData.size(); row++) {
				XSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					XSSFCell cell = rowObj.createCell(col);
					cell.setCellValue(rowData.get(col));
					logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
				}
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

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 static void insertDedicatedStyleCell(XSSFRow row,int col,XSSFCellStyle style,String value){
	String message="XSSFRow or XSSFCellStyle must not be null!";
	Objects.requireNonNull(row, () -> message);
	Objects.requireNonNull(style, () -> message);
	XSSFCell cell=createCellIfNotPresent(row,col);
	setCellProperties(createCellIfNotPresent(row, col), value, style);
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
public static XSSFRow createWageringHeaderRow(XSSFSheet sheet, int row, int col) {
    XSSFRow headerRow = sheet.createRow(row++);

    col = seedCols(col, headerRow);

    List<String> columns = Arrays.asList("winPayoff", "placePayoff",
            "showPayoff", "totalWpsPool", "doublePayoff", "doublePool", "exactaPayoff",
            "exactaPool", "trifectaPayoff", "trifectaPool", "superfectaPayoff",
            "superfectaPool", "pick3Payoff", "pick3Pool", "pick4Payoff", "pick4Pool",
            "pick5Payoff", "pick5Pool");
    for (String column : columns) {
        CellUtil.createCell(headerRow, col++, column);
    }

    return headerRow;
}
 

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包/類
public Datum read(XSSFRow row) {

            short columnIndex = firstColumn;
            Datum datum = new Datum();
            for (String header : headers) {
                Object value = get(row.getCell(columnIndex++));
                if (value != null) {
                    datum.put(header, value);
                }
            }
            if (datum.isEmpty())
                return null;
            int rowNum = row.getRowNum();
            datum.put(Datum.AUTO_ID, resource.getName() + ":" + rowNum);
            datum.put(Datum.ROW_ID, rowNum);
            return datum;
        }
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}
 

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包/類
/**
 * Read the Excel 2010
 * 
 * @param path
 *            the path of the excel file
 * @return
 * @throws IOException
 */
public static String readXlsx(String path) throws IOException {
	InputStream is = new FileInputStream(path);
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
	StringBuffer sb = new StringBuffer("");
	// Read the Sheet
	for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
		if (xssfSheet == null) {
			continue;
		}
		// Read the Row
		for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (xssfRow != null) {
				XSSFCell no = xssfRow.getCell(0);
				XSSFCell name = xssfRow.getCell(1);
				sb.append(no + ":" + name);
				sb.append(";");
			}
		}
	}
	return sb.toString().substring(0, sb.toString().length() - 1);
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
private Node readNodeFromRow(String type, XSSFRow row){
	if(trim(row,0)!=null&& (!trim(row,0).isEmpty())){
		switch(nodeTypes.valueOf(type)){
		case Machine: 
			return readMachineFromRow(row); 
		case Software: 
			return readSoftwareFromRow(row); 
		case Resource: 
			return readResourceFromRow(row); 
		case Application: 
			return readApplicationFromRow(row);
		default : 
			return null; 
		}
		
	}
	else {
		return null; 
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
private void readOptCell(XSSFRow row, Node node, List<Cell> headerList) {
	for(Cell headercell : headerList) {
		String currenFreeValue = trim(row,headercell.getCellNumber()); 
		String keyValue = null;
		if(headercell.getKeyRowNumber()>0){
			keyValue = trim(row, headercell.getKeyRowNumber()); 
		}
		if(currenFreeValue!= null && currenFreeValue!= ""  ){
			if(keyValue!= null && keyValue!=""){
				node.getValueList().add(new Cell(headercell.getCellNumber(),currenFreeValue, headercell.getKeyRowNumber(), keyValue ));
				}
			else{
				node.getValueList().add(new Cell(headercell.getCellNumber(),currenFreeValue )); }
			}	
		}
		
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //導入依賴的package包/類
private void readHeaderFromRow(String type, XSSFRow firstRow) {
	switch(nodeTypes.valueOf(type)){
	case Machine: 
		readMachineHeaderFromRow(firstRow); 
		break; 
	case Software:
		readSoftwareHeaderFromRow(firstRow);
		break; 
	case Resource: 
		readResourceHeadFromRow(firstRow);
		break; 
	case Application: 
		readApplicationHeadFromRow(firstRow);
		break; 
	}

}
 

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<String[]> readAllLines(int sheetIndex){
	XSSFSheet sheet = getWorkBook().getSheetAt(sheetIndex);
	
	List<String[]> rows = CollectionFactory.newArrayList();
	for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
		XSSFRow row = sheet.getRow(i);
		if (row != null) {
			String[] rowValues = new String[row.getPhysicalNumberOfCells()];
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				rowValues[j] = (row.getCell(j) != null)? getReader().read(row.getCell(j)) : "";
			}
			rows.add(rowValues);
		}
	}
	return rows;
}