不是有效的名称。确保它不包含无效字符或标点符号，并且不会太长。 [英] is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

问题描述

当我使用oledb读取excel时，它正在成功检索数据。但是当工作表名称包含＃时，它不会读取数据。

它会出现以下错误。

不是有效名称。请确保它不包括无效字符或标点符号并且不会太长。

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.

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

推荐答案

，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）; / //错误发生在这里

", 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

首先确保名称不包含特殊字符，如＃，& etc等，当它包含某些特定字符时，它会发生冲突我不确定字符列表。



如果在第一步检查文件的名称或扩展名时可能会给出限制，例如获取数据表格不喜欢

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

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

MyApp = new Excel.Application();
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];

我不确定你为什么同时使用interop和oleDb。



因为你已经通过interop打开了工作簿然后继续使用它来访问工作表 - 你有然后选择通过索引[1]，[2]等引用它们（即忽略名称 - 如果你绝对需要它们，则有一个单独的工作表名单），或者你可以使用 foreach



例如:(未经测试）

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)

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);
}

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;
}

我只有 MaxCols 和 MaxRows 作为常量，但你可以派生出来根据需要使用它们。

I just had MaxCols and MaxRows as constants but you could derive them as required.

这篇关于不是有效的名称。确保它不包含无效字符或标点符号，并且不会太长。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！