List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper
@Override
public XSSFCreationHelper getCreationHelper()
From source file:logic.Export.java
public boolean convertXls() throws IOException, FileNotFoundException, IllegalArgumentException, ParseException { FileInputStream tamplateFile = new FileInputStream(templatePath); XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##")); double hours = 0.0; NumberFormat format = NumberFormat.getInstance(Locale.FRANCE); Number number;/* w ww.j a v a 2 s. com*/ XSSFSheet sheet; XSSFSheet sheet2; Cell cell = null; ConvertData cd = new ConvertData(); for (int i = 0; i < cd.getSheetnames().size(); i++) { sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i)); sheet = workbook.getSheetAt(i + 1); //formate sheets sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); cell = sheet.getRow(0).getCell(1); cell.setCellValue(cd.sheetnames.get(i)); ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i)); //setPrintArea workbook.setPrintArea(i + 1, //sheet index 0, //start column Spalte 6, //end column 0, //start row zeile convert.size() + 8 //end row ); for (int Row = 0; Row < convert.size(); Row++) { for (int Cell = 0; Cell < convert.get(Row).length; Cell++) { cell = sheet.getRow(9 + Row).getCell(Cell); if (Cell == 3) { if ("true".equals(convert.get(Row)[Cell])) { XSSFCellStyle style1 = workbook.createCellStyle(); style1 = (XSSFCellStyle) cell.getCellStyle(); style1 = (XSSFCellStyle) style1.clone(); style1.setFillBackgroundColor(HSSFColor.RED.index); style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); XSSFColor myColor = new XSSFColor(Color.RED); style1.setFillForegroundColor(myColor); sheet.getRow(9 + Row).getCell(6).setCellStyle(style1); } } else { cell.setCellValue(convert.get(Row)[Cell]); } } } } workbook.removeSheetAt(0); tamplateFile.close(); File exportFile = newPath.getSelectedFile(); if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) { } else { exportFile = new File(exportFile.getParentFile(), FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx"); } FileOutputStream outFile = new FileOutputStream(exportFile); workbook.write(outFile); outFile.close(); tamplateFile.close(); return true; }
From source file:logic.Export.java
public boolean convertXls2() throws IOException, FileNotFoundException, IllegalArgumentException, ParseException { FileInputStream tamplateFile = new FileInputStream(templatePath); XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##")); double hours = 0.0; NumberFormat format = NumberFormat.getInstance(Locale.FRANCE); Number number;//from w w w.ja va 2s .c o m XSSFSheet sheet; XSSFSheet sheet2; Cell cell = null; ConvertData cd = new ConvertData(); for (int i = 0; i < cd.getSheetnames().size(); i++) { sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i)); sheet = workbook.getSheetAt(i + 1); cell = sheet.getRow(0).getCell(1); cell.setCellValue(cd.sheetnames.get(i)); ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i)); for (int Row = 0; Row < convert.size(); Row++) { for (int Cell = 0; Cell < convert.get(Row).length; Cell++) { cell = sheet.getRow(9 + Row).getCell(Cell + 1); String name; switch (Cell) { case 3: name = convert.get(Row)[Cell]; int parseInt = Integer.parseInt(name); cell.setCellValue(parseInt); cell.setCellType(CellType.NUMERIC); break; case 4: number = format.parse(convert.get(Row)[Cell]); cell.setCellValue(number.doubleValue()); // cell.setCellStyle(cellStyle); cell.setCellType(CellType.NUMERIC); break; default: cell.setCellValue(convert.get(Row)[Cell]); break; } } } for (String[] sa : convert) { number = format.parse(sa[4]); hours = hours + number.doubleValue(); } cell = sheet.getRow(6).getCell(5); cell.setCellValue(hours); cell = sheet.getRow(2).getCell(8); XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8); double numericCellValue = cellHourlyRate.getNumericCellValue(); cell.setCellValue(hours * numericCellValue); } workbook.removeSheetAt(0); tamplateFile.close(); File exportFile = newPath.getSelectedFile(); if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) { } else { exportFile = new File(exportFile.getParentFile(), FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx"); } FileOutputStream outFile = new FileOutputStream(exportFile); workbook.write(outFile); outFile.close(); tamplateFile.close(); return true; }
From source file:mil.tatrc.physiology.datamodel.dataset.DataSetReader.java
License:Apache License
public static void loadData(String directoryName, String fileName) { try {//from w w w . j ava 2s . c o m // Delete current dir contents FileUtils.delete(new File("./substances/")); FileUtils.delete(new File("./patients/")); FileUtils.delete(new File("./environments/")); FileUtils.delete(new File("./nutrition/")); FileUtils.delete(new File("./config/")); // Ok, let's make them again FileUtils.createDirectory("./substances/"); FileUtils.createDirectory("./patients/"); FileUtils.createDirectory("./environments/"); FileUtils.createDirectory("./nutrition/"); FileUtils.createDirectory("./config/"); } catch (Exception ex) { Log.error("Unable to clean directories"); return; } try { File xls = new File(directoryName + "/" + fileName); if (!xls.exists()) { Log.error("Could not find xls file " + directoryName + "/" + fileName); return; } FileInputStream xlFile = new FileInputStream(directoryName + "/" + fileName); XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); evaluator = xlWBook.getCreationHelper().createFormulaEvaluator(); List<SEPatient> patients = readPatients(xlWBook.getSheet("Patients")); for (SEPatient p : patients) { Log.info("Writing patient : " + p.getName()); CDMSerializer.writeFile("./patients/" + p.getName() + ".xml", p.unload()); } Map<String, SESubstance> substances = readSubstances(xlWBook.getSheet("Substances")); for (SESubstance s : substances.values()) { Log.info("Writing substance : " + s.getName()); CDMSerializer.writeFile("./substances/" + s.getName() + ".xml", s.unload()); } List<SESubstanceCompound> compounds = readCompounds(xlWBook.getSheet("Compounds"), substances); for (SESubstanceCompound c : compounds) { Log.info("Writing compound : " + c.getName()); CDMSerializer.writeFile("./substances/" + c.getName() + ".xml", c.unload()); } Map<String, SEEnvironmentalConditions> environments = readEnvironments(xlWBook.getSheet("Environment"), substances); for (String name : environments.keySet()) { Log.info("Writing environment : " + name); environments.get(name).trim();//Removes zero amount ambient substances CDMSerializer.writeFile("./environments/" + name + ".xml", environments.get(name).unload()); } Map<String, SENutrition> meals = readNutrition(xlWBook.getSheet("Nutrition")); for (String name : meals.keySet()) { Log.info("Writing nutrition : " + name); CDMSerializer.writeFile("./nutrition/" + name + ".xml", meals.get(name).unload()); } Map<String, PhysiologyEngineStabilization> stabilization = readStabilization( xlWBook.getSheet("Stabilization")); for (String name : stabilization.keySet()) { Log.info("Writing stabilization : " + name); CDMSerializer.writeFile("./config/" + name + ".xml", stabilization.get(name).unload()); } xlWBook.close(); } catch (Exception ex) { Log.error("Error reading XSSF : " + directoryName + "/" + fileName, ex); return; } }
From source file:mil.tatrc.physiology.utilities.testing.validation.ValdiationTool.java
License:Apache License
public void loadData(String revision, String env, String arch, boolean sendEmail) { String directoryName = DEFAULT_DIRECTORY; String fileName = DEFAULT_FILE; String destinationDirectory = DEST_DIRECTORY; try {/*from ww w . ja v a 2s.co m*/ File dest = new File(DEST_DIRECTORY); dest.mkdir(); // Delete current dir contents // FileUtils.delete(destinationDirectory); // Ok, let's make them again // FileUtils.createDirectory(destinationDirectory); } catch (Exception ex) { Log.error("Unable to clean directories"); return; } try { File xls = new File(directoryName + "/" + fileName); if (!xls.exists()) { Log.error("Could not find xls file " + directoryName + "/" + fileName); return; } // Read in props file File file = new File("ValidationTables.config"); FileInputStream fileInput = new FileInputStream(file); Properties config = new Properties(); config.load(fileInput); fileInput.close(); // Set up the Email object String hostname = "Unknown"; try { InetAddress addr = InetAddress.getLocalHost(); hostname = addr.getHostName(); } catch (Exception ex) { System.out.println("Hostname can not be resolved"); } EmailUtil email = new EmailUtil(); String subj = env + " " + arch + " " + TABLE_TYPE + " Validation from " + hostname + " Revision " + revision; email.setSubject(subj); email.setSender(config.getProperty("sender")); email.setSMTP(config.getProperty("smtp")); if (hostname.equals(config.get("buildhost"))) { Log.info("Emailling all recipients " + subj); for (String recipient : config.getProperty("recipients").split(",")) email.addRecipient(recipient.trim()); } else {// Running on your own machine, just send it to yourself Log.info("Emailling local runner " + subj); email.addRecipient(System.getProperty("user.name") + "@ara.com"); } html.append("<html>"); html.append("<body>"); // Get a list of all the results files we have to work with File vdir = new File("./Scenarios/Validation/"); String[] vFiles = vdir.list(); // Now read in the spreadsheet FileInputStream xlFile = new FileInputStream(directoryName + "/" + fileName); XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); FormulaEvaluator evaluator = xlWBook.getCreationHelper().createFormulaEvaluator(); List<ValidationRow> badSheets = new ArrayList<ValidationRow>(); Map<String, List<ValidationRow>> tables = new HashMap<String, List<ValidationRow>>(); Map<String, List<ValidationRow>> tableErrors = new HashMap<String, List<ValidationRow>>(); List<ValidationRow> allRows = new ArrayList<ValidationRow>(); for (int i = 0; i < xlWBook.getNumberOfSheets(); i++) { XSSFSheet xlSheet = xlWBook.getSheetAt(i); Log.info("Processing Sheet : " + xlSheet.getSheetName()); String sheetName = xlSheet.getSheetName().trim().replaceAll(" ", ""); List<String> sheetFiles = new ArrayList<String>(); String rSheetName = sheetName + "ValidationResults.txt"; File rFile = new File(rSheetName); if (!rFile.exists()) { // Search for any file starting with the sheet name for (String f : vFiles) if (f.startsWith(sheetName) && f.endsWith(".txt")) sheetFiles.add(f); } else sheetFiles.add(rSheetName); for (String resultsName : sheetFiles) { Log.info("Processing " + resultsName); try { // Look for a results file CSVContents results = new CSVContents("./Scenarios/Validation/" + resultsName); results.readAll(resultData); // Find any assessments assessments = new HashMap<String, SEPatientAssessment>(); for (String vFile : vFiles) { if (vFile.indexOf(sheetName) > -1 && vFile.indexOf('@') > -1) { Object aData = CDMSerializer.readFile("./Scenarios/Validation/" + vFile); if (aData instanceof PatientAssessmentData) { String aClassName = "SE" + aData.getClass().getSimpleName(); aClassName = aClassName.substring(0, aClassName.indexOf("Data")); try { Class<?> aClass = Class.forName( "mil.tatrc.physiology.datamodel.patient.assessments." + aClassName); SEPatientAssessment a = (SEPatientAssessment) aClass.newInstance(); aClass.getMethod("load", aData.getClass()).invoke(a, aData); assessments.put(vFile, a); } catch (Exception ex) { Log.error("Unable to load assesment xml " + vFile, ex); } } else Log.error(vFile + " is named like a patient assessment, but its not?"); } } } catch (Exception ex) { ValidationRow vRow = new ValidationRow(); vRow.header = sheetName; vRow.error = danger + "No results found for sheet " + endSpan; badSheets.add(vRow); continue; } // Is this patient validation? patient = null; if (TABLE_TYPE.equals("Patient")) { // Patient Name is encoded in the naming convention (or else it needs to be) String patientName = resultsName.substring(resultsName.lastIndexOf("-") + 1, resultsName.indexOf("Results")); patient = new SEPatient(); patient.load((PatientData) CDMSerializer.readFile("./stable/" + patientName + ".xml")); } allRows.clear(); tables.clear(); tableErrors.clear(); // Read the sheet and process all the validation data rows try { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; int cells = 11;//row.getPhysicalNumberOfCells(); XSSFCell cell = row.getCell(0); if (cell == null) continue; // Check to see if this row is a header String cellValue = cell.getStringCellValue(); if (cellValue == null || cellValue.isEmpty()) continue;// No property, skip it cellValue = row.getCell(1).getStringCellValue(); if (cellValue != null && cellValue.equals("Units")) continue;// Header ValidationRow vRow = new ValidationRow(); allRows.add(vRow); for (int c = 0; c <= cells; c++) { cellValue = null; cell = row.getCell(c); if (cell == null) continue; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_FORMULA: switch (evaluator.evaluateFormulaCell(cell)) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = String.format("%." + 3 + "g", cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } switch (c) { case 0://A Log.info("Processing " + cellValue); vRow.name = cellValue.trim().replaceAll(" ", ""); String prop = vRow.name; if (vRow.name.indexOf('*') != -1) prop = prop.substring(0, prop.length() - 1); vRow.header = vRow.name; break; case 1://B if (cellValue != null && !cellValue.equalsIgnoreCase("none") && !cellValue.equalsIgnoreCase("n\\a") && !cellValue.equalsIgnoreCase("n/a")) { vRow.unit = cellValue; } if (vRow.unit != null && !vRow.unit.isEmpty()) vRow.header += "(" + vRow.unit + ")"; break; case 2://C if (cellValue != null) { String unit = null; int u = cellValue.indexOf("("); if (u > -1) { unit = cellValue.substring(u + 1, cellValue.indexOf(")")); cellValue = cellValue.substring(0, u); } vRow.dType = DataType.valueOf(cellValue); if (vRow.dType == DataType.MeanPerWeight || vRow.dType == DataType.WaveformMinPerWeight || vRow.dType == DataType.WaveformMaxPerWeight) { vRow.weightUnit = unit; } } break; case 3://D // Replace any return characters with empty if (patient != null && vRow.name.indexOf('*') == -1) { try { Method has = SEPatient.class.getMethod("has" + vRow.name); if ((Boolean) has.invoke(patient)) { Method get = SEPatient.class.getMethod("get" + vRow.name); SEScalar s = ((SEScalar) get.invoke(patient)); vRow.refValue = s.getValue(vRow.unit); vRow.refValues = cellValue; break; } else { Log.error("Patient does not have a value for " + vRow.name); } } catch (Exception ex) { // Nothing to do, row is not a patient property } } if (cellValue == null) vRow.refValues = null; else vRow.refValues = cellValue.replace("\n", ""); break; case 4://E // Replace any return characters with empty if (cellValue != null) cellValue = cellValue.replace("\n", ""); vRow.refCites = cellValue; break; case 5://F Reference Page (Internal only) break; case 6://G Notes if (cellValue != null) vRow.notes = cellValue; break;// Skipping for now case 7://H Internal Notes (Internal only) break; case 8://I Reading (Internal only) break; case 9://J Table (Internal only) if (cellValue == null) cellValue = ""; vRow.table = cellValue; if (patient != null) vRow.table = patient.getName() + "Patient" + cellValue; break; case 10://K ResultFile (Internal only) if (cellValue != null) vRow.resultFile = cellValue; break; case 11://L Mantissa Digits if (cellValue != null) vRow.doubleFormat = cellValue; if (patient != null && vRow.dType != DataType.Patient2SystemMean) vRow.refValues = String.format("%." + vRow.doubleFormat, vRow.refValue); break; } } } } catch (Exception ex) { Log.error("Error reading row", ex); ValidationRow vRow = new ValidationRow(); vRow.header = sheetName; vRow.error = danger + "Sheet has errors" + endSpan; badSheets.add(vRow); continue; } // Sort all of our rows, and validate them for (ValidationRow vRow : allRows) { if (vRow.table.isEmpty()) vRow.table = sheetName;//Default table is the sheet name if (!tables.containsKey(vRow.table)) tables.put(vRow.table, new ArrayList<ValidationRow>()); if (!tableErrors.containsKey(vRow.table)) tableErrors.put(vRow.table, new ArrayList<ValidationRow>()); if (buildExpectedHeader(vRow)) { Log.info("Validating " + vRow.header); if (validate(vRow)) { tables.get(vRow.table).add(vRow); } else tableErrors.get(vRow.table).add(vRow); } else tableErrors.get(vRow.table).add(vRow); } for (String name : tables.keySet()) { if (name.contains("All")) continue; List<ValidationRow> t = tables.get(name); WriteHTML(t, name); WriteDoxyTable(t, name, destinationDirectory); if (name.equalsIgnoreCase(sheetName)) { List<String> properties = new ArrayList<String>(); for (ValidationRow vRow : t) properties.add(vRow.name); for (ValidationRow vRow : tableErrors.get(name)) properties.add(vRow.name); CrossCheckValidationWithSchema(properties, tableErrors.get(name), name); } WriteHTML(tableErrors.get(name), name + "Errors"); if (patient != null) CustomMarkdown(patient.getName(), destinationDirectory); } } } xlWBook.close(); WriteHTML(badSheets, fileName + " Errors"); html.append("</body>"); html.append("</html>"); if (sendEmail) email.sendHTML(html.toString()); } catch (Exception ex) { Log.error("Error processing spreadsheet " + fileName, ex); } // Just for fun, I am going to create a single md file with ALL the tables in it try { String line; File vDir = new File(destinationDirectory); PrintWriter writer = new PrintWriter(destinationDirectory + "/AllValidationTables.md", "UTF-8"); for (String fName : vDir.list()) { if (fName.equals("AllValidationTables.md")) continue; if (new File(fName).isDirectory()) continue; FileReader in = new FileReader(destinationDirectory + "/" + fName); BufferedReader inFile = new BufferedReader(in); writer.println(fName); while ((line = inFile.readLine()) != null) writer.println(line); inFile.close(); writer.println("<br>"); } writer.close(); } catch (Exception ex) { Log.error("Unable to create single validation table file.", ex); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/* w w w. j ava 2 s . c o m*/ public void arreglaFechas(OutputStream out) { log.debug("Arreglando fechas"); Date inicio = new Date(); XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); XSSFSheet fechas = wb.createSheet("FECHAS-ANTERIORES"); int fechasRow = 0; XSSFSheet fechas2 = wb.createSheet("FECHAS-POSTERIORES"); int fechas2Row = 0; Transaction tx = null; try { tx = currentSession().beginTransaction(); Query update = currentSession() .createQuery("update Activo set fechaCompra = :fechaCompra where id = :id"); Query query = currentSession().createQuery( "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra < :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo"); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); query.setDate("fechaCompra", sdf.parse("01/01/1970")); List<Activo> activos = query.list(); int cont = 0; for (Activo activo : activos) { Calendar cal1 = Calendar.getInstance(); cal1.setTime(activo.getFechaCompra()); if (cal1.get(Calendar.YEAR) < 10) { log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 2000); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) < 100) { log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 1900); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) >= 1900 && cal1.get(Calendar.YEAR) <= 1912) { log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 100); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } cont++; } currentSession().flush(); query = currentSession().createQuery( "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra > :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo"); query.setDate("fechaCompra", new Date()); activos = query.list(); for (Activo activo : activos) { Calendar cal1 = Calendar.getInstance(); cal1.setTime(activo.getFechaCompra()); if (cal1.get(Calendar.YEAR) < 2020) { log.debug("Quitandole 10 anios {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, -10); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas2.createRow(fechas2Row++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) >= 2020) { log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, -100); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas2.createRow(fechas2Row++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } cont++; } currentSession().flush(); tx.commit(); log.debug("Termino actualizando {} de {} en {}", new Object[] { cont, activos.size(), ((new Date().getTime() - inicio.getTime()) / 1000) }); wb.write(out); } catch (ParseException | HibernateException | IOException e) { log.error("No se pudieron arreglar las fechas de los activos", e); tx.rollback(); throw new RuntimeException("No se pudieron arreglar las fechas de los actios", e); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public void sube(byte[] datos, Usuario usuario, OutputStream out, Integer codigoInicial) { Date inicio = new Date(); int idx = 5;/* www.j av a 2s . c om*/ int i = 0; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yy"); SimpleDateFormat sdf3 = new SimpleDateFormat("dd-MM-yy"); MathContext mc = new MathContext(16, RoundingMode.HALF_UP); NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(0); nf.setMinimumIntegerDigits(5); Transaction tx = null; try { String ejercicioId = "001-2013"; Map<String, CentroCosto> centrosDeCosto = new HashMap<>(); Map<String, TipoActivo> tipos = new HashMap<>(); Query tipoActivoQuery = currentSession() .createQuery("select ta from TipoActivo ta " + "where ta.empresa.id = :empresaId " + "and ta.cuenta.id.ejercicio.id.idEjercicio = :ejercicioId " + "and ta.cuenta.id.ejercicio.id.organizacion.id = :organizacionId"); log.debug("empresaId: {}", usuario.getEmpresa().getId()); log.debug("ejercicioId: {}", ejercicioId); log.debug("organizacionId: {}", usuario.getEmpresa().getOrganizacion().getId()); tipoActivoQuery.setLong("empresaId", usuario.getEmpresa().getId()); tipoActivoQuery.setString("ejercicioId", ejercicioId); tipoActivoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); List<TipoActivo> listaTipos = tipoActivoQuery.list(); for (TipoActivo tipoActivo : listaTipos) { tipos.put(tipoActivo.getCuenta().getId().getIdCtaMayor(), tipoActivo); } log.debug("TIPOS: {}", tipos); Query proveedorQuery = currentSession().createQuery( "select p from Proveedor p where p.empresa.id = :empresaId and p.nombre = :nombreEmpresa"); proveedorQuery.setLong("empresaId", usuario.getEmpresa().getId()); proveedorQuery.setString("nombreEmpresa", usuario.getEmpresa().getNombre()); Proveedor proveedor = (Proveedor) proveedorQuery.uniqueResult(); Query codigoDuplicadoQuery = currentSession() .createQuery("select a from Activo a where a.empresa.id = :empresaId and a.codigo = :codigo"); XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(datos)); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ccostoFantasma = wb.createSheet("CCOSTO-FANTASMAS"); int ccostoFantasmaRow = 0; XSSFSheet sinCCosto = wb.createSheet("SIN-CCOSTO"); int sinCCostoRow = 0; XSSFSheet codigoAsignado = wb.createSheet("CODIGO-ASIGNADO"); int codigoAsignadoRow = 0; XSSFSheet fechaInvalida = wb.createSheet("FECHA-INVALIDA"); int fechaInvalidaRow = 0; XSSFSheet sinCosto = wb.createSheet("SIN-COSTO"); int sinCostoRow = 0; //tx = currentSession().beginTransaction(); for (idx = 5; idx <= 5; idx++) { XSSFSheet sheet = workbook.getSheetAt(idx); int rows = sheet.getPhysicalNumberOfRows(); for (i = 2; i < rows; i++) { log.debug("Leyendo pagina {} renglon {}", idx, i); XSSFRow row = sheet.getRow(i); if (row.getCell(0) == null) { break; } String nombreGrupo = row.getCell(0).getStringCellValue().trim(); switch (row.getCell(0).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: nombreGrupo = row.getCell(0).toString().trim(); break; case XSSFCell.CELL_TYPE_STRING: nombreGrupo = row.getCell(0).getStringCellValue().trim(); break; } TipoActivo tipoActivo = tipos.get(nombreGrupo); if (tipoActivo != null) { String cuentaCCosto = row.getCell(2).toString().trim(); if (StringUtils.isNotBlank(cuentaCCosto)) { CentroCosto centroCosto = centrosDeCosto.get(cuentaCCosto); if (centroCosto == null) { Query ccostoQuery = currentSession().createQuery("select cc from CentroCosto cc " + "where cc.id.ejercicio.id.idEjercicio = :ejercicioId " + "and cc.id.ejercicio.id.organizacion.id = :organizacionId " + "and cc.id.idCosto like :idCosto"); ccostoQuery.setString("ejercicioId", ejercicioId); ccostoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); ccostoQuery.setString("idCosto", "1.01." + cuentaCCosto); ccostoQuery.setMaxResults(1); List<CentroCosto> listaCCosto = ccostoQuery.list(); if (listaCCosto != null && listaCCosto.size() > 0) { centroCosto = listaCCosto.get(0); } if (centroCosto == null) { XSSFRow renglon = ccostoFantasma.createRow(ccostoFantasmaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } centrosDeCosto.put(cuentaCCosto, centroCosto); } String poliza = null; switch (row.getCell(4).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: poliza = row.getCell(4).toString(); poliza = StringUtils.removeEnd(poliza, ".0"); log.debug("POLIZA-N: {}", poliza); break; case XSSFCell.CELL_TYPE_STRING: poliza = row.getCell(4).getStringCellValue().trim(); log.debug("POLIZA-S: {}", poliza); break; } Boolean seguro = false; if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) { seguro = true; } Boolean garantia = false; if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) { garantia = true; } Date fechaCompra = null; if (row.getCell(7) != null) { log.debug("VALIDANDO FECHA"); XSSFCell cell = row.getCell(7); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: log.debug("ES NUMERIC"); if (DateUtil.isCellDateFormatted(cell)) { log.debug("ES FECHA"); fechaCompra = cell.getDateCellValue(); } else if (DateUtil.isCellInternalDateFormatted(cell)) { log.debug("ES FECHA INTERNAL"); fechaCompra = cell.getDateCellValue(); } else { BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); bd = stripTrailingZeros(bd); log.debug("CONVIRTIENDO DOUBLE {} - {}", DateUtil.isValidExcelDate(bd.doubleValue()), bd); fechaCompra = HSSFDateUtil.getJavaDate(bd.longValue(), true); log.debug("Cal: {}", fechaCompra); } break; case Cell.CELL_TYPE_FORMULA: log.debug("ES FORMULA"); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { fechaCompra = DateUtil.getJavaDate(cellValue.getNumberValue(), true); } } } } if (row.getCell(7) != null && fechaCompra == null) { String fechaCompraString; if (row.getCell(7).getCellType() == Cell.CELL_TYPE_STRING) { fechaCompraString = row.getCell(7).getStringCellValue(); } else { fechaCompraString = row.getCell(7).toString().trim(); } try { fechaCompra = sdf.parse(fechaCompraString); } catch (ParseException e) { try { fechaCompra = sdf2.parse(fechaCompraString); } catch (ParseException e2) { try { fechaCompra = sdf3.parse(fechaCompraString); } catch (ParseException e3) { // no se pudo convertir } } } } if (fechaCompra == null) { XSSFRow renglon = fechaInvalida.createRow(fechaInvalidaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } String codigo = null; switch (row.getCell(8).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: codigo = row.getCell(8).toString(); break; case XSSFCell.CELL_TYPE_STRING: codigo = row.getCell(8).getStringCellValue().trim(); break; } if (StringUtils.isBlank(codigo)) { codigo = "SIN CODIGO" + nf.format(codigoInicial); XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue("SIN CODIGO" + codigoInicial); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigoInicial++; } else { // busca codigo duplicado if (codigo.contains(".")) { codigo = codigo.substring(0, codigo.lastIndexOf(".")); log.debug("CODIGO: {}", codigo); } codigoDuplicadoQuery.setLong("empresaId", usuario.getEmpresa().getId()); codigoDuplicadoQuery.setString("codigo", codigo); Activo activo = (Activo) codigoDuplicadoQuery.uniqueResult(); if (activo != null) { XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9) .setCellValue(codigo + "-" + "SIN CODIGO" + nf.format(codigoInicial)); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigo = "SIN CODIGO" + nf.format(codigoInicial); codigoInicial++; } } String descripcion = null; if (row.getCell(9) != null) { switch (row.getCell(9).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: descripcion = row.getCell(9).toString(); descripcion = StringUtils.removeEnd(descripcion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: descripcion = row.getCell(9).getStringCellValue().trim(); break; default: descripcion = row.getCell(9).toString().trim(); } } String marca = null; if (row.getCell(10) != null) { switch (row.getCell(10).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: marca = row.getCell(10).toString(); marca = StringUtils.removeEnd(marca, ".0"); break; case XSSFCell.CELL_TYPE_STRING: marca = row.getCell(10).getStringCellValue().trim(); break; default: marca = row.getCell(10).toString().trim(); } } String modelo = null; if (row.getCell(11) != null) { switch (row.getCell(11).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: modelo = row.getCell(11).toString(); modelo = StringUtils.removeEnd(modelo, ".0"); break; case XSSFCell.CELL_TYPE_STRING: modelo = row.getCell(11).getStringCellValue().trim(); break; default: modelo = row.getCell(11).toString().trim(); } } String serie = null; if (row.getCell(12) != null) { switch (row.getCell(12).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: serie = row.getCell(12).toString(); serie = StringUtils.removeEnd(serie, ".0"); break; case XSSFCell.CELL_TYPE_STRING: serie = row.getCell(12).getStringCellValue().trim(); break; default: serie = row.getCell(12).toString().trim(); } } String responsable = null; if (row.getCell(13) != null) { switch (row.getCell(13).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: responsable = row.getCell(13).toString(); responsable = StringUtils.removeEnd(responsable, ".0"); break; case XSSFCell.CELL_TYPE_STRING: responsable = row.getCell(13).getStringCellValue().trim(); break; default: responsable = row.getCell(13).toString().trim(); } } String ubicacion = null; if (row.getCell(14) != null) { switch (row.getCell(14).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: ubicacion = row.getCell(14).toString(); ubicacion = StringUtils.removeEnd(ubicacion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: ubicacion = row.getCell(14).getStringCellValue().trim(); break; default: ubicacion = row.getCell(14).toString().trim(); } } BigDecimal costo = null; switch (row.getCell(15).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: costo = new BigDecimal(row.getCell(15).getNumericCellValue(), mc); log.debug("COSTO-N: {} - {}", costo, row.getCell(15).getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: costo = new BigDecimal(row.getCell(15).toString(), mc); log.debug("COSTO-S: {} - {}", costo, row.getCell(15).toString()); break; case XSSFCell.CELL_TYPE_FORMULA: costo = new BigDecimal( evaluator.evaluateInCell(row.getCell(15)).getNumericCellValue(), mc); log.debug("COSTO-F: {}", costo); } if (costo == null) { XSSFRow renglon = sinCosto.createRow(sinCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } Activo activo = new Activo(fechaCompra, seguro, garantia, poliza, codigo, descripcion, marca, modelo, serie, responsable, ubicacion, costo, tipoActivo, centroCosto, proveedor, usuario.getEmpresa()); this.crea(activo, usuario); } else { XSSFRow renglon = sinCCosto.createRow(sinCCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } } else { throw new RuntimeException( "(" + idx + ":" + i + ") No se encontro el tipo de activo " + nombreGrupo); } } } //tx.commit(); log.debug("################################################"); log.debug("################################################"); log.debug("TERMINO EN {} MINS", (new Date().getTime() - inicio.getTime()) / (1000 * 60)); log.debug("################################################"); log.debug("################################################"); wb.write(out); } catch (IOException | RuntimeException e) { //if (tx != null && tx.isActive()) { //tx.rollback(); //} log.error("Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); throw new RuntimeException( "Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); } }
From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java
License:Open Source License
private static void addImage(XSSFWorkbook workbook, XSSFSheet worksheet, File file, int rowNum) throws IOException { //add picture data to this workbook. InputStream is = new FileInputStream(file); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); is.close();//from ww w . j a v a 2s .com XSSFDrawing drawing = worksheet.createDrawingPatriarch(); //add a picture shape XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor(); anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(0); anchor.setRow1(rowNum); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); //get the picture width int pictWidthPx = pict.getImageDimension().width; int pictHeightPt = pict.getImageDimension().height; //get the cell width float cellWidthPx = worksheet.getColumnWidthInPixels(0); float cellHeightPx = ConvertImageUnits.heightUnits2Pixel(worksheet.getRow(rowNum).getHeight()); //calculate the center position int centerPosPx = Math.round(cellWidthPx / 2f - (float) pictWidthPx / 2f); int centerPosPy = Math.round(cellHeightPx / 2f - (float) pictHeightPt / 2f + 10); //set the new upper left anchor position anchor.setCol1(0); //set the remaining pixels up to the center position as Dx in unit EMU anchor.setDx1(centerPosPx * Units.EMU_PER_PIXEL); anchor.setDy1(centerPosPy * Units.EMU_PER_PIXEL); //resize the pictutre to original size again //this will determine the new bottom rigth anchor position pict.resize(); }
From source file:offishell.excel.Excel.java
License:MIT License
/** * <p>/*from w w w . jav a2s.c om*/ * Create {@link Excel} wrapper. * </p> * * @param path * @param book */ private Excel(Path path, XSSFWorkbook book) { this.path = path; this.book = book; this.excel = Locator.file(path); this.sheet = book.getSheetAt(0); this.baseStyle = book.createCellStyle(); this.dateStyle = book.createCellStyle(); CreationHelper helper = book.getCreationHelper(); DataFormat dateFormat = helper.createDataFormat(); Font font = book.createFont(); font.setFontName(" Medium"); font.setFontHeightInPoints((short) 10); baseStyle.setFont(font); baseStyle.setAlignment(HorizontalAlignment.CENTER); baseStyle.setVerticalAlignment(VerticalAlignment.CENTER); baseStyle.setShrinkToFit(true); baseStyle.setWrapText(true); dateStyle.cloneStyleFrom(baseStyle); dateStyle.setDataFormat(dateFormat.getFormat("yyyy/mm/dd")); }
From source file:opn.greenwebs.FXMLDocumentController.java
@FXML private void handleInjectExcel(ActionEvent event) { XSSFWorkbook wb = createWorkbook(); FormulaEvaluator evaluator;//ww w.j ava 2 s .c o m inject(wb, cmbSales.getValue(), 5, 14); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); inject(wb, Date.from(instant), 3, 14); inject(wb, txtCustomer.getText(), 10, 2); inject(wb, txtAddress.getText(), 11, 2); inject(wb, txtCity.getText(), 12, 2); inject(wb, txtProvince.getText(), 13, 2); inject(wb, txtPhone.getText(), 14, 2); inject(wb, txtContact.getText(), 15, 2); inject(wb, txtFax.getText(), 14, 4); inject(wb, txtEmail.getText(), 16, 2); inject(wb, txtPO.getText(), 15, 4); if (chkGST.isSelected()) { inject(wb, "Y", 36, 8); } else { inject(wb, "N", 36, 8); } if (chkPST.isSelected()) { inject(wb, "Y", 37, 8); } else { inject(wb, "N", 37, 8); } int nRow = 21; for (Object obj : tblItems.getItems()) { if (obj instanceof Item) { Item item = (Item) obj; inject(wb, item.getSdpQty(), nRow, 0); inject(wb, item.getSspMfr(), nRow, 1); inject(wb, item.getSspSKU(), nRow, 2); inject(wb, item.getSspDescrip(), nRow, 3); inject(wb, item.getSspSupplier(), nRow, 5); inject(wb, item.getSspSupPart(), nRow, 6); inject(wb, item.getSspSerial(), nRow, 7); inject(wb, item.getSdpSalePrice(), nRow, 8); inject(wb, item.getSdpEOS(), nRow++, 9); } } evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); wb.setForceFormulaRecalculation(true); try { File fTemp = createFilename("Q", txtCustomer.getText()); inject(wb, fTemp.getName().substring(0, 7), 46, 14); try (FileOutputStream fos = new FileOutputStream(fTemp)) { wb.write(fos); } Alert alert = new Alert(AlertType.INFORMATION, "Would you like to open the file? Choose no to print", ButtonType.YES, ButtonType.NO); alert.showAndWait().ifPresent(response -> { CustomerDB cust = new CustomerDB(); cust.setStrCustomer(txtCustomer.getText()); cust.setStrAddress(txtAddress.getText()); cust.setStrCity(txtCity.getText()); cust.setStrProvince(txtProvince.getText()); cust.setStrPhone(txtPhone.getText()); cust.setStrEmail(txtEmail.getText()); cust.setStrFax(txtFax.getText()); cust.setStrContact(txtContact.getText()); cust.setStrPO(txtPO.getText()); cust.setBooGST(chkGST.isSelected()); cust.setBooPST(chkPST.isSelected()); if ((aDataCust.query("txtCustomer", cust.getStrCustomer()).isEmpty()) && (aDataCust.query("txtPhone", cust.getStrPhone()).isEmpty())) { aDataCust.addItem(cust); CustData.add(cust.createCustomer()); } if (response == ButtonType.YES) { try { Desktop.getDesktop().open(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } else { try { Desktop.getDesktop().print(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } }); } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } }
From source file:opn.greenwebs.FXMLDocumentController.java
private void inject(XSSFWorkbook wb, Object obj, int row, int col) { if (wb == null) { System.out.println("wb is null"); }/*from w ww. j a va2 s . c om*/ XSSFSheet sheet = wb.getSheet("Digital Version"); Row rowed = sheet.getRow(row); Cell cell = rowed.getCell(col); CellStyle cellStyle = cell.getCellStyle(); XSSFFont font = sheet.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof Date) { CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy")); cell.setCellValue((Date) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else if (obj instanceof Integer) { cell.setCellValue((int) obj); } cell.setCellStyle(cellStyle); }