Macroの杜(JavaScript編)

< 戻る 【 Calc 】

CCI-)[Calc]Document間のCopy

Copy元 ファイル
　A1 : LibreOffice ← Text
　A2 : JavaScript  ← Text
　A3 : Macro       ← Text
　A4 : 1           ← Value
　A5 : =A4*10      ← Formula
を現在のファイルのB1～B5にコピーする。

値としてコピーする場合は
　com.sun.star.sheet.XCellRangeData
　getDataArray() /setDataArray()
を使う。
B5セルには、式でなく値=10 がコピーされる。


//Copy content cell from Spreadsheet to other
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.comp.helper.Bootstrap);
importClass(Packages.com.sun.star.comp.helper.BootstrapException);
importClass(Packages.com.sun.star.frame.XComponentLoader);
importClass(Packages.com.sun.star.lang.XMultiComponentFactory);
importClass(Packages.com.sun.star.uno.Exception);
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.uno.XComponentContext);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheet);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.javax.swing.JOptionPane);

importClass(Packages.com.sun.star.sheet.XCellRangeData);

importClass(Packages.java.lang.Thread);

try{
	xContext = XSCRIPTCONTEXT.getComponentContext();
	xMultiComponentFactory = xContext.getServiceManager();
	xtest = xMultiComponentFactory.createInstanceWithContext("com.sun.star.frame.Desktop", xContext);
	xcomponentloader = UnoRuntime.queryInterface(XComponentLoader,xtest);

	loadURL = "file:///C:/temp/test.ods";
	propertyvalue = new Array();
	xSorceDoc = xcomponentloader.loadComponentFromURL(loadURL, "_blank", 0, propertyvalue);

	xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, xSorceDoc);
	xSpreadsheets = xSpreadsheetDocument.getSheets();
	xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
	oSheet = xIndexAccess.getByIndex(0);
	oRng = UnoRuntime.queryInterface(XSpreadsheet, oSheet);
	oSourceRange = oRng.getCellRangeByName( "A1:A5" );
	xRngData = UnoRuntime.queryInterface(XCellRangeData, oSourceRange);
	oCopyData = xRngData.getDataArray()

	// Paste Doc
	oDoc = XSCRIPTCONTEXT.getDocument();
	xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
	xSpreadsheets = xSpreadsheetDocument.getSheets();
	xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
	oSheet = xIndexAccess.getByIndex(0);
	oRng = UnoRuntime.queryInterface(XSpreadsheet, oSheet);
	oPasteRange = oRng.getCellRangeByName("B1:B5");
	xPasteData = UnoRuntime.queryInterface(XCellRangeData, oPasteRange);
	xPasteData.setDataArray(oCopyData)
	
	Thread.sleep(3000); // 3sec
	xSorceDoc.dispose();

	JOptionPane.showMessageDialog(null, "Success");
}catch(e){
//Error Handling
errorname = e.name;
errormsg = e.message;
eDisp= errorname + "\n" + errormsg;
JOptionPane.showMessageDialog(null, eDisp);
}

 
	一方、式をコピーする場合は
		com.sun.star.sheet.XCellRangeFormula
		getFormulaArray() /　setFormulaArray()
	を使う。
	
	B5セルには、元の式 = A1*10 がコピーされるので、表示値 = 0 となる


//Copy content cell from Spreadsheet to other
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.comp.helper.Bootstrap);
importClass(Packages.com.sun.star.comp.helper.BootstrapException);
importClass(Packages.com.sun.star.frame.XComponentLoader);
importClass(Packages.com.sun.star.lang.XMultiComponentFactory);
importClass(Packages.com.sun.star.uno.Exception);
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.uno.XComponentContext);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheet);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.javax.swing.JOptionPane);

importClass(Packages.com.sun.star.sheet.XCellRangeFormula);

importClass(Packages.java.lang.Thread);

try{
	xContext = XSCRIPTCONTEXT.getComponentContext();
	xMultiComponentFactory = xContext.getServiceManager();
	xtest = xMultiComponentFactory.createInstanceWithContext("com.sun.star.frame.Desktop", xContext);
	xcomponentloader = UnoRuntime.queryInterface(XComponentLoader,xtest);

	loadURL = "file:///C:/temp/test.ods";
	propertyvalue = new Array();
	xSorceDoc = xcomponentloader.loadComponentFromURL(loadURL, "_blank", 0, propertyvalue);

	xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, xSorceDoc);
	xSpreadsheets = xSpreadsheetDocument.getSheets();
	xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
	oSheet = xIndexAccess.getByIndex(0);
	oRng = UnoRuntime.queryInterface(XSpreadsheet, oSheet);
	oSourceRange = oRng.getCellRangeByName("A1:A5");
	xRngData = UnoRuntime.queryInterface(XCellRangeFormula, oSourceRange);
	oCopyData = xRngData.getFormulaArray()

	// Paste Doc
	oDoc = XSCRIPTCONTEXT.getDocument();
	xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
	xSpreadsheets = xSpreadsheetDocument.getSheets();
	xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
	oSheet = xIndexAccess.getByIndex(0);
	oRng = UnoRuntime.queryInterface(XSpreadsheet, oSheet);
	oPasteRange = oRng.getCellRangeByName("B1:B5");
	
	xPasteData = UnoRuntime.queryInterface(XCellRangeFormula, oPasteRange);
	xPasteData.setFormulaArray(oCopyData)

	Thread.sleep(3000); // 3sec
	xSorceDoc.dispose();

	JOptionPane.showMessageDialog(null, "Success");
}catch(e){
//Error Handling
errorname = e.name;
errormsg = e.message;
eDisp= errorname + "\n" + errormsg;
JOptionPane.showMessageDialog(null, eDisp);
}