using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using myExcel = Microsoft.Office.Interop.Excel;
using System.Reflection;//为了调用缺省方法Missing
namespace excelOper
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        myExcel.Application excelApp = new myExcel.Application();
        myExcel.Workbook excelDoc; //Excel文档变量
        myExcel.Worksheet ws;
        private void button1_Click(object sender, EventArgs e)
        {
            //myExcel.Application excelApp = new myExcel.Application(); //Excel应用程序变量,初始化
            string inputFileName = @"E:\C#\Example200\excelOper\excelOper\my.xlsx";          
            excelDoc = excelApp.Workbooks.Open(inputFileName);
            ws = (myExcel.Worksheet)excelDoc.Sheets[1];
            modify(ws);
            MessageBox.Show("执行完毕");
        }
        ///<summary>
        /// 获取指定文件的指定单元格内容
        ///</summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <returns>返回单元指定单元格内容</returns>
        public string getExcelOneCell(string fileName, int row, int column)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets[1];
            string temp = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[row, column]).Text.ToString();
            wbook.Close(false, fileName, false);
            app.Quit();
            NAR(app);
            NAR(wbook);
            NAR(workSheet);
            return temp;
        }
        //此函数用来释放对象的相关资源
        private void NAR(Object o)
        {
            try
            {
                //使用此方法，来释放引用某些资源的基础 COM 对象。 这里的o就是要释放的对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally
            {
                o = null; GC.Collect();
            }
        }
        private void modify(myExcel.Worksheet ws)
        {
            if (textBox1.Text == "" || textBox2.Text == "")
            {
                MessageBox.Show("修改数据行号或设定数据项为空", "错误提示");
                return;
            }
            int row = int.Parse(textBox1.Text);
            string temp = ((myExcel.Range)ws.Cells[row, 1]).Text.ToString();
            ws.Cells[row, 2] = textBox2.Text;
            textBox3.AppendText(temp + "\t" + textBox2.Text);
            textBox3.AppendText("\n");
        }
        private void button2_Click(object sender, EventArgs e)
        {
            string outputFileName = @"E:\C#\Example200\excelOper\excelOper\modify_my.xlsx";
            object Nothing;
            Nothing = Missing.Value;//给Nothing一个缺省的值
            object format = myExcel.XlFileFormat.xlWorkbookDefault;
            //将excelDoc文档对象的内容保存为XLSX文档
            excelDoc.SaveAs(outputFileName, format, Nothing, Nothing, Nothing, Nothing, myExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
            excelDoc.Close(Nothing, Nothing, Nothing);
            //关闭excelApp组件对象
            excelApp.Quit();
            MessageBox.Show("导出完毕");
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            // 调用垃圾回收
            GC.Collect();
        }
    }
}