private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
private void loadData() {
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
if (i % 2 == 0) {
Row rowID = sheet.getRow(i);
Row rowName = sheet.getRow(i + 1);
for (int j = 0; j < 15; j++) {
Cell cellID = rowID.getCell(j);
Cell cellName = rowName.getCell(j);
String name = WorkBookUtil.getCellValue(cellName);
String cellValue = WorkBookUtil.getCellValue(cellID);
if (name == null || name.equalsIgnoreCase("")) {
continue;
}
int parseInt = Integer.parseInt(cellValue);
if (catalogUsers.containsValue(name)) {
repeatUsers.put(parseInt, name);
}
if (cellName.getCellStyle().getFillPattern() == CellStyle.SOLID_FOREGROUND) {
colorUsers.put(parseInt, name);
}
catalogUsers.put(parseInt, name);
}
}
}
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
public void parseExcelData(String filepath)
throws InvalidFormatException, IOException, ParseException {
Sheet sheet = loadWorksheet(filepath);
Iterator<Row> row_iter = sheet.iterator();
// Check if first row is a header row
if (row_iter.hasNext()) {
Row header_row = row_iter.next();
// if the first cell is numeric,
// there is no header row, reset the iterator
if (header_row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC)
row_iter = sheet.iterator();
}
while (row_iter.hasNext()) {
InterviewInstance current_instance = getInstance(row_iter.next());
// if no interview is present, start one
if (interviews.isEmpty()) interviews.add(new Interview(current_instance));
// add to the current interview instance in the list
// make a new interview if the interview ID doesn't match up
if (!interviews.get(interviews.size() - 1).add(current_instance))
interviews.add(new Interview(current_instance));
}
}
@Override
public List<Map<String, Object>> parse() throws ReadCensusException {
XSSFWorkbook listaVotantes = null;
String fileName = Paths.get(filePath).getFileName().toString();
try {
FileInputStream file = new FileInputStream(new File(filePath));
listaVotantes = new XSSFWorkbook(file);
XSSFSheet hoja = listaVotantes.getSheetAt(0);
Iterator<Row> rowIterator = hoja.iterator();
Row row;
while (rowIterator.hasNext()) {
row = rowIterator.next();
Iterator<Cell> columns = row.cellIterator();
if (!columns.hasNext()) continue;
try {
voter = new HashMap<String, Object>();
voter.put("name", columns.next().getStringCellValue());
voter.put("email", columns.next().getStringCellValue());
voter.put("nif", columns.next().getStringCellValue());
voter.put("code", String.valueOf((int) columns.next().getNumericCellValue()));
voter.put("password", PasswordGenerator.generate(8));
voter.put("file", fileName);
voter.put("line", row.getRowNum());
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] ["
+ fileName
+ ":"
+ row.getRowNum()
+ "] El usuario no tiene el formato correcto");
}
voters.add(voter);
}
} catch (FileNotFoundException e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] El fichero no existe");
} catch (Exception e) {
throw new ReadCensusException(
"[ERROR] [" + fileName + "] Fallo inesperado al leer el fichero: " + e.getMessage());
} finally {
try {
if (listaVotantes != null) listaVotantes.close();
} catch (Exception e) {
throw new ReadCensusException("[ERROR] [" + fileName + "] I/O Error: " + e.getMessage());
}
}
if (voters.isEmpty()) {
throw new ReadCensusException("[AVISO] [" + fileName + "] El censo está vacío");
}
return voters;
}
public void loadBeachLocations() {
try {
FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls"));
workbook = new HSSFWorkbook(file);
Sheet sheet = workbook.getSheet("BEACH SUMMARY");
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // Skip the header row.
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String code = row.getCell(1).getStringCellValue().trim();
System.out.println(code);
if (!code.isEmpty()) {
String beachname = row.getCell(1).getStringCellValue().trim();
String county = row.getCell(2).getStringCellValue().trim();
String description = row.getCell(1).getStringCellValue().trim() + "Beach";
System.out.println(beachname + ":" + county + ":" + description);
Beach beach = new Beach(beachname, description, county);
beach.saveBeach();
}
}
JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations....");
} catch (IOException ex) {
ex.printStackTrace();
Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex);
}
}
@Test
public void dataDrivernTest() throws IOException {
FileInputStream f =
new FileInputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\excelfiles\\registration.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("Sheet1");
Iterator<Row> row = ws.iterator();
row.next();
WelcomeMercuryTours wm = PageFactory.initElements(driver, WelcomeMercuryTours.class);
RegisterMercuryTours1 rm1 = PageFactory.initElements(driver, RegisterMercuryTours1.class);
RegisterMercuryTours2 rm2 = PageFactory.initElements(driver, RegisterMercuryTours2.class);
wm.menu.register();
while (row.hasNext()) {
Row r = row.next();
rm1.contactInformation(r);
boolean result = rm2.validateRegistration(r.getCell(9).getStringCellValue());
if (result == true) {
r.createCell(12).setCellValue("Passed");
} else {
r.createCell(12).setCellValue("Failed");
}
driver.navigate().back();
}
FileOutputStream f1 =
new FileOutputStream(
"E:\\July16Batch\\WebdriverProject\\src\\com\\qedge\\resultexcelfiles\\registration.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
/**
* get the specified excel sheet and put its value string to list.
*
* @param sheetName excel sheet name
* @param ignoreRows first several rows not to read.
* @param ignoreCols first several cols not to read.
* @param readRows specified row count to read.
* @param readColumns specified column count to read.
* @throws RuntimeException
*/
public List<String> excelToList(
String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
FileInputStream fso = null;
List<String> paraList = new ArrayList<String>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
}
readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
xlRow = xlSheet.getRow(i);
readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
if (xlRow != null) {
for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
paraList.add("");
} else {
paraList.add(xlCell.toString());
}
}
}
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
/** 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;
}
/**
* Forms report in excel file. Sum weight. Sum volume etc.
*
* @param sheet Sheet where write the items
* @param c Container from where take the info
* @param lastRow last row after all items were written method uses lastRow + 2 to write report in
* next 2 rows after all items of the container were written
*/
private void setReport(Container c, int lastRow) {
String[] reportHeadings = {
"Суммарный вес", // 6
"Суммарный объем", // 7
"Остаток вес", // 8
"Остаток объем" // 9
};
double[] values = {
c.getWeight(),
c.getVolume(),
c.getWeightLimit() - c.getWeight(),
c.getVolumeLimit() - c.getVolume()
};
int dataCell = 5;
int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
lastRow++;
Row headings = outputSheet.createRow(lastRow);
lastRow += 2;
Row data = outputSheet.createRow(lastRow);
for (String report : reportHeadings) {
Cell heading = headings.createCell(dataCell);
heading.setCellValue(report);
Cell dataCellValue = data.createCell(dataCell);
dataCellValue.setCellValue(values[valuesIndex]);
dataCell++;
valuesIndex++;
}
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
private static void createRow(
Sheet sheet,
Map<String, Object> rowData,
List<String> mapping,
List<CellStyle> styles,
int startRowNum) {
Row row = sheet.createRow(startRowNum);
for (int i = 0; i < mapping.size(); i++) {
String name = mapping.get(i);
Object obj = rowData.get(name);
Cell newCell = row.createCell(i);
CellStyle style = styles.get(i);
newCell.setCellStyle(style);
if (obj != null) {
if (obj instanceof Date) {
newCell.setCellValue((Date) obj);
} else if (obj instanceof BigDecimal) {
double dd = ((BigDecimal) obj).doubleValue();
newCell.setCellValue(dd);
} else {
newCell.setCellValue(obj.toString());
}
}
}
}
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 List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Specialite> listSpecialite = new ArrayList<>();
FileInputStream file = new FileInputStream(new File(excelFilePath));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Specialite sp = new Specialite();
Admin admin = new Admin();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 0:
sp.setIntitule((String) getCellValue(nextCell));
break;
}
}
listSpecialite.add(sp);
}
file.close();
return listSpecialite;
}
@Override
public List<String> getHeaderColumns() {
if (headerColumns == null) {
headerColumns = new ArrayList<>();
Row row = dataSheet.getRow(0);
int colCount = row != null ? row.getLastCellNum() : 0;
if (row == null || colCount == 0) {
LogMgr.logError(
"ExcelReader.getHeaderColumns()",
"Cannot retrieve column names because no data is available in the first row of the sheet: "
+ dataSheet.getSheetName(),
null);
String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
messages.append(msg);
messages.appendNewLine();
return headerColumns;
}
for (int i = 0; i < colCount; i++) {
Cell cell = row.getCell(i);
Object value = getCellValue(cell);
if (value != null) {
headerColumns.add(value.toString());
} else {
headerColumns.add("Col" + Integer.toString(i));
}
}
}
return headerColumns;
}
public void writeData(Data data, boolean onlyNumbers) {
try {
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
FileOutputStream fOutputStream = new FileOutputStream(file);
org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
@SuppressWarnings("rawtypes")
ArrayList<ArrayList> dataList = data.getData();
for (int i = 0; i < dataList.size(); i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j);
if (onlyNumbers) {
row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
} else {
row.getCell(j).setCellValue((String) dataList.get(i).get(j));
}
}
}
workbook.write(fOutputStream);
workbook.close();
fOutputStream.close();
workbook = null;
file = null;
sheet = null;
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
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);
}
}
/**
* Compute width of a column based on a subset of the rows and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @param firstRow 0-based index of the first row to consider (inclusive)
* @param lastRow 0-based index of the last row to consider (inclusive)
* @return the width in pixels
*/
public static double getColumnWidth(
Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
AttributedString str;
TextLayout layout;
Workbook wb = sheet.getWorkbook();
DataFormatter formatter = new DataFormatter();
Font defaultFont = wb.getFontAt((short) 0);
str = new AttributedString(String.valueOf(defaultChar));
copyAttributes(defaultFont, str, 0, 1);
layout = new TextLayout(str.getIterator(), fontRenderContext);
int defaultCharWidth = (int) layout.getAdvance();
double width = -1;
for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
Row row = sheet.getRow(rowIdx);
if (row != null) {
Cell cell = row.getCell(column);
if (cell == null) {
continue;
}
double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
width = Math.max(width, cellWidth);
}
}
return width;
}
@Override
public void importTower(Sheet sheet) {
boolean isFirst = Boolean.TRUE;
for(Iterator<Row> it = sheet.rowIterator(); it.hasNext(); ) {
try {
Row row = it.next();
if(isFirst) {
isFirst = Boolean.FALSE;
continue;
}
Manufacturer manufacturer = manufacturerService.findOrCreateByNameAndCategory(
row.getCell(MANUFACTURER).getStringCellValue().trim(),
CategoryEnum.TOWER
);
Tower tower = new Tower(manufacturer);
tower.setName(row.getCell(NAME).getStringCellValue().trim());
tower.setPrice(BigDecimal.valueOf(row.getCell(PRICE).getNumericCellValue()));
tower.setWatts(String.valueOf(row.getCell(WATTS).getNumericCellValue()));
tower.setCode("0000000000");
tower = this.save(tower);
System.out.println(tower.toString());
} catch (ServiceException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
private static void retrieveSheetInformation(Sheet sheet) {
// every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Row row = rowIterator.next();
// Every row has columns, get the column iterator and
// iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
StringBuffer sb = new StringBuffer();
while (cellIterator.hasNext()) {
// Get the Cell object
Cell cell = cellIterator.next();
// check the cell type and process accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
sb.append(cell.getStringCellValue()).append(" $ ");
break;
case Cell.CELL_TYPE_NUMERIC:
sb.append(cell.getNumericCellValue()).append(" ");
}
} // end of cell iterator
System.out.println(sb.toString());
} // end of rows iterator
}
public void parseData(
final Sheet sheet,
final int startRow,
final int endColumn,
final List<String> propertyNames,
final Class clazzOfTestCase) {
int rowCounter = startRow;
while (!isBlank(sheet, rowCounter, endColumn)) {
Row row = sheet.getRow(rowCounter - 1);
for (int i = 0; i < endColumn; i++) {
Cell cell = row.getCell(i);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
try {
Method method = clazzOfTestCase.getMethod("set" + propertyNames.get(i), Object.class);
} catch (NoSuchMethodException e) {
e
.printStackTrace(); // To change body of catch statement use File | Settings | File
// Templates.
}
}
}
}
}
public void parse() throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Row row;
Sheet sheet = wb.getSheet(sheetName);
int startingRow = 0;
boolean breakNow = false;
for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
if (breakNow) {
break;
}
row = sheet.getRow(i);
if (row == null) {
break;
}
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j).getStringCellValue().isEmpty()
|| row.getCell(j).getStringCellValue() == null) {
breakNow = true;
break;
}
// category.setCategoryName(row.getCell(j).getStringCellValue());
}
}
inp.close();
}
private Cell getCell(int rowNums, Integer index) {
Row row = this.sheet.getRow(rowNums);
if (row == null) {
return null;
}
return row.getCell(index);
}
@Override
public List<SeekerAimsTO> buildParticipants() throws Exception {
boolean mark = false;
List<Row> participantRows = new ArrayList<Row>();
for (int i = 0; i < sheetParticipants.getLastRowNum(); i++) {
Row row = sheetParticipants.getRow(i);
if (mark) {
if (row != null) {
participantRows.add(row);
}
}
if (row != null && row.getCell(ParticipantCols.NAME.getColumn()) != null) {
String string = row.getCell(ParticipantCols.NAME.getColumn()).toString();
if (string.contains(ParticipantCols.NAME.getHeader())) {
mark = true;
}
if (mark) {
if (row == null
|| (row.getCell(ParticipantCols.NAME.getColumn()) == null)
|| row.getCell(ParticipantCols.NAME.getColumn()).toString().isEmpty()) {
mark = false;
}
}
}
}
ProgramHeaderTO buildProgramDetails = buildProgramDetails();
List<SeekerAimsTO> processRows = processRows(participantRows);
for (SeekerAimsTO seekerAims : processRows) {
seekerAims.setCountry(buildProgramDetails.getCountry());
}
sLogger.info("Participant list:" + participantRows.size());
return processRows;
}
public static void main(String args[]) throws Exception {
File file = new File("d:/delete/createworkbook.xlsx");
FileInputStream fis = new FileInputStream(file);
if (file.isFile() && file.exists()) {
// Get the workbook instance for XLSX file
XSSFWorkbook workbook = new XSSFWorkbook(fis);
System.out.println("openworkbook.xlsx file open successfully.");
// get worksheet, 0 based
XSSFSheet sheet = workbook.getSheetAt(0);
// get rows
Iterator<Row> it = sheet.iterator();
while (it.hasNext()) {
Row row = (XSSFRow) it.next();
// get cells
Iterator<Cell> cellIt = row.cellIterator();
while (cellIt.hasNext()) {
Cell cell = cellIt.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + " \t\t ");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + " \t\t ");
break;
}
}
}
} else {
System.out.println("Error to open openworkbook.xlsx file.");
}
}
public String readCGCell(Row row, int cellIdx, DataDto dto, CellType cellType) {
Cell cell = row.getCell(cellIdx);
if (null == cell) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getConfidenceGrade()
+ " cell is null.");
return null;
}
if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
log.warn(
"Table import. Table "
+ metaData.getTableName()
+ " Cell at row: "
+ (row.getRowNum() + 1)
+ " col: "
+ (cellIdx + 1)
+ " current value: "
+ dto.getConfidenceGrade()
+ " CG cell is not of type String.");
return null;
}
RichTextString cg = cell.getRichStringCellValue();
return cg.getString();
}
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;
}
@Override
public String getPriceDate(String fileName) {
try {
HSSFSheet sheet = getSheet(fileName, 0);
String marker = "Дата";
int numOfRows = sheet.getPhysicalNumberOfRows() > 10 ? 10 : sheet.getPhysicalNumberOfRows();
String dirtyDate = null;
for (int i = priceConfig.getRowToStart(Brand.STELS); i < numOfRows; i++) {
Row row = sheet.getRow(i);
if (row.getPhysicalNumberOfCells() > 1) {
Cell cell = row.getCell(0);
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.contains(marker)) {
dirtyDate = stringCellValue;
break;
}
}
}
}
if (dirtyDate != null) {
return dirtyDate.trim();
}
} catch (Exception ex) {
LOGGER.error("FAIL to get price date - return today");
return new Date().toString();
}
return new Date().toString();
}
