List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:LoadExcels.Load_viral_load_raw.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String patientccc = ""; String suppression = ""; String testingdate = ""; String agebracket = ""; String dateoftesting = ""; int age = 0;// w w w. jav a 2 s .c om String sex = ""; String batchno = ""; String supporttype = ""; int ageinteger = 0; try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_viral_load.jsp"; //--------------------------------------------------------------------- String numerator_un, denominator_un; String fun_less1, fun_1to4, fun_5to14, fun_15to19, fun_20; String mun_less1, mun_1to4, mun_5to14, mun_15to19, mun_20; String subtotal_un, numerator_vi, denominator_vi; String fvi_less1, fvi_1to4, fvi_5to14, fvi_15to19, fvi_20; String mvi_less1, mvi_1to4, mvi_5to14, mvi_15to19, mvi_20, subtotal_vi; //--------------------------------------------------------------------- numerator_un = denominator_un = ""; fun_less1 = fun_1to4 = fun_5to14 = fun_15to19 = fun_20 = ""; mun_less1 = mun_1to4 = mun_5to14 = mun_15to19 = mun_20 = ""; subtotal_un = numerator_vi = denominator_vi = ""; fvi_less1 = fvi_1to4 = fvi_5to14 = fvi_15to19 = fvi_20 = ""; mvi_less1 = mvi_1to4 = mvi_5to14 = mvi_15to19 = mvi_20 = subtotal_vi = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "sync_viral_load.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_viral_load.jsp"; break; } //#(0) Batch No(1) Patient CCC No(2) Testing Lab(3) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Sample Type(10) Collection Date(11) Received Status(12) Reason for Repeat(13) Regimen (14) Justification (15) ART Initiation Date(16) Date of Receiving(17) Date of Testing(18) Date of Dispatch (19) Result(cp/ml) (20) Result(Log) (21) Suppressed? (22) //Patient CCC No(2) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Date of Testing(18) Suppressed? (22) HSSFCell cellbatcno = rowi.getCell((short) 1); if (cellbatcno.getCellType() == 0) { //numeric batchno = "" + (int) cellbatcno.getNumericCellValue(); } else if (cellbatcno.getCellType() == 1) { batchno = cellbatcno.getStringCellValue(); } { } HSSFCell cellpatienceno = rowi.getCell((short) 2); System.out.println("CELLTYPE IS " + cellpatienceno.getCellType()); if (cellpatienceno.getCellType() == 1) { //this is a string patientccc = (String) cellpatienceno.getStringCellValue(); } else if (cellpatienceno.getCellType() == 0) { //this is a numeric value patientccc = "" + (int) cellpatienceno.getNumericCellValue(); } else { patientccc = (String) cellpatienceno.getStringCellValue(); } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 6); facilityName = cellfacil.getStringCellValue(); HSSFCell cellmfl = rowi.getCell((short) 7); if (cellmfl.getCellType() == 1) { //string mflcode = new Integer(cellmfl.getStringCellValue()); } else { //numeric mflcode = (int) cellmfl.getNumericCellValue(); } HSSFCell cellsex = rowi.getCell((short) 8); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell cellage = rowi.getCell((short) 9); age = (int) cellage.getNumericCellValue(); ageinteger = age; agebracket = getageBracket(ageinteger); HSSFCell celldate = rowi.getCell((short) 18); dateoftesting = "" + celldate.getStringCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); // dateoftesting= new SimpleDateFormat("MM/dd/yyyy").format(celldate.getDateCellValue()); //dateoftesting = formatter.format(dateoftesting); HSSFCell cellsuppression = rowi.getCell((short) 22); if (cellsuppression.getCellType() == 1) { //string suppression = cellsuppression.getStringCellValue(); } else { suppression = "" + (int) cellsuppression.getNumericCellValue(); } //split the date, year and month //raw date is of form m/d/yyyy String dateparameters[] = dateoftesting.split("-"); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equals("01") || month.equals("02") || month.equals("03")) { quarterName = "January-March"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("04") || month.equals("05") || month.equals("06")) { quarterName = "April-June"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("07") || month.equals("08") || month.equals("09")) { quarterName = "July-September"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("10") || month.equals("11") || month.equals("12")) { quarterName = "October-December"; if (dateparameters[0].length() == 4) { //assume year = Integer.parseInt(dateparameters[0]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + dateoftesting); } System.out.println("Quarter " + quarterName + "Year " + year); facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID,ART_Support FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); supporttype = conn.rs.getString(2); if (supporttype == null) { supporttype = ""; } } if (facilityID.length() > 0 && !sex.equals("")) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = batchno + "_" + patientccc + "_" + dateoftesting; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM viral_load_raw WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { System.out.println("INSERT >> " + numerator_un); String inserter = "INSERT INTO viral_load_raw (id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype,suppression_status) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, dateoftesting); conn.pst.setString(11, patientccc); conn.pst.setString(12, batchno); conn.pst.setString(13, supporttype); conn.pst.setString(14, suppression); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE viral_load_raw SET SubPartnerID=?,Year=?,Quarter=?,Mflcode =?,Sex=?,age=?,agebracket=?,SubPartnerNom=?,dateoftesting=?,patientccc=?,batchno=?,supporttype=?,suppression_status=? " + " WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, dateoftesting); conn.pst.setString(10, patientccc); conn.pst.setString(11, batchno); conn.pst.setString(12, supporttype); conn.pst.setString(13, suppression); conn.pst.setString(14, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode on subpartner :" + mflcode); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:Login.HULogin.java
public void loginData(List sheetData) throws UnsupportedEncodingException, IOException { String url = "http://stagehudroid.happilyunmarried.com/huapi/index/login"; HttpClient client = new DefaultHttpClient(); HttpPost post = new HttpPost(url); // add header post.setHeader("User-Agent", USER_AGENT); List<NameValuePair> urlParameters = new ArrayList<NameValuePair>(); for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); //for (int j = 0; j < list.size(); j++) { HSSFCell email = (HSSFCell) list.get(0); //System.out.println(email); HSSFCell password = (HSSFCell) list.get(1); if (email.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mail = email.getStringCellValue(); System.out.println(mail); urlParameters.add(new BasicNameValuePair("email", mail)); }/*from w w w . j a v a2 s .co m*/ if (password.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Password = password.getStringCellValue(); urlParameters.add(new BasicNameValuePair("password", Password)); } urlParameters.add(new BasicNameValuePair("secretkey", "XmgobA7HyvrBLhjI74o5pqec2fDFSf4TWzmIhSYnkNU=")); //urlParameters.add(new BasicNameValuePair("caller", "")); //urlParameters.add(new BasicNameValuePair("num", "12345")); post.setEntity(new UrlEncodedFormEntity(urlParameters)); HttpResponse response = client.execute(post); System.out.println("\nSending 'POST' request to URL : " + url); System.out.println("Post parameters : " + post.getEntity()); System.out.println("Response Code : " + response.getStatusLine().getStatusCode()); BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent())); StringBuffer result = new StringBuffer(); String line = ""; while ((line = rd.readLine()) != null) { result.append(line); } System.out.println(result.toString()); } }
From source file:Login.HULogin.java
private static void UserData(List sheetData) throws UnsupportedEncodingException, IOException { String url = "http://pp.happly.in//huapi/index/register"; HttpClient client = new DefaultHttpClient(); HttpPost post = new HttpPost(url); //post.setHeader("User-Agent", USER_AGENT); List<NameValuePair> urlParameters = new ArrayList<NameValuePair>(); for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); //for (int j = 0; j < list.size(); j++) { HSSFCell email = (HSSFCell) list.get(0); //System.out.println(email); HSSFCell password = (HSSFCell) list.get(1); HSSFCell gender = (HSSFCell) list.get(2); HSSFCell firstname = (HSSFCell) list.get(3); HSSFCell lastname = (HSSFCell) list.get(4); HSSFCell location = (HSSFCell) list.get(5); HSSFCell phone = (HSSFCell) list.get(6); if (email.getCellType() == HSSFCell.CELL_TYPE_STRING) { String mail = email.getStringCellValue(); System.out.println(mail); urlParameters.add(new BasicNameValuePair("email", mail)); }/*from ww w . jav a2s . c o m*/ if (password.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Password = password.getStringCellValue(); urlParameters.add(new BasicNameValuePair("password", Password)); } if (gender.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Gender = gender.getStringCellValue(); urlParameters.add(new BasicNameValuePair("gender", Gender)); } if (firstname.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Firstname = firstname.getStringCellValue(); urlParameters.add(new BasicNameValuePair("firstname", Firstname)); } if (lastname.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Lastname = lastname.getStringCellValue(); urlParameters.add(new BasicNameValuePair("lastname", Lastname)); } urlParameters.add(new BasicNameValuePair("newsletter", "true")); urlParameters.add(new BasicNameValuePair("licence", "ok")); urlParameters.add(new BasicNameValuePair("secretkey", "XmgobA7HyvrBLhjI74o5pqec2fDFSf4TWzmIhSYnkNU=")); urlParameters.add(new BasicNameValuePair("gender", "M")); urlParameters.add(new BasicNameValuePair("dob", "08-22-2015")); if (location.getCellType() == HSSFCell.CELL_TYPE_STRING) { String Location = location.getStringCellValue(); urlParameters.add(new BasicNameValuePair("location", Location)); } if (phone.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { phone.setCellType(phone.CELL_TYPE_STRING); String Phone = phone.getStringCellValue(); urlParameters.add(new BasicNameValuePair("phone", Phone)); } post.setEntity(new UrlEncodedFormEntity(urlParameters)); HttpResponse response = client.execute(post); System.out.println("\nSending 'POST' request to URL : " + url); System.out.println("Post parameters : " + post.getEntity()); System.out.println("Response Code : " + response.getStatusLine().getStatusCode()); BufferedReader rd = new BufferedReader(new InputStreamReader(response.getEntity().getContent())); StringBuffer result = new StringBuffer(); String line = ""; while ((line = rd.readLine()) != null) { result.append(line); } System.out.println(result.toString()); } }
From source file:mnb.io.tabular.xls.HSSFPreparsedSheet.java
License:Open Source License
@Override public PreparsedEntry getEntry(int i, List<TableDescription> cols) { PreparsedEntry entry = null;/*from w ww . jav a 2 s. co m*/ // otherwise we can't cast if (dataType == ReactionColumn.class) { entry = new PreparsedReaction(); } else if (dataType == EntityColumn.class) { entry = new PreparsedMetabolite(); } // add others HSSFRow row = worksheet.getRow(i); ExcelModelProperties properties = super.getProperties(); // for the described columns add them to the preparsed entry for (TableDescription col : cols) { int index = properties.getColumnIndex(col); HSSFCell cell = row.getCell(index); if (cell != null) { int type = cell.getCellType(); if (type == HSSFCell.CELL_TYPE_NUMERIC) { entry.addValue(col, Double.toString(cell.getNumericCellValue())); } else if (type != HSSFCell.CELL_TYPE_BLANK) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); entry.addValue(col, cell.getStringCellValue()); } } } return entry; }
From source file:model.bank.BankTransDAO.java
/** * Metodo que permiete importar las transacciones o movimientos de una cuenta bancaria * desde un archivo de excel emitido por el Banco Internacional * @param idBankAccount numero de id de la cuenta a la que se importaran los datos * @param fileXls <b>File</b> del archivo excel con los datos a importar * @param hacerArchivoLog//ww w . j a va 2 s . c o m * @return el numero de registros agregados * @throws java.lang.ClassNotFoundException * @throws java.sql.SQLException */ public static int importExcel(int idBankAccount, File fileXls, boolean hacerArchivoLog) throws ClassNotFoundException, SQLException { int rowsImported = 0; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando datos desde: " + fileXls.getAbsolutePath() + " - a idcuenta: " + idBankAccount); if (idBankAccount > 0 && fileXls != null) { int numRegImportados = 0; FileTxt archLog = null; if (fileXls.exists()) { // if (hacerArchivoLog) { // String nombreArchLog = fileXls.getAbsolutePath(); // nombreArchLog.replace(".xlsx", ".log"); // nombreArchLog.replace(".xls", ".log"); // archLog = new FileTxt(nombreArchLog); // DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando desde Excel. Archivo: " + archLog.getFile().getAbsolutePath() + "*******************"); // } try { // crear un stream POIFSFileSystem poiFS; poiFS = new POIFSFileSystem(new FileInputStream(fileXls)); // if (hacerArchivoLog) { // archLog.open(FileTxt.OpenMode.WRITE); // } // crear una hoja de excel HSSFWorkbook libro = new HSSFWorkbook(poiFS); HSSFSheet sheet = libro.getSheetAt(0); HSSFRow row; HSSFCell cell; Iterator itr = sheet.rowIterator(); // extraer la informacion a un arrayList int rowsCount = 0; BankTrans trans = new BankTrans(); while (itr.hasNext()) { // reviso fila por fila row = (HSSFRow) itr.next(); if (rowsCount >= 4) { // si la fila es la 4 o mayor importo los datos Iterator itc = row.cellIterator(); trans.setIdBankAccount(idBankAccount); trans.setValue(0.0); trans.setIdRegType(2); // <2> = registro importado int colCount = 0; double value = 0; while (itc.hasNext()) { // reviso celda por celda cell = (HSSFCell) itc.next(); // leo la informacion de la celda if (cell != null) { // si la celda no es nula switch (colCount) { case 0: // columna 0, nada break; case 1: // columna 1, fecha trans.setDate(DateTime.getStringToDateUtil(cell.getStringCellValue(), AppGlobal.getFormatDate())); break; case 2: // columna 2, tipo de transaccion String codTipo = cell.getStringCellValue().trim(); trans.setType(codTipo); Type btt = TypeDAO.get("banktranstypes", codTipo); if (btt != null) { trans.setIdType(btt.getId()); } else { String texto = "Tipo de transanccion bancaria no encontrada: " + codTipo + ". Creandola"; DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), texto); VMessage.show(texto); btt = new Type(); btt.setCode(codTipo); btt.setName(codTipo); btt.setDescription(codTipo); btt.setActive(true); if (TypeDAO.update("banktranstypes", btt) > 0) { Type btt1 = TypeDAO.get("banktranstypes", codTipo); if (btt1 != null) { trans.setIdType(btt1.getId()); DknConsole.msg( Thread.currentThread().getStackTrace()[1].toString(), "Creada y utilizando el Tipo de transaccin bancaria id: " + btt1.getId() + " trans.id: " + trans.getIdType()); } } else { DknConsole.error( Thread.currentThread().getStackTrace()[1].toString(), "Tipo de transaccin bancaria No se pudo crear. Cdigo: " + codTipo); } } break; case 3: // columna 3, numero de transaccin trans.setNumber(cell.getStringCellValue().trim()); break; case 4: // columna 4, nada break; case 5: // columna 5, observaciones trans.setObservations(cell.getStringCellValue().trim()); break; case 6: // columna 6, nada break; case 7: // columna 7, valor debito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); value *= -1; trans.setValue(value); } break; case 8: // columna 8, valor credito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); trans.setValue(value); } break; default: break; } // fin del switch } // Fin celda nula colCount++; } // Fin while de celdas // System.out.println("Agregando: " + trans); String error = BankTransDAO.validate(trans); if (error == null) { if (BankTransDAO.update(trans) >= 0) { numRegImportados++; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("Agregado: " + trans); } } else { DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), "NO agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO agregado: " + trans); } } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "NO paso la validacion: " + error + " - " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO paso la validacion: " + error + " - " + trans); } } } rowsCount++; } // Fin while de filas rowsImported = numRegImportados; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importacion completa. Total " + numRegImportados + " registros importados."); // grabar los datos en la base de datos } catch (FileNotFoundException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException | ParseException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } finally { if (hacerArchivoLog) { try { archLog.close(); } catch (IOException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } } } // JOptionPane.showMessageDialog(this, AppConfig.IMPORTACION_TEXTO, AppConfig.IMPORTACION_TITULO, JOptionPane.INFORMATION_MESSAGE); } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo no existe."); } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo o cuenta no indicadas."); } return rowsImported; }
From source file:net.ceos.project.poi.annotated.core.TestUtils.java
License:Apache License
public static void validationString(String string, XlsElement xlsAnnotation, HSSFCell headerCell, HSSFCell contentCell) {/*from w w w . ja va 2 s. co m*/ assertEquals(xlsAnnotation.title(), headerCell.getStringCellValue()); assertEquals(string, contentCell.getStringCellValue()); }
From source file:net.ceos.project.poi.annotated.core.TestUtils.java
License:Apache License
public static void validationDate(Date date, XlsElement xlsAnnotation, HSSFCell headerCell, HSSFCell contentCell) {/*w ww. j a va 2 s .c om*/ assertEquals(xlsAnnotation.title(), headerCell.getStringCellValue()); assertEquals(date, contentCell.getDateCellValue()); }
From source file:net.ceos.project.poi.annotated.core.TestUtils.java
License:Apache License
public static void validationNumeric(Integer integer, XlsElement xlsAnnotation, HSSFCell headerCell, HSSFCell contentCell) {/* ww w . j a v a 2s . c o m*/ assertEquals(xlsAnnotation.title(), headerCell.getStringCellValue()); Double d = contentCell.getNumericCellValue(); assertEquals(integer, Integer.valueOf(d.intValue())); }
From source file:net.vpc.app.vainruling.core.web.jsf.Vr.java
public void postProcessDataExporterXLS(Object document) { HSSFWorkbook book = (HSSFWorkbook) document; HSSFSheet sheet = book.getSheetAt(0); HSSFRow header = sheet.getRow(0);// w w w . j a va 2 s. c om int rowCount = sheet.getPhysicalNumberOfRows(); HSSFCellStyle headerCellStyle = book.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.AQUA.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); HSSFCreationHelper creationHelper = book.getCreationHelper(); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerCellStyle); } HSSFCellStyle intStyle = book.createCellStyle(); intStyle.setDataFormat((short) 1); HSSFCellStyle decStyle = book.createCellStyle(); decStyle.setDataFormat((short) 2); HSSFCellStyle dollarStyle = book.createCellStyle(); dollarStyle.setDataFormat((short) 5); int maxColumn = -1; Map<String, HSSFCellStyle> datFormats = new HashMap<>(); for (int rowInd = 1; rowInd < rowCount; rowInd++) { HSSFRow row = sheet.getRow(rowInd); int colCount = row.getPhysicalNumberOfCells(); if (maxColumn < colCount) { maxColumn = colCount; } for (int cellInd = 0; cellInd < colCount; cellInd++) { HSSFCell cell = row.getCell(cellInd); String strVal = cell.getStringCellValue(); if (strVal.startsWith("$")) { //do nothing } else { if (strVal.startsWith("'")) { strVal = strVal.substring(1); } if (PlatformUtils.isDouble(strVal)) { cell.setCellType(HSSFCell.CELL_TYPE_BLANK); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); if (PlatformUtils.isInteger(strVal)) { int intVal = Integer.valueOf(strVal.trim()); cell.setCellStyle(intStyle); cell.setCellValue(intVal); } else if (PlatformUtils.isDouble(strVal)) { double dblVal = Double.valueOf(strVal.trim()); cell.setCellStyle(decStyle); cell.setCellValue(dblVal); } } else { boolean isDate = false; for (String dteFormat : new String[] { "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM-dd", "HH:mm" }) { if (PlatformUtils.isDate(strVal, dteFormat)) { HSSFCellStyle dateStyle = datFormats.get(dteFormat.trim()); if (dateStyle == null) { dateStyle = book.createCellStyle(); dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dteFormat)); datFormats.put(dteFormat, dateStyle); } cell.setCellStyle(dateStyle); try { cell.setCellValue(new SimpleDateFormat(dteFormat).parse(strVal)); } catch (ParseException e) { // } isDate = true; break; } } } } } } if (maxColumn >= 0) { for (int cellInd = 0; cellInd < maxColumn; cellInd++) { sheet.autoSizeColumn(cellInd); } } }
From source file:no.abmu.abmstatistikk.annualstatistic.util.ExcelWithLibraryInformationAndDataParser.java
License:Open Source License
/** * Returns the value of the specified column as a String. * * @param row/*from w ww .j av a2 s . c o m*/ * @param columnIndex * @return */ protected String getStringValue(HSSFRow row, Short columnIndex) { HSSFCell cell; if (columnIndex == null) { return null; } cell = row.getCell(columnIndex.shortValue()); if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_NUMERIC: /* For now - convert to long */ return Long.toString(new Double(cell.getNumericCellValue()).longValue()); case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return null; } }