一个操作EXCEL的C#类ExcelUtils

来源：互联网发布：尔雅网络课程网址编辑：程序博客网时间：2024/05/28 22:11

最近在公司里一直从事服务类的工作，涉及到很多excel的处理。部分工作内容是每天重复的，只是每天的数据不同而已。我遇到的一个问题是客户每天发送的几种数据有些excel中的字段顺序是一致的，有些是不一致的，而对数据汇总就要一列一列的去调整，剪切，复制，粘贴，很麻烦。还有类似导入、导出数据、类似的问题。熟悉EXCEL的人肯定知道，其实EXCEL中为我们提供了很多常用的功能，比如筛选、排序、透视表统计等，只是需要手动去操作。实际这些常用操作完全可以用程序去替代，解放我们的双手。需要注意的是，写这种工具要注意一些数据中的细节，或者在程序处理前，我们定义一些约定俗成的规则，比如文件名，比如excel表中确保每个页都存在表头等，之后再用我们的程序代替excel去自动处理，这样就一键搞定。

想到写这种工具，我会想到考虑用C#、python之类的语言，简单粗暴，方便快捷，相比c/c++开发快的多，由于笔者对python GUI的部分不是很了解，而且如果用python，每次在命令行执行，想想也很麻烦，于是决定采用.net framework中提供对office操作的接口，采用C#语言来实现。

用C#操作EXCEL，一种方法是将EXCEL文件作为数据源，像数据库一样的读写，可以采用odbc的方式，例如：

        #region 将Excel文件路径和页名称转换成DataTable        /// <param name="str_path">excel文件路径</param>        /// <param name="str_sheet">页名称</param>        /// <returns>返回一个DataTable对象</returns>        public static System.Data.DataTable ExcelToDT(string str_path, string str_sheet)        {            string str_conn = "Provider=Microsoft.ACE.OLEDB.12.0;" +            //string str_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" +                                 "Data Source=" + str_path + ";" +                                 "Extended Properties=Excel 8.0;";            OleDbConnection ole_conn = new OleDbConnection(str_conn);            ole_conn.Open();            string str_excel = "select * from [" + str_sheet + "$]";            OleDbDataAdapter ole_cmd = new OleDbDataAdapter(str_excel, str_conn);            DataSet ds = new DataSet();            ole_cmd.Fill(ds);            return ds.Tables[0];        }        #endregion

另一种用Visual Studio Tools for Office API Reference ，它是运行使用 Microsoft Visual Studio生成的基于office的解决方案所必需的api。

这里不会讲解关于该api如何使用，我将他封装成了一个类，如果你急需处理EXCEL而没有时间去了解Office API，那么可以使用这个封装好的类，相信使用起来更方便，由于时间仓促，也只是实现了基本的功能，不过对于一般的处理已经足够。

了解之前：

确保你已经引用了Microsoft.Excel 这个com

首先你要知道：

1.一个ExcelUtils对象对应一个excel中的一个页，也就是说我们通常只关注一个页中的二维数据，就像数据库中的一个表，或者如果你熟悉Ado.net，可以把它想象成一个DataTable,相信以后不断完善，它会变成一个DataSet，不过时间仓促，我也暂时封装到这里，实现功能先~~~

2.EXCEL中的行号和列号都是从1开始的，不是0！而且如果excel数据中有表头，那么数据部分的起始位置为2,，1为表头。

使用方法，非常简单：

1.打开数据

ExcelUtils exUtil = new ExcelUtils();exUtil.Open("xxxx.xlsx", "Sheet1");

使用简单，如果你熟悉Ado,net 相信你对conn.Open()一定不会陌生，是的，我们通过如下代码，打开某个Excel文件的某个页

这里我们打开"xxxx.xlsx"这个excel文件中的"Sheet1"这个页,自此exUtil就对应这个页中的二维表

2.读写数据

这里ExcelUtils类中提供了一些基本的excel操作方法。

SetValue方法，该方法设置某单元格的值

/// <param name="row">行号</param>/// <param name="col">列号</param>/// <param name="str_value">待写入的值</param>public void SetValue(int row, int col, string str_value);

GetValue方法，与SetValue方法相对应，获取某单元格的值

/// <param name="row">行号</param>/// <param name="col">列号</param>/// <returns>该单元格的string值</returns>public string GetValue(int row, int col);

GetColNoByName方法，该方法通过字段名返回该字段所在的列号，以方便通过列号对数据进行读写

