CODEWITHSUNDEEP
pythonexcel
FrankBlack78
FrankBlack78

Reputation: 162

How to split Excel-Data into seperate Worksheets and maintain cell formating with Python 3?

I get a huge Excel-Sheet (normal table with header and data) on a regular basis and I need to filter and delete some data and split the table up into seperate sheets based on some rules. I think I can save me some time if I use Python for that tedious task because the filtering, deleting and splitting up into several sheets is based on always the same rules that can logically be defined.

Unfortunately the sheet and the data is partially color-coded (cells and font) and I need to maintain this formating for the resulting sheets. Is there a way of doing that with python? I think I need a pointer in the right direction. I only found workarounds with pandas but that does not allow me to keep the formatting.

Upvotes: 0

Views: 370

Answers (1)

Abhyuday Vaish
Abhyuday Vaish

Reputation: 2379

You can take a look at an excellent Python library for Excel called openpyxl.

Here's how you can use it.

First, install it through your command prompt using:

pip install openpyxl

Open an existing file:

import openpyxl
 
wb_obj = openpyxl.load_workbook(path) # Open notebook

Deleting rows:

import openpyxl
from openpyxl import load_workbook

wb = load_wordbook(path)
ws = wb.active
ws.delete_rows(7)

Inserting rows:

import openpyxl
from openpyxl import load_workbook

wb = load_wordbook(path)
ws = wb.active
ws.insert_rows(7)

Here are some tutorials that you can take a look at:

Tutorial 1

Youtube Video

Upvotes: 1

Related Questions