Calculate total rows and cells for a spreadsheet

Creating a macro - Writing a Script - Using the API

Calculate total rows and cells for a spreadsheet

Postby zmotiwala » Tue Aug 13, 2019 7:38 pm

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   Expand viewCollapse view
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().
               
               
            }
Libre Office 5.2.5
Windows
zmotiwala
 
Posts: 41
Joined: Wed Dec 28, 2011 5:34 pm

Re: Calculate total rows and cells for a spreadsheet

Postby FJCC » Tue Aug 13, 2019 7:59 pm

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   Expand viewCollapse view
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();
   }
}
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7310
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculate total rows and cells for a spreadsheet

Postby UnklDonald418 » Tue Aug 13, 2019 9:56 pm

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   Expand viewCollapse view
XCellRange usedCellRange = Calc.findUsedRange(sheet);
 
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1225
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests