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

By -
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.


Comments

Post a Comment

Popular posts from this blog

sp_addmessage can be used to create a User-Defined Error Message

By -
hi friends, On Today Morning i go through with Custom Error Message in MSSql server. for that i work around below step and it's done man. i found one stored procedure "sp_addmessage" which used for adding my custome error message on server.. USE master GO EXEC sp_addmessage 50002, 10, N'The data %s already exists!' GO RAISERROR (50002, 10, 1, 'MyText') syntax for sp_addmessage is sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'      [ , [ @lang= ] 'language' ]      [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]      [ , [ @replace= ] 'replace' ] [ @msgnum= ] msg_id Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language. [ @severity = ]se
Read more

Command-line Building With csc.exe

By -
Command-line Building With csc.exe You can invoke the C# compiler by typing the name of its executable file (csc.exe) on the command line. If you use the Visual Studio Command Prompt (available as a shortcut on the start menu under Visual Studio Tools), all the necessary environment variables are set for you. Otherwise, you must adjust your path in order to enable csc.exe to be invoked from any subdirectory on your computer. If you do not use the Visual Studio Command Prompt, you must run vsvars32.bat to set the appropriate environment variables to support command line builds. For more information about vsvars32.bat, see How to: Set Environment Variables. If you are working on a computer that only has the Windows Software Development Kit (SDK), you can use the C# compiler at the command line if you use the SDK Command Prompt , which is available from the Microsoft .NET Framework SDK menu option. You can also use MSBuild to programmatically build C# programs. For more informat
Read more

Prepare DTO layer by using below script

By -
DECLARE @CurID INT, @MaxID INT Declare @sql nvarchar(MAX) Declare @TmpTable table (ID int Identity(1,1), c_Column_name varchar(500),c_Data_type varchar(50),c_character_Maximum_length nvarchar(50)) Insert into @TmpTable (c_Column_name,c_Data_type,c_character_Maximum_length)     SELECT COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TT_M_User'  --Table Name ORDER BY ORDINAL_POSITION ASC; set @CurID = 1 set @sql='' SELECT @MaxID=Max(ID) FROM @TmpTable WHILE @MaxID >=@CurID BEGIN -- Logic to be performed select @sql = @sql + char(13)+ ' public ' +     case c_Data_type     when 'varchar' then ' string '     when 'nvarchar' then ' string '     when 'bit' then ' bool '     when 'datetime' then ' DateTime '     when 'binary' then ' Byte[] '     else c_Data_type end + ' ' + c_Column_Name + ' {get;set;} 
Read more