一个操作EXCEL的C#类ExcelUtils
近期在公司里一直从事服务类的工作，涉及到非常多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为表头。
用法，很easy：
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
}
} 时间紧急。先到这里~~
