Skip to content

XlsxReaderWriter is an Objective-C library for iPhone / iPad and Mac. It parses and writes MS Excel XLSX files.

License

Notifications You must be signed in to change notification settings

renebigot/XlsxReaderWriter

Repository files navigation

#XlsxReaderWriter

XlsxReaderWriter is an Objective-C library for iPhone / iPad and Mac. It parses and writes Excel OpenXml files (XLSX).

##Features

XlsxReaderWriter is able to:

  • Read a spreadsheet document (XLSX file)
  • Save a spreadsheet document
  • Create worksheet
  • Copy worksheet
  • Remove worksheet
  • Read cells content (Formula, error, string, attributed string, formatted number, boolean, date)
  • Write cells content (Formula, error, string, attributed string, formatted number, boolean, date)
  • Get images
  • Add images (JPEG or PNG)
  • Add/remove rows in sheets
  • Add/remove columns in sheets
  • Change number formatting
  • Read content from merge cells
  • Get cell fill as a UIColor
  • Change cell fill
  • ... many other things

Todo:

  • Add/remove columns in sheets
  • Create spreadsheet document from scratch
  • Improve number formatting
  • Borders
  • Add better support for comments (add, remove, read)

##Limitation

XlsxReaderWriter can't create a SpreadsheetML (XLSX) file from scratch. You have to open an existing file and modify it before saving it. Not really a problem: Create your file with Excel or Numbers with all the needed formatting (fills, borders, etc.) then include the file as a resource of your project.

##Third parties

Third parties are included in this repository, not linked as git submodules.

  • SSZipArchive: Compression/decompression library
  • XMLDictionary: Converts XML to NSDictionary and NSDictionary to XML

##Linking (Objective-C)

To include the library to your Xcode project:

  • Create a new project or open an existing project
  • Insert XlsxReaderWriter.xcodeproj as a sub project of your project
  • In your target Build phases insert XlsxReaderWriter as a target dependency
  • Add libXlsxReaderWriter.a and libz.tbd in Link binary with Libraries. Older systems can use libz.dylib instead of libz.tbd.
  • Add -all_load in Linking / Other Linker Flags in your project settings
  • Add the XlsxReaderWriter root directory path to User Header Search Paths and set it as recursive. For example, set the path to "$(SRCROOT)/XlsxReaderWriter/", not "$(SRCROOT)/XlsxReaderWriter/XlsxReaderWriter/".

Now, you can import BRAOfficeDocumentPackage.h in your code.

##Linking (Swift bridging)

If you want to use this library from some Swift code, be sure to follow the same steps as in the Objective-C linking, then:

  • you should #import "XlsxReaderWriter-swift-bridge.h" in your bridge header file
  • if you don't have any bridge header file, create a new .h file, and #import "XlsxReaderWriter-swift-bridge.h"
  • Set the path to your bridge file in your project settings : Swift Compiler - Code Generation / Objective-C Bridging Header.

More info about this could be find here

##How to ####Read a spreadsheet document (XLSX file) #####Objective-C

NSString *documentPath = [[NSBundle mainBundle] pathForResource:@"testWorkbook" ofType:@"xlsx"];
BRAOfficeDocumentPackage *spreadsheet = [BRAOfficeDocumentPackage open:documentPath];

#####Swift

var documentPath: String = NSBundle.mainBundle().pathForResource("testWorkbook", ofType: "xlsx")
var spreadsheet: BRAOfficeDocumentPackage = BRAOfficeDocumentPackage.open(documentPath)

####Save a spreadsheet document #####Objective-C

//Save
[spreadsheet save];
	
//Save a copy
NSString *fullPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"workbookCopy.xlsx"];
[spreadsheet saveAs:fullPath];

#####Swift

//Save
spreadsheet.save()

//Save a copy
var fullPath: String = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, true).lastObject().stringByAppendingPathComponent("workbookCopy.xlsx")
spreadsheet.saveAs(fullPath)

####Get a worksheet #####Objective-C

//First worksheet in the workbook
BRAWorksheet *firstWorksheet = spreadsheet.workbook.worksheets[0];
	
//Worksheet named "Foo"
BRAWorksheet *fooWorksheet = [spreadsheet.workbook createWorksheetNamed:@"Foo"];

#####Swift

//First worksheet in the workbook
var firstWorksheet: BRAWorksheet = spreadsheet.workbook.worksheets[0]
	
//Worksheet named "Foo"
var fooWorksheet: BRAWorksheet = spreadsheet.workbook.createWorksheetNamed("Foo")

####Read cells content: Formula #####Objective-C

NSString *formula = [[worksheet cellForCellReference:@"B4"] formulaString]

#####Swift

var formula: String = worksheet.cellForCellReference("B4").formulaString()

####Read cells content: error #####Objective-C

NSString *errorValue = nil;
if ([[worksheet cellForCellReference:@"B2"] hasError]) {
	errorValue = [[worksheet cellForCellReference:@"B2"] stringValue];
}

#####Swift

var errorValue: String? = nil
if worksheet.cellForCellReference("B2").hasError() {
	errorValue = worksheet.cellForCellReference("B2").stringValue()
}

####Read cells content: string #####Objective-C

NSString *string = [[worksheet cellForCellReference:@"B6"] stringValue];

#####Swift

var string: String = worksheet.cellForCellReference("B6").stringValue()

####Read cells content: attributed string #####Objective-C

//Cell style is applied to the cell content
NSAttributedString *attributedString = [[worksheet cellForCellReference:@"B5"] attributedStringValue];

#####Swift

//Cell style is applied to the cell content
var attributedString: NSAttributedString = worksheet.cellForCellReference("B5").attributedStringValue()

####Read cells content: formatted number #####Objective-C

//Integer cell value
NSInteger cellIntValue = [[worksheet cellForCellReference:@"B5"] integerValue];

//Float cell value
CGFloat cellFloatValue = [[worksheet cellForCellReference:@"B5"] floatValue];

//Formatted number cell value
CGFloat cellFloatValue = [[worksheet cellForCellReference:@"B5"] stringValue];

#####Swift

//Integer cell value
var cellIntValue: Int = CInteger(worksheet.cellForCellReference("B5"))!
	
//Float cell value
var cellFloatValue: CGFloat = CFloat(worksheet.cellForCellReference("B5"))!
	
//Formatted number cell value
var cellFloatValue: CGFloat = worksheet.cellForCellReference("B5").stringValue()

####Read cells content: boolean #####Objective-C

BOOL cellTruth = [[worksheet cellForCellReference:@"B5"] boolValue];

#####Swift

var cellTruth: Bool = CBool(worksheet.cellForCellReference("B5"))!

####Write cells content: Formula #####Objective-C

[[worksheet cellForCellReference:@"Y26" shouldCreate:YES] setFormulaString:@"TODAY()"];

#####Swift

worksheet.cellForCellReference("Y26", shouldCreate: true).formulaString = "TODAY()"

####Write cells content: error #####Objective-C

[[worksheet cellForCellReference:@"Y27" shouldCreate:YES] setError:@"#DIV/0!"];

#####Swift

worksheet.cellForCellReference("Y27", shouldCreate: true).error = "#DIV/0!"

####Write cells content: string #####Objective-C

[[worksheet cellForCellReference:@"Y24" shouldCreate:YES] setStringValue:@"FOO / BAR"];

#####Swift

worksheet.cellForCellReference("Y24", shouldCreate: true).stringValue = "FOO / BAR"

####Write cells content: attributed string #####Objective-C

[[worksheet cellForCellReference:@"Z24" shouldCreate:YES]
setAttributedStringValue:[[NSAttributedString alloc] initWithString:@"RED is not GREEN" attributes:@{NSForegroundColorAttributeName: [UIColor greenColor]}]];

#####Swift

worksheet.cellForCellReference("Z24", shouldCreate: true).attributedStringValue = NSAttributedString(string: "RED is not GREEN", attributes: [NSForegroundColorAttributeName: UIColor.greenColor()])

####Write cells content: formatted number #####Objective-C

[[worksheet cellForCellReference:@"Z23" shouldCreate:YES] setFloatValue:12.3];
[[worksheet cellForCellReference:@"Z23"] setNumberFormat:@"0.000"];

#####Swift

worksheet.cellForCellReference("Z23", shouldCreate: true).floatValue = 12.3
worksheet.cellForCellReference("Z23").numberFormat = "0.000"

####Write cells content: boolean #####Objective-C

[[worksheet cellForCellReference:@"Z21" shouldCreate:YES] setBoolValue:NO];

#####Swift

worksheet.cellForCellReference("Z21", shouldCreate: true).boolValue = false

####Write cells content: date #####Objective-C

NSDateFormatter *df = [[NSDateFormatter alloc] init];
df.dateFormat = @"MM/dd/yyyy";
[[worksheet cellForCellReference:@"Y25" shouldCreate:YES] setDateValue:[df dateFromString:@"10/07/1982"]];
[[worksheet cellForCellReference:@"Y25"] setNumberFormat:@"m/d/yyyy"];

#####Swift

var df: NSDateFormatter = NSDateFormatter()
df.dateFormat = "MM/dd/yyyy"
worksheet.cellForCellReference("Y25", shouldCreate: true).dateValue = df.dateFromString("10/07/1982")
worksheet.cellForCellReference("Y25").numberFormat = "m/d/yyyy"

####Get cell fill as a UIColor #####Objective-C

UIColor *cellFillColor = [[worksheet cellForCellReference:@"A35"] cellFillColor];

#####Swift

var cellFillColor: UIColor = worksheet.cellForCellReference("A35").cellFillColor()

####Change cell fill #####Objective-C

[[worksheet cellForCellReference:@"A36" shouldCreate:YES] setCellFillWithForegroundColor:[UIColor yellowColor] backgroundColor:[UIColor blackColor] andPatternType:kBRACellFillPatternTypeDarkTrellis];

#####Swift

