Many financial reports, research papers, legal documents, or invoices are often distributed in PDF format. Reading PDF files enables you to extract information, analyze content, and perform data processing tasks such as text extraction, keyword search, document classification, and data mining.

By using C# to read PDF, you can automate the repetitive task to realize efficient retrieval of specific information from a large collection of PDF files. This is valuable for applications that require searching through extensive archives, digital libraries, or document repositories. This article will give the following examples to show you how to read PDF file in C#.

• Read Text from a PDF Page in C#
• Read Text from a PDF Page Area in C#
• Read PDF Without Preserving Text Layout in C#
• Extract Images and Tables in PDF in C#

C# PDF Reader Library

Spire.PDF for .NET library can serve as a PDF reader library that allows developers to integrate PDF reading capabilities into their applications. It provides functions and APIs for parsing, rendering, and processing of PDF files within .NET applications.

You can either download the C# PDF reader to manually add the DLL files as references in your .NET project, or install it directly via NuGet.

PM> Install-Package Spire.PDF

Read Text from a PDF Page in C#

Spire.PDF for .NET makes it easy to read PDF text in C# through the PdfTextExtractor class. The following are the steps to read all text from a specified PDF page.

• Create a PdfDocument object.
• Load a PDF file using PdfDocument.LoadFromFile() method.
• Get the specific page through PdfDocument.Pages[index] property.
• Create a PdfTextExtractor object.
• Create a PdfTextExtractOptions object, and set the IsExtractAllText property to true.
• Extract text from the selected page using PdfTextExtractor.ExtractText() method.
• Write the extracted text to a TXT file.

The following code example shows how to use C# to read PDF text from a specified page.

using System;
using System.IO;
using Spire.Pdf;
using Spire.Pdf.Texts;

namespace ExtractTextFromPage
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a PdfDocument object
            PdfDocument doc = new PdfDocument();

            //Load a PDF file
            doc.LoadFromFile("TestPDF.pdf");

            //Get the first page
            PdfPageBase page = doc.Pages[0];

            //Create a PdfTextExtractot object
            PdfTextExtractor textExtractor = new PdfTextExtractor(page);

            //Create a PdfTextExtractOptions object
            PdfTextExtractOptions extractOptions = new PdfTextExtractOptions();

            //Set isExtractAllText to true
            extractOptions.IsExtractAllText = true;

            //Read text from the PDF page
            string text = textExtractor.ExtractText(extractOptions);

            //Write to a txt file
            File.WriteAllText("ReadPDF.txt", text);
        }
    }
}

Read Text from a PDF Page Area in C#

To read PDF text from a specified page area in PDF, you can first define a rectangle area and then call the setExtractArea() method of PdfTextExtractOptions class to extract text from it. The following are the steps to extract PDF text from a rectangle area of a page.

• Create a PdfDocument object.
• Load a PDF file using PdfDocument.LoadFromFile() method.
• Get the specific page through PdfDocument.Pages[index] property.
• Create a PdfTextExtractor object.
• Create a PdfTextExtractOptions object, and specify the rectangle area through the ExtractArea property of it.
• Extract text from the rectangle using PdfTextExtractor.ExtractText() method.
• Write the extracted text to a TXT file.

The following code sample shows how to use C# to read PDF text from a specified page area.

using Spire.Pdf;
using Spire.Pdf.Texts;
using System.IO;
using System.Drawing;

namespace ExtractTextFromRectangleArea
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a PdfDocument object
            PdfDocument doc = new PdfDocument();

            //Load a PDF file
            doc.LoadFromFile("TestPDF.pdf");

            //Get the first page
            PdfPageBase page = doc.Pages[0];

            //Create a PdfTextExtractot object
            PdfTextExtractor textExtractor = new PdfTextExtractor(page);

            //Create a PdfTextExtractOptions object
            PdfTextExtractOptions extractOptions = new PdfTextExtractOptions();

            //Specify a rectangle area
            extractOptions.ExtractArea = new RectangleF(0, 180, 800, 160);

            //Read PDF text from the rectangle 
            string text = textExtractor.ExtractText(extractOptions);

            //Write to a txt file
            File.WriteAllText("ReadPDFArea.txt", text);
        }
    }
}

