Reading Data from Excel Document in a Swift App

How to use excel file data in an ios app?

I strongly recommend against using Excel files for this purpose.
There are several reasons for that:

1. Excel files are not designed to be sources of data for automated processing. These are electronic spreadsheets designed to be used by humans. Formats like XML, CSV and JSON work much, much better as data sources for programs.
2. Excel file format is incredibly complex and requires that you ship a specialized library inside your app. This severely increases the binary size of your app and the download time, makes it start up slower and use more RAM.
3. Decoding an Excel document is a CPU heavy task, i.e. your app will drain the battery much more than it should.
4. Excel file stores much more information in addition to your raw data that is absolutely not needed for the data consumer such as a mobile app.
5. It's very easy to accidentally break the data contract with the Excel file. Imagine that the app expects some value in the cell A1. If I insert a new column in the very beginning and make it hidden, the file will still look the same at first glance, but the consumer will not read it correctly.

If your data is fundamentally a hierarchy (a tree) of various objects with different sets attributes, use JSON or XML.

If your data is rather a set of similar objects (a table), use CSV.

Whether you choose JSON, XML or CSV, parsing them will be a much easier task for a mobile app.

If you need a convenient GUI to manually update a file like that, consider using Visual Studio Code with plugins. Or, in case of CSV, you can actually use Excel (just save the final result back to .csv rather than to .xlsx).

Parsing Excel Data in Apple Swift

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]>

Swift - How to open excel app with resource excel file within the project? (Can't open file Error)

Sorry everyone for the year long late reply. Here's my entire function as of today.

func createExcel(amortizationArray: [amortizationObject], loanName: String) -> Date
    {
    // Post notification that createExcel has started
    NotificationCenter.default.post(name: .isStart, object: nil)

    // Set the path to the path of wherever you put your Excel file.
    let path: String = Bundle.main.path(forResource: "AmortizationTable", ofType: "xlsx")!

    // Open the spreadsheet, get the first sheet, first worksheet, and first cell A1.
    let spreadsheet: BRAOfficeDocumentPackage = BRAOfficeDocumentPackage.open(path)
    let worksheetToCopy: BRAWorksheet = spreadsheet.workbook.worksheets[0] as! BRAWorksheet
    let worksheetName = "Amortization Table"
    let worksheet: BRAWorksheet = spreadsheet.workbook.createWorksheetNamed(worksheetName, byCopying: worksheetToCopy)

    var cell: BRACell
    for i in 0..<amortizationArray.count
    {
        let amortObj = amortizationArray[i]

        var color: UIColor
        if  amortObj.shade
        {
            color = UIColor(rgb: 0xf9f9f9)
        }
        else
        {
            color = UIColor(rgb: 0xf2f2f2)
        }
        cell = worksheet.cell(forCellReference: ("A" + String(describing: (i+2))), shouldCreate: true)

        cell.setCellFillWithForegroundColor(color, backgroundColor: .white, andPatternType: kBRACellFillPatternTypeDarkTrellis)

        let period = amortObj.amortiaztionValuesArray[0]
        let dateFormatter = DateFormatter()
        dateFormatter.dateStyle = .short
        dateFormatter.locale = Locale.current

        if let date = Calendar.current.date(byAdding: .month, value: Int(period), to: Date()) {
            let value = dateFormatter.string(from: date)
            cell.setStringValue(value)
        }
        let cellColumns = ["B", "C", "D","E","F","G"]

        for j in 0..<cellColumns.count
        {
            let column = cellColumns[j]
            cell = worksheet.cell(forCellReference: (column + String(describing: (i+2))), shouldCreate: true)
            let value = currencyInput(index: j+1, amortObj: amortObj)
            cell.setStringValue(value)
            cell.setCellFillWithForegroundColor(color, backgroundColor: .white, andPatternType: kBRACellFillPatternTypeDarkTrellis)
        }
    }

    var paths: Array = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true) as Array

    let fullPath: String = paths[0] + "/" + loanName + ".xlsx"
    spreadsheet.workbook.removeWorksheetNamed("Sheet1")
    spreadsheet.save(as: fullPath)

    let date = Date()
    UserDefaults.standard.set(fullPath, forKey: String(describing: date))

    return date
}

The most important piece of information I believe is this snippet of code here.

// Set the path to the path of wherever you put your Excel file.
    let path: String = Bundle.main.path(forResource: "AmortizationTable", ofType: "xlsx")!

    // Open the spreadsheet, get the first sheet, first worksheet, and first cell A1.
    let spreadsheet: BRAOfficeDocumentPackage = BRAOfficeDocumentPackage.open(path)
    let worksheetToCopy: BRAWorksheet = spreadsheet.workbook.worksheets[0] as! BRAWorksheet
    let worksheetName = "Amortization Table"
    let worksheet: BRAWorksheet = spreadsheet.workbook.createWorksheetNamed(worksheetName, byCopying: worksheetToCopy)

Be sure to delete the empty worksheet once you save

var paths: Array = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true) as Array

let fullPath: String = paths[0] + "/" + loanName + ".xlsx"
spreadsheet.workbook.removeWorksheetNamed("Sheet1")
spreadsheet.save(as: fullPath)

Let me know if this helped anyone. I'll answer any questions

Read data from Excel file in Objective-C (iPhone)

Actually, there is such a project - its called libxls on source forge. There is a ObjectiveC class interface to the project as well. With this project you can open an excel file, and read most of the information in it (cell values as well as the file's properties).

In addition there is an iOS Objective C interface to the library.

How to read cell data from an Excel document with objective-c

There is no innate ability to read Excel data into a Foundation container, like an NSArray or NSDictionary. You could, however, convert the file (with Excel) to a comma-separated-value (CSV) file and then parse each line's cells on the iPhone using the NSString instance method -componentsSeparatedByString:.

---

Related Topics