Exporting database query results to PDF is a common requirement in applications such as reporting, data archiving, and document generation. In these scenarios, SQL query results need to be transformed into structured, readable documents that can be easily shared or printed.

Because database data is inherently tabular, preserving its structure during the export process is essential for maintaining clarity and usability. Without proper layout control, the resulting document can quickly become difficult to read, especially when dealing with large datasets.

This article demonstrates how to convert databases to PDF in C# using Spire.XLS for .NET, including examples on retrieving query results, organizing them into a structured table, and exporting them as a formatted PDF document.

Table of Contents

• Understanding the Task
• Convert Database to PDF Using C# (Step-by-Step)
  • Environment Setup
  • Read Data from Database
  • Formatting & Exporting
• Complete C# Example for Database to PDF Conversion
• Advanced Scenarios
• Common Pitfalls
• Conclusion
• FAQ

1. Understanding the Task

Converting database content to PDF typically involves several key steps:

• Data retrieval: Execute SQL queries and load results into memory
• Data structuring: Organize query results into a consistent tabular format
• PDF export: Generate a document that preserves layout and readability

In practice, this workflow is commonly used for generating reports, creating invoices, or archiving query results, where maintaining a clear and structured presentation of data is essential.

2. Convert Database to PDF Using C# (Step-by-Step)

This section provides a complete workflow for converting database query results into a PDF document, including data retrieval, table structuring, formatting, and export.

2.1 Environment Setup

Before implementing the solution, make sure your development environment is ready:

• .NET environment
  Install Visual Studio or use the .NET CLI with a compatible .NET version (e.g., .NET 6 or later).

• Database access
  Prepare a SQL Server database (or any relational database) and ensure you have a valid connection string. For modern .NET applications, use the recommended SQL client library:

  dotnet add package Microsoft.Data.SqlClient
  

  This package provides the ADO.NET implementation for SQL Server and replaces the legacy System.Data.SqlClient.

• Spire.XLS for .NET Install Spire.XLS via NuGet to handle table formatting and PDF export:

  dotnet add package Spire.XLS
  

  You can also download the Spire.XLS for .NET package and add it to your project manually.

Once configured, you can retrieve data from the database and use Spire.XLS to generate and export PDF documents.

2.2 Read Data from Database

The first step is to execute a SQL query and load the results into a DataTable. This structure preserves the schema and data types of the query result, making it suitable for further transformation.

using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost\\SQLEXPRESS;Database=SalesDB;User ID=demouser;Password=YourPassword;Encrypt=true;TrustServerCertificate=true;";
string query = @"
    SELECT o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE YEAR(o.OrderDate) = 2026;
";

DataTable dataTable = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
    adapter.Fill(dataTable);
}

This example uses Microsoft.Data.SqlClient, the modern SQL client library for .NET, which is recommended over the legacy System.Data.SqlClient.

The SqlDataAdapter acts as a bridge between the database and in-memory data. It executes the query and fills the DataTable without requiring explicit connection management for reading operations.

In practical scenarios, this step can be extended to include:

• Parameterized queries to avoid SQL injection
• Stored procedures for complex data retrieval
• Data filtering and aggregation directly in SQL

By preparing clean and structured data at this stage, you reduce the complexity of downstream formatting and improve overall performance.

For a similar scenario involving exporting database query results to Excel instead of PDF, you can also refer to this guide: Export Database to Excel in C#.

2.3 Import Data and Export to PDF with Formatting

After retrieving the data, the next step is to map it into a worksheet, apply formatting, and export it as a PDF document. This approach leverages worksheet-based layout control to ensure the output remains structured and readable.

using Spire.Xls;
using System.Drawing;

// Create workbook and worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

// Import DataTable with headers
sheet.InsertDataTable(dataTable, true, 1, 1);

// Format header row
CellRange headerRange = sheet.Range[1, 1, 1, dataTable.Columns.Count];
headerRange.Style.Font.IsBold = true;
headerRange.Style.Font.Size = 11;
headerRange.Style.Color = Color.LightGray;