Read PDF Without Preserving Text Layout in C#

The above methods read PDF text line by line. You can also read PDF text simply without retain its layout using the SimpleExtraction strategy. It keeps track of the current Y position of each string and inserts a line break into the output if the Y position has changed. The following are the steps to read PDF text simply.

• Create a PdfDocument object.
• Load a PDF file using PdfDocument.LoadFromFile() method.
• Get the specific page through PdfDocument.Pages[index] property.
• Create a PdfTextExtractor object.
• Create a PdfTextExtractOptions object and set the IsSimpleExtraction property to true.
• Extract text from the selected page using PdfTextExtractor.ExtractText() method.
• Write the extracted text to a TXT file.

The following code sample shows how to use C# to read PDF text without preserving text layout.

using System.IO;
using Spire.Pdf;
using Spire.Pdf.Texts;

namespace SimpleExtraction
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a PdfDocument object
            PdfDocument doc = new PdfDocument();

            //Load a PDF file
            doc.LoadFromFile("TestPDF.pdf");

            //Get the first page
            PdfPageBase page = doc.Pages[0];

            //Create a PdfTextExtractor object
            PdfTextExtractor textExtractor = new PdfTextExtractor(page);

            //Create a PdfTextExtractOptions object
            PdfTextExtractOptions extractOptions = new PdfTextExtractOptions();

            //Set IsSimpleExtraction to true to 
            extractOptions.IsSimpleExtraction = true;

            //Read text from the PDF page 
            string text = textExtractor.ExtractText(extractOptions);

            //Write to a txt file
            File.WriteAllText("ExtractPDF.txt", text);
        }
    }
}

Extract Images and Tables in PDF in C#

In addition to read PDF text in C#, Spire.PDF for .NET library also allows you to extract images from PDF or read only the table data in a PDF file. The following links will direct you to the relevant official tutorials:

• Extract Images from PDF in C#
• Extract Table Data from PDF in C#
• Extract Tables from PDF to Excel in C#

Conclusion

This article introduced various ways to read PDF file in C#. You can learn from the given examples on how to read PDF text from a specified page, from a specified rectangle area, or read PDF files without preserving text layout. In addition, extracting images or tables in a PDF file can also be achieved with the Spire.PDF for .NET library.

Explore more PDF processing and conversion capabilities of the .NET PDF library using the documentation. If any issues occurred while testing, feel free to contact technical support team via email or forum.

• Extract Text from PDF Files in C#
• Convert PDF to Excel using C#
• Read Text from Images in C#

Excel files remain a ubiquitous format for storing tabular data, making the ability to efficiently read and extract information from them crucial. C#, benefiting from the extensive functionality of the .NET Framework, is capable of efficient data manipulation. Utilizing C# to read Excel files and write their data to databases and data tables, or use the data for other purposes, is an excellent solution for developers to manipulate Excel data.

This article focuses on how to use C# to read Excel files. It includes the following parts:

• Advantages and API for Reading Excel Files in C#
• Getting Started with Excel File Reading Using C#
• Read Excel Files into Text with C# Code
• Script in C# for Reading Excel Files into DataTable
• C# Code to Read Excel Files and Insert into Databases
• Get a Free License for the API

Advantages and API for Reading Excel Files in C#

Leveraging C# for reading Excel files brings the following advantages:

• Automation and scalability: C#, with .NET Framework and APIs, allows automating Excel file reading tasks, making it possible to process large volumes of data efficiently. Developers can create scripts or applications that can handle batch processing, scheduled tasks, or data import/export operations, saving time and effort.
• Flexibility and customization: The .NET Framework along with the APIs provides a high level of flexibility and customization when working with Excel files. Developers can define custom logic to extract and transform data, apply specific formatting, or perform calculations. This allows tailoring the reading process to specific requirements or business rules.

