程式師世界 >> 編程語言 >> C語言 >> 關於C語言 >> C#中使用Excel

C#中使用Excel

編輯：關於C語言

在做一個小項目，需要把一些查詢結果導出到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表格的方法。