worksheet.cellForCellReference("A36", shouldCreate: true).setCellFillWithForegroundColor(UIColor.yellowColor(), backgroundColor: UIColor.blackColor(), andPatternType: kBRACellFillPatternTypeDarkTrellis)

####Get images #####Objective-C

//Works with oneCellAnchor or twoCellAnchored image
UIImage *image = [worksheet imageForCellReference:@"G8"].uiImage;

#####Swift

//Works with oneCellAnchor or twoCellAnchored image
var image: UIImage = worksheet.imageForCellReference("G8").uiImage

####Add images (JPEG or PNG) #####Objective-C

UIImage *image = [UIImage imageNamed:@"Kitten.jpeg"];
//preserveTransparency force JPEG (NO) or PNG (YES)
BRAWorksheetDrawing *drawing = [worksheet addImage:image betweenCellsReferenced:@"G2" and:@"I10"withInsets:UIEdgeInsetsZero preserveTransparency:NO];
//Set drawing insets (percentage)
drawing.insets = UIEdgeInsetsMake(0., 0., .5, .5);

#####Swift

var image: UIImage = UIImage(named: "Kitten.jpeg")
//preserveTransparency force JPEG (NO) or PNG (YES)
var drawing: BRAWorksheetDrawing = worksheet.addImage(image, betweenCellsReferenced: "G2", and: "I10", withInsets: UIEdgeInsetsZero, preserveTransparency: false)
//Set drawing insets (percentage)
drawing.insets = UIEdgeInsetsMake(0.0, 0.0, 0.5, 0.5)

####Add/remove rows in sheets #####Objective-C

//Insert one row before 18th row
[worksheet addRowsAt:18];
//Remove it
[worksheet removeRow:18];

//Insert 10 rows before 18th row
[worksheet addRowsAt:18 count:10];
//Remove them
[worksheet removeRow:18 count:10];

#####Swift

//Insert one row before 18th row
worksheet.addRowsAt(18)
//Remove it
worksheet.removeRow(18)

//Insert 10 rows before 18th row
worksheet.addRowsAt(18, count: 10)
//Remove them
worksheet.removeRow(18, count: 10)

####Add/remove columns in sheets

TODO

####Change number formatting #####Objective-C

[[worksheet cellForCellReference:@"Y25"] setNumberFormat:@"_(0.00_);(0.00)"];

#####Swift

worksheet.cellForCellReference("Y25").numberFormat = "_(0.00_);(0.00)"

####Read content from merge cells #####Objective-C

//Get the cell at C10 or the upper-left cell if C10 belongs to a merge cell
BRACell *cell = [worksheet cellOrFirstCellInMergeCellForCellReference:@"C10"]

#####Swift

//Get the cell at C10 or the upper-left cell if C10 belongs to a merge cell
var cell: BRACell = worksheet.cellOrFirstCellInMergeCellForCellReference("C10")

####Create worksheet #####Objective-C

BRAWorksheet *worksheet = [spreadsheet.workbook createWorksheetNamed:@"Foo"];

#####Swift

var worksheet: BRAWorksheet = spreadsheet.workbook.createWorksheetNamed("Foo")

####Copy worksheet #####Objective-C

BRAWorksheet *worksheetToCopy = spreadsheet.workbook.worksheets[0];
BRAWorksheet *worksheet = [spreadsheet.workbook createWorksheetNamed:@"Foo" byCopyingWorksheet:worksheetToCopy];

#####Swift

var worksheetToCopy: BRAWorksheet = spreadsheet.workbook.worksheets[0]
var worksheet: BRAWorksheet = spreadsheet.workbook.createWorksheetNamed("Foo", byCopyingWorksheet: worksheetToCopy)

####Remove worksheet #####Objective-C

[_spreadsheet.workbook removeWorksheetNamed:@"Foo"];

#####Swift

spreadsheet.workbook.removeWorksheetNamed("Foo")

####Do some simple operation from Swift

let documentPath = NSBundle.mainBundle().pathForResource("testWorkbook", ofType: "xlsx")
        
let odp = BRAOfficeDocumentPackage.open(documentPath)
let worksheet: BRAWorksheet = odp!.workbook.worksheets[0] as! BRAWorksheet;

NSLog("%@", worksheet.cellForCellReference("A1").attributedStringValue())
        
let paths: Array = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true) as Array
let fullPath: String = (paths[0] as! String).stringByAppendingString("testSaveAs.xlsx")
odp!.saveAs(fullPath)

##A word about XLSX files

XLSX files are OPC packages (see ECMA-376 for more information). Below is a simplified hierarchical representation of the package contents.

Files have relationships, files are relationships... Have a look at this picture each time you want to change something in the library.

##License

Copyright (c) 2014-2016 René BIGOT.

Copyright (c) 2015 Fabian Pahl (Cocoa pods integration).

Copyright (c) 2016 Sam Hatchett (Mac port).

The XlsxReaderWriter library should be accompanied by a LICENSE file. This file contains the license relevant to this distribution. If no license exists, please contact me @renebigot.

About

XlsxReaderWriter is an Objective-C library for iPhone / iPad and Mac. It parses and writes MS Excel XLSX files.

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages