How to embed formulas in libxl for ios

Question

I want to be able to add some simple formulas to libxl like addition and subtraction, just dont know how to do it. Just wondering how to add simple addition and subtraction formulas.

Here is my code :

- (IBAction)createExcel:(id)sender
{
    NSLog(@"createExcel");

    BookHandle book = xlCreateBook(); // use xlCreateXMLBook() for working with xlsx files

    SheetHandle sheet = xlBookAddSheet(book, "Sheet1", NULL);

These are the numbers that i want the formulas to change.(100 & 150)

  xlSheetWriteStr(sheet, 1, 0, 100, 0);
  xlSheetWriteStr(sheet, 1, 0, 150, 0);


          NSString *documentPath =
    [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];
    NSString *filename = [documentPath stringByAppendingPathComponent:@"insuranceclaim.xls"];

    xlBookSave(book, [filename UTF8String]);

    xlBookRelease(book);

    if (![MFMailComposeViewController canSendMail]) {
        //Show alert that device cannot send email, this is because an email account     hasn't been setup.
    }

    else {

        //**EDIT HERE**
        //Use this to retrieve your recently saved file

        NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];
        NSString *filename = [documentPath stringByAppendingPathComponent:@"insuranceclaim.xls"];

        //**END OF EDIT**

        NSString *mimeType = @"application/vnd.ms-excel"; //This should be the MIME type for els files. May want to double check.
        NSData *fileData = [NSData dataWithContentsOfFile:filename];
        NSString *fileNameWithExtension =   self.personFirstnameTextField.text; //This is what you want the file to be called on the email along with it's extension:

        //If you want to then delete the file:
        NSError *error;
        if (![[NSFileManager defaultManager] removeItemAtPath:filename error:&error])
            NSLog(@"ERROR REMOVING FILE: %@", [error localizedDescription]);


    }


}

score 1 · Answer 1 · answered May 18 '15 at 21:00

To the person who complained that we should "read the documentation", the main problem is that there are no examples in Objective-C, just for plain old C.

Here is what works for me, I build a formula string to add two rows in column D, then apply it.

NSString *formula=[NSString stringWithFormat:@"SUM(D%d:D%d)",startRow,endrow]; 
xlSheetWriteFormula(sheet, row, 2, [formula cStringUsingEncoding: NSUTF8StringEncoding], cellFormat);

cellFormat is the format I defined for my desired font and border effects.

I would guess the problem people have been having is with the string encoding.

I would urge anyone who has bought the licence for LibXL to encourage the developers to also develop sample code for Objective-C. We have gone several years now, without it.

score 0 · Accepted Answer · answered Aug 30 '14 at 19:54

0

Adding formulas is straight-forward. Here is the relevant line from the sample app that comes with the LibXl for iOS download:

xlSheetWriteFormula(sheet, 6, 1, "SUM(B5:B6)", boldFormat);

Looks like the only thing important to keep in mind is that the formula needs to be a C string, not an Objective-C string. Here's the relevant passage in their documentation: http://www.libxl.com/spreadsheet.html#writeFormula

answered Aug 30 '14 at 19:54

Matthew Burke

2,295
17
14

i keep getting use of undeclared identifier for bold format, do you know why? How would i declare it? – Flare gun Aug 31 '14 at 00:16
Check the documentation I linked to. You can always pass in 0 for the format. – Matthew Burke Sep 02 '14 at 19:32
The calculations are still not adding up, the excel file is identifying them as text and not number cells, how do i fix this? – Flare gun Sep 09 '14 at 00:13
the user will enter the numbers through a text field and i will gather the info like this - xlSheetWriteStr(sheet, 2, 17,[ self.persontotaldepTextField.text UTF8String], 0); – Flare gun Sep 09 '14 at 01:48
I am pulling a core data string. – Flare gun Sep 09 '14 at 01:48
Ok, let's try this one last time: READ THE DOCUMENTATION. Read the whole thing. In regards to your latest difficulty I see that there is a `xlSheetWriteNum` function. – Matthew Burke Sep 10 '14 at 21:52
with xlSheetWriteNum i get the error message "Passing 'char' [4] to parameter of incompatible 'double'" – Flare gun Sep 10 '14 at 23:40

How to embed formulas in libxl for ios

2 Answers2