Data Bars in Excel is a feature within the Conditional Formatting tool that allows you to visually represent numerical data through a series of bars. This feature is particularly useful for comparing values at a glance, as the length of the bar corresponds to the magnitude of the value it represents. In this article, you will learn how to add data bars in an Excel cell range in Python using Spire.XLS for Python.

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Data Bars in Excel in Python

With Spire.XLS for Python, you are allowed to add a data bar to a specified data range and also set its format. The following are the detailed steps.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worsheets[index] property.
• Add a conditional formatting to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method, and then set its format type to DataBar using IConditionalFormat.FormatType property.
• Set the fill effect and color of the data bars using IConditionalFormat.DataBar.BarFillType and IConditionalFormat.DataBar.BarColor properties.
• Save the result document using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook instance
workbook = Workbook()

# Load a sample Excel document
workbook.LoadFromFile("sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
xcfs = sheet.ConditionalFormats.Add()

# Set the range where the conditional format will be applied
xcfs.AddRange(sheet.Range["C2:C13"])

# Add a condition and set its format type to DataBar
format = xcfs.AddCondition()
format.FormatType = ConditionalFormatType.DataBar

# Set the fill effect and color of the data bars
format.DataBar.BarFillType = DataBarFillType.DataBarFillGradient
format.DataBar.BarColor = Color.get_Red()

# Save the result document
workbook.SaveToFile("ApplyDataBarsToCellRange.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Charts and shapes in Excel are vital tools for clear and effective data presentation. Sometimes, it's beneficial to convert these visual elements into images. Perhaps you need to include a specific chart in a report or presentation outside of Excel. Or maybe you want to use an Excel-created infographic on your company's website. Regardless of the use case, knowing how to export these visuals as standalone image files can be invaluable. In this guide, we will explore how to convert charts and shapes in Excel to images in Python using Spire.XLS for Python.

• Convert a Specific Chart in an Excel Worksheet to Image in Python
• Convert All Charts in an Excel Worksheet to Images in Python
• Convert a Chart Sheet in Excel to Image in Python
• Convert Shapes in Excel to Images in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Convert a Specific Chart in an Excel Worksheet to Image in Python

Spire.XLS for Python offers the Workbook.SaveChartAsImage(worksheet: Worksheet, chartIndex: int) method, allowing you to convert a specific chart within a worksheet into an image stream. This image stream can then be saved as an image file in various formats, including PNG, JPG, BMP, and more. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using the Workbook.Worksheets[] property.
• Save a specific chart in the worksheet to an image stream using the Workbook.SaveChartAsImage(worksheet: Worksheet, chartIndex: int) method.
• Save the image stream to an image file using the Stream.Save() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Charts.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Save the first chart in the worksheet to an image stream
image_stream = workbook.SaveChartAsImage(sheet, 0)

# Save the image stream to a PNG image file
image_stream.Save("Output/chart.png")

workbook.Dispose()

Convert All Charts in an Excel Worksheet to Images in Python

To convert all charts in an Excel worksheet to images, you can use the Workbook.SaveChartAsImage(worksheet: Worksheet) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using the Workbook.Worksheets[] property.
• Save all charts in the worksheet to a list of image streams using the Workbook.SaveChartAsImage(worksheet: Worksheet) method.
• Iterate through the image streams in the list and save them to separate image files.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Charts.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

image_streams = []
# Save the charts in the worksheet to a list of image streams
image_streams = workbook.SaveChartAsImage(sheet)

# Save the image streams to PNG image files
for i, image_stream in enumerate(image_streams):
    image_stream.Save(f"Output/chart-{i}.png")

workbook.Dispose()

Convert a Chart Sheet in Excel to Image in Python

In Microsoft Excel, a chart sheet is a special type of sheet that is dedicated to displaying a single chart or graph. You can convert a chart sheet in an Excel workbook to an image using the Workbook.SaveChartAsImage(chartSheet: ChartSheet) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific chart sheet in the file using the Workbook.Chartsheets[] property.
• Save the chart sheet to an image stream using the Workbook.SaveChartAsImage(chartSheet: ChartSheet) method.
• Save the image stream to an image file.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ChartSheet.xlsx")

# Get the first chart sheet
chart_sheet = workbook.Chartsheets[0]

# Save the chart sheet to an image stream
image_stream = workbook.SaveChartAsImage(chart_sheet)

# Save the image stream to a PNG image file
image_stream.Save("Output/chartSheet.png")

workbook.Dispose()

Convert Shapes in Excel to Images in Python

In addition to converting charts or chart sheets to images, you can also convert shapes in an Excel worksheet to images by using the XlsShape.SaveToImage() method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet in the file using the Workbook.Worksheets[] property.
• Iterate through all the shapes in the worksheet.
• Typecast the shape to an XlsShape object.
• Save the shape to an image stream using the XlsShape.SaveToImage() method.
• Save the image stream to an image file.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Shapes.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Iterate through all the shapes in the worksheet
for i, shape in enumerate(sheet.PrstGeomShapes):
    xls_shape = XlsShape(shape)
    # Save the shape to an image stream
    image_stream = shape.SaveToImage()
    # Save the image stream to a PNG image file
    image_stream.Save(f"Output/shape_{i}.png")

workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Page setup in Excel refers to the various settings that control how an Excel worksheet will be printed or displayed in a print preview. These settings determine the appearance and layout of the printed document, ensuring that it meets the desired formatting and readability standards. Page setup options include page margins, orientation, paper size, print area, headers, footers, scaling, and other print-related settings. In this article, we will explain how to set page setup options in Excel in Python using Spire.XLS for Python.

• Set Page Margins in Excel in Python
• Set Page Orientation in Excel in Python
• Set Paper Size in Excel in Python
• Set Print Area in Excel in Python
• Set Scaling Factor in Excel in Python
• Set FitToPages Options in Excel in Python
• Set Headers and Footers in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Set Page Margins in Excel in Python

In Spire.XLS for Python, the PageSetup class is used to configure page setup options for Excel worksheets. You can access the PageSetup object of a worksheet through the Worksheet.PageSetup property. Then, you can use properties like PageSetup.TopMargin, PageSetup.BottomMargin, PageSetup.LeftMargin, PageSetup.RightMargin, PageSetup.HeaderMarginInch, and PageSetup.FooterMarginInch to set the respective margins for the worksheet. The detailed steps are as follows:

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the top, bottom, left, right, header, and footer margins using PageSetup.TopMargin, PageSetup.BottomMargin, PageSetup.LeftMargin, PageSetup.RightMargin, PageSetup.HeaderMarginInch, and PageSetup.FooterMarginInch properties.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Set top, bottom, left, and right page margins for the worksheet
# The measure of the unit is Inch (1 inch = 2.54 cm)
pageSetup.TopMargin = 1
pageSetup.BottomMargin = 1
pageSetup.LeftMargin = 1
pageSetup.RightMargin = 1
pageSetup.HeaderMarginInch= 1
pageSetup.FooterMarginInch= 1

# Save the modified workbook to a new file
workbook.SaveToFile("SetPageMargins.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Page Orientation in Excel in Python

To set the page orientation for an Excel worksheet, you can use the PageSetup.Orientation property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the page orientation using PageSetup.Orientation property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Set the page orientation for printing the worksheet to landscape mode
pageSetup.Orientation = PageOrientationType.Landscape

# Save the modified workbook to a new file
workbook.SaveToFile("SetPageOrientation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Paper Size in Excel in Python

You can set a wide range of paper sizes, such as A3, A4, A5, B4, B5, Letter, Legal, and Tabloid for printing an Excel worksheet using the PageSetup.PaperSize property. The detailed steps are as follows:

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the paper size using PageSetup.PaperSize property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Set the paper size to A4
pageSetup.PaperSize = PaperSizeType.PaperA4

# Save the modified workbook to a new file
workbook.SaveToFile("SetPaperSize.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Print Area in Excel in Python

The print area of an Excel worksheet can be customized using the PageSetup.PringArea property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the print area using PageSetup.PringArea property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Set the print area of the worksheet to "A1:E5"
pageSetup.PrintArea = "A1:E5"

# Save the modified workbook to a new file
workbook.SaveToFile("SetPrintArea.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Scaling Factor in Excel in Python

You can scale the content of a worksheet to a specific percentage of its original size with the PageSetup.Zoom property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the scaling factor using PageSetup.Zoom property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Set the scaling factor of the worksheet to 90%
pageSetup.Zoom = 90

# Save the modified workbook to a new file
workbook.SaveToFile("SetScalingFactor.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set FitToPages Options in Excel in Python

In addition to scaling the content of a worksheet to a specific percentage of its original size, you can also fit the content of a worksheet to a specific number of pages using PageSetup.FitToPagesTall and PageSetup.FitToPagesWide properties. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Fit the content of the worksheet to one page using PageSetup.FitToPagesTall and PageSetup.FitToPagesWide properties.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the PageSetup object of the worksheet
pageSetup = sheet.PageSetup

# Fit the content of the worksheet within one page vertically (i.e., all rows will fit on a single page)
pageSetup.FitToPagesTall = 1
# Fit the content of the worksheet within one page horizontally (i.e., all columns will fit on a single page)
pageSetup.FitToPagesWide = 1

# Save the modified workbook to a new file
workbook.SaveToFile("FitToPages.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Headers and Footers in Excel in Python

For setting headers and footers in Excel, please check this article: Python: Add Headers and Footers to Excel.

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Converting XLS files to various formats is necessary for data management and presentation. ODS, XPS, PostScript, and PDF/A-1b offer unique advantages and are suitable for different scenarios.

ODS is widely used for compatibility with many office suites. XPS preserves document fidelity and is ideal for sharing and archiving. PostScript is a versatile page description language often used for printing and graphic design. PDF/A-1b ensures long-term archiving by complying with strict preservation standards.

This guide will illustrate how to convert Excel to ODS, XPS, PostScript, and PDF/A-1b with Python using Spire.XLS for Python, leveraging their specific strengths to meet diverse needs.

• Convert Excel to ODS, XPS, and PostScript with Python
• Convert Excel to PDF/A-1b Using Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install it, please refer to this tutorial: How to Install Spire.XLS for Python on Windows.

Convert Excel to ODS, XPS, and PostScript with Python

To convert Excel to ODS, XPS, and PostScript documents, you can utilize Workbook.SaveToFile() method. It supports converting CSV to Excel and PDF, Excel to PDF and XLSX, etc. By using this method provided by Spire.XLS for Python, you can seamlessly transform your documents into these formats while maintaining accuracy without data loss. Read the following steps to learn more:

Steps to convert Excel to ODS, XPS, and PostScript:

1. Create a new Workbook object.
2. Import the file to be converted from the disk using Workbook.LoadFromFile() method.
3. Convert it to ODS, XPS, or PostScript with Workbook.SaveToFile() method.

Here is the code example for reference:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load the file from the disk
workbook.LoadFromFile("sample.xlsx")

# Save the document to an ODS file
workbook.SaveToFile("to_ods.ods", FileFormat.ODS)
# Save the document as an XPS file
workbook.SaveToFile("to_xps.xps", FileFormat.XPS)
# Save the document as a PostScript file
workbook.SaveToFile("to_postscript.ps", FileFormat.PostScript)

workbook.Dispose()

Note: Images 1, 2, and 3 show the results of converting Excel files to ODS, XPS, and PostScript formats, respectively.

How to Convert Excel Documents to PDF/A-1b Format

If you need to convert Excel to PDF/A-1b Format with Python, call Workbook.SaveToFile will help you. The steps to transform Excel documents to PDF/A-1b are similar to those above, except the former involves an additional step. This tutorial will guide you through the process with detailed steps and a code example.

Steps to convert Excel to PDF/A-1b

1. Instantiate a new Workbook object.
2. Read the Excel document from the disk using Workbook.LoadFromFile() method.
3. Set the PDF conformance level to PDF/A-1b.
4. Save the generated document as PDF with Workbook.SaveToFile() method.

Here is the code example for you:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Open the file from the disk
workbook.LoadFromFile("sample.xlsx")

# Set the PDF conformance to PDF/A-1b
workbook.ConverterSetting.PdfConformanceLevel = PdfConformanceLevel.Pdf_A1B
# Convert the Excel document to PDF/A-1b
workbook.SaveToFile("to_pdfa1b", FileFormat.PDF)

workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.