乐于分享
好东西不私藏

C#中使用Excel对象模型

C#中使用Excel对象模型

本文讨论如何在C#代码中通过Excel对象模型写入和读取Excel数据,并封装相关代码。

引用Excel对象库

在Visual Studio中,可以在“解决方案资源管理器”>>“引用”的右键菜单选择“添加引用”,在打开的“引用管理器”中,选中“COM”页,选中列表中的“Microsoft Excel 16.0 Object Library”项,然后点击右下角的“安装”按钮完成引用。这里,16.0是版本号,表示Excel2016,不同计算机中显示的数字可能不同,但不会影响本文代码的测试。

#图#引用Excel对象库

将数据写入新的Excel文件

使用Excel对象模型写入Excel数据的代码封装在cfx/msoffice/tExcelWriter.cs文件,先看如下的代码。

C#

using System.IO;

using Microsoft.Office.Interop.Excel;

namespace cfx.msoffice

{

public class tExcelWriter

{

private string myFileName;

private System.Data.DataTable myData;

private string mySheetName;

private int myBeginRow;

private int myBeginColumn;

private bool myWriteColumnName;

//

private tExcelWriter() { }

//

public static tExcelWriter Create(string filename,

System.Data.DataTable data,   string sheetName = null,

int beginRow = 1, int beginCol =   1,

bool writeColumnName = true)

{

return new tExcelWriter()

{

myFileName = filename,

myData = data,

mySheetName = sheetName,

myBeginRow = beginRow,

myBeginColumn = beginCol,

myWriteColumnName =   writeColumnName

};

}

//

public bool Write()

{

if (myData == null ||   myData.Columns.Count == 0) 

return false;

//

if (File.Exists(myFileName))

File.Delete(myFileName);

// 

if (mySheetName == null ||   mySheetName.Trim().Length == 0)

mySheetName = "数据";

//

Application xapp = new   Application();

xapp.Visible = false;

Workbook wb =   xapp.Workbooks.Add();

Worksheet ws =   wb.Worksheets.Add();

ws.Name = mySheetName;

//

int curRow = myBeginRow;

// 写入列名行

if (myWriteColumnName)

{

for(int col = 0; col <   myData.Columns.Count; col++)

{

int cellCol = col +   myBeginColumn;

ws.Cells[curRow,   cellCol].Value = 

myData.Columns[col].ColumnName;

}

curRow++;

}

// 写入数据

for(int row = 0; row <   myData.Rows.Count; row++)

{

for(int col = 0; col <   myData.Columns.Count; col++)

{

int cellCol = col +   myBeginColumn;

ws.Cells[curRow,   cellCol].Value =

myData.Rows[row][col];

}

curRow++;

}

//

string ext =   Path.GetExtension(myFileName).ToLower();

if (ext == ".xls")// excel97

wb.SaveAs(myFileName,   XlFileFormat.xlExcel8);

else

wb.SaveAs(myFileName);

wb.Close();

xapp.Quit();

return true;

}

}

}

代码中,在cfx.msoffice命名空间中创建了tExcelWriter类,其中的私有字段和Create()静态方法的参数相对应,下面以方法的参数说明其含义。

  • filename,指定要写入的Excel文件路径。指定的文件已存在时会先删除。

  • data,定义包含写入数据的System.Data.DataTable对象。因为Excel对象模型中也有DataTable类型,所以,这里使用了类型的完整路径。

  • sheetName,指定写入工作表的名称。默认为null,此时会使用“数据”。

  • beginRow和beginCol,指定开始写入数据的单元格行号和列号。请注意,Excel对象模型中的工作表索引、行号、列号都是从1开始的。

  • writeColumnName,bool类型,指定是否将DataTable中的列名写入第一行数据,默认为true。

接下来的Write()方法会执行写入操作,其中,主要使用了Application、Workbook、Worksheet等类型的Excel对象,下面分别讨论。

Application对象(xapp),表示Excel应用程序,方法中首先创建了一个新的应用实例,然后将Visible属性设置为false,即不显示Excel应用界面。如果想观察Excel的操作,也可以将其设置为true(默认值)。对象中的Workbooks属性表示应用处理的工作簿集合。

Workbook对象(wb)表示一个工作簿,方法中,使用应用对象Workbooks集合的Add()方法添加一个新的工作簿,并返回对象。工作簿对象的Worksheets属性表示工作簿中处理的工作表集合。

Worksheet对象(ws)表示一个工作表,方法中使用工作簿对象Worksheets集合的Add()方法添加一个新的工作表,并返回对象。添加工作表后,使用Name属性指定工作表的名称。从Worksheets集合获取已存在的工作表对象时,可以使用从1开始的索引或工作表名称作为索引。

获取写入单元格(区域)时使用了工作表对象的Cells属性,其索引分别是行号和列号,其中,行号是从1开始的索引,列号可以使用从1开始的索引,也可以使用字母作为索引,实际工作中使用数值索引会更加直观。使用行号和列号返回的类型为Range对象,使用其中的Value属性可以设置或读取单元格区域的数据。

