ExcelPackage操作Excel的常用方法

meteoric

发布于 2023-10-16 19:36:21

3890

发布于 2023-10-16 19:36:21

文章被收录于专栏：游戏杂谈游戏杂谈

游戏开发中最常用的工具应该当属Excel了，而Excel在数据量比较大，或者多张表进行关联的时候操作比较繁琐，偶尔会有一些工具需要，减化上述的一些操作，提升工作效率。

最开始想使用Node.js，发现二个问题，Github上第三方库，在保存 Excel 的时候会将格式给丢掉。找来找来，发现 C# 的 Epplus 比较符合需求。

第一步：准备工作

(1) 通过 NuGet 下载 Epplus

（2）Form 的构造函数中添加许可

using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using LicenseContext = OfficeOpenXml.LicenseContext;

 public Form1()
 {
    InitializeComponent();

    // EPPlus需要添加许可
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
}

第二步：根据需求对Excel进行操作

ExcelPackage 常用的API

加载或创建 Excel 文件：

ExcelPackage.Load：从现有的 Excel 文件加载数据。

ExcelPackage.Save：保存 Excel 文件。

访问工作表和单元格：

ExcelPackage.Workbook：获取工作簿对象。

ExcelWorkbook.Worksheets：获取工作表集合。

ExcelWorksheet.Cells：获取单元格集合。

ExcelRange.Value：获取或设置单元格的值。

操作工作表：

ExcelWorksheets.Add：添加新的工作表。

ExcelWorksheets.Delete：删除指定的工作表。

操作单元格：

ExcelRange.Merge：合并单元格。

ExcelRange.Style：设置单元格样式。

保存和关闭 Excel 文件：

ExcelPackage.Save：保存 Excel 文件。

ExcelPackage.Dispose：释放 ExcelPackage 对象。

using (var package = new ExcelPackage(new FileInfo("path/to/excel/file.xlsx")))
{
    // 获取工作簿对象
    ExcelWorkbook workbook = package.Workbook;

    // 获取工作表集合
    ExcelWorksheets worksheets = workbook.Worksheets;

    // 获取指定工作表
    ExcelWorksheet worksheet = worksheets["Sheet1"];   // 如果 Sheet1位于第一个位置，使用 worksheets[0] 也可以

    // 遍历每个工作表
    foreach (ExcelWorksheet worksheet in workbook.Worksheets)
    {
        // 获取工作表名称
        string sheetName = worksheet.Name;
        Console.WriteLine($"工作表名称：{sheetName}");

        // 获取工作表的行数和列数
        int rowCount = worksheet.Dimension.Rows;
        int columnCount = worksheet.Dimension.Columns;
        Console.WriteLine($"行数：{rowCount}, 列数：{columnCount}");

        // 遍历每行
        for (int row = 1; row <= rowCount; row++)
        {
            // 遍历每列
            for (int column = 1; column <= columnCount; column++)
            {
                // 获取单元格的值
                object cellValue = worksheet.Cells[row, column].Value;
                Console.WriteLine($"单元格({row}, {column}) 值：{cellValue}");
            }
        }
    }

    // 保存 Excel 文件
    package.Save();
}

第三步：其它注意事项

（1）上面的 Save / SaveAs 函数，对涉及到对 Excel 的读写，如果 Excel 是只读就会报错，最好在操作之前就检测一下。

// 获取Excel的读写属性
FileAttributes attributes = File.GetAttributes(文件的完整路径);

