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);

                    }

                }

            }

        }

    }

}

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';";

                string selectText = string.Format("select * from [{0}$]", sheetName);
                DataSet dataSet = new DataSet();
                System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(connectionText);
                oleDbConnection.Open();
                System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectText, connectionText);

                oleDbDataAdapter.Fill(dataSet, sheetName);
                oleDbConnection.Close();
                return dataSet.Tables[sheetName];

            }

            catch (Exception ex)

            {

                return null;

            }

        }
        /// <summary>

        /// Excel 转 Csv

        /// </summary>

        /// <param name="sourceExcelPathAndName"></param>

        /// <param name="targetCSVPathAndName"></param>

        /// <param name="excelSheetName"></param>

        /// <param name="columnDelimeter"></param>

        /// <param name="headerRowsToSkip"></param>

        /// <returns></returns>

        public static bool ExcelToCsv(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip)

        {

            Excel.Application oXL = null;

            Excel.Workbooks workbooks = null;

            Workbook mWorkBook = null;

            Sheets mWorkSheets = null;

            Worksheet mWSheet = null;
            try

            {

                oXL = new Excel.Application();

                oXL.Visible = false;

                oXL.DisplayAlerts = false;

                workbooks = oXL.Workbooks;

                mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                mWorkSheets = mWorkBook.Worksheets;

                mWSheet = (Worksheet)mWorkSheets.get_Item(excelSheetName);

                Excel.Range range = mWSheet.UsedRange;

                Excel.Range rngCurrentRow;

                for (int i = 0; i < headerRowsToSkip; i++)

                {

                    rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;

                    rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);

                }

                range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                mWorkBook.SaveAs(targetCSVPathAndName, Excel.XlFileFormat.xlCSV,

                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,

                Type.Missing, Type.Missing, Type.Missing,

                Type.Missing, false);

                return true;

            }

            catch (Exception ex)

            {

                return false;

            }

            finally

            {

                if (mWSheet != null) mWSheet = null;

                if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);

                if (mWorkBook != null) mWorkBook = null;

                if (oXL != null) oXL.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);

                if (oXL != null) oXL = null;

                GC.WaitForPendingFinalizers();

                GC.Collect();

                GC.WaitForPendingFinalizers();

                GC.Collect();

            }

        }

    }

}