How to read from cells in existing ODS spreadsheet using SDK and C#

English
, ,
#1

I am writing a C# program to manipulate data from an ODS spreadsheet using the current LibreOffice SDK. I’ve used the various samples to write a test program that will open a spreadsheet file and get a cell, but can’t find anything that shows how to read data from that cell. I’ve spent days searching and reading documentation, but nothing explains how to do this very simple task.

I can get a cell with:

xCell = xSheet.getCellByPosition(1,2);

but I don’t know how to get the contents (value, string, formula). Lots of examples show how to set the cell contents, but nothing shows how to retrieve the cell contents.

Any help would be very greatly appreciated. Thank you!

#3

Please see the examples in this folder, using getString and getValue methods.

#4
#8

Thanks, but that example refers to get/set of cell contents using the spreadsheet’s BASIC language. It doesn’t help with writing an external program, for example in C# as I am attempting, to access the data in a spreadsheet.

#10

The API is exactly the same. Once you know how instanciate some UNO object and access its properties and methods in one langauge, then you have the exact same properties and methods when you instanciate the same type of object in any other supported langauge. In this particular case, a sheet cell has get/set methods for the formula, string, and value. Property FormulaLocal is the localized formula as displayed in the formula bar. MRI reveals all this for any supported langauge and generates code along the way.

#2

[Tutorial] Introduction into object inspection with MRI

#5

Don’t ignore @Villeroy here! MRI (or XRay) is absolutely necessary for doing anything with LO macros or API. One way I do it is to catch an object in LO BASIC then inspect it with MRI or XRay. Then after I have figured out how it works, I go back and use it in my API calls.
Install MRI.
Then just create a module for BASIC with Tools>Macros>Organize>BASIC>Organize(button)>click on the sample spreadsheet>New(button) then go back to Tools>Macros>Edit macros and use something like:

Sub Main
  Dim Sheet As Object
  Sheet = ThisComponent.getSheets().getByIndex(0)
  MRI Sheet
End Sub

Now you can interact with your first sheet as an object…actually drill down into, say, cell 0,0, and look at what is available and how it goes together. Try capturing that in your BASIC macro, and if it works as expected, then use what you know for your language of choice, like C#.

#6

The following code has been generated by the MRI tool:

using System;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.uno;

public class Snippet {
public void snippet(XComponentContext xContext, object oInitialTarget)
{
	try
	{
		XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument) oInitialTarget;
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		
		XNameAccess xNameAccess = (XNameAccess) xSpreadsheets;
		XSpreadsheet xSpreadsheet = (XSpreadsheet) xNameAccess.getByName("Sheet2").Value;
		XCellRange xCellRange = (XCellRange) xSpreadsheet;
		XCell xCell = xCellRange.getCellByPosition(1, 2);
		
		String sFormula = xCell.getFormula();
		
		XTextRange xTextRange = (XTextRange) xCell;
		
		String sString = xTextRange.getString();
		
		double fValue = xCell.getValue();
		
		XPropertySet xPropSet = (XPropertySet)xCell;
		String sFormulaLocal = (String) xPropSet.getPropertyValue("FormulaLocal").Value;
		
		CellContentType nCellContentType = (CellContentType) xPropSet.getPropertyValue("FormulaResultType").Value;
		;
		
	}
	catch (NoSuchElementException e)
	{
		// getByName
		Console.WriteLine(e.Message);
	}
	catch (WrappedTargetException e)
	{
		// getByName, getPropertyValue
		Console.WriteLine(e.Message);
	}
	catch (RuntimeException e)
	{
		// getByName
		Console.WriteLine(e.Message);
	}
	catch (IndexOutOfBoundsException e)
	{
		// getCellByPosition
		Console.WriteLine(e.Message);
	}
	catch (UnknownPropertyException e)
	{
		// getPropertyValue
		Console.WriteLine(e.Message);
	}
}
}
1 Like
#9

Bingo! That’s exactly what I was looking for. There are, in fact, simple calls that get the various kinds of data from a cell.

I spent a few hours trying to figure out how to use MRI, without making any progress. The fact that it produced the code you showed means that if I spent more time on it I probably could have gotten the information I needed, but suspect it would have taken me a very long time. I’m an old-fashioned programmer who expects simple solutions to simple problems like “read a string from a cell”.

Anyhow, thanks! I suspect that your contribution will help many more people than just me.

#11

Call Tools>Add-Ons>MRI
The “Properties” tab shows all properties of the current document. Switch to the “Methods” tab.
Double-click the line starting with getSheets()
Double-click the line starting with getByName()
Fill in the prompted positional arguments addressing your cell.
Hit Ctrl+H for the code window.
menu:Tools>Language>C# CLI
MRI can even set simple values (numbers, strings) after switching menu:Mode>Set. Now you are prompted for a numeric value when you double-click setValue() and the value will be writen into the cell.
Methods like get/setFoo() have a corresponding pseudo-property Foo. This is just a convenience for scripting language where obj.setFoo(42) is equivalent to obj.Foo = 42

In order to call MRI from your own code, you’ve got to instanciate a service named “mytools.Mri” and call its inspect method with the object your are interested in.

1 Like
#12

Well if I’d have realized that MRI did that…Thanks!

#13

More thanks to Villeroy for the guidance on the MRI tool, and for his patience with me.

One thing I would like to add about reading cells from a spreadsheet: you can read a cell using different formats to get the contents in different forms. For example, if a cell contains a value 123 but it is being displayed as “0123” (with a leading zero), getting it as a value gives a double containing 123, but getting it as text gives “0123” as it is being displayed. Likewise, with a cell that has a formula, you can get the formula itself or the value being computed as a string (as displayed) or as a double (as computed). This info may be useful to others who find this post.

#14

Every sheet cell has a value (double), a string and a formula at the same time.
Formulas starting with = yield a calculated formula expression as we know it.
The formula of a string is the string itself.
The formula of a numeric string 0123 is '0123 with a leading apostrophe.
The formula of a string starting with a = is marked by a leading apostrophe too.
The formula of a “date value” is the integer day number.
The formula of a “clock time value” is the fraction of a day number (0.75 is 6pm).

oCell.setFormula("123") 
REM does the same as 
oCell.setValue(123)

oCell.setFormula("'123") 
REM with apostrophe does the same as
oCell.setString("123")

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.