XlsxWriter is a Python module that allows you to write files in the XLSX file format. It can be used to fill several spreadsheets with text, numbers, and formulas. It also allows formatting, images, charts, page setup, auto filters, conditional formatting, and many more features.<\/p>\n
Installation:<\/strong><\/p>\n Use the following command to install the XlsxWriter module.<\/p>\n Using the to_excel() function, we can export the DataFrame to an Excel file.<\/p>\n The target file name must be specified when writing a single object to an excel file. If we wish to write to many\/multiple\u00a0sheets, we must first create an ExcelWriter object with the target filename and then indicate the sheet in the file we want to write to.<\/p>\n The unique sheet_name can also be used to write multiple sheets. All modifications made to the data written to the file must be saved.<\/p>\n NOTE:<\/strong><\/p>\n Syntax:<\/strong><\/p>\n Parameters<\/strong><\/p>\n \u00a0Approach:<\/strong><\/p>\n Below is the implementation:<\/strong><\/p>\n Output:<\/strong><\/p>\n Approach:<\/strong><\/p>\n Below is the implementation:<\/strong><\/p>\n Output:<\/strong><\/p>\n Below is the Implementation:<\/strong><\/p>\n Output:<\/strong><\/p>\npip\u00a0install\u00a0xlsxwriter<\/pre>\n<\/div>\n
Looking in indexes: https:\/\/pypi.org\/simple, https:\/\/us-python.pkg.dev\/colab-wheels\r\n\/public\/simple\/\r\nCollecting xlsxwriter\r\nDownloading XlsxWriter-3.0.3-py3-none-any.whl (149 kB)\r\n|\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 149 kB 5.3 MB\/s \r\nInstalling collected packages: xlsxwriter\r\nSuccessfully installed xlsxwriter-3.0.3<\/pre>\n
DataFrame.to_excel() in Pandas:<\/h4>\n
If we create an ExcelWriter object with a file name that already exists, \r\nit will erase and overwrite the existing file's content.\r\n<\/pre>\n
DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None,\r\ncolumns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, \r\nengine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True,\r\n freeze_panes=None)<\/pre>\n
\n
inf<\/code>. In most cases, it represents infinity.<\/li>\nWorking with Pandas and XlsxWriter in Python | Set \u2013 3<\/h2>\n
\n
Method #1: Using Pandas and XlsxWriter to Plot a Column chart<\/h3>\n
\n
# Import pandas module using the import keyword\r\nimport pandas as pd\r\n\r\n# Create a Pandas dataframe by passing some random data to the\r\n# DataFrame() function of the pandas module and store it in a variable \r\ndatafrme = pd.DataFrame({\r\n 'Subjects': [\"Operating System\", \"Web Technologies\", \"Artificial Intelligence\",\r\n \"Computer Networks\", \"Machine Learning\", \"Software Project Management\"],\r\n 'First Sem' : [88, 91, 75, 80, 68, 95],\r\n 'Second Sem' : [55, 90, 60, 45, 70, 35] })\r\n\r\n# Pass the excel filepath, engine as xlsxwriter as arguments to the \r\n# ExcelWriter() function of the pandas module to create an excel writer object.\r\nwriterObj= pd.ExcelWriter('Demo_ColumnChart.xlsx',\r\n engine ='xlsxwriter')\r\n\r\n# Apply to_excel() function on the above dataframe by passing the above writer object,\r\n# sheet name as arguments to it to Write the above dataframe into the worksheet.\r\ndatafrme.to_excel(writerObj, sheet_name ='Sheet1')\r\n\r\n# Create a xlsxwriter workbook object using the book property and store it in a variable\r\nworkbookObj = writerObj.book\r\n\r\n# Create a xlsxwriter worksheet object using the sheets property \r\n# and store it in another variable\r\nworksheet = writerObj.sheets['Sheet1']\r\n\r\n# Apply set_column() function on the above worksheet to \r\n# Set the width of the B and C columns \r\nworksheet.set_column('B:C', 30)\r\n \r\n# Pass a type as column as an argument to the add_chart() function and \r\n# apply it in the above workbook object to create a column chart object .\r\nchartObject = workbookObj.add_chart({'type': 'column'})\r\n\r\n# Using the add_series() method, you can add a data series with a pattern to a chart. \r\n# The gap is used to highlight the patterns.\r\n\r\n# Add the first series data using the add_series() function \r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 2], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 2, 6, 2], \r\n })\r\n\r\n# Configuring the second series.\r\n# Take note of the usage of different syntax to declare ranges.\r\n# [sheetname, first row, first col, last row, last col]\r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 1], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 1, 6, 1], \r\n })\r\n\r\n# Set the title to the chartObject using the set_title() function by passing the title name as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_title({'name': 'Exam Results'})\r\n\r\n# Label the x axis using the set_x_axis() function by passing the Computer Subjects as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_x_axis({'name': 'Computer Subjects'})\r\n \r\n# Label the y axis using the set_x_axis() function by passing the Marks as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_y_axis({'name': 'Marks'})\r\n\r\n# Add a chartObject to the worksheet with the provided offset values in the top-left corner of a chartObject that is anchored to cell D2.\r\nworksheet.insert_chart('D2', chartObject,\r\n {'x_offset': 20, 'y_offset': 5})\r\n\r\n# Save the above writer object using the save() function\r\nwriterObj.save()<\/pre>\n![\"column](\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/column-chart-using-xlsx-writer.jpg\")
<\/p>\nMethod #2: Using Pandas and XlsxWriter to \u00a0Plot a Line\u00a0chart<\/h3>\n
\n
# Import pandas module using the import keyword\r\nimport pandas as pd\r\n\r\n# Create a Pandas dataframe by passing some random data to the\r\n# DataFrame() function of the pandas module and store it in a variable \r\ndatafrme = pd.DataFrame({\r\n 'Subjects': [\"Operating System\", \"Web Technologies\", \"Artificial Intelligence\",\r\n \"Computer Networks\", \"Machine Learning\", \"Software Project Management\"],\r\n 'First Sem' : [88, 91, 75, 80, 68, 95],\r\n 'Second Sem' : [55, 90, 60, 45, 70, 35] })\r\n\r\n# Pass the excel filepath, engine as xlsxwriter as arguments to the \r\n# ExcelWriter() function of the pandas module to create an excel writer object.\r\nwriterObj= pd.ExcelWriter('Demo_LineChart.xlsx',\r\n engine ='xlsxwriter')\r\n\r\n# Apply to_excel() function on the above dataframe by passing the above writer object,\r\n# sheet name as arguments to it to Write the above dataframe into the worksheet.\r\ndatafrme.to_excel(writerObj, sheet_name ='Sheet1')\r\n\r\n# Create a xlsxwriter workbook object using the book property and store it in a variable\r\nworkbookObj = writerObj.book\r\n\r\n# Create a xlsxwriter worksheet object using the sheets property \r\n# and store it in another variable\r\nworksheet = writerObj.sheets['Sheet1']\r\n\r\n# Apply set_column() function on the above worksheet to \r\n# Set the width of the B and C columns \r\nworksheet.set_column('B:C', 30)\r\n \r\n# Pass a type as line as an argument to the add_chart() function and \r\n# apply it in the above workbook object to create a line chart object .\r\nchartObject = workbookObj.add_chart({'type': 'line'})\r\n\r\n\r\n# Using the add_series() method, you can add a data series with a pattern to a chart. \r\n# The gap is used to highlight the patterns.\r\n# Add the first series data using the add_series() function \r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 2], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 2, 6, 2], \r\n })\r\n# Configuring the second series.\r\n# Take note of the usage of different syntax to declare ranges.\r\n# [sheetname, first row, first col, last row, last col]\r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 1], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 1, 6, 1], \r\n })\r\n\r\n\r\n# Set the title to the chartObject using the set_title() function by passing the title name as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_title({'name': 'Exam Results'})\r\n\r\n# Label the x axis using the set_x_axis() function by passing the Computer Subjects as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_x_axis({'name': 'Computer Subjects'})\r\n \r\n# Label the y axis using the set_x_axis() function by passing the Marks as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_y_axis({'name': 'Marks'})\r\n\r\n# Add a chartObject to the worksheet with the provided offset values in the top-left corner of a chartObject that is anchored to cell D2.\r\nworksheet.insert_chart('D2', chartObject,\r\n {'x_offset': 20, 'y_offset': 5})\r\n\r\n# Save the above writer object using the save() function\r\nwriterObj.save()<\/pre>\n![\"line](\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/line-chart-using-xlsx-writer.jpg\")
<\/p>\nMethod #3: Using Pandas and XlsxWriter to \u00a0Plot a Scatter chart<\/h3>\n
# Import pandas module using the import keyword\r\nimport pandas as pd\r\n\r\n# Create a Pandas dataframe by passing some random data to the\r\n# DataFrame() function of the pandas module and store it in a variable \r\ndatafrme = pd.DataFrame({\r\n 'Subjects': [\"Operating System\", \"Web Technologies\", \"Artificial Intelligence\",\r\n \"Computer Networks\", \"Machine Learning\", \"Software Project Management\"],\r\n 'First Sem' : [88, 91, 75, 80, 68, 95],\r\n 'Second Sem' : [55, 90, 60, 45, 70, 35] })\r\n\r\n# Pass the excel filepath, engine as xlsxwriter as arguments to the \r\n# ExcelWriter() function of the pandas module to create an excel writer object.\r\nwriterObj= pd.ExcelWriter('Demo_ScatterChart.xlsx',\r\n engine ='xlsxwriter')\r\n\r\n# Apply to_excel() function on the above dataframe by passing the above writer object,\r\n# sheet name as arguments to it to Write the above dataframe into the worksheet.\r\ndatafrme.to_excel(writerObj, sheet_name ='Sheet1')\r\n\r\n# Create a xlsxwriter workbook object using the book property and store it in a variable\r\nworkbookObj = writerObj.book\r\n\r\n# Create a xlsxwriter worksheet object using the sheets property \r\n# and store it in another variable\r\nworksheet = writerObj.sheets['Sheet1']\r\n\r\n# Apply set_column() function on the above worksheet to \r\n# Set the width of the B and C columns \r\nworksheet.set_column('B:C', 30)\r\n \r\n# Pass a type as scatter as an argument to the add_chart() function and \r\n# apply it in the above workbook object to create a scatter chart object .\r\nchartObject = workbookObj.add_chart({'type': 'scatter'})\r\n\r\n# Using the add_series() method, you can add a data series with a pattern to a chart. \r\n# The gap is used to highlight the patterns.\r\n\r\n# Add the first series data using the add_series() function \r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 2], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 2, 6, 2], \r\n })\r\n\r\n# Configuring the second series.\r\n# Take note of the usage of different syntax to declare ranges.\r\n# [sheetname, first row, first col, last row, last col]\r\nchartObject.add_series({\r\n 'name': ['Sheet1', 0, 1], \r\n 'categories': ['Sheet1', 1, 3, 6, 3], \r\n 'values': ['Sheet1', 1, 1, 6, 1], \r\n })\r\n\r\n# Set the title to the chartObject using the set_title() function by passing the title name as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_title({'name': 'Exam Results'})\r\n\r\n# Label the x axis using the set_x_axis() function by passing the Computer Subjects as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_x_axis({'name': 'Computer Subjects'})\r\n \r\n# Label the y axis using the set_x_axis() function by passing the Marks as value and key as \"name\" (Here the arguments are object)\r\nchartObject.set_y_axis({'name': 'Marks'})\r\n\r\n# Add a chartObject to the worksheet with the provided offset values in the top-left corner of a chartObject that is anchored to cell D2.\r\nworksheet.insert_chart('D2', chartObject,\r\n {'x_offset': 20, 'y_offset': 5})\r\n\r\n# Save the above writer object using the save() function\r\nwriterObj.save()<\/pre>\n![\"scatter](\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/06\/scatter-chart-using-xlsx-writer.jpg\")
<\/p>\n