devbox
Monodyee
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

python 使用pandas或xlrd、xlwt实现对Excel的读取、添加、追加、删除、重复值处理、缺失值处理、获取行数、列数、获取一行、一列数据等一系列封装_python excel插入列

作者：Monodyee | 2024-02-08 15:12:48

赞

踩

python excel插入列

不说了，又是造轮子的一天。在此我要严重批评CSDN或百度一堆浑水摸鱼的，某些人明明代码明显报错也来上传发博客，要么就是标题党，代码没报错但压根就不是实现那个功能的，简直是浪费时间。

废话不多说直接贴代码：

先新建一个data文件夹，文件路径我填的是 ./data/

使用xlrd、xlwt：

  1. import xlrd
  2. import xlwt
  3. import xlutils.copy
  4.  
  5.  
  6. class XLSX:
  7.     __workbook = xlwt.Workbook(encoding='utf-8')
  8.     __sheet = __workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
  9.  
  10.     # 保存
  11.     def save(self, name, head_list, data_list):
  12.         path = './data/' + name + '.xlsx'
  13.         head = head_list
  14.         data = data_list
  15.         __row = 0
  16.         __col = 0
  17.         for head in head:
  18.             self.__sheet.write(__row, __col, head)
  19.             __col = __col + 1
  20.         for i in range(0, len(data)):
  21.             for j in range(1, len(data[i]) + 1):
  22.                 self.__sheet.write(j, i, data[i][j - 1])
  23.         self.__workbook.save(path)
  24.  
  25.     # 在原来的基础上增加 n 列，包含表头
  26.     @staticmethod
  27.     def save_append_col(name, data_list, head):
  28.         path = './data/' + name + '.xlsx'
  29.         file = xlrd.open_workbook(path)
  30.         table = file.sheet_by_index(0)
  31.         col_len = table.ncols
  32.         ws = xlutils.copy.copy(file)
  33.         ws_table = ws.get_sheet(0)
  34.         ws_table.write(0, col_len, head)
  35.         for i in range(1, len(data_list) + 1):
  36.             ws_table.write(i, col_len, data_list[i - 1])
  37.         ws.save(path)
  38.  
  39.     # 在原来的基础上增加 n 列，包含表头,如果存在就在该列下面继续添加
  40.     @staticmethod
  41.     def save_append_col_append(name, data_list, head, line_index):
  42.         path = './data/' + name + '.xlsx'
  43.         file = xlrd.open_workbook(path)
  44.         table = file.sheet_by_index(0)
  45.         cols = table.row_values(0)
  46.         if head in cols:
  47.             col_len = cols.index(head)
  48.             ws = xlutils.copy.copy(file)
  49.             ws_table = ws.get_sheet(0)
  50.             for i in range(0, len(data_list)):
  51.                 ws_table.write(line_index + i - 1, col_len, data_list[i])
  52.             ws.save(path)
  53.         else:
  54.             col_len = table.ncols
  55.             ws = xlutils.copy.copy(file)
  56.             ws_table = ws.get_sheet(0)
  57.             ws_table.write(0, col_len, head)
  58.             for i in range(1, len(data_list) + 1):
  59.                 ws_table.write(i, col_len, data_list[i - 1])
  60.             ws.save(path)
  61.  
  62.     # 在原来的基础上增加 n 行,这里是按照每一行的方式追加
  63.     @staticmethod
  64.     def save_append_row(name, data_list):
  65.         path = './data/' + name + '.xlsx'
  66.         file = xlrd.open_workbook(path)
  67.         table = file.sheet_by_index(0)
  68.         row_len = table.nrows
  69.         col_len = table.ncols
  70.         ws = xlutils.copy.copy(file)
  71.         ws_table = ws.get_sheet(0)
  72.         for i in range(0, len(data_list)):
  73.             for j in range(0, col_len):
  74.                 ws_table.write(row_len + i, j, data_list[i][j])
  75.         ws.save(path)
  76.  
  77.     # 获取文件所有数据
  78.     @staticmethod
  79.     def get_all_line(file_name):
  80.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  81.         table = file.sheets()[0]
  82.         a = []
  83.         for i in range(1, table.nrows):
  84.             a.append(table.row_values(i))
  85.         return a
  86.  
  87.     # 获取文件所有数据，以行的方式
  88.     @staticmethod
  89.     def get_all_line_col(file_name):
  90.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  91.         sheet = file.sheet_by_index(0)  # 获取第一个表格
  92.         result = []
  93.         rows = sheet.nrows
  94.         for row_idx in range(rows):
  95.             row_values = sheet.row_values(row_idx)
  96.             result.append(row_values)
  97.         return result
  98.  
  99.     # 获取文件指定一列的所有数据
  100.     @staticmethod
  101.     def get_all_col(file_name, col_index):
  102.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  103.         table = file.sheets()[0]
  104.         a = []
  105.         for i in range(1, table.nrows):
  106.             a.append(table.row_values(i)[col_index])
  107.         return a
  108.  
  109.     # 获取文件指定一行的所有数据
  110.     @staticmethod
  111.     def get_one_line(file_name, line_count):
  112.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  113.         t1 = file.sheets()[0]
  114.         return t1.row_values(line_count)
  115.  
  116.     # 获取文件指定一行的指定一列的数据
  117.     @staticmethod
  118.     def get_one_line_col(file_name, line_count, col_count):
  119.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  120.         t1 = file.sheets()[0]
  121.         return t1.row_values(line_count)[col_count]
  122.  
  123.     # 获取文件总行数
  124.     @staticmethod
  125.     def get_row_length(file_name):
  126.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  127.         table = file.sheet_by_index(0)
  128.         return table.nrows
  129.  
  130.     # 获取文件指定一列的长度，不包含空值
  131.     @staticmethod
  132.     def get_one_col_length(file_name, col_index):
  133.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  134.         table = file.sheets()[0]
  135.         col_len = 0
  136.         for row_idx in range(table.nrows):
  137.             cell_value = table.cell_value(row_idx, col_index)
  138.             if cell_value != '':
  139.                 col_len += 1
  140.         return col_len
  141.  
  142.     # 获取总列数
  143.     @staticmethod
  144.     def get_col_length(file_name):
  145.         file = xlrd.open_workbook('./data/' + file_name + '.xlsx')
  146.         table = file.sheet_by_index(0)
  147.         return table.ncols
  148.  
  149.     # 获取重复列表的下标，假如第1/6/8行相同，5/9行相同，则返回[[1,6,8],[5,9]]
  150.     def get_repeat_list(self, file_name, col_index):
  151.         return self.find_duplicates(self.get_all_col(file_name, col_index))
  152.  
  153.     # 删除行,[行数1，[行数2]]
  154.     @staticmethod
  155.     def delete_line(file_name, delete_rows):
  156.         path = './data/' + file_name + '.xlsx'
  157.         workbook = xlrd.open_workbook(path)
  158.         sheet = workbook.sheet_by_index(0)
  159.         rows_num = sheet.nrows
  160.         rows_data = []
  161.         for r in range(rows_num):
  162.             if r not in delete_rows:
  163.                 rows_data.append(sheet.row_values(r))
  164.         book = xlwt.Workbook()
  165.         sheet1 = book.add_sheet('Sheet1', cell_overwrite_ok=True)
  166.         # 将未删除行数据写入sheet
  167.         for r, row in enumerate(rows_data):
  168.             for c, val in enumerate(row):
  169.                 sheet1.write(r, c, val)
  170.         book.save(path)
  171.  
  172.     # 对所有列所有行进行缺失值处理,使用“缺失补充”来替代
  173.     @staticmethod
  174.     def deletion(file_name):
  175.         path = './data/' + file_name + '.xlsx'
  176.         workbook = xlrd.open_workbook(path)
  177.         sheet = workbook.sheet_by_index(0)
  178.         rows_num = sheet.nrows
  179.         cols_num = sheet.ncols
  180.         data = []
  181.         for r in range(rows_num):
  182.             row = []
  183.             for c in range(cols_num):
  184.                 value = sheet.cell_value(r, c)
  185.                 if value == '':  # 如果数据为空,认为是缺失值
  186.                     value = '缺失补充'  # 替换为'缺失'字符串
  187.                 row.append(value)
  188.             data.append(row)
  189.         book = xlwt.Workbook()
  190.         sheet1 = book.add_sheet('Sheet1', cell_overwrite_ok=True)
  191.         for r in range(rows_num):
  192.             for c in range(cols_num):
  193.                 sheet1.write(r, c, data[r][c])
  194.         book.save(path)
  195.  
  196.     @staticmethod
  197.     def find_duplicates(strs):
  198.         result = []
  199.         strs_dic = {}
  200.         for index, s in enumerate(strs):
  201.             if s not in strs_dic:
  202.                 strs_dic[s] = [index]
  203.             else:
  204.                 strs_dic[s].append(index)
  205.         for key in strs_dic:
  206.             if len(strs_dic[key]) > 1:
  207.                 result.append(strs_dic[key])
  208.         return result
  209.  
  210.  
  211. # 测试
  212. # xx = XLSX()
  213. # xx.save('ddd', ['表头1', '表头2', '表头3'],
  214. #         [['列1-第1行', '列1-第2行', '列1-第3行'], ['列2-第1行', '列2-第2行', '列2-第3行'],
  215. #          ['列3-第1行', '列3-第2行', '列3-第3行']])
  216. # xx.save_append_row('ddd', [['列1-第4行', '列2-第4行', '列3-第4行'], ['列1-第5行', '列2-第5行', '列3-第5行'],
  217. #                            ['列1-第6行', '列2-第6行', '列3-第6行']])
  218. # xx.save_append_col('ddd', ['列4-第1行', '列4-第2行', '列4-第3行'], '表头4')
  219. # xx.save_append_col_append('ddd', ['列4-第1行', '列4-第2行', '列4-第3行'], '表头4', 5)
  220. # xx.delete_line('ddd', [5, 6])
  221. # xx.deletion('ddd')
  222. # print(xx.get_all_line('ddd'))
  223. # print(xx.get_one_line('ddd', 2))
  224. # print(xx.get_all_col('ddd', 3))
  225. # print(xx.get_one_line_col('ddd', 1, 0))
  226. # print(xx.get_row_length('ddd'))
  227. # print(xx.get_col_length('ddd'))
  228. # print(ee.get_repeat_list('ddd',1))
  229. # print(xx.get_one_col_length('ddd', 3))

