Kettle Excel输入的详细解析

一、 Excel输入概述

Excel输入是kettle中提供的一个数据抽取器，用于将Excel文件中的数据导入到kettle中进行处理。Excel数据源常被使用在各种报表生成、数据整理等场景中。Kettle在操作Excel文件时比较方便，支持多种格式的Excel导入，包括xls、xlsx、CSV等格式。

在使用Kettle进行Excel输入时，需要指定Excel文件的路径和Sheet页，还可以设置过滤器条件，筛选需要导入的数据。

//Kettle Excel输入代码示例
<job-entry>
  <name>Excel Input</name>
  <description>Reads values from an Excel file.</description>
  <type>ExcelInput</type>
  <filename>C:\data\test.xlsx</filename>
  <sheet>Sheet1</sheet>
  <header>Y</header>
  <noempty>N</noempty>
  <stop_on_empty>Y</stop_on_empty>
</job-entry>

二、Excel导入过程中的数据类型转换

Excel文件中包含的数据类型是比较丰富的，kettle在进行转换时也考虑了这一点。Kettle将Excel输入字段分为以下四种类型：String、Integer、Number和Date，这四种类型可以涵盖Excel文件中大部分的数据类型。

在进行Excel数据类型转换时，需要注意一些细节问题，在转换时要注意中英文逗号、小数点等问题。此外，在使用Date类型时还需要注意日期格式的转换，kettle支持大部分常用日期格式。

//Excel输入中的数据类型转换代码示例
<fields>
  <field>
    <column_name>ID</column_name>
    <data_type>Integer</data_type>
    <date_format_reporting>YYYY/MM/dd</date_format_reporting>
    <ignore_change_of_length>N</ignore_change_of_length>
    <trim_type>none</trim_type>
  </field>
  <field>
    <column_name>Name</column_name>
    <data_type>String</data_type>
    <date_format_reporting>yyyy-MM-dd</date_format_reporting>
    <ignore_change_of_length>N</ignore_change_of_length>
    <trim_type>none</trim_type>
  </field>
  <field>
    <column_name>Price</column_name>
    <data_type>Number</data_type>
    <date_format_reporting></date_format_reporting>
    <ignore_change_of_length>N</ignore_change_of_length>
    <trim_type>none</trim_type>
  </field>
  <field>
    <column_name>Date</column_name>
    <data_type>Date</data_type>
    <date_format_reporting>YYYY-MM-DD HH:mm:ss</date_format_reporting>
    <locale>en_US</locale>
    <ignore_change_of_length>N</ignore_change_of_length>
    <trim_type>none</trim_type>
  </field>
</fields>

三、Excel常见问题解决方法

在使用Excel输入时，会遇到一些常见问题，比如Excel中数据乱码、文件格式不兼容等问题。这时我们需要对Excel文件进行一些处理，以确保文件能够正常被导入。

对于数据乱码问题，可以使用Microsoft Office自带的“文本导入向导”功能进行处理。对于文件格式不兼容问题，可以将文件转换为更加普及的xlsx格式。

//Excel文件转换成xlsx格式代码示例
private static void convertToXlsx(File file) throws IOException {
    // 创建输入流
    FileInputStream fis = new FileInputStream(file);
    // 读取XLS文件
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    // 输出XLSX文件
    FileOutputStream fos = new FileOutputStream(new File("test.xlsx"));
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
    XSSFSheet xssfSheet = xssfWorkbook.createSheet("Sheet1");
    int rowNum = 0;
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet hssfSheet = wb.getSheetAt(i);
        if (hssfSheet == null) {
            continue;
        }
        for (int j = 0; j <= hssfSheet.getLastRowNum(); j++) {
            HSSFRow hssfRow = hssfSheet.getRow(j);
            if (hssfRow == null) {
                continue;
            }
            XSSFRow xssfRow = xssfSheet.createRow(rowNum++);
            for (int k = 0; k < hssfRow.getLastCellNum(); k++) {
                HSSFCell hssfCell = hssfRow.getCell(k);
                if (hssfCell == null) {
                    continue;
                }
                XSSFCell xssfCell = xssfRow.createCell(k);
                xssfCell.setCellValue(hssfCell.toString());
            }
        }
    }
    xssfWorkbook.write(fos);
    fos.close();
}

四、Excel输入优化

当需要处理大量数据时，Excel输入会变得比较耗时。在这种情况下，我们可以进行一些优化，以提高Excel数据导入的效率。

首先，尽量缩小导入数据的范围，只导入需要的数据。其次，我们可以使用一些开源或商业的插件，如Pentaho Excel Input、Apache POI等，对Excel输入进行性能优化。此外，对于非常庞大的Excel文件，可以使用分块或分批次读入的方式进行处理。

//分块读取Excel文件代码示例
private static void readExcelByBlock(String fileName) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(new File(fileName));
    Sheet sheet = workbook.getSheetAt(0);
    int rowStart = 1; //第一行是表头，所以从第二行开始读
    int pageSize = 500; //每块读取500条数据
    int totalRowNum = sheet.getLastRowNum() + 1;//getLastRowNum获取到的是最后一行的行标（索引），所以要加1
    int pageNum = totalRowNum / pageSize + 1; //总共需要读取的块数
    for (int pageIndex = 1; pageIndex <= pageNum; pageIndex++) {//分页读取
        int endIndex = pageIndex * pageSize;
        if (endIndex > totalRowNum) {
            endIndex = totalRowNum;
        }
        for (int i = rowStart; i < endIndex; i++) {//读取一页的数据
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                System.out.print(cell);
            }
            System.out.println();
        }
        rowStart = endIndex;
    }
    workbook.close();
}

五、Excel输入的注意事项

在使用Excel输入时，还需要注意以下几个问题：

1、Excel输入中如果存在空行，会直接跳过。

2、Excel输入中不支持动态变更Sheet页。

3、如果Excel文件被占用或者没有权限访问，会报错并退出任务。

4、Kettle内嵌了部分Pentaho BI components，这些组件和Kettle版本有关，有些组件可能不适用于某些Kettle版本。

六、总结

通过以上的阐述，我们可以总结出Excel输入的主要特点和使用方法。在使用Excel输入时，需要注意数据类型转换问题、常见问题的解决方法、性能优化等方面。如果能够熟练运用Excel输入，可以为各种数据处理、报表生成等场景提供优质的解决方案。