Calculate total rows and cells for a spreadsheet

[zmotiwala]

Using java and the uno api is there anyway to get total number of rows used in a spread sheet and columns? Some way to identify large xls files.

Code: Select all

XSpreadsheets sheets = spreadsheetDocument.getSheets();
            XIndexAccess sheets_XIndexAccess = OfficeUtils.cast( XIndexAccess.class,  sheets); 
            int ns = sheets_XIndexAccess.getCount();
            
            String name[]= sheets.getElementNames();
            XSpreadsheet sheet = null;
           
            for(int i=1;i<ns;i++){
                System.out.println(name[i]);
                sheet = OfficeUtils.cast(XSpreadsheet.class, sheets.getByName(name[i]));
                sheet.createCursor().
               
                
            }

[FJCC]

I don't do Java but I recorded this with the MRI extension. The aCellRangeAddress object has EndColumn and EndRow properties that will tell you the extent of the used area.

Code: Select all

import com.sun.star.container.NoSuchElementException;
import com.sun.star.container.XNameAccess;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.sheet.XCellRangeAddressable;
import com.sun.star.sheet.XSheetCellCursor;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.sheet.XUsedAreaCursor;
import com.sun.star.table.CellRangeAddress;
import com.sun.star.uno.RuntimeException;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;

public static void snippet(XComponentContext xComponentContext, Object oInitialTarget)
{
	try
	{
		XSpreadsheetDocument xSpreadsheetDocument = UnoRuntime.queryInterface(
			XSpreadsheetDocument.class, oInitialTarget);
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		
		XNameAccess xNameAccess = UnoRuntime.queryInterface(
			XNameAccess.class, xSpreadsheets);
		XSpreadsheet xSpreadsheet = UnoRuntime.queryInterface(
			XSpreadsheet.class, xNameAccess.getByName("Sheet1"));
		
		XSheetCellCursor xSheetCellCursor = xSpreadsheet.createCursor();
		
		XUsedAreaCursor xUsedAreaCursor = UnoRuntime.queryInterface(
			XUsedAreaCursor.class, xSheetCellCursor);
		xUsedAreaCursor.gotoEndOfUsedArea(false);
		
		XCellRangeAddressable xCellRangeAddressable = UnoRuntime.queryInterface(
			XCellRangeAddressable.class, xSheetCellCursor);
		CellRangeAddress aCellRangeAddress = xCellRangeAddressable.getRangeAddress();
		
	}
	catch (NoSuchElementException e1)
	{
		// getByName
		e1.printStackTrace();
	}
	catch (WrappedTargetException e2)
	{
		// getByName
		e2.printStackTrace();
	}
	catch (RuntimeException e3)
	{
		// getByName
		e3.printStackTrace();
	}
}

[UnklDonald418]

I too am not a Java programmer, but I believe you will find a simple answer to your question at
http://fivedots.coe.psu.ac.th/~ad/jlop/
Chapter 27 Section 4 explains how to "get the cell range which spans the used area of a sheet"

Edit: Correction, it is actually Chapter 21 that shows Code: Select all XCellRange usedCellRange = Calc.findUsedRange(sheet);