C# Excel操作
这几天做一个小工具，需求是：1.服务器有一个Excel模板 2.用户通过配置文件来下载服务器上的Excel模板文件，并把配置文件内对模板指定位置插入数据。
根据需求我就上网对Excel操作的方式/方法，大概分为以下几种：
1.采用OleDB读取EXCEL文件：没研究（网络上说的）
优点：将Excel直接当做数据源处理，通过SQL直接读取内容，读取速度较快。
缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个Sheet页读取出来后（结果为Datatable）再在Datatable中根据行列数来获取指定的值。
当Excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。
2.引用微软提供的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件 
代码：
//读取EXCEL的方法
private void SetExcel(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Application excel = new Application();
string l_strSaveFileName = string.Empty;
string l_strOperationSheet = string.Empty;
string l_strOperationDB = string.Empty;
Hashtable ht = new Hashtable();
string l_strSavePath = string.Empty;
excel.Visible = false;
excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//根据指定的sheet名称取得指定工作薄
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
try
{
//取得总记录行数 (包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
//int colsint = ws.UsedRange.Cells.Columns.Count; //得到列数
for (int i = 1; i < rowsint; i++) //对工作表每一行
{
string cellValue = ws.UsedRange.Cells[i, 1].Text.ToString();
if (cellValue != "END")
{
if (cellValue == "ファイル名")
{
l_strSaveFileName = ws.UsedRange.Cells[i, 2].Text.ToString();
}
else if (cellValue == "シート名")
{
l_strOperationSheet = ws.UsedRange.Cells[i, 2].Text.ToString();
}
else if (cellValue == "対応付け")
{
ht[ws.UsedRange.Cells[i, 3].Text.ToString()] = ws.UsedRange.Cells[i, 2].Text.ToString();
}
}
}
//根据指定的sheet名称取得指定工作薄
ws = (Worksheet)wb.Worksheets.get_Item(l_strOperationSheet);
//设置指定区域内容 --C2,...
foreach (string item in ht.Keys)
{
ws.Cells.get_Range(item).Value = ht[item];
}
/*以下代码：主要是针对response下载文件而多做的，因为response无法将Workbook对象响应到客户端，所以我就用了以下方式：
*1.先把处理完成的Excel文件存放在服务器上的作为临时文件。
*2.把此临时文件转换为文件流
*3.Response.BinaryWrite方法把文件流响应到客户端。
*4.全部完成后，删除临时文件。
*/
//服务器临时文件
l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);
wb.Saved = true;
wb.SaveCopyAs(l_strSavePath);
using (FileStream fsRead = new FileStream(l_strSavePath, FileMode.Open))
{
int fsLen = (int)fsRead.Length;
byte[] heByte = new byte[fsLen];
int r = fsRead.Read(heByte, 0, heByte.Length);
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + l_strSaveFileName);
Response.ContentType = "application/excel";
Response.ContentEncoding = Encoding.Default;
Response.BinaryWrite(heByte);
Response.End();
}
}
finally
{
if (l_strSavePath != string.Empty && File.Exists(l_strSavePath))
{
File.Delete(l_strSavePath);
}
wb.Close(false, missing, missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
优点：能够非常灵活的读取Excel中的数据，用户可以灵活的调用各种函数进行处理。
缺点：基于单元格的处理，读取速度较慢，对于数据量较大的文件最好不要使用此种方式读取。
需要添加相应的DLL引用，必须存在此引用才可使用，如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。
注：此方法可以处理已制定好的模板，如：
3.NPOI方式读取Excel
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
优点：读取Excel速度较快，读取方式操作灵活性
缺点：需要下载相应的插件并添加到系统引用当中。并且会丢失模板的式样（如：在单元格内有值，单元格背景变色） --不适用于复杂模板
下载地址：http://npoi.codeplex.com/releases
public void NPOIGetExcelDB(string p_strFileName)
{
StringBuilder l_strResult = new StringBuilder();
IWorkbook workbook = null; //新建IWorkbook对象
string l_strSaveFileName = string.Empty;
string l_strOperationSheet = string.Empty;
string l_strOperationDB = string.Empty;
Hashtable ht = new Hashtable();
//string fileName = "E:\\Excel2003.xls";
FileStream fileStream = new FileStream(p_strFileName, FileMode.Open, FileAccess.Read);
try
{
if (p_strFileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
}
else if (p_strFileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
}
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
IRow row;// = sheet.GetRow(0); //新建当前工作表行数据
for (int i = 0; i < sheet.LastRowNum; i++) //对工作表每一行
{
row = sheet.GetRow(i); //row读入第i行数据
if (row != null)
{
string cellValue = row.GetCell(0).ToString(); //获取i行j列数据
if (cellValue != "END")
{
if (cellValue == "ファイル名")
{
l_strSaveFileName = row.GetCell(1).ToString();
}
else if (cellValue == "シート名")
{
l_strOperationSheet = row.GetCell(1).ToString();
}
else if (cellValue == "対応付け")
{
ht[row.GetCell(2).ToString()] = row.GetCell(1).ToString();
}
}
}
}
sheet = workbook.GetSheet(l_strOperationSheet);
int rowindex = 0;
int colindex = 0;
//设置指定区域内容
foreach (string item in ht.Keys)
{
//1.NPOI无法处理Excel中列名（C2），所以做了转换
//并且Excel行列索引是从1开始的，而NPOI是从零开始的所以要减1.
rowindex = GetNumberic(item) - 1;
colindex = StringToNumber(GetStrings(item)) - 1;
if (sheet.LastRowNum >= rowindex)
{
row = sheet.GetRow(rowindex);
}
else
{
row = sheet.CreateRow(rowindex);
}
if (row.LastCellNum >= colindex)
{
row.GetCell(colindex).SetCellValue(ht[item].ToString());
}
else { row.CreateCell(colindex).SetCellValue(ht[item].ToString()); }
}
string l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(l_strSavePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
finally
{
fileStream.Close();
workbook.Close();
}
}
转换方法：
// Convert A, B, .... AAA, AAB to number 1,2, ...
int StringToNumber(string s)
{
int r = 0;
for (int i = 0; i < s.Length; i++)
{
r = r * 26 + s[i] - 'A' + 1;
}
return r;
}
//获取数字
int GetNumberic(string str)
{
Match ms = Regex.Match(str, @"\d+");
return int.Parse(ms.Value);
}
//获取字母
string GetStrings(string str)
{
Match ms = Regex.Match(str, @"[A-Z]+");
return ms.Value;
}
4.ClosedXML操作Excel（借鉴网络文章，如果对Excel模板处理，这个是非常好的）
获取页（Sheet）首先简单介绍两个类XLWorkbook和IXLWorksheet，分别对应着OpenXML里面的Workbook和Worksheet。XLWorkbook对应着你要访问的Excel文件，一个XLWorkbook会有一个IXLWorksheet集——IXLWorksheets，IXLWorksheet和Excel文件里面的Sheet是对应的。我们知道，一个Excel中可能会包含着很多个Sheet，XLWorkbook类中可以通过两种方式获取想要访问的页：
- public IXLWorksheet Worksheet(int position);//根据索引
- public IXLWorksheet Worksheet(string name);//根据sheet名称
第一种方式通过页的位置，也就是顺序来获取Sheet，一般第一页对应的参数为1；后一种通过页名来获取，具体使用哪一种当然要视情况而定。例如，需要遍历Excel文件中的所有内容时，第一种明显更好；如果是定位到某一页，第二种可能更恰当。单元格集合是在Sheet中的，所以要想访问数据，获取页是第一步。
读取数据范围当你用对象浏览器查看上文中IXLWorksheet类时，似乎觉得RowCount()和ColumnCount()这两个方法是来获取最大行数和列数的，但经过测试发现，这两个值往往都很大，包含了你没有存放数据的范围。后来查了一下类成员，发现了FirstRowUsed()这个函数，函数说明为“Gets the first row of the worksheet that contains a cell with a value”，紧接着就果然又发现了LastRowUsed()这个函数。简单测试了下，二者确实表示了包围所有数据的最大矩形的位置，所以包含数据的表范围是：
- rowUsedCount = LastRowUsed().RowNumber() -FirstRowUsed().RowNumber()
- ColumnUsedCount = LastColumnUsed().ColumnNumber() -FirstColumnUsed().ColumnNumber()
读取单元格数据读取单元格数据有两种方式：
- IXLCell Cell(string cellAddressInRange);//通过单元格符号来读取，比如“A1”、“B1”之类的
- IXLCell Cell(int row, int column);//通过行列的位置来读取如第三行第一列为Cell(3, 1)
- IXLCell Cell(int row, string column);//就是二者的综合，因为在Excel里面，行是用数字表示的，而列则是用ABCD来表示的
第三种就是二者的综合，因为在Excel里面，行是用数字表示的，而列则是用ABCD来表示的。第二种更合适一些，更符合“表”的概念。
要使用ClosedXML处理Excel就要先引用ClosedXML.dll和DocumentFormat.OpenXML.dll程序集，这些程序集可是使用Visual Studio的NuGet一键安装（没用过，可以网上搜索）
针对上面所说的需求代码如下：
//读取EXCEL的方法
private void SetExcel(Stream strFileDB)
{
string l_strSaveFileName = string.Empty;
string l_strOperationSheet = string.Empty;
string l_strOperationDB = string.Empty;
Hashtable ht = new Hashtable();
string l_strSavePath = string.Empty;
//实例一个Workbook 注意：XLWorkbook的参数如果我们传入的参数是文件流，发布以后不会包无访问权限，如果是文件路径的话必须把文件上传到服务器上才使用XLWorkbook
var xlBook = new XLWorkbook(strFileDB);
//实例一个worksheet
var ws = xlBook.Worksheet(1);
try
{
//取得总记录行数 (包括标题列)
int rowsint = ws.LastRowUsed().RowNumber() - ws.FirstRowUsed().RowNumber();
for (int i = 1; i <= rowsint; i++) //对工作表每一行
{
string cellValue = ws.Cell(i, 1).Value.ToString();
if (cellValue != "END")
{
if (cellValue == "ファイル名")
{
l_strSaveFileName = ws.Cell(i, 2).Value.ToString();
}
else if (cellValue == "シート名")
{
l_strOperationSheet = ws.Cell(i, 2).Value.ToString();
}
else if (cellValue == "対応付け")
{
ht[ws.Cell(i, 3).Value.ToString()] = ws.Cell(i, 2).Value.ToString();
}
}
}
//根据指定的sheet名称取得指定工作薄
ws = xlBook.Worksheet(l_strOperationSheet);
//设置指定区域内容
foreach (string item in ht.Keys)
{
ws.Cell(item).Value = ht[item];
}
//服务器临时文件
l_strSavePath = Server.MapPath("~/UpFile/" + l_strSaveFileName);
//xlBook.Saved = true;
xlBook.SaveAs(l_strSavePath);
using (FileStream fsRead = new FileStream(l_strSavePath, FileMode.Open))
{
int fsLen = (int)fsRead.Length;
byte[] heByte = new byte[fsLen];
int r = fsRead.Read(heByte, 0, heByte.Length);
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + l_strSaveFileName);
Response.ContentType = "application/excel";
Response.ContentEncoding = Encoding.Default;
Response.BinaryWrite(heByte);
Response.End();
}
}
finally
{
if (l_strSavePath != string.Empty && File.Exists(l_strSavePath))
{
File.Delete(l_strSavePath);
}
}
}
个人感觉最后一种方法处理Excel模板（有特殊样式的）是比较好的，但在国内的网上搜索的时候很不容易搜到ClosedXML操作Excel的方法。