// Apply borders to enhance table structure
CellRange dataRange = sheet.AllocatedRange;
dataRange.BorderAround(LineStyleType.Thin);
dataRange.BorderInside(LineStyleType.Thin);

// Align content for consistency
dataRange.Style.HorizontalAlignment = HorizontalAlignType.Center;
dataRange.Style.VerticalAlignment = VerticalAlignType.Center;

// Auto-fit columns for better layout
sheet.AllocatedRange.AutoFitColumns();

// Center the content horizontally in the page
sheet.PageSetup.CenterHorizontally = true;

// Export to PDF
workbook.SaveToFile("SalesReport_2026.pdf", FileFormat.PDF);

This step combines layout control and PDF generation into a single workflow.

Key points to note:

• Worksheet as layout engine The worksheet acts as a structured canvas where database data is arranged into rows and columns. This ensures the original tabular structure is preserved in the final document.

• Formatting directly impacts PDF output Adjustments such as column width, font style, and borders are not just visual improvements—they determine how the content is rendered in the PDF. Poor formatting can lead to truncated text or unreadable layouts.

• Automatic pagination When exporting, the worksheet content is automatically split across pages based on layout and paper size, which is particularly useful for large datasets.

For further layout optimization, you can enhance the table formatting by:

• Enabling text wrapping for long fields
• Applying number/date formats for better readability

If your project requires more flexible PDF structure control, you can also explore converting DataTable to PDF in C# directly using Spire.PDF for .NET, which provides more advanced document-level layout capabilities for complex reporting needs.

3. Complete C# Example for Converting Databases to PDF

Below is the complete implementation that combines database retrieval, data formatting, and PDF export into a single workflow.

using System;
using System.Data;
using Microsoft.Data.SqlClient;
using Spire.Xls;
using System.Drawing;

class Program
{
    static void Main()
    {
        // Step 1: Retrieve data from database
        string connectionString = "Server=localhost\\SQLEXPRESS;Database=SalesDB;User ID=demouser;Password=YourPassword;Encrypt=true;TrustServerCertificate=true;";
        string query = @"
            SELECT o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount
            FROM Orders o
            JOIN Customers c ON o.CustomerID = c.CustomerID
            WHERE YEAR(o.OrderDate) = 2026;
        ";

        DataTable dataTable = new DataTable();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
            adapter.Fill(dataTable);
        }

        // Step 2: Create workbook and import data
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.Worksheets[0];
        sheet.InsertDataTable(dataTable, true, 1, 1);

        // Step 3: Apply professional formatting
        // Format header row
        CellRange headerRange = sheet.Range[1, 1, 1, dataTable.Columns.Count];
        headerRange.Style.Font.IsBold = true;
        headerRange.Style.Font.Size = 11;
        headerRange.Style.Color = Color.LightGray;

        // Apply borders
        CellRange dataRange = sheet.AllocatedRange;
        dataRange.BorderAround(LineStyleType.Thin);
        dataRange.BorderInside(LineStyleType.Thin);

        // Set alignment
        dataRange.Style.HorizontalAlignment = HorizontalAlignType.Center;
        dataRange.Style.VerticalAlignment = VerticalAlignType.Center;

        // Auto-fit columns
        sheet.AllocatedRange.AutoFitColumns();

        // Center the content horizontally in the pages
        sheet.PageSetup.CenterHorizontally = true;

        // Step 4: Export to PDF
        workbook.SaveToFile("SalesReport_2026.pdf", FileFormat.PDF);

        Console.WriteLine("Database query results successfully exported to PDF.");
    }
}

Below is a preview of the generated PDF:

This example demonstrates an end-to-end workflow from SQL query execution to PDF generation.

4. Advanced Scenarios

In real-world applications, exporting database data to PDF often requires more than just basic conversion. You may need to handle batch exports, improve document readability, or adjust layout settings for better presentation. The following examples demonstrate common enhancements for real-world usage.

Export Multiple Query Results

For scenarios such as batch report generation or scheduled tasks, you may need to execute multiple queries and export each result as a separate PDF document:

