using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace HotelPms.GrpcService.Util
{
    public class Excel
    {
        public static byte[] Export(DataTable dt, string type = "xlsx")
        {            
            const int headerRowIndex = 0;

            // ブック作成
            IWorkbook book;
            switch (type)
            {
                case "xls":
                    book = new HSSFWorkbook();
                    break;
                case "xlsx":
                    book = new XSSFWorkbook();
                    break;
                default:
                    return null;
            }

            // シート作成
            var sheet = book.CreateSheet("Table");

            // ヘッダ行作成
            var headerRow = sheet.CreateRow(0);
            foreach (DataColumn col in dt.Columns)
            {
                ICell cell = headerRow.CreateCell(dt.Columns.IndexOf(col));
                cell.SetCellValue(col.ColumnName);
            }

            // データ行作成
            foreach (DataRow row in dt.Rows)
            {
                var dataRow = sheet.CreateRow(headerRowIndex + 1 + dt.Rows.IndexOf(row));

                foreach (DataColumn col in dt.Columns)
                {
                    var colIndex = dt.Columns.IndexOf(col);
                    if (row[colIndex] == DBNull.Value) continue;

                    ICell cell = dataRow.CreateCell(colIndex);
                    cell.SetCellValue(row[colIndex].ToString());
                }
            }

            // ブックを出力（保存）
            using (var ms = new MemoryStream())
            {
                book.Write(ms, true);
                byte[] data = ms.ToArray();
                ms.Close();
                return data;
            }
        }
    }
}