说明:
1、EPPlus对于excel的封装,已经很好了;这里仅是将文件的操作加入进来。
2、EPPlus的常见操作可参考 系列文章 导出Excel之Epplus使用教程1(基本介绍)
3、Epplus的所有行列数都是以1开始的
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml; //引入EPPLUS函数的命名空间
using System.IO;//对excel文件的操作,涉及到读写,引入其命名空间
namespace OperateExcel
{
/// <summary>
/// 基于EPPlus的excel操作类,仅支持xlsx格式的excel文件
/// </summary>
class ExcelHelper
{
/// <summary>
/// 打开excel文件
/// </summary>
/// <param name="openPath">excel文件路径</param>
/// <returns>excel对象</returns>
public ExcelPackage OpenExcel(string openPath)
{
// using (ExcelPackage package = new ExcelPackage(new FileStream(path, FileMode.Open)))
FileStream excelFile = new FileStream(openPath, FileMode.Open);
ExcelPackage package = new ExcelPackage(excelFile);
return package;
}
/// <summary>
/// 另存excel文件
/// </summary>
/// <param name="package">excel文件对象</param>
/// <param name="savePath">保存路径</param>
public void saveExcel(ExcelPackage package,string savePath)
{
FileStream excelFile = new FileStream(savePath, FileMode.OpenOrCreate);
package.SaveAs(excelFile);
excelFile.Dispose();
package.Dispose(); //释放资源,一般也可采用using语句
}
}
}
操作业务
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using System.IO;
namespace OperateExcel
{
class Program
{
static void Main(string[] args)
{
ExcelHelper helper = new ExcelHelper();
string path = @"E:\Projects\OperateExcel\excelFiles\lowver.xlsx";
ExcelPackage package = helper.OpenExcel(path);
for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)
{
ExcelWorksheet sheet = package.Workbook.Worksheets[i];
for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
{
for (int m = sheet.Dimension.Start.Row, n = sheet.Dimension.End.Row; m <= n; m++)
{
//赋值、取值等操作
package.Workbook.Worksheets[1].SetValue(1, 1, "shannan");
sheet.Cells[m, j].Value = "shannan";
object CellValue = package.Workbook.Worksheets[1].GetValue(1, 1);
}
}
}
string savePath = @"E:\文档\visual studio 2015\Projects\OperateExcel\excelFiles\lowverNew.xlsx";
helper.saveExcel(package, savePath);
}
}
}