给你操作Excel的代码,微软的 /****************************** Module Header ******************************\ * Module Name: Program.cs * Project: CSAutomateExcel * Copyright (c) Microsoft Corporation. * * The CSAutomateExcel example demonstrates how to use C# codes to create an * Microsoft Excel instance, create a workbook, and fill data into the * specified range, as well as how to clean up unmanaged COM resources and * quit the Excel application properly. * * This source is subject to the Microsoft Public License. * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL. * All other rights reserved. * * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. \***************************************************************************/#region Using directives using System; using System.Collections.Generic; using System.Text; using System.IO; using System.Reflection;using Excel = Microsoft.Office.Interop.Excel; using System.Runtime.InteropServices; #endregion class Program { static void Main(string[] args) { object missing = Type.Missing; Excel.Application oXL = null; Excel.Workbooks oWBs = null; Excel.Workbook oWB = null; Excel.Worksheet oSheet = null; Excel.Range oCells = null; Excel.Range oRng1 = null; Excel.Range oRng2 = null; ///////////////////////////////////////////////////////////////////// // Create an instance of Microsoft Excel and make it invisible. // oXL = new Excel.Application(); //Visible = true; //如果只想用程序控制该excel而不想让用户操作时候 oXL.Visible = false; Console.WriteLine("Excel.Application is started"); ///////////////////////////////////////////////////////////////////// // Create a new Workbook. // oWBs = oXL.Workbooks; oWB = oWBs.Add(missing); Console.WriteLine("A new workbook is created"); ///////////////////////////////////////////////////////////////////// // Get the active Worksheet and set its name. // oSheet = oWB.ActiveSheet as Excel.Worksheet; oSheet.Name = "Report"; Console.WriteLine("The active worksheet is renamed as Report"); ///////////////////////////////////////////////////////////////////// // Fill data into the worksheet's cells. // Console.WriteLine("Filling data into the worksheet ..."); // Set the column header oCells = oSheet.Cells; oCells[1, 1] = "First Name"; oCells[1, 2] = "Last Name"; oCells[1, 3] = "Full Name"; // Construct an array of user names string[,] saNames = new string[,] { {"John", "Smith"}, {"Tom", "Brown"}, {"Sue", "Thomas"}, {"Jane", "Jones"}, {"Adam", "Johnson"}}; // Fill A2:B6 with an array of values (First and Last Names). oRng1 = oSheet.get_Range("A2", "B6"); oRng1.Value2 = saNames; // Fill C2:C6 with a relative formula (=A2 & " " & B2). oRng2 = oSheet.get_Range("C2", "C6"); oRng2.Formula = "=A2 & \" \" & B2"; ///////////////////////////////////////////////////////////////////// // Save the workbook as a xlsx file and close it. // Console.WriteLine("Save and close the workbook"); string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly(). Location) + "\\MSDN.xls"; oWB.SaveAs(fileName,missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); oWB.Close(missing, missing, missing); ///////////////////////////////////////////////////////////////////// // Quit the Excel application. // Console.WriteLine("Quit the Excel application"); // Excel will stick around after Quit if it is not under user control // and there are outstanding references. When Excel is started or // attached programmatically and Excel.Application.Visible = false, // Excel.Application.UserControl is false. The UserControl property // can be explicitly set to True which should force the application // to terminate when Quit is called, regardless of outstanding // references. oXL.UserControl = true; oXL.Quit(); ///////////////////////////////////////////////////////////////////// // Clean up the unmanaged COM resources. // // Explicitly call Marshal.FinalReleaseComObject on all accessor // objects. See http://support.microsoft.com/kb/317109. Marshal.FinalReleaseComObject(oRng2); oRng2 = null; Marshal.FinalReleaseComObject(oRng1); oRng1 = null; Marshal.FinalReleaseComObject(oCells); oCells = null; Marshal.FinalReleaseComObject(oSheet); oSheet = null; Marshal.FinalReleaseComObject(oWB); oWB = null; Marshal.FinalReleaseComObject(oWBs); oWBs = null; Marshal.FinalReleaseComObject(oXL); oXL = null; // [-and/or-] // Force a garbage collection as soon as the calling function is off // the stack (at which point these objects are no longer rooted) and // then call GC.WaitForPendingFinalizers. GC.Collect(); GC.WaitForPendingFinalizers(); // GC needs to be called twice in order to get the Finalizers called // - the first time in, it simply makes a list of what is to be // finalized, the second time in, it actually the finalizing. Only // then will the object do its automatic ReleaseComObject. GC.Collect(); GC.WaitForPendingFinalizers(); } }然后附加说明: 一、添加引用 添加com组件(Microsoft Office 11.0 Object Library )命名空间为Microsoft.Office.Interop.Excel 即Microsoft.Office.Interop.Excel.dll 你必须安装office 添加Excel.exe引用默认路径为C:\Program Files\Microsoft Office\OFFICE11\Excel.exe
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using Microsoft.Office.Interop.Excel; namespace TestAccess { class Program { static void Main(string[] args) {
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"; strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;"; strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open(); OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection); DataSet ds = new DataSet(); myCommandd.Fill(ds, "[Sheet1$]"); System.Data.DataTable dt = ds.Tables["[Sheet1$]"]; Console.WriteLine(dt.Columns[0].ToString()); Console.WriteLine(dt.Columns[1].ToString()); DataRow drDisplay = dt.Rows[0]; int[] num = new int[dt.Columns.Count]; for (int j = 0; ; ) { for (int i = 0; i < dt.Columns.Count; i++) {
if (drDisplay[i] is DBNull) ; else num[i] += Convert.ToInt32(drDisplay[i]);
} if (++j >= dt.Rows.Count) break; drDisplay = dt.Rows[j]; } objConnection.Close(); object MissingValue = Type.Missing; Microsoft.Office.Interop.Excel.Application app = new Application(); Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; for (int i = 0; i < dt.Columns.Count; i++) { //注意下面是i+1,,excel小标默认从1开始 wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString(); }
如果存在,就可以用OleDb打开并Insert数据了,当然更好的方式就是在.NET操作Excel对象来创建。思路给你了,你的作业还是自己来完成吧。
{
try
{
// 打开进行操作
}
catch (Exception ex)
{
MessageBox.Show("错误\r\n" + ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
Excel的打开和追加内容,你百度下就有了
/****************************** Module Header ******************************\
* Module Name: Program.cs
* Project: CSAutomateExcel
* Copyright (c) Microsoft Corporation.
*
* The CSAutomateExcel example demonstrates how to use C# codes to create an
* Microsoft Excel instance, create a workbook, and fill data into the
* specified range, as well as how to clean up unmanaged COM resources and
* quit the Excel application properly.
*
* This source is subject to the Microsoft Public License.
* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
* All other rights reserved.
*
* THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
* EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Reflection;using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
#endregion
class Program
{
static void Main(string[] args)
{
object missing = Type.Missing;
Excel.Application oXL = null;
Excel.Workbooks oWBs = null;
Excel.Workbook oWB = null;
Excel.Worksheet oSheet = null;
Excel.Range oCells = null;
Excel.Range oRng1 = null;
Excel.Range oRng2 = null;
/////////////////////////////////////////////////////////////////////
// Create an instance of Microsoft Excel and make it invisible.
// oXL = new Excel.Application();
//Visible = true; //如果只想用程序控制该excel而不想让用户操作时候
oXL.Visible = false;
Console.WriteLine("Excel.Application is started");
/////////////////////////////////////////////////////////////////////
// Create a new Workbook.
// oWBs = oXL.Workbooks;
oWB = oWBs.Add(missing);
Console.WriteLine("A new workbook is created");
/////////////////////////////////////////////////////////////////////
// Get the active Worksheet and set its name.
// oSheet = oWB.ActiveSheet as Excel.Worksheet;
oSheet.Name = "Report";
Console.WriteLine("The active worksheet is renamed as Report");
/////////////////////////////////////////////////////////////////////
// Fill data into the worksheet's cells.
// Console.WriteLine("Filling data into the worksheet ..."); // Set the column header
oCells = oSheet.Cells;
oCells[1, 1] = "First Name";
oCells[1, 2] = "Last Name";
oCells[1, 3] = "Full Name"; // Construct an array of user names
string[,] saNames = new string[,] {
{"John", "Smith"},
{"Tom", "Brown"},
{"Sue", "Thomas"},
{"Jane", "Jones"},
{"Adam", "Johnson"}}; // Fill A2:B6 with an array of values (First and Last Names).
oRng1 = oSheet.get_Range("A2", "B6");
oRng1.Value2 = saNames; // Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng2 = oSheet.get_Range("C2", "C6");
oRng2.Formula = "=A2 & \" \" & B2";
/////////////////////////////////////////////////////////////////////
// Save the workbook as a xlsx file and close it.
// Console.WriteLine("Save and close the workbook"); string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().
Location) + "\\MSDN.xls";
oWB.SaveAs(fileName,missing,
missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
oWB.Close(missing, missing, missing);
/////////////////////////////////////////////////////////////////////
// Quit the Excel application.
// Console.WriteLine("Quit the Excel application"); // Excel will stick around after Quit if it is not under user control
// and there are outstanding references. When Excel is started or
// attached programmatically and Excel.Application.Visible = false,
// Excel.Application.UserControl is false. The UserControl property
// can be explicitly set to True which should force the application
// to terminate when Quit is called, regardless of outstanding
// references.
oXL.UserControl = true; oXL.Quit();
/////////////////////////////////////////////////////////////////////
// Clean up the unmanaged COM resources.
// // Explicitly call Marshal.FinalReleaseComObject on all accessor
// objects. See http://support.microsoft.com/kb/317109.
Marshal.FinalReleaseComObject(oRng2);
oRng2 = null;
Marshal.FinalReleaseComObject(oRng1);
oRng1 = null;
Marshal.FinalReleaseComObject(oCells);
oCells = null;
Marshal.FinalReleaseComObject(oSheet);
oSheet = null;
Marshal.FinalReleaseComObject(oWB);
oWB = null;
Marshal.FinalReleaseComObject(oWBs);
oWBs = null;
Marshal.FinalReleaseComObject(oXL);
oXL = null; // [-and/or-] // Force a garbage collection as soon as the calling function is off
// the stack (at which point these objects are no longer rooted) and
// then call GC.WaitForPendingFinalizers.
GC.Collect();
GC.WaitForPendingFinalizers();
// GC needs to be called twice in order to get the Finalizers called
// - the first time in, it simply makes a list of what is to be
// finalized, the second time in, it actually the finalizing. Only
// then will the object do its automatic ReleaseComObject.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}然后附加说明:
一、添加引用
添加com组件(Microsoft Office 11.0 Object Library )命名空间为Microsoft.Office.Interop.Excel
即Microsoft.Office.Interop.Excel.dll 你必须安装office
添加Excel.exe引用默认路径为C:\Program Files\Microsoft Office\OFFICE11\Excel.exe
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
}
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx