JAVA高级面试进阶训练营视频教程

admin

1.在项目引用NuGet中安装Microsoft.Office.Interop.Excel

2.在cs文件头部添加命名空间

using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

3.窗体界面（灰色部分是datagridview1）

4.代码部分

using System;
using System.Windows.Forms;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace InputExcelTest2
{
    public partial class Form_SelectFile : Form
    {
        public Form_SelectFile()
        {
            InitializeComponent();
        }
        private void BtnCancel_Click(object sender, EventArgs e)
        {
            Close();
        }
        private void BtnSelectFile_Click(object sender, EventArgs e)
        {//选择文件
            openFileDialog1.Filter = "XLS文件|*.xls|XLSX文件|*.xlsx";//筛选文件类型
            openFileDialog1.FileName = "";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                OpenExcel(openFileDialog1.FileName);
            }
            openFileDialog1.Dispose();
        }
        private void OpenExcel(string strFileName)
        {
            object missing = Missing.Value;
            Excel.Application excel = new Excel.Application();//启动excel程序
            try
            {
                if (excel == null)
                {
                    MessageBox.Show("无法访问Excel程序，请重新安装Microsoft Office Excel。");
                }
                else
                {
                    excel.Visible = false;//设置调用引用的Excel文件是否可见
                    excel.UserControl = true;//设置调用引用的Excel是由用户创建或打开的
                    // 以只读的形式打开EXCEL文件（工作簿）想了解这堆参数请访问https://msdn.microsoft.com/zh-cn/library/office/microsoft.office.interop.excel.workbooks.open.aspx
                    Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                     missing, missing, missing, true, missing, missing, missing, missing, missing);
                    //取得第一个工作表
                    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];//索引从1开始 
                    //取得总记录行数(包括标题列)  
                    int rowCount = ws.UsedRange.Cells.Rows.Count; //得到行数 
                    int colCount = ws.UsedRange.Cells.Columns.Count;//得到列数
                    //初始化datagridview1
                    dataGridView1.Rows.Clear();
                    dataGridView1.Columns.Clear();
                    //取得第一行，生成datagridview标题列(下标是从1开始的)
                    for (int i = 1; i <= colCount; i++)
                    {
                        string cellStr = ws.Cells[1, i].Value2.ToString().Trim();
                        dataGridView1.Columns.Add("column"+i,cellStr);
                    }
                    //取得数据(不包括标题列)
                    for(int i = 2; i <= rowCount; i++)
                    {//循环行
                        int index = dataGridView1.Rows.Add();
                        if (ws.Rows != null)
                        {
                            for(int j = 1; j <= colCount; j++)
                            {//循环列
                                if (ws.Cells[i, j].Value2 == null)
                                {//跳过空的单元格
                                    continue;
                                }
                                dataGridView1.Rows[index].Cells[j-1].Value = ws.Cells[i, j].Value2.ToString().Trim();
                            }
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("读取Excel文件失败： "+ex.Message);
            }
            finally
            {
                CloseExcel(excel);//关闭Excel进程
            }
        }
        private void CloseExcel(Excel.Application excel)
        {//关闭Excel进程
            excel.Quit();
            excel = null;
            Process[] procs = Process.GetProcessesByName("excel");
            foreach (Process pro in procs)
            {
                pro.Kill();//杀掉进程  
            }
            GC.Collect();
        }
    }
}