?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
302084
(http://support.microsoft.com/kb/302084/EN-US/
)
HOWTO: Automate Microsoft Excel from Microsoft Visual C# .NET
Use Automation to Transfer an Array of Data to a Range on a Worksheet
You can transfer an array of data to a range of multiple cells at one time:
// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
// Create an array for the headers and add it to cells A1:C1.
object[] objHeaders = {"Order ID", "Amount", "Tax"};
m_objRange = m_objSheet.get_Range("A1", "C1");
m_objRange.Value = objHeaders;
m_objFont = m_objRange.Font;
m_objFont.Bold=true;
// Create an array with 3 columns and 100 rows and add it to
// the worksheet starting at cell A2.
object[,] objData = new Object[100,3];
Random rdm = new Random((int)DateTime.Now.Ticks);
double nOrderAmt, nTax;
for(int r=0;r<100;r++)
{
objData[r,0] = "ORD" + r.ToString("0000");
nOrderAmt = rdm.Next(1000);
objData[r,1] = nOrderAmt.ToString("c");
nTax = nOrderAmt*0.07;
objData[r,2] = nTax.ToString("c");
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(100,3);
m_objRange.Value = objData;
// Save the Workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book2.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
If you transfer your data by using an array instead of cell by cell, you can realize an enormous performance gain with a large quantity of data. Consider the following lines from the aforementioned code that transfer data to 300 cells in the worksheet:
This code represents two interface requests: one for theRangeobject that theRangemethod returns, and another for theRangeobject that theResizemethod returns. In contrast, transferring the data cell by cell requires requests for 300 interfaces toRange???????? ??? Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.
For additional information about using arrays to get and set values in ranges with Excel Automation, click the article number below
to view the article in the Microsoft Knowledge Base:
302096
(http://support.microsoft.com/kb/302096/EN-US/
)
HOWTO: Automate Excel With Visual C# .NET To Fill or Obtain Data In a Range Using Arrays
Use Automation to Transfer an ADO Recordset to a Worksheet Range
The object models for Excel 2000, Excel 2002 and Excel 2003 provide theCopyFromRecordsetmethod for transferring an ADO recordset to a range on a worksheet. The following code illustrates how to automate Excel to transfer the contents of the Orders table in the Northwind sample database by using theCopyFromRecordset????:
// Create a Recordset from all the records in the Orders table.
ADODB.Connection objConn = new ADODB.Connection();
ADODB._Recordset objRS = null;
objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
m_strNorthwind + ";", "", "", 0);
objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
object objRecAff;
objRS = (ADODB._Recordset)objConn.Execute("Orders", out objRecAff,
(int)ADODB.CommandTypeEnum.adCmdTable);
// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
// Get the Fields collection from the recordset and determine
// the number of fields (or columns).
System.Collections.IEnumerator objFields = objRS.Fields.GetEnumerator();
int nFields = objRS.Fields.Count;
// Create an array for the headers and add it to the
// worksheet starting at cell A1.
object[] objHeaders = new object[nFields];
ADODB.Field objField = null;
for(int n=0;n<nFields;n++)
{
objFields.MoveNext();
objField = (ADODB.Field)objFields.Current;
objHeaders[n] = objField.Name;
}
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange = m_objRange.get_Resize(1, nFields);
m_objRange.Value = objHeaders;
m_objFont = m_objRange.Font;
m_objFont.Bold=true;
// Transfer the recordset to the worksheet starting at cell A2.
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange.CopyFromRecordset(objRS, m_objOpt, m_objOpt);
// Save the Workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book3.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
// Close the recordset and connection.
objRS.Close();
objConn.Close();
???:CopyFromRecordsetworks only with ADORecordset???????? ??? You cannot use theDataSetthat you create by using ADO.NET with theCopyFromRecordset?????? ??? Several examples in the sections that follow demonstrate how to transfer data to Excel with ADO.NET.
Use Automation to Create a QueryTable Object on a Worksheet
AQueryTableobject represents a table that is built from data that is returned from an external data source. When you automate Excel, you can create aQueryTableby providing a connection string to an OLE DB or an ODBC data source and a SQL string. Excel generates the recordset and inserts the recordset into the worksheet at the location that you specify.QueryTableobjects offer the following advantages over theCopyFromRecordset????:
Excel handles the creation of the recordset and its placement on the worksheet.
You can save the query with theQueryTableobject and refresh it later to obtain an updated recordset.
When a newQueryTableis added to your worksheet, you can specify that data that already exists in cells on the worksheet be shifted to handle the new data (for more information, see theRefreshStyleproperty).
The following code demonstrates how to automate Excel 2000, Excel 2002, or Excel 2003 to create a newQueryTablein an Excel worksheet by using data from the Northwind sample database:
// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
// Create a QueryTable that starts at cell A1.
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objQryTables = m_objSheet.QueryTables;
m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
m_strNorthwind + ";", m_objRange, "Select * From Orders");
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.Refresh(false);
// Save the workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book4.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
Use the Windows Clipboard
You can use the Windows Clipboard to transfer data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string in which columns are delimited by TAB characters, and rows are delimited by carriage returns. The following code illustrates how Visual C# .NET can use the Windows Clipboard to transfer data to Excel:
// Copy a string to the Windows clipboard.
string sData = "FirstName\tLastName\tBirthdate\r\n" +
"Bill\tBrown\t2/5/85\r\n" +
"Joe\tThomas\t1/1/91";
System.Windows.Forms.Clipboard.SetDataObject(sData);
// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
// Paste the data starting at cell A1.
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objSheet.Paste(m_objRange, false);
// Save the workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book5.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
Create a Delimited Text File that Excel Can Parse into Rows and Columns
Excel can open tab- or comma-delimited files and correctly parse the data into cells. You can use this feature when you want to transfer a large quantity of data to a worksheet while using little, if any, Automation. This may be a good approach for a client-server program because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.
The following code illustrates how to generate a tab-delimited text file from data that is read with ADO.NET:
// Connect to the data source.
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strNorthwind + ";");
objConn.Open();
// Execute a command to retrieve all records from the Employees table.
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(
"Select * From Employees", objConn);
System.Data.OleDb.OleDbDataReader objReader;
objReader = objCmd.ExecuteReader();
// Create the FileStream and StreamWriter object to write
// the recordset contents to file.
System.IO.FileStream fs = new System.IO.FileStream(
m_strSampleFolder + "Book6.txt", System.IO.FileMode.Create);
System.IO.StreamWriter sw = new System.IO.StreamWriter(
fs, System.Text.Encoding.Unicode);
// Write the field names (headers) as the first line in the text file.
sw.WriteLine(objReader.GetName(0) + "\t" + objReader.GetName(1) +
"\t" + objReader.GetName(2) + "\t" + objReader.GetName(3) +
"\t" + objReader.GetName(4) + "\t" + objReader.GetName(5));
// Write the first six columns in the recordset to a text file as
// tab-delimited.
while(objReader.Read())
{
for(int i=0;i<=5;i++)
{
if(!objReader.IsDBNull(i))
{
string s;
s = objReader.GetDataTypeName(i);
if(objReader.GetDataTypeName(i)=="DBTYPE_I4")
{
sw.Write(objReader.GetInt32(i).ToString());
}
else if(objReader.GetDataTypeName(i)=="DBTYPE_DATE")
{
sw.Write(objReader.GetDateTime(i).ToString("d"));
}
else if (objReader.GetDataTypeName(i)=="DBTYPE_WVARCHAR")
{
sw.Write(objReader.GetString(i));
}
}
if(i<5) sw.Write("\t");
}
sw.WriteLine();
}
sw.Flush(); // Write the buffered data to the filestream.
// Close the FileStream.
fs.Close();
// Close the reader and the connection.
objReader.Close();
objConn.Close();
// Establish a connection to the data source.
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
"Book7.xls;Extended Properties=Excel 8.0;");
objConn.Open();
// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();
// Close the connection.
objConn.Close();
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
???? ?????? ????????
????? ?? ???? ????
