openpyxl 笔记

 

Python-Codebase/simpread-python 处理 excel 完整版 - 简书.md at master · dantefung/Python-Codebase (github.com)

 

hexo_blog_config/Python操作Excel.md at master · p0ny233/hexo_blog_config (github.com)

 

 

for i in range(1,sheet.max_row+1):#这里你看要复制多少行，我习惯用sheet，你可以用ws
    sheet[f'B{i}'].value=sheet[f'G{i}'].value

 

pandas 向已有的excel指定的行和列添加数据_pandas excel 指定行列数据写入_heianduck的博客-CSDN博客

import pandas as pd
import openpyxl
 
df1 = pd.DataFrame(
    {
        "num1": [21,25,56,11],
        "num2": [31,35,36,12],
        "num3": [41,45,46,14],
        
    },
    index=[0, 1, 2,3],
)
 
df1


#用openpyxl打开excel
wb=openpyxl.load_workbook('C:\\Users\\19051\\Desktop\\test1.xlsx')
#打开指定的Sheet
ws = wb['Sheet1']
 
startCol = 3
 
#下面两行的意思是，将df1的每一行转成列表
for i in range(0, df1.shape[0]):
    eachRowList = df1.iloc[i,:].tolist()
 
    #取每个列表里面的值
    for j in range(0,len(eachRowList)):
        #row 代表从几行开始， columns 代表从第几列开始
        #这里是从第6行， 第3列开始插入
        ws.cell(row = i+6, column = startCol+j).value =eachRowList[j]
 
#保存为新的表格
wb.save('C:\\Users\\19051\\Desktop\\test2.xlsx')

 

 

Python的Openpyxl，复制和粘贴单元格范围 - VoidCC

关于python：openpyxl-“复制/粘贴”单元格范围 | 码农家园 (codenong.com)

关于python：使用OpenPyxl复制粘贴列范围 | 码农家园 (codenong.com)

 

 

 

xlsx2bin.py

import numpy as np
from openpyxl import load_workbook


# 1.打开 Excel 表格并获取表格名称
workbook = load_workbook(filename="output0.xlsx")
print(workbook.sheetnames)
# 2.通过 sheet 名称获取表格
sheet = workbook["Sheet1"]
print(sheet)
# 3.获取表格的尺寸大小(几行几列数据) 这里所说的尺寸大小，指的是 excel 表格中的数据有几行几列，针对的是不同的 sheet 而言。
print(sheet.dimensions)


date_bin = np.zeros((160, 4), dtype="uint16")

# 5. 获取一系列格子
# 获取 A1:C2 区域的值
cell = sheet["A1:E160"]
# print(cell)

workbook.save("测试表格.xlsx")

input0_idx_min = float("Inf")
input0_idx_max = float("-Inf")

input1_idx_min = float("Inf")
input1_idx_max = float("-Inf")

output_idx_min = float("Inf")
output_idx_max = float("-Inf")


row = 0
for i in cell:
    col = 0
    for j in i:
        # print(j.value)
        if col != 1:
            if col == 0:
                value = j.value
                date_bin[row, 0] = value
                if value < output_idx_min:
                    output_idx_min = value
                if value > output_idx_max:
                    output_idx_max = value

            elif col == 3:
                value = j.value
                date_bin[row, 1] = value
                if value < input0_idx_min:
                    input0_idx_min = value
                if value > input0_idx_max:
                    input0_idx_max = value

            elif col == 4:
                value = j.value
                date_bin[row, 2] = value
                if value < input1_idx_min:
                    input1_idx_min = value
                if value > input1_idx_max:
                    input1_idx_max = value

                if date_bin[row, 1] == date_bin[row, 2]:
                    date_bin[row, 3] = 1
                else:
                    date_bin[row, 3] = 0
        col = col + 1
    row = row + 1

print("input0_idx max:", input0_idx_max, " min:", input0_idx_min, " max - min:", input0_idx_max - input0_idx_min)
print("input1_idx max:", input1_idx_max, " min:", input1_idx_min, " max - min:", input1_idx_max - input1_idx_min)
print("output_idx max:", output_idx_max, " min:", output_idx_min, " max - min:", output_idx_max - output_idx_min)

print(date_bin.shape)
date_bin.tofile("index0-0.bin")

 

xlsx_split_pandas.py

import os
import pandas as pd
import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active


data_df_out = pd.DataFrame()

