首页 文章

如何在没有其他软件的情况下将SSIS导出到Microsoft Excel?

提问于
浏览
5

这个问题很长,因为我在很长一段时间内一直在更新问题,试图让SSIS正确导出Excel数据 . I managed to solve this issue, although not correctly . 除了提供正确答案的人之外,这个问题中列出的解决方案并不可怕 .

我发现的唯一答案是为我的列创建足够宽的 single row named range . 在命名范围 put sample data and hide it . SSIS appends the data and reads metadata from the single row (这足以让它放入其中的东西) . The data takes the format of the hidden single row. 这允许 Headers 等

哇屁股有多痛 . 这将需要450天的出口来收回损失的时间 . 但是,我仍然喜欢SSIS并将继续使用它,因为它仍然比Filemaker LOL更好 . 我的下一次尝试将在报表服务器中执行相同的操作 .


原始问题说明:

如果您在Sql Server集成服务设计器中并且想要从第一行以外的其他内容开始将数据导出到Excel文件,那么让我们说第四行,您如何指定?

我尝试进入数据流的Excel目标,将变量中的AccessMode更改为OpenRowSet,然后将变量设置为“YPlatters $ A4:I20000”这无法说它无法找到工作表 . 该表称为YPlatters .

我以为你可以指定(Sheet $)(起始单元格):(结束单元格)?

更新显然,在Excel中,您可以选择一组单元格,并使用名称框命名它们 . 这允许您选择名称而不是没有$ dollar符号的工作表 . 奇怪的是,无论您指定的范围如何,它都会将数据附加到范围之后的下一行 . 奇怪的是,当您添加数据时,它会增加命名选择的行数 .

另一个奇怪的事情是数据采用指定范围的最后一行的格式 . 我的 Headers 行是粗体 . 如果我指定以 Headers 行结尾的范围,则数据会附加到下面的行,并使所有条目变为粗体 . 如果指定较低的一行,则在 Headers 行和数据之间放置一个空行,但数据不是粗体 .

