Macroの杜(JavaScript編)

< 戻る 【 Calc 】

CCFEt-)[Calc]Cellの値を高速取得

com.sun.star.sheet.XCellRangeData / getDataArray
で値を配列に格納することでA1～C1048576　のデータを
高速で取得するコード。

Basicの同じコードよりも高速化が可能(約2～3倍高速)


//[ Cell ] Get the value from Cell
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheet);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.beans.XPropertySet);

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

importPackage(java.util.date);
importClass(Packages.javax.swing.JOptionPane);
try{
	oDateTime = new Date()
	oStime = oDateTime.getHours() *60*60 + oDateTime.getMinutes() *60 + oDateTime.getSeconds()
	loadProps = new Array(); // We need no properties
	xSheetComponent = XSCRIPTCONTEXT.getDocument();
	xDocument = UnoRuntime.queryInterface(XSpreadsheetDocument,xSheetComponent);
	xSheets = xDocument.getSheets();
	xIndexSheets =UnoRuntime.queryInterface(XIndexAccess, xSheets);
	xSheet = UnoRuntime.queryInterface(XSpreadsheet,xIndexSheets.getByIndex(0));
	// get A1:C1048575 Cell
	cellRange = xSheet.getCellRangeByPosition(0, 0, 2, 1048575);
	xDataArry = UnoRuntime.queryInterface(XCellRangeData,cellRange);
	oDataArry = xDataArry.getDataArray()
	oFirst = "";for(i=0; i<=2; i++){oFirst = oFirst + "  " + oDataArry[0,0,0][i]};
	oLast = "";for(i=0; i<=2; i++){oLast = oLast + "  " + oDataArry[1048575,1048575,1048575][i]};

	oDisp = "First Row : " + oFirst + "\n" + "Last Row : " + oLast

	oDateTime = new Date()
	oEtime = oDateTime.getHours() *60*60 + oDateTime.getMinutes() *60 + oDateTime.getSeconds()
	oTime = oEtime - oStime
	oDisp = oDisp + "\n\nExec Time = " + oTime + " [sec]\n(LO7.6.2.1)"
	JOptionPane.showMessageDialog(null, oDisp);
	//JOptionPane.showMessageDialog(null, oTime + "n" + oDisp + "\nSuccess\n(LO7.6.2.1)");
}catch(e){
//Error Handling
errorname = e.name;
errormsg = e.message;
eDisp= errorname + "\n" + errormsg;
JOptionPane.showMessageDialog(null, eDisp);
}