エクセルのシートに記入されたリストを分割するのを ExcelDNA & F# でやってみるやつ

HelloExcelSplit.fs

module HelloExcelSplit
    
open ExcelDna.Integration

let splitIndex (group: int) (total: int): seq<seq<int>> =
  let d = total / group
  let m = total % group
  let counts = seq { for i in 0 .. group - 1 do yield d + if i < m then 1 else 0 }
  let (_, xs) = Seq.fold (fun (a, xs) x -> (a + x, Seq.append xs (Seq.singleton (seq { a .. a + x - 1 })))) (0, Seq.empty) counts
  xs

let rowCount (source: string): int =
  let mutable i = 0
  while ExcelReference(i, i, 0, 0, source).GetValue().GetType() <> typeof<ExcelEmpty> do
    i <- i + 1
  i

let colCount (source: string) (row: int): int =
  let mutable i = 0
  while ExcelReference(row, row, i, i, source).GetValue().GetType() <> typeof<ExcelEmpty> do
    i <- i + 1
  i

let copyHeader (source: string) (target: string) (col: int): unit =
  for i in 0 .. col - 1 do
    let data = ExcelReference(0, 0, i, i, source).GetValue()
    ExcelReference(0, 0, i, i, target).SetValue(data) |> ignore

let copyRecord (source: string) (target: string) (col: int) (indexes: seq<int>): unit =
  for (index, i) in Seq.zip indexes (Seq.initInfinite id) do
    for j in 0 .. col - 1 do
      let data = ExcelReference(index + 1, index + 1, j, j, source).GetValue()
      ExcelReference(i + 1, i + 1, j, j, target).SetValue(data) |> ignore

[<ExcelCommand(MenuName="こーへーさんのやつ", MenuText="処理実行")>]
let exec() =
  let group = 3
  let sourceSheetName = "くだもの"
  try
    XlCall.Excel(XlCall.xlcWorkbookActivate, sourceSheetName) |> ignore
    let row = rowCount sourceSheetName
    let col = colCount sourceSheetName 0
    let indexes = splitIndex group (row - 1)
    for i in 1 .. group do
      try
        XlCall.Excel(XlCall.xlcWorkbookDelete, sprintf "くだもの%d" i) |> ignore
      with
        | :? XlCallException as ex -> ()
    let workbookName = XlCall.Excel(XlCall.xlfGetDocument, 88)
    for (idxs, i) in Seq.zip indexes (Seq.initInfinite id) do
      let sheetName = sprintf "くだもの%d" (i + 1)
      XlCall.Excel(XlCall.xlcWorkbookInsert) |> ignore
      let oldSheetName = (XlCall.Excel(XlCall.xlfGetWorkbook, 3) :?> obj[,]).[0, 0]
      XlCall.Excel(XlCall.xlcWorkbookName, oldSheetName, sheetName) |> ignore
      let sheetCount = XlCall.Excel(XlCall.xlfGetWorkbook, 4)  
      XlCall.Excel(XlCall.xlcWorkbookMove, sheetName, workbookName, sheetCount) |> ignore
      copyHeader sourceSheetName sheetName col
      copyRecord sourceSheetName sheetName col idxs
  with
    | :? XlCallException as ex -> ()

let testDataHeader = [| "名前"; "色"; "価格" |]
let testData = [|
  ("リンゴ", "赤", 350)
  ("バナナ", "黄", 240)
  ("キウイ", "緑", 180)
  ("桃", "白", 600)
  ("なし", "茶", 140)
  ("ぶどう", "緑", 480)
  ("いちじく", "赤", 320)
  ("スイカ", "緑", 1200)
  ("海ブドウ", "緑", 700)
  ("山ぶどう", "茶", 450)
|]

[<ExcelCommand(MenuName="こーへーさんのやつ", MenuText="テストデータを開く")>]
let createTestData() =
  XlCall.Excel(XlCall.xlcNew) |> ignore
  XlCall.Excel(XlCall.xlcWorkbookName, "Sheet1", "くだもの") |> ignore
  for i in 0 .. testDataHeader.Length - 1 do
    ExcelReference(0, i).SetValue(testDataHeader.[i]) |> ignore
  for i in 0 .. testData.Length - 1 do
    let (name, color, price) = testData.[i]
    ExcelReference(i + 1, 0).SetValue(name) |> ignore
    ExcelReference(i + 1, 1).SetValue(color) |> ignore
    ExcelReference(i + 1, 2).SetValue(price) |> ignore

おおお！期待大です！(^_^)/