XLSETDATA

構文
  1. Boolean = XLSETDATA( Excel, 値, [セル位置, Column, Sheet名, 文字色, セル色] )
引数
Excel
Excel(またはOOoのCalc)のCOMオブジェクト
値
設定する値
セル位置
A1形式、もしくはR1C1形式の行を指定　（省略された場合は現在の選択セル）
Column
R1C1形式時の列
Sheet名
指定シートへ設定（指定がなければアクティブシート）
文字色
BGR値にて指定
セル色
BGR値にて指定
戻値
TRUE
正常
FALSE
処理不能

配列を渡す時にRangeの大きさを指定する必要はありません（ [2,2]の配列を渡す時に"A1B2"ではなく、"A1"だけで可 ）

BGR値　例)　青：$FF0000、緑：$00FF00、赤：$0000FF、オレンジ：$80FF、黄：$FFFF

プログラム実行例

気象庁のホームページから一月分の気温を取得しExcelでグラフを作成

CONST xlUp = -4162
CONST xlLineMarkers = 65

DIM year = 2020
DIM month = 8

DIM IE = IEBoot()
IE.Navigate("https://www.data.jma.go.jp/obd/stats/etrn/view/daily_s1.php?prec_no=14&block_no=47412&year=" + year + "&month=" + month + "&day=&view=p1")
BusyWait(IE)

DIM array[-1][-1]
DIM element = IE.document.getElementById("tablefix1")
getTableData(element, array)

IE.Quit

DIM Excel = XLOPEN()
DIM SheetName = Excel.ActiveSheet.Name

XLSETDATA(Excel, array, "A1")

DIM row = Excel.Cells(Excel.Rows.Count, 1).End(xlUp).Row
DIM Charts = Excel.Charts.Add

WITH Charts
	.ChartType = xlLineMarkers
	.SeriesCollection.NewSeries
	.HasTitle = TRUE
	.ChartTitle.Text = "札幌 " + year + "年" + month + "月気温"

	WITH .FullSeriesCollection(1)
		.XValues = "=Sheet1!$A$5:$A$35"
		.Name = "=<#DBL>最高気温<#DBL>"
		.Values = "=Sheet1!$H$5:$H$35"
		WITH .Format
			.Fill.ForeColor.RGB = 255
			.Line.ForeColor.RGB = 255
		ENDWITH
	ENDWITH

	WITH .FullSeriesCollection(2)
		.XValues = "=Sheet1!$A$5:$A$35"
		.Name = "=<#DBL>最高気温<#DBL>"
		.Values = "=Sheet1!$I$5:$I$35"
		WITH .Format
			.Fill.ForeColor.RGB = 16711680
			.Line.ForeColor.RGB = 16711680
		ENDWITH
	ENDWITH
ENDWITH

//////////////////////////////////////////////////
// 【引数】
//   IE : IEオブジェクト 
// 【戻値】
// 
//////////////////////////////////////////////////
PROCEDURE BusyWait(Var IE)
	SLEEP(0.500)
	DIM t = GETTIME()
	TRY
		REPEAT
			DIM tm = GETTIME() - t
			FUKIDASI("BusyWait：" + tm)
			SLEEP(0.010)
			IF tm >= 60 THEN BREAK
		UNTIL !IE.Busy AND IE.readyState = 4
	EXCEPT
		IE = getIEObj(-1)
		PRINT IE.document.URL + " のIEオブジェクトを取得しました。"
		BusyWait(IE)
	ENDTRY
	FUKIDASI()
FEND

