Блог

У меня есть требование записать более 200 тыс. записей в существующий файл Excel (xlsx). В файле Excel есть несколько обозначенных строк заголовка, и мне нужно написать после этого. Иногда мне может потребоваться добавить строки в существующий файл Excel, в котором уже есть несколько строк данных.

Данные существуют в локальной базе данных sqlite, я запускаю sql-запрос для извлечения данных (я использую функции плотного ранжирования и разделения в SQL для упрощения обработки).

В настоящее время я использую openpyxl. Я перебираю каждую строку из курсора sql и записываю данные в каждую ячейку в Excel. Я сохраняю книгу после записи каждых 3000 строк, чтобы не возникало проблем с памятью. Он работает нормально, но занимает около 5 часов. Я пробовал win32com, и это заняло еще больше времени.

Как я могу повысить производительность этого процесса записи данных?

Мой текущий пример кода приведен ниже.

     al_row = al_headerRow
    
    for sql_row in cursor.execute(ALSheet_SQL_Select):
        al_row = al_row   1
        al_col = 0

        #Loop thru all the fields in the sheet
        #Seq in AL Sheet and Mapping sheet should match
        for al_field in dictMappingDoc[ALSheet]:
            al_col = al_col   1
            #SQL Cursor output will be retrieved like a list, first element index is 0, hence excel col 1 will be SQL list index 0.
            al_col_sql = al_col - 1
            
            ws.cell(row=al_row,column=al_col).value = sql_row[al_col_sql]
        #End - for - al_field in dictMappingDoc[ALSheet]

        #Interim save to clear memory
        save_counter = save_counter   1
        if save_counter == 3000:
            wb.save(filename = TempFilePath)
            sleep(5)
            save_counter = 0
        #End - if
    #End - for - SQL Row
    
#---- Final Save -----
wb.save(filename = TempFilePath)
sleep(10)
wb.close()
  

Как предложил Venkataraman R, используйте pandas . Я работаю с более чем 1,3 млн. записей в файле Excel, и для анализа требуется всего несколько секунд.

1 / прочитайте существующий файл Excel в dataframe

2 / Используйте цикл курсора sql для обновления фрейма данных, используя loc, iloc или любой другой, который вам подходит: df.loc[df['column_name'] == some_value_you got_from_sql] всякий раз, когда вам нужно добавить новую строку, просто добавьте ее во временный фрейм данных, который вы добавите в конце

3 / напишите свой файл Excel