相关:iOS开发创建Excel表格、Excel数据插入、导出Excel
iOS 生成Excel.xlsx文件
iOS 系统原生创建 Excel文件
用
libxlsxwriter
开源库生成Excel
用WKWebView
展示Excel
用UIDocumentInteractionController
来分享、导出Excel
我使用的是C语言编写的库libxlsxwriter创建的excel表格,用法在libxlsxwriter英文文档 ,导出/预览/分享excel,使用的是原生的
UIDocumentInteractionController
实现的。
在项目中的使用步骤如下
步骤一:使用cocoapods下载libxlsxwriter
至项目,我的项目文件名为feiyangApp
1-1: 打开Terminal终端,输入
cd /Users/zhangbin/Desktop/feiyang/feiyangApp
进入项目feiyangApp
1-2:终端输入vim Podfile
,进入该文件
1-3:进入Podfile文件后,输入i
,进入编辑模式。(出现-- INSERT --
就说明进入编辑模式)
1-4 : 在该文件中输入pod 'libxlsxwriter'
1-5 : 先按esc
,再输入:wq
,保存刚才的编辑操作再退出。
1-6 : 终端中输入pod install --verbose --no-repo-update
,安装libxlsxwriter
库,且不更新已安装的其他库。在项目中查看是否安装成功
步骤二:oc项目中使用libxlsxwriter
2.1导入#import <xlsxwriter/xlsxwriter.h>
2.2 表格数据源如下
{
"status" : 200,
"msg" : "OK",
"data" : [
{
"accountNo" : "16160308190000014623",
"paymentTime" : 1590818573000,
"allInPayOrderNo" : "1266608536524165120",
"discountMoney" : 0,
"title" : "炸鸡原料",
"orderNo" : "1590817979569664160",
"price" : 1,
"num" : "1",
"benefitCategoryName" : "其他",
"companyName" : "山东xxxxxx科技有限公司",
"benefitCategoryCode" : "W101103105101",
"orderInfo" : "{"商品名称":"炸鸡原料","商品分类代码":"W101103105101","商品数量":"1","商品单价":1.00,"商品分类名称":"其他"}",
"originalPayment" : 1,
"payment" : 1,
"couponName" : null
},
{
"accountNo" : "16160308190000014623",
"paymentTime" : 1591344525000,
"allInPayOrderNo" : "1268816331247988736",
"discountMoney" : 10,
"title" : "炸鸡原料",
"orderNo" : "1591343029512428230",
"price" : 1,
"num" : "109",
"benefitCategoryName" : "其他",
"companyName" : "山东xxxxxx科技有限公司",
"benefitCategoryCode" : "W101103105101",
"orderInfo" : "{"商品名称":"炸鸡原料","商品分类代码":"W101103105101","商品数量":"109","商品单价":1.00,"商品分类名称":"其他"}",
"originalPayment" : 10.02,
"payment" : 0.02,
"couponName" : null
},
{
"accountNo" : "16160308190000014623",
"paymentTime" : 1591348105000,
"allInPayOrderNo" : "1268831237196234752",
"discountMoney" : 20,
"title" : "炸鸡原料",
"orderNo" : "1591347802389069549",
"price" : 1,
"num" : "104",
"benefitCategoryName" : "其他",
"companyName" : "山东xxxxxx科技有限公司",
"benefitCategoryCode" : "W101103105101",
"orderInfo" : "{"商品名称":"炸鸡原料","商品分类代码":"W101103105101","商品数量":"104","商品单价":1.00,"商品分类名称":"其他"}",
"originalPayment" : 20.02,
"payment" : 0.02,
"couponName" : null
}
]
}
2.3 主要代码如下
#import "ZB_AccountStatementController.h"
#import "ZB_AccountModel.h"
#import <xlsxwriter/xlsxwriter.h>
@interface ZB_AccountStatementController ()<UIDocumentInteractionControllerDelegate>
@property (nonatomic,strong) NSMutableArray<ZB_AccountModel *> *accountDataArray;
@property (nonatomic,strong) UIButton * exportAccountBtn;
@property (nonatomic,strong)UIDocumentInteractionController * document;
@property(nonatomic,assign) NSInteger startYear;
@property(nonatomic,assign) NSInteger startMonth;
@property(nonatomic,assign) NSInteger startDay;
@property(nonatomic,assign) NSInteger endYear;
@property(nonatomic,assign) NSInteger endMonth;
@property(nonatomic,assign) NSInteger endDay;
@end
@implementation ZB_AccountStatementController
-(NSMutableArray *)accountDataArray{
if(!_accountDataArray){
_accountDataArray = [NSMutableArray array];
}
return _accountDataArray;
}
- (void)viewDidLoad {
[super viewDidLoad];
[self exportAccountUI];
[self netRequest];
}
-(void)exportAccountUI{
int navH = (int)NAV_HEIGHT;
self.exportAccountBtn = [[UIButton alloc]initWithFrame:CGRectMake(0,APP_SCREEN_HEIGHT - navH - BOTTOM_SAFE_AREA - tRealLength(35) ,APP_SCREEN_WIDTH,tRealLength(35))];
self.exportAccountBtn.backgroundColor = [UIColor whiteColor];
[self.exportAccountBtn setTitle:@"导出当前对账单" forState:UIControlStateNormal];
[self.exportAccountBtn setImage:[UIImage imageNamed:@"benefit_export.png"] forState:UIControlStateNormal];
[self.exportAccountBtn setTitleColor:[UIColor colorWithHexString:@"#a61b29"] forState:UIControlStateNormal];
self.exportAccountBtn.titleLabel.font = [UIFont systemFontOfSize:tRealFontSize(14)];
[self.exportAccountBtn addTarget:self action:@selector(exportAccountClick:) forControlEvents:UIControlEventTouchUpInside];
self.exportAccountBtn.hidden = YES;
[self.view addSubview:self.exportAccountBtn];
}
-(void)netRequest{
// ....
// 数据请求,拿到数据源
// 指定路径下创建excel
[self creationExcel];
}
// 创建excel
-(void)creationExcel{
// 获取cache路径
NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *xlsxName = [NSString stringWithFormat:@"%ld年%ld月%ld日-%ld年%ld月%ld日对账单.xlsx",self.startYear,self.startMonth,self.startDay,self.endYear,self.endMonth,self.endDay];
// 拼接 cache路径 + 名为xlsxName的文件 注意:该路径下的文件名一定要和后续导出的文件名保持一致,否则后续执行导出操作时,读取不到该文件
NSString *filename = [documentDirectory stringByAppendingPathComponent:xlsxName];
// 创建新xlsx文件,路径需要转成c字符串
lxw_workbook *workbook = workbook_new([filename UTF8String]);
// 创建sheet
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
// 格式1
lxw_format *titleformat = workbook_add_format(workbook);
// 加粗
format_set_bold(titleformat);
// 字体尺寸
format_set_font_size(titleformat, 20);
// 内容垂直居中
format_set_align(titleformat, LXW_ALIGN_VERTICAL_CENTER);
// 内容水平居中
format_set_align(titleformat, LXW_ALIGN_CENTER);
// 合并单元格。0行0列到0行8列合并为一行,并设定内容为"(山东)文旅惠民消费券《核销结算表》"
worksheet_merge_range(worksheet, 0, 0,0, 8, [@"(山东)文旅惠民消费券《核销结算表》" cStringUsingEncoding:NSUTF8StringEncoding], titleformat);
// 格式2
lxw_format *columnTitleformat = workbook_add_format(workbook);
// 内容垂直居中
format_set_align(columnTitleformat, LXW_ALIGN_VERTICAL_CENTER);
// 内容水平居中
format_set_align(columnTitleformat, LXW_ALIGN_CENTER);
// 将"统计周期:" 的总价写入到1行5列
worksheet_write_string(worksheet, 1, 5, [@"统计周期:" UTF8String], columnTitleformat);
// 将"填报日期:" 的总价写入到1行7列
worksheet_write_string(worksheet, 1, 7, [@"填报日期:" UTF8String], columnTitleformat);
// 第1行的高度为20,并将格式2应用到该行上。注意 行的高度和列表的宽度的值单位不一样,此50非彼50
worksheet_set_row(worksheet, 1, 30, columnTitleformat);
// 第0列到第8列的宽度为30。注意 此30非彼30
worksheet_set_column(worksheet, 0, 8, 30.0, NULL);
// 第2行的高度为30,并将格式2应用到该行上。注意 行的高度和列表的宽度的值单位不一样,此30非彼30
worksheet_set_row(worksheet, 2, 30, columnTitleformat);
// 将"序号"写入到2行0列
worksheet_write_string(worksheet, 2, 0, [@"序号" UTF8String], columnTitleformat);
// 将"企业名称"写入到2行1列
worksheet_write_string(worksheet, 2, 1, [@"企业名称" UTF8String], columnTitleformat);
// 将"商家账号"写入到2行2列
worksheet_write_string(worksheet, 2, 2, [@"商家账号" UTF8String], columnTitleformat);
// 将"消费日期"写入到2行3列
worksheet_write_string(worksheet, 2, 3, [@"消费日期" UTF8String], columnTitleformat);
// 将"销售订单号"写入到2行4列
worksheet_write_string(worksheet, 2, 4, [@"销售订单号" UTF8String], columnTitleformat);
// 将"销售金额"写入到2行5列
worksheet_write_string(worksheet, 2, 5, [@"销售金额" UTF8String], columnTitleformat);
// 将"通联订单号"写入到2行6列
worksheet_write_string(worksheet, 2, 6, [@"通联订单号" UTF8String], columnTitleformat);
// 将"优惠(券)"写入到2行7列
worksheet_write_string(worksheet, 2, 7, [@"优惠(券)" UTF8String], columnTitleformat);
// 将"实付金额"写入到2行8列
worksheet_write_string(worksheet, 2, 8, [@"实付金额" UTF8String], columnTitleformat);
int rowA = 3 + (int)self.accountDataArray.count;
// 合并单元格。rowA行0列 到 rowA行5列合并为一行,并设定内容为 "合计:"
worksheet_merge_range(worksheet, rowA, 0 ,rowA, 5, [@"合计:" cStringUsingEncoding:NSUTF8StringEncoding], columnTitleformat);
// 第rowA行的高度为50。注意 行的高度和列表的宽度的值单位不一样,此50非彼50
worksheet_set_row(worksheet, rowA, 50, columnTitleformat);
float discountPrice = 0.00;
float realPrice = 0.00;
// 统计 优惠(券)所在列的总价 和 实付金额 所在列的总价
for (int i = 0; i < self.accountDataArray.count; i++) {
ZB_AccountModel *model = self.accountDataArray[i];
discountPrice = discountPrice + [model.discountMoney floatValue];
realPrice = realPrice + [model.payment floatValue];
}
// 将优惠(券)所在列的总价写入到rowA行7列
worksheet_write_string(worksheet, rowA, 7, [[NSString stringWithFormat:@"%.2lf",discountPrice] UTF8String], columnTitleformat);
// 将实付金额 所在列的总价写入到rowA行8列
worksheet_write_string(worksheet, rowA, 8, [[NSString stringWithFormat:@"%.2lf",realPrice] UTF8String], columnTitleformat);
int rowB = 4 + (int)self.accountDataArray.count;
// 合并单元格。rowB行0列 到 rowB行1列合并为一行,并设定内容为 "负责人:"
worksheet_merge_range(worksheet, rowB, 0 ,rowB, 1, [@"负责人:" cStringUsingEncoding:NSUTF8StringEncoding], columnTitleformat);
// 合并单元格。rowB行3列 到 rowB行4列合并为一行,并设定内容为 "填表人:"
worksheet_merge_range(worksheet, rowB, 3 ,rowB, 4, [@"填表人:" cStringUsingEncoding:NSUTF8StringEncoding], columnTitleformat);
// 合并单元格。rowB行6列 到 rowB行7列合并为一行,并设定内容为 "单位:(盖章)"
worksheet_merge_range(worksheet, rowB, 6 ,rowB, 7, [@"单位:(盖章)" cStringUsingEncoding:NSUTF8StringEncoding], columnTitleformat);
// 第rowB行的高度为50,并将格式2应用到该行上。注意 行的高度和列表的宽度的值单位不一样,此50非彼50
worksheet_set_row(worksheet, rowB, 50, columnTitleformat);
// 格式3
lxw_format *markformat = workbook_add_format(workbook);
// 内容垂直居中
format_set_align(markformat, LXW_ALIGN_VERTICAL_CENTER);
// 内容水平居中
format_set_align(markformat, LXW_ALIGN_CENTER);
// 字体尺寸
format_set_font_size(markformat, 17);
// 字体颜色
format_set_font_color(markformat,0xFF0000);
int rowC = 5 + (int)self.accountDataArray.count;
// 合并单元格。rowC行0列 到 rowB行8列合并为一行,并设定内容为 "备注1、使用文化和旅游惠民消费券的订单,以通联订单号为唯一标识逐笔填写。"
worksheet_merge_range(worksheet, rowC, 0 ,rowC, 8, [@"备注1、使用文化和旅游惠民消费券的订单,以通联订单号为唯一标识逐笔填写。" cStringUsingEncoding:NSUTF8StringEncoding], markformat);
// 第rowC行的高度为50,并将格式3应用到该行上。注意 行的高度和列表的宽度的值单位不一样,此30非彼30
worksheet_set_row(worksheet, rowC, 30, markformat);
// 遍历数据源,将表格数据写入到某行某列中
for (int i = 0; i < self.accountDataArray.count; i++) {
ZB_AccountModel *model = self.accountDataArray[i];
NSString *calTime = [FYTimeManager timeWithDataTime:model.paymentTime/1000];
worksheet_set_row(worksheet, 3+i, 30, columnTitleformat);
worksheet_write_string(worksheet, 3+i, 0, [[NSString stringWithFormat:@"%d",i+1] UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 1, [model.companyName UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 2, [model.accountNo UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 3, [calTime UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 4, [model.orderNo UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 5, [model.price UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 6, [model.allInPayOrderNo UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 7, [model.discountMoney UTF8String], columnTitleformat);
worksheet_write_string(worksheet, 3+i, 8, [model.payment UTF8String], columnTitleformat);
}
//保存
workbook_close(workbook);
}
// 导出按钮点击
-(void)exportAccountClick:(UIButton *)btn{
// 获取cache路径
NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSLog(@"documentDirectory为%@",documentDirectory);
NSString *xlsxName = [NSString stringWithFormat:@"%ld年%ld月%ld日-%ld年%ld月%ld日对账单.xlsx",self.startYear,self.startMonth,self.startDay,self.endYear,self.endMonth,self.endDay];
// 拼接 cache路径 + 名为xlsxName的文件。注意:注意filename一定要和当初创建时的文件名保持一致,否则读取不到该文件
NSString *filename = [documentDirectory stringByAppendingPathComponent:xlsxName];
// 初始化并读取路径为filename的文件
self.document = [UIDocumentInteractionController interactionControllerWithURL:[NSURL fileURLWithPath:filename]];
self.document.delegate = self;
// 以present的方式展示该文件所支持的第三方App列表
[self.document presentOpenInMenuFromRect:self.view.bounds inView:self.view animated:YES];
}
#pragma mark - documentDelegate
-(UIViewController*)documentInteractionControllerViewControllerForPreview:(UIDocumentInteractionController*)controller{
return self;
}
- (UIView*)documentInteractionControllerViewForPreview:(UIDocumentInteractionController*)controller {
return self.view;
}
- (CGRect)documentInteractionControllerRectForPreview:(UIDocumentInteractionController*)controller {
return self.view.frame;
}
//点击预览窗口的“Done”(完成)按钮时调用
- (void)documentInteractionControllerDidEndPreview:(UIDocumentInteractionController*)controller {
}
// 文件分享面板弹出的时候调用
- (void)documentInteractionControllerWillPresentOpenInMenu:(UIDocumentInteractionController*)controller{
NSLog(@"WillPresentOpenInMenu");
}
// 当选择一个文件分享App的时候调用
- (void)documentInteractionController:(UIDocumentInteractionController*)controller willBeginSendingToApplication:(nullable NSString*)application{
NSLog(@"begin send : %@", application);
}
// 弹框消失的时候走的方法
-(void)documentInteractionControllerDidDismissOpenInMenu:(UIDocumentInteractionController*)controller{
NSLog(@"dissMiss");
}
@end
效果
-
使用的UITableview做的效果
-
导出按钮点击,弹出可以支持该文件的三方App
-
excel导出发送到App中