目录
需求说明:给定表格要修改指定项目的值
1 添加excel引用
2 添加名称空间
using myExcel = Microsoft.Office.Interop.Excel;
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();
}
}
}