使用pandas：

  1. import os
  2. import numpy as np
  3. import pandas as pd
  4.  
  5.  
  6. class EXCEL:
  7.  
  8.     # 保存
  9.     @staticmethod
  10.     def save(name, head_list, data_list):
  11.         path = r'./data/' + name + '.xlsx'
  12.         temp = {}
  13.         for i in range(len(head_list)):
  14.             temp[str(head_list[i])] = data_list[i]
  15.         data = pd.DataFrame(temp)
  16.         data.to_excel(path, index=False)
  17.  
  18.     # 在原来的基础上增加一个sheet表
  19.     @staticmethod
  20.     def append_sheet(name, sheet_name, head_list):
  21.         write = pd.DataFrame(columns=head_list)
  22.         path = r'./data/' + name + '.xlsx'
  23.         if not os.path.exists(path):
  24.             write = pd.ExcelWriter(path, engine='openpyxl')
  25.         with pd.ExcelWriter(path, mode='a', engine='openpyxl') as writer:
  26.             write.to_excel(writer, sheet_name=sheet_name, index=False)
  27.  
  28.     # 在原来的基础上增加 n 行，包含表头,这里是按照每一列的方式追加
  29.     @staticmethod
  30.     def append_row(name, head_list, data_list):
  31.         path = r'./data/' + name + '.xlsx'
  32.         temp = {}
  33.         for i in range(len(head_list)):
  34.             temp[str(head_list[i])] = data_list[i]
  35.         old_data = pd.read_excel(path)
  36.         new_data = pd.DataFrame(temp)
  37.         result = pd.concat([old_data, new_data])
  38.         result.to_excel(path, index=False)
  39.  
  40.     # 在原来的基础上增加 n 列
  41.     @staticmethod
  42.     def append_col(name, head_name, data_list):
  43.         path = r'./data/' + name + '.xlsx'
  44.         df = pd.read_excel(path)
  45.         new_col = pd.Series(data_list)
  46.         new_col.name = head_name
  47.         result = pd.concat([df, new_col], axis=1)
  48.         result.to_excel(path, header=True, index=False)
  49.  
  50.     # 获取文件所有数据,以行的方式
  51.     @staticmethod
  52.     def get_all_line(file_name):
  53.         path = r'./data/' + file_name + '.xlsx'
  54.         df = pd.read_excel(path)
  55.         return df.values.tolist()
  56.  
  57.     # 获取文件所有数据，以列的方式
  58.     @staticmethod
  59.     def get_all_line_col(file_name):
  60.         path = r'./data/' + file_name + '.xlsx'
  61.         df = pd.read_excel(path)
  62.         cols = df.columns.tolist()
  63.         result = []
  64.         for col in cols:
  65.             result.append(df[col].values.tolist())
  66.         return result
  67.  
  68.     # 获取文件指定一列的所有数据
  69.     @staticmethod
  70.     def get_all_col(file_name, col_index):
  71.         path = r'./data/' + file_name + '.xlsx'
  72.         df = pd.read_excel(path)
  73.         col = df.iloc[:, col_index]
  74.         return col.values.tolist()
  75.  
  76.     # 获取文件指定一行的所有数据
  77.     @staticmethod
  78.     def get_one_line(file_name, line_count):
  79.         path = r'./data/' + file_name + '.xlsx'
  80.         df = pd.read_excel(path)
  81.         row = df.iloc[line_count]
  82.         return row.values.tolist()
  83.  
  84.     # 获取文件指定一行的指定一列的数据
  85.     @staticmethod
  86.     def get_one_line_col(file_name, line_count, col_count):
  87.         path = r'./data/' + file_name + '.xlsx'
  88.         df = pd.read_excel(path)
  89.         row = df.iloc[line_count]
  90.         col = row[col_count]
  91.         if isinstance(col, np.float64):
  92.             col = pd.Series(col)
  93.         return col
  94.  
  95.     # 获取文件总行数
  96.     @staticmethod
  97.     def get_row_length(file_name):
  98.         path = r'./data/' + file_name + '.xlsx'
  99.         df = pd.read_excel(path)
  100.         total_rows = df.shape[0]
  101.         return total_rows
  102.  
  103.     # 获取文件总列数
  104.     @staticmethod
  105.     def get_col_length(file_name):
  106.         path = r'./data/' + file_name + '.xlsx'
  107.         df = pd.read_excel(path)
  108.         total_cols = df.shape[1]
  109.         return total_cols
  110.  
  111.     # 删除行,[行数1，[行数2]]
  112.     @staticmethod
  113.     def delete_line(file_name, delete_rows):
  114.         path = r'./data/' + file_name + '.xlsx'
  115.         df = pd.read_excel(path)
  116.         df.drop(df.index[delete_rows], inplace=True)
  117.         df.to_excel(path, index=False)
  118.  
  119.     # 获取重复列表的下标，假如第1/6/8行相同，5/9行相同，则返回[[1,6,8],[5,9]]
  120.     def get_repeat_list(self, file_name, col_index):
  121.         return self.find_duplicates(self.get_all_col(file_name, col_index))
  122.  
  123.     # 对指定列 进行缺失值处理
  124.     @staticmethod
  125.     def deletion(file_name, col_head_name):
  126.         path = r'./data/' + file_name + '.xlsx'
  127.         df = pd.read_excel(path)
  128.         # num_col_type = df[col_head_name].dtype
  129.         # if num_col_type == 'int64' or num_col_type == 'float64':
  130.         #     df[col_head_name] = df[col_head_name].fillna(df[col_head_name].median())
  131.         # else:
  132.         #     df[col_head_name] = df[col_head_name].fillna('缺失')
  133.         # 不使用np.nan，也不判断类型了，统一用 “缺失补充”来替代
  134.         df[col_head_name] = df[col_head_name].fillna('缺失补充')
  135.         df.to_excel(path, index=False)
  136.  
  137.     @staticmethod
  138.     def find_duplicates(strs):
  139.         result = []
  140.         strs_dic = {}
  141.         for index, s in enumerate(strs):
  142.             if s not in strs_dic:
  143.                 strs_dic[s] = [index]
  144.             else:
  145.                 strs_dic[s].append(index)
  146.         for key in strs_dic:
  147.             if len(strs_dic[key]) > 1:
  148.                 result.append(strs_dic[key])
  149.         return result
  150.  
  151.  
  152. # 测试
  153. # ee = EXCEL()
  154. # ee.save('ggg', ['表头1', '表头2', '表头3'],
  155. #         [['列1-第1行', '列1-第2行', '列1-第3行'], ['列2-第1行', '列2-第2行', '列2-第3行'],
  156. #          ['列3-第1行', '列3-第2行', '列3-第3行']])
  157. #
  158. # ee.append_sheet('ggg', 'Sheet2', ['表头1', '表头2', '表头3'])
  159. #
  160. # ee.append_row('ggg', ['表头1', '表头2', '表头3'],
  161. #               [['列1-第1行', '列1-第2行'], ['列2-第1行', '列2-第2行'], ['列3-第1行', '列3-第2行']])
  162. #
  163. # ee.append_col('ggg', '表头4', ['列4-第1行', '列4-第2行', '列4-第3行'])
  164. # ee.delete_line('ggg', [2, 3])
  165. # ee.deletion('ggg', '表头4')
  166. #
  167. # print(ee.get_all_line('ggg'))
  168. # print(ee.get_one_line('ggg', 2))
  169. # print(ee.get_all_col('ggg', 3))
  170. # print(ee.get_one_line_col('ggg', 1, 0))
  171. # print(ee.get_row_length('ggg'))
  172. # print(ee.get_col_length('ggg'))
  173. # print(ee.get_repeat_list('ggg',1))
  174. # ee.repeat_count('ggg')

选一个就行了，不要把两份不同的写法混在一起用，两份写法有些是不一样的

最后我想说：

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/69578
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号