iOS 开发将数据写入Excel 并且从Excel中读取数据

1、将数据写入Excel

获取本地沙河地址

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
/// 获取当前日期
NSString *xlsxName = excelName;
  // 拼接 cache路径 + 名为xlsxName的文件   注意：该路径下的文件名一定要和后续导出的文件名保持一致，否则后续执行导出操作时，读取不到该文件
NSString *filename =[documentDirectory stringByAppendingPathComponent:xlsxName];

创建excel 使用xlsxwriter 开源库

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

创建excel样式

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

写入数据

 NSArray *titles = [self getTitls];
    /// 二级标题  模型对应的字段
    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");
}

完整代码

NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
/// 获取当前日期
NSString *xlsxName = excelName;
  // 拼接 cache路径 + 名为xlsxName的文件   注意：该路径下的文件名一定要和后续导出的文件名保持一致，否则后续执行导出操作时，读取不到该文件
NSString *filename =[documentDirectory stringByAppendingPathComponent:xlsxName];


/// 创建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);
// 内容垂直居中
format_set_align(columnTitleformat, LXW_ALIGN_VERTICAL_CENTER);
// 内容水平居中
format_set_align(columnTitleformat, LXW_ALIGN_CENTER);
/// 标题
NSArray *titles = [self getTitls];
/// 二级标题  模型对应的字段
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 中的数据

获取本地沙河地址

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

解析数据

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

协议数据返回

- (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);
        }
    }
}

数据解析也是使用第三方库DHxlsReader 不过gitUP上面好像找不到了