private void initActiveSheet() {
if (dataFile == null) return;
if (sheetIndex > -1) {
dataSheet = dataFile.getSheetAt(sheetIndex);
if (dataSheet == null) {
throw new IndexOutOfBoundsException(
"Sheet with index "
+ sheetIndex
+ " does not exist in file: "
+ inputFile.getFullPath());
}
} else if (sheetName != null) {
dataSheet = dataFile.getSheet(sheetName);
if (dataSheet == null) {
throw new IllegalArgumentException(
"Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
}
} else {
int index = dataFile.getActiveSheetIndex();
dataSheet = dataFile.getSheetAt(index);
}
headerColumns = null;
int numMergedRegions = dataSheet.getNumMergedRegions();
mergedRegions = new ArrayList<>(numMergedRegions);
for (int i = 0; i < numMergedRegions; i++) {
mergedRegions.add(dataSheet.getMergedRegion(i));
}
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
public void removerCliente(String cpf) {
int count = 1;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
while (count < ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
Row row = wb.getSheetAt(0).getRow(count);
wb.getSheetAt(0).removeRow(row);
achou = true;
} else {
count++;
}
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void inserirCliente(Cliente cliente) {
int count = 0;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
// verifica se existe um espaço em null entre as linhas da planilha
while (count <= ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count) == null) {
Row selectedRow = wb.getSheetAt(0).createRow(count);
this.createRow(selectedRow, cliente);
achou = true;
} else if (count == ultimaLinha) {
Row selectedRow = wb.getSheetAt(0).createRow(wb.getSheetAt(0).getLastRowNum() + 1);
this.createRow(selectedRow, cliente);
achou = true;
}
count++;
}
try {
this.fos = new FileOutputStream("RepositorioCliente.xls");
wb.write(fos);
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int getExcelLastCellNum() {
int count = 0;
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row aRow = aSheet.getRow(0);
count = aRow.getLastCellNum();
}
return count;
}
public void insertFormula(int numRow, int numCol, String formula) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.createRow((short) numRow);
Cell csCell = row.createCell((short) numCol);
csCell.setCellFormula(formula);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
private void addSumBottom() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) (ROW_HEIGHT + 100));
for (int j = 0; j < 1000000; j++) {
if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
&& StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
break;
}
Cell cell = row.createCell(j);
cell.setCellStyle(Style.get(book).SUM);
if (j == 0) {
cell.setCellValue("合计");
} else {
cell.setCellValue(0);
}
if (j >= 7) {
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(j + 1),
5,
CellUtils.convertToABC(j + 1),
sheet.getLastRowNum()));
}
}
sheet.addMergedRegion(
new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
}
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
for (int j = 4; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
for (int k = 0; k <= row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
cell.setCellStyle(Style.get(book).SUM);
}
}
}
}
}
public void insertNumToCell(int numRow, int numCol, Double num) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.createRow((short) numRow);
Cell csCell = row.createCell((short) numCol);
csCell.setCellValue(num.doubleValue());
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
public void insertStringToCell(int numRow, int numCol, String strval) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.createRow((short) numRow);
Cell csCell = row.createCell((short) numCol);
// csCell.setEncoding(HSSFCell.ENCODING_UTF_16);
csCell.setCellValue(strval);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
public <T> List<T> map() throws Throwable {
InputStream inputStream = null;
List<T> items = new LinkedList<>();
try {
Iterator<Row> rowIterator;
inputStream = new FileInputStream(excelFile);
Workbook workbook = createWorkbook(inputStream);
int numberOfSheets = workbook.getNumberOfSheets();
for (int index = 0; index < numberOfSheets; index++) {
Sheet sheet = workbook.getSheetAt(index);
rowIterator = sheet.iterator();
Map<String, Integer> nameIndexMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
readExcelHeader(row, nameIndexMap);
} else {
items.add((T) readExcelContent(row, nameIndexMap));
}
}
}
} finally {
if (inputStream != null) {
inputStream.close();
}
}
return items;
}
@Override
public void parse(InputStream in, ICallback callback) {
Workbook wb = null;
try {
callback.begin();
wb = WorkbookFactory.create(in);
int sheetNumber = wb.getNumberOfSheets();
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = wb.getSheetAt(i);
int line = this.startLine;
Row row = null;
while ((row = sheet.getRow(line)) != null) {
Object[] data = this.readLineData(row);
if (data != null) callback.visit(data, line);
line++;
}
}
callback.end();
} catch (Exception e) {
Log.logError(e.getMessage());
} finally {
try {
in.close();
} catch (Exception ex) {
}
}
}
public void rebuildFormula() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
rebuildFormula(book.getSheetAt(i));
}
addSumBottom();
}
/** Reads excel file and matches cells to values */
public Stock readFile(int firstItem, int lastItem)
throws IllegalStateException, InvalidFormatException, IOException {
List<Item> stockList = new ArrayList();
Workbook book = WorkbookFactory.create(this.sourceFile);
Sheet workSheet = book.getSheetAt(sheet);
Item emptyItem = new Item("Системе не удалось разпознать элемент", 0, 0, 0, 0, 0, 0, 0);
for (int n = firstItem - 1; n < lastItem; n++) {
try {
Row row = workSheet.getRow(n);
Item item =
new Item(
row.getCell(cellCodes[0]).toString(),
row.getCell(cellCodes[1]).getNumericCellValue(),
row.getCell(cellCodes[2]).getNumericCellValue(),
row.getCell(cellCodes[3]).getNumericCellValue(),
row.getCell(cellCodes[4]).getNumericCellValue(),
row.getCell(cellCodes[5]).getNumericCellValue(),
row.getCell(cellCodes[6]).getNumericCellValue(),
row.getCell(cellCodes[7]).getNumericCellValue());
stockList.add(item);
} catch (NullPointerException ex) {
stockList.add(emptyItem);
}
}
// pkg.close();
Stock stock = new Stock(stockList);
return stock;
}
/**
* Called to update the embedded Excel workbook. As the format and structire of the workbook are
* known in advance, all this code attempts to do is write a new value into the first cell on the
* first row of the first worksheet. Prior to executing this method, that cell will contain the
* value 1.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
* classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
* SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
* processing, throwing this exception if that file is invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
*/
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
PackagePart pPart = null;
Iterator<PackagePart> pIter = null;
List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
pIter = embeddedDocs.iterator();
while (pIter.hasNext()) {
pPart = pIter.next();
if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
|| pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
// Get an InputStream from the pacage part and pass that
// to the create method of the WorkbookFactory class. Update
// the resulting Workbook and then stream that out again
// using an OutputStream obtained from the same PackagePart.
workbook = WorkbookFactory.create(pPart.getInputStream());
sheet = workbook.getSheetAt(SHEET_NUM);
row = sheet.getRow(ROW_NUM);
cell = row.getCell(CELL_NUM);
cell.setCellValue(NEW_VALUE);
workbook.write(pPart.getOutputStream());
}
}
// Finally, write the newly modified Word document out to file.
this.doc.write(new FileOutputStream(this.docFile));
}
}
@Test
public void report_FacilityUsage_asOperator2() {
final ReportParameters params = baseParams();
params.interval = 3 * 60;
registerMockFacilityUsages(facility1, apiUser);
registerMockFacilityUsages(facility2, apiUser2);
final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", operator2User);
// If this succeeds, the response was a valid excel file
final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
assertThat(getSheetNames(workbook)).containsExactly("Käyttöasteraportti", "Selite");
final Sheet usages = workbook.getSheetAt(0);
// Header and one for each usage type
assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(3);
// Only operator2 visible
assertThat(getDataFromColumn(usages, 3))
.containsOnly("Operaattori", operator2.name.fi)
.doesNotContain(operator1.name.fi);
final List<String> headers = getDataFromRow(usages, 0);
assertThat(headers.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, headers.size()))
.containsExactly("00:00", "03:00", "06:00", "09:00", "12:00", "15:00", "18:00", "21:00");
// Get the hourly utilizations for CAR
// Results are not interpolated.
final List<String> row = getDataFromRow(usages, 1);
assertThat(row.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, row.size()))
.containsExactly("24", "24", "24", "24", "0", "0", "0", "24");
}
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;
}
public void replaceDataToCell(int numRow, int numCol, String temstr, String strval) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
Cell csCell = row.getCell((short) numCol);
String temp = "";
temp = csCell.getStringCellValue();
temp = temp.replaceAll(temstr, strval);
csCell.setCellValue(temp);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
public void make(File source, DataSet header, DataSet master) throws Exception {
this.header = header;
this.master = master;
if (header == null || master == null) throw new Exception("Dataset is empty");
long t = System.currentTimeMillis();
InputStream inp = new FileInputStream(source);
Workbook oldBook = WorkbookFactory.create(inp);
Sheet oldSheet = oldBook.getSheetAt(0);
Workbook newBook = new HSSFWorkbook();
Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());
init(newBook);
process(oldSheet, newSheet);
File target = File.createTempFile("libra", ".xls");
target.deleteOnExit();
FileOutputStream fileOut = new FileOutputStream(target);
newBook.write(fileOut);
fileOut.close();
oldBook.close();
inp.close();
Desktop.getDesktop().open(target);
System.out.println(System.currentTimeMillis() - t);
}
public List<ImportNeolixVO> extractNeolixInfo(File file) {
List<ImportNeolixVO> neolix = new LinkedList<>();
// 解析文件
Workbook workBook = null;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e) {
throw new RuntimeException("没有找到对应的文件", e);
}
try {
workBook = WorkbookFactory.create(fis);
Sheet sheet = workBook.getSheetAt(0);
int lastRowNumber = sheet.getLastRowNum();
Row rowTitle = sheet.getRow(0);
if (rowTitle == null) {
neolix = null;
return neolix;
}
// 从第1行开始（不算标题）
for (int i = 1; i <= lastRowNumber; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
ImportNeolixVO vo = convert(row);
neolix.add(vo);
}
} catch (Exception e) {
System.out.println(e.toString());
neolix = null;
}
return neolix;
}
private static Sheet loadWorksheet(String filepath)
throws InvalidFormatException, FileNotFoundException, IOException, ParseException {
// open the excel file containing the interview
FileInputStream file = new FileInputStream(new File(filepath));
// have the WorkbookFactory interpret the file and an xlsx file
Workbook workbook = WorkbookFactory.create(file);
// retrieve the first sheet from the workbook
Sheet sheet = workbook.getSheetAt(0);
file.close();
return sheet;
}
public void testRead() throws IOException, InvalidFormatException {
InputStream inputXLS =
new BufferedInputStream(getClass().getResourceAsStream(departmentDataXLS));
Workbook hssfInputWorkbook = WorkbookFactory.create(inputXLS);
Sheet sheet = hssfInputWorkbook.getSheetAt(0);
List mappings = new ArrayList();
Department department = new Department();
Map beans = new HashMap();
beans.put("department", department);
mappings.add(new BeanCellMapping(7, (short) 0, "employee", "name"));
mappings.add(new BeanCellMapping(7, (short) 1, "employee", "age"));
mappings.add(new BeanCellMapping(7, (short) 3, "employee", "payment"));
mappings.add(new BeanCellMapping(7, (short) 4, "employee", "bonus"));
XLSBlockReader reader = new SimpleBlockReaderImpl(7, 7, mappings);
XLSRowCursor cursor = new XLSRowCursorImpl(sheet);
XLSLoopBlockReader forEachReader =
new XLSForEachBlockReaderImpl(7, 7, "department.staff", "employee", Employee.class);
forEachReader.addBlockReader(reader);
SectionCheck loopBreakCheck = getLoopBreakCheck();
forEachReader.setLoopBreakCondition(loopBreakCheck);
cursor.setCurrentRowNum(7);
forEachReader.read(cursor, beans);
assertEquals(4, department.getStaff().size());
Employee employee = (Employee) department.getStaff().get(0);
checkEmployee(employee, "Oleg", new Integer(32), new Double(2000.0), new Double(0.20));
employee = (Employee) department.getStaff().get(1);
checkEmployee(employee, "Yuri", new Integer(29), new Double(1800.0), new Double(0.15));
employee = (Employee) department.getStaff().get(2);
checkEmployee(employee, "Leonid", new Integer(30), new Double(1700.0), new Double(0.20));
employee = (Employee) department.getStaff().get(3);
checkEmployee(employee, "Alex", new Integer(28), new Double(1600.0), new Double(0.20));
}
public void loadData(Data data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream file = new FileInputStream(new File(path));
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
try {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList<String> arrayRow = new ArrayList<String>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
testDataType(cell, arrayRow);
}
data.getData().add(arrayRow);
}
} catch (Exception e) {
e.printStackTrace();
}
workbook.close();
file.close();
sheet = null;
workbook = null;
file = null;
}
public Sheet getSheet(String fileName) throws IOException, InvalidFormatException {
InputStream inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
return sheet;
}
@Override
protected Workbook indexExcel(Map<String, String> params, Workbook excel) {
List<Map<String, Object>> list = service.financialIndexData_02(params);
Sheet sheet = excel.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellValue(params.get("statMonth") + reportName);
row = sheet.getRow(2);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
sheet = PoiUtil.copyLastRow(sheet, false);
row = sheet.getRow(sheet.getLastRowNum() - 1);
row.getCell(0).setCellValue(map.get("ENT_NAME").toString());
row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1")));
row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1")));
row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2")));
row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2")));
row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3")));
row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3")));
}
}
sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
return excel;
}
@Test
public void overrideRowsInTemplate() throws IOException {
XSSFWorkbook template = new XSSFWorkbook();
template.createSheet().createRow(1);
Workbook wb = new SXSSFWorkbook(template);
try {
Sheet sheet = wb.getSheetAt(0);
try {
sheet.createRow(1);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[1] in the range [0,1] that is already written to disk.",
e.getMessage());
}
try {
sheet.createRow(0);
fail("expected exception");
} catch (Throwable e) {
assertEquals(
"Attempting to write a row[0] in the range [0,1] that is already written to disk.",
e.getMessage());
}
sheet.createRow(2);
} finally {
wb.close();
template.close();
}
}
/**
* 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);
}
/**
* @param excelType
* @param inputStream
* @param clazz
* @param processor
* @param listeners
* @return
*/
public <T extends PoiBaseBo> ExcelImportResult importExcelNoValid(
ExcelType excelType,
InputStream inputStream,
Class<T> clazz,
BatchProcessor<T> processor,
List<ExcelImportListener> listeners) {
visitorStart(listeners, clazz);
ValidateMeta validateMeta = getClassValiateMeta(clazz);
Workbook workbook = getTempleteWorkBook(excelType, inputStream);
int sheetAt = PoiAnnotationResolver.getSheetAt(clazz);
Sheet sheet = workbook.getSheetAt(sheetAt);
int rowStart = PoiAnnotationResolver.getRowStart(clazz);
int columnStart = PoiAnnotationResolver.getColumnStart(clazz);
int rowEnd = PoiAnnotationResolver.getRowEnd(clazz);
int columnEnd = PoiAnnotationResolver.getColumnEnd(clazz);
rowEnd = rowEnd == -1 ? sheet.getLastRowNum() : rowEnd;
columnEnd = columnEnd == -1 ? Integer.MAX_VALUE : columnEnd;
ExcelImportResult processValidateResults = new ExcelImportResult(super.getFileRepository());
int recordCount = rowEnd - rowStart + 1;
int realCount = recordCount;
int batchCount = (int) Math.ceil((double) realCount / super.getBatchSize());
if (batchCount == 1) {
super.setBatchSize(realCount);
}
this.setCountDownLatch(new CountDownLatch(batchCount));
List<ValidateResult<T>> batchValidates = new ArrayList<ValidateResult<T>>();
for (int i = 0; i < batchCount; i++) {
int batchStart = (i) * super.getBatchSize() + rowStart;
int batchEnd = (i + 1) * super.getBatchSize() + rowStart - 1;
if (batchEnd - rowStart + 1 > realCount) {
batchEnd = realCount + rowStart - 1;
}
List<T> batchRecord = batchRecord(columnStart, columnEnd, batchStart, batchEnd, sheet, clazz);
List<ValidateResult<T>> batchValidate =
batchValidate(
validateMeta,
columnStart,
columnEnd,
batchStart,
batchEnd,
sheet,
clazz,
batchRecord,
0);
batchValidates.addAll(batchValidate);
int batchEndFlag = (i + 1) * super.getBatchSize();
if (batchEndFlag > recordCount) {
batchEndFlag = recordCount;
}
;
visitorImporting(listeners, (i) * super.getBatchSize() + 1, batchEndFlag, recordCount, clazz);
super.getRecordProcessor().processRecords(batchValidate, processor);
}
processValidateResults.merge(
processValidateResults(excelType, sheet, batchValidates, processor));
visitorEnd(listeners, clazz);
return processValidateResults;
}
private org.apache.poi.ss.usermodel.Row getRowForCoordinate(
RowIndex rowIndex, SheetIndex sheetIndex) {
Sheet sheet = workbook.getSheetAt(sheetIndex.value());
org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
if (row == null) row = sheet.createRow(rowIndex.value());
return row;
}
public static void generateTrainingDataFromFile(
String
fileLocation) // Requires that the original file had the metadata and requires that this
// file is formated the same in first sheet
{
testDataLL = (LinkedList<String[]>) dataLL.clone();
actualClassifications = (LinkedList<String>) classificationsLL.clone();
FileInputStream file;
try {
file = new FileInputStream(new File(fileLocation));
Workbook excelFile = new HSSFWorkbook(file);
Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
for (Row row : sheet1) {
String data[] = new String[row.getPhysicalNumberOfCells() - 1];
String classification = "";
int offset =
0; // Used so that we can declare an array of the size of the attributes without the
// classification
for (Cell cell : row) {
int index = cell.getColumnIndex();
if (classificationLocation != index) {
data[index - offset] = cell.toString();
} else {
classification = cell.toString();
offset++;
}
}
// Even though data and classifications are not really used add it onto the end so it is
// still complete for in the event they end up being used in a later version
dataLL.add(data);
classificationsLL.add(classification);
trainingDataLL.add(data);
knownClassifications.add(classification);
// Check to see if we have seen that classification yet
int occurrences = 0;
for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
if (classificationTypes.get(i).compareTo(classification) == 0) {
occurrences = 1;
}
}
if (occurrences == 0) {
classificationTypes.add(classification);
}
}
excelFile.close();
} catch (FileNotFoundException e) {
System.out.println("Error file not found");
System.exit(0);
} catch (IOException e) {
System.out.println("Unable to read file, disk drive may be failing");
e.printStackTrace();
System.exit(0);
}
}
public void atualizar(Cliente cliente) {
String cpf = cliente.getCpf();
int count = 1;
boolean achou = false;
int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
while (count < ultimaLinha && !achou) {
if (wb.getSheetAt(0).getRow(count).getCell(0).equals(cpf)) {
Row selectedRow = wb.getSheetAt(0).getRow(count);
wb.getSheetAt(0).removeRow(selectedRow);
this.createRow(selectedRow, cliente);
} else {
count++;
}
}
}
