C# 解析 Excel 并且生成 Csv 文件代码分析

今天工作中遇到一个需求，就是获取 excel 里面的内容，并且把 excel 另存为 csv，因为本人以前未接触过，所以下面整理出来的代码均来自网络，具体参考链接已丢失，原作者保留所有权利！

例子：
using System;
using System.Data;

namespace ExportExcelToCode
{
    class ExcelOperater
    {
        public void Operater()
        {
            // Excel 路径
            string excelPath = "";
            // Csv 存放路径
            string csvPath = "";

            // 获取 Excel Sheet 名称列表
            string[] sheetNameList = ExcelUtils.GetSheetNameList(excelPath);

            if (sheetNameList != null && sheetNameList.Length > 0)
            {
                foreach (string sheetName in sheetNameList)
                {
                    string itemName = sheetName.TrimEnd(new char[] { '$' });

                    // 解析 Excel 为 DataTable 对象
                    DataTable dataTable = ExcelUtils.ExcelToDataTable(excelPath, itemName);
                    if (dataTable != null && dataTable.Rows.Count > 0)
                    {
                        // 生成 Csv 文件
                        ExcelUtils.ExcelToCsv(excelPath, csvPath, itemName, "|#|", 0);
                    }
                }
            }
        }
    }
}
</div>

ExcelUtils.cs 文件

using System;  
using System.Data;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportExcelToCode
{
    public partial class ExcelUtils
    {
        /// <summary>
        /// 获取 Sheet 名称
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string[] GetSheetNameList(string filePath)
        {
            try
            {
                string connectionText = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1';";

                System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(connectionText);

                oleDbConnection.Open();

                System.Data.DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); ;

                string[] sheetNameList = new string[dataTable.Rows.Count];

                for (int index = 0; index < dataTable.Rows.Count; index++)
                {
                    sheetNameList[index] = dataTable.Rows[index][2].ToString();
                }

                oleDbConnection.Close();

                return sheetNameList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Excel 转 DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelToDataTable(string filePath, string sheetName)
        {
            try
            {
                string connectionText = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1';";