// 获取当前工作表名称string sheetName = (string)XlCall.Excel(XlCall.xlfGetDocument, 7);// 构造动态名称（例如：Sheet1!MyNamedCell）string fullName = $"'{sheetName}'!MyNamedCell";// 获取引用并设置值var namedRange = (ExcelReference)XlCall.Excel(XlCall.xlfName, fullName);namedRange.SetValue($"来自 {sheetName} 的值");设置表格A5
public static void SetRangeA5F10(){try{cl_日志.write_log("招标专家", "执行", "SetRangeA5F10");// 创建一个6行6列的数组（A到F共6列，5到10共6行）object[,] values = new object[6, 6];// 填充示例数据for (int row = 0; row < 6; row++){for (int col = 0; col < 6; col++){// values[row, col] = $"Row{row + 5}Col{col + 1}";string 字符串= $"Row{row + 5}Col{col + 1}";var cell = new ExcelReference(row, col); // A1单元格// 方法1：使用SetValue设置公式字符串（需以等号开头）cell.SetValue(字符串);}}// 设置A1单元格的公式// var cell = new ExcelReference(0, 0); // A1单元格// 方法1：使用SetValue设置公式字符串（需以等号开头）// cell.SetValue("=SUM(B1:B10)");// 方法2：使用xlcFormula命令（更可靠）// XlCall.Excel(XlCall.xlcFormula, cell, "=AVERAGE(C1:C10)");// 获取Sheet1的索引int sheetId = 1; // Sheet1通常是第一个工作表// 设置区域的值// object result = XlCall.Excel(// XlCall.xlcSetValue,// XlCall.Excel(XlCall.xlfR1C1ToA1, 5, 1, sheetId), // A5// values// );// XlCall.Excel()// if (result is ExcelError && (ExcelError)result != ExcelError.ExcelErrorNull)// {// throw new System.Exception($"Excel操作失败: {result}");// cl_日志.write_log("招标专家", "StartExpertSelection", ex.Message);// }}catch (System.Exception ex){// System.Windows.Forms.MessageBox.Show($"发生错误: {ex.Message}", "错误",// System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);cl_日志.write_log("招标专家", "StartExpertSelection", ex.Message);}}
查找非空单元格
using ExcelDna.Integration;public static class RangeOperations
{[ExcelFunction(Description = "获取指定工作表的非空区域（通过公式）")]public static object GetNonEmptyRange(string sheetName = "Sheet1"){try{// 构建公式：获取工作表的已用区域地址string formula = $"=IFERROR(ADDRESS(1,1,4,,\"{sheetName}\")&\":\"&ADDRESS(MAX(IF({sheetName}!1:1048576<>"",ROW({sheetName}!1:1048576))),MAX(IF({sheetName}!1:1048576<>"",COLUMN({sheetName}!1:1048576)))),\"\")";// 计算公式string rangeAddress = (string)XlCall.Excel(XlCall.xlfEvaluate, formula);if (string.IsNullOrEmpty(rangeAddress)){return "错误: 未找到非空区域";}// 将地址转换为ExcelReferencereturn new ExcelReference(rangeAddress);}catch (Exception ex){return $"错误: {ex.Message}";}}
}查找非空
[ExcelFunction(Description = "手动查找指定工作表的非空区域")]
public static object FindNonEmptyRange(string sheetName = "Sheet1")
{try{// 获取工作表实例（通过COM对象，需引用Microsoft.Office.Interop.Excel）var excelApp = (Excel.Application)ExcelDnaUtil.Application;var worksheet = excelApp.Worksheets[sheetName];if (worksheet == null){return $"错误: 找不到工作表 '{sheetName}'";}// 查找最后一行和最后一列Excel.Range lastRow = worksheet.Cells.Find("*", worksheet.Cells[1], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious);Excel.Range lastCol = worksheet.Cells.Find("*", worksheet.Cells[1], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious);if (lastRow == null || lastCol == null){return "错误: 未找到非空单元格";}// 构建区域地址string rangeAddress = $"{worksheet.Name}!{worksheet.Cells[1, 1].Address}:{lastRow.Address}{lastCol.Column}";// 释放COM对象（避免内存泄漏）System.Runtime.InteropServices.Marshal.ReleaseComObject(lastRow);System.Runtime.InteropServices.Marshal.ReleaseComObject(lastCol);System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);return new ExcelReference(rangeAddress);}catch (Exception ex){return $"错误: {ex.Message}";}
}
)
