NPOI read and write excel file in c#

checkboxlist disable multiple selection Here is an another sample class to retrieve excel datas and populate in .net webpage using NPOI dlls, Without Microsoft read and write any version excel, support .xlsx and .xls
NOPI is an open source libirary to read | write xls file and its absolutely free to use.

Open Password Protected Excel Online

Free open password proteted file avaiable online to use eCompareFiles, support password length from 1 to 16 characters.

Download

Example Source Snippets - ExcelNPOI.zip

namespace : using NPOI.SS.UserModel

namespace : using NPOI.XSSF.UserModel

namespace : using NPOI.HSSF.UserModel

How to read excel file in c# windows application NPOI ?


public static ArrayList GetExcelSheetNameList(String Path)
    {
        IWorkbook wb = null;
        ArrayList sheetName = new ArrayList();
        //XSSFSheet sh;
        ISheet sh = null;
        string Sheet_name;
        try
        {
            using (FileStream fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
            {
                if (Path.IndexOf(".xlsx") > 0)
                    wb = new XSSFWorkbook(fs);
                else if (Path.IndexOf(".xls") > 0)
                    wb = new HSSFWorkbook(fs);

                int noOdSheet = wb.NumberOfSheets;

                for (int i = 0; i < noOdSheet; i++)
                {
                    try { Sheet_name = wb.GetSheetAt(i).SheetName.ToString(); sheetName.Add(Sheet_name); }
                    catch { break; }
                }
            }
        }
        catch { }
        return sheetName;
    }
public static DataTable ReadDataFromExcel(String Path, int sheetIndex)
    {
        //XSSFWorkbook wb;
        IWorkbook wb = null;

        //XSSFSheet sh;
        ISheet sh = null;
        String Sheet_name;

        using (FileStream fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
        {
            if (Path.IndexOf(".xlsx") > 0)
                wb = new XSSFWorkbook(fs);
            else if (Path.IndexOf(".xls") > 0)
                wb = new HSSFWorkbook(fs);
        }

        DataTable DT = new DataTable();
        DT.Rows.Clear();
        DT.Columns.Clear();

        if (sheetIndex < 0)
            sheetIndex = 0;

        sh = wb.GetSheetAt(sheetIndex);
        Sheet_name = wb.GetSheetAt(sheetIndex).SheetName;//get  sheet name

        int rowCount = sh.LastRowNum;   
        int i = 0;
        bool isGotRowHit = false;

        for (int r = 0; r <= rowCount; r++)
        {
            i = r;
            isGotRowHit = true;

            int celCount = 0;
            try { celCount = sh.GetRow(i).LastCellNum; }
            catch { }
            if (celCount != 0)
            {
                // add neccessary columns
                if (DT.Columns.Count < celCount)
                {
                    for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
                    {
                        DT.Columns.Add("", typeof(string));
                    }
                }

                // add row
                DT.Rows.Add();

                // write row value        
                for (int j = 0; j <= celCount; j++)
                {
                    var cell = sh.GetRow(i).GetCell(j);
                    if (cell != null)
                    {
                        string val = "";
                        try { val = cell.ToString(); DT.Rows[i][j] = cell.ToString(); }
                        catch
                        {
                            try { DT.Rows[i][j] = val; }
                            catch { }
                        }
                    }
                }
            }
            else { 
                DT.Rows.Add(); 
            }
            i++;
            if (isGotRowHit == false)
            {
                DT.Columns.Add("", typeof(string)); DT.Rows.Add();
            }
        }
        return DT;
    }
                 

smartsnipps.ecomparefiles.com © 2019, All Rights Reserved | Disclaimer: smartsnipps.eCompareFiles.com is free to use any code snippets without guarantee