CsvToXlsx.cs

using System;
using System.IO;
using System.Globalization;
using CsvHelper;
using CsvHelper.Configuration;
using ClosedXML.Excel;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            // Replace these with your actual input and output file paths
            string inputCsvPath = @"input.csv";
            string outputXlsxPath = @"output.xlsx";

            // Convert CSV to XLSX
            ConvertCsvToXlsx(inputCsvPath, outputXlsxPath);

            Console.WriteLine($"Successfully converted {inputCsvPath} to {outputXlsxPath}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }

    static void ConvertCsvToXlsx(string inputCsvPath, string outputXlsxPath)
    {
        // Configure CSV reading with pipe delimiter
        var csvConfig = new CsvConfiguration(CultureInfo.InvariantCulture)
        {
            Delimiter = "|",
            HasHeaderRecord = true
        };

        // Read CSV data
        using var reader = new StreamReader(inputCsvPath);
        using var csv = new CsvReader(reader, csvConfig);

        // Create a dynamic list of records
        var records = csv.GetRecords<dynamic>().ToList();

        // Create Excel workbook
        using var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Data");

        // Write headers
        if (records.Any())
        {
            var headers = ((IDictionary<string, object>)records[0]).Keys.ToList();
            for (int i = 0; i < headers.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = headers[i];
            }

            // Write data rows
            for (int row = 0; row < records.Count; row++)
            {
                var record = (IDictionary<string, object>)records[row];
                for (int col = 0; col < headers.Count; col++)
                {
                    worksheet.Cell(row + 2, col + 1).Value = record[headers[col]];
                }
            }
        }

        // Save the workbook
        workbook.SaveAs(outputXlsxPath);
    }

   
    static void ConvertCsvToXlsxStream(string inputCsvPath, string outputXlsxPath)
    {
        // Configure CSV reading with pipe delimiter
        var csvConfig = new CsvConfiguration(CultureInfo.InvariantCulture)
        {
            Delimiter = "|",
            HasHeaderRecord = true
        };

        // Create Excel workbook
        using var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Data");

        // Read CSV data using IEnumerable directly
        using var reader = new StreamReader(inputCsvPath);
        using var csv = new CsvReader(reader, csvConfig);

        // Get the records as an IEnumerable
        var records = csv.GetRecords<dynamic>();

        // Flag to write headers only once
        bool headersWritten = false;

        // Iterate through records efficiently
        foreach (var record in records)
        {
            // Write headers on first iteration
            if (!headersWritten)
            {
                var headers = ((IDictionary<string, object>)record).Keys.ToList();
                for (int i = 0; i < headers.Count; i++)
                {
                    worksheet.Cell(1, i + 1).Value = headers[i];
                }
                headersWritten = true;
            }

            // Write data rows
            int currentRow = worksheet.LastRowUsed()?.RowNumber() + 1 ?? 2;
            var recordDict = (IDictionary<string, object>)record;
            
            int colIndex = 1;
            foreach (var value in recordDict.Values)
            {
                worksheet.Cell(currentRow, colIndex).Value = value;
                colIndex++;
            }
        }

        // Save the workbook
        workbook.SaveAs(outputXlsxPath);
    }

}