C#连接Excel数据处理

代码片

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;//excel表数据操作
using System.Data;
using System.Globalization;

namespace ConsoleApp15
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = "E:/1.xls";
            var OleDsExcel=Excel(filePath);
            DataTable tb1 = new DataTable();
            if (OleDsExcel.Tables.Count > 0) tb1 = OleDsExcel.Tables[0];
            int rowsCount = tb1.Rows.Count;
            int colsCount = tb1.Columns.Count;
            Dictionary<string, int> dic = new Dictionary<string, int>();//创建字典
            string[,] Arr = new string[rowsCount, colsCount];//创建二维数组
            
            int s=0;
            int num = 0;
            int w = -1;
            for (int i = 0; i < rowsCount; i++)
            {
                string st = tb1.Rows[i][2].ToString().Trim();
                //toupper() 函数用来将小写字母转换为大写字母,trim()的作用就是删除字符串首部和尾部的空格
                if (dic.ContainsKey(st)) w = dic[st];else w =-1;//ContainsKey方法可以用来判断某个键值是否存在
                if (w == -1)
                {
                    for (int j = 0; j < colsCount; j++)
                    {
                        try
                        {
                            //Console.WriteLine(tb1.Rows[i][j].ToString().Trim());
                            Arr[s, j] = tb1.Rows[i][j].ToString().Trim();
                        }
                        catch (Exception err)
                        {
                            Console.WriteLine(err);
                        }
                    }
                    dic[st] = s;
                    s++;
                }
                else
                {
                    num=int.Parse(Arr[w, 8]) + int.Parse(tb1.Rows[i][8].ToString().Trim());
                    Arr[w, 8] = num.ToString();
                }
            }
            for (int ii = 0; ii < Arr.GetLength(0); ii++)
            {
                if (Arr[ii,2]!=null)
                {
                    Console.WriteLine("物料编号={0}总数量={1}",Arr[ii,2],Arr[ii,8]);
                } 
            }
        }


        public static DataSet Excel(string filePath)
        {
            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                string sql = "select * from [Sheet1$]";

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcel = new DataSet();
                OleDaExcel.Fill(OleDsExcel, "Sheet1");
                OleConn.Close();
                return OleDsExcel;
            }
            catch (Exception err)
            {
                Console.WriteLine("数据绑定Excel失败!失败原因：" + err.Message, "提示信息");       
                return null;
            }
        }
    }
}