Rakesh Keerthi

Hi,

I'm working on a code where I need to find a string from a set of sentences, match if the string is in another keywords list and from there get the appropriate value from map and replace in original list.

Here is my code

import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.stream.Collectors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SampleCollection { public static void main(String[] args) throws Exception { Map<String, String> dictMap = new HashMap<String, String>(); dictMap.put("lmn", "lemon"); dictMap.put("gngr", "ginger"); dictMap.put("grn", "green"); dictMap.put("vegan", "vegetarian"); dictMap.put("brea", "bread"); dictMap.put("bre", "bread"); List<String> dictKeys = new ArrayList<String>(); dictKeys.add("lmn"); dictKeys.add("gngr"); dictKeys.add("grn"); dictKeys.add("vegan"); dictKeys.add("bre"); dictKeys.add("brea"); String dictArray[] = dictKeys.toArray(new String[0]); replaceTextInExcel(dictArray, dictMap); } private static void replaceTextInExcel(String[] dictArray, Map<String, String> dictMap) throws Exception { List<String> rawTitleList = new ArrayList<String>(); rawTitleList.add("bread gingerbread vegan"); rawTitleList.add("iced tea lmn gngr grn 32"); rawTitleList.add("iced tea lmn"); String[] rawTitlesArray = rawTitleList.toArray(new String[0]); replaceTheValuesInArray(rawTitleList, dictArray, dictMap); } private static void replaceTheValuesInArray(List<String> rawTitleList, String[] dictArray, Map<String, String> dictMap) { Pattern pattern = Pattern .compile(dictMap.keySet().stream().map(Pattern::quote).collect(Collectors.joining("|"))); Matcher m = pattern.matcher(String.join("~", rawTitleList)); StringBuffer newText = new StringBuffer(); while (m.find()) { m.appendReplacement(newText, dictMap.get(m.group())); } m.appendTail(newText); List<String> newStrings = Arrays.asList(newText.toString().split("~")); System.out.println("Original strings: " + rawTitleList); System.out.println("New strings: " + newStrings); } }

When I run this code the output that I get is as below.

Original strings: [bread gingerbread vegan, iced tea lmn gngr grn 32, iced tea lmn]
New strings: [breadd gingerbreadd vegetarian, iced tea lemon ginger green 32, iced tea lemon]

but my expected output is as below

Original strings: [bread gingerbread vegan, iced tea lmn gngr grn 32, iced tea lmn]
New strings: [bread gingerbread vegetarian, iced tea lemon ginger green 32, iced tea lemon]

Here I want to find entire match in rawTitleList from dictKeys and replace the value using the value from dictMap.

please let me know where am I going wrong and how can I fix this.

Thanks,
Sunny

Hi,

I'm writing a Web app and using Excel as backend as i'm not supposed to use anyother. Here in my code i am performing a jdbc on 2 tables. Insert in 1 and Update in another. Ans below is my code.

