以编程方式将SQLite数据导出到iOS中的Excel [英] Export SQLite data to Excel in iOS programmatically
问题描述
在我的应用程序中，我使用sqlite作为后端（本地存储数据）。
我能够将数据插入到我的表中。但我想要做的是，想要以编程方式将所有我的sqlite数据导入excel。我不想在这个app.once excel表中使用服务器是生成用户应该能够邮寄该表。
这可以在iPhone上使用：
请帮帮我。
In my application,i am using sqlite as a backend(to store data locially). I am able to insert data into my table.but what i want to do is,want to import all my sqlite data into excel programmatically.And i don't want to use server for this app.once the excel sheet is generate user should be able to mail that sheet. Is this possible in iPhone: Please help me out .
以下是我将数据插入表格的代码：
Following is my code to insert data into table:
-(IBAction)Login
{
sqlite3_stmt *stmt;
char *errorMsg;
char *update1 = "insert into Login1 values (?,?,?,?);";
int x = sqlite3_prepare_v2(database, update1, -1, &stmt, nil);
if (x == SQLITE_OK)
{
sqlite3_bind_text(stmt, 1, NULL,-1, NULL);
sqlite3_bind_text(stmt, 2, [USERID UTF8String],-1, NULL);
sqlite3_bind_text(stmt, 3, [str1 UTF8String],-1, NULL);
sqlite3_bind_text(stmt, 4, [str4 UTF8String],-1, NULL);
}
if (sqlite3_step(stmt) != SQLITE_DONE)
NSLog(@"Error: %@",errorMsg);
sqlite3_finalize(stmt);
}
推荐答案
对于我这样做的应用程序，SQLite数据相当大。因此，我使用后台线程将所有数据导出到CSV（逗号分隔值）文件，Excel可以导入该文件，然后打开一个邮件编辑器，将CSV文件作为附件。如果您的数据很小，则可能不需要使用后台线程：
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];
}
这是 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];
}
}
然后，我有一个类封装了我的所有SQLite数据。这个类是唯一一个进行sqlite调用的类。在本课程中，我有一种方法可以将数据导出到我应用的缓存目录中的CSV文件中。上面代码中的变量 sqliteDb 是此类的一个实例。以下是导出数据的方法：
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];
}
我的代码是导出位置信息数据库。显然，在 exportCsv 中，您需要将sqlite调用替换为适合您的数据库内容的调用。
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.
显然，此代码是在ARC可用之前编写的。根据需要进行调整。
Obviously, this code was written before ARC was available. Adjust as needed.
这篇关于以编程方式将SQLite数据导出到iOS中的Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