数据写入完成后,会通过文件扩展名判断写入的文件类型,如果扩展名为.xls则保存为Excel97格式;否则保存为Excel对象库当前版本的默认格式。

工作簿的SaveAs()方法可以将Excel文件保存到指定的路径,这里保存为myFileName字段指定的位置。方法的第二个参数可以指定保存的文件类型,其中XlFileFormat.xlExcel8枚举值表示Excel 8,即Excel97格式的文件。

保存数据后,使用工作簿对象的Close()方法关闭Excel文件,使用Application对象的Quit()方法退出当前Excel应用实例。

正确处理Excel数据写入后,方法会返回true。

测试完成后,确认代码无误可以在Write()方法中添加try...catch结构,防止实际工作中出现异常时,会将异常直接抛出,导致应用意外终止。

下面的代码,在Program.cs文件中测试Excel写入操作。

C#

using System;

using System.Data;

using cfx.msoffice;

namespace csfx_demo

{

class Program

{

static void Main(string[] args)

{

DataTable data =   tApp.DbJet.GetTable("select * from t1");

bool result =   tExcelWriter.Create(@"d:\tmp.xlsx", data).Write();

Console.WriteLine(result);

}

}

}

代码中,首先从t1表中读取全部数据;然后写入到d:\tmp.xlsx文件中,正确执行会显示True。这里,可以修改tExcelWriter.Create()方法的参数来观察数据写入的效果。

将数据写入已存在的Excel文件

下面的代码(cfx/msoffice/tExcelWriter.cs),在tExcelWriter类中创建WriteExisted()方法,用于将数据写入已存在的Excel文件。

C#

using System.IO;

using Microsoft.Office.Interop.Excel;

namespace cfx.msoffice

{

public class tExcelWriter

{

// 其它代码

//

public bool WriteExisted()

{

if (myData == null ||   myData.Columns.Count == 0)

return false;

//

if (File.Exists(myFileName) ==   false)

return false;

//

Application xapp = new   Application();

xapp.Visible = false;

Workbook wb =   xapp.Workbooks.Open(myFileName);

//

Worksheet ws = null;

for(int i = 1; i <=   wb.Worksheets.Count; i++)

{

if (wb.Worksheets[i].Name ==   mySheetName)

ws = wb.Worksheets[i];

}

if (ws == null)

{

ws = wb.Worksheets.Add();

}

//

int curRow = myBeginRow;

// 写入列名行

if (myWriteColumnName)

{

for (int col = 0; col <   myData.Columns.Count; col++)

{

int cellCol = col +   myBeginColumn;

ws.Cells[curRow,   cellCol].Value = 

myData.Columns[col].ColumnName;

}

curRow++;

}

// 写入数据

for (int row = 0; row <   myData.Rows.Count; row++)

{

for (int col = 0; col <   myData.Columns.Count; col++)

{

int cellCol = col +   myBeginColumn;

ws.Cells[curRow,   cellCol].Value =

myData.Rows[row][col];

}

curRow++;

}

//

wb.Save();

wb.Close();

xapp.Quit();

return true;

}

}

}

将数据写入已存在的Excel文件时,首先使用Excel应用(Application)对象Workbooks集合的Open()方法打开文件,其参数为文件的路径。

确定工作表时,如果mySheetName字段包含了有效的工作表,则打开此工作表,否则,将在文件中创建一个新的工作表,并使用默认名称,如Sheet1、Sheet2、Sheet3、……。

写入数据后,直接调用工作簿(Workbook)对象的Save()方法保存文件。并且要关闭工作簿、退出应用。操作成功后,最后返回true值。

下面的代码,在Program.cs文件中测试tExcelWriter.WriteExistsed()方法的应用。

C#

using System;

using System.Data;

using cfx.msoffice;

namespace csfx_demo

{

class Program

{

static void Main(string[] args)

{

DataTable data =   tApp.DbJet.GetTable("select * from t1");

bool result =   tExcelWriter.Create(@"d:\tmp.xlsx", data, "Sheet1")

.WriteExisted();

Console.WriteLine(result);

}

}

}

代码会将t1表的数据写入d:\tmp.xlsx文件的Sheet1工作表。

下面的代码(cfx/msoffice/tExcelWriter.cs),在tExcelWriter类中添加WriteByTemplate()方法,用于将数据写入模板文件。

C#

using System.IO;

using Microsoft.Office.Interop.Excel;

namespace cfx.msoffice

{

public class tExcelWriter

{

// 其它代码

//

public bool WriteByTemplate(string template)

{

try

{

if (File.Exists(template) ==   false)

return false;

File.Copy(template,   myFileName, true);

return WriteExisted();

}

catch { return false; }

}

//

}

}

WriteExists()方法的参数需要指定模板文件的路径;方法中,首先会将模板文件复制到指定的位置(myFileName),然后调用WriteExisted()方法写入数据。

读取Excel数据

下面的代码(cfx/msoffice/tExcelReader.cs)会创建tExcelReader类,用于读取Excel文件中指定工作表的数据。

C#

using System.IO;

using System.Data;

using Microsoft.Office.Interop.Excel;

namespace cfx.msoffice