However, reading Excel files with only C# and the .NET Framework can be a challenging task. The native library provided by the .NET Framework for handling Excel files is Microsoft.Office.Interop.Excel. However, the usage of this library requires the installation of MS Excel, and the code logic for processing Excel files with it is extremely complex, making it difficult to efficiently read Excel files. As a result, developers prefer third-party libraries to work with Excel files efficiently and conveniently.

Spire.XLS for .NET is a professional C# API for Excel file processing. It features in extensive format support and efficient Excel data manipulation, including reading Excel files and writing the data to database, datatable, etc. Users can download Spire.XLS for .NET from the official site or install it through NuGet.

PM> Install-Package Spire.XLS

Getting Started with Excel File Reading in C#

Spire.XLS for .NET allows developers to create, read, write, and manipulate Excel files in their .NET applications with simple code.

To effectively work with Spire.XLS, you'll interact with various classes, properties, and methods. Here's a table summarizing some of the main ones along with their descriptions:

The typical workflow of using Spire.XLS for .NET  to read Excel files involves these steps:

1. Load the Excel file using Workbook.LoadFromFile() method.
2. Access worksheets through the Workbook.Worksheets[] property.
3. Access cells through the Worksheet.Range[] property.
4. Access displayed text in cells through the CellRange.Text property or access the value of cells (text, numbers, formulas, etc.) through the CellRange.Value property
5. Retrieve the data, write the data to text files, databases, etc., or perform other desired operations.
6. To export cell values to a data table, developers can directly use the Worksheet.ExportDataTable() method and it will return a DataTable object.
7. Alternatively, developers can also directly convert workbook using Workbook.SaveToFile() method to any supported format.

In short, this API provides classes and properties that allow developers to load Excel files and read their contents using straightforward code, making reading Excel files with C# efficient and simple. Additionally, it enables direct conversion of workbooks and worksheets to PDF, HTML, XML, and other file formats.

C# to Read Excel Files into Text

Steps for reading Excel files and write the data to text files with C#:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel  file using the Workbook.LoadFromFile() method.
3. Get the first worksheet through the Workbook.Worksheets[] property.
4. Create a text file to write the data in the worksheet.
5. Iterate through the allocated rows and columns, access cells through the Worksheet.Range[] property, and get the displayed text of each cell through the CellRange.Text property and write it to the text file.
6. Release resources.

Code Example:

using Spire.Xls;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile("Sample.xlsx");

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

        // Create an output text file
        string outputFile = "Output.txt";
        StreamWriter writer = new StreamWriter(outputFile);

        // Iterate through the rows and columns of the worksheet and write the data to the text file
        for (int row = 1; row <= worksheet.LastRow; row++)
        {
            for (int col = 1; col <= worksheet.LastColumn; col++)
            {
                CellRange range = worksheet.Range[row, col];
                string cellValue = range.Text == null ? string.Empty : range.Text.ToString();
                writer.Write(cellValue + "\t"); // Separate cell data using a tab character
            }
            writer.WriteLine(); // Write a line break
        }

        // Close the writer and save the text file
        writer.Close();

        // Release resources
        workbook.Dispose();
    }
}

The reading result:

Code in C Sharp for Reading Excel Files into DataTables

DataTable is an object that represents a data table in the .NET Framework. It is used to store and manipulate data in memory and can perform operations such as sorting, filtering, modifying, and exporting. Developers can use C# to read Excel file data and write it to the DataTable object, thus facilitating further processing of the data. The following are the steps:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel file using the Workbook.LoadFromFile() method.
3. Get the first worksheet through the Workbook.Worksheets[] property.
4. Export the data from the worksheet to a DataTable object using the Worksheet.ExportDataTable() method.
5. Release resources.

Code Example:

using Spire.Xls;
using System.Data;

namespace ExcelToAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an object of Workbook class
            Workbook workbook = new Workbook();

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

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

            // Export the data from the worksheet to a DataTable object
            DataTable dataTable = worksheet.ExportDataTable();

            workbook.Dispose();
        }
    }
}

C# Code to Read Excel Files and Insert into Databases

