---
lang: ja-jp
breaks: true
---
# C# NPOI を使用したExcelファイルに関するユーティリティークラス　2021-04-15

## 環境
* Visual Studio 2017
* Windows Server 2019
* Excel2007
* .Net Framework 4.7.2
* NPOI 2.4.1

## NPOIExcelUtil

    /// <summary>
    /// 
    /// </summary>
    public static class NPOIExcelUtil
    {
        public const int DefExcelMaxColNumber = 16384;

        private static readonly Dictionary<string/*項目名英字*/, int/*インデックス*/> DicXlsColIndex = CreateDicXlsColIndex();
        private static Dictionary<string/*項目名英字*/, int/*インデックス*/> CreateDicXlsColIndex()
        {
            var dicXlsColIndex = new Dictionary<string, int>();
            for (int i = 0; i < DefExcelMaxColNumber; i++)
            {
                string ascii = GetStrColumnName(i + 1);
                dicXlsColIndex.Add(ascii, i);
            }
            return dicXlsColIndex;
        }

        /// <summary>
        /// 1→A、2→B
        /// </summary>
        /// <param name="lngColNum"></param>
        /// <returns></returns>
        public static string GetStrColumnName(long lngColNum)
        {
            string str = "";
            // インデックス数値に変換
            lngColNum--;
            do
            {
                str = Convert.ToChar(lngColNum % 26 + 0x41) + str;
            }
            while ((lngColNum = lngColNum / 26 - 1) != -1);
            return str;
        }

        public static int GetIdxColumn(string ascii)
        {
            return DicXlsColIndex[ascii];
        }

        public static int GetIdxRow(int intRowNum)
        {
            return intRowNum - 1;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xlsFile"></param>
        /// <param name="intMaxRowNumber"></param>
        /// <param name="intColCount"></param>
        /// <returns></returns>
        public static List<ExcelRow> GetXlsData(
            FileInfo xlsFile,
            int? intMaxRowNumber = null,
            int? intColCount = null
            )
        {
            // 開いている場合もあるので、TEMPにコピーしてから読み取る。
            var datetime = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            var tempdir  = Path.GetTempPath() + datetime;
            var tempPath = tempdir + Path.DirectorySeparatorChar + xlsFile.Name;

            // 一時ファイル用のディレクトリを作成する。
            if (Directory.Exists(tempdir) == false)
            {
                Directory.CreateDirectory(tempdir);
            }

            FileInfo fInfoTempXlsFile = xlsFile.CopyTo(tempPath);

            List<NPOIExcelRow> lstRows = new List<NPOIExcelRow>();
            using (FileStream stream = fInfoTempXlsFile.OpenRead())
            {
                IWorkbook hssfworkbook = WorkbookFactory.Create(stream);
                try
                {
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    int intLastRowNumber = sheet.LastRowNum;
                    if (   intMaxRowNumber != null
                        && intMaxRowNumber < intLastRowNumber
                        )
                    {
                        intLastRowNumber = intMaxRowNumber.Value;
                    }
                    for (int i = 0; i < intLastRowNumber; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        float heightInPoints = row.HeightInPoints;
                        List<ICell> cells = new List<ICell>();
                        if (row != null)
                        {
                            foreach (ICell cell in row)
                            {
                                while (cells.Count < cell.ColumnIndex)
                                {
                                    cells.Add(null as ICell);
                                }
                                cells.Insert(cell.ColumnIndex, cell);
                            }
                        }
                        lstRows.Add(
                            new NPOIExcelRow(
                                cells, 
                                heightInPoints
                                )
                            );
                    }
                }
                finally
                {
                    hssfworkbook.Close();
                }
            }

            int intMaxCol = lstRows.Max(n => n.LstCell.Count);
            if (   intColCount != null
                && intColCount > intMaxCol
                )
            {
                intMaxCol = intColCount.Value;
            }
            List<ExcelRow> xlsData = new List<ExcelRow>();
            foreach (NPOIExcelRow row in lstRows)
            {
                string[] strings              = new string[intMaxCol];
                Color?[] colorFillForegrounds = new Color?[intMaxCol];
                Color?[] colorFillBackgrounds = new Color?[intMaxCol];
                int idxCol = 0;
                foreach (ICell cell in row.LstCell)
                {
                    if (strings.Length <= idxCol)
                    {
                        break;
                    }
                    if (cell != null)
                    {
                        // セルの値を取得
                        if (cell.CellType == CellType.Formula)
                        {
                            if (cell.CachedFormulaResultType == CellType.Numeric)
                            {
                                strings[idxCol] = cell.NumericCellValue.ToString();
                            }
                            else if (cell.CachedFormulaResultType == CellType.String)
                            {
                                strings[idxCol] = cell.StringCellValue;
                            }
                            else
                            {
                                strings[idxCol] = cell.ToString();
                            }
                        }
                        else if (cell.CellType == CellType.Numeric)
                        {
                            string val = null;

                            string strTemp = cell.ToString();
                            long lngTemp = 0;
                            if (long.TryParse(strTemp, out lngTemp))
                            {
                                val = lngTemp.ToString();
                            }
                            else
                            {
                                if (cell.DateCellValue != null)
                                {
                                    if (cell.DateCellValue.TimeOfDay == new TimeSpan(0))
                                    {
                                        if (cell.DateCellValue.Ticks != 0)
                                        {
                                            val = cell.DateCellValue.ToShortDateString();
                                        }
                                    }
                                }
                            }

                            if (val == null)
                            {
                                val = cell.ToString();
                            }

                            strings[idxCol] = val;
                        }
                        else
                        {
                            strings[idxCol] = cell.ToString();
                        }

                        // セルの背景色を取得
                        {
                            Color? color = NPOIExcelUtil.GetColorFromICell(cell, n => n.FillForegroundColorColor);
                            if (color != null)
                            {
                                colorFillForegrounds[idxCol] = color;
                            }
                        }
                        {
                            Color? color = NPOIExcelUtil.GetColorFromICell(cell, n => n.FillBackgroundColorColor);
                            if (color != null)
                            {
                                colorFillBackgrounds[idxCol] = color;
                            }
                        }
                    }
                    idxCol++;
                }
                xlsData.Add(new ExcelRow(
                        strings, 
                        row.FltHeightInPoints,
                        colorFillForegrounds,
                        colorFillBackgrounds
                        )
                    );
            }

            // 作成した一時ディレクトリを削除。
            if (Directory.Exists(tempdir))
            {
                Directory.Delete(tempdir, true);
            }

            return xlsData;
        }

        public static IWorkbook GetWorkbook_Write(
            FileInfo filExcel
            )
        {
            IWorkbook writeBook = null;
            // 書込用Excelファイルの読込
            using (FileStream fstream = filExcel.OpenRead())
            {
                writeBook = WorkbookFactory.Create(fstream);
            }
            return writeBook;
        }

        public static void WriteWorkbook(
            IWorkbook writeBook,
            FileInfo xlsxNew
            )
        {
            if (xlsxNew.Exists)
            {
                xlsxNew.Delete();
            }
            using (FileStream fstream = xlsxNew.OpenWrite())
            {
                writeBook.Write(fstream);
            }
        }

        public static void WriteWorkbook(
            FileInfo filExcelInput,
            FileInfo filExcelOutput,
            Action<IWorkbook, ISheet> actWriteWorkbook
            )
        {
            IWorkbook writeBook = GetWorkbook_Write(filExcelInput);
            try
            {
                ISheet sheet = writeBook.GetSheetAt(0);

                if (actWriteWorkbook != null)
                {
                    actWriteWorkbook(writeBook, sheet);
                }

                //sheet.ShiftRows(10, sheet.LastRowNum, 1, true, false);
                //IRow rowOrg = sheet.GetRow(9);
                //IRow rowNew = sheet.CreateRow(10);
                //for (int i = 0; i< rowOrg.LastCellNum; i++)
                //{
                //    rowNew.CreateCell(i);
                //}

                //rowOrg.CopyRowTo(10);

                //for (int i=0; i<30; i++)
                //{
                //    CopyRow(writeBook, sheet, 6, 7, 30);
                //}

                // 属性のコピー
                //row2.CreateCell(3).CellStyle = row1.GetCell(0).CellStyle()

                //string strNewExcelFile = filExcelInput.Directory.FullName + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(filExcelInput.FullName) + "_New" + Path.GetExtension(filExcelInput.FullName);
                FileInfo xlsxNew = filExcelOutput;// new FileInfo(strNewExcelFile);
                WriteWorkbook(writeBook, xlsxNew);
            }
            finally
            {
                writeBook.Close();
            }

        }

        /// <summary>
        /// HSSFRow Copy Command
        /// 
        /// Description:  Inserts a existing row into a new row, will automatically push down
        ///               any existing rows.  Copy is done cell by cell and supports, and the
        ///               command tries to copy all properties available (style, merged cells, values, etc...)
        /// </summary>
        /// <param name="workbook">Workbook containing the worksheet that will be changed</param>
        /// <param name="worksheet">WorkSheet containing rows to be copied</param>
        /// <param name="sourceRowNum">Source Row Number</param>
        /// <param name="destinationRowNum">Destination Row Number</param>
        public static void CopyRow(
            IWorkbook workbook, 
            ISheet worksheet, 
            int sourceRowNum, 
            int destinationRowNum,
            float? heightInPoints = null,
            Color? fillForegroundColor = null,
            Color? fillBackgroundColor = null,
            Dictionary<int/* 項目のインデックス */, Color> fillForegroundColors = null,
            Dictionary<int/* 項目のインデックス */, Color> fillBackgroundColors = null
            )
        {
            // Get the source / new row
            IRow newRow    = worksheet.GetRow(destinationRowNum);
            IRow sourceRow = worksheet.GetRow(sourceRowNum);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                // コピー先にデータが存在している場合は、行を新たに追加する。
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1, copyRowHeight: true, resetOriginalRowHeight: false);
            }
            // 新しい行を生成する。
            newRow = worksheet.CreateRow(destinationRowNum);

            if (heightInPoints != null)
            {
                newRow.HeightInPoints = heightInPoints.Value;
            }

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = newRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                ICellStyle newCellStyle = workbook.CreateCellStyle();
                {
                    newCellStyle.CloneStyleFrom(oldCell.CellStyle);

                    NPOIExcelUtil.SetICellStyleColor(newCellStyle, fillForegroundColor, (s, c) => s.SetFillForegroundColor(c));
                    NPOIExcelUtil.SetICellStyleColor(newCellStyle, fillBackgroundColor, (s, c) => s.SetFillBackgroundColor(c));

                    if (fillForegroundColors != null)
                    {
                        Color color;
                        if (fillForegroundColors.TryGetValue(newCell.ColumnIndex, out color))
                        {
                            NPOIExcelUtil.SetICellStyleColor(newCellStyle, color, (s, c) => s.SetFillForegroundColor(c));
                        }
                    }
                    if (fillBackgroundColors != null)
                    {
                        Color color;
                        if (fillBackgroundColors.TryGetValue(newCell.ColumnIndex, out color))
                        {
                            NPOIExcelUtil.SetICellStyleColor(newCellStyle, color, (s, c) => s.SetFillBackgroundColor(c));
                        }
                    }

                    newCell.CellStyle = newCellStyle;
                    //newCell.CellStyle = oldCell.CellStyle;
                }

                // If there is a cell comment, copy
                if (oldCell.CellComment != null)
                {
                    newCell.CellComment = oldCell.CellComment;
                }

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null)
                {
                    newCell.Hyperlink = oldCell.Hyperlink;
                }

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.Blank:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            // セルの結合をコピー
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress != null)
                {
                    if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                    {
                        CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                            newRow.RowNum,
                            newRow.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow),
                            cellRangeAddress.FirstColumn,
                            cellRangeAddress.LastColumn
                            );
                        worksheet.AddMergedRegion(newCellRangeAddress);
                    }
                }
            }

            // 条件付き書式のコピー
            ISheetConditionalFormatting iConditions = worksheet.SheetConditionalFormatting;
            for (int i = 0; i < iConditions.NumConditionalFormattings; i++)
            {
                IConditionalFormatting cond = iConditions.GetConditionalFormattingAt(i);
                if (cond != null)
                {
                    CellRangeAddress[] rangs = cond.GetFormattingRanges();
                    List<CellRangeAddress> lstRangeNew = new List<CellRangeAddress>();
                    foreach (CellRangeAddress rang in rangs)
                    {
                        if (rang.FirstRow == sourceRow.RowNum)
                        {
                            CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                                newRow.RowNum,
                                newRow.RowNum + (rang.FirstRow - rang.LastRow),
                                rang.FirstColumn,
                                rang.LastColumn
                                );
                            lstRangeNew.Add(newCellRangeAddress);
                        }
                    }

                    if (lstRangeNew.Any())
                    {
                        List<IConditionalFormattingRule> lstRuleNew = new List<IConditionalFormattingRule>();
                        for (int j = 0; j < cond.NumberOfRules; j++)
                        {
                            IConditionalFormattingRule rule = cond.GetRule(j);
                            lstRuleNew.Add(rule);
                        }
                        iConditions.AddConditionalFormatting(lstRangeNew.ToArray(), lstRuleNew.ToArray());
                    }
                }
            }
        }

        public static Color? GetColorFromICell(
            ICell cell, 
            Func<XSSFCellStyle, IColor> fncGetIColor
            )
        {
            Color? color = null;

            if (   cell != null 
                && cell.CellStyle is XSSFCellStyle
                )
            {
                var cellStyle = cell.CellStyle as XSSFCellStyle;

                if (fncGetIColor != null)
                {
                    IColor iColor = fncGetIColor(cellStyle);
                    if (   iColor     != null 
                        && iColor.RGB != null
                        )
                    {
                        color = Color.FromArgb(
                            red   : iColor.RGB[0],
                            green : iColor.RGB[1],
                            blue  : iColor.RGB[2]
                            );
                    }
                }
            }
            return color;
        }

        public static void SetICellStyleColor(
            ICellStyle iCelStylel, 
            Color? color, 
            Action<XSSFCellStyle, XSSFColor> actSetColor
            )
        {
            if (iCelStylel is XSSFCellStyle)
            {
                var cellStyle = iCelStylel as XSSFCellStyle;
                if (actSetColor != null)
                {
                    if (color != null)
                    {
                        actSetColor(cellStyle, new XSSFColor(color.Value));
                    }
                }
            }
        }
    }

