다음 입력 파일 ( "ToSplit2.xlsx")이 제공됩니다.
+-----------------+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Section One | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 1 | 100 | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 2 | 100 | 200 | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 3 | 100 | 200 | 300 | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 4 | 100 | 200 | 300 | 400 | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 5 | 100 | 200 | 300 | 400 | 500 | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 6 | 100 | 200 | 300 | 400 | 500 | 600 | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 7 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 8 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 9 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 10 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| | | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Section Two | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 1 | 100 | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 2 | 100 | 200 | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 3 | 100 | 200 | 300 | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 4 | 100 | 200 | 300 | 400 | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 5 | 100 | 200 | 300 | 400 | 500 | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 6 | 100 | 200 | 300 | 400 | 500 | 600 | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 7 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 8 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 9 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 10 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| | | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Section Three | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 1 | 100 | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 2 | 100 | 200 | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 3 | 100 | 200 | 300 | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 4 | 100 | 200 | 300 | 400 | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 5 | 100 | 200 | 300 | 400 | 500 | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 6 | 100 | 200 | 300 | 400 | 500 | 600 | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 7 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 8 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 9 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 10 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
그리고 다음 Python 코드 :
import pandas as pd
import numpy as np
spreadsheetPath = "ToSplit2.xlsx"
xls = pd.ExcelFile(spreadsheetPath)
# Iterate through worksheets in opened Excel file
for sheet in xls.sheet_names:
# Create a Pandas dataframe from the Excel worksheet (with no headers)
excel_data_df = pd.read_excel(
spreadsheetPath, sheet_name=sheet, header=None)
# Return a list of dataframe index values where entire row is blank
indexList = excel_data_df[excel_data_df.isnull().all(1)].index.tolist()
# Prints [11, 23]
print(indexList)
# Initiate a dictionary
dataframeDictionary = {}
# For every index value in the list
for index in indexList:
# Split and add the result to the dictionary of Panda's dataframes
dataframeDictionary = np.array_split(excel_data_df, index)
# For every pandas dataframe in the dataframe dictionary
for dataframe in dataframeDictionary:
# Write the pandas dataframe to Excel with a worksheet name equal to dataframe address 0,0
dataframe.to_excel("output.xlsx",sheet_name=str(dataframe.iloc[0][0]))
빈 행을 기반으로 Excel 워크 시트를 여러 스프레드 시트로 분할하려고합니다. 예 :
섹션 1 : (섹션 2 및 섹션 3 워크 시트도 있습니다.)
+-----------------+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Section One | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 1 | 100 | | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 2 | 100 | 200 | | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 3 | 100 | 200 | 300 | | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 4 | 100 | 200 | 300 | 400 | | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 5 | 100 | 200 | 300 | 400 | 500 | | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 6 | 100 | 200 | 300 | 400 | 500 | 600 | | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 7 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 8 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 9 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| Label 10 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
나는 내가 정말로 가깝다고 생각하지만 데이터 프레임 분할에서 미끄러지는 것 같습니다.
파일 이름에 따라 변경하십시오.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# Read excel file
df = pd.read_excel('ToSplit2.xlsx', skip_blank_lines=False, header=None)
# Split by blank rows
df_list = np.split(df, df[df.isnull().all(1)].index)
# Create new excel to write the dataframes
writer = pd.ExcelWriter('Excel_one.xlsx', engine='xlsxwriter')
for i in range(1, len(df_list) + 1):
df_list[i - 1] = df_list[i - 1].dropna(how='all')
df_list[i - 1].to_excel(writer, sheet_name='Sheet{}'.format(i), header=None, index=False)
# Save the excel file
writer.save()
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다