将 XML 数据传输到 Excel(仅限于 2002 和 2003 版)以提供可以被格式化和排列为行和列的数据。
方法
使用“自动化”逐个单元格地传输数据
利用“自动化”,您可以逐个单元格地向工作表传输数据:
// 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";
// 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 知识库中相应的文章:
// 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";
// 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 知识库中相应的文章:
// 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";
// 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/EN-US/) PRB:Cross-Process COM Automation Can Hang Client Application on Win95/98
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
// 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();
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
// 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();
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
这些代码代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
有关通过 Excel 自动化并使用数组获取和设置区域中的值的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
// 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();
有关使用 ADO.NET 的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
// 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();
有关使用 ADO.NET 的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
// 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();
Excel 2002 和 2003 可以打开格式完好的任何 XML 文件。您可以使用文件菜单上的打开命令直接打 开 XML 文件,也可以使用 Workbooks 集合的 Open 或 OpenXML 方法以编程方式打开 XML 文件。如果您创建供在 Excel 中使用的 XML 文件,您还可以创建样式表来设置数据的格式。
有关如 何将 XML 与 Excel 2002 一起使用的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
307029 (http://support.microsoft.com/kb/307029/EN-US/) HOW TO:使用 Visual C# .NET 向 Microsoft Excel 2002 传输 XML 数据
288215 (http://support.microsoft.com/kb/288215/EN-US/) INFO: Microsoft Excel 2002 and XML
在 COM 选项卡上,找到 Microsoft Excel 10.0 对象库或 Microsoft Excel 11.0 对象库,然后单击选择。注意:如果您正在使用 Microsoft Excel 2002,并且尚未下载并安装 Microsoft Office XP 主 Interop 程序集,Microsoft 建议您下载然后安装 Microsoft Office XP 主 Interop 程序集 (PIA)。有关 Office XP PIA 的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
328912 (http://support.microsoft.com/kb/328912/EN-US/) INFO:Microsoft Office XP PIA 可供下载
// 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";
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; }
// 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_objSh
请发表评论