Visual Studio Tools for Office เขียน Excel Macro ด้วย C#

โดยปกติสร้าง Excel Macro หรือการเขียนคำสั่งเพื่อ automate งานบางอย่างใน Microsoft Excel เรามักทราบว่าเราจะเขียนด้วยคำสั่ง VB หรือ VBA (Visual Basic for Applications)

แต่สำหรับหลายท่านที่คุ้นเคยกับ C# และ .NET เหมือนกับ admin คงชื่นชมที่จะเขียน code ด้วยคำสั่ง C# มากกว่า

ใน Excel ได้เปิดโอกาสให้เราสามารถเขียนคำสั่ง C# เข้าไปจัดการกับข้อมูลใน Excel file ได้ โดยบทความวันนี้เราจะมาลองเขียนำคำสั่ง C# เพื่ออ่านและแก้ไขข้อมูลใน Excel file กัน

สิ่งที่เตรียมพร้อมสำหรับการพัฒนา Excel Programming ด้วย C#

• Visual Studio ที่ได้ติดตั้ง Visual Studio Tool for Office ด้วย Visual Studio Installer เพื่อให้เราสามารถสร้าง Microsoft Office Project ได้จาก Visual Studio

• ติดตั้ง Microsoft Excel (ในตัวอย่างนี้ admin ได้ติดตั้ง Microsoft Office 2016 version)
• ข้อมูลการรองรับสำหรับ office version อื่นๆ

เมื่อได้ติดตั้งทุกอย่างเรียบร้อยแล้วก็เริ่มลงมือเขียน program กันได้เลยครับ

• สร้าง project ใหม่ เลือก Excel VSTO Workbook เราจะเขียน code ลงไปใน Excel workbook file เลย โดยเราสามารถสร้าง file ใหม่หรือเลือก Excel file ที่มีอยู่แล้ว

• ตัวอย่าง Excel Designer ใน Visual Studio หลังจากสร้าง project เสร็จแล้ว

• การเขียนคำสั่งจะอยู่ในรูปแบบ code behind คล้ายๆ กับ Windows Form หรือ Web Form application โดยเรียกใช้ Excel object model
• เมื่อเปิด project ขึ้นมา ผมได้ทำการ update ชื่อ method และให้ code นี้ถูกเรียกใช้เมื่อเราเปิด sheet ขึ้นมา

    private void MainSheet_Startup(object sender, EventArgs e)
    {
        const int year = 2020;
    
        var orderedDayOfWeeks = new[]
        {
            DayOfWeek.Monday,
            DayOfWeek.Tuesday,
            DayOfWeek.Wednesday,
            DayOfWeek.Thursday,
            DayOfWeek.Friday,
            DayOfWeek.Saturday,
            DayOfWeek.Sunday
        };
    
        var firstDateOfYear = new DateTime(year, 1, 1);
        var firstMondayOfFirstWeek = firstDateOfYear.AddDays(
            -Array.IndexOf(orderedDayOfWeeks, firstDateOfYear.DayOfWeek)
        );
    
        var weekRanges =
            from date in Enumerable.Range(0, int.MaxValue)
            let mondayOfWeek = firstMondayOfFirstWeek.AddDays(date * 7) // Create a next Monday
            select (From: mondayOfWeek, To: mondayOfWeek.AddDays(4));
    
        var weekRangesInYear = weekRanges.TakeWhile(x => x.From.Year <= year).ToArray();
    
        var firstRowNumber = 5;
        for (int index = 0; index < weekRangesInYear.Length; index++)
        {
            var range = get_Range($"A{ firstRowNumber + index}");
            var formattedDate = 
                $"# {index + 1:0#}) {weekRangesInYear[index].From:MMM dd} - {weekRangesInYear[index].To:MMM dd}";
            range.Value2 = formattedDate;
        }
    }

• Run code แบบไม่ต้อง debug ด้วยการกดปุ่ม CTRL + F5 workbook Microsoft Excel จะเปิดขึ้นมาและเรียกใช้งาน work book ที่เราได้เขียนคำสั่งไว้

• ตัวอย่างผลลัพธ์การทำงาน

เปรียบเทียบกับการใช้งานกับการเขียนด้วย VB Script (VBA)

(นี้เป็นความเห็นส่วนตัวของผมเองนะครับ)

• โดยส่วนตัวการเขียนคำสั่ง ชอบวิธีการของ Visual Studio for Office มากกว่า เพราะสามารถเขียนด้วยภาษา C# ซึ่งเป็นภาษาโปรดของผม
• ยังได้ความสามารถต่างของ Visual Studio มาช่วยในการเขียน program ได้ง่ายขึ้น เช่น Intellisence หรือ autocomplete
• ได้ใช้ feature language ของ C# และ .NET features เช่น LINQ to object
• Error message อ่านเข้าใจง่าย
• แต่สิ่งที่การเขียนด้วย VBA ได้เปรียบกว่าคือ การพัฒนาหรือแก้ไขไม่จำเป็นต้องมี Visual Studio เพียงแค่เปิด file ด้วย Excel ก็สามารถแก้ไขงานได้ทันที copy ไปใช้ต่อได้ทันที่
• ไม่ต้องทำการเปิด permission ให้กับ DLL file ที่ได้จากวิธีการของ VSTO
• เมื่อเปิด Excel ใช้งานตัว Script ค่อนข้างจะโหลดเร็วกว่าในครั้งแรก

สรุป

• หากต้องเลือกวิธีการใดวิธีการหนึ่งก็ trade off แล้วกันนะครับ
• สำหรับคนที่เป็น VB อยู่แล้ว ก็อาจจะเลือก VBA
• แต่สำหรับที่เป็น C# อยู่ การเขียน Excel programming ด้วย VSTO ก็เป็นทางเลือกที่น่าสนใจเลยทีเดียว

TIP

• การปรับแก้ให้แสดง Excel designer ใน Visual Studio ให้แสดงผลได้ถูกต้อง

Visual Studio

Microsoft Excel

• ถ้าเจอปัญหาตัว Excel designer ได้ปิดไปพอ double click ที่ Workbook หรือ Worksheet แล้วมี error message "The process cannot access the file because it is being used by another process." ให้ใช้ Task manager kill Excel process ที่ค้างอยู่แล้วลองใหม่

ลองเอาไปใช้กันดูนะครับ

source code