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
討論的結果就是：提前垃圾回收，或者殺死進程。
