CSharp: Convert CSV to XLS Using Open XML SDK

using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Globalization;
using CsvHelper;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace CsvToXlsConverter
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Parse command line arguments
                string inputPath = null;
                string outputPath = null;
                Encoding encoding = Encoding.UTF8;
                char delimiter = ',';

                for (int i = 0; i < args.Length; i++)
                {
                    switch (args[i].ToLower())
                    {
                        case "-i":
                        case "--input":
                            if (i + 1 < args.Length)
                                inputPath = args[++i];
                            break;
                        case "-o":
                        case "--output":
                            if (i + 1 < args.Length)
                                outputPath = args[++i];
                            break;
                        case "-e":
                        case "--encoding":
                            if (i + 1 < args.Length)
                            {
                                string encodingName = args[++i];
                                try
                                {
                                    encoding = Encoding.GetEncoding(encodingName);
                                }
                                catch
                                {
                                    Console.WriteLine($"Warning: Unsupported encoding '{encodingName}'. Using default UTF-8.");
                                }
                            }
                            break;
                        case "-d":
                        case "--delimiter":
                            if (i + 1 < args.Length)
                            {
                                string delimiterStr = args[++i];
                                if (delimiterStr.Length == 1)
                                    delimiter = delimiterStr[0];
                                else if (delimiterStr.Equals("tab", StringComparison.OrdinalIgnoreCase))
                                    delimiter = '\t';
                                else
                                    Console.WriteLine($"Warning: Unsupported delimiter '{delimiterStr}'. Using default comma.");
                            }
                            break;
                        case "-h":
                        case "--help":
                            ShowHelp();
                            return;
                    }
                }

                // Validate input and output paths
                if (string.IsNullOrEmpty(inputPath))
                    throw new ArgumentException("Input file path is required. Use -i or --input option.");

                if (string.IsNullOrEmpty(outputPath))
                    outputPath = Path.ChangeExtension(inputPath, ".xls");

                if (!File.Exists(inputPath))
                    throw new FileNotFoundException("Input file not found.", inputPath);

                // Check if output directory exists
                string outputDirectory = Path.GetDirectoryName(outputPath);
                if (!string.IsNullOrEmpty(outputDirectory) && !Directory.Exists(outputDirectory))
                    Directory.CreateDirectory(outputDirectory);

                // Convert CSV to XLS
                Console.WriteLine("CSV to XLS Converter");
                Console.WriteLine("====================");
                Console.WriteLine($"Input file: {inputPath}");
                Console.WriteLine($"Output file: {outputPath}");
                Console.WriteLine($"Encoding: {encoding.EncodingName}");
                Console.WriteLine($"Delimiter: {(delimiter == '\t' ? "Tab" : delimiter.ToString())}");
                Console.WriteLine();

                DateTime startTime = DateTime.Now;

                try
                {
                    // Read CSV
                    Console.WriteLine("Reading CSV file...");
                    DataTable dataTable = ReadCsv(inputPath, encoding, delimiter);
                    Console.WriteLine($"Successfully read {dataTable.Rows.Count} rows and {dataTable.Columns.Count} columns.");

                    // Convert to XLS
                    Console.WriteLine("Converting to XLS format...");
                    ConvertDataTableToXls(dataTable, outputPath);

                    TimeSpan duration = DateTime.Now - startTime;

                    Console.WriteLine();
                    Console.WriteLine($"Conversion completed successfully in {duration.TotalSeconds:F2} seconds.");
                    Console.WriteLine($"Output file saved to: {outputPath}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine();
                    throw new Exception($"Conversion failed: {ex.Message}", ex);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                Console.WriteLine();
                Console.WriteLine("Use --help for usage information.");
            }
        }

        /// <summary>
        /// Reads a CSV file and returns its contents as a DataTable
        /// </summary>
        public static DataTable ReadCsv(string filePath, Encoding encoding, char delimiter)
        {
            try
            {
                using (var reader = new StreamReader(filePath, encoding))
                using (var csv = new CsvReader(reader, new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture)
                {
                    Delimiter = delimiter.ToString(),
                    HasHeaderRecord = true,
                    IgnoreBlankLines = true,
                    TrimOptions = CsvHelper.Configuration.TrimOptions.Trim
                }))
                {
                    using (var dr = new CsvDataReader(csv))
                    {
                        var dt = new DataTable();
                        dt.Load(dr);
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to read CSV file: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// Converts a DataTable to an XLS file using Open XML SDK
        /// </summary>
        public static void ConvertDataTableToXls(DataTable dataTable, string outputPath)
        {
            try
            {
                // Create a new spreadsheet document
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
                {
                    // Add a WorkbookPart to the document
                    WorkbookPart workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    // Add a WorksheetPart to the WorkbookPart
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook
                    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                    // Append a new worksheet and associate it with the workbook
                    Sheet sheet = new Sheet()
                    {
                        Id = workbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "Sheet1"
                    };
                    sheets.Append(sheet);

                    // Get the SheetData object
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    // Add header row
                    Row headerRow = new Row();
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        Cell cell = CreateCell(column.ColumnName, CellValues.String);
                        headerRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(headerRow);

                    // Add data rows
                    foreach (DataRow row in dataTable.Rows)
                    {
                        Row dataRow = new Row();
                        foreach (var item in row.ItemArray)
                        {
                            CellValues cellType = GetCellValueType(item);
                            string cellValue = GetCellValueAsString(item, cellType);
                            Cell cell = CreateCell(cellValue, cellType);
                            dataRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(dataRow);
                    }

                    // Save the workbook
                    workbookPart.Workbook.Save();
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to create XLS file: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// Creates a new Excel cell with the specified value and type
        /// </summary>
        private static Cell CreateCell(string value, CellValues cellType)
        {
            Cell cell = new Cell();
            cell.DataType = new EnumValue<CellValues>(cellType);
            cell.CellValue = new CellValue(value);
            return cell;
        }

        /// <summary>
        /// Determines the cell value type based on the object type
        /// </summary>
        private static CellValues GetCellValueType(object value)
        {
            if (value == DBNull.Value)
                return CellValues.String;

            Type type = value.GetType();

            if (type == typeof(int) || type == typeof(long) || type == typeof(short) || type == typeof(byte))
                return CellValues.Number;
            else if (type == typeof(float) || type == typeof(double) || type == typeof(decimal))
                return CellValues.Number;
            else if (type == typeof(DateTime))
                return CellValues.Date;
            else if (type == typeof(bool))
                return CellValues.Boolean;
            else
                return CellValues.String;
        }

        /// <summary>
        /// Converts an object to its string representation based on the cell type
        /// </summary>
        private static string GetCellValueAsString(object value, CellValues cellType)
        {
            if (value == DBNull.Value)
                return string.Empty;

            switch (cellType)
            {
                case CellValues.Boolean://错误的
                    return (bool)value ? "1" : "0";
                case CellValues.Date:
                    DateTime dateValue = (DateTime)value;
                    // Excel stores dates as OLE Automation dates
                    return dateValue.ToOADate().ToString(CultureInfo.InvariantCulture);
                case CellValues.Number:
                    return Convert.ToString(value, CultureInfo.InvariantCulture);
                default:
                    return Convert.ToString(value);
            }
        }

        /// <summary>
        /// Shows help information
        /// </summary>
        private static void ShowHelp()
        {
            Console.WriteLine("CSV to XLS Converter");
            Console.WriteLine("Converts CSV files to XLS (Excel 97-2003) format using Open XML SDK.");
            Console.WriteLine();
            Console.WriteLine("Usage: CsvToXlsConverter [options]");
            Console.WriteLine();
            Console.WriteLine("Options:");
            Console.WriteLine("  -i, --input      Input CSV file path (required)");
            Console.WriteLine("  -o, --output     Output XLS file path (optional, defaults to input path with .xls extension)");
            Console.WriteLine("  -e, --encoding   Encoding of the CSV file (optional, defaults to UTF-8)");
            Console.WriteLine("                   Examples: UTF-8, ASCII, Windows-1252, etc.");
            Console.WriteLine("  -d, --delimiter  Field delimiter character (optional, defaults to comma)");
            Console.WriteLine("                   Use 'tab' for tab-delimited files");
            Console.WriteLine("  -h, --help       Show this help message");
            Console.WriteLine();
            Console.WriteLine("Examples:");
            Console.WriteLine("  CsvToXlsConverter -i data.csv");
            Console.WriteLine("  CsvToXlsConverter -i input.csv -o output.xls");
            Console.WriteLine("  CsvToXlsConverter -i data.tsv -d tab -e Windows-1252");
        }
    }
}

　　

 

 /// <summary>
 /// 
 /// </summary>
 /// <param name="inpFile"></param>
 /// <param name="outFile"></param>
 /// <returns></returns>
 static bool ConvertCsvToXls(string inpFile, string outFile)
 {
     bool ok = false;
     Encoding encoding = Encoding.UTF8;
     char delimiter = ',';
     try
     {
         DataTable dataTable = ReadCsv(inpFile, encoding, delimiter);
         ok = ConvertDataTableToXls(dataTable, outFile);
        
     }
     catch (Exception ex)
     {
         ex.Message.ToString();

     }
     return ok;
 }


         /// <summary>
         /// Reads a CSV file and returns its contents as a DataTable
         /// </summary>
     static DataTable ReadCsv(string filePath, Encoding encoding, char delimiter)
     {
             try
             {
                 using (var reader = new StreamReader(filePath, encoding))
                 using (var csv = new CsvReader(reader, new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture)
                 {
                     Delimiter = delimiter.ToString(),
                     HasHeaderRecord = true,
                     IgnoreBlankLines = true,
                     TrimOptions = CsvHelper.Configuration.TrimOptions.Trim
                 }))
                 {
                     using (var dr = new CsvDataReader(csv))
                     {
                         var dt = new DataTable();
                         dt.Load(dr);
                         return dt;
                     }
                 }
             }
             catch (Exception ex)
             {
                 throw new Exception($"Failed to read CSV file: {ex.Message}", ex);
             }
     }
 /// <summary>
 /// 
 /// </summary>
 /// <param name="dataTable"></param>
 /// <param name="outputPath"></param>
 /// <returns></returns>
 /// <exception cref="Exception"></exception>
 static bool ConvertDataTableToXls(DataTable dataTable, string outputPath)
 {
     bool ok = false;    
     try
     {
         // Create a new spreadsheet document
         using (SpreadsheetDocument document = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
         {
             // Add a WorkbookPart to the document
             WorkbookPart workbookPart = document.AddWorkbookPart();
             workbookPart.Workbook = new Workbook();

             // Add a WorksheetPart to the WorkbookPart
             WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
             worksheetPart.Worksheet = new Worksheet(new SheetData());

             // Add Sheets to the Workbook
             Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

             // Append a new worksheet and associate it with the workbook
             Sheet sheet = new Sheet()
             {
                 Id = workbookPart.GetIdOfPart(worksheetPart),
                 SheetId = 1,
                 Name = "Sheet1"
             };
             sheets.Append(sheet);

             // Get the SheetData object
             SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

             // Add header row
             Row headerRow = new Row();
             foreach (DataColumn column in dataTable.Columns)
             {
                 Cell cell = CreateCell(column.ColumnName, CellValues.String);
                 headerRow.AppendChild(cell);
             }
             sheetData.AppendChild(headerRow);

             // Add data rows
             foreach (DataRow row in dataTable.Rows)
             {
                 Row dataRow = new Row();
                 foreach (var item in row.ItemArray)
                 {
                     CellValues cellType = GetCellValueType(item);
                     string cellValue = GetCellValueAsString(item, cellType);
                     Cell cell = CreateCell(cellValue, cellType);
                     dataRow.AppendChild(cell);
                 }
                 sheetData.AppendChild(dataRow);
             }

             // Save the workbook
             workbookPart.Workbook.Save();
             ok = true;  
         }
     }
     catch (Exception ex)
     {
         throw new Exception($"Failed to create XLS file: {ex.Message}", ex);
     }
     return ok;
 }

 /// <summary>
 /// Creates a new Excel cell with the specified value and type
 /// </summary>
 private static Cell CreateCell(string value, CellValues cellType)
 {
     Cell cell = new Cell();
     cell.DataType = new EnumValue<CellValues>(cellType);
     cell.CellValue = new CellValue(value);
     return cell;
 }

 /// <summary>
 /// Determines the cell value type based on the object type
 /// </summary>
 private static CellValues GetCellValueType(object value)
 {
     if (value == DBNull.Value)
         return CellValues.String;

     Type type = value.GetType();

     if (type == typeof(int) || type == typeof(long) || type == typeof(short) || type == typeof(byte))
         return CellValues.Number;
     else if (type == typeof(float) || type == typeof(double) || type == typeof(decimal))
         return CellValues.Number;
     else if (type == typeof(DateTime))
         return CellValues.Date;
     else if (type == typeof(bool))
         return CellValues.Boolean;
     else
         return CellValues.String;
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="value"></param>
 /// <param name="cellType"></param>
 /// <returns></returns>
 private static string GetCellValueAsString(object value, DocumentFormat.OpenXml.Spreadsheet.CellValues cellType)
 {
     if (value == DBNull.Value || value == null)
         return string.Empty;

     if (cellType == DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean)
     {
         return (value is bool boolVal && boolVal) ? "1" : "0";
     }
     else if (cellType == DocumentFormat.OpenXml.Spreadsheet.CellValues.Number)
     {
         if (value is DateTime dateVal)
         {
             return dateVal.ToOADate().ToString(CultureInfo.InvariantCulture);
         }
         return Convert.ToString(value, CultureInfo.InvariantCulture);
     }
     else if (cellType == DocumentFormat.OpenXml.Spreadsheet.CellValues.String ||
              cellType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
     {
         return Convert.ToString(value) ?? string.Empty;
     }
     else
     {
         return string.Empty;
     }
 }