pandas和SQL数据分析实战视频教程
# -*- coding: utf-8 -*-
"""
Created on Mon Apr 4 18:04:06
@author: Toby，qq:231469242,原创版权
"""
import csv,os,openpyxl
#获取所有excel文件名
def Get_excelFileNames():
excelFiles_list=[]
for excelFilename in os.listdir('.'):
if excelFilename.endswith('.xlsx') or excelFilename.endswith('.xls'):
excelFiles_list.append(excelFilename)
return excelFiles_list
'''
excelFiles_list
Out[17]: ['Book1.xlsx', 'Book2.xlsx', 'Book3.xlsx', 'Book4.xlsx']
'''
#获取一个excel内所有表格
def Get_sheets_from_oneExcel(excelFileName):
wb=openpyxl.load_workbook(excelFileName)
sheets=wb.get_sheet_names()
return sheets
'''
Get_sheets_from_oneExcel('Book1.xlsx')
Out[19]: ['Sheet1', 'Sheet2', 'Sheet3']
'''
#获取excel的行信息
def Get_sheet_rowData(excelFileName,sheetName,rowNum):
wb=openpyxl.load_workbook(excelFileName)
sheet=wb.get_sheet_by_name(sheetName)
highest_column=sheet.get_highest_column()
rowData=[] #append each cell to this list
#loop through each cell in the row
for colNum in range(1,highest_column+1):
#append each cell's data to rowData
cell_value=sheet.cell(row=rowNum,column=colNum).value
rowData.append(cell_value)
return rowData
'''
Get_sheet_rowData('Book1.xlsx','Sheet1',1)
Out[39]: ['fsdf ', 'ds', 'fdf']
'''
#获取excel的信息
def Get_sheet_Data(excelFileName,sheetName):
wb=openpyxl.load_workbook(excelFileName)
sheet=wb.get_sheet_by_name(sheetName)
highest_row=sheet.get_highest_row()
highest_column=sheet.get_highest_column()
sheet_data=[]
for rowNum in range(1,highest_row+1):
rowData=[] #append each cell to this list
#loop through each cell in the row
for colNum in range(1,highest_column+1):
#append each cell's data to rowData
cell_value=sheet.cell(row=rowNum,column=colNum).value
rowData.append(cell_value)
sheet_data.append(rowData)
rowData=[] #清空行数据，为遍历重新准备
return sheet_data
'''
Get_sheet_Data("Book1.xlsx",'Sheet1')
Out[41]: [['fsdf ', 'ds', 'fdf'], ['fdsf', 'tt', 'fds'],
['gfdgg', 'gfdg', 'gdfgdg']]
'''
#取一个CSV名字
def Get_csvFileName(excelFileName,sheetName):
baseName=os.path.splitext(excelFileName)[0]
csvFileName=os.path.join('csvFiles',baseName+"_"+sheetName+".csv")
return csvFileName
#把一个excel的sheet转换为csv
def Convert_oneExcelsheet_to_csv(excelFileName,sheetName):
csvFileName=Get_csvFileName(excelFileName,sheetName)
csvObj=open(csvFileName,'w',newline='')
csvWriter=csv.writer(csvObj)
sheet_data=Get_sheet_Data(excelFileName,sheetName)
for rowData in sheet_data:
csvWriter.writerow(rowData)
csvObj.close()
'''
Convert_oneExcelsheet_to_csv("Book1.xlsx",'Sheet1')
'''
#把一个excel的所有sheet转换为各自的csv文件
def Convert_oneExcelAllsheets_to_csv(excelFileName):
sheets=Get_sheets_from_oneExcel(excelFileName)
for sheetName in sheets:
Convert_oneExcelsheet_to_csv(excelFileName,sheetName)
#转换所有excel的所有sheet为各自的csv文件
def Convert_allExcellAllsheets_to_csv(excelFiles_list):
for excelFileName in excelFiles_list:
Convert_oneExcelAllsheets_to_csv(excelFileName)
os.makedirs('csvFiles', exist_ok=True)
excelFiles_list=Get_excelFileNames()
#转换所有excel的所有sheet为各自的csv文件
Convert_allExcellAllsheets_to_csv(excelFiles_list)
