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文件，先看如下的代码。

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写入操作。

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文件。

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()方法的应用。

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()方法，用于将数据写入模板文件。

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文件中指定工作表的数据。

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数据的操作。

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文件，可以使用浏览器查看读取的数据。