## ExcelRow

    public class ExcelRow
    {
        private string[] m_rows;
        private float m_heightInPoints;
        private Color?[] m_colorFillForegrounds;
        private Color?[] m_colorFillBackgrounds;
        /// <summary>
        ///
        /// </summary>
        /// <param name="rows                 "></param>
        /// <param name="heightInPoints       "></param>
        /// <param name="colorFillForegrounds "></param>
        /// <param name="colorFillBackgrounds "></param>
        public ExcelRow(
        string[] rows,
        float heightInPoints,
        Color?[] colorFillForegrounds,
        Color?[] colorFillBackgrounds
        )
         : base()
        {
            this.m_rows = rows;
            this.m_heightInPoints = heightInPoints;
            this.m_colorFillForegrounds = colorFillForegrounds;
            this.m_colorFillBackgrounds = colorFillBackgrounds;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="org"></param>
        public ExcelRow(
        ExcelRow org
        )
         : base()
        {
            this.m_rows = org.m_rows;
            this.m_heightInPoints = org.m_heightInPoints;
            this.m_colorFillForegrounds = org.m_colorFillForegrounds;
            this.m_colorFillBackgrounds = org.m_colorFillBackgrounds;
        }
        public string[] Rows { get { return this.m_rows; } protected set { this.m_rows = value; } }
        public float HeightInPoints { get { return this.m_heightInPoints; } protected set { this.m_heightInPoints = value; } }
        public Color?[] ColorFillForegrounds { get { return this.m_colorFillForegrounds; } protected set { this.m_colorFillForegrounds = value; } }
        public Color?[] ColorFillBackgrounds { get { return this.m_colorFillBackgrounds; } protected set { this.m_colorFillBackgrounds = value; } }
        public override string ToString()
        {
            return new StringBuilder()
                .Append("rows：").Append(m_rows).AppendLine("、")
                .Append("heightInPoints：").Append(m_heightInPoints).AppendLine("、")
                .Append("colorFillForegrounds：").Append(m_colorFillForegrounds).AppendLine("、")
                .Append("colorFillBackgrounds：").Append(m_colorFillBackgrounds).AppendLine("、")
                .ToString();
        }
    }

## IndexCell

    public class IndexCell
    {
        public IndexCell(
            int rowNum,
            string colName
            )
            : base()
        {
            this.m_row = NPOIExcelUtil.GetIdxRow(rowNum);
            this.m_col = NPOIExcelUtil.GetIdxColumn(colName);
        }

        private int m_row;
        private int m_col;
        /// <summary>
        ///
        /// </summary>
        /// <param name="row "></param>
        /// <param name="col "></param>
        public IndexCell(
        int row,
        int col
        )
         : base()
        {
            this.m_row = row;
            this.m_col = col;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="org"></param>
        public IndexCell(
        IndexCell org
        )
         : base()
        {
            this.m_row = org.m_row;
            this.m_col = org.m_col;
        }
        public int Row { get { return this.m_row; } protected set { this.m_row = value; } }
        public int Col { get { return this.m_col; } protected set { this.m_col = value; } }
        public override string ToString()
        {
            return new StringBuilder()
                .Append("row：").Append(m_row).AppendLine("、")
                .Append("col：").Append(m_col).AppendLine("、")
                .ToString();
        }
    }

## NPOIExcelRow

    class NPOIExcelRow
    {
        private List<ICell> m_lstCell;
        private float m_fltHeightInPoints;
        /// <summary>
        ///
        /// </summary>
        /// <param name="lstCell           "></param>
        /// <param name="fltHeightInPoints "></param>
        public NPOIExcelRow(
        List<ICell> lstCell,
        float fltHeightInPoints
        )
         : base()
        {
            this.m_lstCell = lstCell;
            this.m_fltHeightInPoints = fltHeightInPoints;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="org"></param>
        public NPOIExcelRow(
        NPOIExcelRow org
        )
         : base()
        {
            this.m_lstCell = org.m_lstCell;
            this.m_fltHeightInPoints = org.m_fltHeightInPoints;
        }
        public List<ICell> LstCell { get { return this.m_lstCell; } protected set { this.m_lstCell = value; } }
        public float FltHeightInPoints { get { return this.m_fltHeightInPoints; } protected set { this.m_fltHeightInPoints = value; } }
        public override string ToString()
        {
            return new StringBuilder()
                .Append("lstCell：").Append(m_lstCell != null ? "[" + string.Join("][", m_lstCell) + "]" : "").AppendLine("、")
                .Append("fltHeightInPoints：").Append(m_fltHeightInPoints).AppendLine("、")
                .ToString();
        }
    }

###### tags: `NPOI` `C#` `Excel2007`