Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
2.00/5 (3 votes)
See more:
when i was reading the excel by using oledb, it is retrieving the data sucessfully. But when the sheet name contains '#' , it does not reading the data.
It gives the following error.
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

[EDIT - OP code from comment]
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application(); 

 Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
 workBookObject = excelFileObject.Workbooks.Open(File, 0, true, 5, "", "", false, 

 Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
 Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
 Microsoft.Office.Interop.Excel.Worksheet sheet = null;

 String Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File + ";Extended Properties="Excel 

 12.0;HDR=Yes;IMEX=2\"";
 OleDbConnection obj_Con = new OleDbConnection(Con);
 obj_Con.Open();

 string sheet_Name = "ABCD#EFGH";
 string query = String.Format("select * from [{0}$]", sheet_Name);
 OleDbCommand obj_CmdSelect = new OleDbCommand(query, obj_Con);
 OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
 obj_Adapter.SelectCommand = obj_CmdSelect;
 DataSet obj_Dataset = new DataSet();
 obj_Adapter.Fill(obj_Dataset, "Data"); /// Error occurs here
Posted
Updated 12-Jun-19 7:48am
v2
Comments
CHill60 10-Jun-15 5:46am    
Post the code that you using to access that sheet
karthikv101 10-Jun-15 5:55am    
Hi CHill60,

Thanks for your reply. Below is the code

Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(File, 0, true, 5, "", "", false,

Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = null;

String Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File + ";Extended Properties="Excel

12.0;HDR=Yes;IMEX=2\"";
OleDbConnection obj_Con = new OleDbConnection(Con);
obj_Con.Open();

string sheet_Name = "ABCD#EFGH";
string query = String.Format("select * from [{0}$]", sheet_Name);
OleDbCommand obj_CmdSelect = new OleDbCommand(query, obj_Con);
OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
obj_Adapter.SelectCommand = obj_CmdSelect;
DataSet obj_Dataset = new DataSet();
obj_Adapter.Fill(obj_Dataset, "Data"); /// Error occurs here
karthikv101 10-Jun-15 6:11am    
Also the error is "ABCD.EFGH is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

It seems OLEDB converts the "#" character to "." character.

I'm not sure why you are using both interop and oleDb.

As you already have the workbook open via interop then continue to access the sheets using that - you have the option then of referring to them via their index [1], [2] etc (i.e. ignoring the name - have a separate list of sheetnames if you absolutely need them), or you can use foreach

For example: (untested)
C#
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application(); 
 
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(File, 0, true, 5, "", "", false, 
 
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
 
DataSet oDS = new DataSet();

foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets)
{
    DataTable obj_Dataset = FillDataSetFromWorkSheet(sheet);
    oDS.Tables.Add(obj_Dataset);
}
with
C#
private DataTable FillDataSetFromWorkSheet(Microsoft.Office.Interop.Excel.Worksheet sheet)
{
    var oDT = new DataTable();
    for (int colIndex = 0; colIndex < MaxCols; colIndex++)
        oDT.Columns.Add(string.Format("Column{0}", colIndex));

    for (int rowIndex = 1; rowIndex <= MaxRows; rowIndex++)
    {
        var row = oDT.NewRow();

        //Note Excel arrays are 1-based not 0-based
        for (int colIndex = 0; colIndex < MaxCols; colIndex++)
            row[colIndex] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowIndex, colIndex + 1]).Value2);

        oDT.Rows.Add(row);
    }
    return oDT;
}
I just had MaxCols and MaxRows as constants but you could derive them as required.
 
Share this answer
 
Comments
karthikv101 10-Jun-15 7:11am    
@Chill60, great.. it is working fine using interop. But is there any way to read through Oledb?
CHill60 10-Jun-15 7:17am    
I continued experimenting after I posted this, trying to use a DataReader instead of DataAdaptor but got the same problem. I even played about with getting the schema of the workbook and working through that, but didn't succeed.
I thought there was a way to iterate through each sheet, but I think that must have just been using interop. I did some searching too, but I haven't been able to find any alternative (but that doesn't mean that there isn't one!)
Member 13577557 5-Feb-18 0:45am    
excel to gridview

i tryed this code


        //Coneection String by default empty  
        string ConStr = "";
        //Extantion of the file upload control saving into ext because   
        //there are two types of extation .xls and .xlsx of Excel   
        string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
        //getting the path of the file   
        string path = Server.MapPath("~/Upload/" + FileUpload1.FileName);
        //saving the file inside the MyFolder of the server  
        FileUpload1.SaveAs(path);
        Label1 = FileUpload1.FileName + "\'s Data showing into the GridView";
        //checking that extantion is .xls or .xlsx  
        if (ext.Trim() == ".xls")
        {
            //connection string for that file which extantion is .xls  
            ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        else if (ext.Trim() == ".xlsx")
        {
            //connection string for that file which extantion is .xlsx  
            ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }
        //making query  
        string sheet_Name = "ABCD#EFGH";
        string query = String.Format("select * from [{0}$]", sheet_Name);
        //Providing connection  
        OleDbConnection conn = new OleDbConnection(ConStr);
        //checking that connection state is closed or not if closed the   
        //open the connection  
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        //create command object  
        OleDbCommand cmd = new OleDbCommand(query, conn);
        // create a data adapter and get the data into dataadapter  
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        //fill the Excel data to data set  
        da.Fill(ds);
        //set data source of the grid view  
        viewuser_details.DataSource = ds.Tables[0];
        //binding the gridview  
        viewuser_details.DataBind();
        //close the connection  
        conn.Close();  
  



but its not working the error is

'ABCD.EFGH$' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long.


what i do???????
CHill60 5-Feb-18 5:53am    
Remove the full-stop from the sheet name - punctuation is not allowed
First make sure that the name not contain the special character like #,& etc bcz it conflict when it contain some specific character i am no sure about the list of character.

if it possible give the restriction when your checking the name or extension of the file in first step like getting the data with the sheet no like

C#
private static Excel.Workbook MyBook = null;
private static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;



C#
MyApp = new Excel.Application();
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
 
Share this answer
 
Comments
karthikv101 10-Jun-15 6:01am    
@Joshi, But it is mandatory to read the data from that sheet also.
joshi akhilesh 10-Jun-15 6:31am    
i think try the name with out all special character i think there is no need of using a special character at the mean time i am also try to solve and give you the feeedback
karthikv101 10-Jun-15 6:34am    
Without special character it is working fine. But the file is coming with the "#" character from the other end.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900