文章 合集 资源 图书 工具 捐赠

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

豫ICP备11002860-1

公安机关备案41152102000002号

http://caohuayu.com    本网站及微信公众号内容均为原创，转载请注明出处