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 void generateEnforcementsRegion(Workbook wb, StringBuilder sb) {
// Get the Enforcements Sheet
Sheet sheet = wb.getSheet("Enforcements");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
int id = (int) cellId.getNumericCellValue();
Cell cellName = row.getCell(1);
String name = cellName.getStringCellValue().replaceAll("\"", "'");
Cell cellDescription = row.getCell(2);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellType = row.getCell(3);
String type = cellType.getStringCellValue();
sb.append("Enforcement En" + id + " {");
sb.append("\n");
sb.append("\tName \"" + name + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
sb.append("\tType " + type);
sb.append("\n}");
sb.append("\n\n");
} else break;
}
}
@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();
}
}
}
/**
* 获取销售记录
*
* @param productSheet
* @return
*/
private int getSaleRecordsFormXSheet(Sheet salesSheet) {
int count = 0;
if (salesSheet == null) return 0;
try {
int rowStart = salesSheet.getFirstRowNum();
int rowEnd = salesSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = salesSheet.getRow(i);
SaleRecord sr = new SaleRecord();
// 日期
String datestr = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(datestr)) continue;
Date date = null;
try {
date = new SimpleDateFormat("yyyy-MM-dd").parse(datestr);
} catch (Exception e) {
continue;
}
String struser = String.valueOf(getXssCellData(row.getCell(1)));
// if have not user info discard this record
if ("".equals(struser)) continue;
int user = Integer.parseInt(struser);
String strproduct = String.valueOf(getXssCellData(row.getCell(2)));
// if have not user info discard this record
if ("".equals(strproduct)) continue;
int product = Integer.parseInt(strproduct);
// 购买价格
String pricestr = (String) getXssCellData(row.getCell(3));
double price = 0d;
if (!"".equals(pricestr)) {
price = Double.parseDouble(pricestr);
}
// 购买量
String numstr = (String) getXssCellData(row.getCell(3));
double num = 0d;
if (!"".equals(numstr)) {
num = Double.parseDouble(numstr);
}
sr.setNum(num);
sr.setPrice(price);
sr.setProduct(product);
sr.setUser(user);
sr.setSaledate(date);
simpleDao.createEntity(sr);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getSaleRecordsFormXSheet exception !");
}
return count;
}
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();
}
}
@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;
}
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));
}
}
public static void copySheet(Sheet sheet, Sheet newSheet) {
int maxCol = 0;
for (int row = 0; row <= sheet.getLastRowNum(); row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = newSheet.getRow(row);
if (newRow == null) newRow = newSheet.createRow(row);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
for (int col = 0; col < oldRow.getLastCellNum(); col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col);
if (newCell == null) newCell = newRow.createCell(col);
copyCell(oldCell, newCell, true);
}
}
for (int col = 0; col <= maxCol; col++) {
if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
}
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress cra = sheet.getMergedRegion(i);
newSheet.addMergedRegion(cra);
}
}
@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 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();
}
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);
}
}
/** 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;
}
@Test
public void linksTest() throws IOException {
FileInputStream f =
new FileInputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\excelfiles\\datadrivenexcel.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(f);
XSSFSheet ws = wb.getSheet("sheet1");
Iterator<Row> row = ws.iterator();
row.next();
while (row.hasNext()) {
Row r = row.next();
String linkname = r.getCell(0).getStringCellValue();
try {
driver.findElement(By.linkText(linkname)).click();
String acturl = driver.getCurrentUrl();
r.createCell(2).setCellValue(acturl);
String expurl = r.getCell(1).getStringCellValue();
if (acturl.equals(expurl)) {
r.createCell(3).setCellValue("passsed");
} else {
r.createCell(3).setCellValue("failed");
}
driver.navigate().back();
} catch (Exception e) {
r.createCell(3).setCellValue("links not found");
}
}
FileOutputStream f1 =
new FileOutputStream(
"C:\\Users\\RAHUL\\Downloads\\eclipse-jee-mars-R-win32 (1)\\eclipse\\rahu new\\seleniumprograms\\src\\com\\rahul\\resultsexcelfiles\\datadrivenexcel.xlsxlinks.xlsx");
wb.write(f1);
f1.close();
driver.quit();
}
public String getCellContent(Sheet sheet, int rowNum, int colNum) {
Row row = sheet.getRow(rowNum);
String contents = "";
if (row != null && row.getCell(colNum) != null) {
contents = getCellContent(row.getCell(colNum)).trim();
}
return contents;
}
private void generateServicesRegion(Workbook wb, StringBuilder sb) {
// Get the Services Sheet
Sheet sheet = wb.getSheet("Services");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
int id = (int) cellId.getNumericCellValue();
Cell cellName = row.getCell(1);
String name = cellName.getStringCellValue().replaceAll("\"", "'");
Cell cellDescription = row.getCell(2);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellPrivateData = row.getCell(3);
Cell cellPartOf = row.getCell(4);
sb.append("Service S" + id + " {");
sb.append("\n");
sb.append("\tName \"" + name + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int privateData = (int) cellPrivateData.getNumericCellValue();
sb.append("\tRefersTo PrivateData PD" + privateData);
sb.append("\n");
} else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) {
String privateData = cellPrivateData.getStringCellValue();
if (privateData.equals("All")) {
sb.append("\tRefersTo PrivateData All");
} else {
sb.append("\tRefersTo PrivateData ");
for (String s : privateData.split(", ")) {
sb.append("PD" + s + ",");
}
// Delete last ','
sb.deleteCharAt(sb.length() - 1);
}
sb.append("\n");
}
if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int partOf = (int) cellPartOf.getNumericCellValue();
sb.append("\tService_Part S" + partOf);
sb.append("\n");
}
sb.append("}");
sb.append("\n\n");
} else break;
}
}
private void generateRecipientsRegion(Workbook wb, StringBuilder sb) {
// Get the Recipients Sheet
Sheet sheet = wb.getSheet("Recipients");
Iterator<Row> rowIt = sheet.rowIterator();
// Ignore the Header row
rowIt.next();
while (rowIt.hasNext()) {
Row row = rowIt.next();
Cell cellId = row.getCell(0);
if (cellId != null) {
if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int id = (int) cellId.getNumericCellValue();
Cell cellDescription = row.getCell(1);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellScope = row.getCell(2);
String scope = cellScope.getStringCellValue();
if (scope.contains("/")) {
scope = "Internal/External";
} else {
scope = scope.substring(0, 1).toUpperCase() + scope.substring(1);
}
Cell cellType = row.getCell(3);
String type = cellType.getStringCellValue();
if (type.contains("/")) {
type = "Individual/Organization";
} else {
type = type.substring(0, 1).toUpperCase() + type.substring(1);
}
Cell cellPartOf = row.getCell(4);
sb.append("Recipient R" + id + " {");
sb.append("\n");
sb.append("\tName \"" + description + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
int partOf = (int) cellPartOf.getNumericCellValue();
sb.append("\tRecipient_Part R" + partOf);
sb.append("\n");
}
sb.append("\tScope " + scope);
sb.append("\n");
sb.append("\tType " + type);
sb.append("\n}");
sb.append("\n\n");
}
} else break;
}
}
private Cliente createCliente(Row posicao) {
return new Cliente(
posicao.getCell(6).getStringCellValue(),
posicao.getCell(2).getStringCellValue(),
posicao.getCell(0).getStringCellValue(),
posicao.getCell(1).getStringCellValue(),
posicao.getCell(3).getStringCellValue(),
new Date(posicao.getCell(4).getStringCellValue()),
posicao.getCell(5).getStringCellValue());
}
/**
* @param header
* @param codateRow
*/
private void populateStartDate(ProgramHeaderTO header, Row codateRow) {
if (codateRow.getCell(ProgramCols.EVENT_DATE.getCell() + 1) != null) {
String string = codateRow.getCell(ProgramCols.EVENT_DATE.getCell() + 1).toString();
SimpleDateFormat format = new SimpleDateFormat(DD_MMM_YYYY);
try {
Date date = format.parse(string);
header.setProgramStartDate(date);
} catch (ParseException e) {
header.setProgramRawStartDate(string);
}
}
}
public static String setOd() throws Exception {
String od = Dater.getUnique();
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(ShippingRequestValueContainerCola.file_path));
HSSFWorkbook wb = new HSSFWorkbook(fs);
Sheet sheet1 = wb.getSheetAt(0);
Row row1 = sheet1.getRow(start);
row1.getCell(clmnod).setCellValue(od);
row1.getCell(clmncomment).setCellValue(od);
wb.write(new FileOutputStream(ShippingRequestValueContainerCola.file_path));
return od;
}
public int getTotalLength(String sheetName) {
ArrayList<Row> rows = getRows(sheetName);
int totalLength = 0;
for (Row row : rows) {
if (!row.getCell(0).getStringCellValue().equals("")) {
totalLength += row.getCell(1).getNumericCellValue();
} else {
break;
}
}
return totalLength;
}
private String validateRow(Row row) {
for (int i = 0; i < NUM_COLS; i++) {
Cell cell = row.getCell(i);
if (cell == null) {
if (REQUIRED[i]) {
return "Required cell "
+ HEADER_TITLES[i]
+ " missing for row "
+ String.valueOf(row.getRowNum());
}
} else {
if (i == DECLARED_LICENSE_COL || i == CONCLUDED_LICENSE_COL) {
try {
SPDXLicenseInfoFactory.parseSPDXLicenseString(cell.getStringCellValue());
} catch (SpreadsheetException ex) {
if (i == DECLARED_LICENSE_COL) {
return "Invalid declared license in row "
+ String.valueOf(row.getRowNum())
+ " detail: "
+ ex.getMessage();
} else {
return "Invalid seen license in row "
+ String.valueOf(row.getRowNum())
+ " detail: "
+ ex.getMessage();
}
}
} else if (i == LICENSE_INFO_IN_FILES_COL) {
String[] licenses =
row.getCell(LICENSE_INFO_IN_FILES_COL).getStringCellValue().split(",");
if (licenses.length < 1) {
return "Missing licenss information in files";
}
for (int j = 0; j < licenses.length; j++) {
try {
SPDXLicenseInfoFactory.parseSPDXLicenseString(cell.getStringCellValue().trim());
} catch (SpreadsheetException ex) {
return "Invalid license information in row "
+ String.valueOf(row.getRowNum())
+ " detail: "
+ ex.getMessage();
}
}
}
// if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
// return "Invalid cell format for "+HEADER_TITLES[i]+" for forw
// "+String.valueOf(row.getRowNum());
// }
}
}
return null;
}
public static void setDateLoadUnload(String dateLoad, String dateUnload) throws Exception {
try {
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(ShippingRequestValueContainerCola.file_path));
HSSFWorkbook wb = new HSSFWorkbook(fs);
Sheet sheet1 = wb.getSheetAt(0);
Row row1 = sheet1.getRow(start);
row1.getCell(clmndateLoad).setCellValue(dateLoad);
row1.getCell(clmndateUnload).setCellValue(dateUnload);
wb.write(new FileOutputStream(ShippingRequestValueContainerCola.file_path));
} catch (IOException e) {
System.out.println("setDateLoadUnload");
}
}
/**
* read excel Xls and add the result into arraylist.
*
* @param sheetName excel sheet name
* @throws RuntimeException
*/
public List<Map<String, String>> excelToList(String sheetName) {
Row firstxlRow = null;
FileInputStream fso = null;
List<Map<String, String>> paraList = new ArrayList<Map<String, 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!");
return null;
}
firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
int firstCell = firstxlRow.getFirstCellNum();
int lastCell = firstxlRow.getLastCellNum();
List<String> keyList = new ArrayList<String>();
for (int cNum = firstCell; cNum < lastCell; cNum++) {
if (firstxlRow.getCell(cNum).toString() == null) {
break;
}
keyList.add(firstxlRow.getCell(cNum).toString());
}
for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
xlRow = xlSheet.getRow(i);
List<String> valueList = new ArrayList<String>();
if (xlRow == null) {
break;
}
for (int j = firstCell; j < lastCell; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
valueList.add(null);
continue;
} else {
valueList.add(xlCell.toString());
}
}
paraList.add(creatMap(keyList, valueList));
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
private int getUserFormXSheet(Sheet userSheet) {
int count = 0;
if (userSheet == null) return 0;
try {
int rowStart = userSheet.getFirstRowNum();
int rowEnd = userSheet.getLastRowNum();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = userSheet.getRow(i);
User user = new User();
String strid = String.valueOf(getXssCellData(row.getCell(0)));
if ("".equals(strid)) continue;
int id = Integer.parseInt(strid);
String name = (String) getXssCellData(row.getCell(1));
String address = (String) getXssCellData(row.getCell(2));
String str_borthday = getXssCellData(row.getCell(3)).toString();
Date borthday = null;
if (!"".equals(str_borthday)) {
try {
borthday = new SimpleDateFormat("yyyy-MM-dd").parse(str_borthday);
} catch (Exception e) {
}
}
String gender = (String) getXssCellData(row.getCell(4));
user.setId(id);
user.setName(name);
user.setAddress(address);
user.setBirthday(borthday);
if ("男".equals(gender)) {
user.setGender("男");
}
simpleDao.createEntity(user);
count++;
}
} catch (Exception e) {
e.printStackTrace();
log.error("getUserFormXSheet exception !");
}
return count;
}
/**
* 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;
}
private Cell getCell(int rowNums, Integer index) {
Row row = this.sheet.getRow(rowNums);
if (row == null) {
return null;
}
return row.getCell(index);
}
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.
}
}
}
}
}
@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();
}
/**
* 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;
}