C#中使用Excel
2024-07-21 02:19:16
供稿：网友
在做一个小项目，需要把一些查询结果导出到excel，找了一些资料，自己也总结出了一点方法，与大家共享。
一、首先简要描述一下如何操作excel表
先要添加对excel的引用。选择项目-〉添加引用-〉com-〉添加microsoft excel 9.0。（不同的office讲会有不同版本的dll文件）。
using excel;
using system.reflection;
//产生一个excel.application的新进程
excel.application app = new excel.application();
if (app == null)
{
statusbar1.text = "error: excel couldn''t be started!";
return ;
}
app.visible = true; //如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
app.usercontrol = true;
workbooks workbooks =app.workbooks;
_workbook workbook = workbooks.add(xlwbatemplate.xlwbatworksheet); //根据模板产生新的workbook
// _workbook workbook = workbooks.add("c://a.xls"); //或者根据绝对路径打开工作簿文件a.xls
sheets sheets = workbook.worksheets;
_worksheet worksheet = (_worksheet) sheets.get_item(1);
if (worksheet == null)
{
statusbar1.text = "error: worksheet == null";
return;
}
// this paragraph puts the value 5 to the cell g1
range range1 = worksheet.get_range("a1", missing.value);
if (range1 == null)
{
statusbar1.text = "error: range == null";
return;
}
const int ncells = 2345;
range1.value2 = ncells;
二、示例程序
在visual studio .net中建立一个c# winform工程.
添加microsoft excel object library引用:
右键单击project , 选“添加引用”
在com 标签项，选中 locate microsoft excel object library
点确定按钮完成添加引用。 on the view menu, select toolbox to display the toolbox. add two buttons and a check box to form1.
在form1上添加一个button1，双击 button1，添加click事件的代码.把数组里的数据填到excel表格。
首先添加引用：
using system.reflection;
using excel = microsoft.office.interop.excel;
声明两个类的成员变量
excel.application objapp;
excel._workbook objbook;
private void button1_click(object sender, system.eventargs e)
{
excel.workbooks objbooks;
excel.sheets objsheets;
excel._worksheet objsheet;
excel.range range;
try
{
// instantiate excel and start a new workbook.
objapp = new excel.application();
objbooks = objapp.workbooks;
objbook = objbooks.add( missing.value );
objsheets = objbook.worksheets;
objsheet = (excel._worksheet)objsheets.get_item(1);
//get the range where the starting cell has the address
//m_sstartingcell and its dimensions are m_inumrows x m_inumcols.
range = objsheet.get_range("a1", missing.value);
range = range.get_resize(5, 5);
if (this.fillwithstrings.checked == false)
{
//create an array.
double[,] saret = new double[5, 5];
//fill the array.
for (long irow = 0; irow < 5; irow++)
{
for (long icol = 0; icol < 5; icol++)
{
//put a counter in the cell.
saret[irow, icol] = irow * icol;
}
}
//set the range value to the array.
range.set_value(missing.value, saret );
}
else
{
//create an array.
string[,] saret = new string[5, 5];
//fill the array.
for (long irow = 0; irow < 5; irow++)
{
for (long icol = 0; icol < 5; icol++)
{
//put the row and column address in the cell.
saret[irow, icol] = irow.tostring() + "|" + icol.tostring();
}
}
//set the range value to the array.
range.set_value(missing.value, saret );
}
//return control of excel to the user.
objapp.visible = true;
objapp.usercontrol = true;
}
catch( exception theexception )
{
string errormessage;
errormessage = "error: ";
errormessage = string.concat( errormessage, theexception.message );
errormessage = string.concat( errormessage, " line: " );
errormessage = string.concat( errormessage, theexception.source );
messagebox.show( errormessage, "error" );
}
}
4.在form1上添加一个button2，双击 button2，添加click事件的代码，从excel表格读数据到数组:
private void button2_click(object sender, system.eventargs e)
{
excel.sheets objsheets;
excel._worksheet objsheet;
excel.range range;
try
{
try
{
//get a reference to the first sheet of the workbook.
objsheets = objbook.worksheets;
objsheet = (excel._worksheet)objsheets.get_item(1);
}
catch( exception theexception )
{
string errormessage;
errormessage = "can't find the excel workbook. try clicking button1 " +
"to create an excel workbook with data before running button2.";
messagebox.show( errormessage, "missing workbook?");
//you can't automate excel if you can't find the data you created, so
//leave the subroutine.
return;
}
//get a range of data.
range = objsheet.get_range("a1", "e5");
//retrieve the data from the range.
object[,] saret;
saret = (system.object[,])range.get_value( missing.value );
//determine the dimensions of the array.
long irows;
long icols;
irows = saret.getupperbound(0);
icols = saret.getupperbound(1);
//build a string that contains the data of the array.
string valuestring;
valuestring = "array data/n";
for (long rowcounter = 1; rowcounter <= irows; rowcounter++)
{
for (long colcounter = 1; colcounter <= icols; colcounter++)
{
//write the next value into the string.
valuestring = string.concat(valuestring,
saret[rowcounter, colcounter].tostring() + ", ");
}
//write in a new line.
valuestring = string.concat(valuestring, "/n");
}
//report the value of the array.
messagebox.show(valuestring, "array values");
}
catch( exception theexception )
{
string errormessage;
errormessage = "error: ";
errormessage = string.concat( errormessage, theexception.message );
errormessage = string.concat( errormessage, " line: " );
errormessage = string.concat( errormessage, theexception.source );
messagebox.show( errormessage, "error" );
}
}
三、更多内容
《how to: transfer data to an excel workbook by using visual c# .net》描述了多种方式（如数组、数据集、ado.net、xml）把数据导到excel表格的方法。
如果你需要把大数据量倒入到excel 表的话，建议使用 clipboard(剪贴板)的方法。实现方法参看上面的连接，讨论参看：http://expert.csdn.net/expert/topic/3086/3086690.xml
倒完数据后，在程序退出之前，如果需要结束excel 的进程，讨论参看：http://expert.csdn.net/expert/topic/3068/3068466.xml
讨论的结果就是：提前垃圾回收，或者杀死进程。
