// 批量創(chuàng)建12個(gè)帶格式的Sheet
var workbook = new Workbook();
var template = workbook.Worksheets[0];
template.Range["A1"].Text = "月份統(tǒng)計(jì)";
template.Range["A1"].Style.Font.IsBold = true;

foreach (var month in Enumerable.Range(1,12)) {
    Worksheet sheet = workbook.Worksheets.Create($"2023-{month}");
    template.Copy(sheet); // 繼承模板樣式
    sheet.Range["B1"].Text = $"{month}月數(shù)據(jù)";
}

// 創(chuàng)建全局樣式
CellStyle warningStyle = workbook.Styles.Add("warning");
warningStyle.Color = Color.OrangeRed;
warningStyle.Font.IsBold = true;

// 應(yīng)用樣式到跨Sheet區(qū)域
workbook.Worksheets.ToList()
    .ForEach(sheet => sheet.Range["Z100"].Style = warningStyle);

Install-Package Spire.XLS -Version 13.8.0

using Spire.Xls;

// 實(shí)例化工作簿（兼容.NET 5+）
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2016;

// 創(chuàng)建年度匯總表
Worksheet summarySheet = workbook.Worksheets.Create("Annual");
summarySheet.Range["A1"].Formula = "=SUM(Jan!C10, Feb!C10)"; // 跨Sheet聚合

// 按月生成分表
foreach (var month in months) {
    Worksheet monthSheet = workbook.Worksheets.Create(month.Name);
    monthSheet.ImportData(dataTable, 1, 1); // 快速導(dǎo)入DataTable
    
    // 設(shè)置跨表引用驗(yàn)證
    CellRange validationRange = monthSheet.Range["D2:D100"];
    validationRange.DataValidation.SetFormula("=Annual!$B$2"); 
}

// 異常處理（特定于加密場(chǎng)景）
try {
    workbook.SaveToFile("report.xlsx", FileFormat.Version2016);
} catch (EncryptedDocumentException ex) {
    // 處理文檔加密錯(cuò)誤
}

// 錯(cuò)誤做法：頻繁創(chuàng)建Workbook實(shí)例
for(int i=0; i<100; i++) {
    var wb = new Workbook(); // 內(nèi)存飆升！
}

// 正確做法：復(fù)用實(shí)例+適時(shí)釋放
using (Workbook wb = new Workbook()) {
    foreach (var data in bigDataSet) {
        var sheet = wb.Create();
        //...
        sheet.Dispose(); // 及時(shí)釋放Sheet資源
    }
}

// 分頁寫入（每5000行提交一次）
int pageSize = 5000;
for (int i = 0; i < total; i += pageSize) {
    var batchData = GetBatchData(i, pageSize);
    sheet.InsertData(batchData, startRow: i+1);
    workbook.SaveChanges(); // 階段性保存
}