/**
* public Result getDataBinding(String postcode) throws PostcodeNotFoundException {
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class);
* if(response == null){ throw new PostcodeNotFoundException("Postcode " +
* postcode.toUpperCase()+" cannot be found."); } } catch (JsonParseException e) {
* System.out.println("JsonParseException: "+ e.toString()); } catch (JsonMappingException e) {
* System.out.println("JsonMappingException: "+ e.toString()); } catch (IOException e) {
* System.out.println("IOException: "+ e.toString()); } countNum++; return response; }
*
* <p>public Result getDataBindingFirstTry(String postcode) {
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class); }catch
* (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
* (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
* (IOException e) { // System.out.println("IOException: "+ e.toString()); } return response; }
*
* <p>public Result getDataBindingReTry(String postcode, String bindingName){
*
* <p>Result response = null; try { String dir =
* this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+bindingName+".json"), Result.class); }catch
* (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
* (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
* (IOException e) { System.out.println("IOException: "+ e.toString()); } countNum++; return
* response;
*
* <p>}
*
* <p>public boolean saveJsonFileByPostcode(String postcode, String url) throws
* PostcodeNotFoundException{
*
* <p>// System.setProperty("http.proxySet", "true"); //
* System.setProperty("http.proxyHost","proxy.abdn.ac.uk"); //
* System.setProperty("http.proxyPort", "8080"); URL website = null; ReadableByteChannel rbc =
* null; FileOutputStream fos = null; try { website = new URL(url); rbc =
* Channels.newChannel(website.openStream()); String dir =
* this.getClass().getClassLoader().getResource("").getPath(); // System.out.println("dir:
* "+dir+""+ POSTCODE_FOLDER+File.separator+postcode); File directory = new File(dir+""+
* POSTCODE_FOLDER); if(!directory.exists()){ directory.mkdirs(); } fos = new FileOutputStream(new
* File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"));
* fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE); fos.flush(); fos.close(); return true;
* }catch (MalformedURLException e) { e.printStackTrace(); }catch(FileNotFoundException e){ //
* e.printStackTrace(); throw new PostcodeNotFoundException("Postcode "+postcode.toUpperCase()+"
* cannot be found."); }catch (IOException e) { e.printStackTrace(); } return false; }
*
* <p>public String getURLByPostcode(String postcode){
*
* <p>String url = "http://uk-postcodes.com/postcode/"; url += postcode; url += ".json"; return
* url; }
*/
public void readExcel() {
String path = this.getClass().getClassLoader().getResource("").getPath();
File file = new File(path + File.separator + POSTCODE_TABLE);
OPCPackage opcPackage = null;
try {
opcPackage = OPCPackage.open(file);
} catch (InvalidFormatException e) {
e.printStackTrace();
}
// Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
Sheet sheet = null;
if (POSTCODE_TABLE.toLowerCase().endsWith("xlsx")) {
try {
workbook = new XSSFWorkbook(opcPackage);
} catch (IOException e) {
e.printStackTrace();
}
}
// Get the number of sheets in the xlsx file
int numberOfSheets = workbook.getNumberOfSheets();
if (numberOfSheets > 0) {
// System.out.println("read the first sheet from the workbook...");
sheet = workbook.getSheetAt(0);
}
read(sheet);
}
public static Date getBaseDateFromExcelWithPoi(File file) {
InputStream in = null;
try {
in = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0);
Name name = workbook.getName("雷線基準日");
CellReference cellRef = new CellReference(name.getRefersToFormula());
Row row = sheet.getRow(cellRef.getRow());
Cell baseDateCell = row.getCell(cellRef.getCol());
// System.out.println("cellが日付か:"
// + PoiUtil.isCellDateFormatted(baseDateCell));
Date baseDate = baseDateCell.getDateCellValue();
return baseDate;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (in != null)
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 批量导出
*
* @param data
* @throws FileNotFoundException
*/
public void transform(Map<String, Object> data) throws FileNotFoundException {
FileInputStream templateIs = new FileInputStream(template);
try {
XSSFWorkbook bookTmp = (XSSFWorkbook) transformer.transformXLS(templateIs, data);
bookTmp.setSheetName(0, "第" + count + "页");
if (count == 1) {
book = new SXSSFWorkbook(bookTmp);
} else {
Sheet newSheet = book.createSheet(bookTmp.getSheetName(0));
Sheet sheet = bookTmp.getSheetAt(0);
Util.copySheets(newSheet, sheet);
}
count++;
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} finally {
try {
templateIs.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 单个导出
*
* @param template
* @param data
* @param dest
* @throws IOException
*/
public void transform(String template, Map<String, Object> data, String dest) throws IOException {
try {
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(template, data, dest);
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
public MediaType detect(TikaInputStream input, Metadata metadata) throws IOException {
ZipFile zip = new ZipFile(input.getFile());
for (ZipEntry entry : Collections.list(zip.entries())) {
// Is it an Open Document file?
if (entry.getName().equals("mimetype")) {
InputStream stream = zip.getInputStream(entry);
try {
return fromString(IOUtils.toString(stream, "UTF-8"));
} finally {
stream.close();
}
} else if (entry.getName().equals("_rels/.rels")
|| entry.getName().equals("[Content_Types].xml")) {
// Office Open XML File
// As POI to open and investigate it for us
try {
OPCPackage pkg = OPCPackage.open(input.getFile().toString());
input.setOpenContainer(pkg);
PackageRelationshipCollection core =
pkg.getRelationshipsByType(ExtractorFactory.CORE_DOCUMENT_REL);
if (core.size() != 1) {
throw new IOException(
"Invalid OOXML Package received - expected 1 core document, found " + core.size());
}
// Get the type of the core document part
PackagePart corePart = pkg.getPart(core.getRelationship(0));
String coreType = corePart.getContentType();
// Turn that into the type of the overall document
String docType = coreType.substring(0, coreType.lastIndexOf('.'));
return fromString(docType);
} catch (InvalidFormatException e) {
throw new IOException("Office Open XML File detected, but corrupted - " + e.getMessage());
}
} else if (entry.getName().equals("buildVersionHistory.plist")) {
// This is an iWork document
// Reset and ask
zip.close();
zip = new ZipFile(input.getFile());
return IWorkPackageParser.identifyType(zip);
} else if (entry.getName().equals("META-INF/")) {
// Java Jar
return MediaType.application("java-archive");
}
}
return MediaType.APPLICATION_ZIP;
}
public int getRowCount(String sheetName) {
int retVal = 0;
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
retVal = s.getLastRowNum();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return retVal;
}
/**
* 从文件路径读取相应的Excel文件到对象列表
*
* @param path 文件路径下的path
* @param clz 对象类型
* @param readLine 开始行，注意是标题所在行
* @param tailLine 底部有多少行，在读入对象时，会减去这些行
* @return
*/
public List<Object> readExcelByPath(String path, Class clz, int readLine, int tailLine) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(new File(path));
return readExcel(wb, clz, readLine, tailLine);
} catch (FileNotFoundException e) {
e.printStackTrace();
logger.error(e);
} catch (InvalidFormatException e) {
e.printStackTrace();
logger.error(e);
} catch (IOException e) {
e.printStackTrace();
logger.error(e);
}
return null;
}
private static String extractInvalidFormatMessage(String sampleNameSuffix) {
InputStream is =
OpenXML4JTestDataSamples.openComplianceSampleStream(
"OPCCompliance_CoreProperties_" + sampleNameSuffix);
OPCPackage pkg;
try {
pkg = OPCPackage.open(is);
} catch (InvalidFormatException e) {
// expected during successful test
return e.getMessage();
} catch (IOException e) {
throw new RuntimeException(e);
}
pkg.revert();
// Normally must thrown an InvalidFormatException exception.
throw new AssertionFailedError("expected OPC compliance exception was not thrown");
}
public String getExcelData(String sheetName, int rowNum, int colNum) {
String retVal = null;
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
Row r = s.getRow(rowNum);
Cell c = r.getCell(colNum);
retVal = c.getStringCellValue();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return retVal;
}
public void writeToExcel(String sheetName, int rowNum, int colNum, String desc) {
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
Row r = s.getRow(rowNum);
Cell c = r.createCell(colNum);
c.setCellValue(desc);
FileOutputStream fos = new FileOutputStream(path1);
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public RecordSet getRecords() throws MalformedSourceException {
try {
openReader();
} catch (IOException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
} catch (InvalidFormatException e) {
logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
throw new MalformedSourceException(
"Problem loading file: " + filename + " (" + e.getMessage() + ")");
}
RecordSet ret = new RecordSet();
// Currently we need this flag in order for
// TransformationEngine not to go into an infinite loop.
if (!isRead_) {
logger_.info("Opening file: " + filename);
int nSheets = wb_.getNumberOfSheets();
logger_.info("number of sheets: " + nSheets);
for (int i = 0; i < nSheets; i++) {
Sheet cSheet = wb_.getSheetAt(i);
String cSheetName = cSheet.getSheetName();
for (int j = skipLines_; j <= cSheet.getLastRowNum(); j++) {
if (ignoreLinesAfter_ != 0 && j >= ignoreLinesAfter_) {
break;
}
Row row = cSheet.getRow(j);
MapRecord rec = new MapRecord();
for (int k = 0; k < row.getLastCellNum(); k++) {
if (!fieldMap_.keySet().contains(k)) {
continue;
}
StringValue val;
Cell cCell = row.getCell(k);
if (cCell == null) {
continue;
}
int cellType = cCell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING:
val = new StringValue(cCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cCell)) {
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
val = new StringValue(sdf.format(cCell.getDateCellValue()));
} else {
val = new StringValue(String.valueOf(cCell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BLANK:
val = new StringValue("");
break;
case Cell.CELL_TYPE_BOOLEAN:
val = new StringValue(String.valueOf(cCell.getBooleanCellValue()));
break;
default:
val = new StringValue("Unsupported cell type");
}
rec.addValue(fieldMap_.get(k), val);
}
// TODO remove the hardcoded value
rec.addValue("ExcelSheetName", new StringValue(cSheetName));
ret.addRecord(rec);
}
}
isRead_ = true;
}
return ret;
}
/**
* Read Excel format file.
*
* @param filename
*/
private void readExcel(String filename) {
try {
FileInputStream fin = new FileInputStream(filename);
if (flgDoubleQuotes) {
delimiter = "\"" + delimiter + "\"";
}
try {
Workbook wb = WorkbookFactory.create(fin);
// HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
String sheetname = sheet.getSheetName();
String fname = sheetname;
if (delimiter.equals("\t")) {
fname = fname + ".tsv";
} else {
fname = fname + ".csv";
}
File file = new File(fname);
FileOutputStream fout = new FileOutputStream(file);
OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
BufferedWriter bw = new BufferedWriter(ow);
for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
Row row = rowIter.next();
String tmp = "";
if (flgDoubleQuotes) {
tmp = "\"";
}
if (row != null) {
for (int k = 0; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
// CellValue celv = evaluator.evaluate(cell);
if (cell == null) {
tmp = tmp + delimiter;
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
tmp = tmp + " " + delimiter;
break;
case Cell.CELL_TYPE_NUMERIC:
tmp = tmp + getNumericValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_STRING:
tmp = tmp + getStringValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_FORMULA:
try {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
tmp = tmp + getNumericValue(cell) + delimiter;
} else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
tmp = tmp + getStringValue(cell) + delimiter;
}
} catch (FormulaParseException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
} catch (NotImplementedException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
}
break;
default:
tmp = tmp + " " + delimiter;
}
}
tmp = tmp.substring(0, tmp.length() - 1);
}
bw.write(tmp + "\n");
}
bw.flush();
bw.close();
ow.close();
fout.close();
System.gc();
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
