C # ASP. Read Excel cell read null value does not display

Share with you, "excerpt": http://blog.csdn.net/li185416672/article/details/8213729

When you read Excel, some cells are empty values

I see:

When we use Olebb to read Excel, if we do not configure the properties of Imex=1, Microsoft's processing mechanism is to convert columns to the same type to read. For example, when you write the number format in the first line, and the second line writes the character format, some columns have values that cannot be read. In fact, the problem is very simple, If you know where the problem is. Set the property to "Imex=1"

The following references are attached:

String xlsdriver = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0};extended properties= ' Excel 8.0;imex=1 ';";
OleDbConnection conn = new OleDbConnection (String.Format (xlsdriver, filename));

"Hdr=yes;" indicates that first row contains columnnames, not data.
"Hdr=no;" indicates the opposite.
"Imex=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect Excel sheet write access negative.
With the Imex=1 attribute, the value of Excel cells is read in text form, avoiding some values that cannot be read because of inconsistent data types.

Could not find installable ISAM

When you import Excel into your application, you are prompted with a solution that says "installable ISAM cannot be found":

1. Connection string Q

Extended properties= ' Excel 8.0; Hdr=no;imex=1 '

(1) HDR indicates whether to use the first row as the data or column name, as the data with Hdr=no, as the column name with Hdr=yes; by Imex=1, the mixed type is read as a text type, avoiding null values.

(2) Around two single quotes can not be less

2. Simply register the Excel ISAM to

Enter the carriage return in the Run dialog box: Regsvr32 c:\WINDOWS\system32\msexcl40.dll

Public DataSet Excelreader (string excelname)
{
//spelling connection string, opening connection
String strconn = "provider= microsoft.jet.oledb.4.0; "+" Data source= "+ Excelname +"; Extended properties= ' Excel 8.0; Hdr=yes; Imex=1 ' ";
OleDbConnection objconn = new OleDbConnection (strconn);
objConn.Open ();
//Get all worksheets in Excel workbook
DataTable schematable = objconn.getoledbschematable (OleDbSchemaGuid.Tables, null);
OleDbDataAdapter Sqlada = new OleDbDataAdapter ();
DataSet ds = new DataSet ();
//Traverse worksheet to get data and deposit to DataSet
foreach (DataRow dr in schemaTable.Rows)
{
Try
{
String strSQL = "SELECT * from ["+ dr[2]. ToString (). Trim () + "]";
OleDbCommand objcmd = new OleDbCommand (strSQL, objconn);
Sqlada. SelectCommand = objcmd;
Sqlada. Fill (ds, dr[2]. ToString (). Trim ());
}
catch (Exception ex)
{

}
}
Objconn.close ();
return DS;
}