0

I have a Excel workbook with multiple sheets. Some sheets contain screen shots and some sheets contain data. Some sheets contain both. Call this workbook Workbook A. I need to programatically create a copy of the workbook after refreshing the data in the sheets and retain all the screenshots. Call this workbook Workbook B. The data is refreshed after connecting to a database and executing a SQL. This is how I am doing it currently

Step 1 - Make a copy of Workbook A and call the copy Workbook B.

Step 2 - For each sheet in Workbook A which has data, 

      - execute the SQL and create a dataframe from the SQL results.

      - delete the sheet from Workbook B

      - Create a new sheet with the same name in Workbook B from the dataframe (using df.to_excel())

This works fine but the only problem is that the new sheets added to the Workbook B gets added at the end of the workbook as the last sheets. This changes the order of the sheets compared to Workbook A. I need to retain the same order of the sheets in Workbook B as in Workbook A.

I have also tried to do this using xlrd, openpyxl and pandas but I am not able to get the same order of the sheets as the original workbook. If use pandas, convert all sheets to dataframes and then recreate the workbook from the dataframe in order, I can get the same order but I lose all the screenshots.

Any help to achieve this will be much appreciated.

Suresh Subramaniam
  • 47
  • 2
  • 8
  • Can't you just reorder the sheets? See [Move a worksheet in a workbook using openpyxl or xl* or xlsxwriter?](https://stackoverflow.com/questions/51082458/move-a-worksheet-in-a-workbook-using-openpyxl-or-xl-or-xlsxwriter) – GordonAitchJay Mar 12 '20 at 15:45
  • Ah! I didn't know openpyxl could do this. Will try it out. Thanks! – Suresh Subramaniam Mar 13 '20 at 04:04

0 Answers0