[C#] 匯入 / 匯出 CSV 檔案

之前介紹過如何匯入/滙出Excel 檔案, 當嘗試開啟 CSV (Comma-separated Value) 檔案時, 因為File Header 與xls / xlsx 不符, 故NPOI 會彈出exception 如下:

與直接 CreateObject(“Excel.Application”) 不同, Excel 本身有處理CSV 檔案的機制, 而在.net 環境中, 其實可以直接當text file 讀取檔案, 並利用Array.Split(“,”) 配合迴圈來讀取資料. 但在這邊, 可以介紹一個Helper class CsvHelper 來簡化過程, 而且它還可以像ORM 進行DTO (Date-to-Object) mapping. 在示範中, 利用它作檔案匯入 / 匯出, 代碼如下:

介面 IWorkbookHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lib.Util.WorkbookHelper
{
    public interface IWorkbookHelper
    {
        void resetCurrentDataTable();
        void addImportTableColumn(string colName, string colType);
        void loadFile(string filePath);
        DataTable readFileData(int sheetNum, int offsetLineNum);
        void readFileHeader(int sheetNum, int headerline);
        DataTable readFileToDataTable(string filePath, int sheetNum);
        void exportDataToFile(DataTable dataTable, string fileName);
    }
}

Base class WorkbookHelperBase.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lib.Util.WorkbookHelper
{
    public abstract class WorkbookHelperBase: IWorkbookHelper
    {
        protected DataTable _dataTable;
        protected String _filePath;

        private string currentExcelFileType;

        public WorkbookHelperBase()
        {
            resetCurrentDataTable();
        }

        public void resetCurrentDataTable()
        {
            _dataTable = new DataTable();
        }
        public abstract void loadFile(string filePath);
        public abstract DataTable readFileData(int sheetNum, int offsetLineNum);

        public abstract void readFileHeader(int sheetNum, int headerline);
        public abstract void exportDataToFile(DataTable dataTable, string fileName);
        public void addImportTableColumn(string colName, string colType)
        {
            switch (colType)
            {
                case TableColumnType.String:
                    _dataTable.Columns.Add(colName, typeof(string));
                    break;
                case TableColumnType.Decimal:
                    _dataTable.Columns.Add(colName, typeof(decimal));
                    break;
                default:
                    throw new Exception("Column type not support");
            }
        }

        public DataTable readFileToDataTable(string filePath, int sheetNum)
        {
            this.loadFile(filePath);
            this.resetCurrentDataTable();
            this.readFileHeader(sheetNum, 0);
            return this.readFileData(sheetNum, 1);     //line 0 is header, data start from line 1
        }
    }
}

客制化的 CsvHelper class:

using CsvHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lib.Util.WorkbookHelper
{
    /// <summary>
    /// Helper class to import / export CSV file content from / to DataTable.
    /// </summary>
    public class CSVHelper : WorkbookHelperBase
    {
        /// <summary>
        /// Export Data to File.
        /// </summary>
        /// <param name="dataTable">Data source.</param>
        /// <param name="fileName">Target file name.</param>
        public override void exportDataToFile(DataTable dataTable, string fileName)
        {
            if (File.Exists(fileName))
                throw new InvalidDataException("File " + fileName + " already existed.");

            using (TextWriter textWriter = File.CreateText(fileName))
            {
                using (CsvWriter csvWriter = new CsvWriter(textWriter))
                {
                    List<DataColumn> dataColumns = dataTable.Columns.Cast<DataColumn>().ToList();
                    // Write header to csv file.
                    dataColumns.ForEach(delegate (DataColumn dataColumn) 
                    {
                        csvWriter.WriteField(dataColumn.ColumnName);
                    });
                    csvWriter.NextRecord();

                    // Write data to csv file.
                    dataTable.Rows.Cast<DataRow>().ToList().ForEach(delegate (DataRow dataRow) 
                    {
                        dataColumns.ForEach(delegate (DataColumn dataColumn)
                        {
                            csvWriter.WriteField(dataColumn.DataType, dataRow[dataColumn.ColumnName]);
                        });
                        csvWriter.NextRecord();
                    });
                }
            }
        }
        /// <summary>
        /// Load CSV File.
        /// </summary>
        /// <param name="filePath">File path.</param>
        public override void loadFile(string filePath)
        {
            if (!File.Exists(filePath))
                throw new InvalidDataException("Invalid filePath. Check File path is valid or accessable or not.");
            if (!Path.GetExtension(filePath).ToUpper().Equals(ExcelFileType.csv.ToUpper()))
                throw new InvalidDataException("Invalid file extension detected. Only allow CSV file.");

            this._filePath = filePath;
        }
        /// <summary>
        /// Read CSV File data.
        /// </summary>
        /// <param name="sheetNum">Sheet no. Default 0.</param>
        /// <param name="offsetLineNum">Line of start reading data</param>
        /// <returns></returns>
        public override DataTable readFileData(int sheetNum=0, int offsetLineNum=1)
        {
            using (TextReader textReader = File.OpenText(_filePath))
            {
                using (CsvReader csvReader = new CsvReader(textReader))
                {
                    // Initial CSV reader settings.
                    if (offsetLineNum == 0)
                        csvReader.Configuration.HasHeaderRecord = false;

                    // Initialize column if not do previously. Then get list of column name.
                    if (_dataTable.Columns.Count <= 0)
                        this.readFileHeader(0, 0);
                    List<DataColumn> dataColumns = _dataTable.Columns.Cast<DataColumn>().ToList();

                    // Get data.
                    while (csvReader.Read())
                    {
                        DataRow dataRow = _dataTable.NewRow();
                        dataColumns.ForEach(delegate (DataColumn dataColumn) 
                        {
                            // Update dataRow if value found.
                            if(!String.IsNullOrEmpty(csvReader.GetField(dataColumn.ColumnName)))
                                dataRow[dataColumn] = csvReader.GetField(dataColumn.DataType, dataColumn.ColumnName);
                        });
                        _dataTable.Rows.Add(dataRow);
                    }
                }
            }
            return _dataTable;
        }
        /// <summary>
        /// Read Excel Header to Data Table.
        /// </summary>
        /// <param name="sheetNum">Sheet no. Default 0.</param>
        /// <param name="headerline">Header line. Default 0.</param>
        public override void readFileHeader(int sheetNum=0, int headerline=0)
        {
            using (TextReader textReader = File.OpenText(_filePath))
            {

                using (CsvReader csvReader = new CsvReader(textReader))
                {
                    csvReader.Read();
                    csvReader.FieldHeaders.ToList().ForEach(delegate (String fieldHeader)
                    {
                        string fieldData = csvReader.GetField(fieldHeader);
                        _dataTable.Columns.Add(fieldHeader, (DataTypeUtil.ParseString(fieldData)));
                    });
                }
            }
        }
    }
}

Reference

CsvHelper, Githib, https://github.com/JoshClose/CsvHelper