public class PostDataDAO { DBConnection dbConnection = new DBConnection(); public void save(UserBean postBean) throws SQLException { String userName = new com.sun.security.auth.module.NTSystem().getName(); System.out.println(userName); Connection conn, conn1 = dbConnection.conn; PreparedStatement ps, ps1 = dbConnection.ps; String excelPath = "D:\\Danny\\Result1.xls"; String mainPath = dbConnection.excelPath; try { dbConnection.createClassForName(); conn = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + excelPath + "; READONLY=FALSE;"); System.out.println("Connecting to database…"); System.out.println("Oracle JDBC Driver Registered!"); if (conn != null) { System.out.println("You made it, take control your database now!"); } else { System.out.println("Failed to make connection!"); } String insert = "insert into [result$] VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"; System.out.println(postBean.getStartDate() + " from Bean" + " end date is " + postBean.getEndDate()); ps = conn.prepareStatement(insert); ps.setString(1, postBean.getCaseNumber()); ps.setString(2, postBean.getCaseOwner()); ps.setString(3, postBean.getStatus()); ps.setString(4, postBean.getIssue()); ps.setString(5, postBean.getReason()); ps.setString(6, postBean.getDateOpened()); ps.setInt(7, postBean.getAge()); ps.setString(8, postBean.getResolution()); ps.setString(9, postBean.getFinalStatus()); ps.setString(10, postBean.getStartDate()); ps.setString(11, postBean.getEndDate()); ps.setDouble(12, postBean.getTotalTimeTaken()); System.out.println("Date from post bean ----------------- Case Number is " + postBean.getCaseNumber()); System.out.println("Storing Object is Done!"); ps.execute(); conn1 = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + mainPath + "; READONLY=FALSE;"); System.out.println("Connecting to database…"); System.out.println("Oracle JDBC Driver Registered!"); if (conn1 != null) { System.out.println("You made it, take control your database now! in the second block"); } else { } System.out.println("for m second block " + postBean.getCaseNumber()); String x = postBean.getCaseNumber(); String updateSheet = "UPDATE [report1448039568905$] SET STATE = 1 where [Case Number] = '" + x + "'"; ps1 = conn1.prepareStatement(updateSheet); System.out.println(" and " + updateSheet); ps1.execute(); ps1.close(); conn1.commit(); conn1.close(); ps.close(); conn.commit(); conn.close(); } catch (Exception e) { e.printStackTrace(); } }

Here my problem is, when i hit submit, the insert part is getting correctly posted. But the update part is a bit tricky and confusing.
The System.out.println() is printing everything as expected, and to post the data I'm going to my code, delete a line, undo a delete, Save it. and i get the below message in my eclipse console.

Dec 01, 2015 7:18:23 PM org.apache.catalina.core.StandardContext reload INFO: Reloading Context with name [/SFDCTracker] has started Dec 01, 2015 7:18:24 PM org.apache.catalina.core.StandardContext reload INFO: Reloading Context with name [/SFDCTracker] is completed


Once i get the above message, the other sheet is getting updated.

What i'm confused is, why am i doing this for the second table but not for the first. The first is getting inserted without any issues.

Please let me know how can i fix this.

Thanks,
Rakesh

Hi All,

If anyone have such problem, I've found a solution that worked for me.

use an id for the form like

<form method="post" id="myForm" action="PostData">

Then for the button add an onClick event as below

<input type="button" value="Submit Form" onclick="getLastDate()" />

and in the javascript in <head>, use the below method. Make sure that the method name is same as the onClick event attribute value.

Here i need to fill a text box with current time, so i used the below code, you have to change as per your requirement.

var date = Date.parse(new Date()); function getLastDate() { var date = new Date(); document.getElementById("endTime").value = [ date.getMonth() + 1, date.getDate(), date.getFullYear() ].join('/') + ' ' + [ date.getHours(), date.getMinutes(), date.getSeconds() ] .join(':'); setTimeout(function() { document.getElementById("myForm").submit(); }, 3000); }

This is solution for my first question.

For time delay i.e. my second question.

Use a setTimeout method in javascript as below. Here 3000 means 3 seconds.

setTimeout(function() { document.getElementById("myForm").submit(); }, 3000);

This has to be given wherever you want a time delay,

In my case, i need to get the time in text box, pause it and then submit the form data, so i took this after the time is fetched (please refer to my third code block for full js method).

This is how I've solved my problem.

Cheers,
Rakesh

Hi,

I've below jsp.

