输出具有多个工作表的单个Excel文件 [英] Outputting a single Excel file with multiple worksheets

问题描述

Talend Open Studio for Data Integration中是否有一个组件可以输出一个Excel文件，但其中包含2个单独的工作表?

我想将原始文件中的某些列分成另一张纸，而将另一组列分隔到第二张纸.

解决方案

您需要将数据输出到两个单独的tFileOutputExcel组件中，第二个组件应设置为将数据作为不同的表附加到文件中.

一个简单的示例具有针对一个唯一ID保留的一些名称和年龄数据，需要将其分为两个单独的工作表，一个工作表上的ID和名称，另一工作表上的ID和年龄.

我正在使用tRowGenerator组件生成此数据，该组件配置为生成ID和随机名以及年龄在18到75之间的序列:

然后我使用tMap组件拆分这些数据:

第一个数据流可以转到第一个tFileOutputExcel组件，以使用名称"表创建文件:

不幸的是，我们不能仅将第二个数据表直接输出到下一个文件，因为Talend将需要在Excel文件上打开写锁.因此，在这种情况下，我们改为使用tBufferOutput组件将数据存储到内存中(尽管我们也可以使用tHashOutput组件，或者如果可能超过总内存，则可以将数据存储在磁盘中的临时文件或数据库中).

在第一个子作业完成后，将名称数据写入目标文件的名称表中，然后我们就可以从缓冲区中读取年龄数据，并读取到第二个tFileOutputExcel中，然后将其配置为将数据表附加到目标文件中.目标文件:

Is there a component in Talend Open Studio for Data Integration to be able to output a single Excel file but with 2 separate sheets in it?

I want to separate some columns in the original file into another sheet and another set of columns to the second sheet.

解决方案

You'll need to output your data into two separate tFileOutputExcel components with the second one set to append the data to the file as a different sheet.

A quick example has some name and age data held against a unique id that needs to be split into two separate sheets with id and name on one sheet and id and age on another sheet.

I'm generating this data using the tRowGenerator component configured to generate a sequence for the id and random first names and ages between 18 and 75:

I then split this data using a tMap component:

The first flow of data can go to the first tFileOutputExcel component to create the file with a "Names" sheet:

Unfortunately we can't just output the second sheet of data straight away to the next file as Talend will need to open a write lock on the Excel file. So instead we stash the data into memory using the tBufferOutput component in this case (although we could also use a tHashOutput component or potentially stash the data on disk in either a temporary file or database if this is likely to exceed total memory).

Once the first sub job is completed writing the names data to the Names sheet of our target file we can then read the Age data out of the buffer and into the second tFileOutputExcel which is then configured to append the sheet of data to the target file:

这篇关于输出具有多个工作表的单个Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！