Handling of the error "External table is not in the expected format" when connecting to excel

For files. the Excel File Created by create (filename), although in the format of Excel files, is not a standard Excel file. When using ado.net to connect to this file, the error message "The External table is not in the expected format" is displayed. This type of file is opened in the same format as the TXT file.

To solve the problem of an Excel file that prompts "The External table is not in the expected format", read the datatable file in the file reading mode and then process it.

 

// Read the file and the memory table exists.

Private datatable readfile (string filename, int columnnum)

{

Datatable dataread = createdatatable (columnnum );

If (file. exists (filename ))

{

String strread; // stores the Read File data.

Encoding encoding = encoding. getencoding ("gb18030 ");

Try

{

Filestream FS = new filestream (filename, filemode. Open, fileaccess. Read, fileshare. readwrite );

Streamreader sr = new streamreader (FS, encoding );

Strread = Sr. readtoend ();

Sr. Close ();

FS. Close ();

// Import the memory datatable

String [] readarray = strread. Split (New char [] {'/N'}, stringsplitoptions. removeemptyentries );

Foreach (string row in readarray)

{

String [] column = row. replace ("",""). replace ("/R ",""). split (New char [] {'/t'}, stringsplitoptions. removeemptyentries );

Datarow DR = dataread. newrow ();

For (INT I = 0; I <column. length; I ++)

{

Dr [I] = column [I];

}

Dataread. Rows. Add (DR );

}

}

Catch (exception ex)

{

Console. Write (ex. tostring ());

}

}

Else

{

MessageBox. Show ("file missing ");

}

Return dataread;

}