private void ReadDataToArray(Workbook dataWb) {
Connection con = DbConnection.getConnection();
String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);";
for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) {
if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) {
try {
preparedStatement = con.prepareStatement(DataSheetInsertQuery);
preparedStatement.setString(
1,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue()));
preparedStatement.setDate(
2,
new Date(
(dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime()));
preparedStatement.setString(
3,
String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
DbConnection.closePreparedStatement(preparedStatement);
DbConnection.closeConnection(con);
System.out.println("All the data in the sheet is analysed and updated to memory");
}
/**
* @param data
* @param fileType
*/
private void buildWorkBook(byte[] data, FileType fileType) {
ByteArrayInputStream bs = new ByteArrayInputStream(data);
try {
if (fileType == FileType.XLSX) workbook = new XSSFWorkbook(bs);
else if (fileType == FileType.XLS) {
workbook = new HSSFWorkbook(bs);
}
sheetEvent = workbook.getSheet("Event Details");
sheetParticipants = workbook.getSheet("Participants Details");
} catch (IOException e) {
sLogger.error("Issues with files", e);
}
}
public static void main(String args[]) {
try {
String fileName = "C:/Users/NITHA/Desktop/Task.xlsx";
// Create the input stream from the xlsx/xls file
FileInputStream fis = new FileInputStream(fileName);
// Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if (fileName.toLowerCase().endsWith("xlsx")) {
workbook = new XSSFWorkbook(fis);
} else if (fileName.toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(fis);
}
// Get the nth sheet from the workbook
Sheet sheet = workbook.getSheet(HEADER_VALUES);
retrieveSheetInformation(sheet);
// close file input stream
fis.close();
} catch (IOException e) {
e.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;
}
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);
}
}
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 Transaction parseAsTransaction(Row row) {
String savingsAccountIdCheck = readAsInt(SAVINGS_ACCOUNT_NO_COL, row);
if (!savingsAccountIdCheck.equals("")) savingsAccountId = savingsAccountIdCheck;
String transactionType = readAsString(TRANSACTION_TYPE_COL, row);
String amount = readAsDouble(AMOUNT_COL, row).toString();
String transactionDate = readAsDate(TRANSACTION_DATE_COL, row);
String paymentType = readAsString(PAYMENT_TYPE_COL, row);
String paymentTypeId = getIdByName(workbook.getSheet("Extras"), paymentType).toString();
String accountNumber = readAsLong(ACCOUNT_NO_COL, row);
String checkNumber = readAsLong(CHECK_NO_COL, row);
String routingCode = readAsLong(ROUTING_CODE_COL, row);
String receiptNumber = readAsLong(RECEIPT_NO_COL, row);
String bankNumber = readAsLong(BANK_NO_COL, row);
return new Transaction(
amount,
transactionDate,
paymentTypeId,
accountNumber,
checkNumber,
routingCode,
receiptNumber,
bankNumber,
Integer.parseInt(savingsAccountId),
transactionType,
row.getRowNum());
}
private void initActiveSheet() {
if (dataFile == null) return;
if (sheetIndex > -1) {
dataSheet = dataFile.getSheetAt(sheetIndex);
if (dataSheet == null) {
throw new IndexOutOfBoundsException(
"Sheet with index "
+ sheetIndex
+ " does not exist in file: "
+ inputFile.getFullPath());
}
} else if (sheetName != null) {
dataSheet = dataFile.getSheet(sheetName);
if (dataSheet == null) {
throw new IllegalArgumentException(
"Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
}
} else {
int index = dataFile.getActiveSheetIndex();
dataSheet = dataFile.getSheetAt(index);
}
headerColumns = null;
int numMergedRegions = dataSheet.getNumMergedRegions();
mergedRegions = new ArrayList<>(numMergedRegions);
for (int i = 0; i < numMergedRegions; i++) {
mergedRegions.add(dataSheet.getMergedRegion(i));
}
}
/**
* 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());
}
}
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;
}
}
public void rowCount(String sheetName)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream fis = new FileInputStream(filepath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
int rowcont = sh.getLastRowNum() + 1;
System.out.println(rowcont);
}
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;
}
}
public static Sheet readExcelFile(String filePath, String sheetName) throws Exception {
File f = new File(filePath);
FileInputStream in = new FileInputStream(f);
Workbook wrkBook = (Workbook) WorkbookFactory.create(in);
Sheet merchantsheet = wrkBook.getSheet(sheetName);
return merchantsheet;
}
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;
}
}
public String getTestData(String sheetName, int rowNum, int colNum)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream fis = new FileInputStream(filepath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
String data = row.getCell(colNum).getStringCellValue();
return data;
}
private void openWorkbook(Workbook workbook) {
ObservableList<String> sheets = new ObservableListWrapper<>(new ArrayList<>());
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
worksheetSelector.getItems().add(workbook.getSheetName(i));
}
sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName));
Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst());
}
public Integer getColumnCount()
throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheet(sheetName);
Row row = sheet.getRow(0);
int numColumns = row.getLastCellNum();
inp.close();
return numColumns;
}
/**
* Opens a excel sheet
*
* @param fileName name of the file where you want data
* @param sheetName name of the sheet in the excel file
*/
public ParsingExcel(String fileName, String sheetName) {
System.out.println(fileName);
try {
File file = new File(fileName);
System.out.println(file.getAbsolutePath());
if (fileName.indexOf("xlsx") < 0) { // for .xls format
wb = new HSSFWorkbook(new FileInputStream(file));
ws = wb.getSheet(sheetName);
} else { // for .xlsx format
wb = new XSSFWorkbook(fileName);
ws = (XSSFSheet) wb.getSheet(sheetName);
}
} catch (IOException io) {
throw new Error(
"Invalid file '"
+ fileName
+ "' or incorrect sheet '"
+ sheetName
+ "', enter a valid one");
}
}
/**
* put dataList to excel sheets.
*
* @param sheetName excel sheet name.
* @param dataList data list to be parsed and put into excel sheets.
* @param rowNum row count of the sheet to be modified.
* @param ignoreRows rows to skip when put value.
* @param ignoreColumns columns to skip when put value.
* @throws RuntimeException
* @throws IllegalArgumentException
*/
public void putListToExcelWithFullIgnore(
String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
if (dataList.size() % rowNum != 0) {
LOG.error("dataList has wrong element count for excel!");
throw new IllegalArgumentException("dataList has wrong element count for excel!");
}
String value = null;
int index = 0;
final int colCount = dataList.size() / rowNum;
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);
}
for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
xlRow = xlSheet.getRow(j);
if (xlRow == null) {
xlRow = xlSheet.createRow(j);
}
for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
value = dataList.get(index);
xlCell = xlRow.getCell(i);
if (xlCell == null) {
xlCell = xlRow.createCell(i);
}
xlCell.setCellType(1);
if (value != null) {
xlCell.setCellValue(value);
}
index++;
}
}
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());
}
}
/**
* 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 String setTestData(String sheetName, int rowNum, int colNum, String data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream fis = new FileInputStream(filepath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
Cell cel = row.createCell(colNum);
cel.setCellType(Cell.CELL_TYPE_STRING);
cel.setCellValue(data);
FileOutputStream fos = new FileOutputStream(filepath);
cel.setCellValue(data);
wb.write(fos);
wb.close();
return data;
}
@Test
public void test() throws Throwable {
Workbook wb =
WorkbookFactory.create(new File(Resources.getResource("excel/employees.xlsx").getFile()));
Sheet sheet = wb.getSheet("employees");
Iterator<Row> itr = sheet.iterator();
itr.next();
List<Employee> employees = new LinkedList<>();
while (itr.hasNext()) {
employees.add(populate(itr.next()));
}
IOUtils.closeQuietly(wb);
Assert.assertEquals(2, employees.size());
TestUtils.print(employees);
}
public int getRowCount(String sheetName) {
int retVal = 0;
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
retVal = s.getLastRowNum();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return retVal;
}
@Override
public Result parse() {
Result result = new Result();
Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
Integer noOfEntries = getNumberOfRows(savingsTransactionSheet, AMOUNT_COL);
for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
Row row;
try {
row = savingsTransactionSheet.getRow(rowIndex);
if (isNotImported(row, STATUS_COL)) savingsTransactions.add(parseAsTransaction(row));
} catch (Exception e) {
logger.error("row = " + rowIndex, e);
result.addError("Row = " + rowIndex + " , " + e.getMessage());
}
}
return result;
}
@Override
public void run(TaskMonitor tm) throws Exception {
tm.setTitle("Loading network from table");
tm.setProgress(0.0);
tm.setStatusMessage("Loading network...");
Workbook workbook = null;
// Load Spreadsheet data for preview.
if (fileType != null
&& (fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension())
|| fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension()))
&& workbook == null) {
try {
workbook = WorkbookFactory.create(is);
} catch (InvalidFormatException e) {
throw new IllegalArgumentException(
"Could not read Excel file. Maybe the file is broken?", e);
} finally {
if (is != null) is.close();
}
}
try {
if (this.fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension())
|| this.fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension())) {
String networkName = ntmp.getName();
if (networkName == null) networkName = workbook.getSheetName(0);
final Sheet sheet = workbook.getSheet(networkName);
reader =
new ExcelNetworkSheetReader(
networkName, sheet, ntmp, nMap, rootNetwork, serviceRegistrar);
} else {
reader = new NetworkTableReader(inputName, is, ntmp, nMap, rootNetwork, serviceRegistrar);
}
} catch (Exception ioe) {
tm.showMessage(TaskMonitor.Level.ERROR, "Unable to read table: " + ioe.getMessage());
return;
}
loadNetwork(tm);
tm.setProgress(1.0);
}
public void generateReport() throws IOException {
System.out.println("Test Report Gen");
Sheet sheet = workbook.getSheet("sheet1");
try {
String strSql = "Select * from participant;";
Sql db = new Sql();
ResultSet rs = db.executeQuery(strSql);
ResultSetMetaData rsMeta = rs.getMetaData();
Row row = sheet.createRow(0);
Cell cell;
// get table columns which will act as title columns
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String colName = rsMeta.getColumnName(i);
int dataType = rsMeta.getColumnType(i);
cell = row.createCell(i - 1);
cell.setCellValue(colName);
}
int i = 1;
while (rs.next()) {
// create row to write data to
row = sheet.createRow(i);
// create cells for writing data to
for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
cell = row.createCell(c - 1);
String colName = rsMeta.getColumnName(c);
cell.setCellValue(rs.getString(colName));
}
i++;
}
JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
} catch (SQLException ex) {
ex.printStackTrace();
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
/// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
workbook.write(fileOut);
fileOut.close();
}
public String getExcelData(String sheetName, int rowNum, int colNum) {
String retVal = null;
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
Row r = s.getRow(rowNum);
Cell c = r.getCell(colNum);
retVal = c.getStringCellValue();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return retVal;
}
private void generatePrivateDataRegion(Workbook wb, StringBuilder sb) {
// Get the Private Data Sheet
Sheet sheet = wb.getSheet("PrivateData");
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 cellType = row.getCell(1);
String type = cellType.getStringCellValue().replaceAll(" ", "");
Cell cellDescription = row.getCell(2);
String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
Cell cellAttributes = row.getCell(3);
String attributes = cellAttributes.getStringCellValue();
sb.append("PrivateData PD" + id + " {");
sb.append("\n");
sb.append("\tName \"PD" + id + "\"");
sb.append("\n");
sb.append("\tDescription \"" + description + "\"");
sb.append("\n");
sb.append("\tType " + type);
sb.append("\n");
for (String a : attributes.split(",\n")) {
a = a.substring(0, 1).toUpperCase() + a.substring(1);
sb.append("\tAttribute \"" + a + "\" {");
sb.append("\n");
sb.append("\t\tDescription \"" + a + "\"");
sb.append("\n\t}");
sb.append("\n");
}
// Delete last '\n'
// sb.deleteCharAt(sb.length() - 2);
sb.deleteCharAt(sb.length() - 1);
sb.append("\n}");
sb.append("\n\n");
} else break;
}
}
public void writeToExcel(String sheetName, int rowNum, int colNum, String desc) {
try {
FileInputStream fis = new FileInputStream(path1);
Workbook wb = WorkbookFactory.create(fis);
Sheet s = wb.getSheet(sheetName);
Row r = s.getRow(rowNum);
Cell c = r.createCell(colNum);
c.setCellValue(desc);
FileOutputStream fos = new FileOutputStream(path1);
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public String[] getHeaders() throws FileNotFoundException, IOException, InvalidFormatException {
InputStream inp;
inp = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(inp);
List<String> listHeaders = new ArrayList();
Row row;
Sheet sheet = wb.getSheet(sheetName);
row = sheet.getRow(0);
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j).getStringCellValue().isEmpty()
|| row.getCell(j).getStringCellValue() == null) {
break;
}
listHeaders.add(row.getCell(j).getStringCellValue());
}
inp.close();
return listHeaders.toArray(new String[listHeaders.size()]);
}