Analyze Excel data in Apple Swift

Question

My current workflow involves using Applescript to significantly differentiate Excel data and format it into text files. We aim for the whole Swift environment, but I have not yet found any sets to parse my Excel data in Swift.

The only thing I can think of is to use C or something else and wrap it, but that is not ideal. Any best suggestions on parsing this data for use in Swift?

The goal is to exclude Applescript, but I'm not sure if this will be possible when interacting with Excel files. Scripting Excel through Applescript seems to be the only method.

EDIT: I have no way to exclude Excel from this workflow. This is how the data will flow into the application, so I have to enable it.

The ability to streamline the process of analyzing this data, then processing will be of utmost importance. I know that Applescript has been good in the past, helping me handle it; nonetheless, it is becoming too closed to me.

I was looking at writing something in Swift / Cocoa, but it might still be necessary for the data to be retrieved using Applescript, right?

A big plus for pressing Swift is readability. I don’t know Objective-C is all that good, and fast, it would be easier to switch, I feel.

My workflow on a PC uses a COM object, which has been said to be unavailable in Mac Excel. At the moment I am only looking for data. Some previous applications were handled in the application, but I am looking to make this very self-sufficient, so all the processing is in the application that I am developing. As soon as the data is extracted from the .XLS or .XLSX files, I will do the text editing through RegEx and maybe a little crunch. Nothing crazy. At the moment, it will work on the client side, but I want to expand it to the server process.

Answer 1

In Mac OS X 10.6 Snow Leopard, Apple introduced the AppleScriptObjC framework, which simplifies the interaction between Cocoa and AppleScript. AppleScript code and Objective-C syntax can be used in the same source file. This is much more convenient than Scripting Bridge and NSAppleScript .

AppleScriptObjC cannot be used directly in Swift because the loadAppleScriptObjectiveCScripts NSBundle command is not bound to Swift.

However, you can use the Objective-C bridge class, for example

ASObjC.h

 @import Foundation; @import AppleScriptObjC; @interface NSObject (Excel) - (void)openExcelDocument:(NSString *)filePath; - (NSArray *)valueOfUsedRange; @end @interface ASObjC : NSObject + (ASObjC *)sharedASObjC; @property id Excel; @end 

ASObjC.m

 #import "ASObjC.h" @implementation ASObjC + (void)initialize { if (self == [ASObjC class]) { [[NSBundle mainBundle] loadAppleScriptObjectiveCScripts]; } } + (ASObjC *)sharedASObjC { static id sharedInstance = nil; static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ sharedInstance = [[ASObjC alloc] init]; }); return sharedInstance; } - (instancetype)init { self = [super init]; if (self) { _Excel = NSClassFromString(@"ASExcel"); } return self; } @end 

Create the AppleScript source file from the AppleScriptObjC template

ASExcel.applescript

 script ASExcel property parent: class "NSObject" on openExcelDocument:filePath set asFilePath to filePath as text tell application "Microsoft Excel" set sourceBook to open workbook workbook file name asFilePath repeat try get workbooks return end try delay 0.5 end repeat end tell end openDocument on valueOfUsedRange() tell application "Microsoft Excel" tell active sheet set activeRange to used range return value of activeRange end tell end tell end valueOfUsedRange end script 

Bind AppleScriptObjC to the structure if necessary.
Create a bridge header and import ASObjC.h

Then you can call AppleScriptObjC from Swift with

  ASObjC.sharedASObjC().Excel.openExcelDocument("Macintosh HD:Users:MyUser:Path:To:ExcelFile.xlsx") 

or

 let excelData = ASObjC.sharedASObjC().Excel.valueOfUsedRange() as! Array<[String]> 

Answer 2

It is somewhat unclear whether you are trying to exclude Excel as a dependency (which is not unreasonable: it costs money, not for everyone) or AppleScript as a language (a well-understood, but bad practical step, because Apple's alternatives to automate applications suck everything).

There are third-party Excel parsing libraries available for other languages, for example. I have successfully used openpyxl (for .xlsx) and xlrd (for .xsl) Python files in my own projects. And I see through the magic of Google that someone wrote the framework of ObjC, DHlibxls , which [in the absence of dynamic fraudulent operations] should be used directly from Fast, but I did not use it myself, so I can no longer tell you.

Answer 3

You can use ScriptingBridge or NSAppleScript to interact with Apple Scriptable content.

ScriptingBridge can generate a header file from an Apple Script dictionary.

NSAppleScript can execute any AppleScript for you by passing String

Answer 4

1. Export to plain text CSV

If all you are trying to do is extract data from Excel to use it elsewhere, unlike capturing formulas and formatting Excel, you probably should not try to read the .xls file. XLS is a complex format. This is good for Excel, not for general data sharing.

Likewise, you probably don't need to use AppleScript or anything else to integrate with Excel, if all you want to do is save the data in clear text. Excel already knows how to save data in clear text. Just use the Excel Save As command. (This is what he called on the Mac. I don't know about the PC.)

