SSIS到Excel - Excel公式不自动运行？

Question

我有一个SSIS包，目前它有以下步骤：

1 - 文件系统任务 - 复制一个Excel文件(.xlsx)作为SSIS包输出的模板。 Excel文件有3个选项卡 - 其中一个没有数据，但是引用第二和第三个选项卡上的值的公式。这是最终用户查看的页面（我们称之为end_user）。

2 - 数据流任务 - OLEDB源 ->数据转换 -> Excel目标（选项卡2，让我们称其为raw_data）。

3 - 电子邮件任务 - 尚未实现，但将在产品的最后阶段出现，以使其完全自动化。

当包运行完成时，我打开Excel文件，数据已写入raw_data，但是end_user上没有任何显示。这是我已经尝试过的解决方法（在Excel中）：

• File -> Options -> Formulas -> Automatic (where it already was). And I tried every other choice as well (Automatic except for data tables, Manual, Manual with Recalculate workbook before saving, and back to Automatic).
• Ensured the appropriate data types were selected for each column in raw_data, and end_user [i.e. Number, Currency, Short Date, etc.]
• Tried setting all data field types to General
• Using a different Excel file as a template.
• Copying the formulas into Notepad, putting a random number in place of the formula in Excel, save and close the file, reopen, and put the formula back in.
• Deleting the SSIS Package (.dtsx) and make a new one.

• Creating an Excel macro to force formula updates at the time the file is opened.

  Private Sub Workbook_Open()
  
  Application.Calculation = xlCalculationAutomatic
  
  End Sub
  

• Running a Repair on Office.

• Press Ctrl + Alt + F9 in Excel. (This makes the formulas recalculate, and does make the values show up the way they are supposed to.)

虽然使用 Ctrl + Alt + F9 选项可以使文件正常显示，但这并不是一个可接受的解决方案，因为我们都知道最终用户希望一切都能完美运作。是否有人有建议、经验或其他见解？任何有用的信息都将不胜感激！

- Derek

---

你可以运行一个脚本任务来完成相同的刷新。我有一种预感，SSIS 不会有任何内在特性。而且，Excel 的驱动程序非常糟糕。我很惊讶宏选项对你没有起作用。也许如果你包含那段代码，我们可以进行故障排除？ - Jacob H

@JacobH - 感谢您的回复和好主意。我已将其添加到上面的描述中。 - Derek

先生，那是一个否定的结果。没有起作用。 - Derek

你尝试过使用xltx或者xltm文件格式来代替xlsx格式吗？ - milivojeviCH

我没有尝试过那些，但显然它喜欢 .xls 而不是 .xlsx。可能是因为 Excel 的新版本有什么问题。但还是谢谢你的建议 - 它让我找到了解决问题的正确方向！ - Derek

Answer 1

我曾经有过类似的情况。所以尽管这个问题已经超过3年了，我希望我的解决方案能帮助到其他人。

我创建了一个脚本任务，其中包含以下C#代码（您只需要编辑TheNameOfYourExcelConnection.xls即可）。

该脚本将连接到电子表格，迭代每个工作表中的每个单元格，并将每个“=”符号替换为其本身。实际上没有改变公式，但是强制进行重新计算。

#region Namespaces
using System;
using Microsoft.SqlServer.Dts.Runtime;


//add reference C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
using Excel = Microsoft.Office.Interop.Excel;
#endregion

namespace ST_10cafd32acda4eb1b677ebbdd2eb1286
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        public void Main()
        {
            Excel.Application excelApp = new Excel.Application();
            ConnectionManager cm = Dts.Connections["TheNameOfYourExcelConnection.xls"];
            string filepath = cm.Properties["ExcelFilePath"].GetValue(cm).ToString();

            excelApp.DisplayAlerts = false;
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(filepath, 1, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, null, false);
            try
            {
                Excel.Sheets excelWorkSheet = excelWorkbook.Sheets;
                foreach (Excel.Worksheet work in excelWorkSheet)
                {
                    work.Cells.Replace("=", "=");

                }
                excelWorkbook.Save();
                excelWorkbook.Close();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                excelApp.Quit();
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

- Ethan1701

---

catch (Exception ex)不起作用，有什么建议吗？ - Joeysonic

catch (Exception ex) 不起作用，有什么建议吗？ - undefined