Using Excel in C #

Excel is doing a small project, you need to export some of the results of the query to Excel, find some information, they also summed up a little way to share with you.

First, a brief description of how to operate Excel table


To add a reference to Excel first. Select Project-〉 Add Reference-〉com-〉 add Microsoft Excel 9.0. (Different office will have different versions of DLL files).
Using Excel;
Using System.Reflection;

Create a new process of excel.application
Excel.Application app = new Excel.Application ();
if (app = null)
{
Statusbar1.text = "Error:excel couldn ' t be started!";
return;
}

App. Visible = true; Set to False if you want to use program control only for this Excel and do not want the user to operate
App. UserControl = true;

Workbooks workbooks =app. workbooks;

_workbook workbook = workbooks. ADD (Xlwbatemplate.xlwbatworksheet); Generate new Workbook from template
_workbook workbook = workbooks. ADD ("C:\\a.xls"); or open the workbook file based on the absolute path A.xls


Sheets Sheets = workbook. worksheets;
_worksheet worksheet = (_worksheet) sheets.get_item (1);
if (worksheet = null)
{
Statusbar1.text = "Error:worksheet = null";
Return
}


This is 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;



Second, the sample program


In Visual Studio. NET to establish a C # WinForm project.
Add Microsoft Excel Object Library Reference:
Right-click Project, select Add Reference
In COM tab entries, select locate Microsoft Excel Object Library
The Click OK button completes the Add Reference. On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.
Add a button1 to the Form1, double-click Button1, and add the code for the Click event. Fill in the data in the array into an Excel table.
Add a reference first:

Using System.Reflection;
Using Excel = Microsoft.Office.Interop.Excel;


Declaring a member variable 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" 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 is 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 the Form1, double-click Button2, add code for the Click event, read the data from the Excel table to the 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 the 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 a Excel workbook with data before running Button2."

MessageBox.Show (errormessage, "Missing Workbook");

Can ' t automate Excel if you can ' t find the data for your 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");
}

This is 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 content
How to:transfer the data to a Excel workbook by Using Visual C #. NET describes ways in which arrays, datasets, ado.net, and XML are directed to Excel tables.

If you need to pour large amounts of data into Excel tables, it is recommended that you use the ClipBoard (Clipboard) method. The implementation method see above connection, discussion see: Http://expert.csdn.net/Expert/topic/3086/3086690.xml

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