In this article we want to talk about Excel Manipulation with Python XlsxWriter, so first of all let’s talk about XlsxWriter.
What is Python Python XlsxWriter ?
Python XlsxWriter is a powerful library, using that library you can create and manipulate Excel files in the XLSX format. XlsxWriter is open source library, you can easily install XlsxWriter using pip, you can use XlsxWriter for complex Excel files with graphs, images, formulas and formatting. in this article we want to learn about Excel Manipulation with Python XlsxWriter.
Why use XlsxWriter ?
XlsxWriter is one of the best library for working with Excel files in Python. It has different features, and it is an excellent choice for developers who want to manipulate Excel files programmatically. some of the key benefits of using XlsxWriter are:
- Easy to use: XlsxWriter is easy to use even for beginners programmers. The library has user-friendly interface and also it has detailed documentation that explains how to use each feature.
- Compatibility: You can use XlsxWriter with Python 2.7 and 3.x versions. It can also be used on different platforms such as Windows, Linux and macOS.
- Flexibility: XlsxWriter provides different features such as formatting cells, creating charts, adding images and many more. and it is good for complex excel files.
- Speed: XlsxWriter is fast and efficient, it means means that it can handle large Excel files quickly and easily.
How to Install XlsxWriter ?
You can use pip for the installation of XlsxWriter, simply open your command prompt or terminal and write this command.
|
1 |
pip install XlsxWriter |
How to Create Excel file using XlsxWriter
Now let’s create our example, we want to create an example of how to create excel file using XlsxWriter and Python . This is a simple example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
import xlsxwriter # Create new Excel file workbook = xlsxwriter.Workbook('file.xlsx') # Create new worksheet worksheet = workbook.add_worksheet() # Write some data to worksheet worksheet.write('A1', 'Code') worksheet.write('B1', 'Loop') # Close workbook workbook.close() |
This program will create new Excel file at name of file.xlsx in the current directory. It will also create new worksheet and write the strings “CodeLoop” to cells A1 and B1.
Formatting Excel cells in Python XlsxWriter
XlsxWriter provides different methods to format cells in an Excel file. You can change the font, color, alignment, borders, and more. this is an example that demonstrates how to format cells:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
import xlsxwriter # Create new Excel file workbook = xlsxwriter.Workbook('file.xlsx') # Create new worksheet worksheet = workbook.add_worksheet() # Create formate for the cell bold = workbook.add_format({'bold': True}) # Write som data to the worksheet worksheet.write('A1', 'Code', bold) worksheet.write('B1', 'Loop', bold) # Set the width of column A to 30 worksheet.set_column('A:A', 30) # Close the workbook workbook.close() |
This program creates new Excel file at name of file.xlsx and create new worksheet. after that It will creates a format for the cell that makes the text bold. The program will write “CodeLoop” to cells A1 and B1. using the bold format.
How to Create Charts with Python & XlsxWriter
XlsxWriter also provides methods to create charts in an Excel file. You can create line charts, bar charts, scatter charts and many more. This is an example that demonstrates how to create a line chart:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import xlsxwriter # Create new Excel file workbook = xlsxwriter.Workbook('file.xlsx') # Create new worksheet worksheet = workbook.add_worksheet() # Write some data to worksheet data = [ ['Category', 'Value'], ['A', 10], ['B', 20], ['C', 30], ['D', 40], ] for row_num, row_data in enumerate(data): for col_num, col_data in enumerate(row_data): worksheet.write(row_num, col_num, col_data) # Create chart object chart = workbook.add_chart({'type': 'column'}) # Configure the chart data chart.add_series({'values': '=Sheet1!$B$2:$B$5'}) # Insert the chart into the worksheet worksheet.insert_chart('D2', chart) # Close the workbook workbook.close() |
This code will create an Excel file named file.xlsx with a worksheet, it contains the data and a column chart based on that data. Adjust the data and chart type as needed for your specific requirements.
Run the code and this will be the result
What are Other Python Libraries Instead of XlsxWriter
Python has different libraries for working with Excel files. These are some popular alternatives to XlsxWriter:
- openpyxl: openpyxl is Python library, using this library you can read and write Excel files in the XLSX format. It provides different features such as formatting cells, creating charts, and many more.
- xlrd and xlwt: xlrd and xlwt are Python libraries that allow you to read and write Excel files in the XLS format. They provide basic features such as reading and writing cell data and formatting.
- pandas: pandas is Python library that provides powerful data manipulation and analysis capabilities. you can use that for reading and writing Excel files and supports different features, such as filtering, grouping and many more.
- pyexcel and pyexcel-xlsx: pyexcel is Python library that provides simple interface for reading and writing Excel files in different formats, including XLSX. pyexcel-xlsx is a plugin for pyexcel that adds support for XLSX files.
- win32com: win32com is Python library that allows you to manipulate Microsoft Excel files using Windows COM interface. It provides access to the full range of features in Excel including charts, pivot tables and many more.
Subscribe and Get Free Video Courses & Articles in your Email