String cannot be read after reading from an Excel file using OLE DB

Many people use OLE DB to read the Excel file when you find that when a column of data starts with a number, the subsequent string can not be read, today to share the solution to the problem of the tips.

1. Read the column headings as data (thehdr=no setting handles the first row as data rather than the header):

String strconn = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + FileName + ";" +
"; Extended properties=\ "Excel 12.0; hdr=no, imex=1\ "";

2, processing the data read: Set the column name, delete the first row (column header)

private static void Convertdata (DataTable dt)
{
foreach (DataColumn dc in dt. Columns)
{

　　　　　　　 //The first line should actually be the column name, so just take it to set
string colname = dt. ROWS[0][DC. ColumnName]. ToString ();
if (!string. IsNullOrEmpty (colname))
{
dc. ColumnName = getdatacolumnname (dt, colname);
}
}
　　　　　 //First line task complete, delete it
dt. Rows.removeat (0);
}

Full code:

　　

usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.OleDb;usingSystem.Linq;usingSystem.Text;namespacedepthdataconvert{Internal classExcelfilereader { Public StaticDataTable Getexceldata (stringfileName) {DataTable dt=NewDataTable (); OleDbConnection Conn=NULL; OleDbDataAdapter mycommand=NULL; Try            {                stringstrconn ="provider=microsoft.ace.oledb.12.0;"+"Data source="+ FileName +";"+"; Extended properties=\ "Excel 12.0; Hdr=no;imex=1\ ""; Conn=NewOleDbConnection (strconn); Conn.                Open (); stringStrexcel =""; DataSet DS=NULL; Strexcel="select * FROM [sheet1$]"; MyCommand=NewOleDbDataAdapter (Strexcel, strconn); DS=NewDataSet (); Mycommand.fill (DS,"table1"); DT= ds. tables[0]; }            Catch(Exception e) {Throwe; }            finally            {                if(Conn. state = =ConnectionState.Open) {conn.                    Close ();                    Mycommand.dispose (); Conn.                Dispose ();            }} convertdata (dt);            Removeempty (DT); returnDT; }        Private Static voidremoveempty (DataTable dt) {List<DataRow> removelist =NewList<datarow>();  for(inti =0; i < dt. Rows.Count; i++)            {                BOOLIsNull =true;  for(intj =0; J < dt. Columns.count; J + +)                {                    if(!string. IsNullOrEmpty (dt. ROWS[I][J]. ToString (). Trim ())) {IsNull=false; }                }                if(IsNull) {removelist. ADD (dt.                Rows[i]); }            }             for(inti =0; I < Removelist. Count; i++) {dt.            Rows.remove (Removelist[i]); }        }        Private Static voidconvertdata (DataTable dt) {foreach(DataColumn DCinchdt. Columns) {stringcolname = dt. rows[0][DC. ColumnName].                ToString (); if(!string. IsNullOrEmpty (colname)) {DC. ColumnName=getdatacolumnname (DT, colname); }} dt. Rows.removeat (0); }        Private Static stringGetdatacolumnname (DataTable DT,stringcn) {            stringColName =cn; intindex =1;  while(dt. Columns.contains (colname)) {colname= cn + index++; }            returncolname; }         Public Static voidexportexcel (DataTable dt) {if(dt = =NULL|| Dt. Rows.Count = =0)return; Microsoft.Office.Interop.Excel.Application xlapp=NewMicrosoft.Office.Interop.Excel.Application (); if(xlapp = =NULL)            {                return; } System.Globalization.CultureInfo Currentci=System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture=NewSystem.Globalization.CultureInfo ("en -US"); Microsoft.Office.Interop.Excel.Workbooks Workbooks=Xlapp.workbooks; Microsoft.Office.Interop.Excel.Workbook Workbook=workbooks.            ADD (Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet Worksheet= (Microsoft.Office.Interop.Excel.Worksheet) workbook. worksheets[1];            Microsoft.Office.Interop.Excel.Range Range; LongTotalCount =dt.            Rows.Count; LongRowread =0; floatPercent =0;  for(inti =0; i < dt. Columns.count; i++) {worksheet. cells[1, i +1] =dt. Columns[i].                ColumnName; Range= (Microsoft.Office.Interop.Excel.Range) worksheet. cells[1, i +1]; Range. Interior.ColorIndex= the; Range. Font.Bold=true; }             for(intR =0; R < dt. Rows.Count; r++)            {                 for(inti =0; i < dt. Columns.count; i++) {worksheet. Cells[r+2, i +1] =dt. Rows[r][i].                ToString (); } rowread++; Percent= ((float)( -* rowread))/TotalCount; } xlapp.visible=true; }    }}

String cannot be read after reading from an Excel file using OLE DB