<%@page import="org.bean.UserBean"%> <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@page import="java.util.*"%> <%@page import="java.text.SimpleDateFormat"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> <script type="text/javascript"> function enableSubmit() { document.getElementById("getCase").disabled = true; document.getElementById("submitButton").disabled = false; } function enableGetCase() { document.getElementById("getCase").disabled = false; document.getElementById("submitButton").disabled = true; } function dynamicdropdown(listindex) { document.getElementById("subcategory").length = 0; switch (listindex) { case "closed": document.getElementById("subcategory").options[0] = new Option( "Please select framework", ""); document.getElementById("subcategory").options[1] = new Option( "Closed", "Closed"); document.getElementById("subcategory").options[2] = new Option( "Cash Apps/Financial Processing", "Cash Apps/Financial Processing"); break; case "open": document.getElementById("subcategory").options[0] = new Option( "Please select framework", ""); document.getElementById("subcategory").options[1] = new Option( "Waiting For BLUP", "Waiting For BLUP"); document.getElementById("subcategory").options[2] = new Option( "Waiting for Clarification from Rep", "Waiting for Clarification from Rep"); document.getElementById("subcategory").options[3] = new Option( "Waiting for Supervisor Assistance", "Waiting for Supervisor Assistance"); document.getElementById("subcategory").options[4] = new Option( "Need to route the case to Cash Apps/FP", "Need to route the case to Cash Apps/FP"); document.getElementById("subcategory").options[5] = new Option( "Waiting for Approval", "Waiting for Approval"); document.getElementById("subcategory").options[6] = new Option( "Send Updated Invoice", "Send Updated Invoice"); break; case "reassigned": document.getElementById("subcategory").options[0] = new Option( "Please select framework", ""); document.getElementById("subcategory").options[1] = new Option( "Assigned to other agent", "Assigned to other agent"); document.getElementById("subcategory").options[2] = new Option( "Assigned to other queue", "Assigned to other queue"); break; } return true; } var date = Date.parse(new Date()); function getLastDate() { var date = new Date(); document.getElementById("endTime").value = [ date.getMonth() + 1, date.getDate(), date.getFullYear() ].join('/') + ' ' + [ date.getHours(), date.getMinutes(), date.getSeconds() ] .join(':'); document.postFormData.submit(); } </script> </head> <body> <form method="post" name="postDataForm" action="PostData"> <% Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss"); String startFormattedDate1 = sdf.format(date); Date d1 = sdf.parse(startFormattedDate1); date = new Date(); String startFormattedDate2 = sdf.format(date); %> <input type="Submit" value="Get Case" name="getCase" id="getCase" disabled="disabled" /> <table> <tr> <td>Case Number</td> <td><input id="caseNumber" name="caseNumber" type="text" value="${userBeans[0].getCaseNumber()}" /></td> <td>Case Owner</td> <td><input id="caseOwner" name="caseOwner" type="text" value="${userBeans[0].getCaseOwner()}" /></td> </tr> <tr> <td>Source</td> <td><input id="source" name="source" type="text" /></td> <td>Status</td> <td><input id="status" name="status" type="text" value="${userBeans[0].getStatus()}" /></td> </tr> <tr> <td class="auto-style2">Issue</td> <td class="auto-style2"><input id="issue" name="issue" value="${userBeans[0].getIssue()}" type="text" /></td> <td class="auto-style2">Reason</td> <td class="auto-style2"><input id="reason" name="reason" value="${userBeans[0].getReason()}" type="text" /></td> </tr> <tr> <td>Date/Time Opened</td> <td><input id="dateOpened" type="text" name="dateOpened" value="${userBeans[0].getDateOpened()}" /></td> <td>Age(Days)</td> <td><input id="age" type="text" name="age" value="${userBeans[0].getAge()}" /></td> </tr> <tr> <td>Resolution</td> <td><select id="Select1" name="resolution" onchange="javascript: dynamicdropdown(this.options[this.selectedIndex].value);"> <option value="" id="">Select</option> <option value="closed" id="closed">Closed</option> <option value="open" id="open">Open</option> <option value="reassigned" id="reassigned">Reassigned</option> </select></td> <td>Final Status</td> <td><select name="finalStatus" id="subcategory"> <option value="">Please select framework</option> </select></td> </tr> <tr> <td>Start Time</td> <td><input id="startTime" type="text" name="startDate" value="<%=startFormattedDate1%>" /></td> <td>End Time</td> <td><input id="endTime" name="endDate" type="text" /></td> </tr> </table> <input type="button" value="Submit" name="submitButton" id="submitButton" onClick="getLastDate()" /> </form> </body> </html>

