using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

using OfficeOpenXml;

// https://www.epplussoftware.com/docs/5.6/api/index.html

namespace TopNugetPackages
{
    public class MainEPPlusDriver
    {
        private string Xls1FileName = Directory.GetCurrentDirectory() + @"\..\..\..\Resources\New.xlsx";
        private string Xls2FileName = Directory.GetCurrentDirectory() + @"\..\..\..\Resources\Sample.xlsx";

        public MainEPPlusDriver()
        {
            // NEED license for commercial product
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        private void CreateExcel()
        {
            var newFile = new FileInfo(Xls1FileName);
            var pck = new ExcelPackage(newFile);

            //Add the Content sheet
            var ws = pck.Workbook.Worksheets.Add("Content");
            ws.View.ShowGridLines = false;

            ws.Column(4).OutlineLevel = 1;
            ws.Column(4).Collapsed = true;
            ws.Column(5).OutlineLevel = 1;
            ws.Column(5).Collapsed = true;
            ws.OutLineSummaryRight = true;

            //Headers
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "Size";
            ws.Cells["D1"].Value = "Created";
            ws.Cells["E1"].Value = "Last modified";
            ws.Cells["B1:E1"].Style.Font.Bold = true;

            pck.Save();
        }

        private void ReadSampleExcel()
        {
            if (Path.GetExtension(Xls2FileName) == ".xlsx")
            {
                Stream fs = File.OpenRead(Xls2FileName);
                ExcelPackage package = new ExcelPackage(fs);
                DataTable dt = new DataTable();
                dt = ToDataTable(package);
                List<DataRow> listOfRows = new List<DataRow>();
                listOfRows = dt.AsEnumerable().ToList();
            }
        }

        private DataTable ToDataTable(ExcelPackage package)
        {
            ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
            DataTable table = new DataTable();
            foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
            {
                table.Columns.Add(firstRowCell.Text);
            }

            for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
            {
                var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
                var newRow = table.NewRow();
                foreach (var cell in row)
                {
                    newRow[cell.Start.Column - 1] = cell.Text;
                }
                table.Rows.Add(newRow);
            }
            return table;
        }

        // Main driver for reading data from excel
        public void DoIt()
        {
            CreateExcel();
            ReadSampleExcel();
        }
    }
}