Delete column from xls file [closed]
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 6 years ago .
Using Python, I need to be able to do the following operations to a workbook for excel 2007:
I am looking into xlrd; however.
Can anyone please tell me how could do this?
2 Answers 2
If you’re working in Python 3.x, you’ll find a lot of trouble using the xlrd / xlwt / xlutils family, as they’re modules for Python 2.
You might consider openpyxl for working with Excel 2007 .xlsx files in Python 3.
If you just need to shift values over (disregarding formatting, etc) here’s one way to do it. You can build on this:
This deletes a column from an xlsx worksheet using openpyxl 2.3.3. You can specify either a column number or letter:
On its final iteration this copies None values from column ws.max_column+1 to column ws.max_column , wiping out the values that used to be there. While the values in the cells are correct, unfortunately ws.max_column does not decrement.
In other answers there is talk of using Worksheet.garbage_collect() to reset ws.max_column , but I could only find the private method Worksheet._garbage_collect() , so I did not use it.
UPDATE: In the end I found that deleting a lot of columns was inefficient. Hiding them is a superior solution. This preserves formatting and even more importantly, maintains the integrity of formulae that reference the hidden cells:
Use Python to Delete Excel Rows & Columns
This tutorial will show you how to use the Python openpyxl library to delete rows or columns from existing Excel files.
Library
To install the openpyxl library, type the following in a command prompt window:
Sample Dataset
Copy and run the following code to create a sample Excel file to follow the tutorial.
openpyxl created Excel file
Python Delete Excel Rows and Columns
- delete_rows(row_id, number_of_rows)
- delete_cols(col_id, number_of_cols)
Delete at Known Position
Since we need to delete from existing Excel files, let’s first read the file content into Python. Then we can use the delete_rows() or delete_cols() to remove rows and columns.
Delete 1 Row or Column
If we want to delete just 1 row, just call delete_rows(n), which will delete the nth row from the top. We can ignore the second argument, which equals 1 by default. Similarly, to delete just 1 column, use delete_cols(n) and skip the second argument.
Delete Multiple Rows or Columns
To delete multiple rows or columns, we’ll need to use both arguments. The following code will delete the 2nd row to the 5th row. It means to delete row 2, plus the 3 rows underneath it, for a total of 4 rows.
Similarly, to delete multiple columns we need to use both arguments. The below deletes from column 2 (B), and for a total of 2 columns (B and C).
Unfortunately, there’s no easy way to delete multiple non-consecutive rows or columns. We have to delete one by one and figure out the new position of the rows/cols after each delete.
Delete Based on Cell Values
Our dataset contains 2 empty rows at id 4 and 9. Let’s say if the empty rows can appear randomly anywhere in a file, then we don’t know the exact location of those rows (or columns) so we can’t use the delete methods directly.
openpyxl delete empty rows
We need to first find where those rows are. Basically, we need to know which rows have empty values.
In the below code, using ws.iter_rows() we can get tuples of Cell objects. Then from those Cell objects, we can access the cell values and row/column positions.
any() returns True if at least one of the elements is not None, and returns False if all elements are None. We can use this function to locate the empty rows.
Inserting and deleting rows and columns, moving ranges of cells¶
You can insert rows or columns using the relevant worksheet methods:
The default is one row or column. For example to insert a row at 7 (before the existing row 7):
Deleting rows and columns¶
To delete the columns F:H :
Openpyxl does not manage dependencies, such as formulae, tables, charts, etc., when rows or columns are inserted or deleted. This is considered to be out of scope for a library that focuses on managing the file format. As a result, client code must implement the functionality required in any particular use case.
Moving ranges of cells¶
You can also move ranges of cells within a worksheet:
This will move the cells in the range D4:F10 up one row, and right two columns. The cells will overwrite any existing cells.
If cells contain formulae you can let openpyxl translate these for you, but as this is not always what you want it is disabled by default. Also only the formulae in the cells themselves will be translated. References to the cells from other cells or defined names will not be updated; you can use the Parsing Formulas translator to do this:
This will move the relative references in formulae in the range by one row and one column.
Merge / Unmerge cells¶
When you merge cells all cells but the top-left one are removed from the worksheet. To carry the border-information of the merged cell, the boundary cells of the merged cell are created as MergeCells which always have the value None. See Styling Merged Cells for information on formatting merged cells.
Inserting & Deleting rows/columns using openpyxl
This python tutorial help to insert and delete rows and columns into an excel file using openpyxl. openpyxl is a Python Library developed by Eric Gazoni and Charlie Clark to read and write Excel xlsx/xlsm/xltm/xltx files without using the Excel software. It is an open source excel libs and the most widely used library for excel operation. The openpyxl is the default reader for Python Pandas.
You can also checkout other python excel tutorials:
Excel is a very powerful and popular software for spreadsheets. The python excel libs helps in reading and modifying excel spreadsheet files through python programs.
Inserting and Deleting rows and columns
The openpyxl providing a set of methods to the sheet class, that help to add and delete rows/columns from the excel sheet. I’m going to load the workbook, and then grab that active sheet and perform add/delete operations.
How To Install openpyxl Library
This module does not come built-in with Python 3. You can install this package into your python application by running of the following command into the terminal.
pip3 install openpyxl
I am just extending the previous tutorial and adding functionality to insert and delete rows with columns.
How To Insert a Row into Excel File
You can insert rows using an excel file using the insert_rows() worksheet methods. The default is one row to insert into an excel file. The syntax is as follows:
Whereas: The first parameter represents row number and the second parameter represents a number of rows.
The sample python code to Inserting row into excel:
How To Insert a Column into Excel File
You can insert columns into the excel file using the insert_cols() worksheet methods. The default is one column to insert into excel file. The syntax is as follows:
insert_cols(idx, amount=1)
Whereas : The first parameter represents column number and the second parameter represents the number of columns to add
The sample python code to Inserting Column into excel:
How To Delete a Row From Excel File
You can delete a row from the excel file using the delete_rows() worksheet methods. The default is one row to delete from the excel file. The syntax is as follows:
Whereas: The first parameter represents row number and the second parameter represents the number of rows to delete
Sample Python Code to Delete Row from Excel:
How To Delete a Column From Excel File
You can delete a column from an excel file using the delete_cols() worksheet methods. The default is one column to delete from the excel file. The syntax is as follows:
Sample Python Code to Delete Column from Excel:
Conclusion
We have learned to insert a row of data into the existing excel file and insert a column into the existing excel file. Also, deleted a row and column from the excel file using openpyxl. There are a lots of Popular Python excel Library.