Use Excel in C #
Export some of the query results to Excel, some of the information found
First, first briefly describe how to operate the Excel table
First add a reference to Excel. Select Project -> Add References -> COM-> Add Microsoft Excel 9.0. (Different Office will have different versions of DLL files). Using Excel; Using System.Reflection; // Generate an Excel.Application App = New Excel.Application App = New Excel.Application (); if (App == Null) {Statusbar1.Text = "Error: Excel COULDN' ' BE Started! "; Return;} app.visible = true; // If you just use the program to control the Excel without wanting the user to operate, you can set to falseapp.userControl = true; Workbooks Workbooks = App.Workbooks; _Workbook Workbook = Workbooks.add (xlwbatemplate.xlwbatworksheet); // Generate new workbook // _Workbook workbook = workbooks.add ("c: //a.xls") according to the template; // or open workbook file A.xls according to the absolute path
Sheets Sheets = Workbook.worksheet; _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 G1RANGE RANGE1 = Worksheet.get_Range ("A1", MISSING.VALUE); if (Range1 == Null) {statusbar1.text = "error: Range == NULL"; return; CONST INT ncells = 2345; Range1.Value2 = ncells;
Second, the sample program
Create a C # WinForm project in Visual Studio .NET. Add Microsoft Excel Object Library reference:
Right-click Project, select "Add Reference" in the COM tab, select the Locate Microsoft Excel Object Library point OK button to complete the addition reference. On The View Menu, Select Toolbox To Display The Toolbox. Add TWO Buttons and a check box to form1. Add a Button1 on Form1, double-click Button1, add the code of the Click event. Fill in the data in the array to the Excel form.
First add a reference:
Using system.reflection; using excel = microsoft.Office.Interop.Excel;
Declare two class members variables
Excel.Application Objapp;
Excel._Workbook Objbook;
Private void button1_click (Object sender, system.eventargs e) {
Excel.Workbooks objBooks;
Excel.sheets objsheet;
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.worksheet;
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 colorn 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. Add a Button2 on Form1, double-click Button2, add the code of the Click event, read data from the Excel form to an array:
Private void button2_click (Object Sender, System.Eventargs E)
{
Excel.sheets objsheet;
Excel._Worksheet Objsheet;
Excel.range Range;
Try
{
Try
{
// Get a reason to the first sheet of the workbook.
Objsheets = objbook.worksheet;
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");
}
}
Third, more
"How to: Transfer Data to An Excel Workbook by Using Visual C # .NET" describes a variety of ways (such as arrays, data sets, ADO.NET, XML) to lead data to Excel form.
If you need to pour the large amount of data into the Excel table, it is recommended to use the Clipboard method. Refer to the above connection, discussion, see: http://expert.9cbs.net/expert/topic/3086/3086690.xml
After completing the data, before the program exits, if you need to end the Excel process, discuss the results: http://expert.9cbs.net/expert/topic/3068/3068466.xml The result of the discussion is: Early garbage collection, or kill Death process.
