Install-Package Spire.XLS

using Spire.Xls;
using System.Drawing; // 用于顏色設(shè)置

public class ExcelWriter
{
    public static void CreateNewExcel()
    {
        // 1. 創(chuàng)建一個新的Workbook對象
        Workbook workbook = new Workbook();

        // 2. 獲取第一個工作表（默認會有一個Sheet1）
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "銷售數(shù)據(jù)"; // 設(shè)置工作表名稱

        // 3. 向單元格寫入文本數(shù)據(jù)
        sheet.Range["A1"].Text = "產(chǎn)品名稱";
        sheet.Range["B1"].Text = "銷售數(shù)量";
        sheet.Range["C1"].Text = "單價";
        sheet.Range["D1"].Text = "總金額";

        // 4. 向單元格寫入數(shù)字數(shù)據(jù)
        sheet.Range["A2"].Text = "筆記本電腦";
        sheet.Range["B2"].NumberValue = 10;
        sheet.Range["C2"].NumberValue = 8999.00;
        sheet.Range["D2"].Formula = "=B2*C2"; // 寫入公式

        sheet.Range["A3"].Text = "智能手機";
        sheet.Range["B3"].NumberValue = 25;
        sheet.Range["C3"].NumberValue = 4599.50;
        sheet.Range["D3"].Formula = "=B3*C3";

        // 5. 設(shè)置單元格格式
        // 設(shè)置表頭樣式
        CellStyle headerStyle = workbook.Styles.Add("headerStyle");
        headerStyle.Font.IsBold = true;
        headerStyle.KnownColor = ExcelColors.LightGreen1;
        headerStyle.HorizontalAlignment = HorizontalAlignType.Center;
        headerStyle.VerticalAlignment = VerticalAlignType.Center;
        sheet.Range["A1:D1"].Style = headerStyle;

        // 設(shè)置金額列的貨幣格式
        sheet.Range["C2:D3"].Style.NumberFormat = "￥#,##0.00";
        
        // 自動調(diào)整列寬以適應(yīng)內(nèi)容
        sheet.AutoFitColumn(1);
        sheet.AutoFitColumn(2);
        sheet.AutoFitColumn(3);
        sheet.AutoFitColumn(4);

        // 6. 保存Excel文件
        string filePath = "銷售報表_新.xlsx";
        workbook.SaveToFile(filePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel文件已保存到: {Path.GetFullPath(filePath)}");

        // 7. 釋放資源
        workbook.Dispose();
    }
}

using Spire.Xls;
using System.Data;
using System.Collections.Generic;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

public class ExcelWriter
{
    public static void WriteDataFromCollection()
    {
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "產(chǎn)品列表";

        // 準備數(shù)據(jù)
        List<Product> products = new List<Product>
        {
            new Product { Id = 1, Name = "鍵盤", Price = 199.00m, Stock = 50 },
            new Product { Id = 2, Name = "鼠標", Price = 99.50m, Stock = 120 },
            new Product { Id = 3, Name = "顯示器", Price = 1299.00m, Stock = 30 }
        };

        // 將List<T>數(shù)據(jù)寫入Excel，并包含表頭
        // 第一個參數(shù)是數(shù)據(jù)源，第二個參數(shù)表示是否包含列頭，
        // 第三個和第四個參數(shù)表示從Excel的第幾行第幾列開始寫入
        sheet.InsertCollection(products, true, 1, 1);

        // 如果是DataTable，用法類似
        DataTable dataTable = new DataTable("用戶信息");
        dataTable.Columns.Add("UserID", typeof(int));
        dataTable.Columns.Add("UserName", typeof(string));
        dataTable.Columns.Add("Email", typeof(string));
        dataTable.Rows.Add(101, "張三", "zhangsan@example.com");
        dataTable.Rows.Add(102, "李四", "lisi@example.com");

        // 將DataTable數(shù)據(jù)寫入Excel，從A5單元格開始
        sheet.InsertDataTable(dataTable, true, 5, 1);

        // 自動調(diào)整所有列寬
        sheet.AutoFitColumns();

        string filePath = "產(chǎn)品與用戶數(shù)據(jù).xlsx";
        workbook.SaveToFile(filePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel文件已保存到: {Path.GetFullPath(filePath)}");

        workbook.Dispose();
    }
}

using Spire.Xls;
using System.IO;

public class ExcelWriter
{
    public static void UpdateExistingExcel()
    {
        string existingFilePath = "銷售報表_新.xlsx"; // 假設(shè)這個文件已經(jīng)存在

        // 檢查文件是否存在
        if (!File.Exists(existingFilePath))
        {
            Console.WriteLine($"文件不存在: {Path.GetFullPath(existingFilePath)}，請先運行 CreateNewExcel 方法。");
            return;
        }

        // 1. 加載現(xiàn)有的Workbook對象
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(existingFilePath);

        // 2. 獲取指定的工作表
        Worksheet sheet = workbook.Worksheets["銷售數(shù)據(jù)"]; // 根據(jù)名稱獲取

        // 3. 追加新數(shù)據(jù)行
        int lastRow = sheet.LastRow + 1; // 獲取當前數(shù)據(jù)的最后一行，然后加1作為新行的起始行
        sheet.Range[$"A{lastRow}"].Text = "無線耳機";
        sheet.Range[$"B{lastRow}"].NumberValue = 50;
        sheet.Range[$"C{lastRow}"].NumberValue = 399.00;
        sheet.Range[$"D{lastRow}"].Formula = $"=B{lastRow}*C{lastRow}";
        sheet.Range[$"C{lastRow}:D{lastRow}"].Style.NumberFormat = "￥#,##0.00"; // 重新應(yīng)用格式

        // 4. 修改現(xiàn)有單元格數(shù)據(jù)
        sheet.Range["B2"].NumberValue = 12; // 將筆記本電腦的銷售數(shù)量從10修改為12
        sheet.CalculateAllValue(); // 重新計算所有公式

        // 5. 添加超鏈接和注釋
        sheet.Range["A" + (lastRow + 2)].Text = "訪問官網(wǎng)";
        HyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["A" + (lastRow + 2)]);
        hyperlink.Type = HyperLinkType.Url;
        hyperlink.Address = "https://www.e-iceblue.com/";

        sheet.Range["A" + (lastRow + 3)].Text = "重要提示";
        sheet.Range["A" + (lastRow + 3)].Comment.Text = "這是關(guān)于新產(chǎn)品的銷售提示！";
        
        // 6. 保存修改
        workbook.SaveToFile(existingFilePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel文件已更新: {Path.GetFullPath(existingFilePath)}");

        workbook.Dispose();
    }
}

// 保存為XLSX格式（推薦）
workbook.SaveToFile("MyReport.xlsx", ExcelVersion.Version2016);

// 保存為XLS格式（兼容舊版Excel）
// workbook.SaveToFile("MyReport.xls", ExcelVersion.Version97to2003);

try
{
    // Excel操作代碼
    workbook.SaveToFile(filePath, ExcelVersion.Version2016);
}
catch (Exception ex)
{
    Console.WriteLine($"保存Excel文件時發(fā)生錯誤: {ex.Message}");
}

using (Workbook workbook = new Workbook())
{
    // 所有Excel操作
    // workbook.SaveToFile(...)
} // 在這里workbook會自動Dispose