kettle读取excel文件输入数据库

db66239192 2009-12-23 12:07:22

请问下用kettle的Api怎么成excel文件中读取数据再插入指定表中？
成数据库中读取数据放入excel文件中这个我写了可以借鉴下？
但哦不知道读再插进去怎么写了
哪位大哥能指点下？。。谢谢！
下面是成数据库中读取数据放入excel文件中的代码：
public class tt
{
public static final String[] databasesXML = {
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<connection>" +
"<name>source</name>" +
"<server>localhost</server>" +
"<type>MYSQL</type>" +
"<access>Native</access>" +
"<database>gomai</database>" +
"<port>3306</port>" +
"<username>root</username>" +
"<password>123456</password>" +
"<servername/>" +
"<data_tablespace/>" +
"<index_tablespace/>" +
"<attributes>" +
"<attribute><code>extra_option_mysql.defaultfetchsize</code><attribute>500</attribute></attribute>" +
"<attribute><code>extra_option_mysql.usecursorfetch</code><attribute>true</attribute></attribute>" +
"<attribute><code>port_number</code><attribute>3306</attribute></attribute>" +
"</attributes>" +
"</connection>"
};

/**
* Creates a new Transformation using input parameters such as the tablename to read from.
* @param transformationName 转换的名称
* @param sourceDatabaseName 来源的数据库名
* @param sourceTableName 表名
* @param sourceFields 表里的列字段数组
*/
public static final TransMeta buildCopyTable(String transformationName, String sourceDatabaseName, String sourceTableName, String[] sourceFields) throws KettleException
{

EnvUtil.environmentInit();
try
{
//创建一个转换
// Create a new transformation...
//
TransMeta transMeta = new TransMeta();
transMeta.setName(transformationName);
DatabaseMeta databaseMeta = new DatabaseMeta(databasesXML[0]);
transMeta.addDatabase(databaseMeta);
DatabaseMeta sourceDBInfo = transMeta.findDatabase(sourceDatabaseName);

// create the source step...创建输入的步骤，就是读的内容
//
String fromstepname = "read from [" + sourceTableName + "]";
TableInputMeta tii = new TableInputMeta();
tii.setDatabaseMeta(sourceDBInfo);
//Const.CR相当于1个回车换行
String selectSQL = "SELECT "+Const.CR;
for (int i=0;i<sourceFields.length;i++)
{
if (i>0) selectSQL+=", "; else selectSQL+=" ";
selectSQL+=sourceFields[i]+Const.CR;
}
selectSQL+="FROM "+sourceTableName;
//上面的循环是产生查询的SQL语句
tii.setSQL(selectSQL);
StepLoader steploader = StepLoader.getInstance();
//加载
String fromstepid = steploader.getStepPluginID(tii);
//根据上面的创建了一个来源步骤
StepMeta fromstep = new StepMeta(fromstepid, fromstepname, (StepMetaInterface) tii);
fromstep.setLocation(150, 100);
fromstep.setDraw(true);
fromstep.setDescription("Reads information from table [" + sourceTableName + "] on database [" + sourceDBInfo + "]");
transMeta.addStep(fromstep);

//添加xls输出步骤
// Add the TableOutputMeta step...
//
String tostepname = "write to outputExcel ";
ExcelOutputMeta toi = new ExcelOutputMeta();

//下面这个设置是全部设置成默认，设成这个，其余的可以都不用设置
//toi.setDefault();

//这段是手动设置
toi.setFileName("test");//设置文件名
toi.setExtension("xls");//扩展名
toi.setHeaderEnabled(true);//页头
toi.setSheetname("测试");//sheet工作簿的名字
int nrfields = 0;
toi.allocate(nrfields);
ExcelField outputFields[] = new ExcelField[nrfields];
for(int i = 0; i < nrfields; i++)
{
outputFields[i] = new ExcelField();
outputFields[i].setName("field" + i);
outputFields[i].setType("Number");
outputFields[i].setFormat("0,000,000.00;-0,000,000.00");
}

toi.setOutputFields(outputFields);
//加载这个步骤
String tostepid = steploader.getStepPluginID(toi);
//输出步骤
StepMeta tostep = new StepMeta(tostepid, tostepname, (StepMetaInterface) toi);
tostep.setLocation(550, 100);
tostep.setDraw(true);
tostep.setDescription("写入xls文件");
transMeta.addStep(tostep);

//为2个步骤创建连接
// Add a hop between the two steps...
//
TransHopMeta hi = new TransHopMeta(fromstep, tostep);
transMeta.addTransHop(hi);

// 返回这个转换
return transMeta;
}
catch (Exception e)
{
throw new KettleException("An unexpected error occurred creating the new transformation", e);
}
}

/**
* 1) create a new transformation 建立一个新的转换（transformation）
* 2) save the transformation as XML file 把转换（transformation）存储为XML文件
* 3) Execute the transformation 执行转换（transformation）
* @param args
*/
public static void main(String[] args) throws Exception
{
EnvUtil.environmentInit();
// Init the logging...
LogWriter log = LogWriter.getInstance("TransBuilder.log", true, LogWriter.LOG_LEVEL_DETAILED);

// Load the Kettle steps & plugins
StepLoader stloader = StepLoader.getInstance();
if (!stloader.read())
{
log.logError("TransBuilder", "Error loading Kettle steps & plugins... stopping now!");
return;
}
//要复制的来源数据
// The parameters we want, optionally this can be
String fileName = "NewTrans.xml";
String transformationName = "Test Transformation";
//上面XML中配置的connection名字
String sourceDatabaseName = "source";
//要找的表名
String sourceTableName = "user";
String sourceFields[] = {
"*" //"*"代表该表中的所有列或字段
};
// 产生这个转换
TransMeta transMeta = tt.buildCopyTable(
transformationName,
sourceDatabaseName,
sourceTableName,
sourceFields
);
//将上面生成的转换配置保存为XML文件
String xml = transMeta.getXML();
DataOutputStream dos = new DataOutputStream(new FileOutputStream(new File(fileName)));
dos.write(xml.getBytes("UTF-8"));
dos.close();
System.out.println("Saved transformation to file: "+fileName);

//执行转换
Trans trans = new Trans(transMeta);
trans.execute(null);
trans.waitUntilFinished();

}
}

...全文