def getProvinceInfoEng(): provincesInfo = ExcelTool.getArrayBySheetName(os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Province.xlsx"),"province") provincesInfoList = ExcelTool.nArrayToList(provincesInfo, 31, 6) result = {} for i in range(31): proEnglishName = provincesInfoList[i][2] result[proEnglishName] = provincesInfoList[i] return result
def getTableData(): countryNum = 189 # 国家总数 provinceNum = 31 # 身份数量 # 获取省份名列表,包括 : 中文名、英文名、纬度、经度 provincesInfo = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Province.xlsx"), "province") # #获取国家名 地址列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") # BR国家子集合,包含63个国家名,或其别名 sunCountrys = SubCountrys.getBrCountryList() countryList = [] # 国家名list,有序 countrySwitch = CountrySwitchName.getcountrySwitch() # 替换一些国家的名字 for i in range(countryNum): countryName = country_name[i, 0] # if(countrySwitch.has_key(countryName) and countrySwitch[countryName]!=""): if (countryName in countrySwitch and countrySwitch[countryName] != ""): countryList.append(countrySwitch[countryName]) else: countryList.append(countryName) files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP4_DIR"]) print(files) # .xlsx结果文件列表 resultList = [] # 全部excel文件处理后的结果,容器 errMsg = "" # 错误信息 unit = '%' # 单位 默认undefined for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 fullFileName = file.split("\\")[len(file.split("\\")) - 1] result["fullFileName"] = fullFileName # 文件全名 (带后缀) result["fileName"] = fullFileName.split(".")[0] # 文件全名 (不带后缀) print(file + " start") excelData = xlrd.open_workbook(file, "rb") emptySheets = [] # 空数据的sheet timeline = [] # timeline 滚动轴 的 数据集合 sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 seriesList = {} # series数据,,所有省份对国家的数据, #初始化seriesList,每个省份数据都会空的list for i in range(provinceNum): seriesList[provincesInfo[i][2]] = [] validData = [] # sheet中 所有有效数据之和, validDataNum = 0 #有效数据的个数,大于0的数据都是有效数据 maxMin = [] #全部sheet,每个sheet的最大值和最小值 for sheetName in sheetNameList: # 遍历所有sheet #处理某个sheet sheetMaxMin = [] # 记录下每个sheet,每列的最大值,最小值 #sheetName = sheetName # sheet名转码 #处理(某sheet)的数据 timeline.append(sheetName) # 年份加入timeline中,转为int sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, 'name', row=countryNum, column=provinceNum) # 获取某年(某sheet)的数据 #sheetData.sum()/(countryNum*provinceNum) # 先处理空sheet if (len(sheetData) == 0): maxMin.append([0, 0]) #给这个sheet设置个默认的最大最小值 #创建一个189*31的 零矩阵 for i in range(provinceNum): #遍历省份,即每一列 seriesCountry = [] #某列的数据,即某省在某年的数据 # seriesCountrySub=[] # 某列的数据,即某省在某年的数据,只包含指定的63个国家 for k in range(countryNum): #遍历此列的所有国家 countryInfo = [] countryInfo.append(189) # 排序 countryInfo.append(0) # 数据 countryInfo.append(countryList[k]) # 国家名 seriesCountry.append({ "name": countryList[k], "value": countryInfo }) # if (countryList[k] in sunCountrys ): # seriesCountrySub.append({ # "name": countryList[k], # "value": countryInfo # }) seriesList[provincesInfo[i][2]].append({ "time": sheetName, "min": -1, #注意,这里设置的min和max,刚好可以使空sheet,在地图上展示的时候,不渲染任何颜色,min和max不能包括数据0 "max": -0.1, "data": seriesCountry, "subData": [] }) emptySheets.append(sheetName) #记下空sheet continue #再处理非空sheet # 先遍历一遍,如果有负数,处理成0 for row in range(countryNum): for column in range(provinceNum): # if (sheetData[row][column] < -100 or sheetData[row][column] > 100): #大于100或者小于-100,处理成0 sheetData[row][column] = 0 sheetDataSort = np.argsort(-sheetData, axis=0) # 排序,按列排序,降序 for i in range(provinceNum): #遍历省份,即每一列 sheetMaxMin.append(sheetData[sheetDataSort[0][i]][i]) sheetMaxMin.append(sheetData[sheetDataSort[countryNum - 1][i]][i]) #处理某一列的数据 sort = {} for j in range(countryNum): sort[sheetDataSort[j] [i]] = j + 1 # 索引从0开始,排序从1开始。获取到此列(某省)的买个国家的排序 seriesCountry = [] # 某列的数据,即某省在某年的数据 seriesCountrySub = [] # 某列的数据,即某省在某年的数据,只包含指定的63个国家 for k in range(countryNum): #遍历此列的所有国家 countryInfo = [] countryInfo.append(sort[k]) # 排序 countryInfo.append(sheetData[k][i]) # 数据 #countryInfo.append(sheetName) # sheet名,滚动轴项名 countryInfo.append(countryList[k]) # 国家名 seriesCountry.append({ "name": countryList[k], "value": countryInfo }) if (countryList[k] in sunCountrys and countryList[k] != "China"): seriesCountrySub.append({ "name": countryList[k], "value": countryInfo }) if (sheetData[k][i] > 0): #记录下有效数据 validData.append(sheetData[k][i]) validDataNum = validDataNum + 1 seriesList[provincesInfo[i][2]].append({ "time": sheetName, "min": sheetData[sheetDataSort[countryNum - 1][i]][i], "max": sheetData[sheetDataSort[0][i]][i], "data": seriesCountry, "subData": seriesCountrySub }) maxMin.append([min(sheetMaxMin), max(sheetMaxMin)]) result["average"] = np.array(validData).sum() / validDataNum #平均值 result["counties"] = countryList # 国家列表 result["timeline"] = timeline # 滚动轴,sheet名集合 result["emptySheets"] = emptySheets # 空数据sheet名集合 result["series"] = seriesList result['unit'] = unit # 单位 result['maxMin'] = maxMin # 每个sheet的最大最小值 resultList.append(result) print(file + " end") except BaseException: print("Error: 文件有问题: " + file) import traceback traceback.print_exc() errMsg += file + "<br/>" resultListJson = json.dumps(resultList) print("Map4返回值 resultListJson :") #print( resultListJson) return resultListJson
def rank_result(): country_num = 5 # 前10个国家,这个参数可以从页面上传进来 # #获取国家名 地址列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") print(os.getcwd()) #获取结果excel 地址列表 ,只获取xls和xlsx文件 files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP1_DIR"]) print(files) # .xlsx结果文件列表 result_list = [] errMsg = "" for file in files: #批量读取 try: result = {} #单个excel文件处理后的结果 file_name = file.split("\\")[len(file.split("\\")) - 1].split(".")[0] result['importCountryNum'] = country_num result['exportCountryNum'] = country_num result["fileName"] = file_name print(file + " start") excelData = xlrd.open_workbook(file, "rb") #从excel获取sheet, 转化成numpy.array Tot = ExcelTool.getNpArrayFromSheet(excelData, u'Tot', 'name') FD_ = ExcelTool.getNpArrayFromSheet(excelData, u'FD_S', 'name') Tra = ExcelTool.getNpArrayFromSheet(excelData, u'Tra', 'name') FD4 = ExcelTool.getNpArrayFromSheet(excelData, u'FD4', 'name') T4 = ExcelTool.getNpArrayFromSheet(excelData, u'T4', 'name') unit = '' #单位 try: unit = excelData.sheet_by_name("Unit").cell_value(0, 0) except Exception as e: unit = '未定义' # 单位未定义 # if (e.message.find("No sheet named") ==-1 ): # unit='未定义' #单位未定义 result['unit'] = unit # 单位 for i in range(0, len(Tot[0]) - 1): # 对Tot做处理,把对角线数据设为0 Tot[i, i] = 0 Tot[i, len(Tot[0]) - 1] = 0 Tot[len(Tot[0]) - 1, i] = 0 for i in range(0, Tra.shape[1] - 1): # Tra最后一行赋值为0 Tra[189, i] = 0 Tra_sort = np.argsort(-Tra, axis=0) # 按列排序 index_im = Tra_sort[:, 1] # 第2列排序的索引值 第2列是进口总排序 index_ex = Tra_sort[:, 0] # 第1列排序的索引值 第1列是出口总排行 import_data = getImportData(country_name, Tra, Tot, index_im, country_num, index_ex) export_data = getExportData(country_name, Tra, Tot, index_ex, country_num, index_im) result["exportData"] = export_data result["importData"] = import_data print(file + " end") result_list.append(result) #一个文件计算完毕 except BaseException: print("Error: 文件有问题," + file) import traceback traceback.print_exc() errMsg += file + "<br/>" # if len(errMsg)!= 0: # result_list result_list_json = json.dumps(result_list) print("返回值 result_list_json :") print(result_list_json) return (result_list_json)
def getIndexDate(request): # Setting.FILR_DIR["INDEX_DIR"] #文件路径 files = ExcelTool.listExcelFile(Setting.FILR_DIR["INDEX_DIR"]) print(files) resultList = {} # 获取省份名列表,包括 : 中文名、英文名、纬度、经度 provincesInfo = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Province.xlsx"), "province") #provincesInfoList = transform(provincesInfo,31,6) resultList["province"] = ProvinceTool.getProvinceInfoChina() for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 fullFileName = file.split("\\")[len(file.split("\\")) - 1] fileName = fullFileName.split(".")[0] if (fileName == "1"): #处理1.xlsx print("1.xlsx") yearNum = 0 # 有几年 yearList = [] #年份 excelData = xlrd.open_workbook(file, "rb") # excel的全部数据 sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 # if ('year' not in sheetNameList): # # 如果不存item,那么excel错误, # print("Error: 文件有问题: " + file) # break # 读取 year, 获得年份 sheetData = ExcelTool.getNpArrayFromSheet( excelData, "year", "name", 0, 6) # 只有6列 yearNum = sheetData.shape[0] #有几年 for i in range(yearNum): yearList.append(str(sheetData[i][0]).split(".")[0]) resultList["year"] = yearList yearNum = len(yearList) #年数 # 处理 “POP” 人口 sheetDataPOP = ExcelTool.getNpArrayFromSheet( excelData, "POP", "name", 31, 0) # sheetDataPOPList = ExcelTool.nArrayToList( sheetDataPOP, 31, yearNum) resultList["pop"] = sheetDataPOPList # 处理GDP sheetDataGDP = ExcelTool.getNpArrayFromSheet( excelData, "GDP", "name", 31, 0) # sheetDataGDPList = ExcelTool.nArrayToList( sheetDataGDP, 31, yearNum) resultList["gdp"] = sheetDataGDPList # 处理Energy sheetDataenergy = ExcelTool.getNpArrayFromSheet( excelData, "energy", "name", 31, 0) # sheetDataenergyList = ExcelTool.nArrayToList( sheetDataenergy, 31, yearNum) resultList["energy"] = sheetDataenergyList print("end 1.xlsx") elif (fileName == "2"): print("2.xlsx") except BaseException: print("Error: 文件有问题: " + file) import traceback traceback.print_exc() resultListJson = json.dumps(resultList) return HttpResponse(resultListJson)
def deleteData(request): mapDir = request.GET.get("mapDir") fileNameList = request.GET.get("fileNameList") res = ExcelTool.deleteFile(Setting.FILR_DIR[mapDir], fileNameList) return HttpResponse(res)
def getIndexDate(request): # Setting.FILR_DIR["INDEX_DIR"] #文件路径 provinceNum = 31 #31个省市 不包括港澳台 files = ExcelTool.listExcelFile(Setting.FILR_DIR["INDEX_DIR"]) print(files) resultList = {} resultList2 = {} # 获取省份名列表,包括 : 中文名、英文名、纬度、经度 provincesInfo = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Province.xlsx"), "province") #provincesInfoList = transform(provincesInfo,31,6) resultList["province"] = ProvinceTool.getProvinceInfoChina() for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 fullFileName = file.split("\\")[len(file.split("\\")) - 1] fileName = fullFileName.split(".")[0] if (fileName == "1"): #处理1.xlsx print("1.xlsx") yearNum = 0 # 有几年 yearList = [] #年份 excelData = xlrd.open_workbook(file, "rb") # excel的全部数据 sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 # if ('year' not in sheetNameList): # # 如果不存item,那么excel错误, # print("Error: 文件有问题: " + file) # break # 读取 year, 获得年份 sheetData = ExcelTool.getNpArrayFromSheet( excelData, "year", "name", 0, 6) # 只有6列 yearNum = sheetData.shape[0] #有几年 for i in range(yearNum): yearList.append(str(sheetData[i][0]).split(".")[0]) resultList["year"] = yearList yearNum = len(yearList) #年数 # 处理 “POP” 人口 sheetDataPOP = ExcelTool.getNpArrayFromSheet( excelData, "POP", "name", provinceNum, 0) # sheetDataPOPList = ExcelTool.nArrayToList( sheetDataPOP, 31, yearNum) resultList["pop"] = sheetDataPOPList # 处理GDP sheetDataGDP = ExcelTool.getNpArrayFromSheet( excelData, "GDP", "name", provinceNum, 0) # sheetDataGDPList = ExcelTool.nArrayToList( sheetDataGDP, 31, yearNum) resultList["gdp"] = sheetDataGDPList # 处理Energy sheetDataenergy = ExcelTool.getNpArrayFromSheet( excelData, "energy", "name", provinceNum, 0) # sheetDataenergyList = ExcelTool.nArrayToList( sheetDataenergy, 31, yearNum) resultList["energy"] = sheetDataenergyList print("end 1.xlsx") elif (fileName == "2_r"): #处理2_r.xlsx #读取List excelData = xlrd.open_workbook(file, "rb") # excel的全部数据 sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 # 读取 List, 获得年份 typeNum = 47 #类型总数,一共有47种类型 sheetListData = ExcelTool.getNpArrayFromSheet( excelData, "List", "name", typeNum, 3) # title = sheetListData[0][0] # 标题 unit = sheetListData[1][0] # 单位 typeList = [] typeName = [] for i in range(typeNum): typeList.append(int(float(sheetListData[i][1]))) #划分 typeName.append(sheetListData[i][2]) #类型名称 yearList = [] # 年份 yearDataList = {} for sheetName in sheetNameList: if sheetName != "List": yearList.append(int(sheetName)) yearData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name", typeNum, provinceNum) # 某年的数据 sheetDataList = ExcelTool.nArrayToList( yearData, typeNum, provinceNum) # yearDataList[sheetName] = sheetDataList resultList2 = { "title": title, "unit": unit, "typeList": typeList, "typeName": typeName, "yearList": sorted(yearList), "yearDataList": yearDataList, "province": ProvinceTool.getProvinceInfoChina() } print("2_r.xlsx") except BaseException: print("Error: 文件有问题: " + file) import traceback traceback.print_exc() resultListJson = json.dumps({ "echart1": resultList, #第一个echarts图 "echart2": resultList2 #第二个echarts图 }) return HttpResponse(resultListJson)
def getTableData(): files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP3_DIR"]) print(files) # .xlsx结果文件列表 resultList = [] # 全部excel文件处理后的结果,容器 errMsg = "" #错误信息 countryNum = 189 #国家总数 # 获取国家名列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") countryList = [] # 国家名list,有序 for i in range(countryNum): countryList.append(country_name[i, 0]) for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 fullFileName = file.split("\\")[len(file.split("\\")) - 1] result["fullFileName"] = fullFileName # 文件全名 (带后缀) result["fileName"] = fullFileName.split(".")[0] # 文件全名 (不带后缀) print(file + " start") excelData = xlrd.open_workbook(file, "rb") unit = 'undefined' # 单位 默认undefined unitX = 'undefined' # 单位 unitY = 'undefined' # 单位 emptySheets = [] # 空数据的sheet xAxis = [] # x轴最小值 最大值 人均消耗 yAxis = [] # y轴最小值 最大值 人均gdp symbolSize = [] # sheet中 气泡大小之和列表, timeline = [] #timeline ,年数的集合 sheetNameList = excelData.sheet_names() #获取此文件的全部sheet名 seriesList = [] # series数据,所有年份,所有国家的数据 for sheetName in sheetNameList: #遍历sheet if sheetName != 'Unit': #处理某年(某sheet)的数据 timeline.append(int(sheetName)) # 年份加入timeline中,转为int series = [] # 某年,所有国家的数据 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, 'name', row=countryNum) #获取某年(某sheet)的数据 #先处理空sheet if (len(sheetData) == 0): seriesList.append(series) emptySheets.append(sheetName) continue # 遍历一遍,如果有负数,处理成0 for row in range(countryNum): for column in range(3): # 默认都是3列 if (sheetData[row][column] < 0): sheetData[row][column] = 0 symbolSize.append(sheetData[:, 2].sum()) #本sheet中,气泡大小之和 sheetDataSort = np.argsort(-sheetData, axis=0) #排序,按列排序,降序 yAxis.append( sheetData[sheetDataSort[0][1]][1]) #把最大值 先存起来,之后比较 for index in range(1, countryNum): #找到y轴最小的非0值,作为y轴最小值 if (sheetData[sheetDataSort[countryNum - index][1]][1] != 0): yAxis.append(sheetData[sheetDataSort[countryNum - index][1]][1]) break xAxis.append([ sheetData[sheetDataSort[0][0]][0], sheetData[sheetDataSort[countryNum - 1][0]][0] ]) #把最大值和最小值都先存起来,之后比较 sort = {} #气泡大小排序 for i in range(countryNum): sort[sheetDataSort[i][2]] = i # 索引和排序都从0开始 for j in range(countryNum): seriesCountry = [] #某年某个国家的数据 seriesCountry.append(sheetData[j][0]) # 人均消耗量 seriesCountry.append(sheetData[j][1]) # 人均gdp seriesCountry.append(countryList[j]) # 国家名 seriesCountry.append(sort[j] + 1) # 排序号,气泡 # 大小的排序号. 从1开始 seriesCountry.append(sheetData[j][2]) # 气泡大小 series.append(seriesCountry) seriesList.append(series) else: #处理3个单位 unit = excelData.sheet_by_name("Unit").cell_value( 0, 2) # 标题单位 unitY = excelData.sheet_by_name("Unit").cell_value( 0, 1) # Y轴单位 unitX = excelData.sheet_by_name("Unit").cell_value( 0, 0) # X轴单位 xMax = handleMaxMin(xAxis, "max", 2, rate=1) xMin = handleMaxMin(xAxis, "min", 2) yMax = handleMaxMin(yAxis, "max", 1) yMin = handleMaxMin(yAxis, "min", 2) result['unit'] = unit # 单位 result['unitX'] = unitX # 单位X轴 result['unitY'] = unitY # 单位Y轴 result["xAxisMax"] = np.array(xAxis).max() #x轴最小值 最大值 人均消耗 result["xMax"] = xMax #x轴最大值 人均消耗 result["xMin"] = xMin #x轴最小值 人均消耗 result["xAxisMin"] = np.array(xAxis).min() #x轴最小值 最大值 人均消耗 result["yAxisMax"] = np.array(yAxis).max() #y轴最小值 最大值 人均gdp result["yAxisMin"] = np.array(yAxis).min() #y轴最小值 最大值 人均gdp result["yMax"] = yMax # y轴最大值 人均gdp result["yMin"] = yMin # y轴最小值 人均gdp result["counties"] = countryList #国家列表 result["timeline"] = timeline #时间 ,sheet名集合 result["emptySheets"] = emptySheets #空数据sheet名集合 result["series"] = seriesList result["averageSize"] = np.array(symbolSize).sum() / ( countryNum * len(timeline)) # 气泡大小平均值 resultList.append(result) print(file + " end") except BaseException: print("Error: 文件有问题: " + file) import traceback traceback.print_exc() print(BaseException) errMsg += file + "<br/>" resultListJson = json.dumps(resultList) print("Map3返回值 resultListJson :") print(resultListJson) return resultListJson
def getTableData(): countryNum = 189 # 全部国家总数 189个国家 #countryNum = 100 # 国家总数,100个国家 # 获取省份名列表,包括 : 中文名、英文名、纬度、经度 # #获取国家名 地址列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") # BR国家子集合,包含63个国家名,或其别名 sunCountrys = SubCountrys.getBrCountryList() countryList = [] # 国家名list,有序 countryInfo = {} #国家信息,带序号 countrySwitch = CountrySwitchName.getcountrySwitch() # 替换一些国家的名字 for i in range(countryNum): countryName = country_name[i, 0] flag = False #是否是BR国家,默认不是 if (countryName in sunCountrys or # (countrySwitch.has_key(countryName) and countrySwitch[countryName] in sunCountrys)): ((countryName in countrySwitch) and countrySwitch[countryName] in sunCountrys)): flag = True # if(countrySwitch.has_key(countryName) and countrySwitch[countryName]!=""): if (countryName in countrySwitch and countrySwitch[countryName] != ""): countryList.append(countrySwitch[countryName]) countryInfo[countrySwitch[countryName]] = { "EchartName": countrySwitch[countryName], "SourceName": countryName, "sort": i, "isBrRegion": flag } else: countryList.append(countryName) countryInfo[countryName] = { "EchartName": countryName, "SourceName": countryName, "sort": i, "isBrRegion": flag } files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP5_DIR"]) print(files) # .xlsx结果文件列表 resultList = [] # 全部excel文件处理后的结果,容器 errMsg = "" # 错误信息 # 单位 默认undefined for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 original = [] # 原始数据 middleData = [] # 中间数据 middleNameList = [] # 中间数据的sheet名,有序 unit = [] #单位 sheetMaxSource = [] #原始数据,每个指标的最大值,也就是每个中间数据shet的最大值 sheetMax = [] #中间数据,每个指标的最大值,也就是每个中间数据shet的最大值 fullFileName = file.split("\\")[len(file.split("\\")) - 1] result["fullFileName"] = fullFileName # 文件全名 (带后缀) result["fileName"] = fullFileName.split(".")[0] # 文件全名 (不带后缀) print(file + " start") excelData = xlrd.open_workbook(file, "rb") sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 n = len( sheetNameList ) - 2 # n是中间数据sheet的个数,也就是雷达图中indicator指标的个数。sheets中有一个Index,一个unit,其余的都是中间数据 for sheetName in sheetNameList: # 遍历所有sheet if (sheetName.lower() == "unit"): # 单位sheet 1*n矩阵 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name") for i in range(n): unit.append(sheetData[0][i]) elif (sheetName.lower() == "index"): #源数据sheet 189*n矩阵 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name", countryNum, n) for row in range(countryNum): originalRow = [] for column in range(n): if sheetData[row][column] == '': #空cell处理成0 originalRow.append(0) else: originalRow.append( float(sheetData[row][column])) #转为float original.append(originalRow) sheetDataSort = np.argsort( -np.array(original).astype(np.float64), axis=0) # 排序,按列排序,降序 for column in range(n): # 列,先记录下每列的最大值, sheetMaxSource.append( original[sheetDataSort[0][column]][column]) else: #中间数据sheet 189*189矩阵 middleNameList.append(sheetName) #中间数据的sheet名,有序 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name", countryNum, countryNum) sheetMax.append(np.max(sheetData)) #此sheet的最大值 #遍历整个sheet middleSheet = [] #此sheet的数据容器 for row in range(countryNum): middleRow = [] # 此sheet中,某行的数据容器 for column in range(countryNum): middleRow.append(sheetData[row][column]) middleSheet.append(middleRow) middleData.append(middleSheet) result["original"] = original #原始数据 result["middle"] = middleData #中间数据 result["middleNameList"] = middleNameList #中间数据 sheet名称,也就是指标名称,有序 result["sheetMax"] = sheetMax #中间数据 sheet的最大值 result["sheetMaxSource"] = sheetMaxSource #原始数据sheet,每个指标的最大值 result["unit"] = unit # 单位 result["countryList"] = countryList # 国家列表 有序列表 result["countryInfo"] = countryInfo # 国家字典 带序号 resultList.append(result) print(file + " end") except BaseException: print("############################################ ") print("Error: 文件有问题: " + file) import traceback traceback.print_exc() errMsg += file + "<br/>" print("############################################ ") resultListJson = json.dumps(resultList) print("Map5返回值 resultListJson :") return resultListJson
def deleteData(request, dir): fileNameList = request.GET.get("fileNameList") res = ExcelTool.deleteFile(Setting.FILR_DIR[dir], fileNameList) return HttpResponse("<p>" + res + "</p>")
def getTableData(): countryNum = 189 # 全部国家总数 189个国家 lineNum = 3 # 每个层级里,线的数目 # #获取国家名 地址列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") # BR国家子集合,包含63个国家名,或其别名 sunCountrys = SubCountrys.getBrCountryList() countryList = [] # 国家名list,有序 countryInfo = {} # 国家信息,带序号 countrySwitch = CountrySwitchName.getcountrySwitch() # 替换一些国家的名字, echarts中,有些国家的名字与excel中的国家名对不上 for i in range(countryNum): countryName = country_name[i, 0] flag = False #是否是BR国家,默认不是 if (countryName in sunCountrys or # (countrySwitch.has_key(countryName) and countrySwitch[countryName] in sunCountrys)): (countryName in countrySwitch and countrySwitch[countryName] in sunCountrys)): flag = True # if(countrySwitch.has_key(countryName) and countrySwitch[countryName]!=""): if (countryName in countrySwitch and countrySwitch[countryName] != ""): countryList.append(countrySwitch[countryName]) countryInfo[countrySwitch[countryName]] = { "EchartName": countrySwitch[countryName], "SourceName": countryName, "sort": i, "isBrRegion": flag } else: countryList.append(countryName) #其中countryName是EchartName countryInfo[countryName] = { "EchartName": countryName, "SourceName": countryName, "sort": i, "isBrRegion": flag } #其中countryName是EchartName files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP6_DIR"]) print(files) # .xlsx结果文件列表 resultList = [] # 全部excel文件处理后的结果,容器 errMsg = "" # 错误信息 # 单位 默认undefined for file in files: try: result = {} # 单个excel文件处理后的结果 original = [] # 原始数据 middleData = [] # 中间数据 middleDataSort = [] # 中间数据 的 排序数据 DataNameList = [] # 中间数据的sheet名,有序 unit = [] # 单位 sheetMaxSource = [] # 原始数据,每个指标的最大值,也就是每个中间数据shet的最大值 sheetMax = [] # 中间数据,每个指标的最大值,也就是每个中间数据shet的最大值 fullFileName = file.split("\\")[len(file.split("\\")) - 1] result["fullFileName"] = fullFileName # 文件全名 (带后缀) result["fileName"] = fullFileName.split(".")[0] # 文件全名 (不带后缀) print(file + " start") excelData = xlrd.open_workbook(file, "rb") sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 n = len(sheetNameList) - 1 # n是中间数据sheet的个数,一个unit,其余的都是中间数据 for sheetName in sheetNameList: # 遍历所有sheet if (sheetName.lower() == "unit"): # 单位sheet 1*n矩阵 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name", 1, 1) unit = sheetData[0][0] else: #中间数据sheet 189*189矩阵 DataNameList.append(sheetName) # 中间数据的sheet名,有序 sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, "name", countryNum, countryNum) # 遍历整个sheet middleSheet = [] # 此sheet的数据容器 for row in range(countryNum): middleRow = [] # 此sheet中,某行的数据容器 for column in range(countryNum): middleRow.append(sheetData[row][column]) middleSheet.append(middleRow) middleData.append(middleSheet) # 遍历整个sheetDataSort sheetDataSort = np.argsort(-sheetData, axis=1) # 排序,按行排序,降序 middleSheetSort = [] # 此sheetSort的数据容器 for row in range(countryNum): middleRowSort = [] # 此sheet中,某行的数据容器 for column in range(countryNum): middleRowSort.append( int(sheetDataSort[row][column]) ) #这里要吧int64转换成int,不然下面的json.dumps会报错 middleSheetSort.append(middleRowSort) middleDataSort.append(middleSheetSort) result["middleData"] = middleData #数据 result["middleDataSort"] = middleDataSort #数据的排序 result["level"] = n #层级, 等于sheet的个数-1 # result["curLevel"]=0 #当前所在层级,默认是0,这个属性,主要用在前台的逻辑控制 result["DataNameList"] = DataNameList # 数据 sheet名称,也就是指标名称,有序 result["countryList"] = countryList # 国家列表 有序列表 result["countryInfo"] = countryInfo # 国家字典 带序号 result["unit"] = unit # 国家字典 带序号 resultList.append(result) print(file + " end") except BaseException as e: print("############################################ ") print("Error: 文件有问题: " + file) import traceback traceback.print_exc() errMsg += file + "<br/>" print("############################################ ") resultListJson = json.dumps(resultList) print("Map6返回值 resultListJson :") return resultListJson
def getTableData(): countryNum = 189 # 全部国家总数 189个国家 provinceNum = 31 # 中国的31个省市自治区,不处理港澳台 countryShowNum = 10 #对于每个省份,只要展示其进口前10名和出口前10名 # 获取省份名列表,包括 : 中文名、英文名、纬度、经度 provincesInfo = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Province.xlsx"), "province") proInfoList = {} #省份的经纬度信息 for iii in range(provinceNum): proName = provincesInfo[iii, 2] # 省份名字 proLatitude = provincesInfo[iii, 4] # 省份纬度 proLongitude = provincesInfo[iii, 5] # 省份经度 proInfoList[proName] = { "name": proName, "latitude": proLatitude, "longitude": proLongitude } # #获取国家名 地址列表 country_name = ExcelTool.getArrayBySheetName( os.path.join(Setting.FILR_DIR["COMMON_DIR"], "Countries.xlsx"), "country") # BR国家子集合,包含63个国家名,或其别名 sunCountrys = SubCountrys.getBrCountryList() countryList = [] # 国家名list,有序 countrySwitch = CountrySwitchName.getcountrySwitch() # 替换一些国家的名字 for ii in range(countryNum): countryName = country_name[ii, 0] # if(countrySwitch.has_key(countryName) and countrySwitch[countryName]!=""): if (countryName in countrySwitch and countrySwitch[countryName] != ""): countryList.append(countrySwitch[countryName]) else: countryList.append(countryName) files = ExcelTool.listExcelFile(Setting.FILR_DIR["MAP7_DIR"]) print(files) # .xlsx结果文件列表 resultList = [] # 全部excel文件处理后的结果,容器 unit = '%' # 单位 默认undefined for file in files: # 遍历每个excel文件 try: result = {} # 单个excel文件处理后的结果 fullFileName = file.split("\\")[len(file.split("\\")) - 1] result["fullFileName"] = fullFileName # 文件全名 (带后缀) result["fileName"] = fullFileName.split(".")[0] # 文件全名 (不带后缀) print(file + " start") excelData = xlrd.open_workbook(file, "rb") #excel的全部数据 emptySheets = [] # 空数据的sheet timeline = [] # timeline 滚动轴 的 数据集合 sheetNameList = excelData.sheet_names() # 获取此文件的全部sheet名 seriesList = {} # series数据,,所有省份对国家的数据, #初始化seriesList,每个省份数据都会空的list for m in range(provinceNum): seriesList[provincesInfo[m][2]] = [] # 遍历所有sheet for n in range(len(sheetNameList)): #开始处理某个sheet sheetName = sheetNameList[n] #sheetName = sheetName # sheet名转码 #处理单位 if sheetName == 'Unit' or sheetName.lower() == 'unit': unit = excelData.sheet_by_name(sheetName).cell_value(0, 0) title = excelData.sheet_by_name(sheetName).cell_value(0, 1) continue # 处理(某sheet)数据 timeline.append(int(sheetName)) #年份加入timeline中,转为int sheetData = ExcelTool.getNpArrayFromSheet( excelData, sheetName, 'name', row=countryNum, column=2 * provinceNum) # 获取某年(某sheet)的数据 if (sheetData.shape[1] != 62 or sheetData.shape[0] != 189): #跳过这个sheet print(fullFileName + " 的sheet有问题: " + sheetName) continue #先处理空sheet if (len(sheetData) == 0): emptySheets.append(sheetName) # 记下空sheet continue #直接跳出,不显示空sheet的数据 #再处理非空sheet #先遍历一遍,如果有负数,处理成0 for row in range(countryNum): for column in range(provinceNum * 2): # if (sheetData[row][column] < 0): #小于0,处理成0 sheetData[row][column] = 0 # 排序,按列排序,降序 sheetDataSort = np.argsort(-sheetData, axis=0) # 遍历省份,即每一列 ,前31列是进口数据,后31列是出口数据 for i in range(provinceNum * 2): # 处理某一列的数据 seriesCountry = [] # 某列的数据,即某省在某年的数据 for k in range(countryShowNum): #遍历前31列中,每列的排序前10的国家,进口 kCoun = sheetDataSort[k][i] #此列排第k个国家的国家序号 #sheetData[kCoun][i] # 值 isBr = 0 #是否是BR国家,默认不是 if (countryList[kCoun] in sunCountrys and countryList[k] != "China"): isBr = 1 seriesCountry.append({ "name": countryList[kCoun], #国家名 "value": sheetData[kCoun][i], #值 "isBr": isBr #是否是BR国家 }) #对于每个省份的seriesList,前10个是进口数据,后10个是出口数据 if i < provinceNum: seriesList[provincesInfo[i][2]].append( {"data": seriesCountry}) else: for seriesCountryInfo in seriesCountry: #把10个出口数据,放到进口数据后面 seriesList[provincesInfo[i - provinceNum][2]][n][ "data"].append(seriesCountryInfo) result["counties"] = countryList # 国家列表 result["timeline"] = timeline # 滚动轴,sheet名集合 result["emptySheets"] = emptySheets # 空数据sheet名集合 result["series"] = seriesList result['unit'] = unit # 单位 result['title'] = title # 标题 result['proInfoList'] = proInfoList # 省份的经纬度信息 resultList.append(result) print(file + " end") except BaseException: print("Error: 文件有问题: " + file) import traceback traceback.print_exc() resultListJson = json.dumps(resultList) print("Map7返回值 resultListJson :") return resultListJson