// traversal cell
public void traversalCell(String filePath) {
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(filePath); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
}
// 获得Excel中工作表个数
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
// 循环每个工作表
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
Sheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
System.out.println(
"工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());
if (rows > 0) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum(); // 获得列数
for (short c = 0; c < cells; c++) { // 列循环
Cell cell = row.getCell(c);
if (cell != null) {
String value = getCellData(cell);
System.out.println("第" + r + "行 " + "第" + c + "列：" + value);
}
}
}
}
}
// 查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
System.out.println("第" + i + "个合并单元格");
CellRangeAddress region = sheet.getMergedRegion(i);
int row = region.getLastRow() - region.getFirstRow() + 1;
int col = region.getLastColumn() - region.getFirstColumn() + 1;
System.out.println("起始行:" + region.getFirstRow());
System.out.println("起始列:" + region.getFirstColumn());
System.out.println("所占行:" + row);
System.out.println("所占列:" + col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* 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;
}
@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");
}
private void processInnland(Sheet sheet) throws InvalidFormatException {
if (sheet.getPhysicalNumberOfRows() > 0) {
int lastRowNum = 0;
Row row = null;
lastRowNum = sheet.getLastRowNum();
System.out.println("Innland har " + lastRowNum + " rader");
row = sheet.getRow(0);
int lastCellNum = row.getLastCellNum();
System.out.println("Innland rad 0 har " + lastCellNum + " celler");
System.out.println("Kjører tilregnelighetssjekk");
String KO = text(row, INNLAND_KONTONR);
String LE = text(row, INNLAND_LEVNR);
String NA = text(row, INNLAND_NAVN);
String A1 = text(row, INNLAND_ADDR1);
String A2 = text(row, INNLAND_ADDR2);
String NR = text(row, INNLAND_POSTNUMMER);
String ST = text(row, INNLAND_POSTSTED);
if (KO.equals("Kontonr")
&& LE.equals("Lev.nr")
&& NA.equals("Navn")
&& A1.equals("Adresse 1")
&& A2.equals("Adresse 2")
&& NR.equals("Postnr.")
&& ST.equals("Poststed")) {
System.out.println("Første rad ser OK ut, fortsetter");
for (int j = 1; j <= lastRowNum; j++) {
System.out.println("Prosesserer rad " + j + " av " + lastRowNum);
row = sheet.getRow(j);
this.rowToXML(row, j);
}
} else {
throw new InvalidFormatException(
"Kjenner ikke igjen første rad\n"
+ "Skulle vært (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'"
+ ""
+ KO
+ "' '"
+ LE
+ "' '"
+ NA
+ "' '"
+ A1
+ "' '"
+ A2
+ "' '"
+ NR
+ "' '"
+ ST
+ "'");
}
}
}
private void checkMaxUtilization_rows(Workbook workbook) {
/*
EXAMPLE:
Hubi Helsinki X-Park Liityntä Henkilöauto Toiminnassa 50 Arkipäivä 100 %
Hubi Helsinki X-Park Liityntä Henkilöauto Toiminnassa 50 Lauantai 100 %
Hubi Helsinki X-Park Liityntä Henkilöauto Toiminnassa 50 Sunnuntai 100 %
*/
final Sheet utilization = workbook.getSheetAt(0);
assertThat(utilization.getPhysicalNumberOfRows()).isEqualTo(4);
final List<String> businessDay = getDataFromRow(utilization, 1);
assertThat(businessDay)
.containsExactly(
hub.name.fi,
"Helsinki", // The region name
operator1.name.fi,
translationService.translate(facility1.usages.first()),
translationService.translate(CAR),
translationService.translate(facility1.status),
"" + facility1.builtCapacity.get(CAR),
translationService.translate(DayType.BUSINESS_DAY),
"100%");
final List<String> saturday = getDataFromRow(utilization, 2);
assertThat(saturday)
.containsExactly(
hub.name.fi,
"Helsinki", // The region name
operator1.name.fi,
translationService.translate(facility1.usages.first()),
translationService.translate(CAR),
translationService.translate(facility1.status),
"" + facility1.builtCapacity.get(CAR),
translationService.translate(DayType.SATURDAY),
"50%");
final List<String> sunday = getDataFromRow(utilization, 3);
assertThat(sunday)
.containsExactly(
hub.name.fi,
"Helsinki", // The region name
operator1.name.fi,
translationService.translate(facility1.usages.first()),
translationService.translate(CAR),
translationService.translate(facility1.status),
"" + facility1.builtCapacity.get(CAR),
translationService.translate(DayType.SUNDAY),
"20%");
}
/**
* 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;
}
public List<String> readExcel(String zh) throws IOException {
List<String> values = new ArrayList<String>();
FileInputStream fise = new FileInputStream(excelPath);
HSSFWorkbook wb = new HSSFWorkbook(fise);
Sheet sheet3 = wb.getSheetAt(1);
Row r = null;
int linenum = 0;
Cell cell = null;
for (int i = 8; i < sheet3.getPhysicalNumberOfRows(); i++) {
r = sheet3.getRow(i);
cell = r.getCell(3);
if (cell.toString().contains(zh)) {
linenum = i;
break;
}
}
r = sheet3.getRow(linenum);
String value = null;
for (int i = 15; i <= 21; i++) {
cell = r.getCell(i);
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
HSSFDataFormatter df = new HSSFDataFormatter();
String cellf = df.formatCellValue(cell);
value = cellf;
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
value = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
value = "0";
}
values.add(value);
}
cell = r.getCell(24);
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
HSSFDataFormatter df = new HSSFDataFormatter();
String cellf = df.formatCellValue(cell);
value = cellf;
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
value = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
value = "0";
}
values.add(value);
// System.out.println(values);
return values;
}
public List<ExcelRow> readTemporalSentences() {
List<ExcelRow> returnVal = new ArrayList<>();
try {
FileInputStream fileInputStream = new FileInputStream(FILE_LOC);
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet worksheet = workbook.getSheetAt(sheet);
Row row;
ExcelRow dataRow;
for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
row = worksheet.getRow(i);
dataRow = new ExcelRow(row);
if (dataRow.getSen() == 1 && dataRow.getFinalMarked() == 1) returnVal.add(dataRow);
}
fileInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
return returnVal;
}
private void checkHubsAndFacilities_facilityInfo(Workbook workbook) {
final Sheet facilities = workbook.getSheetAt(1);
assertThat(facilities.getPhysicalNumberOfRows()).isEqualTo(2);
final List<String> facilityInfo = getDataFromRow(facilities, 1);
assertThat(facilityInfo)
.containsSequence(
facility1.name.fi,
String.join(", ", facility1.aliases),
hub.name.fi,
operator1.name.fi,
translationService.translate(facility1.status),
facility1.statusDescription.fi,
String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getX()),
String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getY()),
"",
"08:00 - 18:00",
"08:00 - 18:00",
facility1.openingHours.info.fi,
""
+ facility1
.builtCapacity
.entrySet()
.stream()
.filter(entry -> asList(motorCapacities).contains(entry.getKey()))
.mapToInt(entry -> entry.getValue())
.sum(),
""
+ facility1
.builtCapacity
.entrySet()
.stream()
.filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
.mapToInt(entry -> entry.getValue())
.sum(),
"" + facility1.builtCapacity.getOrDefault(CAR, 0),
"" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
"" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
"" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
"" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
"" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0));
}
@Test
public void report_FacilityUsage_asAdmin_oneFacility() {
final ReportParameters params = baseParams();
params.interval = 180;
params.facilities = singleton(facility1.id);
params.capacityTypes = singleton(CAR);
params.usages = singleton(Usage.PARK_AND_RIDE);
params.operators = singleton(facility1.operatorId);
registerMockFacilityUsages(facility1, apiUser);
registerMockFacilityUsages(facility2, apiUser2);
final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", adminUser);
// If this succeeds, the response was a valid excel file
final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
final Sheet usages = workbook.getSheetAt(0);
// Header + one row
assertThat(getDataFromColumn(usages, 0))
.containsExactly("Pysäköintipaikan nimi", facility1.name.fi);
assertThat(getDataFromColumn(usages, 4)).containsExactly("Käyttötapa", "Liityntä");
assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(2);
}
private void checkHubsAndFacilities_hubInfo(Workbook workbook) {
final Sheet hubs = workbook.getSheetAt(0);
assertThat(hubs.getPhysicalNumberOfRows()).isEqualTo(2);
final List<String> hubInfo = getDataFromRow(hubs, 1);
assertThat(hubInfo)
.containsExactly(
hub.name.fi,
String.join(
", ",
toArray(hub.address.streetAddress.fi, hub.address.postalCode, hub.address.city.fi)),
String.format(Locale.ENGLISH, "%.4f", hub.location.getX()),
String.format(Locale.ENGLISH, "%.4f", hub.location.getY()),
""
+ facility1
.builtCapacity
.entrySet()
.stream()
.filter(entry -> asList(motorCapacities).contains(entry.getKey()))
.mapToInt(entry -> entry.getValue())
.sum(),
""
+ facility1
.builtCapacity
.entrySet()
.stream()
.filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
.mapToInt(entry -> entry.getValue())
.sum(),
"" + facility1.builtCapacity.getOrDefault(CAR, 0),
"" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
"" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
"" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
"" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
"" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0),
facility1.name.fi);
}
public static List<Doctor> importBuildInfo(File file, HttpServletRequest request) {
try {
// 创建需要批量插入数据集合
List<Doctor> list = new ArrayList<Doctor>();
// 创建一个FileInputStream 文件输入流
FileInputStream inputStream = new FileInputStream(file);
// 创建对Excel工作簿文件的引用
Workbook wookbook = null;
String name = file.getName();
String fileType = name.substring(name.lastIndexOf(".") + 1, name.length());
if (fileType.equals("xlsx")) {
wookbook = new XSSFWorkbook(inputStream);
} else if (fileType.equals("xls")) {
wookbook = new HSSFWorkbook(inputStream);
}
// 在Excel文档中，第一张工作表的缺省索引是0
Sheet sheet = wookbook.getSheetAt(0);
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 遍历行 从第二行开始遍历
for (int i = 1; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 将对象增加到集合中
list.add(null);
}
}
// 返回集合
return list;
} catch (IOException e) {
logger.error("创建导入excel对象报错！", e);
}
return null;
}
@Test
public void report_FacilityUsage_asAdmin() {
final ReportParameters params = baseParams();
params.interval = 180;
registerMockFacilityUsages(facility1, apiUser);
registerMockFacilityUsages(facility2, apiUser2);
final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", adminUser);
// If this succeeds, the response was a valid excel file
final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
final Sheet usages = workbook.getSheetAt(0);
// Header and one for each usage type for both facilities
assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(5);
assertThat(getDataFromColumn(usages, 3))
.containsExactly(
"Operaattori",
operator1.name.fi,
operator1.name.fi,
operator2.name.fi,
operator2.name.fi);
}
@Test
// Read XL file have Executable content
public void ReadTestCase() {
boolean flag = false;
PropertyConfigurator.configure("Configuration/log.properties");
log.info("Reading Output XL file to Execute test Cases...");
// Start Reading XL file
try {
FileInputStream FSRead = new FileInputStream(OutputfilePath);
Workbook WB = new HSSFWorkbook(FSRead);
Sheet sh = WB.getSheet(sheet_name);
int rows = sh.getPhysicalNumberOfRows();
// System.out.println("No. of rows in Input XL file for Test Scenarios Sheet = "
// + rows);
int cols = sh.getRow(0).getLastCellNum();
// System.out.println("No. of columns in input file for Test Scenarios Sheet = "
// + cols);
int ExecuteTCCounter = -1;
// Iterate Rows and Read complete input XL file
for (int i = 0; i < rows; i++) {
ExecuteTCCounter++;
TestScenarios.add(new ArrayList());
// Iterate Columns
innerLoop:
for (int j = 0; j < cols; j++) {
Row current_row = sh.getRow(i);
// Debug: System.out.println("row and col value =" + i + " , " + j);
Cell blank_cell = current_row.getCell(j, current_row.CREATE_NULL_AS_BLANK);
// Debug: System.out.println("Blank cell = " + blank_cell);
int type = current_row.getCell(j).getCellType();
// Debug: System.out.println("Value of Type = " + type);
if (type == 3 && j == 0) {
ExecuteTCCounter--;
TestScenarios.remove(new ArrayList());
break innerLoop;
}
if (type == 3) {
// System.out.println("Empty Cell");
((ArrayList) TestScenarios.get(ExecuteTCCounter)).add("");
}
if (type == 1) {
String data = current_row.getCell(j).getStringCellValue();
// System.out.println("Cell Value" + " " + data + "\n");
((ArrayList) TestScenarios.get(ExecuteTCCounter)).add(data);
}
if (type == 0) {
double value = current_row.getCell(j).getNumericCellValue();
// System.out.println("Cell Value" + " " + value + "\n");
((ArrayList) TestScenarios.get(ExecuteTCCounter)).add(value);
}
type = -1;
}
}
FSRead.close();
} catch (IOException e) {
log.debug("Error in ReadExcel.java while reading XL file", e);
}
// Reading Complete Array (Stored output XL file) to execute Test Case
log.info("Start Reading Test Cases to Execute...");
// Debug:
// System.out.println("Test Scenario Size" + TestScenarios.size());
// System.out.println("Test Scenario row size" + ((ArrayList) TestScenarios.get(0)).size());
// for(int i=0; i<TestScenarios.size();i++) {
for (int i = 0; i < 1; i++) {
String newLine = System.getProperty("line.separator");
System.out.println(newLine);
log.info("Executing Test Case No." + i);
next:
for (int c = 0; c < ((ArrayList) TestScenarios.get(i)).size(); c++) {
if (c == 0 || c == 1) {
log.info("Executing " + (String) ((ArrayList) TestScenarios.get(i)).get(c));
}
if (((String) ((ArrayList) TestScenarios.get(i)).get(c)).equals("")) {
// System.out.println("Bank");
// Need to do some validation later: Check if user error and cell value is empty. (i.e.
// check next cell value).
break next;
}
if (c > 2) {
String MethodName = (String) ((ArrayList) TestScenarios.get(i)).get(c);
// System.out.println("Ash " + MethodName);
String className = "selenium.dd.FunctionalCode.ExecuteTestCase";
// Get input if type defined in XL column
if (MethodName.contains("Type")) {
// Increment to get the next cell value and set flag
c++;
flag = true;
// Checking if last column have type. This is validation for a defect cause user error
// in XL sheet
if (c == ((ArrayList) TestScenarios.get(i)).size()) {
driver.quit();
log.info("Test Case Execution finished, checking for next test case");
break next;
}
}
try {
Class cls = Class.forName(className);
Object obj = cls.newInstance();
// Defined to parameterized reflection method call
Class[] paramTypes = new Class[1];
paramTypes[0] = String.class;
// If type was defined in XL column get the next 'Input' value from XL
// And invoke method that required parameters. + Reset flag to flase.
if (flag) {
flag = false;
String Input = (String) ((ArrayList) TestScenarios.get(i)).get(c);
Method method = cls.getMethod(MethodName, paramTypes);
method.invoke(obj, Input);
} // else invoke simple reflection method (i.e. without parameter).
else {
Method method = cls.getMethod(MethodName);
method.invoke(obj);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
log.debug(e);
// e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// Checking for last column of row, so that we can close driver and close browser.
// We will re initiate driver and re-open browser for next test case
if (c == ((ArrayList) TestScenarios.get(i)).size() - 1) {
log.info("Closing Driver for " + i + " Test Case");
driver.quit();
}
} // Close inner For loop
} // Close outer For loop
}
public void readProcessSpreadSheet() {
processSpreadSheet = System.getProperty("processSpreadSheet");
System.out.println("processSpreadSheet: " + processSpreadSheet);
if (processCodesList.size() == 0) {
if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
Workbook wb1 = null;
try {
wb1 = new XSSFWorkbook(processSpreadSheet);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet sheet = wb1.getSheetAt(0);
Row row;
Cell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it
// doesn't start from first few rows
for (int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if (row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
// out.println("tmp value"+tmp);
if (tmp > cols) {
cols = tmp;
}
}
}
ProcessDefinition tempProcessCode;
for (int r1 = 0; r1 < rows; r1++) {
tempProcessCode = new ProcessDefinition();
row = sheet.getRow(r1);
if (row != null) {
if (row.getCell(0) != null) {
for (int counter = 0; counter < cols; counter++) {
cell = row.getCell((short) counter);
// cell = row.getCell(1);
if (counter == 0) {
if (cell != null) {
tempProcessCode.setProcessName(cell.getStringCellValue());
} else {
tempProcessCode.setProcessName("");
}
} else if (counter == 1) {
if (cell != null) {
tempProcessCode.setProcessCode(cell.getStringCellValue());
} else {
tempProcessCode.setProcessCode("");
}
} else if (counter == 2) {
if (cell != null) {
tempProcessCode.setIaeaCode(cell.getStringCellValue());
} else {
tempProcessCode.setIaeaCode("");
}
} else if (counter == 3) {
if (cell != null) {
tempProcessCode.setProcessDescription(cell.getStringCellValue());
// System.out.println(tempProcessCode.getProcessDescription());
} else {
tempProcessCode.setProcessDescription("");
// System.out.println("Process Description EMpty");
}
}
}
}
} else {
rows++;
}
processCodesList.add(tempProcessCode);
}
}
}
System.out.println(processCodesList.size());
}
/** Clone the sheet at the passed index and replace values as needed */
public Sheet addSheet(
Workbook wb,
SheetToAdd sheetToAdd,
Set<String> usedSheetNames,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
Sheet sheet = sheetToAdd.getSheet();
sheet.setForceFormulaRecalculation(true);
int sheetIndex = wb.getSheetIndex(sheet);
// Configure the sheet name, replacing any values as needed, and ensuring it is unique for the
// workbook
String sheetName =
EvaluationUtil.evaluateExpression(
sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix)
.toString();
sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames);
wb.setSheetName(sheetIndex, sheetName);
usedSheetNames.add(sheetName);
log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex);
// Iterate across all of the rows in the sheet, and configure all those that need to be
// added/cloned
List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>();
int totalRows = sheet.getPhysicalNumberOfRows();
int rowsFound = 0;
for (int rowNum = 0;
rowsFound < totalRows && rowNum < 50000;
rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases
Row currentRow = sheet.getRow(rowNum);
log.debug("Handling row: " + ExcelUtil.formatRow(currentRow));
if (currentRow != null) {
rowsFound++;
}
// If we find that the row that we are on is a repeating row, then add the appropriate number
// of rows to clone
String repeatingRowProperty =
getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections);
if (repeatingRowProperty != null) {
String[] dataSetSpanSplit = repeatingRowProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData());
int numRowsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numRowsToRepeat; i++) {
Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i));
if (repeatNum == 1 && row != null && row != currentRow) {
rowsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
sheetToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
rowsToAdd.add(new RowToAdd(row, newReplacements));
log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow);
}
}
if (numRowsToRepeat > 1) {
rowNum += numRowsToRepeat - 1;
}
} else {
rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData()));
log.debug("Adding row: " + ExcelUtil.formatRow(currentRow));
}
}
// Now, go through all of the collected rows, and add them back in
for (int i = 0; i < rowsToAdd.size(); i++) {
RowToAdd rowToAdd = rowsToAdd.get(i);
if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) {
Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections);
log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow));
}
}
return sheet;
}
@Transactional
public void saveWeeklyProfit(String fileTag, MultipartFile file) throws Exception {
try {
Workbook workbook;
Sheet worksheet;
if (file != null && file.getOriginalFilename().endsWith("xls")) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (file != null && file.getOriginalFilename().endsWith("xlsx")) {
workbook = new XSSFWorkbook(file.getInputStream());
} else {
throw new IllegalArgumentException(
"Received file does not have a standard excel extension.");
}
worksheet = workbook.getSheetAt(0);
if (worksheet == null) {
throw new Exception("Couldn't get the sheet.");
}
Integer noOfEntries = worksheet.getPhysicalNumberOfRows();
logger.info(noOfEntries.toString());
for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
Row entry = worksheet.getRow(rowIndex);
String name = entry.getCell(0).getStringCellValue();
String nameOfBusiness = entry.getCell(1).getStringCellValue();
Date weekEndingDt = entry.getCell(2).getDateCellValue();
// Date date = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH).parse(openingDate);
Double sale = entry.getCell(3).getNumericCellValue();
Double approximateProfit = entry.getCell(4).getNumericCellValue();
// Integer externalId=((Double)entry.getCell(0).getNumericCellValue()).intValue();
String event = entry.getCell(5).getStringCellValue();
logger.info(
"Row Contents:"
+ name
+ " "
+ nameOfBusiness
+ " "
+ weekEndingDt
+ " "
+ sale
+ " "
+ approximateProfit
+ " "
+ event);
WeeklyProfit wp = weeklyProfits.findProfits(name, nameOfBusiness, weekEndingDt);
if (wp == null) {
wp = new WeeklyProfit();
}
wp.setName(name);
wp.setNameOfBusiness(nameOfBusiness);
wp.setWeekEndingAt(weekEndingDt);
wp.setSale(sale);
wp.setApproximateProfit(approximateProfit);
wp.setEvents(event);
Long id = weeklyProfits.save(wp);
logger.info("Saved : " + id);
System.out.println("###### Saved : " + id);
}
} catch (Exception e) {
logger.info(e.getMessage() + " " + e.getCause());
// throw new MultipartException("Constraints Violated");
throw e;
}
}
/**
* create a FeatureSource with the specified Query
*
* @param entry
* @param query - a query containing a filter that will be applied to the data
*/
public ExcelFeatureSource(ContentEntry entry, Query query) {
super(entry, query);
Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);
dataStore = (ExcelDataStore) entry.getDataStore();
sheet = dataStore.getSheet();
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
int rows = sheet.getPhysicalNumberOfRows();
int start = dataStore.getHeaderRowIndex() + 1;
latCol = dataStore.getLatColumnIndex();
lonCol = dataStore.getLonColumnIndex();
features = new ArrayList<SimpleFeature>();
filteredFeatures = new ArrayList<SimpleFeature>();
evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
if (schema == null) {
schema = getSchema();
}
GeometryFactory geometryFactory = dataStore.getGeometryFactory();
SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
Row header = sheet.getRow(dataStore.getHeaderRowIndex());
for (int i = start; i < rows; i++) {
Row data = sheet.getRow(i);
double x = 0.0;
double y = 0.0;
for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
final Cell cell = data.getCell(col);
CellValue value = evaluator.evaluate(cell);
if (col == latCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
y = value.getNumberValue();
}
} else if (col == lonCol) {
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
x = value.getNumberValue();
}
} else {
// cast and handle
final String name = header.getCell(col).getStringCellValue().trim();
switch (value.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
AttributeType type = schema.getType(name);
Class<?> clazz = type.getBinding();
if (clazz == Double.class) {
builder.set(name, value.getNumberValue());
} else if (clazz == java.sql.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
builder.set(name, date);
} else if (clazz == java.util.Date.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
builder.set(name, javaDate);
} else if (clazz == Time.class) {
final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
final Calendar cal = Calendar.getInstance();
cal.clear();
cal.setTime(javaDate);
cal.set(0, 0, 0);
Time time = new Time(cal.getTimeInMillis());
builder.set(name, time);
}
break;
case Cell.CELL_TYPE_STRING:
builder.set(name, value.getStringValue().trim());
break;
case Cell.CELL_TYPE_BOOLEAN:
builder.set(name, value.getBooleanValue());
break;
default:
System.out.println(
"We don't handle "
+ cell.getCellType()
+ " type cells "
+ cell.getStringCellValue());
}
}
}
Point p = geometryFactory.createPoint(new Coordinate(x, y));
builder.set("the_geom", p);
SimpleFeature feature = builder.buildFeature(null);
features.add(feature);
}
filterFeatures(query);
}
public void parse(Sheet sheet) {
logger.debug("开始解析Sheet页:" + sheet.getSheetName());
startSheet(sheet);
int rowCount = sheet.getPhysicalNumberOfRows();
logger.debug("行数：" + rowCount);
// 遇到表头区域的时候记录开始列
int headerStartCol = 0;
for (Row r : sheet) {
HSSFRow row = (HSSFRow) r;
if (row == null) continue;
this.rowIndex = row.getRowNum();
if (rowIndex < startRow) {
continue;
}
// sundl: row.getFirstCellNum()不是很稳定，第一列的空格有时候会是null，有时候又是空字符串，这样导致这个值不可信
// 目前强制写成1， 后续可以考虑改成自己写找第一个非空列。
int firstCellNum = row.getFirstCellNum();
if (firstCellNum == -1) continue;
else firstCellNum = 1;
HSSFCell firstCell = row.getCell(firstCellNum);
String firstcellString = getCellStringValue(firstCell, evaluator);
if (!firstcellString.isEmpty()) {
if (aresStart.contains(rowIndex)) {
endArea();
logger.debug("Area开始,行号: " + rowIndex);
startArea(firstcellString);
} else if (areaTags.indexOf(firstcellString) != -1) {
endArea();
logger.debug("Area开始,行号: " + rowIndex);
startArea(firstcellString);
}
if (blockStart.keySet().contains(rowIndex)) {
endBlock();
startBlock(firstcellString, blockStart.get(rowIndex));
} else if (blocks.containsKey(firstcellString)) {
endBlock();
startBlock(firstcellString, blocks.get(firstcellString));
// Text区域第一行暂时不解析
if (currentBlockType == BlockTypes.TEXT) {
continue;
}
}
}
String[] strings = readRowStrings(row, 1, evaluator);
switch (currentBlockType) {
case BlockTypes.KEY_VALUE:
parseKeyValue(row, 1);
break;
case BlockTypes.TABLE:
if (!headerFound) {
// header按理说不应该存在空字符串，开头应该不会，但结尾可能存在
while (StringUtils.isEmpty(strings[strings.length - 1])) {
strings = (String[]) ArrayUtils.remove(strings, strings.length - 1);
}
headerStartCol = firstCellNum;
header(strings);
headerFound = true;
} else {
strings = POIUtils.readRowStrings(row, headerStartCol, evaluator);
row(strings);
}
break;
case BlockTypes.TEXT:
text.append(StringUtils.join(strings));
text.append('\n');
break;
}
}
endBlock();
endArea();
endSheet();
}
public int getSheetRows(Sheet sheet) {
return sheet.getPhysicalNumberOfRows();
}
/**
* 기념일정보 excel생성
*
* @param inputStream
*/
public List<AnnvrsryManageVO> selectAnnvrsryManageBnde(InputStream inputStream) {
String sTempId = null; // 사용자ID
String sTempNm = null; // 사용자명
String sTempAnnvrsryDe = null; // 기념일자
String sTempCldrSe = null; // 양/음 구분
String sTempAnnvrsrySe = null; // 기념일구분
String sTempAnnvrsryNm = null; // 기념일명
String sTempReptitSe = null; // 반복여부
List<AnnvrsryManageVO> list = new ArrayList<AnnvrsryManageVO>();
try {
Workbook hssfWB = excelZipService.loadWorkbook(inputStream);
// 엑셀 파일 시트 갯수 확인 sheet = 1
if (hssfWB.getNumberOfSheets() == 1) {
Sheet annvrsrySheet = hssfWB.getSheetAt(0); // 기념일관리 시트 가져오기
// HSSFRow annvrsryRow = annvrsrySheet.getRow(1); // 기념일 row 가져오기
// int annvrsrySheetRowCnt = annvrsryRow.getPhysicalNumberOfCells(); // 기념일 cell Cnt
int rowsCnt = annvrsrySheet.getPhysicalNumberOfRows(); // 행 갯수 가져오기
// 사용자ID 기념일자 양/음 구분 기념일구분 기념일명
for (int j = 1; j < rowsCnt; j++) { // row 루프
AnnvrsryManageVO annvrsryManageVO = new AnnvrsryManageVO();
Row row = annvrsrySheet.getRow(j); // row 가져오기
if (row != null) {
// int cells = row.getPhysicalNumberOfCells(); // cell 갯수 가져오기
Cell cell = null;
cell = row.getCell(0); // 사용자ID
if (cell != null) {
sTempId = cell.getRichStringCellValue().toString();
}
cell = row.getCell(1); // 사용자명
if (cell != null) {
sTempNm = cell.getRichStringCellValue().toString();
}
cell = row.getCell(2); // 기념일자
if (cell != null) {
sTempAnnvrsryDe = cell.getRichStringCellValue().toString();
}
cell = row.getCell(3); // 양/음구분
if (cell != null) {
sTempCldrSe = cell.getRichStringCellValue().toString();
}
cell = row.getCell(4); // 기념일구분
if (cell != null) {
sTempAnnvrsrySe = (String) cell.getStringCellValue();
}
cell = row.getCell(5); // 기념일명
if (cell != null) {
sTempAnnvrsryNm = cell.getRichStringCellValue().toString();
}
cell = row.getCell(6); // 반복여부
if (cell != null) {
sTempReptitSe = cell.getRichStringCellValue().toString();
}
annvrsryManageVO.setUsid(sTempId); // 당직자ID
annvrsryManageVO.setUsNm(sTempNm); // 당직자명
annvrsryManageVO.setAnnvrsrySe(sTempAnnvrsrySe);
annvrsryManageMapper.selectAnnvrsryManageBnde(annvrsryManageVO);
annvrsryManageVO.setAnnvrsryDe(DateUtil.formatDate(sTempAnnvrsryDe, "-"));
annvrsryManageVO.setCldrSe(sTempCldrSe);
annvrsryManageVO.setAnnvrsryNm(sTempAnnvrsryNm);
annvrsryManageVO.setReptitSe(sTempReptitSe);
list.add(annvrsryManageVO);
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return list;
}
void excelCopy() {
FileInputStream file1;
int lastRowUpdated = 0, currentRow = 0;
int i = 0;
try {
file1 =
new FileInputStream(
"C:\\Users\\SharvaP\\Desktop\\Excel Format\\LookUp for Transaction Name.xls");
int columnIndex = 0;
Workbook lookupWorkbook = new HSSFWorkbook(file1);
Sheet lookupSheet = lookupWorkbook.getSheetAt(0);
FileInputStream file2 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round1_temp.xls");
Workbook round1Workbook = new HSSFWorkbook(file2);
Sheet round1Sheet = round1Workbook.getSheetAt(0);
FileInputStream file3 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\template.xls");
Workbook templateWorkbook = new HSSFWorkbook(file3);
Sheet templateSheet = templateWorkbook.getSheetAt(0);
FileInputStream file4 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round2_temp.xls");
Workbook round2Workbook = new HSSFWorkbook(file4);
Sheet round2Sheet = round2Workbook.getSheetAt(0);
int averageRow = round1Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow = round1Sheet.getPhysicalNumberOfRows() - 1;
System.out.println("Average row1:" + averageRow);
int averageRow2 = round2Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow2 = round2Sheet.getPhysicalNumberOfRows() - 1;
/*Row round1Row = round1Sheet.getRow(averageRow);
Row round2Row = round2Sheet.getRow(averageRow2);
Cell tempRound1Cell=round1Row.getCell(columnIndex);
Cell tempRound2Cell=round2Row.getCell(columnIndex);
CellStyle cellStyle = templateWorkbook.createCellStyle();
CreationHelper createHelper=templateWorkbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
*/
Iterator<Row> rowIterator1 = lookupSheet.iterator();
while (rowIterator1.hasNext()) {
Row lookupRow = rowIterator1.next();
if (lookupRow.getRowNum() > 0) {
Cell lookupCell = lookupRow.getCell(1);
String transationNameTemplate = lookupCell.getStringCellValue();
// System.out.println("NAme:"+transationNameTemplate);
// System.out.println(templateSheet.getPhysicalNumberOfRows());
for (int count = lastRowUpdated;
count < templateSheet.getPhysicalNumberOfRows() - 1;
count++) {
Row templateRow = templateSheet.getRow(count);
Cell templateCell = templateRow.getCell(0);
Cell templateCellFormulae1 = templateRow.getCell(5);
Cell templateCellFormulae2 = templateRow.getCell(6);
String templateCellName = templateCell.getStringCellValue();
// System.out.println("template cell name:"+templateCellName);
currentRow = templateRow.getRowNum();
if (templateCellName.equals(transationNameTemplate)) {
// System.out.println("Transac Name:"+templateCellName+", row num:"+currentRow+", last
// updated row:"+lastRowUpdated);
Row round1RowAverage = round1Sheet.getRow(averageRow);
Cell round1CellAverage = round1RowAverage.getCell(columnIndex);
Row round2RowAverage = round2Sheet.getRow(averageRow2);
Cell round2CellAverage = round2RowAverage.getCell(columnIndex);
Cell templateCellRound1Average = templateRow.getCell(1);
Cell templateCellRound2Average = templateRow.getCell(3);
templateCellFormulae1.setCellFormula(
"IF(B"
+ (currentRow + 1)
+ ">D"
+ (currentRow + 1)
+ ",((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/B"
+ (currentRow + 1)
+ ")*100,((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/D"
+ (currentRow + 1)
+ ")*100)");
System.out.println(round1CellAverage.getNumericCellValue());
templateCellRound1Average.setCellValue(round1CellAverage.getNumericCellValue());
templateCellRound2Average.setCellValue(round2CellAverage.getNumericCellValue());
Row round1RowPercentile = round1Sheet.getRow(percentileRow);
Cell round1CellPercentile = round1RowPercentile.getCell(columnIndex);
Row round2RowPercentile = round2Sheet.getRow(percentileRow2);
Cell round2CellPercentile = round2RowPercentile.getCell(columnIndex);
Cell templateCellRound1Percentile = templateRow.getCell(2);
Cell templateCellRound2Percentile = templateRow.getCell(4);
templateCellFormulae2.setCellFormula(
"IF(C"
+ (currentRow + 1)
+ ">E"
+ (currentRow + 1)
+ ",((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/C"
+ (currentRow + 1)
+ ")*100,((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/E"
+ (currentRow + 1)
+ ")*100)");
templateCellRound1Percentile.setCellValue(round1CellPercentile.getNumericCellValue());
templateCellRound2Percentile.setCellValue(round2CellPercentile.getNumericCellValue());
// System.out.println("round1 avg value:"+round1CellAverage.getNumericCellValue());
// System.out.println("round2 avg value:"+round2CellAverage.getNumericCellValue());
// System.out.println("Match Found"+templateRow.getCell(1).getNumericCellValue());
columnIndex++;
lastRowUpdated = currentRow;
break;
}
}
}
}
FileOutputStream out =
new FileOutputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\Template.xls");
templateWorkbook.write(out);
out.close();
System.out.println("Finished....");
file1.close();
file2.close();
file3.close();
file4.close();
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
e.printStackTrace();
} catch (IOException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