{

public class tExcelReader

{

// Excel文件名

private string myFileName;

// 读取式作表索引

private int mySheetIndex;

// 如果设置工作表名称,则优先使用

private string mySheetName;

// 开始读取数据的行和列

private int myBeginColumn;

private int myBeginRow;

// 第一行作为列名

private bool myFirstRowAsColumnName;

// 读取的行数,大于0时有效,否则读取全部

private int myReadRowCount;

//

private tExcelReader() { }

//

public static tExcelReader Create(string filename,

int sheetIndex = 1, string   sheetName = null,

int beginRow = 1, int beginCol =   1,

bool firstRowAsColName = true,   int readRowCount = -1)

{

return new tExcelReader()

{

myFileName = filename,

mySheetIndex = sheetIndex,

mySheetName = sheetName,

myBeginRow = beginRow,

myBeginColumn = beginCol,

myFirstRowAsColumnName =   firstRowAsColName,

myReadRowCount = readRowCount

};

}

//

public System.Data.DataTable Read()

{

if (File.Exists(myFileName) ==   false)

return null;

//

Application xapp = new   Application();

xapp.Visible = false;

Workbook wb =   xapp.Workbooks.Open(myFileName);

//

Worksheet ws = null;

for (int i = 1; i <=   wb.Worksheets.Count; i++)

{

if (wb.Worksheets[i].Name ==   mySheetName)

ws = wb.Worksheets[i];

}

if (ws == null)

{

if (mySheetIndex >= 1   && mySheetIndex <= wb.Worksheets.Count)

ws =   wb.Worksheets[mySheetIndex];

else

ws = wb.Worksheets[1];

}

// 

System.Data.DataTable data = new   System.Data.DataTable();

// 创建列

for (int col = myBeginColumn;

col <=   ws.UsedRange.Columns.Count; col++)

data.Columns.Add();

// 读取数据

int rowCounter = 0;

for(int row = myBeginRow; 

row <=   ws.UsedRange.Rows.Count; row++)

{

DataRow newRow =   data.NewRow();

for(int col = myBeginColumn; 

col <=   ws.UsedRange.Columns.Count; col++)

{

int colIndex = col -   myBeginColumn;

newRow[colIndex] =   ws.Cells[row, col].Value;

}

data.Rows.Add(newRow);

//

rowCounter++;

if (myReadRowCount > 0   && rowCounter == myReadRowCount)

break;

}

// 第一行提升为列名

if (myFirstRowAsColumnName)

{

string sName;

for (int col = 0; col <   data.Columns.Count; col++)

{

sName =   data.Rows[0][col].ToString().Trim();

if (sName !=   "")

data.Columns[col].ColumnName = sName;

}

data.Rows.RemoveAt(0);

}

//

wb.Close();

xapp.Quit();

//

return data;

}

//

}

}

tExcelReader类中首先定义了一些私有字段,Create()静态方法的参数与这些字段相对应,下面通过参数说明其含义。

  • filename,指定读取的Excel文件路径。

  • sheetIndex,指定读取的工作表索引,默认1,表示第一个工作表。如果指定的工作表索引不存在,同样读取第一个工作表。

  • sheetName,string类型,指定读取的工作表名称,默认为null。如果指定了有效的工作表名称则读取此工作表数据,如果指定的工作表名称不存在,则通过数值索引读取工作表。

  • beginRow和beginCol,指定开始读取数据的单元格的行号和列号(从1 开始)。

  • firstRowAsColName,是否将第一行数据作为DataTable对象中的列名,默认为true。

  • readRowCount,int类型,指定读取多少行数据,默认为-1,表示读取全部数据。大于0时指定最多读取的记录数量。

Read()方法中,首先通过Excel应用(Application)对象Workbooks集合的Open()方法打开Excel文件,并返回工作簿(Workbook)对象;然后根据工作表名称或数值索引打开工作表(Worksheet)对象。

需要注意的是,工作表(Workbook)对象的UsedRange属性表示实际使用的数据区域,其中的Rows属性表示数据行集合,Columns属性表示数据列集合,可以使用它们的Count属性获取工作表中数据实际使用的行数和列数。此外,行和列的索引是从1开始的。

接下来,会将数据读取到DataTable对象,并使用rowCounter变量作为读取行的计数,达到指定的行数时会停止读取。

将第一行作为DataTable对象的列名时,会将第一行的数据设置为对应列的列名,然后从DataTable对象的数据中删除第一行(索引0)

操作完成后需要关闭工作簿,并退库Excel应用。最后返回包含读取数据的DataTable对象。

下面的代码,在Program.cs文件中测试读取Excel数据的操作。

C#

using System;

using System.Data;

using System.IO;

using cfx.data;

using cfx.msoffice;

namespace csfx_demo

{

class Program

{

static void Main(string[] args)

{

DataTable data =   tExcelReader.Create(@"d:\tmp.xlsx",

sheetName: "数据")

.Read();

string s =   tDataHelper.ToHtmlTable(data);

File.WriteAllText(@"d:\tmp.html", s);

}

}

}

代码会读取d:\tmp.xlsx文件中“数据”工作表的数据,然后将数据写入d:\tmp.html文件,可以使用浏览器查看读取的数据。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料