很多人采用OleDB读取EXCEL文件的时候会发现，当一列数据以数字开头的时候，后面的字符串无法读取，今天就给大家分享一下解决此问题的小窍门。

　　1、把列标题当做数据来读取（HDR=NO设置把第一行当做数据而不是表头来处理）：

　　　　 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" +

                    ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";

　　2、对读取的数据进行处理：设置列名称、删掉第一行（列标题）

　　　　private static void convertData(DataTable dt)

        {

            foreach (DataColumn dc in dt.Columns)

            {

　　　　　　　//第一行其实应该是列名称，所以直接拿来设置

                string colName = dt.Rows[0][dc.ColumnName].ToString();

                if (!string.IsNullOrEmpty(colName))

                {

                    dc.ColumnName = getDataColumnName(dt, colName);

                }

            }

　　　　　 //第一行任务完成，删除它

            dt.Rows.RemoveAt(0);

        }

 

 

　　完整代码：

　　

using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Linq;using System.Text;namespace DepthDataConvert{    internal class ExcelFileReader    {        public static DataTable GetExcelData(string fileName)        {            DataTable dt = new DataTable();            OleDbConnection conn = null;            OleDbDataAdapter myCommand = null;            try            {                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" +                     ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";                conn = new OleDbConnection(strConn);                conn.Open();                string strExcel = "";                DataSet ds = null;                strExcel = "select * from [sheet1$]";                myCommand = new OleDbDataAdapter(strExcel, strConn);                ds = new DataSet();                myCommand.Fill(ds, "table1");                dt = ds.Tables[0];            }            catch (Exception e)            {                throw e;            }            finally            {                if (conn.State == ConnectionState.Open)                {                    conn.Close();                    myCommand.Dispose();                    conn.Dispose();                }            }            convertData(dt);            removeEmpty(dt);            return dt;        }        private static void removeEmpty(DataTable dt)        {            List
     
       removelist = new List
      
       ();            for (int i = 0; i < dt.Rows.Count; i++)            {                bool IsNull = true;                for (int j = 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 (int i = 0; i < removelist.Count; i++)            {                dt.Rows.Remove(removelist[i]);            }        }        private static void convertData(DataTable dt)        {            foreach (DataColumn dc in dt.Columns)            {                string colName = dt.Rows[0][dc.ColumnName].ToString();                if (!string.IsNullOrEmpty(colName))                {                    dc.ColumnName = getDataColumnName(dt, colName);                }            }            dt.Rows.RemoveAt(0);        }        private static string getDataColumnName(DataTable dt, string cn)        {            string colName = cn;            int index = 1;            while (dt.Columns.Contains(colName))            {                colName = cn + index++;            }            return colName;        }        public static void ExportExcel(DataTable dt)        {            if (dt == null || dt.Rows.Count == 0) return;            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();            if (xlApp == null)            {                return;            }            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;            System.Threading.Thread.CurrentThread.CurrentCulture = new System.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;            long totalCount = dt.Rows.Count;            long rowRead = 0;            float percent = 0;            for (int i = 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 = 15;                range.Font.Bold = true;            }            for (int r = 0; r < dt.Rows.Count; r++)            {                for (int i = 0; i < dt.Columns.Count; i++)                {                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();                }                rowRead++;                percent = ((float)(100 * rowRead)) / totalCount;            }            xlApp.Visible = true;        }    }}