This example shows how to use C# code to write Excel worksheet data to a database using an Access database. This method also requires the System.Data.OleDB namespace. If the .NET Framework you are using does not have it, you can install it by entering the following code in the Package Management Console:

Install-Package System.Data.OleDb

The steps for reading an Excel file and inserting into a database are as follows:

1. Import the needed namespaces.
2. Create an object of Workbook class and load an Excel  file using the Workbook.LoadFromFile() method.
3. Retrieve the first worksheet with the Workbook.Worksheets[] property.
4. Get the worksheet name as the table name by accessing the Worksheet.Name property.
5. Retrieve the first row as column names by accessing the CellRange object of the first row with Worksheet.Rows[] property and storing the value of each column in a string array.
6. Connect to the Access database using the OleDbConnection by specifying the connection string and the file path of the database.
7. Create the data table by dynamically generating an SQL query string for creating the table, including the table name, column names, and data types.
8. Execute the create table query using an OleDbCommand object and the ExecuteNonQuery method.
9. Insert the data by iterating through each row of the Excel worksheet (starting from the second row) and constructing an insert statement with parameterized queries. Insert each row of data into the Access database table.
10. Close the database connection and release resources.

Code Example:

using Spire.Xls;
using System.Data.OleDb;

class Program
{
    static void Main(string[] args)
    {
        // Set the Excel file path
        string excelFilePath = "Sample.xlsx";
        // Set the Access database file path
        string accessDbFilePath = "Sample.accdb";

        // Load the Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(excelFilePath);

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

        // Use the worksheet name as the table name
        string tableName = worksheet.Name;

        // Get the first row as column names
        CellRange headerRange = worksheet.Rows[0];
        string[] columnNames = new string[headerRange.Columns.Length];
        for (int i = 0; i < headerRange.Columns.Length; i++)
        {
            columnNames[i] = headerRange.Columns[i].Value.Replace(" ", "_");
        }

        // Connect to the Access database
        string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={accessDbFilePath};Persist Security Info=False;";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();

            // Create the table
            string createTableQuery = $"CREATE TABLE [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}] Memo"))})";
            using (OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, connection))
            {
                createTableCommand.ExecuteNonQuery();
            }

            // Insert data
            string insertQuery = $"INSERT INTO [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}]"))}) VALUES ({string.Join(", ", columnNames.Select(c => $"@{c}"))})";
            using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, connection))
            {
                foreach (CellRange row in worksheet.Rows.Cast().Skip(1))
                {
                    for (int i = 0; i < row.Columns.Length; i++)
                    {
                        insertCommand.Parameters.AddWithValue($"@{columnNames[i]}", row.Columns[i].Value);
                    }

                    insertCommand.ExecuteNonQuery();
                    insertCommand.Parameters.Clear();
                }
            }

            connection.Close();
            workbook.Dispose();
        }
    }
}

The reading and inserting result:

Get a Free License for the API

Spire.XLS for .NET provides a free trial license, allowing developers, businesses, and individual users to explore its powerful Excel file processing capabilities without any usage restrictions or watermarks. By obtaining a free license, users can maximize the potential of Spire.XLS for .NET, expand their file handling capabilities, and significantly improve their efficiency in processing Excel files.

Conclusion

In this article, we explore how to use C# to read Excel files and extract data for various purposes. By leveraging the powerful features of Spire.XLS for .NET, developers can efficiently manipulate Excel data, export it to different formats, and enhance data-driven applications. With step-by-step instructions, code examples, and graphical presentations of processing results, this guide helps optimize file processing efficiency and unlock the full potential of data analysis in C# projects.

• Extract Text from Images or Scanned PDF with C#
• Convert PDF to HTML with Python Code
• Apply Conditional Formatting in Excel with Python
• Convert PDF to Text Effortlessly with Python Code

Conditional formatting is a feature in Microsoft Excel that enables you to apply formatting rules to cells based on specific conditions or criteria. These conditions can be based on cell values, formulas, or other specified criteria. Conditional formatting allows you to dynamically change the appearance of cells, such as font color, cell background color, borders, and data bars, to visually highlight or emphasize certain data points. In this blog, we will explore how to apply conditional formatting to Excel using Python.

