Use excel in C #

In a small project, you need to export some query results to excel, find some materials, and summarize some methods to share with you.

1. First, briefly describe how to operate an Excel worksheet

Add a reference to excel. Select project> Add reference> COM> to add Microsoft Excel 9.0. (Different office versions have different DLL files ).
Using Excel;
Using system. reflection;

// Generate a new process for excel. Application
Excel. Application APP = new excel. Application ();
If (APP = NULL)
{
Statusbar1.text = "error: Excel couldn't be started! ";
Return;
}

App. Visible = true; // if you only want to use a program to control the Excel file and do not want the user to operate it, you can set it to false.
App. usercontrol = true;

Workbooks workbooks = app. workbooks;

_ Workbook = workbooks. Add (xlwbatemplate. xlwbatworksheet); // generate a new workbook Based on the template
/// _ Workbook = workbooks. Add ("C: // a.xls"); // The actual workbook a.xls

Sheets sheets = Workbook. worksheets;
_ 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;

 

Ii. Sample program

1. Create a C # winform project in Visual Studio. NET.
2. AddMicrosoft Excel Object LibraryReference:
1. Right-clickProject, Select "add reference"
2. InComLabel, select the locateMicrosoft Excel Object Library
3. Click OK to add a reference. OnViewMenu, selectToolboxTo display the toolbox. Add two buttons and a check box to form1.
3. Add a button1 on form1 and double-clickButton1,Add the code for the click event. Fill in the data in the array to the Excel table.

First add reference:

Using system. reflection;
Using Excel = Microsoft. Office. InterOP. Excel;

Declare member variables of two classes

      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. AddButton2, Double-clickButton2,Add the code for the click event to read data from an Excel table to an array:

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" );         }      }

3. More content

How to: transfer data to an Excel Workbook by using Visual C #. net describes a variety of methods (such as arrays, datasets, and ADO. net, XML) to export data to an Excel table.

If you need to import a large amount of data to an Excel table, we recommend that you use the clipboard (Clipboard) method. For the implementation method, see the above connection, see: http://expert.csdn.net/Expert/topic/3086/3086690.xml

After the data, before the program exits, if you need to end the process of Excel, see: http://expert.csdn.net/Expert/topic/3068/3068466.xml
The result of the discussion is: garbage collection in advance or killing the process.