c# - Microsoft OLEDB 错误外部表不是预期的格式

Question

我编写了一个代码，可以使用 Microsoft 数据访问引擎读取 excel 2007 文件，下面的代码片段适用于大多数文件，但它恰好适用于大多数 excel 文件，即 .xlsx,.xls 但当它在 objConn.Open() 处失败时；对于有excel格式问题的excel文件，请引用下图

打开 ole Db Connection 会失败，提示错误 External table is not in the expected format 。此导入过程的另一个问题是

            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM "+ SelectionSheet, objConn);

无法阅读以空格开头的表格，我们将不胜感激任何解决此问题的帮助。

       public DataTable ReadExcel(string Path, ArrayList IgnoreString, ArrayList IgnoreColumn)
        {
            DataTable dtReturn = new DataTable();
            DataTable dtPrintable = new DataTable();
            DataTable dtTemp = new DataTable();
            try
            {
                string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                        "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";

                OleDbConnection objConn = new OleDbConnection(sConnectionString);




                objConn.Open();
                DataTable dtSheetnames = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                DataTable dtTesting = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.DbInfoLiterals, new object[] {});
                DataTable dtTesting2 = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, new object[] { });


                string SelectionSheet = dtSheetnames.Rows[0][2].ToString();

                if (SelectionSheet.Contains("'") )
                {
                    SelectionSheet = SelectionSheet.Remove(0, 1);
                    SelectionSheet = "[" + SelectionSheet;
                    SelectionSheet = SelectionSheet.Remove(SelectionSheet.Length - 1, 1);
                    // -- Mod by zeemz on 23 dec
                    //    string PrintArea = SelectionSheet + "Print_Area]";

                    SelectionSheet = SelectionSheet + "]";
                }
                else
                {
                    SelectionSheet = "["+ SelectionSheet + "]";
                }


                //OleDbCommandBuilder objCmdBuilder = new OleDbCommandBuilder(


                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM "+ SelectionSheet, objConn);




                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                DataSet objDataSet = new DataSet();

                DataSet PrintAreads = new DataSet();

                objAdapter.SelectCommand = objCmdSelect;
                objAdapter.Fill(objDataSet);

                // -- Mod by zeemz on 23 dec
                //objCmdSelect.CommandText = "SELECT * FROM " + PrintArea;
                //objAdapter.Fill(PrintAreads);




                objConn.Close();

                dtReturn = objDataSet.Tables[0].Copy();
               // dtPrintable = PrintAreads.Tables[0].Copy();

                // -- Mod by zeemz on 23 dec
                //if (dtPrintable.Columns.Count != dtReturn.Columns.Count)
                //{
                //    int TotalPrintable = dtPrintable.Columns.Count;
                //    int TotalComing = dtReturn.Columns.Count;
                //    int StartRemovingPos = TotalComing - TotalPrintable;

                //    for (int i = TotalPrintable; dtPrintable.Columns.Count != dtReturn.Columns.Count; i++)
                //    {

                //        dtReturn.Columns.RemoveAt(i);
                //        i = i - 1 ;
                //    }


                //}


                int iCount = 0;
                while (iCount <= dtReturn.Rows.Count - 1)
                {
                    if (isRowEmpty(dtReturn.Rows[iCount]))
                    {
                        dtReturn.Rows.RemoveAt(iCount);
                    }
                    else
                    {
                        iCount += 1;
                    }
                }


                //now applying the filters

                //column ignore
                for (int i = IgnoreColumn.Count - 1; i >= 0; i--)
                {
                    dtReturn.Columns.RemoveAt((int)IgnoreColumn[i]);
                }

                //string ignore
                for (int i = IgnoreString.Count - 1; i >= 0; i--)
                {
                    for (int j = dtReturn.Rows.Count - 1; j >= 0; i--)
                    {
                        foreach (DataColumn dCol in dtReturn.Columns)
                        {
                            if (dtReturn.Rows[j][dCol.ColumnName].ToString().ToLower().Contains(IgnoreString[i].ToString().ToLower()))
                            {
                                dtReturn.Rows.RemoveAt(j);
                                break;
                            }
                        }
                    }
                }


                /* Hack to get rid of DateTime Columns */
                // added by zeemz
                dtTemp = dtReturn.Clone();
                dtTemp.Clear();
                foreach (DataColumn tempColumn in dtTemp.Columns)
                {
                //    if (tempColumn.DataType == typeof(DateTime))
//                    {
                        tempColumn.DataType = typeof(String);
  //                  }
                }
                foreach (DataRow tempRow in dtReturn.Rows)
                {
                    DataRow insRow = dtTemp.NewRow();
                    foreach (DataColumn tempColumn in dtReturn.Columns)
                    {

                        if (tempColumn.DataType == typeof(DateTime))
                        {
                            if (!String.IsNullOrEmpty(tempRow[tempColumn.ColumnName.ToString()].ToString()))
                            {
                                insRow[tempColumn.ColumnName.ToString()] = Convert.ToDateTime(tempRow[tempColumn.ColumnName.ToString()].ToString()).ToString("yyyyMMddhhmmss");
                            }
                            else
                            {
                                insRow[tempColumn.ColumnName.ToString()] = "";
                            }
                        }
                        else
                        {

                            insRow[tempColumn.ColumnName.ToString()] = tempRow[tempColumn.ColumnName.ToString()].ToString();
                        }

                    }
                    dtTemp.Rows.Add(insRow);
                }



            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtTemp;
        }

Answer 1

当您手动或以编程方式修改 xlsx 文件而不使用 excel 时，我碰巧发现问题存在，一旦您修改了 xlsx，格式就不会保持完整，并且会出现上述错误，因为 OleDbDataAdapter 无法处理修改后的文件文件和 Excel 它自己可以修复损坏的文件按预期显示文件。