Create excel spreadsheet programmatically in xcode

Question

I am trying to integrate excel spreadsheets in ios so they could eventually be sent in an email in .xls or .csv format. I have found a tutorial and i think it is what im looking for but i do not know how to send it in an email yet. I am familiar with sending text in email but now so much with sending files.

Here is the link to the tutorial - http://xcodetipss.blogspot.com/2013/11/create-excelxls-file-programatically-in.html

and here is the tutorial code :

 1. Download Library from the url : Libxl Download and then add LibXL.framework to your xcode project

 2. Down your deployment target to ios6.0

 3. In Other linker Flag - set "-lstdc++"

 4. Add Framework -  “libc++.dylib”

 5. Add in your view controller - 
 #include "LibXL/libxl.h”

 6. Create xls file from the below code  and save to document directory.

 BookHandle book = xlCreateBook(); // use xlCreateXMLBook() for working with xlsx files                 SheetHandle sheet = xlBookAddSheet(book, "Sheet1", NULL);    
 FontHandle font = xlBookAddFont(book, 0);   
 xlFontSetColor(font, COLOR_RED);    
 xlFontSetBold(font, true);    
 FormatHandle boldFormat = xlBookAddFormat(book, 0);    
 xlFormatSetFont(boldFormat, font);     
 xlSheetWriteStr(sheet, 2, 1, "Title", boldFormat);    
 xlSheetWriteStr(sheet, 2, 2, "First name", boldFormat);    
 xlSheetWriteStr(sheet, 2, 3, "Last name", boldFormat);    
 xlSheetWriteStr(sheet, 2, 4, "Nationality", boldFormat);    
 xlSheetWriteStr(sheet, 2, 5, "Address", boldFormat);    
 xlSheetWriteStr(sheet, 2, 6, "P.O.Box", boldFormat);    
 xlSheetWriteStr(sheet, 2, 7, "City", boldFormat);    
 xlSheetWriteStr(sheet, 2, 8, "Country", boldFormat);    
 xlSheetWriteStr(sheet, 2, 9, "Phone", boldFormat);    
 xlSheetWriteStr(sheet, 2, 10, "Email", boldFormat);    
 xlSheetWriteStr(sheet, 2, 11, "Birth Date", boldFormat);    
 xlSheetWriteStr(sheet, 2, 12, "Wedding Date", boldFormat);        
 NSMutableArray *dataArray = [[NSMutableArray alloc]init];    
 dataArray = [Database executeQuery:@"select * from user"];    
 for (int i=0; i<[dataArray count]; i++) {        
 NSDictionary *dict = [dataArray objectAtIndex:i];        
 const char *converted_back = [[dict objectForKey:@"title"] UTF8String];        
 const char *converted_back1 = [[dict objectForKey:@"firstname"] UTF8String];        
 const char *converted_back2 = [[dict objectForKey:@"lastname"] UTF8String];        
 const char *converted_back3 = [[dict objectForKey:@"nationality"] UTF8String];        
 const char *converted_back4 = [[dict objectForKey:@"address"] UTF8String];        
 const char *converted_back5 = [[dict objectForKey:@"pobox"] UTF8String];        
 const char *converted_back6 = [[dict objectForKey:@"city"] UTF8String];        
 const char *converted_back7 = [[dict objectForKey:@"country"] UTF8String];        
 const char *converted_back8 = [[dict objectForKey:@"phone"] UTF8String];        
 const char *converted_back9 = [[dict objectForKey:@"email"] UTF8String];        
 const char *converted_back10 = [[dict objectForKey:@"birthdate"] UTF8String];        
 const char *converted_back11 = [[dict objectForKey:@"weddingdate"] UTF8String];

 xlSheetWriteStr(sheet, i+3, 1, converted_back, 0);        
 xlSheetWriteStr(sheet, i+3, 2, converted_back1, 0);        
 xlSheetWriteStr(sheet, i+3, 3, converted_back2, 0);        
 xlSheetWriteStr(sheet, i+3, 4, converted_back3, 0);        
 xlSheetWriteStr(sheet, i+3, 5, converted_back4, 0);        
 xlSheetWriteStr(sheet, i+3, 6, converted_back5, 0);        
 xlSheetWriteStr(sheet, i+3, 7, converted_back6, 0);        
 xlSheetWriteStr(sheet, i+3, 8, converted_back7, 0);        
 xlSheetWriteStr(sheet, i+3, 9, converted_back8, 0);        
 xlSheetWriteStr(sheet, i+3, 10, converted_back9, 0);        
 xlSheetWriteStr(sheet, i+3, 11, converted_back10, 0);        
 xlSheetWriteStr(sheet, i+3, 12, converted_back11, 0);    
 }     

 NSString *documentPath =    [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];    
 NSString *filename = [documentPath stringByAppendingPathComponent:@"Cartier.xls"];          xlBookSave(book, [filename UTF8String]);   
 xlBookRelease(book);

So my question is - How do i implement this in xcode so it can be sent in an email.

Thanks in advance.

This is my .h file

//
//  DataViewController.h
//  libxl-example
//
//  Created by dmytro on 12/25/12.
//  Copyright (c) 2012 xlware. All rights reserved.
//

#import <UIKit/UIKit.h>
#import <MessageUI/MessageUI.h>

@interface DataViewController : UIViewController <MFMailComposeViewControllerDelegate>

@property (strong, nonatomic) IBOutlet UILabel *dataLabel;
@property (strong, nonatomic) id dataObject;

- (IBAction)createExcel:(id)sender;



@end

This is my .m file

//
//  DataViewController.m
//  libxl-example
//
//  Created by dmytro on 12/25/12.
//  Copyright (c) 2012 xlware. All rights reserved.
//

