# ⚙️ B28｜Build Pro Excel Tools with C# + Excel-DNA — Companion Assets

> **Series**: AI for Office Work: From Beginner to Pro
> **Article**: B28 (Track 7 Office Dev / Level 4 Hardcore)
> **Title (EN)**: Build Pro Excel Tools with C# + Excel-DNA
> **Title (ZH)**: 用 C# + Excel-DNA 打造专业 Excel 工具

---

## 1. Who is this for? What problem does it solve?

For anyone who has **hit three walls using Excel**:

- **VBA isn't professional** — slow on big data, hard to debug, blocked by macro policies when shared;
- **Built-in functions aren't enough** — you need a function that follows YOUR rules; nested IFs drive you crazy;
- **Real-time data won't come in** — you need a dashboard that auto-refreshes (RTD).

The main tutorial walks through the **full development path**. This folder ships a **real, compilable minimal project** — `MyExcelTools.csproj` + `MyFunctions.cs` + `MyRtdServer.cs` + `MyTools.dna` + `Properties\AssemblyInfo.cs`. Copy into Visual Studio, hit `F6`, and you get a `.xll` to load into Excel.

| File | Purpose |
|------|---------|
| `MyExcelTools_en.csproj` | .NET Framework 4.7.2 project file (with ExcelDna.AddIn references) |
| `MyFunctions_en.cs` | Custom functions: `MyAdd` / `MyConcat` / `GetLivePrice` |
| `MyRtdServer_en.cs` | RTD real-time-data server (pushes a new value every 1 second) |
| `MyTools_en.dna` | Excel-DNA config (ExternalLibrary Pack="true") |
| `Properties_AssemblyInfo_en.txt` | AssemblyInfo template |
| (matching `_zh` series) | Chinese-comment versions |

After this folder, you have the bones of a "double-click `.xll` → load into Excel → use as a formula + RTD" hardcore tool.

---

## 2. How to use (5 steps to run the sample)

### Step 1: Environment

- **Visual Studio 2019+** Community (free); check "**.NET desktop development**" workload during install;
- **.NET Framework 4.7.2 Developer Pack** (or 4.8). Not sure? In the New Project dialog, if `.NET Framework 4.7.2` appears in the target-framework dropdown, you have it;
- **Excel 2016+**;
- Access to **NuGet**.

> ⚠️ **Don't pick .NET (Core) / 5+ / 6+ / 7+ / 8+** — Excel-DNA supports .NET Framework 4.x best; fewest pitfalls.

### Step 2: Create the project + install NuGet

1. VS → Create new project → **"Class Library (.NET Framework)"** (the one with .NET Framework in the name);
2. Name the project `MyExcelAddIn`, target framework `.NET Framework 4.7.2` → Create;
3. Right-click project → **Manage NuGet Packages** → Browse → search **`ExcelDna.AddIn`** → Install;
4. After install, NuGet auto: ① generates a `.dna` config; ② wires up "produce `.xll` on build" MSBuild target; ③ scaffolds `MyFunctions.cs` / `MyRtdServer.cs` / `AssemblyInfo.cs`.

> ⚠️ Make sure you install `ExcelDna.AddIn` (the all-in-one), not the bare `ExcelDna`.

### Step 3: Copy the source files from this folder

Copy `MyFunctions_en.cs` and `MyRtdServer_en.cs` into your project directory (overwrite VS placeholders). Rename `Properties_AssemblyInfo_en.txt` to `Properties\AssemblyInfo.cs`. Rename `MyTools_en.dna` to `MyExcelAddIn.dna` (matches the `RootNamespace`).

### Step 4: Build + load

