アクティブなブック内のすべてのグラフシートを表すSheetsコレクションを返します。
- 構文
- Sheets = Application.Charts
- 引数
- 戻り値
- グラフシートを表すSheetsコレクション
プログラム実行例
2軸のグラフを作成する
CONST xlLine = 4
CONST xlColumnClustered = 51
CONST xlMarkerStyleCircle = 8
CONST xlValue = 2
CONST xlSecondary = 2
DIM Excel = ExcelBoot("東京の気温と降水量(2019年).xlsx")
DIM SheetName = "Sheet1"
DIM Charts = Excel.Charts.Add
DIM Series
Charts.ChartArea.ClearContents
Series = Charts.SeriesCollection.NewSeries
WITH Series
WITH Charts.Axes(xlValue)
.HasTitle = TRUE
.AxisTitle.Caption = "気温[℃]"
ENDWITH
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleCircle
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("C5:C16")
.Name = Excel.Worksheets(SheetName).Range("C4")
.Format.Line.ForeColor.RGB = 255
.MarkerBackgroundColor = 255
ENDWITH
Series = Charts.SeriesCollection.NewSeries
WITH Series
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleCircle
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("D5:D16")
.Name = Excel.Worksheets(SheetName).Range("D4")
.Format.Line.ForeColor.RGB = 16711680
.MarkerBackgroundColor = 16711680
ENDWITH
Series = Charts.SeriesCollection.NewSeries
WITH Series
TEXTBLOCK
WITH Charts.Axes(xlValue)
.HasTitle = TRUE
.AxisTitle.Caption = "降水量[mm]"
ENDWITH
ENDTEXTBLOCK
.ChartType = xlColumnClustered
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("E5:E16")
.Name = Excel.Worksheets(SheetName).Range("E4")
.Format.Line.ForeColor.RGB = 10341600
.AxisGroup = xlSecondary
ENDWITH
//////////////////////////////////////////////////
// 【引数】
// path : 開くファイルのパス名
// 【戻値】
// Excelオブジェクト
//////////////////////////////////////////////////
FUNCTION ExcelBoot(path = "")
DIM Excel = CREATEOLEOBJ("Excel.Application")
Excel.Visible = TRUE
IFB path = "" THEN
Excel.Workbooks.Add
ELSE
DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
IFB FSO.GetParentFolderName(path) = "" THEN
path = GET_CUR_DIR + "\" + path
ENDIF
Excel.Workbooks.Open(path)
ENDIF
RESULT = Excel
FEND
//////////////////////////////////////////////////
// 【引数】
// シリアル値 : 時間を表すシリアル値を指定
// 【戻値】
//
//////////////////////////////////////////////////
FUNCTION Second(serial)
RESULT = REPLACE(FORMAT(INT(serial * 86400) MOD 60, 2), " ", "0")
FEND
気象庁のホームページから一月分の気温を取得し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 = 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
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
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
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
- IEBoot
- IE.Navigate
- BusyWait
- document.getElementById
- getTableData
- XLOPEN
- Excel.Application.ActiveSheet
- Excel.WorkSheet.Name
- XLSETDATA
- Excel.Application.Cells
- Excel.Range.End
- Excel.Range.Row
- Excel.Application.Charts
- Excel.Sheets.Add
- Excel.Charts
- Excel.Chart.ChartType
- Excel.Chart.SeriesCollection
- Excel.SeriesCollection.NewSeries
- Excel.Series
- Excel.Chart.HasTitle
- Excel.Chart.ChartTitle
- Excel.ChartTitle.Text
- Excel.Chart.FullSeriesCollection
- Excel.FullSeriesCollection
- Excel.FullSeriesCollection.Item
- Excel.Series.XValues
- Excel.Series.Name
- Excel.Series.Values
- Excel.Series.Format
- Excel.FillFormat.ForeColor
- Excel.ColorFormat.RGB
- Excel.LineFormat.FontColor
- 結果