采用OleDB读取EXCEL文件 读取数字后,字符串无法读取

　　很多人采用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<DataRow> removelist = new List<DataRow>();
            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;
        }
    }
}