Pages

Google Analytics Alternative

Search This Blog

Saturday, February 25, 2012

Find Excel Workbook available sheet name using asp.net c#

hi friends, today morning i find one issues to find available sheet in Excel workbook..
so here is solution for this.

first of all we has to upload that file to server [excel] folder and then read excel sheet with oledb provider.
1. create aspx page with having two controls

  •      File upload controls with name [flUpldExcel]
  •      One command button to perform action. 
than once we save excel sheet to server so next step is to find available sheet in work book.
Now want to find available sheets on workbook. for that i write code that will find all sheets.

Code for saving file on server


 try
        {
            string strFilePath = Server.MapPath("excel") + @"\";
            string strFileName = string.Empty;
            if (flUpldExcel.HasFile)
            {
                strFileName = flUpldExcel.PostedFile.FileName;
                flUpldExcel.SaveAs(strFilePath + strFileName);
                string[] strSheetName;
                strSheetName = GetExcelSheetNames(strFilePath + strFileName);
                foreach (string item in strSheetName)
                {
                    Response.Write(item + "<br>");
                }
            }
        }
        catch (Exception ex)
        {
            
            throw ex;
        }


2. Code for find Sheet on excel workbook


 private String[] GetExcelSheetNames(string excelFile)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;

        try
        {
            // Connection String. Change the excel file to the file you
            // will search.
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
            // Create connection object by using the preceding connection string.
            objConn = new OleDbConnection(connString);
            // Open connection with the database.
            objConn.Open();
            // Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            // Loop through all of the sheets if you want too...
            for (int j = 0; j < excelSheets.Length; j++)
            {
                // Query each excel sheet.
            }

            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }


Note: Excel file should be in Excel 2003 file format.


No comments: