Export SQLite data to Excel in iOS programmatically(OC)

浏览数：36 / 时间：2015年06月11日

//For the app I have that did this, the SQLite data was fairly large. Therefore, I used a background thread to export all the data to a CSV (comma separated value) file, which Excel can import, and then opened up a mail composer with the CSV file as an attachment. If your data is small, you might not need to use a background thread:

- (IBAction) export: (id) sender
{
// in my full code, I start a UIActivityIndicator spinning and show a
// message that the app is "Exporting ..."

[self performSelectorInBackground: @selector(exportImpl) withObject: nil];
}

//Here is exportImpl

- (void) exportImpl
{
NSAutoreleasePool* pool = [[NSAutoreleasePool alloc] init];

NSArray* documentPaths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSSystemDomainMask, YES);
NSString* documentsDir = [documentPaths objectAtIndex:0];
NSString* csvPath = [documentsDir stringByAppendingPathComponent: @"export.csv"];

// TODO: mutex lock?
[sqliteDb exportCsv: csvPath];

[pool release];

// mail is graphical and must be run on UI thread
[self performSelectorOnMainThread: @selector(mail:) withObject: csvPath waitUntilDone: NO];
}

- (void) mail: (NSString*) filePath
{
// here I stop animating the UIActivityIndicator

// http://howtomakeiphoneapps.com/home/2009/7/14/how-to-make-your-iphone-app-send-email-with-attachments.html
BOOL success = NO;
if ([MFMailComposeViewController canSendMail]) {
// TODO: autorelease pool needed ?
NSData* database = [NSData dataWithContentsOfFile: filePath];

if (database != nil) {
MFMailComposeViewController* picker = [[MFMailComposeViewController alloc] init];
picker.mailComposeDelegate = self;
[picker setSubject:[NSString stringWithFormat: @"%@ %@", [[UIDevice currentDevice] model], [filePath lastPathComponent]]];

NSString* filename = [filePath lastPathComponent];
[picker addAttachmentData: database mimeType:@"application/octet-stream" fileName: filename];
NSString* emailBody = @"Attached is the SQLite data from my iOS device.";
[picker setMessageBody:emailBody isHTML:YES];

[self presentModalViewController:picker animated:YES];
success = YES;
[picker release];
}
}

if (!success) {
UIAlertView* warning = [[UIAlertView alloc] initWithTitle: @"Error"
message: @"Unable to send attachment!"
delegate: self
cancelButtonTitle: @"Ok"
otherButtonTitles: nil];
[warning show];
[warning release];
}
}

//And then, I have a class that encapsulates all my SQLite data. This class is the only one that makes sqlite calls. In this class, I have a method for exporting data into a CSV file in my app‘s caches directory. The variable sqliteDb in the code above is an instance of this class. Here‘s the method to export data:

-(void) exportCsv: (NSString*) filename
{
// We record this filename, because the app deletes it on exit
self.tempFile = filename;

NSAutoreleasePool* pool = [[NSAutoreleasePool alloc] init];
// Setup the database object
sqlite3* database;

// Open the database from the users filessytem
if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK)
{
[self createTempFile: filename];
NSOutputStream* output = [[NSOutputStream alloc] initToFileAtPath: filename append: YES];
[output open];
if (![output hasSpaceAvailable]) {
NSLog(@"No space available in %@", filename);
// TODO: UIAlertView?
} else {
NSString* header = @"Source,Time,Latitude,Longitude,Accuracy\n";
NSInteger result = [output write: [header UTF8String] maxLength: [header length]];
if (result <= 0) {
NSLog(@"exportCsv encountered error=%d from header write", result);
}

BOOL errorLogged = NO;
NSString* sqlStatement = @"select timestamp,latitude,longitude,horizontalAccuracy from my_sqlite_table";

// Setup the SQL Statement and compile it for faster access
sqlite3_stmt* compiledStatement;
if (sqlite3_prepare_v2(database, [sqlStatement UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
{
// Loop through the results and write them to the CSV file
while (sqlite3_step(compiledStatement) == SQLITE_ROW) {
// Read the data from the result row
NSInteger secondsSinceReferenceDate = (NSInteger)sqlite3_column_double(compiledStatement, 0);
float lat = (float)sqlite3_column_double(compiledStatement, 1);
float lon = (float)sqlite3_column_double(compiledStatement, 2);
float accuracy = (float)sqlite3_column_double(compiledStatement, 3);

if (lat != 0 && lon != 0) {
NSDate* timestamp = [[NSDate alloc] initWithTimeIntervalSinceReferenceDate: secondsSinceReferenceDate];
NSString* line = [[NSString alloc] initWithFormat: @"%@,%@,%f,%f,%d\n",
table, [dateFormatter stringFromDate: timestamp], lat, lon, (NSInteger)accuracy];
result = [output write: [line UTF8String] maxLength: [line length]];
if (!errorLogged && (result <= 0)) {
NSLog(@"exportCsv write returned %d", result);
errorLogged = YES;
}
[line release];
[timestamp release];
}
// Release the compiled statement from memory
sqlite3_finalize(compiledStatement);
}
}
}
[output close];
[output release];
}

sqlite3_close(database);
[pool release];
}

-(void) createTempFile: (NSString*) filename {
NSFileManager* fileSystem = [NSFileManager defaultManager];
[fileSystem removeItemAtPath: filename error: nil];

NSMutableDictionary* attributes = [[NSMutableDictionary alloc] init];
NSNumber* permission = [NSNumber numberWithLong: 0640];
[attributes setObject: permission forKey: NSFilePosixPermissions];
if (![fileSystem createFileAtPath: filename contents: nil attributes: attributes]) {
NSLog(@"Unable to create temp file for exporting CSV.");
// TODO: UIAlertView?
}
[attributes release];
}

My code is exporting a database of location information. Obviously, inside exportCsv, you will need to replace my sqlite calls with ones that are appropriate for your database content.

Also, the code stores the data in a temporary file. You‘ll probably want to decide when to clean out those temp files.

Obviously, this code was written before ARC was available. Adjust as needed.

Export SQLite data to Excel in iOS programmatically(OC),,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Export SQLite data to Excel in iOS programmatically(OC)