//////////////////////////////////////////////////
// 【引数】
//   文字列 or 数値 : 取得したいIEオブジェクトのタイトル・URLもしくは数値を指定 
//   完全一致フラグ : （TRUE : 文字列が完全一致したものを取得、FALSE : 文字列の一部を含むものを取得） 
// 【戻値】
//   Internet Explorerオブジェクト 
//////////////////////////////////////////////////
FUNCTION getIEObj(str, flg = FALSE)
	DIM Shell = CREATEOLEOBJ("Shell.Application")
	SELECT CHKNUM(str)
		CASE TRUE
			DIM cnt = 0
			SELECT TRUE
				CASE str > 0
					FOR n = 0 TO Shell.Windows.Count - 1
						TRY
							IFB Shell.Windows.Item(n).Name = "Internet Explorer" THEN
								cnt = cnt + 1
								IFB str = cnt THEN
									RESULT = Shell.Windows.Item(n)
									EXIT
								ENDIF
							ENDIF
						EXCEPT
						ENDTRY
					NEXT
				CASE str < 0
					FOR n = Shell.Windows.Count - 1 TO 0 STEP -1
						TRY
							IFB Shell.Windows.Item(n).Name = "Internet Explorer" THEN
								cnt = cnt + 1
								IFB ABS(str) = cnt THEN
									RESULT = Shell.Windows.Item(n)
									EXIT
								ENDIF
							ENDIF
						EXCEPT
						ENDTRY
					NEXT
				CASE str = 0
					FOR n = 0 TO Shell.Windows.Count - 1
						TRY
							IF Shell.Windows.Item(n).Name = "Internet Explorer" THEN cnt = cnt + 1
						EXCEPT
						ENDTRY
					NEXT
					RESULT = cnt
					EXIT
			SELEND
		CASE FALSE
			DIM t = GETTIME()
			REPEAT
				FOR n = 0 TO Shell.Windows.Count - 1
					TRY
						DIM targetObj = Shell.Windows.Item(n)
						IFB targetObj.Name = "Internet Explorer" THEN
							SELECT flg
								CASE TRUE
									IFB targetObj.document.title = str OR targetObj.LocationURL = str THEN
										RESULT = Shell.Windows.Item(n)
										EXIT
									ENDIF
								CASE FALSE
									IFB POS(str, targetObj.document.title) OR POS(str, targetObj.LocationURL) THEN
										RESULT = Shell.Windows.Item(n)
										EXIT
									ENDIF
							SELEND
						ENDIF
					EXCEPT
					ENDTRY
				NEXT
			UNTIL GETTIME() - t >= 5
	SELEND
	RESULT = ERR_VALUE
FEND

//////////////////////////////////////////////////
// 【引数】
//   table : tableエレメント 
//   arr : 取得したデータを格納する配列（参照引数） 
// 【戻値】
// 
//////////////////////////////////////////////////
PROCEDURE getTableData(table, Var arr[][])
	rowMax = table.rows.length - 1
	colMax = 0
	FOR row = 0 TO table.rows.length - 1
		IFB table.rows(row).cells.length - 1 > colMax THEN
			colMax = table.rows(row).cells.length - 1
		ENDIF
	NEXT
	DIM arr[rowMax][colMax]
	FOR row = 0 TO table.rows.length - 1
		FOR col = 0 TO table.rows(row).cells.length - 1
			n = 0
			WHILE arr[row][col + n] <> ""
				n = n + 1
			WEND
			arr[row][col + n] = table.rows(row).cells(col).innerText
			// rowSpan（行結合）とcolSpan（列結合）の両方があれば
			IFB table.rows(row).cells(col).rowSpan > 1 AND table.rows(row).cells(col).colSpan > 1 THEN
				rmax = table.rows(row).cells(col).rowSpan - 1
				cmax = table.rows(row).cells(col).colSpan - 1
				FOR r = 1 TO rmax
					FOR c = 1 TO cmax
						arr[row + r][col + c] = "←"
					NEXT
				NEXT
			ENDIF
			// rowSpan（行結合）があれば結合セルに「↑」を代入
			IFB table.rows(row).cells(col).rowSpan > 1 THEN
				n = table.rows(row).cells(col).rowSpan - 1
				WHILE n
					arr[row + n][col] = "↑"
					n = n - 1
				WEND
			ENDIF
			// colSpan（列結合）があれば結合セルに「←」を代入
			IFB table.rows(row).cells(col).colSpan > 1 THEN
				n = table.rows(row).cells(col).colSpan - 1
				WHILE n
					arr[row][col + n] = "←"
					n = n - 1
				WEND
			ENDIF
		NEXT
	NEXT
FEND

//////////////////////////////////////////////////
// 【引数】
//   InPrivate : InPrivateブラウズ・モードを有効にするときはTRUEを指定（デフォルトはFALSE） 
// 【戻値】
//   InternetExplorerオブジェクト 
//////////////////////////////////////////////////
FUNCTION IEBoot(InPrivate = FALSE)
	DIM IE
	SELECT InPrivate
		CASE TRUE
			DOSCMD("start iexplore -private")
			GETID("InPrivate - Internet Explorer - [InPrivate]", "IEFrame", -1)
			IE = GETACTIVEOLEOBJ("InternetExplorer.Application","InPrivate - Internet Explorer - [InPrivate]")
		CASE FALSE
			TRY
				IE = CREATEOLEOBJ("InternetExplorer.Application")
				IE.Visible = TRUE
			EXCEPT
				EXEC("C:\Program Files\Internet Explorer\iexplore.exe")
				GETID("Internet Explorer", "IEFrame", -1)
				TRY
					IE = GETACTIVEOLEOBJ("InternetExplorer.Application")	
				EXCEPT
					IE = getIEObj(-1)
				ENDTRY
			ENDTRY
	SELEND
	RESULT = IE
