엑셀에 있는 데이터들을 해당 모델 인스턴스를 생성해서 저장하는 api와 특정 기간에 해당하는 인스턴스들을 불러와 그 데이터들을 다시 엑셀에 저장하는 api를 만들어보았다. django에서 excel에 접근하기 위해서 pandas라는 python 라이브러리를 사용했다.
Pandas
python dataframe인 pandas를 사용하여 엑셀을 읽고 써볼 것이다. 우선 pandas를 설치해준다
pip install pandas파이썬 Pandas로 Excel 파일 읽기
import pandas as pd
filename = '엑셀파일 이름 넣기'
df_excel = pd.read_excel(filename)
print(df_excel)
# 엑셀에 있는 데이터들이 출력위에서는 read_excel에 파일명만 넣었었는데, 빈번하게 사용될만한 옵션들을 넣어봤다.
pandas.read_excel(filename,
sheet_name = '서울',
header = None,
names = ['일시','평균','최저','최고'],
index_col = None,
usecols = "C:F",
dtype = {'일시':str, '평균':float, '최저':float, '최고':float},
skiprows = 32,
nrows = 28,
na_values = 'nan',
thousands = ',') - sheet_name : 기본 값은 0 이다. int 0이 첫번째 시트, 1이 두번째 시트이다. 문자열로 sheet_name = "sheet1" 방법이나, [0,"sheet2"] 처럼 리스트 값이 들어갈 수 있다.
- header : 어느 행(row)에 열(column)의 이름이 있는지를 지정하는 것으로 기본 값은 0으로 첫번째 줄이다. None으로 하면 첫번째 줄(행,row)부터 바로 데이터로 받아드린다.
- names : header가 None일 경우 열(column)의 이름을 지정해준다.
- index_col : 각 행(row)의 이름이 위치한 열(column)을 지정하며 기본 값은 None이다.
- usecols : 기본 값은 None으로 모든 열을 다 불러온다. "A:E", "A,C,F:H" 등으로 원하는 열을 선택하여 읽어오게 할 수 있다.
- dtype : 각 열의 데이터 타입(type)을 지정할 수 있다.
- skiprows : 엑셀을 읽을 때 첫줄(0)으로 부터 몇 줄을 건너뛸지 지정한다.
- nrow : 몇 줄을 읽을지 지정한다.
- na_values : 값이 없는 경우 어떤 str 등으로 넣을지 지정한다.
- thousands : 돈과 같이 천단위로 쉼표(,)로 구분된 문자를 변환하기 위하여, 천단위의 구분자가 무엇인지 지정한다.
파이썬 Pandas로 Excel 파일 쓰기
import pandas as pd
df = pd.DataFrame([[11, 21, 31], [12, 22, 32], [31, 32, 33]],
index=['one', 'two', 'three'], columns=['a', 'b', 'c'])
print(df)
# a b c
# one 11 21 31
# two 12 22 32
# three 31 32 33
# 엑셀 파일 출력
df.to_excel('경로및 파일명/파일명만 적으면 현재 디렉토리내에 생성')to_excel() 함수의 옵션을 몇개 살펴보면 다음과 같다.
- sheet_name : 시트 이름을 지정한다.
- na_rep : 기본 값은 '' 이다. 비어있는 값을 'NaN' 등으로 설정할 수 있다.
- na_rep : 예를 들어 float_format = "%.2f"로 한다면, 0.1234가 0.12로 표기된다.
- header : 기본 값은 True로 데이터프레임의 열(column)의 이름을 기본적으로 쓰게 된다. False값은 열의 이름을 쓰지 않는다. ['a','b','c']과 같이 문자열이 있는 리스트를 넣어서 열의 이름을 바꿀 수 있다.
- index : 기본 값은 True로, 각 행(row), 즉 첫줄부터 0으로 시작하여 숫자가 매겨진다.
- startrow : 기본 값은 0, 시작 행 지정
- startcol : 기본 값은 0, 시작 열 지정
엑셀에서 데이터들을 업로드하는 api
우선 엑셀의 생김새는 다음과 같다.
15분 단위로 데이터들이 날짜별로 있다. 이 데이터들을 이제 모델 인스턴스를 만들어 주어서 넣어주어야 한다. Data라는 모델에는 timestamp와 위에 value 값들이 들어가면 된다.
@api_view(('POST',))
def uploadExcelDataToTargetTag(request):
try:
# 포스트요청으로 들어온 엑셀파일을 받아온다.
excel_file = request.FILES['excelFile']
filename, fileExtension = os.path.splitext(str(excel_file))
# 파일확장자를 통해 엑셀파일이 맞는지 체크해준다
if fileExtension != ".xlsx":
return Response("Unavailable file extension")
else:
df = pd.read_excel(excel_file, usecols='C:CU')
time_array = pd.read_excel(excel_file, usecols='D:CU').columns.to_numpy()
bulk_list = []
for row in df.itertuples():
date = row[1].replace('.', '-') # timestamp 포멧 변경
values = row[2:] # 00:15 ~ 24:00 까지의 values
timestamp = ""
for i, d in enumerate(list(values)):
if isinstance(d, (int, float)):
timestamp = f"{date} {time_array[i]}"
bulk_list.append(Data(
timestamp=timestamp,
value=d
))
Data.objects.bulk_create(bulk_list, batch_size=250)
return Response("Data Uploaded")
except Exception as e:
print(e)
return Response('Data Upload Failed')데이터들을 엑셀에 저장하는 api
입력으로 기간이 오고 출력으로는 기간에 해당하는 데이터들을 엑셀파일에 저장해주는 api를 만들어줄것이다. 우선은 이차배열에다가 데이터들을 저장해준다음 마지막에 한꺼번에 엑셀파일들을 생성해줄것이다.
@api_view(('POST',))
def downloadExcelData(request):
start_date = request.data['start_date'] # 시작날짜
end_date = request.data['end_date'] # 끝난 날짜
datas = Data.objects.filter(timestamp__range=[start_date, end_date])
columns = ['고객번호', '계기번호', '날짜', '00:00', '00:15', '00:30', '00:45', '01:00', '01:15', '01:30', '01:45', '02:00',
'02:15', '02:30', '02:45', '03:00', '03:15', '03:30', '03:45', '04:00', '04:15', '04:30', '04:45',
'05:00', '05:15', '05:30', '05:45', '06:00', '06:15', '06:30', '06:45', '07:00', '07:15', '07:30',
'07:45', '08:00', '08:15', '08:30', '08:45', '09:00', '09:15', '09:30', '09:45', '10:00', '10:15',
'10:30', '10:45', '11:00', '11:15', '11:30', '11:45', '11:00', '12:00', '12:15', '12:30', '12:45',
'13:00', '13:15', '13:30', '13:45', '14:00', '14:15', '14:30', '14:45', '15:00', '15:15', '15:30',
'15:45', '16:00', '16:15', '16:30', '16:45', '17:00', '17:15', '17:30', '17:45', '18:00', '18:15',
'18:30', '18:45', '19:00', '19:15', '19:30', '19:45', '20:00', '20:15', '20:30', '20:45', '21:00',
'21:15', '21:30', '21:45', '22:00', '22:15', '22:30', '22:45', '23:00', '23:15', '23:30', '23:45',
'합계(kWh)'] # 칼럼 우선 생성해주기,,,노가다,,,ㅎㅎ,,,
dates = [] # 인스턴스에서 날짜만 저장해주기
for r in raw_datas:
date, hour = str(r.timestamp).split(' ') # 타임스탬프에 날짜와 시간이 모두 있기 때문에 나눠주기
if date in dates: #중복이면 패스
pass
else:
dates.append(date)
excel_data = [[" " for j in range(len(columns))] for i in range(len(dates))]
for i in len(dates):
excel_data[i][2] = dates[i] # 날짜만 우선 넣어주기
for data in datas:
date, hour = str(raw_data.timestamp).split(' ')
hour = hour[:5] # 시간만 추출해주기
for j, column in enumerate(columns): 해당 시간에 데이터 저장해주기
if hour == column:
excel_data[dates.index(date)][j] = data.value
result = 0
for x in range(len(excel_data)):
for y in range(3, len(excel_data[x])):
if excel_data[x][y] != ' ':
result += excel_data[x][y]
excel_data[x][100] = result # 00:00~23:45분까지 데이터들의 합 저장해주기
df = pd.DataFrame(data, columns=columns)
df.to_excel('sample.xlsx', index=False)
return Response("Data Download")
{ "start_date":"2020-09-24", "end_date":"2020-09-25" }
위처럼 이제 해당 api로 요청을 보내주면 아래와 같이 엑셀이 만들어지면서 저장이 된다.