#import "DataViewController.h"

#include "LibXL/libxl.h"

@interface DataViewController ()


@end
@implementation DataViewController

- (void)dealloc
{
    [_dataLabel release];
    [_dataObject release];
    [super dealloc];
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
    self.dataLabel.text = [self.dataObject description];
}

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

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

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

    xlSheetWriteStr(sheet, 2, 1, "Hello World !", 0);
    xlSheetWriteNum(sheet, 4, 1, 1000, 0);
    xlSheetWriteNum(sheet, 5, 1, 2000, 0);

    FontHandle font = xlBookAddFont(book, 0);
    xlFontSetColor(font, COLOR_RED);
    xlFontSetBold(font, true);
    FormatHandle boldFormat = xlBookAddFormat(book, 0);
    xlFormatSetFont(boldFormat, font);
    xlSheetWriteFormula(sheet, 6, 1, "SUM(B5:B6)", boldFormat);

    FormatHandle dateFormat = xlBookAddFormat(book, 0);
    xlFormatSetNumFormat(dateFormat, NUMFORMAT_DATE);
    xlSheetWriteNum(sheet, 8, 1, xlBookDatePack(book, 2011, 7, 20, 0, 0, 0, 0), dateFormat);

    xlSheetSetCol(sheet, 1, 1, 12, 0, 0);

    NSString *documentPath =
    [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];
    NSString *filename = [documentPath stringByAppendingPathComponent:@"out.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:@"Cartier.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 = @"Cartier.xls"; //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]);


    //Send email
    MFMailComposeViewController *mailMessage = [[MFMailComposeViewController alloc] init];
    [mailMessage setMailComposeDelegate:self];
    [mailMessage addAttachmentData:fileData mimeType:mimeType fileName:fileNameWithExtension];
    [self presentViewController:mailMessage animated:YES completion:nil];
    }


}


- (void)mailComposeController:(MFMailComposeViewController *)controller didFinishWithResult:(MFMailComposeResult)result error:(NSError *)error {

    switch (result)
    {
        case MFMailComposeResultCancelled:
            NSLog(@"Mail cancelled: you cancelled the operation and no email message was queued.");
            break;
        case MFMailComposeResultSaved:
            NSLog(@"Mail saved: you saved the email message in the drafts folder.");
            break;
        case MFMailComposeResultSent:
            NSLog(@"Mail send: the email message is queued in the outbox. It is ready to send.");
            break;
        case MFMailComposeResultFailed:
            NSLog(@"Mail failed: the email message was not saved or queued, possibly due to an error.");
            break;
        default:
            NSLog(@"Mail not sent.");
            break;
    }

    [controller dismissViewControllerAnimated:YES completion:nil];
}

@end

Solution

Once you've saved it to a documents directory sending it in an email is fairly straight forward:

 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:@"Cartier.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 = @"Cartier.xls"; //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]);


     //Send email
     MFMailComposeViewController *mailMessage = [[MFMailComposeViewController alloc] init];
     [mailMessage setMailComposeDelegate:self];
     [mailMessage addAttachmentData:fileData mimeType:mimeType fileName:fileNameWithExtension];
     [self presentViewController:mailMessage animated:YES completion:nil];
 }

The email delegate method is then:

- (void)mailComposeController:(MFMailComposeViewController *)controller didFinishWithResult:(MFMailComposeResult)result error:(NSError *)error {

    switch (result)
    {
        case MFMailComposeResultCancelled:
            NSLog(@"Mail cancelled: you cancelled the operation and no email message was queued.");
        break;
        case MFMailComposeResultSaved:
            NSLog(@"Mail saved: you saved the email message in the drafts folder.");
        break;
        case MFMailComposeResultSent:
            NSLog(@"Mail send: the email message is queued in the outbox. It is ready to send.");
        break;
        case MFMailComposeResultFailed:
            NSLog(@"Mail failed: the email message was not saved or queued, possibly due to an error.");
        break;
        default:
            NSLog(@"Mail not sent.");
        break;
    }

    [controller dismissViewControllerAnimated:YES completion:nil];
}

*EDIT -- *

You need to include the MessageUI framework (Build Phases, Link Binary with Libraries) then in your .h at the top add:

#import <MessageUI/MessageUI.h>

Then subscribe to the delegate:

@interface YOURCONTROLLER : UIViewController <MFMailComposeViewControllerDelegate>

Comments

[user3229614]

How would you implement this into the existing code i have?

[user3229614]

When I add the code to the project that I am working on it says that MFMail is an undeclared identifier. Would you know how to correct this issue? Any help is appreciated.

[user3229614]

The only error I have remaining is when i add the line: - (void)mailComposeController:(MFMailComposeViewController *)controller didFinishWithResult:(MFMailComposeResult)result error:(NSError *)error I get an error: use of undeclared identifier 'mailComposeController' How would i solve this issue?

[user3229614]

What is your email i would like to send you a photo of the error but stack overflow wont let me.

[CW0007007]

Did you read my last edit ? At the bottom ? You need to include the framework.

[user3229614]

Yes i have included the framework and am still experiencing errors these are what it says - (undeclared identifier) if you could provide me with an email you might be able to see what the problem is, thanks.

[CW0007007]

I don't need an email. Did you add the framework to the build settings ?

[user3229614]

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,NSU‌​serDomainMask, YES) objectAtIndex:0]; NSString *filename = [documentPath stringByAppendingPathComponent:@"Cartier.xls"]; //**END OF EDIT*

[user3229614]

I also added the whole .m file in case you need it.

[CW0007007]

You've put my code outside of the function. See my edit on yours.