data_df = pd.read_excel("grid_proc.xlsx", sheet_name="Sheet1", header=None)
# 下面两行的意思是，将data_df的每一行转成列表
for i in range(0, 10):
    eachRowList = data_df.iloc[i, :].tolist()

    startRow = (i % 800)
    startCol = (i // 800) * 6
    for j in range(0, len(eachRowList)):
        data_df_out.insert(startRow, startCol + j, eachRowList[j], allow_duplicates=True)
        # data_df_out.loc[startCol] = eachRowList[j]

data_df_out.to_excel("out.xlsx", sheet_name="Sheet1", index=False, header=None)
exit

workbook.save("grid_sort.xlsx")
exit

workbook = openpyxl.load_workbook(filename="grid_proc.xlsx")
sheet = workbook.active


for i in range(1, sheet.max_row + 1):
    sheet[f"B{i}"].value = sheet[f"G{i}"].value


startCol = 3

# 下面两行的意思是，将df1的每一行转成列表
for i in range(0, df1.shape[0]):
    eachRowList = df1.iloc[i, :].tolist()

    # 取每个列表里面的值
    for j in range(0, len(eachRowList)):
        # row 代表从几行开始， columns 代表从第几列开始
        # 这里是从第6行， 第3列开始插入
        sheet.cell(row=i + 6, column=startCol + j).value = eachRowList[j]

# 保存为新的表格
workbook.save("C:\\Users\\19051\\Desktop\\test2.xlsx")


workbook = openpyxl.load_workbook(filename="grid_proc.xlsx")
sheet = workbook.active

cell1 = sheet.cell(row=1, column=1)
cell2 = sheet.cell(row=11, column=3)
print(cell1.value, cell2.value)


newbook = openpyxl.Workbook()
nsh = newbook.active

FileNames = os.listdir(path)
for fn in FileNames:
    fullfilename = os.path.join(path, fn)
    print(fullfilename)
    book = openpyxl.load_workbook("output0.xlsx")
    sh = book.active
    cell = sh["A1:B10"]
    print(cell)

    for item in sh["A1":"C10"]:  # item表示每一行的单元格元组
        for cell in item:  # cell表示每一行的每一个单元格对象
            print(cell, cell.value)  # 打印出每个单元格对象

    for celL_tmp in sh["A1":"C10"]:
        print(celL_tmp)

    arr = [sh[n].value for n in cell]
    book.close()
    print(arr)
    nsh.append(arr)

newbook.save(r"E:\数据汇总文件.xlsx")
print("读取完成！！！")

import os
import pandas as pd
import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

data_df = pd.read_excel("grid_proc.xlsx", sheet_name="Sheet1", header=None)
# 下面两行的意思是，将data_df的每一行转成列表
for i in range(0, data_df.shape[0]):
    eachRowList = data_df.iloc[i, :].tolist()

    startRow = (i % 800) + 1
    startCol = (i // 800) * 6 + 1
    for j in range(0, len(eachRowList)):
        # row 代表从几行开始， columns 代表从第几列开始
        # 这里是从第6行， 第3列开始插入
        sheet.cell(row=startRow, column=startCol + j).value = eachRowList[j]

workbook.save("grid_sort.xlsx")
exit

workbook = openpyxl.load_workbook(filename="grid_proc.xlsx")
sheet = workbook.active


for i in range(1, sheet.max_row + 1):
    sheet[f"B{i}"].value = sheet[f"G{i}"].value


startCol = 3

# 下面两行的意思是，将df1的每一行转成列表
for i in range(0, df1.shape[0]):
    eachRowList = df1.iloc[i, :].tolist()

    # 取每个列表里面的值
    for j in range(0, len(eachRowList)):
        # row 代表从几行开始， columns 代表从第几列开始
        # 这里是从第6行， 第3列开始插入
        sheet.cell(row=i + 6, column=startCol + j).value = eachRowList[j]

# 保存为新的表格
workbook.save("C:\\Users\\19051\\Desktop\\test2.xlsx")


workbook = openpyxl.load_workbook(filename="grid_proc.xlsx")
sheet = workbook.active

cell1 = sheet.cell(row=1, column=1)
cell2 = sheet.cell(row=11, column=3)
print(cell1.value, cell2.value)


newbook = openpyxl.Workbook()
nsh = newbook.active

FileNames = os.listdir(path)
for fn in FileNames:
    fullfilename = os.path.join(path, fn)
    print(fullfilename)
    book = openpyxl.load_workbook("output0.xlsx")
    sh = book.active
    cell = sh["A1:B10"]
    print(cell)

    for item in sh["A1":"C10"]:  # item表示每一行的单元格元组
        for cell in item:  # cell表示每一行的每一个单元格对象
            print(cell, cell.value)  # 打印出每个单元格对象

    for celL_tmp in sh["A1":"C10"]:
        print(celL_tmp)

    arr = [sh[n].value for n in cell]
    book.close()
    print(arr)
    nsh.append(arr)

newbook.save(r"E:\数据汇总文件.xlsx")
print("读取完成！！！")

 

xlsx_split.py

import os
import pandas as pd
import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

data_df = pd.read_excel("grid_proc.xlsx", sheet_name="Sheet1", header=None)
# 下面两行的意思是，将data_df的每一行转成列表
for i in range(0, data_df.shape[0]):
    eachRowList = data_df.iloc[i, :].tolist()

    startRow = (i % 800) + 1
    startCol = (i // 800) * 6 + 1
    for j in range(0, len(eachRowList)):
        # row 代表从几行开始， columns 代表从第几列开始
        # 这里是从第6行， 第3列开始插入
        sheet.cell(row=startRow, column=startCol + j).value = eachRowList[j]

workbook.save("grid_sort.xlsx")
exit

 

npy2xlsx.py

import numpy as np
import pandas as pd

data = np.load("./test_grid_2/grid_proc.npy")
data_df = pd.DataFrame(data)
print(data_df.shape)

writer = pd.ExcelWriter('grid_proc.xlsx')
data_df.to_excel(writer,'Sheet1',float_format='%.5f', header=False, index=True)
writer.save()
writer.close()

 

npy2bin.py

import numpy as np

grid_input = np.load("./test_grid_2/grid_input.npy")
grid_input.tofile("./test_grid_2/grid_input.bin")

grid_proc = np.load("./test_grid_2/grid_proc.npy")
grid_proc_int16 = grid_proc.astype(np.int16)
grid_proc_int16.tofile("./test_grid_2/grid_proc.bin")