Created
April 25, 2018 12:39
-
-
Save wegry/c0afd53de8bf0ccb395a43cca732a925 to your computer and use it in GitHub Desktop.
Extract XLSX into JSON
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(* 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