如何通过OleAutomation设置Excel工作表单元格区域的数字格式？

问题描述 #

无法通过Excel OleAutomation设置单元格区域的数字格式，尝试普通字符串和宽字符串两种方式均抛出异常，代码如下：

Variant xlApp, wBook, wSheet, vRange, vCell1, vCell2;

WideString xlFile  = "C:\\Temp\\ExcelTestFile.xlsx",
       xlTitle = "Relatório de Geração";
try{
     xlApp = CreateOleObject("Excel.Application");
     // Hide Excel
     xlApp.OlePropertySet("Visible", false);
     // Add new Workbook
     xlApp.OlePropertyGet("WorkBooks").OleFunction("Add", -4167);
     // Get WorkBook
     wBook = xlApp.OlePropertyGet("Workbooks").OlePropertyGet("Item", 1);

     // Get WorkSheet
     wSheet = wBook.OlePropertyGet("Worksheets").OlePropertyGet("Item", 1);
     wSheet.OlePropertySet("Name", xlTitle); 

     // Set number format
     vCell1 = wSheet.OlePropertyGet("Cells", 3, 4);
     vCell2 = wSheet.OlePropertyGet("Cells", maxRowsInXL + 1, 20);
     vRange = wSheet.OlePropertyGet("Range", vCell1, vCell2);
     //vRange.OlePropertySet("NumberFormat" , "#.###.##0,00" ); // Raise Incorrect Type
     vRange.OlePropertySet("NumberFormat" , L"#.###.##0,00" ); // Raise Can't set region NumberFormat
}
catch(Exception &E){
    ShowMessage( E.Message );
    xlApp.OlePropertySet("DisplayAlerts",false);
    xlApp.OleProcedure("Quit");
}

解决方案 #

针对你的问题，可通过以下步骤解决：

• 验证Range对象有效性
  代码中使用maxRowsInXL + 1作为结束行号，需确保该值未超出Excel的最大行限制（Excel 2007及以后版本最大行是1048576）。超出范围会导致Range对象无效，进而无法设置格式。建议先对行号做合法性校验：

  int maxValidRow = std::min(maxRowsInXL, 1048576);
  // 避免+1导致超出，直接使用maxValidRow作为结束行
  vCell2 = wSheet.OlePropertyGet("Cells", maxValidRow, 20);
  

• 简化Range获取方式
  无需通过两个Cells对象创建Range，直接使用单元格地址字符串获取，减少OLE对象交互的潜在问题：

  WideString rangeAddr = WideString().sprintf(L"D3:T%d", maxValidRow);
  vRange = wSheet.OlePropertyGet("Range", rangeAddr);
  

• 正确传递格式字符串
  在C++Builder中，直接使用WideString封装格式字符串传递给OlePropertySet，确保类型匹配：

  vRange.OlePropertySet("NumberFormat", WideString(L"#.###.##0,00"));
  

修正后的完整代码片段 #

Variant xlApp, wBook, wSheet, vRange;

WideString xlFile  = "C:\\Temp\\ExcelTestFile.xlsx",
       xlTitle = "Relatório de Geração";
try{
     xlApp = CreateOleObject("Excel.Application");
     xlApp.OlePropertySet("Visible", false);
     xlApp.OlePropertyGet("WorkBooks").OleFunction("Add", -4167);
     wBook = xlApp.OlePropertyGet("Workbooks").OlePropertyGet("Item", 1);
     wSheet = wBook.OlePropertyGet("Worksheets").OlePropertyGet("Item", 1);
     wSheet.OlePropertySet("Name", xlTitle); 

     // 校验并设置有效行号
     int maxValidRow = std::min(maxRowsInXL, 1048576);
     WideString rangeAddr = WideString().sprintf(L"D3:T%d", maxValidRow);
     vRange = wSheet.OlePropertyGet("Range", rangeAddr);
     // 设置数字格式
     vRange.OlePropertySet("NumberFormat", WideString(L"#.###.##0,00"));

     // 后续操作：保存、关闭等
     wBook.OleFunction("SaveAs", xlFile);
}
catch(Exception &E){
    ShowMessage( E.Message );
    if (!xlApp.IsEmpty()) {
        xlApp.OlePropertySet("DisplayAlerts",false);
        xlApp.OleProcedure("Quit");
    }
}

内容的提问来源于stack exchange，提问作者Jayme Jeffman