假设某一Excel文件,其中一列数据为文本型,但实际数据为数字【如:身份证、银行卡、保单号】通过读取Excel区域构建数据表,数据表筛选处理后,写入表格前虽然将数据表中对应列的数据类型转换为字符串,但是实际写入表格后仍然为科学计数法,有什么办法处理?
Dim objDatatable = ""
Dim arrayRet = ""
Dim iRet = ""
Dim objExcelWorkBook = ""
objExcelWorkBook = Excel.OpenExcel(@res"原始数据.xlsx",False,"Excel","","")
iRet = Excel.GetRowsCount(objExcelWorkBook,"Sheet1")
arrayRet = Excel.ReadRange(objExcelWorkBook,"Sheet1","A1:CM"&iRet)
Excel.CloseExcel(objExcelWorkBook,True)
objDatatable = Datatable.BuildDataTable(arrayRet,["所属出单团队","团队渠道","渠道小类","所属分公司","渠道大类","保单号","分保单号","批单号","批改原因","险类","产品名称","险种名称","投保人名称","被保险人名称","车辆种类名称","使用性质","车架号","车辆用途","车牌号码","新车购置价(不含税)","车船税额合计","车损保额","分保标志","核保人名称","产险业务员代码","产险业务员名称","合作网点代码","合作网点名称","寿险业务员代码","寿险业务员名称","二级渠道","代理机构名称","代理人名称","保险起期","保险止期","保费计算日期","承保确认日期","收付时间","保单保额(CNY)","签单保费(含税)","收付金额","期次","代理手续费(CNY)","代理手续费率(%)","已付手续费(CNY)","应付手续费(CNY)","个人业务提奖(CNY)","个人业务提奖率(%)","团队管理津贴(CNY)","团队管理津贴率(%)","客户服务费用(CNY)","客户服务费用率(%)","已付客户服务费(CNY)","应付客户服务费(CNY)","业务推动费(CNY)","业务推动费率(%)","绩效提奖(CNY)","绩效提奖率(%)","业务管理费用(CNY)","业务管理费用率(%)","已付业务管理费用(CNY)","应付业务管理费用(CNY)","机构费用(CNY)","机构费用率(%)","已付机构费用(CNY)","应付机构费用(CNY)","部门费用(CNY)","部门费用率(%)","已付部门费用","应付部门费用","团队费用(CNY)","团队费用率(%)","已付团队费用(CNY)","应付团队费用(CNY)","平台服务费用(CNY)","平台服务费用率(%)","已付平台服务费用(CNY)","应付平台服务费用(CNY)","渠道管理津贴(CNY)","渠道管理津贴费用率","税费及附加(CNY)","税费及附加费用率","车系","投保习惯","所属渠道","结算状态","关联单标识","关联单号","新业务价值","新标转续志","平台不浮动原因"])
objDatatable = Datatable.QueryDataTable(objDatatable,"险种名称.str.contains(\"机动车\") or 险种名称==\"险种名称\"")
Datatable.ConvertColumnDataType(objDatatable,"保单号","str",False,Null)
arrayRet = Datatable.GetDataTableByArray(objDatatable,False)
TracePrint(arrayRet)
objExcelWorkBook = Excel.OpenExcel(@res"车险数据.xlsx",True,"Excel","","")
Excel.WriteRange(objExcelWorkBook,"Sheet1","A1",arrayRet,False)
Excel.CloseExcel(objExcelWorkBook,True)