另一个更新无论我尝试什么,SSIS都会对文件的“第一行”进行采样,并根据找到的内容设置元数据 . 但是,如果样本数据的值为零但格式为第一行,则会将该列视为文本,并在前面插入带有单引号的数值('123.34) . 我还尝试了不反映列数据类型的标头 . 我尝试更改Excel目标的元数据,但它总是在我运行项目时将其更改回来,然后无法说它会截断数据 . 如果我告诉它忽略错误,它会导入除该列之外的所有内容 .

一段时间后的几天几个小时......

我尝试了每个组合的另一个更新 . 一个主要的工作示例是从列 Headers 开始创建命名范围 . 格式化列 Headers ,因为您希望数据看起来像数据采用此格式 . 在我的例子中,这些是从A4到E4,这是我定义的范围 . SSIS附加到定义范围之后的行,因此定义A4到E68将从A69开始追加行 . 您将Connection定义为第一行包含字段名称 . 它采用 Headers 行的元数据,奇怪的是,而不是第二行,并且它猜测数据类型,而不是列的格式化数据类型,即 Headers 是文本,因此我的所有元数据都是文本 . 如果 Headers 是粗体,那么所有数据都是粗体 . 我甚至试图制作样本数据行但没有成功......我认为没有人真正使用Excel和默认的MS SSIS导出 .

如果您可以定义没有 Headers 行的“插入范围”(A5到E5)并格式化这些列(货币,而不是粗体等)而不在Excel中跳过一行,这将非常有用 . 根据我的收集,没有第三方连接管理器,没有人使用SSIS导出Excel .

有关如何正确设置以便正确格式化数据的任何想法,即从Excel读取的元数据适用于实际数据,格式是否继承自第一行数据,而不是Excel中的 Headers ?

最后一次更新(2009年7月17日)我的工作非常顺利 . 我添加到Excel的一件事是Excel连接字符串中的IMEX = 1:“Excel 8.0; HDR =是; IMEX = 1” . 这迫使Excel(我认为)查看所有行以查看其中包含哪种数据 . 一般来说,这不会丢弃信息,例如,如果你有一个邮政编码然后大约9行,你有一个zip 4,Excel没有这个空白完全没有错误 . 同IMEX = 1,它识别Zip实际上是一个字符字段而不是数字字段 . 当然,还有一次更新(2009年8月27日)IMEX = 1将成功导入前8行中缺少内容的数据,但是在没有数据的情况下导出数据将失败 . 因此,将它放在导入连接字符串上,而不是导出Excel连接字符串 .

我不得不说,经过这么多摆弄之后,它的效果非常好 .

附:如果您使用的是x64位版本,请确保从C:\ Program Files \ Microsoft SQL Server \ 90 \ DTS.x86 \ Binn调用DTExec . 它将加载32位Excel驱动程序并正常工作 .

8 回答

  • 0

    在脚本任务中创建Excel工作簿会更容易,然后在流程中稍后再提取它吗?

    SSIS的引擎部分很好,但与Excel的集成很糟糕

    “将SSIS与Excel结合使用就像让热焦油在路锥中汇集你的iHole”

  • 1

    Zim博士,我相信你是最初提出这个问题的人 . 我完全感受到你的痛苦 . 我非常喜欢SSIS,但我绝对讨厌Excel标准的有限工具 . 我想要做的就是在Excel中加粗 Headers 或行1记录,而不是加粗以下记录 . 我没有找到一个很好的方法来做到这一点;授予我接近这个没有脚本任务或自定义扩展,但你会认为这个简单的东西是标准选项 . 看起来我可能被迫研究和编写一些对于一项应该如此基础的任务而言很喜欢的东西 . 我自己已经花了很多时间在这上面 . 有谁知道你是否可以使用Excel版本的Excel版本:2000 / XP / 2003?谢谢 .

  • 2

    这是一个旧线程但是如何使用平面文件连接并将数据作为格式化的html文档写出来 . 将页眉中的mime类型设置为“application / excel” . 当您将文档作为附件发送并且收件人打开附件时,它将打开一个浏览器会话,但是应该在其顶部弹出Excel,其中的数据根据页面中指定的样式(CSS)进行格式化 .

  • 2

    你可以让SSIS将数据写入从A1开始的Excel工作表,然后创建另一个工作表,格式化你喜欢的,引用A1处的另一个工作表,但显示为A4吗?也就是说,在“漂亮”的纸张上,A4将参考SSIS表上的A1 .

    这将允许SSIS执行它的好处(操作基于表的数据),但允许Excel格式化或操作,无论您喜欢什么 .

  • 1

    当excel是SSIS中的目标或SSRS中的目标导出类型时,您无法控制格式并指定最终文件的方式 . 我曾经为SSRS编写了一个自定义excel渲染引擎,因为我的客户对生成的最终Excel报告的格式非常严格 . 我使用'Excel xml'在我的自定义渲染器中完成工作 . 可能是您可以使用XML输出并使用XSLT将其转换为Excel XML .

  • 1

    我知道你宁愿不使用脚本组件,所以也许你可以使用脚本包含的代码创建自己的custom task,以便其他人可以在将来使用它 . 检查here以获取示例 .

    如果这似乎可行,我使用的解决方案是CarlosAg Excel Xml Writer Library . 有了这个,您可以创建类似于使用Interop库但以xml格式生成excel的代码 . 这避免了使用Interop对象,这有时会导致excel进程hanging around .

  • 3

    考虑到SSIS对EXCEL的支持,我们可以去做,而不是使用迂回的方式来尝试将数据写入特定的单元格,格式化单元格,对它们进行样式化,这确实是一项非常繁琐的工作 . “模板”的方式来做到这一点 .

    假设我们需要在so&so单元格中写入数据,并在其上完成所有自定义格式化 . 拥有工作表中的所有格式,说“SheetActual”,而保存数据的单元格实际上会有Lookups / refrences / Formulaes来引用SSIS在隐藏工作表中导出的原始数据说同一Excel的“SheetMasterHidden”连接 . 这个“SheetMasterHidden”基本上将主数据保存为SSIS将数据写入excel的默认格式 . 这样您就不必担心格式化数据运行时 .

    格式化Excel是一次性工作“IF”格式不经常更改 . 如果格式改变并且格式是运行时决定的,那么这个解决方案可能不会很好 .

  • 1

    答案就在这个问题上 . 随着时间的推移,它成了一个进展状况 . 但是,如果您创建TABLE演示文稿,则会有SSRS创建Excel文件 . 它的效果也很好 .

相关问题