excel操作

操作excel 
2018.8.21学习   
1. 测试数据一般存在数据库，excel中  
2. 怎么操作excel？  
   1> 打开工作薄 -- wb = load_workbook() （打开工作薄，且必须是已经存在的工作薄）
   2> 找到表单  --- wb['Sheet1']
   3> 定位到指定单元格  
3. 操作excel的库：openpyxl, xlrd(只能读), xlwt(只能写)   
4. openpyxl的常用的函数：load_workbook用于打开指定的excel表，返回一个工作薄对象; Workboot 新建一个工作薄
5. **openpyxl操作excel文件，必须是.xlsx后缀，不能是.xls后缀，否则会报错，且新建.xlsx文件不要在pycharm使用New--File，这样新建后在使用load_workbook时会报错，正确的操作是打开准备新建excel目录，在windows下新建一个excel文件**  
6. 如果涉及写入数据到excel，那一定要先将excel关闭，再去操作写入或是更新数据，否会报错`IOError: [Errno 13] Permission denied: 'test_data.xlsx'，且最后需要保存更新  
   ```python
   from openpyxl import  load_workbook
   
   #1.打开工作薄，且是已经存在的工作薄
   wb = load_workbook('test_data.xlsx')
   
   #2.定位到表单
   sheet = wb['Sheet1']  #最新定义的一种用法
   #sheet = wb.get_sheet_by_name('Sheet1')  #可能会有一个warning,说已过时，但是不影响使用
   
   #3.定位到指定单元格，通过行号和列号(坐标，行号和列号都是从1开始数)，获取单元格里面的数据
   print(sheet.cell(row=2,column=2).value)  #返回的数据类型为unicode
   #print(sheet['B2'].value)  #该方法也可以
   
   #4. 修改指定单元格的数据
   sheet.cell(row=2,column=3).value = 'qqq'  #赋值运算
   wb.save('test_data.xlsx')   #更新数据后一定要保存工作薄，注意：要先将工作薄关闭才能保存成功，否则会报错IOError: [Errno 13] Permission denied: 'test_data.xlsx'
   ```
7. 读出excel第一行所有的数据，存储到一个列表中
   ```python
   wb = load_workbook('test_data.xlsx')
   sheet = wb['Sheet1'] 
   row_list=[]
   for i in range(1,sheet.max_column+1):
       row_list.append(sheet.cell(row=1,column=i).value)
   print row_list
   ```
8.读出excel第一列所有的数据，存储到一个列表中
   ```python
   wb = load_workbook('test_data.xlsx')
   heet = wb['Sheet1'] 
   column_list = []
   for j in range(1,sheet.max_row+1):
       column_list.append(sheet.cell(row=j,column=1).value)
   print(column_list)
   ```
   
9. 读出excel全部的数据，第一行存储在一个子列表中，所有行的数据再存储到一个大列表里
    ```python
    wb = load_workbook('test_data.xlsx')
    sheet = wb['Sheet1'] 
    all_list=[]  #所有数据列表
    for i in range(1,sheet.max_row+1):  #控制行
        row_list = []  #存储每一行数据的列表
        for j in range(1,sheet.max_column+1): #控制列
            row_list.append(sheet.cell(row=i,column=j).value)
        all_list.append(row_list)
    print all_list
    ```
10. **从excel里读取出来的数据除了int和float类型读出后仍是int和float类型，其它的都全部变为str类型了**
    **因此对excel写入数据时，也只能是整形，浮点和字符串类型**   
   ```python
   wb = load_workbook('test_data.xlsx')
   sheet = wb['Sheet1'] 
   all_list=[]  #所有数据列表
   for i in range(1,sheet.max_row+1):  #控制行
       row_list = []  #存储每一行数据的列表
       for j in range(1,sheet.max_column+1): #控制列
           if type(sheet.cell(row=i,column=j).value) == long or  type(sheet.cell(row=i,column=j).value) ==  float:
               row_list.append(sheet.cell(row=i,column=j).value)
           else:
               #对于非浮点或非long的数据类型，因为返回的是unicode，故需要转换为str
               row_list.append(sheet.cell(row=i, column=j).value.encode())
       all_list.append(row_list)
   print all_list
   ```
11. 读取excel数据的代码封装成一个类
   ```python
   from openpyxl import  load_workbook
   
   class DoExcel():
   
       def __init__(self,filename,sheet):
           self.filename = filename
           self.sheet = sheet
   
       def do_excel(self):
           #1.打开工作薄，且是已经存在的工作薄
           wb = load_workbook(self.filename)
   
           #2.定位到表单
           sheet = wb[self.sheet]  #最新定义的一种用法
   
           all_list=[]  #所有数据列表
           for i in range(1,sheet.max_row+1):  #控制行
               row_list = []  #存储每一行数据的列表
               for j in range(1,sheet.max_column+1): #控制列
                   if type(sheet.cell(row=i,column=j).value) == long or  type(sheet.cell(row=i,column=j).value) ==  float:
                       row_list.append(sheet.cell(row=i,column=j).value)
                   else:
                       #对于非浮点或非long的数据类型，因为返回的是unicode，故需要转换为str
                       row_list.append(sheet.cell(row=i, column=j).value.encode())
               all_list.append(row_list)
           return all_list
   
   if __name__ == '__main__':
       res = DoExcel('test_data.xlsx','Sheet1').do_excel()
       print (res)
   ```
12. 读取excel里所以数据，每一行数据存储到字典中，其中第一行作为字典的key，之后从第二行数据开始，都存储到一个大列表里面
    ```python
    
    from openpyxl import load_workbook
    
    class DoExcel():
        def __init__(self, filename, sheet):
            self.filename = filename
            self.sheet = sheet
    
        def get_title(self):
            # 1.打开工作薄，且是已经存在的工作薄
            wb = load_workbook(self.filename)
    
            # 2.定位到表单
            sheet = wb[self.sheet]  # 最新定义的一种用法
            title_list = []  # 存储表头
            for i in range(1, sheet.max_column+1):
                title_list.append(sheet.cell(row=1, column=i).value)
            return title_list
    
        def do_excel(self):
            # 1.打开工作薄，且是已经存在的工作薄
            wb = load_workbook(self.filename)
    
            # 2.定位到表单
            sheet = wb[self.sheet]  # 最新定义的一种用法
            title_list = self.get_title()
    
            all_list = []  # 所有数据列表
            for i in range(2, sheet.max_row + 1):  # 控制行
                row_dict = {}  # 存储每一行数据的字典
                for j in range(1, sheet.max_column + 1):  # 控制列
                    #if type(sheet.ce,ll(row=i, column=j).value) == int or type(sheet.cell(row=i, column=j).value) == float:
                    #为什么j-1，因为行号和列号是从1开始，但是索引是从0开始
                    row_dict[title_list[j-1]] = sheet.cell(row=i, column=j).value
                    #else:
                        # 对于非浮点或非long的数据类型，因为返回的是unicode，故需要转换为str
                        #row_dict[title_list[j]] = sheet.cell(row=i, column=j).value.encode()
                all_list.append(row_dict)
            return all_list
    
    
if __name__ == '__main__':
   res = DoExcel('http_data.xlsx', 'Sheet1').do_excel()
   print(res)
```