DEV Community

jelizaveta
jelizaveta

Posted on

How to Convert Between Excel and CSV in C#: Based on Spire.XLS

In modern data processing and analysis, Excel and CSV, as two of the most common data formats, play pivotal roles. Excel, with its powerful formatting and multi-worksheet support, is the preferred choice for business reports and data analysis; while CSV (Comma-Separated Values), with its simplicity, universality, and ease of parsing, is widely used for data exchange and lightweight storage. However, in practical development, developers often face the challenge of data conversion between C# Excel to CSV or CSV to Excel, especially when dealing with large files, complex encodings, or the need to maintain data integrity.

These challenges may include: how to correctly handle different encodings (such as UTF-8, GBK), how to specify or identify the correct delimiter, how to efficiently process large volumes of data to avoid performance bottlenecks, and how to ensure data types are not lost or distorted during the conversion process.

Fortunately, with the help of powerful third-party libraries, we can greatly simplify this process. This article will delve into how to use Spire.XLS for .NET, an efficient and reliable C# component, to easily achieve bidirectional conversion between Excel and CSV files, helping you solve the practical pain points in data conversion.

C# Convert Excel to CSV Using Spire.XLS

Spire.XLS for .NET is a feature-rich Excel component that allows developers to create, read, edit, and convert Excel files in C# applications without requiring Microsoft Office to be installed. First, you need to install it into your project via the NuGet package manager:

Install-Package Spire.XLS
Enter fullscreen mode Exit fullscreen mode

Core Steps and Code Example

The core steps to convert Excel to CSV include: loading the Excel file, selecting the target worksheet, and then saving it as a CSV with specified delimiter and encoding format.

  1. Load the Excel file: Use the Workbook object to load an existing .xlsx or .xls file.
  2. Select the worksheet: Spire.XLS allows you to access every worksheet in the workbook. You can select a specific worksheet for conversion or iterate through all worksheets.
  3. Save as CSV: Utilize the Worksheet.SaveToFile() method, specifying the output filename, CSV separator type, and encoding.

Here is an example of converting the first worksheet of an Excel file to CSV:

csharp

using Spire.Xls;
using System.Text;

public class ExcelToCsvConverter
{
    public static void ConvertExcelToCsv(string excelFilePath, string csvFilePath)
    {
        // 1. Create a Workbook object and load the Excel file
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(excelFilePath);

        // 2. Get the first worksheet (you can also iterate through all worksheets)
        Worksheet sheet = workbook.Worksheets[0];

        // 3. Save the worksheet as a CSV file
        sheet.SaveToFile(csvFilePath, ",", Encoding.UTF8);

        Console.WriteLine($"Excel file '{excelFilePath}' has been successfully converted to CSV file '{csvFilePath}'.");
    }

    public static void Main(string[] args)
    {
        // Example usage
        string inputExcel = "Input.xlsx"; // Ensure this file exists
        string outputCsv = "Output.csv";
        ConvertExcelToCsv(inputExcel, outputCsv);
    }
}
Enter fullscreen mode Exit fullscreen mode

Notes:

  • Encoding Issues: Be sure to correctly choose the Encoding type (e.g., Encoding.UTF8 is a universal and recommended choice, Encoding.GetEncoding("GB2312") for Chinese environments) based on your data source and the requirements of the target system.
  • Multiple Worksheets: If your Excel file contains multiple worksheets and you want to convert them into separate CSV files, you can iterate through the workbook.Worksheets collection and perform the SaveToFile operation for each worksheet.

C# Convert CSV to Excel Using Spire.XLS

The reverse conversion, generating an Excel file from a CSV file, is also a common requirement in data processing. Spire.XLS for .NET also provides great convenience in this process.

Core Steps and Code Example

The core steps to convert CSV to Excel include: loading the CSV file (specifying the delimiter and encoding), and then saving it in Excel format.

  1. Load the CSV file: Use the Workbook.LoadFromFile() method, explicitly specifying the delimiter and the starting cell for writing the data.
  2. Save as Excel: Use the SaveToFile() method to save the loaded data in .xlsx or .xls format.

Here is an example of converting a CSV file to Excel:

csharp

using Spire.Xls;
using System.Text;

public class CsvToExcelConverter
{
    public static void ConvertCsvToExcel(string csvFilePath, string excelFilePath)
    {
        // 1. Create a Workbook object
        Workbook workbook = new Workbook();

        // 2. Load the CSV file, specifying the comma delimiter and the starting cell (row 1, column 1) for data
        workbook.LoadFromFile(csvFilePath, ",", 1, 1);

        // 3. Save the loaded data as an Excel file (.xlsx format)
        workbook.SaveToFile(excelFilePath, FileFormat.XLSX);

        Console.WriteLine($"CSV file '{csvFilePath}' has been successfully converted to Excel file '{excelFilePath}'.");
    }

    public static void Main(string[] args)
    {
        // Example usage
        string inputCsv = "Input.csv"; // Ensure this file exists
        string outputExcel = "Output.xlsx";
        ConvertCsvToExcel(inputCsv, outputExcel);
    }
}
Enter fullscreen mode Exit fullscreen mode

Notes:

  • Data Type Inference: Spire.XLS attempts to infer cell data types (such as numbers, dates, text) when loading CSV, but for complex or non-standard data, manual adjustment of cell formats might be needed afterwards.
  • Large CSV Files: Spire.XLS is optimized for performance, but when processing extremely large CSV files, it is still recommended to test and tune for memory usage and processing time.

Summary

Through the introduction in this article, we have seen how Spire.XLS for .NET provides developers in the C# environment with a powerful and flexible tool to meet the data conversion needs of C# Excel to CSV and CSV to Excel . Whether handling encoding, specifying delimiters, or simplifying complex API calls, Spire.XLS demonstrates excellent ease of use, comprehensive functionality, and superior performance.

It not only solves the common pain points in traditional data conversion but also greatly improves development efficiency through its intuitive API design. We highly recommend that you download and try Spire.XLS for .NET to experience its powerful capabilities in the field of data processing firsthand. We believe it will become an indispensable data conversion tool in your C# projects, bringing unprecedented convenience and efficiency to your data management tasks.

Top comments (0)