c#中使用excel

標籤：winform   io   os   使用   ar   strong   for   檔案   資料   

在做一個小項目，需要把一些查詢結果匯出到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;

 

二、樣本程式

1. 在Visual Studio .NET中建立一個C# WinForm工程.
2. 添加Microsoft Excel Object Library引用:
1. 按右鍵Project , 選“添加引用”
2. 在COM 標籤項，選中 locate Microsoft Excel Object Library
3. 點確定按鈕完成添加引用。 On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.
3. 在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" );         }      }

c#中使用excel