/**
* 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 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;
}
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();
}
}
/**
* 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();
}
}