需求说明:给定表格要修改指定项目的值
1 添加excel引用
2 添加名称空间
1. using myExcel = Microsoft.Office.Interop.Excel; 2. using System.Reflection;//为了调用缺省方法Missing
3 完整代码与界面展示
使用方法:制定数据项的行数和要设定的值,点击单项修改,添加设定值到数据表中;
多项数据添加完毕后,点击数据导出,即可输出修改后的数据记录
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(); } } }