---
lang: ja-jp
breaks: true
---
# ClosedXML C# UWP CSVを読み込んだデータからDataTableを作成し、Excelファイルを作成する。　2021-04-24

## NOPIと比較
* 出力スピードが倍くらい遅い。
* 事前にDataTableを作成する為、メモリを３倍以上浪費する。
* DataTableのメモリ使用量はたかが知れてるので、実際にはClosedXML側で大量のメモリを使用している。

    public static class ClosedXMLExcelUtil
    {
        public const int DefExcelMaxColNumber = 16384;

        public const string DefSheetName = "Sheet1";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="lstCsvRec"></param>
        /// <param name="hasHeader"></param>
        /// <param name="sheetname"></param>
        /// <param name="dicCellType"></param>
        public static void CreateNewBookFromCsv(
            FileInfo filePath,
            List<List<string>> lstCsvRec,
            bool hasHeader = true,
            string sheetname = null,
            Dictionary<string/* ColumnsName */, CellType> dicCellType = null
            )
        {
            DataTable dtb = new DataTable();
            try
            {
                if (string.IsNullOrWhiteSpace(sheetname) == false)
                {
                    dtb.TableName = sheetname;
                }
                else
                {
                    dtb.TableName = DefSheetName;
                }

                var dicColumns = new Dictionary<int/* columns index */, string/* columns name */>();
                if (hasHeader && dicCellType != null && dicCellType.Any())
                {
                    List<string> headers = lstCsvRec.FirstOrDefault();
                    if (headers != null)
                    {
                        int columnIndex = 0;
                        foreach (string columnsName in headers)
                        {
                            Type typ = typeof(string);

                            CellType cellType;
                            if (dicCellType.TryGetValue(columnsName, out cellType))
                            {
                                switch (cellType)
                                {
                                    case CellType.Unknown:
                                        break;
                                    case CellType.Numeric:
                                        typ = typeof(double);
                                        break;
                                    case CellType.String:
                                        break;
                                    case CellType.Formula:
                                        break;
                                    case CellType.Blank:
                                        break;
                                    case CellType.Boolean:
                                        typ = typeof(bool);
                                        break;
                                    case CellType.Error:
                                        break;
                                }
                            }
                            dtb.Columns.Add(new DataColumn(columnsName, typ));

                            dicColumns.Add(columnIndex, columnsName);
                            columnIndex++;
                        }
                    }
                }

                {
                    int rowIndex = 0;
                    foreach (List<string> rec in lstCsvRec.Skip(1))
                    {
                        DataRow drw = dtb.NewRow();

                        object[] itemArray = new object[dtb.Columns.Count];
                        int columnIndex = 0;
                        foreach (string val in rec)
                        {
                            var cellType = CellType.Unknown;
                            {
                                if (dicCellType != null && dicCellType.Any())
                                {
                                    string colmnsName = "";
                                    dicColumns.TryGetValue(columnIndex, out colmnsName);

                                    CellType cellType_conf;
                                    if (dicCellType.TryGetValue(colmnsName, out cellType_conf))
                                    {
                                        cellType = cellType_conf;
                                    }
                                }
                            }

                            object objVal = null;
                            switch (cellType)
                            {
                                case CellType.Unknown:
                                    objVal = val;
                                    break;
                                case CellType.Numeric:
                                    {
                                        double dbl;
                                        if (double.TryParse(val, out dbl))
                                        {
                                            objVal = dbl;
                                        }
                                        else
                                        {
                                            Serilog.Log.Logger.Error("double.TryParse error. [" + rowIndex + "][" + columnIndex + "][" + cellType + "][" + val + "]");
                                        }
                                    }
                                    break;
                                case CellType.String:
                                    objVal = val;
                                    break;
                                case CellType.Formula:
                                    break;
                                case CellType.Blank:
                                    break;
                                case CellType.Boolean:
                                    {
                                        bool dbl;
                                        if (bool.TryParse(val, out dbl))
                                        {
                                            objVal = dbl;
                                        }
                                        else
                                        {
                                            Serilog.Log.Logger.Error("bool.TryParse error. [" + rowIndex + "][" + columnIndex + "][" + cellType + "][" + val + "]");
                                        }
                                    }
                                    break;
                                case CellType.Error:
                                    break;
                                default:
                                    objVal = val;
                                    break;
                            }

                            itemArray[columnIndex] = objVal;

                            columnIndex++;
                        }

                        drw.ItemArray = itemArray;
                        dtb.Rows.Add(drw);

                        rowIndex++;
                    }
                }

                using (var workbook = new XLWorkbook())
                {
                    var worksheet = workbook.Worksheets.Add(dtb);
                    workbook.SaveAs(filePath.FullName);
                }
            }
            finally
            {
                if (dtb != null)
                {
                    dtb.Clear();
                    dtb.Dispose();
                }
            }
        }
    }

###### tags: `ClosedXML` `C#` `UWP`