excelAppWithDataset.zip (68.32 KB)

程序代码：

using System;
using System.Collections.Generic;
using using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using System.Data.OleDb;
using System.Diagnostics;
using myExcel = Microsoft.Office.Interop.Excel;

namespace excelAppWithDataset
{
    public partial class Form1 : Form
    {
        private string excelfile = string.Empty;

        public Form1()
        {
            InitializeComponent();
        }

        //Fill datagridview with the data in Excel file.
        private void getDataFromExcel()
        {
            //Open a new Excel.exe.
            myExcel.Application excelApp = new myExcel.Application();

            //Open a Excel file.
            System.Reflection.Missing none = System.Reflection.Missing.Value;
            myExcel.Workbook mybook = excelApp.Workbooks.Open(this.excelfile, none, none, none, none, none, none, none, none, none, none, none, none, none, none);

            //Get the first sheet.
            myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets[1];
            //myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets.Item[1];

            //Query the total number of rows and columns.
            int rows = sheet.UsedRange.Cells.Rows.Count;
            int cols = sheet.UsedRange.Cells.Columns.Count;
            
            //Add columns to datagridview first.
            for(int i=1;i<= cols;i++)
            {
                this.dgvExcelData.Columns.Add("", i.ToString());
                //Set this to enable change row/column selection mode.
                this.dgvExcelData.Columns[i - 1].SortMode = DataGridViewColumnSortMode.NotSortable;
            }
            
            //Fill data to datagridview.
            for (int i = 1; i <= rows; i++)
            {
                this.dgvExcelData.Rows.Add();// Add row

                for (int j = 1; j <= cols; j++)
                {
                    myExcel.Range rng = (myExcel.Range)sheet.Cells[i, j];
                    this.dgvExcelData.Rows[i-1].Cells[j-1].Value = rng.Value2;
                }
            }

            //Close all.
            mybook.Close();
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            mybook = null;
            //Force to quit Excel.exe.
            this.killExcel();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = "excelAppWithDataset";
            this.groupBox1.Text = "Data View";
            this.btnOpen.Text = "Open...";
            this.btnBeginEdit.Text = "Start Edit";
            this.btnEndEdit.Text = "End Edit";
            this.btnSave.Text = "Save";
            this.btnDelete.Text = "Delete";
            this.btnRowCol.Text = "RowSelection/ColumnSelection";
            this.btnExit.Text = "Exit";

            this.dgvExcelData.ReadOnly = true;
            this.dgvExcelData.AllowUserToOrderColumns = false;           
            this.dgvExcelData.AllowUserToDeleteRows = false;
            this.dgvExcelData.AllowUserToAddRows = false;

            this.btnEndEdit.Enabled = false;
            this.btnDelete.Enabled = false;
            this.btnSave.Enabled = false;
            this.btnBeginEdit.Enabled = false;
            this.btnRowCol.Enabled = false;      
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            OpenFileDialog o = new OpenFileDialog();
            o.CheckFileExists = true;
            o.Filter = "Excel 2007 file(*.xlsx)|*.xlsx";
            o.Multiselect = false;

            if(o.ShowDialog()==DialogResult.OK)
            {
                try
                {
                    //Clear datagridview if load data the second time.
                    this.dgvExcelData.Rows.Clear();
                    this.dgvExcelData.Columns.Clear();

                    this.excelfile = o.FileName;
                    //Load data to datagridview.
                    this.getDataFromExcel();
                    //Enable the btnBeginEdit-button after load file.
                    this.btnBeginEdit.Enabled = true;
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        //The function to kill Excel.exe.
        private void killExcel()
        {
            try
            {
                Process[] ps = Process.GetProcessesByName("EXCEL");
                foreach (Process p in ps)
                {
                    p.Kill();
                }
            }catch { }
        }
        
        //Stop edit the datagridview,Readonly=true.
        private void btnEndEdit_Click(object sender, EventArgs e)
        {
            this.dgvExcelData.ReadOnly = true;
            this.dgvExcelData.AllowUserToOrderColumns = false;
            this.dgvExcelData.AllowUserToDeleteRows = false;
            this.dgvExcelData.AllowUserToAddRows = false;

            this.btnEndEdit.Enabled = false;
            this.btnBeginEdit.Enabled = true;
            this.btnDelete.Enabled = false;
            this.btnSave.Enabled = true;
            this.btnRowCol.Enabled = false;
        }
        
        //Start edit the datagridview,Readonly=false.
        private void btnBeginEdit_Click(object sender, EventArgs e)
        {
            this.dgvExcelData.ReadOnly = false;
            this.dgvExcelData.AllowUserToOrderColumns = true;
            this.dgvExcelData.AllowUserToDeleteRows = true;
            this.dgvExcelData.AllowUserToAddRows = true;

            this.btnBeginEdit.Enabled = false;
            this.btnEndEdit.Enabled = true;
            this.btnDelete.Enabled = true;
            this.btnSave.Enabled = false;
            this.btnRowCol.Enabled = true;
        }

        //Change select mode,full-row-select or full-column-select.
        private void btnRowCol_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.SelectionMode==DataGridViewSelectionMode.ColumnHeaderSelect)
            {
                this.dgvExcelData.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
            }
            else
            {
                this.dgvExcelData.SelectionMode = DataGridViewSelectionMode.ColumnHeaderSelect;
            }
        }

        //Delete columns or rows.
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.SelectionMode==DataGridViewSelectionMode.RowHeaderSelect)  //Delete rows
            {
                if(this.dgvExcelData.SelectedRows.Count>0)  //If datagridview has selected rows
                {
                    if(MessageBox.Show("Are you sure to delete row(s)?","Info",MessageBoxButtons.OKCancel,MessageBoxIcon.Question)==DialogResult.OK) //Ask again before delete
                    {
                        foreach(DataGridViewRow row in this.dgvExcelData.SelectedRows)
                        {
                            this.dgvExcelData.Rows.Remove(row);
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else
            {
                if(this.dgvExcelData.SelectedColumns.Count>0)  //Delete columns if datagridview has selected columns
                {
                    if (MessageBox.Show("Are you sure to delete column(s)?", "Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK) //Ask again before delete
                    {
                        foreach (DataGridViewColumn col in this.dgvExcelData.SelectedColumns)
                        {
                            this.dgvExcelData.Columns.Remove(col);
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            if(this.dgvExcelData.Columns.Count==0)  //If there is nothing in datagridview,then exit
            {
                Application.Exit();
                return;
            }

            if(MessageBox.Show("Make sure you have saved changes,are you sure to exit?", "Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)  //Remind to save changes
            {
                Application.Exit();
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if(this.btnEndEdit.Enabled==true)  //Can't save unless end edit datagridview
            {
                MessageBox.Show("Please end edit first!");
                return;
            }

            if(this.dgvExcelData.Columns.Count==0)  //If there is nothing in datagridview,don't save
            {
                return;
            }

            try
            {
                this.saveExcelFile();
                MessageBox.Show("Successfully!");
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void saveExcelFile()
        {
            //Open a new Excel.exe.
            myExcel.Application excelApp = new myExcel.Application();

            //Add workbook,there is only a sheet in it defaultly.
            myExcel.Workbook mybook = excelApp.Workbooks.Add(true);
            myExcel._Worksheet sheet = (myExcel._Worksheet)mybook.Worksheets[1];

            //Write data to the sheet.
            //sheet.Name = "1";

            for (int i = 0; i < this.dgvExcelData.Rows.Count; i++)
            {
                for (int j = 0; j < this.dgvExcelData.Columns.Count; j++)
                {
                    myExcel.Range rng = (myExcel.Range)sheet.Cells[i+1, j+1];
                    rng.Value2 = this.dgvExcelData.Rows[i].Cells[j].Value;
                }
            }

            //Don't show save dialogue when saving file.
            excelApp.DisplayAlerts = false;
            excelApp.AlertBeforeOverwriting = false;

            //Save the file.   
            mybook.SaveAs(this.excelfile);
            mybook.Saved = true;

            //End the Excel.exe.
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            mybook = null;
            //Force to quit Excel.exe.
            this.killExcel();
        }
    }
}

[此贴子已经被作者于2016-1-29 23:53编辑过]

[此贴子已经被作者于2016-2-1 12:22编辑过]