FEND
  1. IEBoot
  2. IE.Navigate
  3. BusyWait
  4. document.getElementById
  5. getTableData
  6. XLOPEN
  7. Excel.Application.ActiveSheet
  8. Excel.WorkSheet.Name
  9. XLSETDATA
  10. Excel.Application.Cells
  11. Excel.Range.End
  12. Excel.Range.Row
  13. Excel.Application.Charts
  14. Excel.Sheets.Add
  15. Excel.Charts
  16. Excel.Chart.ChartType
  17. Excel.Chart.SeriesCollection
  18. Excel.SeriesCollection.NewSeries
  19. Excel.Series
  20. Excel.Chart.HasTitle
  21. Excel.Chart.ChartTitle
  22. Excel.ChartTitle.Text
  23. Excel.Chart.FullSeriesCollection
  24. Excel.FullSeriesCollection
  25. Excel.FullSeriesCollection.Item
  26. Excel.Series.XValues
  27. Excel.Series.Name
  28. Excel.Series.Values
  29. Excel.Series.Format
  30. Excel.FillFormat.ForeColor
  31. Excel.ColorFormat.RGB
  32. Excel.LineFormat.FontColor
結果

Excelでセルに値を代入

DIM Excel = XLOPEN()

FOR row = 1 TO 5
	FOR col = 1 TO 5
		XLSETDATA(Excel, row + ", " + col, row, col)
	NEXT
NEXT

//XLCLOSE(Excel, TRUE)
  1. XLOPEN
  2. XLSETDATA
  3. XLCLOSE

セルに配列の値を代入

配列の場合、セル位置は必須。

DIM Excel = XLOPEN()
DIM arr[2][1] = 12, 45, 78, 2417, 51, 478

XLSETDATA(Excel, arr, "A1")

//XLCLOSE(Excel, TRUE)
  1. XLOPEN
  2. XLSETDATA
  3. XLCLOSE

指定したセルから値を取得します

DIM Excel = XLOPEN()
XLSETDATA(Excel, RANDOM(), "A1")
PRINT XLGETDATA(Excel, "A1")

//XLCLOSE(Excel, TRUE)
  1. XLOPEN
  2. XLSETDATA
  3. XLGETDATA
  4. XLCLOSE

楽天銀行定期預金の金利をExcelに保存

DIM IE = CREATEOLEOBJ("InternetExplorer.Application")
IE.Visible = TRUE
IE.Navigate("https://rbweb.rakuten-bank.co.jp/REF/main/fis/BasicTimeDepositInterestRate.html?PageID=BasicTimeDepositInterestRatePresentationLogicBean")
BusyWait(IE)

DIM elements = IE.document.getElementsByTagName("table")
DIM element = elements.Item(0)
DIM arr[-1][-1]

getTableData(element, arr)

IE.Quit

DIM Excel = XLOPEN()
XLSETDATA(Excel, arr, "A1")

DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
DIM path = uniqueFilename(FSO.BuildPath(GET_CUR_DIR, "楽天銀行定期預金金利.xlsx"))
XLCLOSE(Excel, path)

//////////////////////////////////////////////////
// 【引数】
//   IE : IEオブジェクト 
// 【戻値】
// 
//////////////////////////////////////////////////
PROCEDURE BusyWait(Var IE)
	SLEEP(0.500)
	DIM t = GETTIME()
	TRY
		REPEAT
			DIM tm = GETTIME() - t
			FUKIDASI("BusyWait：" + tm)
			SLEEP(0.010)
			IF tm >= 60 THEN BREAK
		UNTIL !IE.Busy AND IE.readyState = 4
	EXCEPT
		IE = getIEObj(-1)
		PRINT IE.document.URL + " のIEオブジェクトを取得しました。"
		BusyWait(IE)
	ENDTRY
	FUKIDASI()
FEND

