iOS 開發(fā)將數(shù)據(jù)寫入Excel 并且從Excel中讀取數(shù)據(jù)

1、將數(shù)據(jù)寫入Excel

獲取本地沙河地址

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
/// 獲取當(dāng)前日期
NSString *xlsxName = excelName;
  // 拼接 cache路徑 + 名為xlsxName的文件   注意：該路徑下的文件名一定要和后續(xù)導(dǎo)出的文件名保持一致，否則后續(xù)執(zhí)行導(dǎo)出操作時(shí)，讀取不到該文件
NSString *filename =[documentDirectory stringByAppendingPathComponent:xlsxName];

創(chuàng)建excel 使用xlsxwriter 開源庫

lxw_workbook  *workbook  = workbook_new([filename UTF8String]);
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, @"desipot".UTF8String);

創(chuàng)建excel樣式

lxw_format *columnTitleformat = workbook_add_format(workbook);
// 內(nèi)容垂直居中
format_set_align(columnTitleformat, LXW_ALIGN_VERTICAL_CENTER);
// 內(nèi)容水平居中
format_set_align(columnTitleformat, LXW_ALIGN_CENTER);

寫入數(shù)據(jù)

 NSArray *titles = [self getTitls];
    /// 二級標(biāo)題  模型對應(yīng)的字段
    NSMutableArray *attrs = [self getAttrs];
    int i = 0;
    for (NSString *title in titles) {
        worksheet_write_string(worksheet,0,i, [title UTF8String], columnTitleformat);
        worksheet_write_string(worksheet,1,i, [attrs[i] UTF8String], columnTitleformat);
        for (int j = 0; j < dataArray.count; j++) {
            ZYWealthDataItemModel *md = dataArray[j];
            NSMutableDictionary *mdDic = [md yy_modelToJSONObject];
            
            NSString *keySting = attrs[i];
            NSString *valueString =  @"";
            if([keySting isEqual:@"isupLoad"]){
                valueString = [NSString stringWithFormat:@"%d",j + 2];
            }else{
                valueString = [mdDic valueForKey:attrs[i]];
            }
            worksheet_write_string(worksheet,j + 2,i, [valueString UTF8String], columnTitleformat);
        }
        i ++;
    }

關(guān)閉寫入

int resut = workbook_close(workbook);
if(resut != 0){
    NSLog(@"falied");
}

完整代碼

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
/// 獲取當(dāng)前日期
NSString *xlsxName = excelName;
  // 拼接 cache路徑 + 名為xlsxName的文件   注意：該路徑下的文件名一定要和后續(xù)導(dǎo)出的文件名保持一致，否則后續(xù)執(zhí)行導(dǎo)出操作時(shí)，讀取不到該文件
NSString *filename =[documentDirectory stringByAppendingPathComponent:xlsxName];


/// 創(chuàng)建excel表格  char * 類型
lxw_workbook  *workbook  = workbook_new([filename UTF8String]);
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, @"desipot".UTF8String);

lxw_format *format = workbook_add_format(workbook);
format_set_bold(format);
worksheet_set_column(worksheet, 0, 0, 20, NULL);

lxw_format *columnTitleformat = workbook_add_format(workbook);
// 內(nèi)容垂直居中
format_set_align(columnTitleformat, LXW_ALIGN_VERTICAL_CENTER);
// 內(nèi)容水平居中
format_set_align(columnTitleformat, LXW_ALIGN_CENTER);
/// 標(biāo)題
NSArray *titles = [self getTitls];
/// 二級標(biāo)題  模型對應(yīng)的字段
NSMutableArray *attrs = [self getAttrs];


int i = 0;
for (NSString *title in titles) {
    worksheet_write_string(worksheet,0,i, [title UTF8String], columnTitleformat);
    worksheet_write_string(worksheet,1,i, [attrs[i] UTF8String], columnTitleformat);
    for (int j = 0; j < dataArray.count; j++) {
        ZYWealthDataItemModel *md = dataArray[j];
        NSMutableDictionary *mdDic = [md yy_modelToJSONObject];
        
        NSString *keySting = attrs[i];
        NSString *valueString =  @"";
        if([keySting isEqual:@"isupLoad"]){
            valueString = [NSString stringWithFormat:@"%d",j + 2];
        }else{
            valueString = [mdDic valueForKey:attrs[i]];
        }
        worksheet_write_string(worksheet,j + 2,i, [valueString UTF8String], columnTitleformat);
    }
    i ++;
}
int resut = workbook_close(workbook);
if(resut != 0){
    NSLog(@"falied");
}

2、讀取Excel 中的數(shù)據(jù)

獲取本地沙河地址

NSString *paths =   [[NSBundle mainBundle]pathForResource:@"list" ofType:@".xlsx"];

解析數(shù)據(jù)

LAWExcelTool.shareInstance.delegate = self;
[LAWExcelTool.shareInstance parserExcelWithPath:paths];

協(xié)議數(shù)據(jù)返回

- (void)parser:(LAWExcelTool *)parser success:(id)responseObj{
    NSArray * array = (NSArray *)responseObj;
    NSInteger startIndex = 0;
    NSInteger endIndex = 0;
    
    
    NSInteger startCompanyIndex = 0;
    NSInteger endCompanyIndex = 0;
    
    NSUInteger index = 0;
    for (ZContent *content in array) {
        /// 獲取所有的 股票代碼
        ///
        if([content.value isEqual:@"stockName"]) startCompanyIndex = index;
        if([content.value isEqual:@"股票代碼"]) endCompanyIndex = index;

        if([content.value isEqual:@"stockCode"]) startIndex = index;
        if([content.value isEqual:@"公司名稱"]) endIndex = index;
                
        index ++;
    }
    
    NSMutableArray *items = [[NSMutableArray alloc]init];
    for(NSUInteger j = startIndex + 1; j < endIndex; j++){
        ZContent *content = array[j];
        ZContent *company = array[j-startIndex + startCompanyIndex];
        ZYWealthDataItemModel *model = [[ZYWealthDataItemModel alloc]init];
        model.stockCode = content.value;
        model.stockName  = company.value;
        [items addObject:content.value];
    }
    
    if(items.count > 0){
        if(self.getAllStockCodeBlock){
            self.getAllStockCodeBlock(items);
        }
    }
}

數(shù)據(jù)解析也是使用第三方庫DHxlsReader 不過gitUP上面好像找不到了