Java XSSFSheet.getRow方法代碼示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的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.XSSFSheet; //導入方法依賴的package包/類
private List<Object> getReferenceList(XSSFSheet sheet, DataValidationConstraint validationConstraint) {
	List<Object> references = new LinkedList<>();

	AreaReference areaRef = new AreaReference(validationConstraint.getFormula1());
	CellReference[] cellRefs = areaRef.getAllReferencedCells();
	for (CellReference cellRef : cellRefs) {
		XSSFSheet referenceListSheet;
		if (cellRef.getSheetName() != null) {
			referenceListSheet = sheet.getWorkbook().getSheet(cellRef.getSheetName());
		} else {
			referenceListSheet = sheet;
		}
		Row row = referenceListSheet.getRow(cellRef.getRow());
		if (row != null) {
			Cell cell = row.getCell(cellRef.getCol());
			if (cell != null) {
				Object cellValue = CellValueParser.getCellValue(cell);
				references.add(cellValue);
			}
		}
	}
	return references;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的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.XSSFSheet; //導入方法依賴的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.XSSFSheet; //導入方法依賴的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;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * 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);
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public static Object getValue(XSSFSheet sheet, int row, int col) {
    try {

        XSSFRow xrow = sheet.getRow(row);
        XSSFCell cell = xrow.getCell(col);

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return (new DateComponentFormatter(
                        ComponentManager.getInstance().getComponentFormatDefaults().getSelectedDateFormat()))
                        .stringToValue(cell.getStringCellValue());
        }

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

    return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * @param sheet 
 * @param pos 位置【row,col】
 * @param style
 * @param v
 */
public static void setCell(final XSSFWorkbook wb,final XSSFSheet sheet,int[] pos,CellStyle style,FontStyle font,String v){
	XSSFRow row = sheet.getRow(pos[0]);
	XSSFCell cell = CellValueUtil.createCellIfNotPresent(row, pos[1]);
	XSSFCellStyle _style = Objects
			.requireNonNull(new XSSFCellStyleLib(wb).get(style),
					"specified style not defined!");
	XSSFFont _font = Objects
			.requireNonNull(new XSSFFontStyleLib(wb).get(font),
					"specified font not defined!");
	_style.setFont(_font);
	setCellProperties(cell, v, _style);
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * 設置單行全部內容
 * @param sheet
 * @param wb
 * @param rowNum
 * @param kVal
 */
public static void setRowMutipleColContent(XSSFSheet sheet,XSSFWorkbook wb,int rowNum,final Map<Integer,String> kVal){
	String message="XSSFSheet must not be null!";
	Objects.requireNonNull(sheet, () -> message);
	XSSFRow row=sheet.getRow(rowNum);
	if(!kVal.isEmpty()){
		kVal.forEach((k,v)->{
			setRowContents(row,k,v,wb);
		});
	}
	
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public static void applyStyleToRange(XSSFSheet sheet, XSSFCellStyle style, int rowStart, int colStart, int rowEnd, int colEnd) {
    for (int r = rowStart; r <= rowEnd; r++) {
        for (int c = colStart; c <= colEnd; c++) {
            XSSFRow row = sheet.getRow(r);

            if (row != null) {
                XSSFCell cell = row.getCell(c);

                if (cell != null) {
                    cell.setCellStyle(style);
                }
            }
        }
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
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;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的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.XSSFSheet; //導入方法依賴的package包/類
private static JSONArray extractDataFromXls(XSSFWorkbook wb) {
    XSSFSheet sheet = wb.getSheetAt(0);

    int rows = sheet.getPhysicalNumberOfRows();
    int cols = sheet.getRow(0).getPhysicalNumberOfCells();
    int startRow = 1; // Pular cabecalho.

    JsonObjectCreator jsonObjCreator = new FedDepJsonObjectCreator();
    JSONArray jArr = new JSONArray();
    for(int row = startRow; row < rows; row++) {
        XSSFRow xssfRow = sheet.getRow(row);
        if(xssfRow != null) {

            String[] rowData = new String[cols];
            for(int col = 0; col < cols; col++) {
                XSSFCell cell = xssfRow.getCell(col);
                if(cell != null) {
                    String datum;
                    if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
                        datum = cell.getStringCellValue();
                    }
                    else if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                        datum = String.valueOf(cell.getNumericCellValue());
                    }
                    else {
                        System.out.println("A célula: [" + row + "," + col + "] não contém um String e nem um Número.");
                        datum = "";
                    }

                    rowData[col] = datum;
                }
            }

            JSONObject jObj = jsonObjCreator.processLine(rowData);
            jArr.add(jObj);
        }
    }

    return jArr;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * Opens the xlsx file based on the filename and sheet passed in.
 * Creates the 2 dimension array filled with the data.
 *
 * @param fname     - File name (relative path from resources directory)
 * @param sheetName - Sheet name within the excel file.
 * @return Object[][] 2 dimensional array with data from the xlsx file
 */
public static String[][] dataProvider(String fname, String sheetName) {
    try {
        ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
        String fileName = Paths.get(classLoader.getResource(fname).toURI()).toString();
        //log.info("Filename to be loaded: " + fileName);

        File xlsFile = new File(fileName);
        FileInputStream file = new FileInputStream(xlsFile);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheet(sheetName);
        int rowCount = sheet.getLastRowNum() + 1;
        int colCount = sheet.getRow(0).getLastCellNum();

        //log.info("Rows: " + rowCount + " Columns: " + colCount);

        String[][] retval = new String[rowCount - 1][colCount];

        for (int i = 1; i < rowCount; i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < colCount; j++) {
                //System.out.print(row.getCell(j) + "\t\t");
                retval[i - 1][j] = row.getCell(j).toString().trim();
            }
            //System.out.println();
        }
        return retval;

    } catch (Exception ex) {
        log.error("Error occurred while loading excel file: " + ex.toString());
        return null;
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
public void writeToSheet(String sheetName, Map<Integer,String> cellValues, Map<Integer,String> headerValues){
	XSSFSheet sheet; 
	if(sheetNames.contains(sheetName)){
		sheet = wb.getSheet(sheetName); 
		}
	else{
		sheet = wb.createSheet(sheetName); 
		sheetNames.add(sheetName); 
		}
	setRowNumber(headerValues);
	
	
	XSSFRow row = sheet.createRow(lastRow); 
	
	for(int i : cellValues.keySet()){
		XSSFCell cell = row.createCell(i); 
		cell.setCellValue(cellValues.get(i));
	}
	
	
	XSSFRow headerRow = sheet.getRow(0);
	if(headerRow == null); 
		headerRow = sheet.createRow(0); 
	
	for(int j : headerValues.keySet()){
		XSSFCell headerCell = headerRow.createCell(j); 
		headerCell.setCellValue(headerValues.get(j));
	}

	incRowNumber(headerValues);
}