C#窗体程序中经常会使用到对数据的输入输出,而Excel文件是比较通用的文本格式,针对功能需求:
- 快速方便读写Excel文件
- 对于未安装Office的机器也能具备生成和读写Excel的能力
通常有NPOI、ClosedXML、EPPlus等类库可供使用,本文对EPPlus的简单使用做一总结。
EPPlus的GitHub网址:https://github.com/EPPlusSoftware/EPPlus
EPPlus版本:5.0.4
读取Excel文件至二维数组:
FileInfo file = new FileInfo(@"数据.xlsx");
ExcelPackage excelPackage = new ExcelPackage(file);
//许可证,必须添加许可证,否则会报错
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
//选择读取的数据表
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];
//获取表格数据的最大行数
int rowNum = excelWorksheet.Dimension.End.Row;
//获取表格数据的最大列数
int columnNum = excelWorksheet.Dimension.End.Column;
//定义存储数据的二维数组
string[,] dataTable = new string[rowNum, columnNum];
//按行读取,存入二维数组dataTable
for (int i = excelWorksheet.Dimension.Start.Row; i <= excelWorksheet.Dimension.End.Row; i++)
{
for (int j = excelWorksheet.Dimension.Start.Column; j <= excelWorksheet.Dimension.End.Column; j++)
{
dataTable[i - 1, j - 1] = excelWorksheet.Cells[i, j].Value.ToString();
}
}
//将数组遍历输出
for (int i = 0; i < dataTable.GetLength(0); i++)
{
for (int j = 0; j < dataTable.GetLength(1); j++)
{
Console.Write(dataTable[i, j] + "\t");
}
Console.Write("\n");
}
Console.ReadKey();
将数组写入Excel表
//待写入的数组,及Excel表存储位置
string[] str = { "1", "5", "10", "15", "20" };
string outputExcelPath = System.Environment.CurrentDirectory + "\\数据.xlsx";
FileInfo file = new FileInfo(outputExcelPath);
//许可证
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
//如果文件存在,则读取并写入新的列,若不存在则创建
if (file.Exists)
{
ExcelPackage excelPackage = new ExcelPackage(file);
//选择读取的数据表
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];
//获取表格中已有数据的列数
int columnNum = excelWorksheet.Dimension.End.Column;
//写入数据
excelWorksheet.Cells[1, columnNum + 1].Value = "新数据";
for (int i = 0; i < str.Length; i++)
{
excelWorksheet.Cells[i + 2, columnNum + 1].Value = str[i];
}
excelPackage.Save();
}
else
{
file = new FileInfo(outputExcelPath);
ExcelPackage excelPackage = new ExcelPackage(file);
//创建新的数据表
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
//写入数据
excelWorksheet.Cells[1, 1].Value = "数据";
for (int i = 0; i < str.Length; i++)
{
excelWorksheet.Cells[i + 2, 1].Value = str[i];
}
excelPackage.Save();
}
运行结果:
End