How to Convert TXT to Excel in C#

In data processing and management scenarios, efficiently transforming raw text (TXT) files into structured Excel spreadsheets is a common requirement. For developers who are automating reports or processing log files, converting TXT to Excel using C# streamlines data organization and analysis. This guide explores how to achieve this using Spire.XLS for .NET, a powerful library designed to handle Excel XLS or XLSX files without requiring Microsoft Office.

• Why Convert TXT to Excel Programmatically?
• How to Convert Text Files to Excel in C# (Step-by-Step Guide)
  • Install Spire.XLS for .NET
  • Import a Text File into Excel Using C#
• Pro Tips for TXT to Excel Conversion

Why Convert TXT to Excel Programmatically?

Text files are simple but lack the analytical power of Excel. Key advantages of converting TXT to XLS or XLSX format include:

• Automation: Process large or recurring files without manual intervention.
• Data Structuring: Organize raw text into rows, columns, and sheets.
• Advanced Features: Leverage Excel formulas, charts, and pivot tables.
• Integration: Embed conversion feature into .NET applications or APIs.

How to Convert Text Files to Excel in C# (Step-by-Step Guide)

Install Spire.XLS for .NET

Spire.XLS for .NET is a professional Excel document processing component, provides efficient and convenient APIs that allow developers to achieve TXT to Excel conversion through simple code.

Before getting started, you can choose one of these methods to install the library:

Method 1: NuGet Package Manager

• Open your project in Visual Studio.
• Right-click on the project in the Solution Explorer and select "Manage NuGet Packages."
• Search for "Spire.XLS" and click "Install".

Method 2: Package Manager Console

• Go to "Tools > NuGet Package Manager > Package Manager Console."
• Run the following command in the console:

  PM> Install-Package Spire.XLS

Method 3: Manual Installation with DLL Files

• Visit the Spire.XLS Download Page and get the latest version.
• Extract the files and then add the Spire.Xls.dll to your project.

Import a Text File into Excel Using C#

Follow the below steps to write the data in a txt file into an Excel worksheet:

• Read TXT File: use the File.ReadAllLines() method to read all lines in a text file and returns them as an array of strings.
• Parse each line:
  • Use the string.Trim() method to remove the leading/trailing whitespaces.
  • Use the string.Split() method to split the data based on specified delimiters.
  • Add the split text data to a list.
• Create a Workbook instance and get a worksheet
• Write Data to specified cells:
  • Iterate through the rows and columns in the list.
  • Assign the data in the list to the corresponding Excel cells through the Worksheet.Range[].Value property.
• Save the Excel File.

Code Example:

using Spire.Xls;
using System.IO;
using System.Collections.Generic;

class TxtToExcelConverter
{
    static void Main()
    {
        // Open a text file and read all lines in it
        string[] lines = File.ReadAllLines("Data.txt");

        // Create a list to store the data in text file
        List data = new List();

        // Split data into rows and columns and add to the list
        foreach (string line in lines)
        {
            data.Add(line.Trim().Split('\t')); // Adjust delimiter as needed
        }

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

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

        // Iterate through the rows and columns in the data list
        for (int row = 0; row < data.Count; row++)
        {
            for (int col = 0; col < data[row].Length; col++)
            {
                // Write the text data in specified cells
                sheet.Range[row + 1, col + 1].Value = data[row][col];

                // Set the header row to Bold
                sheet.Range[1, col + 1].Style.Font.IsBold = true;
            }
        }

        // Autofit columns
        sheet.AllocatedRange.AutoFitColumns();

        // Save the Excel file
        workbook.SaveToFile("TXTtoExcel.xlsx", ExcelVersion.Version2016);
        workbook.Dispose();
    }
}

Result:

Pro Tips for TXT to Excel Conversion

• Handling Different Delimiters:

If your TXT file uses a different delimiter (e.g., space, comma, semicolon), modify the parameter of the Split(params char[] separator) method.

• Format Cells:

After a text file being converted to an Excel file, you can take advantage of the Spire.XLS library’s rich features to format cells, such as setting the background colors, adding cell borders, applying number formats, etc.

Conclusion

By following this step-by-step guide, you can efficiently transform unstructured text data into organized Excel spreadsheets, which is ideal for data analysis, reporting, and management. Remember to optimize your implementation for your specific delimiters and leverage Spire.XLS's advanced features for complex conversion scenarios.

Get a Free License

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