Python读取Excel文件中指定的列数并生成CSV文件

0x00 安装Pandas和OpenPyXL

pip install pandaspip install openpyxl

---

0x01 Encoding

import os
import pandas as pd
import openpyxl
from openpyxl import load_workbook
import csvdef write_dict_to_csv(dict_data, fileName):df = pd.DataFrame.from_dict(dict_data)# 将DataFrame写入CSV文件df.to_csv(fileName, index=False)def writeCSV(dict_data, fileName):# 打开CSV文件with open(fileName, 'w', newline='') as file:writer = csv.writer(file)# writer.writerow(["key", "value"])print("====================================")# 工况点字典msgPoints_dict = {}msgPoints_num = 51# 遍历字典for key, value in dict_data.items():print(value)# writer.writerow(value)if isinstance(value, list):for item in value:# 若表格数据为空则过滤掉if isinstance(item, str) and item.startswith('EP') and item is not None:msgPoints_dict[msgPoints_num] = value# writer.writerow(value)msgPoints_num = msgPoints_num + 1print("msgPoints_dict_size: " + str(len(msgPoints_dict)))for key, value in msgPoints_dict.items():print(key, value, end="\n")# 检查键值是否以"EP"开头if isinstance(value, list) and value and isinstance(value[2], str) and value[2].startswith("EP"):# print(value)tmp_list = [str(key)]tmp_list.extend(value)# 写入CSV文件# writer.writerow([key, item])writer.writerow(tmp_list)passdef main():print("=== Read Excel ===")fileName = './doc/data.xlsx'# 字典类型dict_data = {}if os.path.exists(fileName):# 当前活跃的Sheel页wb = load_workbook(fileName)print(wb.active)shell = wb.active# print(ws['B7'].value)# print(ws.cell(7, 2).value)# rows = ws.rows# print(rows)columns_to_print = ['B', 'F', 'O']index = 1for row in shell.iter_rows():tmp_list = []for col_idx, cell in enumerate(row, start=1):column_letter = openpyxl.utils.get_column_letter(col_idx)if column_letter in columns_to_print:tmp_list.append(cell.value)# print(cell.value)dict_data[index] = tmp_listindex = index + 1writeCSV(dict_data, "output.csv")# write_dict_to_csv(dict_data, "msg_points.csv")passif __name__ == "__main__":main()