使用Microsoft.Office.Interop.Excel提取Excel文件数据

将Excel数据提取出来放到二维数组中:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = ExcelToDataTable(@"c:aaa.xls", "aaa");

            System.Data.DataTable ExcelTable = ds.Tables["aaa"];

            int iColums = ExcelTable.Columns.Count;//列数

            int iRows = ExcelTable.Rows.Count;//行数

            string[,] storedata = new string[iRows, iColums];
            for (int i = 0; i < ExcelTable.Rows.Count; i++)
                for (int j = 0; j < ExcelTable.Columns.Count; j++)
                {
                    storedata[i, j] = ExcelTable.Rows[i][j].ToString();
                }

            string strSql = GetStrSql(storedata, iRows);

        }

        public static DataSet ExcelToDataTable(string strExcelFileName, string strSheetName)
        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";

            string strExcel = string.Format("select * from [{0}$]", strSheetName);

            DataSet ds = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                Application app = new Application();
                app.Application.Workbooks.Open(strExcelFileName);
                //app.Visible = true;
                conn.Open();

                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);

                adapter.Fill(ds, strSheetName);

                conn.Close();

            }
            return ds;
        }

        public static string GetStrSql(string[,] datainfo, int rows)
        {
            string strSql = "";
            for (int i = 0; i < datainfo.GetLength(0); i++)
            {

                strSql = "insert into tmp_localfile_data(finish_time,data_value,product_id,prod_group,proj_code,parameter_id," +
                         " machine,category_flag,partno,fixture,marking,head_id,shift,batch,id_1,id_2)" +
                         " VALUES(to_date('" + datainfo[i, 0] + "','mm/dd/yyyy hh24:mi:ss'),'" + datainfo[i, 1] +
                         "','" + datainfo[i, 2] + "','" + datainfo[i, 3] + "','" + datainfo[i, 4] + "'," +
                         "'" + datainfo[i, 5] + "','" + datainfo[i, 6] + "','" + datainfo[i, 7] + "','" + datainfo[i, 8] +
                         "','" + datainfo[i, 9] + "','" + datainfo[i, 10] + "','" + datainfo[i, 11] + "','" + datainfo[i, 12] +
                         "','" + datainfo[i, 13] + "','" + datainfo[i, 14] + "','" + datainfo[i, 15] + "')";
            }

            return strSql;
        }

    }
}