// 獲取當前工作表名稱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}";}
}
)
