You must Sign In to post a response.
  • Category: .NET

    How to read xls format file in C#

    Hai All,

    May i know how to read excel xls format file in C#
    I'm using NPOI dll for reading xls format excel file.
    Working fine except Excel 7.0/95 (BIFF5)

    Error : The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)

    please suggest any other dll for reading xls file [free dll]
    or how to handle BIFF5 error in NPOI
    or how to convert BIFF5 format to BIFF8 format in C#

    code :
    DLL : using NPOI.HSSF.UserModel;
    public static DataTable ReadXls(string filePath, int headerRowNo, int dataRowNo)
    var stream = File.OpenRead(filePath);
    DataTable dtXlsColumn = null;
    string xlsFirstSheetName = null;
    HSSFWorkbook hssfwb = new HSSFWorkbook(stream);
    NPOI.SS.UserModel.ISheet sheet = hssfwb.GetSheetAt(0);
    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
    xlsFirstSheetName = sheet.SheetName;
    dtXlsColumn = new DataTable();
    int count = 0;

    int rowHeader = 0;
    if (!string.IsNullOrEmpty(headerRowNo.ToString())) rowHeader = Convert.ToInt32(headerRowNo) - 1;
    int dataStartAt = 1;
    if (!string.IsNullOrEmpty(dataRowNo.ToString())) dataStartAt = Convert.ToInt32(dataRowNo) - 1;
    DataTable dtmask = new DataTable();

    NPOI.SS.UserModel.IRow rowss = sheet.GetRow(rowHeader);
    //NPOI.SS.UserModel.IRow rowsData = sheet.GetRow(dataStartAt);

    for (int i = 0; i < rowss.Cells.Count; i++)

    while (rows.MoveNext())
    if (count >= dataStartAt)
    NPOI.HSSF.UserModel.HSSFRow row = (HSSFRow)rows.Current;
    DataRow dr = dtXlsColumn.NewRow();
    dr["RowNumber"] = (count + 1).ToString();
    for (int i = 0; i < row.LastCellNum; i++)
    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
    if (cell != null)
    if (cell.CellType.ToString().ToUpper() == "NUMERIC" && (cell.ToString().Contains('-') || cell.ToString().Contains('/')))
    //double d = double.Parse(cell.ToString());
    dr[i] = Convert.ToDateTime(cell.ToString()).ToString("yyyy-MM-dd HH:mm:ss");
    dr[i] = cell.ToString();

    return dtXlsColumn;
    catch (Exception ex) //Error
    return null;


    Thanks and Regards,
  • #763892
    You can try the following OLEDB

    OleDbConnection MyOleDbConnection = new OleDbConnection(ExcelConnectionString);
    OleDbCommand MyOleDbCommand = new OleDbCommand();
    OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();

    MyOleDbCommand.Connection = MyOleDbConnection;

    //Read name of the First Sheet
    DataTable MyExcelSchema;
    DataTable MyDataTable = new DataTable();
    MyExcelSchema = MyOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string MySheetName = MyExcelSchema.Rows[0]["TABLE_NAME"].ToString();

    //Read Data from First Sheet
    MyOleDbCommand.CommandText = "SELECT * From [" + SheetName + "]";
    MyOleDbDataAdapter.SelectCommand = cmdExcel;
    MyOleDbDataAdapter.Fill(MyDataTable );

    //Bind Data to GridView
    GridView1.DataSource = MyDataTable ;

    By Nathan
    Direction is important than speed

  • #763909

    Hai Chellapan,
    You can use the API "JExcelAPI". This is an open source and can be found at the below link:

    This API is able to read the Excel files of all type of format so this should work well in your case.
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #763928
    You can use OLEDB class to read excel file, why want third party dll ?
    There are couple of ways to read excel file
    1. Using Interop object
    2. Using OLEDB
    see below snippet to read excel file using OLEDB
    private void button1_Click(object sender, EventArgs e)
    System.Data.OleDb.OleDbConnection MyConnection ;
    System.Data.DataSet DtSet ;
    System.Data.OleDb.OleDbDataAdapter MyCommand ;
    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\';Extended Properties=Excel 8.0;");
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
    MyCommand.TableMappings.Add("Table", "TestTable");
    DtSet = new System.Data.DataSet();
    dataGridView1.DataSource = DtSet.Tables[0];
    catch (Exception ex)
    MessageBox.Show (ex.ToString());

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764331
    Hi there

    [quote]please suggest any other dll for reading xls file [free dll] [/quote]

    I not very familiar with NOPI, not sure if NOPI supports .xls. Alternatively, you could use free Spire.XLS ( ) to open and read .xls, .xlsx file using following code:

    Workbook wb = new Workbook();
    wb.LoadFromFile("sample.xls", ExcelVersion.Version97to2003);

Sign In to post your comments