The question is which plaintext format to use. One obvious choice for this is the plain text comma-delimited (CSV) file because it is a simple de facto standard (as opposed to a complex official standard such as XML). This will facilitate consumption in Swift or in any other language.

2. Export in UTF-8 encoding, if possible, other than UTF-16

So how do you do this? Plaintext is remarkably simple, but one subtlety that you need to keep track of is the text encoding. Text encoding is a way of representing characters in a plaintext file. Unfortunately, you cannot reliably specify the encoding of the file, just by looking at the file, so you need to select the encoding when saving it and do not forget to use this encoding when reading it. If you ruin this, accented characters, typographic quotes, dashes, and other non-ASCII characters will be distorted. So what text encoding should you use? Short answer: you should always use UTF-8, if possible .

But if you are working with an old version of Excel, you will not be able to use UTF-8. In this case, you should use UTF-16. In particular, UTF-16, I believe, is the only export option in Excel 2011 for Mac that gives a predicted result that will not depend on unexpected methods from unclear locale settings or Microsoft-specific encodings.

So, if you are using Excel 2011 for Mac, for example, select "Unicode Text UTF-16" from the Excel Save As command.

This will force Excel to save the file so that each line is a line of text and each column is separated by a tab character. (So ​​technically these are tab-delimited value files, not a comma-delimited value file.)

3. Import with Swift

Now you have a plaintext file, which, as you know, was saved in UTF-8 (or UTF-16) encoding. So now you can read it and analyze it in Swift.

If your Excel data is complex, you may need a full-featured CSV parser. the best option is probably CHCSVParser .

Using CHCSV, you can parse the file with the following code:

 NSURL * const inputFileURL = [NSURL fileURLWithPath:@"/path/to/exported/file.txt"]; unichar tabCharacter = '\t'; NSArray *rows = [NSArray arrayWithContentsOfCSVFile:inputFilePath options:CHCSVParserOptionsSanitizesFields delimiter:tabCharacter]; 

(Of course, you can also call it from Swift.)

On the other hand, if you data is relatively simple (for example, it does not have escaped characters), you may not need to use an external library at all. You can write Swift code that parses tab-delimited values ​​only by reading in a file as a line, splitting a newline into lines, and then splitting into tabs .

This function will take a String representing TSV data and return an array of dictionaries:

 /** Reads a multiline, tab-separated String and returns an Array<NSictionary>, taking column names from the first line or an explicit parameter */ func JSONObjectFromTSV(tsvInputString:String, columnNames optionalColumnNames:[String]? = nil) -> Array<NSDictionary> { let lines = tsvInputString.componentsSeparatedByString("\n") guard lines.isEmpty == false else { return [] } let columnNames = optionalColumnNames ?? lines[0].componentsSeparatedByString("\t") var lineIndex = (optionalColumnNames != nil) ? 0 : 1 let columnCount = columnNames.count var result = Array<NSDictionary>() for line in lines[lineIndex ..< lines.count] { let fieldValues = line.componentsSeparatedByString("\t") if fieldValues.count != columnCount { // NSLog("WARNING: header has %u columns but line %u has %u columns. Ignoring this line", columnCount, lineIndex,fieldValues.count) } else { result.append(NSDictionary(objects: fieldValues, forKeys: columnNames)) } lineIndex = lineIndex + 1 } return result } 

Therefore you only need to read the file in a line and pass it to this function. This snippet is derived from this value for the tsv-to-json converter . And if you need to know more about which text encodings are produced by Microsoft products and which Cocoa can be automatically detected, then this text encoding repo contains research on export samples, which led to the conclusion that UTF-16 is the way for old products Microsoft on Mac.

(I understand that I'm getting attached to my own repositories here. Excuse me?)