Here I've 2 questions,

I'm unable to submit my data to database using a button through JavaScript. Using a submit type is good, but here i need to fill a textbox(id = endTime) with date and time when i hit submit. That's why I've used it as a button instead of submit. I thought that the button is not working, but when ever i click on that button the latest time is fetched into textbox(id = endTime).

I need a small pause of say 2-3 seconds after the time is generated in the textbox and then the post should be done.

Please let me know how can i get this and where am i going wrong.

Thanks.

Bear Bibeault wrote:Why use toString() rather than a getter (accessor) that gives you the value that you want?

Hi Bear,

Thanks for this tip. I did it (using below) and got the result as expected.

<input id="Text1" type="text" value="${userBeans[0].getCaseNumber();}" />

Thanks Again
:beerchug:

K. Tsang wrote:Are you meant to display just the first item or all the records in the list?

If the latter, better to use the <c:foreach> to loop through the list.

Hi Tsang, I don't want to retrieve all the values, i want only the first value to be retrieved.

Hi,

I've the below DAO File.

package org.DAO; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.servlet.RequestDispatcher; import org.bean.UserBean; import org.code.general.DBConnection; public class GetDataDAO { DBConnection dbConnection = new DBConnection(); public List<UserBean> list() throws Exception { List<UserBean> userBeans = new ArrayList<UserBean>(); try { Connection conn = dbConnection.conn; Statement stmt = dbConnection.stmt; ResultSet rs = dbConnection.rs; PreparedStatement ps = dbConnection.ps; String excelPath = dbConnection.excelPath; String queryString = null; dbConnection.createClassForName(); conn = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + excelPath + "; READONLY=FALSE;"); System.out.println("Connecting to database…"); System.out.println("Oracle JDBC Driver Registered!"); if (conn != null) { System.out.println("You made it, take control your database now!"); } else { System.out.println("Failed to make connection!"); } stmt = conn.createStatement(); queryString = "Select * from [report1448039568905$] where ([Case Owner] = 'SSHD' or [Case Owner] = 'Hyderabad Operations' or [Case Owner] = 'Customer Service- Core')"; rs = stmt.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); int rowCount = rsmd.getColumnCount(); System.out.println("bno of cols are " + rsmd.getColumnCount()); for (int i = 0; i < rowCount; i++) { System.out.print(rsmd.getColumnName(i + 1) + " \t"); System.out.println(rsmd.getColumnTypeName(i + 1)); } while (rs.next()) { UserBean userBean = new UserBean(); userBean.setCaseNumber(rs.getString(1)); userBean.setCaseOwner(rs.getString(2)); userBean.setStatus(rs.getString(4)); userBean.setIssue(rs.getString(5)); userBean.setReason(rs.getString(6)); userBean.setDateOpened(rs.getString(7)); userBean.setAge(rs.getInt(8)); userBeans.add(userBean); } } catch (Exception e) { e.printStackTrace(); } return userBeans; } }

