@Test
public void testImportExcel2007() throws Exception {
long beginTime = System.currentTimeMillis();
String fileName = "D:\\Backup\\test.xlsx";
List<ExcelData> dataList = Lists.newArrayList();
OPCPackage pkg = OPCPackage.open(fileName);
XSSFReader r = new XSSFReader(pkg);
XMLReader parser = XMLReaderFactory.createXMLReader();
ContentHandler handler = new Excel2007SheetHandler(dataList);
parser.setContentHandler(handler);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
// 把最后剩下的不足batchSize大小
if (dataList.size() > 0) {
doBatchSave(dataList);
}
long endTime = System.currentTimeMillis();
log.info("耗时(秒):" + (endTime - beginTime) / 1000);
}
/** 导入 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);
}