Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
zmotiwala
Posts: 46 Joined: Wed Dec 28, 2011 5:34 pm
Post
by 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
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
FJCC
Moderator
Posts: 9277 Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA
Post
by 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
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();
}
}
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
UnklDonald418
Volunteer
Posts: 1549 Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA
Post
by 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
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11