We will discuss some commonly used types of conditional formatting rules in Excel:

• Highlight Cell Rules
• Top or Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• Formula-Based Rules

Python Library to Apply Conditional Formatting to Excel

To apply conditional formatting to Excel files using Python, we need to install a Python module that supports this functionality. In this blog post, we will use the Spire.XLS for Python library, which offers a comprehensive set of functions and properties specifically designed for applying conditional formatting rules to Excel files based on various criteria like cell values, formulas, and more.

To install Spire.XLS for Python, you can run the following pip command:

pip install Spire.XLS

Highlight Cell Rules

Highlight Cell Rules are a type of conditional formatting in Excel that allows you to highlight cells based on their values. You can set conditions such as greater than, less than, equal to, between, and more to determine which cells should be formatted. You can choose formatting options like font color, background color, and borders.

Here is an example that shows how to highlight cells containing values greater or less than a specific value in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the sheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["C2:C11"])

# Create the first condition to highlight cells containing values greater than a specific value
condition1 = conditionalFormat.AddCondition()
condition1.FormatType = ConditionalFormatType.CellValue
condition1.FirstFormula = "90"
condition1.Operator = ComparisonOperatorType.Greater
condition1.BackColor = Color.get_Yellow()

# Create the second condition to highlight cells containing values less than a specific value
condition2 = conditionalFormat.AddCondition()
condition2.FormatType = ConditionalFormatType.CellValue
condition2.FirstFormula = "80"
condition2.Operator = ComparisonOperatorType.Less
condition2.BackColor = Color.get_LightGreen()

# Save the result file
workbook.SaveToFile("HighlightCellRules.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

In addition to highlighting cells with values that are greater or less than a specific value, Spire.XLS for Python also supports many other options, for example, you can highlight cells with duplicate or unique values, highlight cells with dates that fall within a specified time period, and many more.

Here is an example that shows how to highlight cells with duplicate or unique values in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["C2:C11"])

# Create the first condition to highlight cells containing duplicate values
condition1 = conditionalFormat.AddCondition()
condition1.FormatType = ConditionalFormatType.DuplicateValues
condition1.BackColor = Color.get_IndianRed()

# Create the second condition to highlight cells containing unique values
condition2 = conditionalFormat.AddCondition()
condition2.FormatType = ConditionalFormatType.UniqueValues
condition2.BackColor = Color.get_Yellow()