1. Press **`F6`** (Build Solution);
2. Open `bin\Debug\` → find **`MyExcelAddIn.xll`** (name follows your AssemblyName);
3. In Excel: **File → Options → Add-Ins** → at the bottom, "Manage" pick **"Excel Add-ins"** → **Go...** → **Browse** → select the `.xll` → OK.
   - Legacy keyboard reference: `Alt + T + I` opens Add-Ins manager (NOT `Alt+F11` — that's the VBA editor).
   - Even easier: drag the `.xll` into an Excel window.
4. In any cell type `=MyAdd(10, 20)`, press Enter → **30**;
5. Type `=GetLivePrice()` → refreshes every ~1 second (RTD push works).

### Step 5: Let AI extend it

Once running, send this prompt to AI to add business functions quickly:

```
I'm using C# + Excel-DNA to build an Excel add-in. Project name MyExcelAddIn.
MyFunctions.cs already has MyAdd / MyConcat / GetLivePrice.

Please add a new function =Tax(salary, region) that returns income tax:
- salary is taxable income (number)
- region is text ("Beijing" / "Shanghai" / "Shenzhen"), determines tax rate
- Give me the full method signature with [ExcelFunction] + [ExcelArgument] + body (bilingual comments)
```

---

## 3. File inventory

| File | Type | Purpose | Lines |
|------|------|---------|-------|
| `B28_ExcelDNA_EnglishREADME.md` | Markdown | This file | — |
| `B28_ExcelDNA_中文README.md` | Markdown | Chinese README | — |
| `MyExcelTools_en.csproj` | MSBuild XML | Project file (English comments) | ~80 |
| `MyExcelTools_zh.csproj` | MSBuild XML | Project file (Chinese comments) | ~80 |
| `MyFunctions_en.cs` | C# | Custom function impl (English) | ~85 |
| `MyFunctions_zh.cs` | C# | Custom function impl (Chinese) | ~85 |
| `MyRtdServer_en.cs` | C# | RTD server (English) | ~95 |
| `MyRtdServer_zh.cs` | C# | RTD server (Chinese) | ~95 |
| `MyTools_en.dna` | XML | Excel-DNA config (English comments) | ~40 |
| `MyTools_zh.dna` | XML | Excel-DNA config (Chinese comments) | ~40 |
| `Properties_AssemblyInfo_en.txt` | C# | Assembly info template (English) | ~30 |
| `Properties_AssemblyInfo_zh.txt` | C# | Assembly info template (Chinese) | ~30 |

---

## 4. Real code (key snippets explained line-by-line)

### ① [ExcelFunction]-attributed custom function

```csharp
[ExcelFunction(
    Name        = "MyAdd",                         // Excel formula name
    Description = "Add two numbers and return the result.",
    Category    = "My Tools",                      // Function-wizard category
    IsVolatile  = false)]                          // Non-volatile
public static double MyAdd(
    [ExcelArgument(Name = "Number1", Description = "First addend")] double x,
    [ExcelArgument(Name = "Number2", Description = "Second addend")] double y)
{
    return x + y;
}
```

**Four iron rules**:
- Class MUST be **`public static class`**;
- Method MUST be **`public static`** (COM reflection invokes static methods);
- `[ExcelFunction]` pasted on the method;
- Parameters tagged with `[ExcelArgument]` for friendly name + tooltip.

### ② Wrap an RTD function

```csharp
[ExcelFunction(Name = "GetLivePrice", Description = "Get a live price.")]
public static object GetLivePrice()
{
    // XlCall.RTD(serverProgID, callbackParams, topic1, topic2, ...)
    return XlCall.RTD("MyExcelAddIn.MyRtdServer", null, "PRICE");
}
```

**Key point**: First argument of `XlCall.RTD` is the RTD server's "full name" — by convention `Namespace.ClassName` (`MyExcelAddIn.MyRtdServer`). Different Excel-DNA versions may register RTD slightly differently; check your version's docs.

### ③ RTD server

```csharp
public class MyRtdServer : ExcelRtdServer
{
    private Timer _timer;
    private double _lastValue = 0;

    // Required: server start (parameter-less)
    protected override bool ServerStart()
    {
        _timer = new Timer(1000);
        _timer.Elapsed += (s, e) =>
        {
            _lastValue = new System.Random().NextDouble() * 100;
            foreach (Topic topic in GetActiveTopics())   // protected instance method, NOT override
            {
                topic.UpdateValue(_lastValue);
            }
        };
        _timer.Start();
        return true;
    }

