Inserting Sheets in Spreadsheet through c#

Question

I have created a a project that reads different files and puts then in different sheets with a spreadsheet. I have used Open office calc spreadsheet therefore used the following code to open a blank file:

public XSpreadsheet getSpreadsheet(int nIndex, XComponent xComp)
{
    XSpreadsheets xSheets = ((XSpreadsheetDocument)xComp).getSheets();
    XIndexAccess xSheetsIA = (XIndexAccess)xSheets;
    XSpreadsheet xSheet =(XSpreadsheet)xSheetsIA.getByIndex(nIndex).Value;

    return xSheet;         
}

I call a sheet to be used like so:

XSpreadsheet newSheet = getSpreadsheet(sheetIndex, xComp);

where xComp is:

string filePathway = @"file:///c:/temp/blank.ods";  
PropertyValue[] propVals = new PropertyValue[0];
XComponent oCalcuDoc = oDesktop.loadComponentFromURL(filePathway, "_blank", 0, propVals);

However my problem is that file blank.ods needs to be set up with the number of sheets that will be required already inserted into the spreadsheet before the application is run. This is not ideal as the number of sheets needed is not always known. Is there a way of inserting sheets from within my application?

Any help would be appreciated.

Lasse Christiansen · Accepted Answer · 2012-08-23T11:15:33.320

I just took a brief look at the OpenOffice API and found this: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XSpreadsheets.html

.. it states that the interface XSpreadsheets:

provides methods to access the spreadsheets by name and to insert, copy, remove and rearrange spreadsheets.

It includes methods like:

insertNewByName, which according to the API documentation:

inserts a new sheet into the collection.

see: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XSpreadsheets.html#insertNewByName

... and so on.

I'm by no means an OpenOffice API expert - I just took a brief view at their API documenation - hope this can point you in the right direction.

Actually, the documentation contains an example of how to add a new sheet in a document:

 /** Inserts a new empty spreadsheet with the specified name.
 @param xDocument The XSpreadsheetDocument interface of the document.
 @param aName The name of the new sheet.
 @param nIndex The insertion index.
 @return The XSpreadsheet interface of the new sheet.
 */
 public com.sun.star.sheet.XSpreadsheet insertSpreadsheet(
     com.sun.star.sheet.XSpreadsheetDocument xDocument,
     String aName, short nIndex ) {

     // Collection of sheets
     com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
     com.sun.star.sheet.XSpreadsheet xSheet = null;

     try {
         xSheets.insertNewByName(aName, nIndex);
         xSheet = xSheets.getByName( aName );
     } catch (Exception ex) {
     }

     return xSheet;
 }

The example can be seen at the bottom of this page: http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Working_With_Spreadsheet_Documents

Inserting Sheets in Spreadsheet through c#

1 Answers1