//////////////////////////////////////////////////
// 【引数】
//   文字列 or 数値 : 取得したいIEオブジェクトのタイトル・URLもしくは数値を指定 
//   完全一致フラグ : （TRUE : 文字列が完全一致したものを取得、FALSE : 文字列の一部を含むものを取得） 
// 【戻値】
//   Internet Explorerオブジェクト 
//////////////////////////////////////////////////
FUNCTION getIEObj(str, flg = FALSE)
	DIM Shell = CREATEOLEOBJ("Shell.Application")
	SELECT CHKNUM(str)
		CASE TRUE
			DIM cnt = 0
			SELECT TRUE
				CASE str > 0
					FOR n = 0 TO Shell.Windows.Count - 1
						TRY
							IFB Shell.Windows.Item(n).Name = "Internet Explorer" THEN
								cnt = cnt + 1
								IFB str = cnt THEN
									RESULT = Shell.Windows.Item(n)
									EXIT
								ENDIF
							ENDIF
						EXCEPT
						ENDTRY
					NEXT
				CASE str < 0
					FOR n = Shell.Windows.Count - 1 TO 0 STEP -1
						TRY
							IFB Shell.Windows.Item(n).Name = "Internet Explorer" THEN
								cnt = cnt + 1
								IFB ABS(str) = cnt THEN
									RESULT = Shell.Windows.Item(n)
									EXIT
								ENDIF
							ENDIF
						EXCEPT
						ENDTRY
					NEXT
				CASE str = 0
					FOR n = 0 TO Shell.Windows.Count - 1
						TRY
							IF Shell.Windows.Item(n).Name = "Internet Explorer" THEN cnt = cnt + 1
						EXCEPT
						ENDTRY
					NEXT
					RESULT = cnt
					EXIT
			SELEND
		CASE FALSE
			DIM t = GETTIME()
			REPEAT
				FOR n = 0 TO Shell.Windows.Count - 1
					TRY
						DIM targetObj = Shell.Windows.Item(n)
						IFB targetObj.Name = "Internet Explorer" THEN
							SELECT flg
								CASE TRUE
									IFB targetObj.document.title = str OR targetObj.LocationURL = str THEN
										RESULT = Shell.Windows.Item(n)
										EXIT
									ENDIF
								CASE FALSE
									IFB POS(str, targetObj.document.title) OR POS(str, targetObj.LocationURL) THEN
										RESULT = Shell.Windows.Item(n)
										EXIT
									ENDIF
							SELEND
						ENDIF
					EXCEPT
					ENDTRY
				NEXT
			UNTIL GETTIME() - t >= 5
	SELEND
	RESULT = ERR_VALUE
FEND

//////////////////////////////////////////////////
// 【引数】
//   table : tableエレメント 
//   arr : 取得したデータを格納する配列（参照引数） 
// 【戻値】
// 
//////////////////////////////////////////////////
PROCEDURE getTableData(table, Var arr[][])
	rowMax = table.rows.length - 1
	colMax = 0
	FOR row = 0 TO table.rows.length - 1
		IFB table.rows(row).cells.length - 1 > colMax THEN
			colMax = table.rows(row).cells.length - 1
		ENDIF
	NEXT
	DIM arr[rowMax][colMax]
	FOR row = 0 TO table.rows.length - 1
		FOR col = 0 TO table.rows(row).cells.length - 1
			n = 0
			WHILE arr[row][col + n] <> ""
				n = n + 1
			WEND
			arr[row][col + n] = table.rows(row).cells(col).innerText
			// rowSpan（行結合）とcolSpan（列結合）の両方があれば
			IFB table.rows(row).cells(col).rowSpan > 1 AND table.rows(row).cells(col).colSpan > 1 THEN
				rmax = table.rows(row).cells(col).rowSpan - 1
				cmax = table.rows(row).cells(col).colSpan - 1
				FOR r = 1 TO rmax
					FOR c = 1 TO cmax
						arr[row + r][col + c] = "←"
					NEXT
				NEXT
			ENDIF
			// rowSpan（行結合）があれば結合セルに「↑」を代入
			IFB table.rows(row).cells(col).rowSpan > 1 THEN
				n = table.rows(row).cells(col).rowSpan - 1
				WHILE n
					arr[row + n][col] = "↑"
					n = n - 1
				WEND
			ENDIF
			// colSpan（列結合）があれば結合セルに「←」を代入
			IFB table.rows(row).cells(col).colSpan > 1 THEN
				n = table.rows(row).cells(col).colSpan - 1
				WHILE n
					arr[row][col + n] = "←"
					n = n - 1
				WEND
			ENDIF
		NEXT
	NEXT
FEND

//////////////////////////////////////////////////
// 【引数】
//   path : ファイルのパス 
// 【戻値】
//   重複しないファイル名 
//////////////////////////////////////////////////
FUNCTION uniqueFilename(path)
	DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
	IFB FSO.FileExists(path) THEN
		DIM fol = FSO.GetParentFolderName(path)
		DIM filename = FSO.GetBaseName(path)
		DIM extension = FSO.GetExtensionName(path)
		DIM i = 2
		WHILE FSO.FileExists(FSO.BuildPath(fol, filename + " (" + i + ")." + extension))
			i = i + 1
		WEND
		RESULT = FSO.BuildPath(fol, filename + " (" + i + ")." + extension)
	ELSE
		RESULT = path
	ENDIF
FEND
  1. CREATEOLEOBJ
  2. IE.Visible
  3. IE.Navigate
  4. BusyWait
  5. IE.Document
  6. IE.document.getElementsByTagName
  7. getTableData
  8. XLOPEN
  9. XLSETDATA
  10. uniqueFilename
  11. FSO.BuildPath
  12. XLCLOSE