Parsing Excel Data in Apple Swift

Question

My current workflow involves using Applescript to essentially delimit Excel data and format it into plain text files. We're pushing towards an all Swift environment, but I haven't yet found any sort of kits for parsing my Excel data into Swift.

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

The goal is to eliminate Applescript, but I'm not sure if that will be possible while still interacting with Excel files. Scripting Excel via Applescript seems to be the only method.

EDIT: I don't have the option of eliminating Excel from this workflow. This is how the data will be coming to the application, thus I have to include it.

Being able to streamline the process of parsing this data then processing it will be paramount. I know Applescript has been good in the past with helping me to process it; however, it's getting a little too closed-off for me.

I've been looking at writing something in Swift/Cocoa, but that still might require the data to be extracted with an Applescript, right?

A big plus for pushing Swift is the readability. I don't know Objective-C all that well, and swift would be an easier transition, I feel.

My workflow on PC has been using the COM object, which as has been said, isn't available in the Mac Excel app. I'm only looking for data extraction at this point. Some previous apps did processing within the app, but I'm looking to make this very self-contained, thus all processing within the app I'm developing. Once the data is extracted from the .XLS or .XLSX files, I'll be doing some text editing via RegEx and perhaps a little number crunching. Nothing too crazy. As of now, it will run on the client side, but I'm looking to extend this to a server process.

Answer 1

In Mac OS X 10.6 Snow Leopard Apple introduced the AppleScriptObjC framework which makes it very easy to interact between Cocoa and AppleScript. AppleScript code and a Objective-C like syntax can be used in the same source file. It's much more convenient than Scripting Bridge and NSAppleScript.

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

However you can use a 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 a AppleScript source file form 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

Link to the AppleScriptObjC framework if necessary.
Create the Bridging 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]>