    protected override void ServerTerminate() { _timer?.Stop(); _timer?.Dispose(); }

    protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
    {
        newValues = true;
        return _lastValue;
    }

    protected override void DisconnectData(Topic topic) { /* no-op */ }
}
```

**Four mandatory methods**:
- `ServerStart()`: **parameter-less** virtual method (signature may vary across versions — check your docs);
- `ServerTerminate()`: cleanup (Timer / DB connections);
- `ConnectData(Topic, IList<string>, ref bool)`: return the initial value;
- `DisconnectData(Topic)`: cleanup on unsubscribe.

**One common mistake**: `GetActiveTopics()` is a **protected instance method** (NOT an override) — use it to walk all subscribers when pushing updates.

### ④ .dna single-file deploy config

```xml
<DnaLibrary Name="MyExcelAddIn AddIn" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyExcelAddIn.dll" Pack="true" />
</DnaLibrary>
```

**One key line**: `Pack="true"` tells Excel-DNA to embed the `.dll` into the `.xll` — distribution is just one `.xll` file.

---

## 5. FAQ / Pitfalls

1. **Wrong NuGet package**
   Install `ExcelDna.AddIn` (all-in-one), NOT bare `ExcelDna`. Wrong package → no `.xll` or runtime can't find `ExcelDna.Integration`.

2. **Wrong .NET runtime**
   This tutorial uses **.NET Framework 4.7.2**. .NET (Core) / 5+ change `.csproj` syntax, `.dna`'s `RuntimeVersion`, and RTD registration; when in doubt, stick to .NET Framework.

3. **Add-in loaded but functions don't appear**
   Three common causes: ① build failed (check VS Output for errors); ② `.dna` `ExternalLibrary Path` doesn't match the built dll name (default is correct, don't hand-edit); ③ Excel 64-bit vs project target platform mismatch — set project Properties → Build → Platform target to `Any CPU` or match Excel's bitness.

4. **`=GetLivePrice()` returns `#VALUE!` / `#NAME?`**
   RTD server full-name typo (must be `MyExcelAddIn.MyRtdServer`), or RTD registration failed. Verify `.dna` `ExternalLibrary` points to the right dll; check whether corporate IT disabled RTD add-ins.

5. **Corporate machine blocks the add-in**
   `.xll` is essentially an executable add-in; corporate security / group policy may block it. NOT a code issue — put `.xll` in a local trusted location or ask IT to whitelist; verify on your own machine before distributing.

---

## 6. Next steps

After this folder, you have a "double-click `.xll` to use" professional Excel tool skeleton. Natural next stops:

- **Real data feed**: replace `_lastValue = new Random().NextDouble() * 100` with HTTP API / DB / message-queue calls;
- **Custom Ribbon buttons**: use Excel-DNA's `ExcelDnaUtils` + `IRibbonExtensibility` to add custom buttons to Excel's ribbon, so the tool isn't just a formula — it's a clickable feature;
- **Single-file distribution**: `ExcelDnaPack` packages all dependencies into one `.xll` — colleagues just double-click;
- **Async support**: add `async` (Excel-DNA 0.34+) so slow APIs don't freeze Excel;
- **Stay light**: if you only need to batch-process Excel, go back to **B25 (Python + openpyxl)**.

---

> 📌 **Quick checklist** (verify before running):
> - [ ] VS has ".NET desktop development" workload;
> - [ ] Project is "Class Library (.NET Framework)", target framework 4.7.2;
> - [ ] NuGet installed `ExcelDna.AddIn` (NOT `ExcelDna`);
> - [ ] Project has `MyFunctions.cs` / `MyRtdServer.cs` / `Properties\AssemblyInfo.cs`;
> - [ ] Root directory has `<RootNamespace>.dna` with matching `ExternalLibrary Path`;
> - [ ] `F6` build succeeded; `.xll` appears in `bin\Debug\`;
> - [ ] Excel loaded it via "File → Options → Add-Ins → Go... → Browse";
> - [ ] `=MyAdd(10, 20)` returns 30;
> - [ ] `=GetLivePrice()` refreshes every second.