string[] queries = {
    "SELECT * FROM Orders WHERE Status = 'Pending'",
    "SELECT * FROM Customers WHERE Region = 'North'"
};

for (int i = 0; i < queries.Length; i++)
{
    DataTable dt = ExecuteQuery(queries[i]);
    Workbook wb = new Workbook();
    Worksheet ws = wb.Worksheets[0];
    ws.InsertDataTable(dt, true, 1, 1);
    ws.AllocatedRange.AutoFitColumns();
    wb.SaveToFile($"Report_{i + 1}.pdf", FileFormat.PDF);
}

This approach is useful for automating report generation where multiple datasets need to be exported independently.

Add Title and Metadata

To improve readability and provide context, you can add a title row above the data before exporting to PDF:

// Insert title row
sheet.InsertRow(1);
sheet.Range[1, 1].Text = "Sales Report - 2026";
sheet.Range[1, 1].Style.Font.IsBold = true;
sheet.Range[1, 1].Style.Font.Size = 14;

// Merge title cells
sheet.Range[1, 1, 1, dataTable.Columns.Count].Merge();

// Auto-fit the title row
sheet.AutoFitRow(1);

The following image shows the generated PDF with the title row applied:

Adding a title helps users quickly understand the context of the document, especially when sharing or printing reports.

Set Page Size, Orientation, and Margins

To ensure the PDF layout fits your data properly, you can configure page size, orientation, and margins before exporting:

// Set the page size and orientation
sheet.PageSetup.PaperSize = PaperSizeType.PaperA4;
sheet.PageSetup.Orientation = PageOrientationType.Portrait;

// Set the page margins
sheet.PageSetup.TopMargin = 0.5f;
sheet.PageSetup.BottomMargin = 0.2f;
sheet.PageSetup.LeftMargin = 0.2f;
sheet.PageSetup.RightMargin = 0.2f;

Adjusting these settings helps prevent content overflow and ensures consistent layout across different reports.

Control Page Layout and Scaling

When working with large tables, you may need to control how content is distributed across pages. By default, content is split automatically, but you can adjust scaling behavior to fit more data within a page.

// Fit content to page width
workbook.ConverterSetting.SheetFitToWidth = true;

// Fit entire sheet into a single page (may reduce readability)
workbook.ConverterSetting.SheetFitToPage = true;

• SheetFitToWidth ensures the table fits within the page width while allowing vertical pagination
• SheetFitToPage scales the entire worksheet to fit into a single page

These settings are useful when generating compact reports, but should be used carefully to avoid making text too small.

Add Headers and Footers

Headers and footers are useful for adding contextual information such as report titles, timestamps, or page numbers:

sheet.PageSetup.LeftHeader = "&\"Arial,Bold\"&16 Sales Report - 2026";
sheet.PageSetup.RightHeader = "&\"Arial,Italic\"&10 Generated on &D";
sheet.PageSetup.CenterFooter = "&\"Arial,Regular\"&16 Page &P of &N";

The following image shows the generated PDF with headers and footers applied:

These elements improve document navigation and are especially valuable for multi-page reports.

Encrypt PDFs

To protect sensitive data, you can apply encryption to the exported PDF:

workbook.ConverterSetting.PdfSecurity.Encrypt("openpsd");

Encryption ensures that only authorized users can access the document, which is important for reports containing confidential or business-critical data.

For more related scenarios involving document export and PDF customization, you can also explore Excel to PDF conversion in C#.

5. Common Pitfalls

Database Connection Issues

Ensure the connection string is correct and the database server is accessible. Verify authentication settings (e.g., SQL authentication or integrated security) and confirm that encryption-related parameters match your environment configuration.

Empty Query Results

Check whether the DataTable contains data before proceeding. Empty result sets may lead to blank PDFs or unexpected formatting behavior.

if (dataTable.Rows.Count == 0)
{
    Console.WriteLine("No data found for the specified query.");
    return;
}

In production scenarios, you may also choose to generate a placeholder PDF or log the issue instead of exiting the process.

Column Width Overflow

