using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

using System.Linq;

using System.Text;

namespace LeaRun.Utilities

{

    public static class NpoiHelper

    {

        public static Stream RenderDataTableToExcel(DataTable SourceTable)

        {

            HSSFWorkbook workbook = new HSSFWorkbook();

            MemoryStream ms = new MemoryStream();

            ISheet sheet = workbook.CreateSheet();

            IRow headerRow = sheet.CreateRow();

            // handling header.

            foreach (DataColumn column in SourceTable.Columns)

                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.

            int rowIndex = ;

            foreach (DataRow row in SourceTable.Rows)

            {

                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)

                {

                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

                }

                rowIndex++;

            }

            workbook.Write(ms);

            ms.Flush();

            ms.Position = ;

            sheet = null;

            headerRow = null;

            workbook = null;

            return ms;

        }

        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)

        {

            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);

            ISheet sheet = workbook.GetSheetAt(SheetIndex);

            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(HeaderRowIndex);

            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)

            {

                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

                table.Columns.Add(column);

            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)

            {

                IRow row = sheet.GetRow(i);

                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)

                {

                    if (row.GetCell(j) != null)

                        dataRow[j] = row.GetCell(j).ToString();

                }

                table.Rows.Add(dataRow);

            }

            ExcelFileStream.Close();

            workbook = null;

            sheet = null;

            return table;

        }

    }

}