And the below Servlet
package org.servlet; import org.DAO.*; import org.bean.UserBean; import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("/GetData") public class GetData extends HttpServlet { private static final long serialVersionUID = 1L; public GetData() { super(); } GetDataDAO getdatadao = null; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // set content type try { getdatadao = new GetDataDAO(); List<UserBean> userBeans = getdatadao.list(); request.setAttribute("userBeans", userBeans); request.getRequestDispatcher("DisplayTable.jsp").forward(request, response); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }


Here i'm trying to print the first value (caseNumber) in a jsp textbox using the below code.

<body> <table border=2 width="45%"> <tr> <td colspan="2" align="center"><b>Comedy Movies</b></td> </tr> <tr> <td><b>Title</b></td> <td><b>Description</b></td> </tr> <tr> <td>Case Number</td> <td><c:out value="${userBeans[0].toString()}" /></td> </tr> </table> </body>

But the output i getting is as below

org.bean.UserBean@16c8f41

but instead of this i need the exact value at there. please let me know where am i going wrong and how do i fix it.

Thanks,
Sunny

Got it. This is for reference, just in case someone gets the same problem.

public class ReadData { @SuppressWarnings({ "resource", "null" }) public static void main(String[] args) throws IOException { String colIndex[] = { "AA", "AB", "AF", "AG", "AI", "AM", "AN" }; // get file FileInputStream fin = new FileInputStream(new File("D:\\Test.xlsx")); // create book holding object XSSFWorkbook wb = new XSSFWorkbook(fin); // get sheet XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { XSSFRow row = (XSSFRow) rowIt.next(); for (int a = 0; a < colIndex.length; a++) { short ref = new CellReference(colIndex[a]).getCol(); Cell cell = row.getCell(ref); System.out.println(cell); } System.out.println("\n\n\n\n"); } } }

Hi,

I'm writing a Java program to fetch data from Excel sheet.

From the below program, i'm able to retrieve the entire data.

import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.ss.formula.functions.Column; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadData { @SuppressWarnings({ "resource", "null" }) public static void main(String[] args) throws IOException { // get file FileInputStream fin = new FileInputStream( new File(""D:\\Test.xlsx")); // create book holding object XSSFWorkbook wb = new XSSFWorkbook(fin); // get sheet XSSFSheet sheet = wb.getSheetAt(0); // iterate through rows Iterator<Row> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { XSSFRow row = (XSSFRow) rowIt.next(); // iterate through Columns Iterator<Cell> colIt = row.cellIterator(); while (colIt.hasNext()) { Cell cell = colIt.next(); System.out.println(cell.toString()); } System.out.println(); } } }

But here my case is there are nearly 45-47 columns and out of there there is some data which is not required(for me, but needed for some other teams). Every column has a heading, and out of these 45-47 columns i want to pull data only from 12 columns, and there are randomly placed between the rest of columns in Excel sheet.

And I need to retrieve only a set of column data from the Excel Sheet. I'm trying to do it with the below program.

I've initialized the columns from where i need to retrieve data in a string named colIndex.

public class ReadData { @SuppressWarnings({ "resource", "null" }) public static void main(String[] args) throws IOException { String colIndex[] = { "AA", "AB", "AF", "AG", "AI", "AM", "AN" }; // get file FileInputStream fin = new FileInputStream(new File("D:\\Test.xlsx")); // create book holding object XSSFWorkbook wb = new XSSFWorkbook(fin); // get sheet XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { XSSFRow row = (XSSFRow) rowIt.next(); for (int a = 0; a < colIndex.length; a++) { CellReference ref = new CellReference(colIndex[a]); Cell cell = row.getCell(ref.toString()); System.out.println(cell); } } } }

I'm getting the below error

Exception in thread "main" java.lang.Error: Unresolved compilation problem: The method getCell(int) in the type XSSFRow is not applicable for the arguments (String) at ReadData.main(ReadData.java:33)

I'm using Apache POI.

please let me know how can i fix this.

Thanks,
Sunny

Got it thank you very much. :-)

when i'm trying to use the correct format it doesn't show the correct output in the query, if the format is modified, then i get the correct format with an exception

Nop. I'm getting the Exception if i change the format.

Yes that is right when i use
dd-M-yyy
the output is
Wed May 09 00:00:00 IST 14

when i use
dd/MM/yyyy
the output is
java.text.ParseException: Unparseable date: "09-03-14"

Sorry, that was a typo in my sql string. updated it, but still the same output

Below screenshots shows the input and the query generated.

The red boxes represents the dates.