When working with long text fields, AutoFitColumns() may produce excessively wide columns, which can negatively affect PDF layout.

To improve readability, consider:

• Setting a maximum column width
• Enabling text wrapping for long content
• Manually adjusting key columns based on data type

This is especially important when exporting large datasets with variable-length text.

Missing Font Support

If the exported PDF contains special characters (e.g., non-Latin text) or custom fonts, ensure the required fonts are installed and accessible at runtime.

Missing fonts may cause text rendering issues or fallback substitutions, which can affect document appearance and readability.

Unexpected PDF Layout

If the exported PDF layout appears compressed or improperly scaled, check page setup and scaling options such as SheetFitToWidth or SheetFitToPage.

Improper scaling may cause content to appear too small or distort the original table structure.

Conclusion

This article demonstrated a practical approach to converting database query results to PDF in C#. By combining structured data retrieval with worksheet-based formatting, you can generate clear and professional documents directly from SQL data.

This method is particularly effective for report generation and data presentation scenarios where maintaining table structure and readability is essential.

If you are evaluating Spire.XLS, you can request a free temporary license to remove evaluation limitations during development.

FAQ

Can Spire.XLS export database data to PDF without third-party tools?

Yes. Spire.XLS performs all operations independently and does not require Microsoft Office or any other external tools.

How do I handle large datasets when exporting to PDF?

For large datasets, consider paginating the results or filtering the query to retrieve only necessary data. You can also adjust PDF page settings to optimize output size.

Can I customize the PDF page layout?

Yes. Spire.XLS allows you to configure page settings including orientation, margins, and paper size before exporting to PDF.

Does this method work with databases other than SQL Server?

Yes. The approach works with any database that supports ADO.NET data providers, including MySQL, PostgreSQL, and Oracle. Simply use the appropriate connection class and data adapter.

Should I use Microsoft.Data.SqlClient or System.Data.SqlClient?

For modern .NET applications, it is recommended to use Microsoft.Data.SqlClient. It is actively maintained and provides better support for newer SQL Server features, while System.Data.SqlClient is considered legacy and no longer receives major updates.

Converting between Excel and JSON formats is a valuable skill for developers dealing with data exchange, API integration, and modern web applications.

Excel files (.xls, .xlsx) are excellent for organizing and analyzing tabular data, while JSON (JavaScript Object Notation) is lightweight, human-readable, and ideal for transmitting data across platforms.

In this step-by-step tutorial, you’ll learn how to seamlessly convert Excel to JSON and JSON to Excel in C# using the Spire.XLS for .NET library. Whether you’re exporting Excel data for frontend apps, feeding structured datasets into APIs, or importing JSON into spreadsheets, this guide provides clear explanations, complete code samples, and tips to help you get started quickly.

What You Will Learn

• Why Convert Between Excel and JSON Formats
• Prerequisites
• Installing Required Packages
• How to Convert Excel to JSON in C# .NET (Step-by-Step)
• How to Convert JSON to Excel in C# .NET (Step-by-Step)
• Tips and Best Practices
• Conclusion
• Further Reading
• FAQs

Why Convert Between Excel and JSON Formats?

Converting between Excel and JSON can be beneficial for several reasons:

• Data Exchange: JSON is a standard format for data interchange in web applications, making it easier to share data across platforms.
• Integration with APIs: Many web APIs require data in JSON format, necessitating conversion from Excel for seamless integration.
• Lightweight and Compact: JSON files are generally smaller in size compared to Excel files, leading to faster data transfer and reduced storage needs.
• Readability: JSON is human-readable and easier to understand, which can simplify data analysis and troubleshooting.
• Compatibility with NoSQL Databases: JSON format is commonly used in NoSQL databases, facilitating easy data migration and storage.

Prerequisites

Before we start, ensure you have the following:

• Visual Studio or any C# development IDE installed.
• .NET Framework or .NET Core installed.
• Spire.XLS package installed for handling Excel files.
• Newtonsoft.Json package installed for handling JSON serialization and deserialization.

Installing Required Packages