# Save the result file
workbook.SaveToFile("HighlightCellRules.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Here is an example that shows how to highlight cells with dates that fall within a specific time period in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example2.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["B2:B9"])

# Create a condition to highlight cells with dates that fall within a specific time period
condition = conditionalFormat.AddTimePeriodCondition(TimePeriodType.Last7Days)
condition.BackColor = Color.get_Yellow()

# Save the result file
workbook.SaveToFile("HighlightCellRules.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Top or Bottom Rules

Top/Bottom Rules are another type of conditional formatting in Excel that enables you to highlight cells containing the highest or lowest values within a range. You can specify the number of top or bottom values to highlight, and Excel will automatically apply the formatting based on the selected rule.

Here is an example that shows how to highlight cells containing the top or bottom ranked values in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["C2:C11"])

# Add the first condition to highlight the top 2 ranked values
condition1 = conditionalFormat.AddTopBottomCondition(TopBottomType.Top, 2)
condition1.BackColor = Color.get_MediumPurple()

# Add the second condition to highlight the bottom 2 ranked values
condition2 = conditionalFormat.AddTopBottomCondition(TopBottomType.Bottom, 2)
condition2.BackColor = Color.get_LightBlue()

# Save the result file
workbook.SaveToFile("TopOrBottomRules.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

In addition to highlighting cells containing the top or bottom ranked values, Spire.XLS for Python is also capable of highlighting cells with values above or below the average value in Excel. You can refer to the following example:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat1 = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat1.AddRange(sheet.Range["C2:C11"])

# Create a condition to highlight cells with values below the average
condition1 = conditionalFormat1.AddAverageCondition(AverageType.Below)
condition1.BackColor = Color.get_SkyBlue()

# Add a conditional format to the worksheet
conditionalFormat2 = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat2.AddRange(sheet.Range["C2:C11"])

# Create a condition to highlight cells with values above the average
condition2 = conditionalFormat2.AddAverageCondition(AverageType.Above)
condition2.BackColor = Color.get_Orange()

# Save the result file
workbook.SaveToFile("HighlightValuesAboveOrBelowAverage.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Data Bars

Data Bars are a visual representation of conditional formatting in Excel. They create horizontal bars within cells that visually represent the relative values of the data. The length of the bar corresponds to the value in the cell, allowing for easy comparison of data points.

Here is an example that shows how to create data bars in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["C2:C11"])

# Add a condition and set its format type to DataBar
condition = conditionalFormat.AddCondition()
condition.FormatType = ConditionalFormatType.DataBar
# Set fill effect for data bars
# condition.DataBar.BarFillType = DataBarFillType.DataBarFillGradient
# Set bar color
condition.DataBar.BarColor = Color.get_SkyBlue()

# Save the result file
workbook.SaveToFile("DataBars.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Color Scales

Color Scales are a type of conditional formatting that applies color gradients to cells based on their values. Excel uses a range of colors to represent the distribution of values within a selected range of cells. Higher values are assigned one color, while lower values are assigned another color, with shades in between for intermediate values. Color Scales provide a visual representation of the data distribution, allowing you to easily identify high and low values, as well as the relative positioning of values within the data set.

Here is an example that shows how to create color scales in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["C2:C11"])

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

# Save the result file
workbook.SaveToFile("ColorScales.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Icon Sets

Icon Sets are a type of conditional formatting that uses visual icons, such as arrows, symbols, or traffic lights, to represent different conditions or values within cells. Excel provides predefined sets of icons that you can apply based on specific criteria or value ranges. For example, you can use arrow icons to indicate whether values are increasing or decreasing, or use traffic light icons to represent the status of certain metrics. Icon Sets offer a visually intuitive way to interpret and compare data based on the assigned icons.

Here is an example that shows how to create icon sets in Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example3.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["A3:R3"])

# Add a condition and set its format type to IconSet
condition = conditionalFormat.AddCondition()
condition.FormatType = ConditionalFormatType.IconSet
# Set the type of icon sets to ThreeArrows
condition.IconSet.IconSetType = IconSetType.ThreeArrows

# Save the result file
workbook.SaveToFile("IconSets.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Formula-Based Rules

Formula-based conditional formatting gives you the flexibility to create custom rules using formulas. You can define complex conditions by utilizing functions, operators, and cell references. This allows for highly tailored formatting based on specific calculations or comparisons.

Here is an example that shows how to apply formula-based conditional formatting to Excel using Python and Spire.XLS for Python:

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add a conditional format to the worksheet
conditionalFormat = sheet.ConditionalFormats.Add()
# Specify the cell range to apply the conditional format
conditionalFormat.AddRange(sheet.Range["A2:C11"])

# Add a condition and set its format type to Formula
condition = conditionalFormat.AddCondition()
condition.FormatType = ConditionalFormatType.Formula
condition.FirstFormula = "=MOD(ROW(),2)=1"
condition.BackColor = Color.get_LightGray()

# Save the result file
workbook.SaveToFile("FormulaBasedRules.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Get a Free License

To fully experience the capabilities of Spire.XLS for Python without any evaluation limitations, you can request a free 30-day trial license.

Conclusion

This blog explained how to apply different types of conditional formatting to Excel using Spire.XLS for Python. If you have any questions, please feel free to post them on our forum or send them to our support team via email.

• Python: Add or Remove Data Validation in Excel Cells
• Python: Merge Excel Files into One
• Python: Hide or Unhide Rows and Columns in Excel
• Python: Convert Excel to PDF