使用Talend ETL合并Excel单元格

I want to write a MySQL Data into Excel file. In that, I need to create table border for my data as well need to merging the few column headers.

我想将MySQL数据写入Excel文件。在那里，我需要为我的数据创建表格边框，以及合并几个列标题。

I have done the mapping to Load the MySQL data into Excel Sheet. But I don't know the way to create table border as well merging the column headers.

我已完成映射以将MySQL数据加载到Excel工作表中。但我不知道创建表格边框以及合并列标题的方法。

Below code will help me to Merge the cells, But how to incorporate this code in between my two talend components,

下面的代码将帮助我合并单元格，但如何将此代码合并到我的两个talend组件之间，

tMySQLInput -> tFileOutputExcel

tMySQLInput - > tFileOutputExcel

import java.io.*;

import java.io. *;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.usermodel。*;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.usermodel。*;

import org.apache.poi.ss.util.CellRangeAddress;

public class MergeCellsAcrossColumns {

公共类MergeCellsAcrossColumns {

    public static void main(String[] args) throws Exception{
            /* Create Workbook and Worksheet */
            HSSFWorkbook my_workbook = new HSSFWorkbook();
            HSSFSheet my_sheet = my_workbook.createSheet("Merge Cells");                
            Row row = my_sheet.createRow((short) 1);
            Cell cell = row.createCell((short) 1);
            cell.setCellValue("Merge Data with Apache POI");
            my_sheet.addMergedRegion(new CellRangeAddress(
                    1, // mention first row here
                    1, //mention last row here, it is 1 as we are doing a column wise merging
                    1, //mention first column of merging
                    5  //mention last column to include in merge
                    ));
            /* Write changes to the workbook */
            FileOutputStream out = new FileOutputStream(new File("C:\\Merge_Across_Columns.xls"));
            my_workbook.write(out);
            out.close();


    }

}

1 个解决方案

#1

you can use tJavaRow between your components. This component allows you to write java code and transform data. this component has input and output schema where you can specify input this component will take and output this component will give. section below for import will move to Advance Settings section of tJavaRow.

您可以在组件之间使用tJavaRow。该组件允许您编写Java代码并转换数据。此组件具有输入和输出模式，您可以在其中指定此组件将采用的输入以及此组件将输出的输出。下面的导入部分将移至tJavaRow的“高级设置”部分。

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

You can write your java code in BAsic Settings section your refer to input data schema column by

您可以在BAsic Settings部分编写您的java代码，参考输入数据模式列

input_row.column_name
and output as output_row.column_name

...(try to user GenerateCode option to see how input_row and output_row works)

...（尝试使用GenerateCode选项查看input_row和output_row的工作原理）

Ideally for merging/transforming data you can also use tMap which can merge/transform rows and output the data to next level

理想情况下，对于合并/转换数据，您还可以使用tMap，它可以合并/转换行并将数据输出到下一级

#1

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;