I am trying to update my existing calc sheet through java program , but when i try to insert some data into my sheet ,it is first loaded and opened to insert data . My requirement is that my sheet should not be opened while updating it . it is very urgent . Please help me out . Following is the code that I m using to insert some data :
Code: Select all
import com.sun.star.beans.PropertyValue;
import com.sun.star.container.XIndexAccess;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiComponentFactory;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.table.CellContentType;
import com.sun.star.table.XCell;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;
public class ConnectToCalc
{
public static void main(String args[])
{
XComponentContext xContext = null;
try
{
xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
System.out.println("Connected to a running office ...");
}
catch( Exception e)
{
e.printStackTrace(System.err);
System.exit(1);
}
XSpreadsheetDocument myDoc = null;
System.out.println("Opening an empty Calc document........");
myDoc = openCalc(xContext);
//-----------------------------------Step 3----------------------------------
// get the sheet an insert some data.
// Get the sheets from the document and then the first from this container.
// Now some data can be inserted. For this purpose get a Cell via
// getCellByPosition and insert into this cell via setValue() (for floats)
// or setFormula() for formulas and Strings
//---------------------------------------------------------------------------
XSpreadsheet xSheet=null;
try
{
// System.out.println("Getting spreadsheet") ;
// XSpreadsheets xSheets = myDoc.getSheets() ;
// XIndexAccess oIndexSheets = (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSheets);
// xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, oIndexSheets.getByIndex(0));
}
catch (Exception e)
{
System.out.println("Couldn't get Sheet " +e);
e.printStackTrace(System.err);
}
System.out.println("Creating the Header") ;
//insertIntoCell(0,0,"Name",xSheet,"");
System.out.println("Fill the lines");
//insertIntoCell(0,19,"Nitin",xSheet,"");
// System.out.println(getDataFromCell(0,19,xSheet,""));
}//End Of Main Block
public static XSpreadsheetDocument openCalc(XComponentContext xContext)
{
//define variables
XMultiComponentFactory xMCF = null;
XComponentLoader xCLoader;
XSpreadsheetDocument xSpreadSheetDoc = null;
XComponent xComp = null;
XComponent xCom = null;
try
{
// get the service manager from the office
xMCF = xContext.getServiceManager();
// create a new instance of the the desktop
Object oDesktop = xMCF.createInstanceWithContext("com.sun.star.frame.Desktop", xContext );
// query the desktop object for the XComponentLoader
xCLoader = ( XComponentLoader ) UnoRuntime.queryInterface(XComponentLoader.class, oDesktop );
PropertyValue [] szEmptyArgs = new PropertyValue [0];
//String strDoc = "private:factory/scalc";
java.io.File sourceFile = new java.io.File("D:\\nitin.ods");
String strDoc = "file:///"+sourceFile.getCanonicalPath().replace('\\', '/');
java.io.File saveFile =new java.io.File("D:\\nitin.ods");
//String sSaveUrl="file:///"+saveFile.getCanonicalPath().replace('\\', '/');
xComp = xCLoader.loadComponentFromURL(strDoc, "_default", 0, szEmptyArgs );
xSpreadSheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xComp);
XSpreadsheet xSheet=null;
try
{
System.out.println("Getting spreadsheet") ;
XSpreadsheets xSheets = xSpreadSheetDoc.getSheets() ;
XIndexAccess oIndexSheets = (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSheets);
xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, oIndexSheets.getByIndex(0));
}
catch (Exception e)
{
System.out.println("Couldn't get Sheet " +e);
e.printStackTrace(System.err);
}
insertIntoCell(2,1,"Message",xSheet,"");
insertIntoCell(2,19,"Hi",xSheet,"");
com.sun.star.frame.XStorable xStorable =
(com.sun.star.frame.XStorable)UnoRuntime.queryInterface(
com.sun.star.frame.XStorable.class, xComp );
xStorable.storeAsURL( strDoc.toString(), szEmptyArgs );
com.sun.star.util.XCloseable xCloseable = (com.sun.star.util.XCloseable)
UnoRuntime.queryInterface(com.sun.star.util.XCloseable.class,xComp );
if (xCloseable != null ) {
xCloseable.close(false);
} else
{
com.sun.star.lang.XComponent xCompp = (com.sun.star.lang.XComponent)
UnoRuntime.queryInterface(
com.sun.star.lang.XComponent.class, xComp );
xCompp.dispose();
}
}
catch(Exception e)
{
System.err.println(" Exception " + e);
e.printStackTrace(System.err);
}
return xSpreadSheetDoc;
}
public static void insertIntoCell(int CellX, int CellY, String theValue, XSpreadsheet TT1, String flag)
{
XCell xCell = null;
try
{
xCell = TT1.getCellByPosition(CellX, CellY);
}
catch (com.sun.star.lang.IndexOutOfBoundsException ex)
{
System.err.println("Could not get Cell");
ex.printStackTrace(System.err);
}
if (flag.equals("V"))
{
xCell.setValue((new Float(theValue)).floatValue());
}
else
{
xCell.setFormula(theValue);
}
}//End of method insertIntoCell block
public static String getDataFromCell(int CellX, int CellY, XSpreadsheet TT1, String flag)
{
XCell xCell = null;
String str = null;
try
{
xCell = TT1.getCellByPosition(CellX, CellY);
}
catch (com.sun.star.lang.IndexOutOfBoundsException ex)
{
System.err.println("Could not get Cell");
ex.printStackTrace(System.err);
}
if (flag.equals("V"))
{
Double val= xCell.getValue();
}
else
{
str= xCell.getFormula();
System.out.println(CellContentType.FORMULA_value);
}
return str;
}//End of method getDataFromCell block
}
