Java XSSFSheet.getLastRowNum方法代碼示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
List<ClassifiableText> xlsxToClassifiableTexts(File xlsxFile, int sheetNumber) throws IOException, EmptySheetException {
  if (xlsxFile == null ||
      sheetNumber < 1) {
    throw new IllegalArgumentException();
  }

  try (XSSFWorkbook excelFile = new XSSFWorkbook(new FileInputStream(xlsxFile))) {
    XSSFSheet sheet = excelFile.getSheetAt(sheetNumber - 1);

    // at least two rows
    if (sheet.getLastRowNum() > 0) {
      return getClassifiableTexts(sheet);
    } else {
      throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is empty");
    }
  } catch (IllegalArgumentException e) {
    throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is not found");
  }
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
private List<ClassifiableText> getClassifiableTexts(XSSFSheet sheet) {
  List<Characteristic> characteristics = getCharacteristics(sheet);
  List<ClassifiableText> classifiableTexts = new ArrayList<>();

  // start from second row
  for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    Map<Characteristic, CharacteristicValue> characteristicsValues = getCharacteristicsValues(sheet.getRow(i), characteristics);

    // exclude empty rows
    if (!sheet.getRow(i).getCell(0).getStringCellValue().equals("")) {
      classifiableTexts.add(new ClassifiableText(sheet.getRow(i).getCell(0).getStringCellValue(), characteristicsValues));
    }
  }

  return classifiableTexts;
}
 

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 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包/類
/**
 * 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 List<Node> readListOfNodes(final XSSFWorkbook excel, String type){
	List<Node> nodeList = new ArrayList<Node>(); 
	
	final XSSFSheet sheet = excel.getSheet(type);
	if(sheet==null)
	{
		log.error(type+" sheet not found ! ");
	}
	final int lines = sheet.getLastRowNum();
	log.info("found "+lines+" lines in '"+type+"' ");
		
		final XSSFRow firstRow = sheet.getRow(0);
		
		readHeaderFromRow(type, firstRow); 

	for(int line = 2 ;line<=lines;line++) // skip the first line two lines 
	{
		
		final XSSFRow row = sheet.getRow(line);
		
		Node node =	readNodeFromRow(type, row); 
		if(node!=null)
			nodeList.add(node);
		}
	return nodeList; 
	
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
private PdfPTable createTable(PdfWriter writer, LabelFormat labelFormat, int firstRow, int lastRow) throws DocumentException {
    if(firstRow >= lastRow) {
        LOG.info("First row = " + firstRow + ", last row = " + lastRow + " so returning null");
        return null;
    }
    if(lastRow - firstRow > labelFormat.getColumns() * labelFormat.getRows()) {
        LOG.info("There were too many labels requested.  "
                + "You wanted: " + (lastRow - firstRow) + " labels, "
                + "but this label format only allows: " + labelFormat.getColumns() * labelFormat.getRows());
        return null;
    }
    final XSSFSheet sheet = xSSFWorkbook.getSheetAt(0);
    if(lastRow > sheet.getLastRowNum()) {
        LOG.info("last row was larger that the last row number of this sheet: " + sheet.getLastRowNum());
        lastRow = sheet.getLastRowNum();
    }
    if(firstRow >= lastRow) {
        LOG.info("after adjusting the last row, First row = " + firstRow + ", last row = " + lastRow + " so returning null");
        return null;
    }
    
    final PdfPTable table = new PdfPTable(labelFormat.getColumns());
    table.getDefaultCell().setBorder(PdfPCell.NO_BORDER);
    table.setWidthPercentage(labelFormat.getWidthPercentage());   
    LOG.info("The width percentage i found is; " + labelFormat.getWidthPercentage());
    for(int i = firstRow; i <= lastRow; i++) {
        LOG.info("Showing row: " + i);
        showOneLabel(sheet.getRow(i), table, writer, labelFormat);
    }
    if(lastRow == sheet.getLastRowNum()) {
        // we are at the end
        //check to make sure that the last row is filled in
        if(lastRow % labelFormat.getColumns() != 0) {
            for(int x = 0; x < lastRow % labelFormat.getColumns(); x++) {
                final PdfPCell cell = new PdfPCell();
                cell.setFixedHeight(labelFormat.getHeight() * 72);
                cell.addElement(new Paragraph("  "));
                table.addCell(cell);
            }
                
        }
    }
    return table;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
@RequestMapping(value = "/export.pdf")
public ResponseEntity<byte[]> generatePdf(@RequestParam Map<String,String> allRequestParams) 
        throws DocumentException {
    final LabelFormat labelFormat = allRequestParams.containsKey("labelFormatString") ?
            LabelFormat.valueOf(allRequestParams.get("labelFormatString")) :
            LabelFormat.AVERY5160;
    
    createLineTypes(allRequestParams);

    if(xSSFWorkbook == null) {
        LOG.info("The workbook is null so this wouldn't work really");
        return new ResponseEntity<>(showBlank(), HttpStatus.OK);
    }
    if(allRequestParams == null) {
        LOG.info("the allRequestParams param is null so this wouldn't work really");
        return new ResponseEntity<>(showBlank(), HttpStatus.OK);
    }
    
    // we create a new document with zero left/right margins
    // we calculate the top and bottom margin
    final float topMargin = (PageSize.LETTER.getHeight() - labelFormat.getRows() * labelFormat.getHeight() * 72) / 2;
    final Document document = new Document(PageSize.LETTER, 0,0,topMargin,topMargin);
    final ByteArrayOutputStream baos = new ByteArrayOutputStream();
    
    final PdfWriter writer = PdfWriter.getInstance(document, baos);
    
    document.open();
    
    final XSSFSheet sheet = xSSFWorkbook.getSheetAt(0);
    final int rowCount = sheet.getLastRowNum();
    LOG.info("With: " + rowCount + " rows, "
            + "and " + labelFormat.getRows() * labelFormat.getColumns() + " labels per page, "
            + "we need: " + (1 + rowCount / (labelFormat.getRows() * labelFormat.getColumns())) + " pages");
    for(int i = 0 ; i <=  rowCount / labelFormat.getLabelsPerPage(); i++) {
        LOG.info("Showing page: " + i);
        int firstRow = i * labelFormat.getLabelsPerPage();
        int lastRow =  firstRow + labelFormat.getLabelsPerPage();
        if(lastRow > rowCount) lastRow = rowCount;
        LOG.info("At i = " + i + ", we need to show rows " + firstRow + " to " + lastRow);
        if(lastRow > firstRow) {
            final PdfPTable t = createTable(writer, labelFormat, firstRow, lastRow);
            document.add(t);
            t.setComplete(true);
            LOG.info("i = " + i + ", added the table and adding a new page");
            document.newPage();
        }
    } 
    
    document.close();
    
    final HttpHeaders httpHeaders = new HttpHeaders();
    httpHeaders.setContentType(MediaType.APPLICATION_PDF);
    final ResponseEntity<byte[]> result = new ResponseEntity<>(baos.toByteArray(), 
            httpHeaders, HttpStatus.OK);
    return result;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //導入方法依賴的package包/類
/**
 * 創建內置字典
 *
 * @param xlsxSheet Excel 頁簽
 * @return 多語言翻譯字典, Map<"原文", "譯文">
 *
 */
private static Map<String, String> createInnerDict(XSSFSheet xlsxSheet) {
    if (xlsxSheet == null) {
        // 如果參數對象為空,
        // 則直接退出!
        return Collections.emptyMap();
    }

    // 獲取總行數
    int rowNum = xlsxSheet.getLastRowNum();

    if (rowNum <= 0) {
        // 如果文件內容為空,
        // 則直接退出!
        return Collections.emptyMap();
    }

    // 創建內置字典
    Map<String, String> innerDict = new HashMap<>();

    for (int i = 1; i <= rowNum; i++) {
        // 獲取一行數據
        XSSFRow xlsxRow = xlsxSheet.getRow(i);

        // 獲取原文和譯文,
        // A 列 = 原文, B 列 = 譯文
        String origStr = XSSFUtil.getStrCellVal(xlsxRow.getCell(0));
        String langStr = XSSFUtil.getStrCellVal(xlsxRow.getCell(1));

        if (origStr == null ||
            origStr.isEmpty() ||
            langStr == null ||
            langStr.isEmpty()) {
            continue;
        }

        // 將原文譯文添加到字典
        innerDict.put(origStr, langStr);
    }

    return innerDict;
}