将Excel导入到虚拟DataTable中

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Threading;
using System.Data.OracleClient;

添加以上引用

        public void Button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "工作薄(*.xls)|*.xlsx";
            CreateTempExcelTable();
            if (openFileDialog1.FilterIndex == 1 && openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                splashScreenManager2.ShowWaitForm();//显示waitform
                splashScreenManager2.SetWaitFormCaption("请稍等");
                string fileName = openFileDialog1.FileName;
                Excel.Application excel = new Excel.Application();
                Excel.Workbook workbook = excel.Workbooks.Open(fileName);
                Excel.Worksheet sheet = workbook.Sheets[1];
                int number = 0;//记录插入有效行数
                int flag = 0;//数据行是否符合标准,再决定是否把该行加入dt中
                int i = 2;//从第二行开始录入,第一行为字段名
                while (((Excel.Range)sheet.Cells[i, 1]).Text != "")
                {
                    DataRow dr = dt_ReciveExcelFileWage.NewRow();
                    for (int j = 1; j <= dt_ReciveExcelFileWage.Columns.Count + 2; j++)//从第二列开始循环,编码,Excel中的第三列和第四列不用存储
                    {
                        if (j == 1)
                        {
                            dr[j - 1] = GetStaffIdByFcode(((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text);
                        }
                        else if (j == 2 || j == 3)
                        {
                            continue;
                        }
                        else
                        {
                            if (((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text != "")
                            {
                                if (IsFloat(((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text))
                                {
                                    dr[j - 3] = ((Excel.Range)sheet.Cells[i, getCode((j + 1).ToString())]).Text;
                                }
                                else
                                {
                                    flag++;
                                    break;
                                }
                            }
                        }
                    }
                    if (flag == 0)
                    {
                        dt_ReciveExcelFileWage.Rows.Add(dr);
                        number++;
                    }
                    else
                    {
                        flag = 0;
                    }
                    i++;
                }
                i = i - 2;
                Kill(excel);
                splashScreenManager2.CloseWaitForm();//关闭waitform
                DialogResult dia = MessageService.ShowMessage("共导入" + i + "条记录,其中有效记录" + number + "条,是否确定导入", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (dia == DialogResult.OK)
                {//用事物存储表dt_
                    splashScreenManager2.ShowWaitForm();
                    splashScreenManager2.SetWaitFormDescription("正在保存数据...");
                    splashScreenManager2.SetWaitFormCaption("请稍等");
                    if (DoWork(dt_ReciveExcelFileWage) == true)
                    {
                        MessageService.ShowMessage("成功存储" + number + "条记录", "提示");
                    }
                    else
                    {
                        MessageService.ShowMessage("导入失败", "提示");
                    }
                    splashScreenManager2.CloseWaitForm();
                }
                else
                {
                    dt_ReciveExcelFileWage = null;
                }
            }
            else
            {
                dt_ReciveExcelFileWage = null;
            }
        }
        #region 获取excel中列
        /// <summary>
        /// 获取excel中列
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        private string getCode(string num)
        {
            switch (num)
            {
                case "1":
                    return "A";
                case "2":
                    return "B";
                case "3":
                    return "C";
                case "4":
                    return "D";
                case "5":
                    return "E";
                case "6":
                    return "F";
                case "7":
                    return "G";
                case "8":
                    return "H";
                case "9":
                    return "I";
                case "10":
                    return "J";
                case "11":
                    return "K";
                case "12":
                    return "L";
                case "13":
                    return "M";
                case "14":
                    return "N";
                case "15":
                    return "O";
                case "16":
                    return "P";
                case "17":
                    return "Q";
                case "18":
                    return "R";
                case "19":
                    return "S";
                case "20":
                    return "T";
                case "21":
                    return "U";
                case "22":
                    return "V";
                case "23":
                    return "W";
                case "24":
                    return "X";
                case "25":
                    return "Y";
                case "26":
                    return "Z";

                default:
                    return "A";
            }
        }
        #endregion

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        public static void Kill(Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值