// 检查文件的读写属性
if ((attributes & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
{
    MessageBox.Show($"文本配置表Excel为只读模式，请修改后再操作。");
    return;
}

（2）部分Excel很大，处理起来非常耗时，有以下几个方法进行优化

用 Task 或者线程单独处理 Excel 的读、写、保存操作，处理过程中如果要操作主界面的UI（如果更新进度，打印输出日志）

private void writeLogByTask(string str)
{
    this.Invoke((Action)(() =>
    {
        TextBox_Log.AppendText(str + "\n");

        // 滚动至底部
        //TextBox_Log.ScrollToCaret();
    }));
}


Task.Run(parseDefaultTextExcelHandler).ContinueWith(task =>
{
    this.Invoke((Action)(() =>
    {
        // 操作主界面 UI
        Button_SaveText.Enabled = true;
        Button_SaveText.Text = "保存文本";
    }));
});

（3）当数据量过大时，特别是需要插入数据时，注意使用数组替代List，有插入操作创建数组时预留比较大的空间

private int getInsertIdx(int nId)
{
    int idx = -1;

    for (int i = 3; i < _textIdsArr.Length; i++)
    {
        if (nId > _textIdsArr[i - 1] && (nId < _textIdsArr[i] || _textIdsArr[i] == -1))
        {
            idx = i;
            break;
        }
    }

    if (idx != -1)
    {
        // 后移操作
        for (int i = _textIdsArr.Length - 1; i > idx; i--)
        {
            _textIdsArr[i] = _textIdsArr[i - 1];
        }

        // 插入操作
        _textIdsArr[idx] = nId;
    }

    return idx;
}

ExcelRange的读取操作，注意判断得到的值是否为空

ExcelWorksheet _worksheet = _excelPackage.Workbook.Worksheets[0];
string strContent = _worksheet.Cells[$"B{rowNum}"].Value?.ToString();

// 获取单元格的值
object cellValue = worksheet.Cells[row, column].Value;

// 用 "B1" 可以获取单元格的值，用  worksheet.Cells[1, 2] 也可以

(4) 单元格样式的操作方法：边框、行高、合并、背景色、文字颜色、文字大小

// 检查是否存在名为 "xxxx" 的工作表
bool sheetExists = excelPackage.Workbook.Worksheets.Any(sheet => sheet.Name == SHEET_NAME);
if (sheetExists)
{
    excelPackage.Workbook.Worksheets.Delete(SHEET_NAME);
    //excelPackage.Save();
}

ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add(SHEET_NAME);

// 设置XX项目xx版本周报
// 合并 A1 到 ?1 的单元格
char colStart = 'A';
char colEnd = (char)(colStart + (DEST_RELATE_DICTIONARY.Count - 1));
string strRange = string.Format("A1:{0}1", colEnd);

worksheet.Cells[strRange].Merge = true;

setCellBorder(worksheet.Cells[strRange]);

// 设置 A1 到 ?1 的单元格背景颜色为黄色
worksheet.Cells[strRange].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[strRange].Style.Fill.BackgroundColor.SetColor(Color.White);

// 设置 A1 到 ?1 的单元格高度为 30
worksheet.Row(1).Height = 30;

// 在 A1 到 ?1 的单元格中添加文本
worksheet.Cells["A1"].Value = "XX项目XX版本";
worksheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["A1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

worksheet.Cells["A1"].Style.Font.Name = FONT_NAME;
worksheet.Cells["A1"].Style.Font.Bold = true;
worksheet.Cells["A1"].Style.Font.Size = 20;           

// 构建第二列，定义自定义颜色
Color myColor = Color.FromArgb(255, 153, 0);
worksheet.Row(2).Height = 20;
for (int i = 0; i < FIELD_COLUMN_LIST.Count; i++)
{
    string secondRowColIdx = $"{(char)('A' + i)}2";

    ExcelRange cell = worksheet.Cells[secondRowColIdx];

    // 在 B 列中设置单元格值
    cell.Value = FIELD_COLUMN_LIST[i];

    // 设置单元格背景颜色
    cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
    cell.Style.Fill.BackgroundColor.SetColor(myColor);
    cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

    cell.Style.Font.Name = FONT_NAME;
    cell.Style.Font.Bold = true;
    cell.Style.Font.Size = 10;

    setCellBorder(cell);
}



private void setCellBorder(ExcelRange cell)
{
    // 设置单元格所有线框
    cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
    cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
    cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
    cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;

    cell.Style.Font.Name = FONT_NAME;
}

（5）复制多行单元格（保留其样式），并在指定的行插入复制的行

private void copyRowDataAndPaste(ExcelWorksheet worksheet, RowRangeInfo rowRangeInfo, int insertRowIdx)
{
    // 复制行数据
    int sourceRowStart = rowRangeInfo.sourceRowStartIdx;
    int sourceRowEnd = rowRangeInfo.sourceRowEndIdx;

    for (int row = sourceRowEnd; row >= sourceRowStart; row--)
    {
        // copyStylesFromRow
        worksheet.InsertRow(insertRowIdx, 1, row);

        writeLogToMainThread($"在第 {insertRowIdx} 行插入一行，复制第 {row} 行的样式");

        ExcelRange sourceRange = worksheet.Cells[row, 1, row, worksheet.Dimension.Columns];
        ExcelRange destinationRange = worksheet.Cells[insertRowIdx, 1, insertRowIdx, worksheet.Dimension.Columns];

        // update value
        destinationRange.Value = sourceRange.Value;
    }
}

// InsertRow函数的使用说明
using (var package = new ExcelPackage(new FileInfo("path/to/excel/file.xlsx")))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];

    // 在第3行之后插入2行
    worksheet.InsertRow(3, 2);

    // 从第1行复制样式到新插入的行
    worksheet.InsertRow(3, 2, 1);

    // 保存修改后的Excel文件
    package.Save();
}

（6）对指定的单元格设置注释

using (var package = new ExcelPackage(new FileInfo("path/to/excel/file.xlsx")))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];

    // 获取单元格A1
    ExcelRange cell = worksheet.Cells["A1"];

    // 设置注释
    ExcelComment comment = cell.AddComment("这是一个注释");
    comment.Author = "John";
    comment.AutoFit = true;

    // 获取注释的 RichText 对象
    ExcelRichText richText = comment.RichText.Add("这是注释的文本");

    // 设置文字大写
    richText.Font.Uppercase = true;

    // 设置其他字体属性
    richText.Font.Size = 12;
    richText.Font.Bold = true;
    richText.Font.Italic = true;
    richText.Font.Color = Color.Red;

    // 设置注释框的大小
    comment.SetSize(200, 100);

    // 保存修改后的Excel文件
    package.Save();
}

（7）删除指定的行

using (var package = new ExcelPackage(new FileInfo("path/to/excel/file.xlsx")))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];

    // 删除第5行
    int rowIndex = 5;
    worksheet.DeleteRow(rowIndex);

    // 保存修改后的 Excel 文件
    package.Save();
}

更多操作方法，请使用 ChatGPT 进行查询

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-06-08，如有侵权请联系 cloudcommunity@tencent.com 删除

excel