winform 打开导入Excel并读取

279 阅读1分钟

以下代码使用了Microsoft Office Interop库来访问Excel文件。需要注意的是,使用Interop库需要安装Microsoft Office软件,并且程序运行时会启动一个Excel进程。

        string filePath = "";//excel路径
        OpenFileDialog file = new OpenFileDialog();
        file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
        file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        file.Multiselect = false;
        if (file.ShowDialog() == DialogResult.OK)
        {
            filePath = file.FileName;
        }
        IWorkbook workbook = GetWorkBook(filePath);
        if (workbook == null)
        {
            return;
        }
        try
        {
            ISheet sheet = workbook.GetSheetAt(int.Parse(sheetIndex) - 1);
            //行数
            int rowNum = sheet.LastRowNum;
            if (rowNum == 0)
            {
                MessageBox.Show("Excel中没有数据", "提示");
                return;
            }
            //读取数据行
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row.GetCell(int.Parse(idColIndex) - 1).CellType != CellType.Blank)
                {
                    if (row.GetCell(int.Parse(idColIndex) - 1).CellType != CellType.String)
                    {
                        MessageBox.Show("请将xx列设置为文本格式", "提示");
                        return;
                    }
                    if (row.GetCell(int.Parse(PhoneColeIndex) - 1).CellType != CellType.String)
                    {
                        MessageBox.Show("请将xx列设置为文本格式", "提示");
                        return;
                    }

                    string? id = row.GetCell(int.Parse(idColIndex) - 1).ToString();
                    if (!string.IsNullOrEmpty(id) && !pointPersonDic.ContainsKey(id))
                    {
                        string? name = row.GetCell(int.Parse(nameColIndex) - 1).ToString();
                        string? phone = row.GetCell(int.Parse(PhoneColeIndex) - 1).ToString();
                        if (!string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(phone))
                        {
                            nameList.Add(name);
                            idList.Add(id);
                            pointGridView.AddRow(new string[2] { name, id });
                            pointGridView.Rows[idList.Count - 1].Cells["phone"].Value = phone;
                            pointPersonDic.Add(id, new PointPerson(id, name));
                            pointPersonDic[id].Phone = phone;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "错误");
        }
        finally
        {
            workbook.Close();
        }