You can install the required packages using NuGet Package Manager in Visual Studio:

Install-Package Spire.XLS
Install-Package Newtonsoft.Json

How to Convert Excel to JSON in C# .NET (Step-by-Step)

Exporting Excel files to JSON format in C# involves extracting data from Excel spreadsheets and transforming it into a structured JSON string. This process is particularly useful for applications that require data interchange between web services or databases. Below are detailed steps to guide you through the conversion process.

Steps to Export Excel to JSON

1. Load the Excel File:
   • Begin by creating a Workbook object that will hold the Excel file's content using the Spire.XLS library. Load the Excel file into this object.
2. Access the Desired Worksheet:
   • Identify and access the specific worksheet from which you want to extract data. This is done by referencing the appropriate index of the Worksheets collection.
3. Export to DataTable:
   • Utilize the ExportDataTable() method to convert the worksheet's content into a DataTable. This provides a structured representation of the data, making it easier to manipulate.
4. Serialize to JSON:
   • Use the Newtonsoft.Json library to serialize the DataTable into a JSON string. This step involves converting the structured data into a JSON format, which is human-readable and suitable for web applications.
5. Save the JSON to a File:
   • Finally, write the generated JSON string to a file. This allows for easy access and reuse of the data in future applications or processes.

Complete Code Example: Excel to JSON

Here’s a complete code example demonstrating the process:

using Newtonsoft.Json;
using Spire.Xls;
using System.Data;
using System.IO;

namespace ConvertExcelToJSON
{
    class Program
    {        
        static void Main(string[] args)
        {
            // Path to the Excel file
            string excelFilePath = @"Sample.xlsx";

            // Create a new Workbook instance
            Workbook workbook = new Workbook();

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

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

            // Export the worksheet content to a DataTable
            DataTable dataTable = worksheet.ExportDataTable();

            // Convert the DataTable to a JSON string
            string jsonResult = JsonConvert.SerializeObject(dataTable, Formatting.Indented);

            // Save JSON string to a text file
            File.WriteAllText("output.txt", jsonResult);
        }
    }
}

How to Convert JSON to Excel in C# .NET (Step-by-Step)

Importing JSON data into an Excel file is a valuable process, especially when you need to analyze or present data in a more user-friendly format. JSON is easy for humans to read and write, and easy for machines to parse and generate. However, for many users, Excel remains the preferred tool for data analysis, reporting, and visualization.

In the following steps, we will outline the process of importing JSON into Excel, enabling you to effectively utilize your JSON data within Excel for further analysis and reporting.

Steps to Import JSON into Excel

1. Read the JSON String:
   • Start by reading the JSON data from a file or other sources. This could include API responses, local files, or even hardcoded strings for testing purposes.
2. Deserialize to DataTable:
   • Use the Newtonsoft.Json library to deserialize the JSON string into a DataTable. This structured format makes it easy to manipulate data before inserting it into Excel.
3. Create a New Excel Workbook:
   • Initialize a new Workbook instance using the Spire.XLS library. This workbook will serve as the container for your Excel data.
4. Insert the DataTable into the Worksheet:
   • Use the InsertDataTable() method to transfer the contents of the DataTable into the first worksheet of the workbook. This method allows you to include column headers and organize the data neatly.
5. Apply Optional Formatting:
   • Enhance the visual appeal of your Excel file by applying formatting to headers and data cells. This step involves defining styles for fonts, background colors, and borders, making the data easier to read.
6. Save the Workbook:
   • Finally, save the populated workbook as an Excel file. Choose an appropriate file format (e.g., .xlsx) to ensure compatibility with modern Excel versions.

Complete Code Example: JSON to Excel

Here’s a complete code snippet demonstrating the conversion process:

using Newtonsoft.Json;
using Spire.Xls;
using System.Data;
using System.Drawing;

