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.

When printing Excel spreadsheets, particularly those containing complex datasets or detailed reports, configuring the page setup properly is crucial. Excel’s page setup options enable you to adjust key factors such as page margins, orientation, paper size, and scaling, ensuring your documents are tailored to fit various printing needs. By customizing these settings, you can control how your content is displayed on the page, making sure it appears polished and professional. In this article, we will demonstrate how to set page setup options in Excel in C# using Spire.XLS for .NET.

• Set Page Margins in Excel in C#
• Set Page Orientation in Excel in C#
• Set Paper Size in Excel in C#
• Set Print Area in Excel in C#
• Set Scaling Factor in Excel in C#
• Set Fit-to-Pages Options in Excel in C#
• Set Headers and Footers in Excel in C#

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Set Page Margins in Excel in C#

The PageSetup class in Spire.XLS for .NET 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, use properties like PageSetup.TopMargin, PageSetup.BottomMargin, PageSetup.LeftMargin, PageSetup.RightMargin, PageSetup.HeaderMarginInch, and PageSetup.FooterMarginInch to set the corresponding 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.

using Spire.Xls;

namespace SetPageMargins
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 C#

The PageSetup.Orientation property lets you determine how the page should be oriented when printed. You can choose between two options: portrait mode or landscape mode. 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.

using Spire.Xls;

namespace SetPageOrientation
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Page Orientation
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 C#

The PageSetup.PaperSize property enables you to select from a variety of paper sizes for printing your worksheet. These options include A3, A4, A5, B4, B5, letter, legal, tabloid, and more. 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.

using Spire.Xls;

namespace SetPaperSize
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 C#

You can specify the exact area that you want to print 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.

using Spire.Xls;

namespace SetPrintArea
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 C#

If you want to scale the content of your worksheet to a specific percentage of its original size, use 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.

using Spire.Xls;

namespace SetScalingFactor
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 Fit-to-Pages Options in Excel in C#

Spire.XLS also enables you to fit your worksheet content to a specific number of pages by using the 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.

using Spire.Xls;

namespace SetFitToPages
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the PageSetup object of the worksheet
            PageSetup 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 C#

For setting headers and footers in Excel, please check this article: C#/VB.NET: 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.

Handling PDF documents using bytes and bytearray provides an efficient and flexible approach within applications. By processing PDFs directly as byte streams, developers can manage documents in memory or transfer them over networks without the need for temporary file storage, optimizing space and improving overall application performance. This method also facilitates seamless integration with web services and APIs. Additionally, using bytearray allows developers to make precise byte-level modifications to PDF documents.

This article will demonstrate how to save PDFs as bytes and bytearray and load PDFs from bytes and bytearray using Spire.PDF for Python, offering practical examples for Python developers.

• Create a PDF Document and Save It to Bytes
• Load a PDF Document from Bytes

Install Spire.PDF for Python

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

pip install Spire.PDF

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

Create a PDF Document and Save It to Bytes and Bytearray

Developers can create PDF documents using the classes and methods provided by Spire.PDF for Python, save them to a Stream object, and then convert it to an immutable bytes object or a mutable bytearray object. The Stream object can also be used to perform byte-level operations.

The detailed steps are as follows:

• Create an object of PdfDocument class to create a PDF document.
• Add a page to the document and draw text on the page.
• Save the document to a Stream object using PdfDocument.SaveToStream() method.
• Convert the Stream object to a bytes object using Stream.ToArray() method.
• The bytes object can be directly converted to a bytearray object.
• Afterward, the byte streams can be used for further operations, such as writing them to a file using the BinaryIO.write() method.

from spire.pdf import *

# Create an instance of PdfDocument class
pdf = PdfDocument()

# Set the page size and margins of the document
pageSettings = pdf.PageSettings
pageSettings.Size = PdfPageSize.A4()
pageSettings.Margins.Top = 50
pageSettings.Margins.Bottom = 50
pageSettings.Margins.Left = 40
pageSettings.Margins.Right = 40

# Add a new page to the document
page = pdf.Pages.Add()

# Create fonts and brushes for the document content
titleFont = PdfTrueTypeFont("HarmonyOS Sans SC", 16.0, PdfFontStyle.Bold, True)
titleBrush = PdfBrushes.get_Brown()
contentFont = PdfTrueTypeFont("HarmonyOS Sans SC", 13.0, PdfFontStyle.Regular, True)
contentBrush = PdfBrushes.get_Black()

# Draw the title on the page
titleText = "Brief Introduction to Cloud Services"
titleSize = titleFont.MeasureString(titleText)
page.Canvas.DrawString(titleText, titleFont, titleBrush, PointF(0.0, 30.0))

# Draw the body text on the page
contentText = ("Cloud computing is a service model where computing resources are provided over the internet on a pay-as-you-go basis. "
               "It is a type of infrastructure-as-a-service (IaaS), platform-as-a-service (PaaS), and software-as-a-service (SaaS) model. "
               "Cloud computing is typically offered througha subscription-based model, where users pay for access to the cloud resources on a monthly, yearly, or other basis.")
# Set the string format of the body text
contentFormat = PdfStringFormat()
contentFormat.Alignment = PdfTextAlignment.Justify
contentFormat.LineSpacing = 20.0
# Create a TextWidget object with the body text and apply the string format
textWidget = PdfTextWidget(contentText, contentFont, contentBrush)
textWidget.StringFormat = contentFormat
# Create a TextLayout object and set the layout options
textLayout = PdfTextLayout()
textLayout.Layout = PdfLayoutType.Paginate
textLayout.Break = PdfLayoutBreakType.FitPage
# Draw the TextWidget on the page
rect = RectangleF(PointF(0.0, titleSize.Height + 50.0), page.Canvas.ClientSize)
textWidget.Draw(page, rect, textLayout)

# Save the PDF document to a Stream object
pdfStream = Stream()
pdf.SaveToStream(pdfStream)

# Convert the Stream object to a bytes object
pdfBytes = pdfStream.ToArray()

# Convert the Stream object to a bytearray object
pdfBytearray = bytearray(pdfStream.ToArray())

# Write the byte stream to a file
with open("output/PDFBytearray.pdf", "wb") as f:
    f.write(pdfBytearray)

Load a PDF Document from Byte Streams

Developers can use a bytes object of a PDF file to create a stream and load it using the PdfDocument.LoadFromStream() method. Once the PDF document is loaded, various operations such as reading, modifying, and converting the PDF can be performed. The following is an example of the steps:

• Create a bytes object with a PDF file.
• Create a Stream object using the bytes object.
• Load the Stream object as a PDF document using PdfDocument.LoadFromStream() method.
• Extract the text from the first page of the document and print the text.

from spire.pdf import *

# Create a byte array from a PDF file
with open("Sample.pdf", "rb") as f:
    byteData = f.read()

# Create a Stream object from the byte array
stream = Stream(byteData)

# Load the Stream object as a PDF document
pdf = PdfDocument(stream)

# Get the text from the first page
page = pdf.Pages.get_Item(0)
textExtractor = PdfTextExtractor(page)
extractOptions = PdfTextExtractOptions()
extractOptions.IsExtractAllText = True
text = textExtractor.ExtractText(extractOptions)

# Print the text
print(text)

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.