/// <param name="colName">要查找的列名</param>/// <returns>找到返回序号，找不到返回-1</returns>public int GetColNoByName(string colName);

GetCurSheetUsedRangeRowsCount方法，好吧，名字有点长~，有待该进。。。该方法用户获取当前页中已用的最大行号

/// <returns>返回已用的最大行号</returns>public int GetCurSheetUsedRangeRowsCount();

有时我们需要知道数据在当前页中最末尾在哪一行，然后接下来附加数据在最大行号的下一行开始写数据，如下图中，调用GetCurSheetUsedRangeRowsCount()将返回4，个人认为这个比较常用.

BoxToBoxWrite方法，很好理解，拷贝一个单元格的值到另一个单元格，这两个单元格可以在不同的excel文件中。

/// <param name="getUtil">获取数据的ExcelUtils对象</param>/// <param name="g_row">获取数据的ExcelUtils对象的某单元格行号</param>/// <param name="g_col">获取数据的ExcelUtils对象的某单元格列号</param>/// <param name="setUtil">待写入数据的ExcelUtils对象</param>/// <param name="s_row">待写入数据的ExcelUtils对象的某单元格行号</param>/// <param name="s_col">待写入数据的ExcelUtils对象的某单元格列号</param>public static void BoxToBoxWrite(ExcelUtils getUtil, int g_row, int g_col,                                                                  ExcelUtils setUtil, int s_row, int s_col);

ColToColWrite方法，将某excel中的某一字段从o_row_start到o_row_end拷贝到另一个excel某字段，并且从另一个excel的s_row_start行开始写入

注意：如果你想从excel的已用最大行下一行开始写入，那么s_row_start通常为：先调用GetCurSheetUsedRangeRowsCount()获取已用最大行号后 +1，表示从已用最大行下一行开始写入

/// <param name="origUtil">源ExcelUtils对象</param>       /// <param name="origColName">源ExcelUtils对象中要操作的列名</param>     /// <param name="o_row_start">复制数据的起始行号</param>      /// <param name="o_row_end">复制数据的结束行号</param>     /// <param name="srcUtil">待写入的ExcelUtil对象</param>     /// <param name="srcColName">待写入的列名</param>       /// <param name="s_row_start">从s_row_start行开始写入</param>      public static void ColToColWrite(ExcelUtils origUtil, string origColName, int o_row_start, int o_row_end,                                     ExcelUtils srcUtil, string srcColName, int s_row_start)

CloseAndSave方法

最后，同样类似Ado.net，Open之后不要忘记关闭，这里ExcelUtils对象对应一个Excel中的页，调用CloseAndSave()，之前的写入操作才会保存。

public void CloseAndSave();

