Skip to content

Instantly share code, notes, and snippets.

@wegry
Created April 25, 2018 12:39
Show Gist options
  • Save wegry/c0afd53de8bf0ccb395a43cca732a925 to your computer and use it in GitHub Desktop.
Save wegry/c0afd53de8bf0ccb395a43cca732a925 to your computer and use it in GitHub Desktop.
Extract XLSX into JSON
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp2.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<Compile Include="Program.fs" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="EPPlus" Version="4.5.1" />
<PackageReference Include="Newtonsoft.Json" Version="11.0.2" />
</ItemGroup>
</Project>
(* Built with .NET Core 2.1.3
To build as a binary for a specific platform, MacOS in this case.
run `$ dotnet publish --runtime osx-x64 -c Release`
*)
open System
open System.IO
open System.Text.RegularExpressions
open System.Collections.Generic
open OfficeOpenXml
open Newtonsoft.Json
let scrapePrefix worksheetName =
let numericSheet = Regex("^(\d+\.\d+)")
match worksheetName with
"general" -> "general_"
| x ->
(numericSheet.Match(x).Groups.[0].Value)
|> sprintf "%s_"
let readWorksheet (worksheet: ExcelWorksheet) =
let worksheetName = worksheet.Name
let prefix = scrapePrefix worksheetName
Seq.initInfinite(fun n-> n + 1)
|> Seq.skip 1
|> Seq.map (fun i ->
let key = sprintf "%s%s" prefix (worksheet.Cells.[i, 1].Value :?> string)
let value = worksheet.Cells.[i, 2].Value :?> string
(key, value))
|> Seq.takeWhile (fun x -> (obj.ReferenceEquals(snd(x), null)) |> not)
|> Seq.map(fun (key, value) -> key, (value.Trim()))
[<EntryPoint>]
let main argv =
if (Array.length argv) <> 1 then (
eprintfn "Make sure to include the path to the xlsx file you want\n\nExecutableName [path here]"
Environment.Exit(1)
)
let inputFilePath = argv.[0]
use package =
new ExcelPackage(
inputFilePath
|> FileInfo
)
let result =
seq {
for i=0 to (package.Workbook.Worksheets.Count) - 1 do
let worksheet = package.Workbook.Worksheets.[i]
if (worksheet.Name = "template")
then yield Seq.empty
else yield readWorksheet worksheet
}
|> Seq.concat
|> Map.ofSeq
JsonConvert.SerializeObject(result, Formatting.Indented) |> printfn "%s"
0 // return an integer exit code
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment