C#中使用Excel
在做一个小项目，需要把一些查询结果导出到Excel，找了一些资料，自己也总结出了一点方法，与大家共享。
一、首先简要描述一下如何操作Excel表
先要添加对Excel的引用。选择项目-〉添加引用-〉COM-〉添加Microsoft Excel 9.0。（不同的office讲会有不同版本的dll文件）。
using Excel;
using System.Reflection;
//产生一个Excel.Application的新进程
Excel.Application app = new Excel.Application();
if (app == null)
{
statusBar1.Text = "ERROR: EXCEL couldn''''t be started!";
return ;
}
app.Visible = true; //如果只想用程序控制该excel而不想让用户操作时候，可以设置为false
app.UserControl = true;
Workbooks workbooks =app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //根据模板产生新的workbook
// _Workbook workbook = workbooks.Add("c://a.xls"); //或者根据绝对路径打开工作簿文件a.xls
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null)
{
statusBar1.Text = "ERROR: worksheet == null";
return;
}
// This 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;
二、示例程序
在Visual Studio .NET中建立一个C# WinForm工程.
添加Microsoft Excel Object Library引用:
右键单击Project , 选“添加引用”
在COM 标签项，选中 locate Microsoft Excel Object Library
点确定按钮完成添加引用。 On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.
在Form1上添加一个button1，双击 Button1，添加click事件的代码.把数组里的数据填到Excel表格。
首先添加引用：
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
声明两个类的成员变量
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 address 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 of 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.在Form1上添加一个Button2，双击 Button2，添加click事件的代码，从Excel表格读数据到数组:
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 first 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 an Excel workbook with data before running Button2.";
MessageBox.Show( errorMessage, "Missing Workbook?");
//You can''t automate Excel if you can''t find the data you 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");
}
//Report 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" );
}
}
三、更多内容
《HOW TO: Transfer Data to an Excel Workbook by Using Visual C# .NET》描述了多种方式（如数组、数据集、ADO.NET、XML）把数据导到Excel表格的方法。
如果你需要把大数据量倒入到Excel 表的话，建议使用 ClipBoard(剪贴板)的方法。实现方法参看上面的连接，讨论参看：http://expert.youkuaiyun.com/Expert/topic/3086/3086690.xml
倒完数据后，在程序退出之前，如果需要结束Excel 的进程，讨论参看：http://expert.youkuaiyun.com/Expert/topic/3068/3068466.xml
讨论的结果就是：提前垃圾回收，或者杀死进程。
-----------------------------------------------------------------------------------------------------
所有Excel的程序操作都来源于Excel的对象库Excel9.olb.本例也只是对这个东东做一个简单的操作了解。有告诫待于朋友们的具体了解：）也算是为我们站点上天天为Excel烦的兄弟们，指一条“明路”吧：）
首先的一步就是使用Tlbimp这个工具将Excel9.0的对象库文件Excel8.olb转换成为dll，这样才能做为.Net平台Assembly来使用：）操作如下：
TlbImp Excel9.olb Excel.dll
只要有了这个Excel.dll，现在我们就能使用Excel的各种操作函数了。
下面就让我们具体看看C#是如何使用这些东东吧。
1. 创建一个新Excel的Application:
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started");
return 0;
}
2. 让这个工程可见:
exc.set_Visible(0, true);
3. 获取WorkBooks集合:
Workbooks workbooks = exc.Workbooks;
4. 加入新的WorkBook:
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
5. 获取WorkSheets集合:
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR in worksheet == null");
}
6. 给单元格设置变量：
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range1, args1);
例程:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;
class Excel {
public static int Main() {
Application exc = new Application();
if (exc == null) {
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return 0;
}
exc.set_Visible(0, true);
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null) {
Console.WriteLine ("ERROR: worksheet == null");
}
Range range1 = worksheet.get_Range("C1", Missing.Value);
if (range1 == null) {
Console.WriteLine ("ERROR: range == null");
}
const int nCells = 1;
Object[] args1 = new Object[1];
args1[0] = nCells;
range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,range1, args1);
return 100;
}
}
现在我们来看看如何使用数组，他有些类似于设置单元格。仅仅需要的改变只是args2[0] = array2;
const int nCell = 5;
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int [nCell];
for (int i=0; i < array2.GetLength(0); i++) {
array2[i] = i+1;
}
Object[] args2 = new Object[1];
args2[0] = array2;
range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range2, args2);
----------------------------------------------------------------------------------------------------------------------
首先引入类库，Microsoft.Office.Interop.Word,然后进行编程。代码如下：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Word;
namespace WordTest
{
public partial class Form1 : Form
{
object strFileName;
Object Nothing;
Microsoft.Office.Interop.Word.Application myWordApp = new Microsoft.Office.Interop.Word.ApplicationClass();
Document myWordDoc;
string strContent = "";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
createWord();
//openWord();
}
private void createWord()
{
strFileName = System.Windows.Forms.Application.StartupPath + "test.doc";
if (System.IO.File.Exists((string)strFileName))
System.IO.File.Delete((string)strFileName);
Object Nothing = System.Reflection.Missing.Value;
myWordDoc = myWordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
#region 将数据库中读取得数据写入到word文件中
strContent = "你好nnr";
myWordDoc.Paragraphs.Last.Range.Text = strContent;
strContent = "这是测试程序";
myWordDoc.Paragraphs.Last.Range.Text = strContent;
#endregion
//将WordDoc文档对象的内容保存为DOC文档
myWordDoc.SaveAs(ref strFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
//关闭WordDoc文档对象
myWordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
//关闭WordApp组件对象
myWordApp.Quit(ref Nothing, ref Nothing, ref Nothing);
this.richTextBox1.Text = strFileName + "rn" + "创建成功";
}
private void openWord()
{
fontDialog1.ShowDialog();
System.Drawing.Font font = fontDialog1.Font;
object filepath = "D:asp.docx";
object oMissing = System.Reflection.Missing.Value;
myWordDoc = myWordApp.Documents.Open(ref filepath, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
myWordDoc.Content.Font.Size = font.Size;
myWordDoc.Content.Font.Name = font.Name;
myWordDoc.Save();
richTextBox1.Text = myWordDoc.Content.Text;
myWordDoc.Close(ref oMissing, ref oMissing, ref oMissing);
myWordApp.Quit(ref oMissing, ref oMissing, ref oMissing);
}
}
======================================
另外当然还需要引用Interop.Word.Dll.
代码如下：
///#region 打开Word文档,并且返回对象wDoc,wDoc
///
/// 打开Word文档,并且返回对象wDoc,wDoc
///
/// 完整Word文件路径+名称
/// 返回的Word.Document wDoc对象
/// 返回的Word.Application对象
public static void CreateWordDocument(string FileName,ref Word.Document wDoc,ref Word.Application WApp)
{
if(FileName == "") return;
Word.Document thisDocument = null;
Word.FormFields formFields = null;
Word.Application thisApplication = new Word.ApplicationClass();
thisApplication.Visible = true;
thisApplication.Caption = "";
thisApplication.Options.CheckSpellingAsYouType = false;
thisApplication.Options.CheckGrammarAsYouType = false;
Object filename = FileName;
Object ConfirmConversions = false;
Object ReadOnly = true;
Object AddToRecentFiles = false;
Object PasswordDocument = System.Type.Missing;
Object PasswordTemplate = System.Type.Missing;
Object Revert = System.Type.Missing;
Object WritePasswordDocument = System.Type.Missing;
Object WritePasswordTemplate = System.Type.Missing;
Object Format = System.Type.Missing;
Object Encoding = System.Type.Missing;
Object Visible = System.Type.Missing;
Object OpenAndRepair = System.Type.Missing;
Object DocumentDirection = System.Type.Missing;
Object NoEncodingDialog = System.Type.Missing;
Object XMLTransform = System.Type.Missing;
try
{
Word.Document wordDoc =
thisApplication.Documents.Open(ref filename, ref ConfirmConversions,
ref ReadOnly, ref AddToRecentFiles, ref PasswordDocument, ref PasswordTemplate,
ref Revert,ref WritePasswordDocument, ref WritePasswordTemplate, ref Format,
ref Encoding, ref Visible, ref OpenAndRepair, ref DocumentDirection,
ref NoEncodingDialog, ref XMLTransform );
thisDocument = wordDoc;
wDoc = wordDoc;
WApp = thisApplication;
formFields = wordDoc.FormFields;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
#endregion
调用上面静态方法，打开目标文件并且把DataGrid中数据填充到对应Word标签中去
///#region Word填充数据(For Example)
///
/// Word填充数据
///
private void WordLoadData()
{
Word.Document wDoc=null;
Word.Application wApp=null;
sysFun.CreateWordDocument("E://监测报告(new).dot",ref wDoc,ref wApp);
//对标签"C"进行填充
object bkmC="C";
if(wApp.ActiveDocument.Bookmarks.Exists("C") == true)
{
wApp.ActiveDocument.Bookmarks.get_Item
(ref bkmC).Select();
}
wApp.Selection.TypeText(this.txt1.Text);
object bkmG = "TWaterTable3";
object unit;
object count; //移动数
object extend;
extend = Word.WdMovementType.wdExtend;
unit = Word.WdUnits.wdCell;
//把DataGrid中数据填充到标签TWaterTable3上
if(wApp.ActiveDocument.Bookmarks.Exists("TWaterTable3") == true)
{
wApp.ActiveDocument.Bookmarks.get_Item
(ref bkmG).Select();
for(int i=0;i {
if(i==0)
{
count=1;
}
else
{
count=0;
}
//需填充5列数据
wApp.Selection.Move(ref unit,ref count);
wApp.Selection.TypeText(gridEX1.GetRow(i).Cells[0].Text);
count=1;
wApp.Selection.Move(ref unit,ref count);
wApp.Selection.TypeText(gridEX1.GetRow(i).Cells[1].Text);
wApp.Selection.Move(ref unit,ref count);
wApp.Selection.TypeText(gridEX1.GetRow(i).Cells[2].Text);
wApp.Selection.Move(ref unit,ref count);
wApp.Selection.TypeText(gridEX1.GetRow(i).Cells[3].Text);
wApp.Selection.Move(ref unit,ref count);
wApp.Selection.TypeText(gridEX1.GetRow(i).Cells[4].Text);
//换行
wApp.Selection.MoveRight(ref unit,ref count,ref extend);
}
}
}
#endregion
然后就OK了，在对标签表控制要注意列循环和换行.
C#操作Excel（导入导出）
有很多朋友说需要 C#导出到Excel的代码，现共享给大家
///
/// 读取Excel文档
///
/// 文件名称
/// 返回一个数据集
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"table1");
return ds;
}
///
/// 写入Excel文档
///
/// 文件名称
public bool S***eFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
//cmd.CommandText ="Update [sheet1$] SET 姓名='2005-01-01' Where 工号='日期'";
//cmd.ExecuteNonQuery ();
for(int i=0;i {
if(fp2.Sheets [0].Cells[i,0].Text!="")
{
cmd.CommandText ="Insert INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
}
这种方法是相当有效的。
下附：VB.NET版实现word打开与关闭，有兴趣的朋友可以研究一下
VB.NET实现word打开与关闭
Imports Word
'打开
Dim mWordapp As Word.Application 'word 应用程序
Dim mobjDoc As Word.Document 'word 文档
Dim fullFileName as string '文件路径
mWordapp = CreateObject("Word.Application")
mobjDoc = mWordapp.Documents.Add(FullFileName)
'关闭
Dim missing As Object = System.Reflection.Missing.Value
mWordapp.Application.Quit()
If Not mobjDoc Is Nothing Then
'垃圾回收
System.Runtime.InteropServices.Marshal.ReleaseComObject(mobjDoc)
mobjDoc = Nothing
End If
If Not mWordapp Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(mWordapp)
mWordapp = Nothing
End If
'真正释放word进程
GC.Collect()
扫一扫
330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