ExcelUtils完整代码

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.Office.Interop.Excel;using System.Reflection;using System.IO;namespace AutoReportDeal{        class ExcelUtils    {            public Microsoft.Office.Interop.Excel.Application xlsApp = null;         public Microsoft.Office.Interop.Excel.Workbook workbook = null;            public Microsoft.Office.Interop.Excel.Worksheet worksheet = null;            public string str_this_path = null;         public string str_this_sheet = null;                  #region 打开某EXCEL文件的某个页            /// <param name="str_path">EXCEL文件路径</param>         /// <param name="str_sheet">要操作的页</param>                public void Open(string str_path, string str_sheet)            {            str_this_path = str_path;             str_this_sheet = str_sheet;             //Excel Application             xlsApp = new Microsoft.Office.Interop.Excel.Application();                       //Excel Workbook                       workbook = xlsApp.Workbooks.Open(str_path, 0, true, 5,                                             System.Reflection.Missing.Value,                                              System.Reflection.Missing.Value,                                                false, System.Reflection.Missing.Value,                                             System.Reflection.Missing.Value, true,                                             false, System.Reflection.Missing.Value,                                                false, false, false);              //Excel Worksheet                    worksheet = (Worksheet)workbook.Worksheets[str_sheet];           }         #endregion                 #region 将值写入某单元格          /// <param name="row">行号</param>          /// <param name="col">列号</param>          /// <param name="str_value">待写入的值</param>          public void SetValue(int row, int col, string str_value)              {            if (row <= 0 || col <= 0 || str_value == null)                                throw new Exception("参数不合法");                                worksheet.Cells[row, col] = str_value;               }        #endregion                #region 获取当前可用页中的已用的最大行号              /// <returns>返回已用的最大行号</returns>        public int GetCurSheetUsedRangeRowsCount()              {                      if (xlsApp == null)                               throw new Exception("ExcelUtils对象尚未Open()");                            int used_rng_rows = worksheet.UsedRange.Rows.Count;                     return used_rng_rows;             }        #endregion                #region 查找某字段名的列号(列号从1开始)              /// <param name="colName">要查找的列名</param>             /// <returns>找到返回序号，找不到返回-1</returns>            public int GetColNoByName(string colName)              {                    int col_used = worksheet.UsedRange.Columns.Count;                   for (int i = 1; i <= col_used; ++i)                        {                              if (GetValue(1, i).ToString().Trim() == colName)                              return i;                       }                   return -1;              }               #endregion                       #region 得到某一单元格的值                /// <param name="row">行号</param>              /// <param name="col">列号</param>               /// <returns>该单元格的string值</returns>           public string GetValue(int row, int col)           {                   if (row <= 0 || col <= 0)                            throw new Exception("参数不合法");                               Range myRange = null;                       myRange = worksheet.get_Range(worksheet.Cells[row, col], worksheet.Cells[row, col]);                  string str = myRange.Text.ToString();                     return str;             }        #endregion                      #region 将某excel当前页的某单元格的值写入到另一个excel当前页的某单元格          /// <param name="getUtil">获取数据的ExcelUtils对象</param>             /// <param name="g_row">获取数据的ExcelUtils对象的某单元格行号</param>            /// <param name="g_col">获取数据的ExcelUtils对象的某单元格列号</param>            /// <param name="setUtil">待写入数据的ExcelUtils对象</param>             /// <param name="s_row">待写入数据的ExcelUtils对象的某单元格行号</param>           /// <param name="s_col">待写入数据的ExcelUtils对象的某单元格列号</param>            public static void BoxToBoxWrite(ExcelUtils getUtil, int g_row, int g_col,                                                       ExcelUtils setUtil, int s_row, int s_col)                {            if (getUtil == null || setUtil == null)                throw new Exception("ExcelUtils对象尚未Open()");                                 if (g_row <= 0 || g_col <= 0 || s_row <= 0 || s_col <= 0)                         throw new Exception("参数不合法");                                   string str_to_write = getUtil.GetValue(g_row, g_col);                 setUtil.SetValue(s_row, s_col, str_to_write);              }               #endregion                   #region 将某excel页中某列从o_row_start到o_row_end的数据写入到另一个Excel页中，并从s_row_start行位置开始写入              /// <param name="origUtil">源ExcelUtils对象</param>               /// <param name="origColName">源ExcelUtils对象中要操作的列名</param>             /// <param name="o_row_start">复制数据的起始行号</param>              /// <param name="o_row_end">复制数据的结束行号</param>             /// <param name="srcUtil">待写入的ExcelUtil对象</param>             /// <param name="srcColName">待写入的列名</param>               /// <param name="s_row_start">从s_row_start行开始写入</param>              public static void ColToColWrite(ExcelUtils origUtil, string origColName, int o_row_start, int o_row_end,                                            ExcelUtils srcUtil, string srcColName, int s_row_start)            {                       if (origUtil.worksheet == null || srcUtil.worksheet == null)                       throw new Exception("ExcelUtils对象尚未Open()");                                  if (origColName == null || srcColName == null || o_row_start <= 0 ||                         o_row_end <= 0 || s_row_start <= 0 || o_row_start > o_row_end)                               throw new Exception("参数不合法");                                   int o_col_index = origUtil.GetColNoByName(origColName);                        if (o_col_index < 0)                               throw new Exception("列名不存在");                                   int s_col_index = srcUtil.GetColNoByName(srcColName);                     if (s_col_index < 0)                             throw new Exception("列名不存在");                                  for (int i = o_row_start, j = s_row_start; i <= o_row_end; ++i, ++j)                    {                              BoxToBoxWrite(origUtil, i, o_col_index, srcUtil, j, s_col_index);                     }         }        #endregion                #region 保存并关闭                public void CloseAndSave()            {                      xlsApp.DisplayAlerts = false;                 xlsApp.AlertBeforeOverwriting = false;                             if (File.Exists(str_this_path))                    {                File.Delete(str_this_path);                   }                        xlsApp.ActiveWorkbook.SaveCopyAs(str_this_path);                xlsApp.Quit();                     xlsApp = null;                      workbook = null;                       worksheet = null;                      str_this_path = null;             }        #endregion        }}

时间紧急，先到这里~~

0 0