private void processFile(String filename) throws Exception {
HSSFRequest req = new HSSFRequest();
mockListen = new MockHSSFListener();
listener = new FormatTrackingHSSFListener(mockListen);
req.addListenerForAllRecords(listener);
HSSFEventFactory factory = new HSSFEventFactory();
try {
InputStream is = HSSFTestDataSamples.openSampleFileStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(is);
factory.processWorkbookEvents(req, fs);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void setUp() {
HSSFRequest req = new HSSFRequest();
mockListen = new MockHSSFListener();
listener = new SheetRecordCollectingListener(mockListen);
req.addListenerForAllRecords(listener);
HSSFEventFactory factory = new HSSFEventFactory();
try {
InputStream is = HSSFTestDataSamples.openSampleFileStream("3dFormulas.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
factory.processWorkbookEvents(req, fs);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* Read an excel file and spit out what we find.
*
* @param args Expect one argument that is the file to read.
* @throws IOException When there is an error processing the file.
*/
public static void main(String[] args) throws IOException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// con = DriverManager
// .getConnection("jdbc:sqlserver://10.130.133.3:1433;DatabaseName=RTB;user=i264678;password=;SelectMethod=cursor ");
con =
DriverManager.getConnection(
"jdbc:sqlserver://10.135.128.227:1433;DatabaseName=RTBWTC;user=i264678;password=;SelectMethod=cursor ");
System.out.println("connected");
// create a new file input stream with the input file specified
// at the command line
FileInputStream fin = new FileInputStream(args[0]);
// create a new org.apache.poi.poifs.filesystem.Filesystem
POIFSFileSystem poifs = new POIFSFileSystem(fin);
// get the Workbook (excel part) stream in a InputStream
InputStream din = poifs.createDocumentInputStream("Workbook");
// construct out HSSFRequest object
HSSFRequest req = new HSSFRequest();
// lazy listen for ALL records with the listener shown above
req.addListenerForAllRecords(new LoadServiceFromExcel());
// create our event factory
HSSFEventFactory factory = new HSSFEventFactory();
// process our events based on the document input stream
factory.processEvents(req, din);
// once all the events are processed close our file input stream
fin.close();
// and our document input stream (don't want to leak these!)
din.close();
System.out.println("done.");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void parse(Key key) throws IOException {
_firstRow = true;
InputStream is = DKV.get(key).openStream();
try {
_fs = new POIFSFileSystem(is);
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
_formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
request.addListenerForAllRecords(_formatListener);
factory.processWorkbookEvents(request, _fs);
} finally {
try {
is.close();
} catch (IOException e) {
}
}
}
/**
* @Title: processRecord @Description: process parse excel.
*
* @param inputStream
* @throws IOException
* @throws
*/
public void processRecord(InputStream inputStream) throws IOException {
MissingRecordAwareHSSFListener missingRecordAwarelistener =
new MissingRecordAwareHSSFListener(this);
formatTrackingHSSFListener = new FormatTrackingHSSFListener(missingRecordAwarelistener);
sheetRecordCollectingListener =
new EventWorkbookBuilder.SheetRecordCollectingListener(formatTrackingHSSFListener);
HSSFRequest request = new HSSFRequest();
request.addListenerForAllRecords(sheetRecordCollectingListener);
HSSF_EVENT_FACTORY.processWorkbookEvents(request, new POIFSFileSystem(inputStream));
}
/** 导入 excel 2003 biff格式 如果是xml格式的 可以使用SAX（未测试） */
@Test
public void testImportExcel2003() throws Exception {
long beginTime = System.currentTimeMillis();
String fileName = "D:\\Backup\\Book1.xls";
List<ExcelData> dataList = Lists.newArrayList();
// 输入流
InputStream fis = new BufferedInputStream(new FileInputStream(fileName));
// 创建 org.apache.poi.poifs.filesystem.Filesystem
POIFSFileSystem poifs = new POIFSFileSystem(fis);
// 从输入流 得到 Workbook(excel 部分)流
InputStream din = poifs.createDocumentInputStream("Workbook");
// 构造 HSSFRequest
HSSFRequest req = new HSSFRequest();
// 添加监听器
req.addListenerForAllRecords(new Excel2003Listener(dataList));
// 创建事件工厂
HSSFEventFactory factory = new HSSFEventFactory();
// 根据文档输入流处理事件
factory.processEvents(req, din);
// 关闭输入流
fis.close();
// 关闭文档流
din.close();
System.out.println(dataList.size());
// 把最后剩下的不足batchSize大小
if (dataList.size() > 0) {
doBatchSave(dataList);
}
long endTime = System.currentTimeMillis();
log.info("耗时(秒):" + (endTime - beginTime) / 1000);
}
/*
* (non-Javadoc)
*
* @see
* com.toolsverse.etl.connector.DataSetConnector#populate(com.toolsverse
* .etl.connector.DataSetConnectorParams, com.toolsverse.etl.common.DataSet,
* com.toolsverse.etl.driver.Driver)
*/
public ConnectorResult populate(ExcelConnectorParams params, DataSet dataSet, Driver driver)
throws Exception {
if (dataSet == null
|| params == null
|| Utils.isNothing(dataSet.getName())
|| (driver == null && dataSet.getDriver() == null)) {
ConnectorResult result = new ConnectorResult();
result.setRetCode(ConnectorResult.VALIDATION_FAILED_CODE);
if (dataSet == null)
result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NULL.getValue());
if (driver == null && dataSet.getDriver() == null)
result.addResult(ConnectorResource.VALIDATION_ERROR_DRIVER_NULL.getValue());
if (params == null)
result.addResult(ConnectorResource.VALIDATION_ERROR_PARAMS_NULL.getValue());
if (dataSet != null && Utils.isNothing(dataSet.getName()))
result.addResult(ConnectorResource.VALIDATION_ERROR_DATA_SET_NO_NAME.getValue());
return result;
}
dataSet.clear();
driver = driver != null ? driver : dataSet.getDriver();
if (!params.isSilent())
Logger.log(
Logger.INFO,
EtlLogger.class,
EtlResource.LOADING_DATASET_MSG.getValue() + dataSet.getName() + "...");
FileInputStream fin = null;
POIFSFileSystem poifs = null;
try {
String fileName = null;
if (params.getInputStream() == null) {
fileName =
SystemConfig.instance()
.getPathUsingAppFolders(
params.getFileName(
dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(),
".xls",
true));
fin = new FileInputStream(fileName);
poifs = new POIFSFileSystem(fin);
} else poifs = new POIFSFileSystem(params.getInputStream());
XlsProcessor xlsProcessor = new XlsProcessor(params, dataSet, driver);
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(xlsProcessor);
FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener);
xlsProcessor.setFormatListener(formatListener);
HSSFRequest request = new HSSFRequest();
request.addListenerForAllRecords(formatListener);
HSSFEventFactory factory = new HSSFEventFactory();
try {
factory.processWorkbookEvents(request, poifs);
} catch (Exception ex) {
if (!params.isMaxRowsExceededException(ex) && !params.isSheetAlreadyExatractedException(ex))
throw ex;
}
if (dataSet.getFieldCount() > 0
&& dataSet.getRecordCount() == 0
&& params.getAddRecordCallback() != null) {
params.getAddRecordCallback().onAddRecord(dataSet, driver, null, 0);
}
ConnectorResult connectorResult = new ConnectorResult();
connectorResult.addResult(
Utils.format(
FileConnectorResource.FILE_POPULATED.getValue(),
new String[] {FilenameUtils.getName(fileName)}));
return connectorResult;
} finally {
if (fin != null) fin.close();
if (params.getInputStream() != null && params.isCloseInput()) params.getInputStream().close();
if (params.getAfterCallback() != null) params.getAfterCallback().onAfter(dataSet, driver);
}
}