Visual C# .NET 向 Excel 工作簿传输数据

技术2022-05-11 26

最常用于向 Excel 工作簿传输数据的方法是“自动化”。利用“自动化”功能，您可以调用特定于 Excel 任务的方法和属性。“自动化”功能为您提供了指定数据在工作簿中所处的位置、将工作簿格式化以及在运行时进行各种设置的最大的灵活性。利用“自动化”功能，您可以使用多种方法来传输数据：逐个单元格地传输数据。将数组中的数据传输到单元格区域。使用“CopyFromRecordset”方法向单元格区域传输 ADO 记录集中的数据。在 Excel 工作表上创建一个“QueryTable”对象，该对象包含对 ODBC 或 OLEDB 数据源进行查询的结果。将数据传输到剪贴板，然后将剪贴板内容粘贴到 Excel 工作表中。还可以使用多种未必需要利用“自动化”功能来向 Excel 传输数据的方法。如果您正在运行服务器端程序，这会是一种将批量数据处理从客户端移走的好方法。要在不使用“自动化”功能的情况下传输数据，您可以使用下列方法：将数据传输到制表符分隔的或逗号分隔的文本文件，然后 Excel 可以将该文本文件分析为工作表上的单元格。使用 ADO.NET 将数据传输到工作表。将 XML 数据传输到 Excel（仅限于 2002 和 2003 版）以提供可以被格式化和排列为行和列的数据。本文讨论了其中的每一种方法并提供了每一种方法的代码示例。本文的创建完整的示例 Visual C# 2005 或 Visual C# .NET 项目一节（在本文稍后部分）演示了如何创建执行每一种方法的 Visual C# .NET 程序。方法使用“自动化”功能逐单元格传输数据利用“自动化”功能，您可以逐单元格地向工作表传输数据： // 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)); // Add data to cells in the first worksheet in the new workbook. 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_objRange.Value = "Last Name"; m_objRange = m_objSheet.get_Range("B1", m_objOpt); m_objRange.Value = "First Name"; m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange.Value = "Doe"; m_objRange = m_objSheet.get_Range("B2", m_objOpt); m_objRange.Value = "John"; // Apply bold to cells A1:B1. m_objRange = m_objSheet.get_Range("A1", "B1"); m_objFont = m_objRange.Font; m_objFont.Bold=true; // Save the Workbook and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book1.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(); 如果您具有少量的数据，则逐单元格地传输数据是可以接受的方法。您可以灵活地将数据放到工作簿中的任何地方，并可以在运行时根据条件对单元格进行格式设置。不过，如果您具有大量需要传输到 Excel 工作簿的数据，则使用这种方法不是一个好主意。您在运行时获取的每一个“Range”对象都会产生一个接口请求，这意味着数据传输速度会变得较慢。此外，Microsoft Windows 95、Microsoft Windows 98 以及 Microsoft Windows Millennium Edition (Me) 都对接口请求有 64 KB 的限制。如果您具有 64 KB 以上的接口请求，则“自动化”服务器 (Excel) 可能会停止响应，或者您可能会收到指出内存不足的错误消息。有关其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 216400 (http://support.microsoft.com/kb/216400/ ) PRB：Win95/98 上的进程间 COM 自动化可使客户端应用程序挂起需要再次强调的是，逐单元格地传输数据仅对少量数据而言才可以接受。如果您必须向 Excel 传输大型数据集，则应考虑使用本文中讨论的其他方法之一来批量地传输数据。有关其他信息以及如何利用 Visual C# .NET 自动运行 Excel 的示例，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 302084 (http://support.microsoft.com/kb/302084/ ) 如何在 Microsoft Visual C# .NET 中使 Microsoft Excel 自动运行使用“自动化”功能将数据数组传输到工作表上的区域可以将数据数组一次性地传输到由多个单元格组成的区域： // 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(); 如果您使用数组而不是逐单元格地传输数据，则在传输大量数据时，传输性能会大大地增强。请考虑前面代码中的下面几行，这些行将数据传输到工作表中的 300 个单元格： objRange = objSheet.get_Range("A2", m_objOpt); objRange = objRange.get_Resize(100,3); objRange.Value = objData; 上面的代码代表了两个接口请求：一个请求是针对“Range”方法返回的“Range”对象，另一个请求是针对“Resize”方法返回的“Range”对象。相比之下，逐单元格地传输数据却需要对“Range”对象发出 300 个接口请求。只要有可能，您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 302096 (http://support.microsoft.com/kb/302096/ ) 如何在 Visual C# 中使 Excel 自动运行以使用数组填充或获取某个区域中的数据使用“自动化”功能将 ADO 记录集传输到工作表区域 Excel 2000、Excel 2002 和 Excel 2003 的对象模型提供了“CopyFromRecordset”方法，用于向工作表上的区域传输 ADO 记录集。下面的代码说明了如何使用“CopyFromRecordset”方法使 Excel 自动运行，以传输 Northwind 示例数据库中的“订单”表的内容： // 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(); 注意：“CopyFromRecordset”只能与 ADO“Recordset”对象一起使用。使用 ADO.NET 创建的“DataSet”不能与“CopyFromRecordset”方法一起使用。以下几节中的多个示例演示了如何利用 ADO.NET 向 Excel 传输数据。使用“自动化”功能在工作表上创建 QueryTable 对象 “QueryTable”对象代表了一个表，该表是用从外部数据源返回的数据生成的。当您自动运行 Excel 时，可以通过提供指向 OLE DB 或 ODBC 数据源的连接字符串和 SQL 字符串来创建“QueryTable”。Excel 将生成记录集并将该记录集插入到工作表中您所指定的位置。“QueryTable”对象提供了下列优于“CopyFromRecordset”方法的优点： Excel 处理记录集的创建并将其放置到工作表中。您可以利用“QueryTable”对象保存查询，并在以后刷新它以获取更新的记录集。当向工作表中添加新的“QueryTable”时，可以指定将工作表上的单元格中已经存在的数据移位，以处理新数据（有关更多信息，请查看“RefreshStyle”属性）。下面的代码演示了如何自动运行 Excel 2000、Excel 2002 或 Excel 2003，以便使用 Northwind 示例数据库中的数据在 Excel 工作表中创建新的“QueryTable”： // 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(); 使用 Windows 剪贴板可以使用 Windows 剪贴板来向工作表传输数据。要将数据粘贴到工作表上的多个单元格中，可以复制具有以下格式的字符串：在该字符串中，列由制表符分隔，行由回车符分隔。下面的代码说明了 Visual C# .NET 如何使用 Windows 剪贴板来向 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(); 创建可由 Excel 分析为行和列的带分隔符的文本文件 Excel 可以打开由制表符或逗号分隔的文件并正确地将数据分析为单元格。当您希望向工作表传输大量数据而只使用少量（如果有的话）自动化功能时，可以使用此功能。这对于客户端-服务器程序而言可能是一个好方法，因为文本文件可以在服务器端生成。然后，可以在客户端根据需要使用“自动化”功能来打开文本文件。下面的代码说明了如何从利用 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(); 上述代码没有使用“自动化”功能。不过，如果您愿意，您可以按如下方式使用“自动化”功能来打开文本文件，并以 Excel 工作簿格式保存该文件： // Open the text file in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "Book6.txt", Excel.XlPlatform.xlWindows, 1, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book6.xls", Excel.XlFileFormat.xlWorkbookNormal, 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(); 使用 ADO.NET 将数据传输到工作表您可以使用 Microsoft Jet OLE DB 提供程序向现有的 Excel 工作簿中的表中添加记录。Excel 中的表只是由单元格组成的区域；该区域可能具有定义的名称。通常，区域的第一行包含标题（或字段名），该区域中所有以后的行都包含记录。下面的代码向 Book7.xls 中的表中添加了两个新记录。在此示例中，该表是 Sheet1： // 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(); 当您按本示例所示的方法利用 ADO.NET 添加记录时，工作簿中的格式将被保持。添加到行中的每个记录都将继承它前面的行的格式。有关使用 ADO.NET 的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 306636 (http://support.microsoft.com/kb/306636/ ) 如何使用 ADO.NET 2005 和 Visual C# 2005 或者 ADO.NET 和 Visual C# .NET 连接到数据库并运行命令 314145 (http://support.microsoft.com/kb/314145/ ) 如何使用 Visual C# .NET 从数据库填充 DataSet 对象 307587 (http://support.microsoft.com/kb/307587/ ) 如何使用 Visual C# 2005 或 Visual C# .NET 从 DataSet 对象更新数据库有关如何将 Jet OLEDB 提供程序与 Excel 数据源一起使用的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章。 316934 (http://support.microsoft.com/kb/316934/ ) 如何在 Visual Basic .NET 中使用 ADO.NET 检索和修改 Excel 工作簿中的记录 278973 (http://support.microsoft.com/kb/278973/ ) ExcelADO 演示如何在 Excel 工作簿中使用 ADO 来读写数据 257819 (http://support.microsoft.com/kb/257819/ ) 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据传输 XML 数据（Excel 2002 和 Excel 2003） Excel 2002 和 2003 可以打开格式完好的任何 XML 文件。您可以使用“文件”菜单上的“打开”命令直接打开 XML 文件，也可以使用“Workbooks”集合的“Open”或“OpenXML”方法以编程方式打开 XML 文件。如果您创建供在 Excel 中使用的 XML 文件，您还可以创建样式表来设置数据的格式。有关如何将 XML 与 Excel 2002 一起使用的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 307029 (http://support.microsoft.com/kb/307029/ ) 如何使用 Visual C# 向 Microsoft Excel 2002 传输 XML 数据 288215 (http://support.microsoft.com/kb/288215/ ) INFO：Microsoft Excel 2002 和 XML 回到顶端创建完整的示例 Visual C# .NET 项目创建一个名为 C:/ExcelData 的新文件夹。示例程序将在此文件夹中存储 Excel 工作簿。创建一个新工作簿，以供示例向其中写入数据：在 Excel 中启动一个新工作簿。在新工作簿的 Sheet1 上，在单元格 A1 中键入 FirstName，在单元格 B1 中键入 LastName。选择 A1:B1。在“插入”菜单上，指向“名称”，然后单击“定义”。键入名称 MyTable，然后单击“确定”。将该工作簿另存为 C:/Exceldata/Book7.xls。退出 Excel。启动 Microsoft Visual Studio 2005 或 Microsoft Visual Studio .NET。在“文件”菜单上，指向“新建”，然后单击“项目”。在“Visual C# 项目”或“Visual C#”下，选择“Windows 应用程序”。默认情况下会创建 Form1。添加对 Excel 对象库和 ADODB 主互操作程序集的引用。为此，请按照下列步骤操作：在“项目”菜单上，单击“添加引用”。在“NET”选项卡上，找到“ADODB”，然后单击“选择”。注意：在 Visual Studio 2005 中，您不需要单击“选择”。在“COM”选项卡上，找到“Microsoft Excel 10.0 对象库或 Microsoft Excel 11.0 对象库”，然后单击“选择”。注意：在 Visual Studio 2005 中，您不需要单击“选择”。注意：如果您正在使用 Microsoft Excel 2002，并且尚未这样做，Microsoft 建议您下载并安装 Microsoft Office XP 主互操作程序集 (PIA)。有关 Office XP PIA 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 328912 (http://support.microsoft.com/kb/328912/ ) Microsoft Office XP主互操作程序集 (PIA) 可供下载在“添加引用”对话框中，单击“确定”以接受您的选择。向 Form1 中添加一个“Combo Box”控件和一个“Button”控件。为该窗体的“Load”事件和 Button 控件的“Click”事件添加事件处理程序：在 Form1.cs 的设计视图中，双击“Form1”。此时将创建该窗体的“Load”事件的处理程序，该处理程序出现在 Form1.cs 中。在“视图”菜单上，单击“设计器”以切换到设计视图。双击“Button1”。此时将创建该按钮的“Click”事件的处理程序，该处理程序出现在 Form1.cs 中。在 Form1.cs 中，将以下代码： private void Form1_Load(object sender, System.EventArgs e) { } private void button1_Click(object sender, System.EventArgs e) { } 替换为： // Excel object references. private Excel.Application m_objExcel = null; private Excel.Workbooks m_objBooks = null; private Excel._Workbook m_objBook = null; private Excel.Sheets m_objSheets = null; private Excel._Worksheet m_objSheet = null; private Excel.Range m_objRange = null; private Excel.Font m_objFont = null; private Excel.QueryTables m_objQryTables = null; private Excel._QueryTable m_objQryTable = null; // Frequenty-used variable for optional arguments. private object m_objOpt = System.Reflection.Missing.Value; // Paths used by the sample code for accessing and storing data. private object m_strSampleFolder = "C://ExcelData//"; private string m_strNorthwind = "C://Program Files//Microsoft Office//Office10//Samples//Northwind.mdb"; private void Form1_Load(object sender, System.EventArgs e) { comboBox1.DropDownStyle = ComboBoxStyle.DropDownList; comboBox1.Items.AddRange(new object[]{ "Use Automation to Transfer Data Cell by Cell ", "Use Automation to Transfer an Array of Data to a Range on a Worksheet ", "Use Automation to Transfer an ADO Recordset to a Worksheet Range ", "Use Automation to Create a QueryTable on a Worksheet", "Use the Clipboard", "Create a Delimited Text File that Excel Can Parse into Rows and Columns", "Transfer Data to a Worksheet Using ADO.NET "}); comboBox1.SelectedIndex = 0; button1.Text = "Go!"; } private void button1_Click(object sender, System.EventArgs e) { switch (comboBox1.SelectedIndex) { case 0 : Automation_CellByCell(); break; case 1 : Automation_UseArray(); break; case 2 : Automation_ADORecordset(); break; case 3 : Automation_QueryTable(); break; case 4 : Use_Clipboard(); break; case 5 : Create_TextFile(); break; case 6 : Use_ADONET(); break; } //Clean-up m_objFont = null; m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBooks = null; m_objBook = null; m_objExcel = null; GC.Collect(); } private void Automation_CellByCell() { // 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)); // Add data to cells of the first worksheet in the new workbook. 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_objRange.set_Value(m_objOpt,"Last Name"); m_objRange = m_objSheet.get_Range("B1", m_objOpt); m_objRange.set_Value(m_objOpt,"First Name"); m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange.set_Value(m_objOpt,"Doe"); m_objRange = m_objSheet.get_Range("B2", m_objOpt); m_objRange.set_Value(m_objOpt,"John"); // Apply bold to cells A1:B1. m_objRange = m_objSheet.get_Range("A1", "B1"); m_objFont = m_objRange.Font; m_objFont.Bold=true; // Save the workbook and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } private void Automation_UseArray() { // 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.set_Value(m_objOpt,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.set_Value(m_objOpt,"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_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } private void Automation_ADORecordset() { // 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.set_Value(m_objOpt,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_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); //Close the recordset and connection objRS.Close(); objConn.Close(); } private void Automation_QueryTable() { // 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_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } private void Use_Clipboard() { // Copy a string to the 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_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } private void Create_TextFile() { // 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(); // ================================================================== // Optionally, automate Excel to open the text file and save it in the // Excel workbook format. // Open the text file in Excel. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBooks.OpenText(m_strSampleFolder + "Book6.txt", Excel.XlPlatform.xlWindows, 1, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook; // Save the text file in the typical workbook format and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book6.xls", Excel.XlFileFormat.xlWorkbookNormal, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } private void Use_ADONET() { // 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(); } } // End Class }// End namespace 注意：您必须更改 Visual Studio 2005 中的代码。默认情况下，当创建一个 Windows 窗体项目时，Visual C# 会向该项目中添加一个窗体。该窗体被命名为 Form1。代表该窗体的两个文件被命名为 Form1.cs 和 Form1.designer.cs。您在 Form1.cs 中编写代码。Form1.designer.cs 文件是 Windows 窗体设计器编写代码的地方，这些代码可以实现通过拖放工具箱中的控件而执行的所有操作。有关 Visual C# 2005 中的 Windows 窗体设计器的更多信息，请访问下面的 Microsoft Developer Network (MSDN) 网站： http://msdn2.microsoft.com/zh-cn/library/ms173077.aspx (http://msdn2.microsoft.com/zh-cn/library/ms173077.aspx) 注意：如果您没有将 Office 安装到默认文件夹 (C:/Program Files/Microsoft Office) 中，请修改代码示例中的“m_strNorthwind”常量以匹配 Northwind.mdb 的安装路径。将下面的代码添加到 Form1.cs 中的“Using”指令中： using System.Reflection; using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel;

http://support.microsoft.com/kb/306023/zh-cn#12

专利

最新回复(0)