namespace ConvertJSONToExcel
{
    class Program
    {        
        static void Main(string[] args)
        {
            // Sample JSON data
            string json = @"
        [
            {""Name"":""John Smith"",""Age"":30,""Department"":""Sales"",""StartDate"":""2020-05-12"",""FullTime"":true},
            {""Name"":""Jane Doe"",""Age"":25,""Department"":""Marketing"",""StartDate"":""2021-09-01"",""FullTime"":false},
            {""Name"":""Michael Lee"",""Age"":40,""Department"":""IT"",""StartDate"":""2018-03-15"",""FullTime"":true},
            {""Name"":""Emily Davis"",""Age"":35,""Department"":""Finance"",""StartDate"":""2019-07-20"",""FullTime"":true}
        ]";

            // Deserialize JSON into DataTable
            DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json);

            // Create a new Excel workbook
            Workbook workbook = new Workbook();

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

            // Insert DataTable into worksheet with column headers
            worksheet.InsertDataTable(dataTable, true, 1, 1);

            // (Optional) Applying formatting to Excel data
            // Set style for heading row
            CellStyle headerStyle = workbook.Styles.Add("HeaderStyle");
            headerStyle.Font.IsBold = true;
            headerStyle.Font.Size = 12;
            headerStyle.Font.Color = Color.White;
            headerStyle.HorizontalAlignment = HorizontalAlignType.Center;
            headerStyle.VerticalAlignment = VerticalAlignType.Center;
            headerStyle.Color = Color.DarkBlue;

            int colCount = dataTable.Columns.Count;
            for (int c = 1; c <= colCount; c++)
            {
                worksheet.Range[1, c].CellStyleName = "HeaderStyle";
            }

            // Set style for data cells
            CellStyle dataStyle = workbook.Styles.Add("DataStyle");
            dataStyle.HorizontalAlignment = HorizontalAlignType.Center;
            dataStyle.VerticalAlignment = VerticalAlignType.Center;
            dataStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            dataStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            dataStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            dataStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

            int rowCount = dataTable.Rows.Count;
            worksheet.Range[2, 1, rowCount + 1, colCount].CellStyleName = "DataStyle";

            // Auto-fit column widths
            worksheet.AllocatedRange.AutoFitColumns();

            // Save Excel file
            workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);
            // Release resources
            workbook.Dispose();
        }
    }
}

Tips and Best Practices

When converting between Excel and JSON, following best practices can help ensure data integrity and usability. Here are some key tips to keep in mind:

• Validate Data Types: Ensure that data types (e.g., dates, numbers) are correctly formatted to avoid issues during conversion.
• Handle Empty Cells: Decide how to treat empty cells (e.g., convert to null or omit) to maintain data integrity.
• Use Consistent Naming Conventions: Standardize column names in Excel for clear and consistent JSON keys.
• Test Thoroughly: Always test the conversion processes to ensure valid JSON output and accurate Excel representation.
• Include Headers: When converting JSON to Excel, always insert headers for improved readability and usability.

Conclusion

Converting Excel to JSON and JSON to Excel is a common but critical operation in modern C# development, especially for applications involving data exchange and API integration. Using Spire.XLS together with Newtonsoft.Json simplifies this process with intuitive APIs and robust functionality.

This guide has walked you through every step—from installing necessary packages to implementing complete converters—with clear explanations and sample code. With these tools and knowledge, you can confidently integrate Excel-JSON conversion into your applications, improving flexibility and interoperability.

Further Reading

• Spire.XLS for .NET Documentation
• Newtonsoft.Json Official Documentation

FAQs

Q1: How to convert multiple worksheets to JSON at once?

You can iterate through the Workbook.Worksheets collection and export each worksheet’s data individually, supporting batch Excel to JSON conversion.

Q2: How to customize JSON output formatting?

JsonConvert.SerializeObject allows you to set indentation, camelCase naming, or ignore null values. You can also use custom converters for more control.

Q3: How to improve readability when converting JSON to Excel?

Keep column headers, set alignment, apply borders and styles to generate a clear and easy-to-read Excel report.

Q4: Is this method compatible with .NET Core?

Yes, it is fully compatible. Both Spire.XLS and Newtonsoft.Json support .NET Core and .NET Framework, making it usable in various C# projects.