List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:main.DataAppender.java
License:RPL License
public static void main(String[] args) { String[] toAppend = { "Acres", "Area", "Population", "Employment", "Housing", "Drive Time", "Transit Duration", "Walk Time", "Walk Distance" }; try {//from www .j a v a2s . com UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName()); } catch (Exception e) { } append_combo = new JComboBox(toAppend); //Have the user select the directory where the original TAZ xml files are stored JFileChooser choose = new JFileChooser(); choose.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY); int choice = choose.showOpenDialog(null); String path; tazForCombo = new ArrayList<String>(); if (choice == JFileChooser.APPROVE_OPTION) { path = choose.getSelectedFile().getPath(); File dir = new File(path); File[] files = dir.listFiles(new XMLFilter()); localTaz = new HashMap<String, TAZ>(); for (File f : files) { String xmlPath = f.getPath(); TAZ t = new TAZ(xmlPath); localTaz.put(t.getTAZ(), t); tazForCombo.add(t.getTAZ() + ""); } } //////////// GUI WORK ///////////////// taz_combo = new JComboBox(tazForCombo.toArray()); excel_directory = new JTextField(10); excel_directory.addMouseListener(new MouseListener() { public void mouseClicked(MouseEvent e) { JFileChooser choose = new JFileChooser(); int choice = choose.showOpenDialog(null); if (choice == JFileChooser.APPROVE_OPTION) { excel_directory.setText(choose.getSelectedFile().getPath()); } } public void mouseEntered(MouseEvent e) { } public void mouseExited(MouseEvent e) { } public void mousePressed(MouseEvent e) { } public void mouseReleased(MouseEvent e) { } }); System.out.println("Files loaded"); JFrame mainFrame = new JFrame(); JPanel mainPanel = new JPanel(); JPanel forCombo = new JPanel(); final JPanel forTAZ = new JPanel(); JPanel forDirectory = new JPanel(); JButton submit = new JButton("Submit"); forCombo.setLayout(new FlowLayout()); forTAZ.setLayout(new FlowLayout()); forDirectory.setLayout(new FlowLayout()); forCombo.add(new JLabel("Data to Alter: ")); forTAZ.add(new JLabel("TAZ to Alter: ")); forDirectory.add(new JLabel("Excel File: ")); append_combo.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { if (append_combo.getSelectedIndex() < 5) taz_combo.setEnabled(false); else taz_combo.setEnabled(true); } }); forCombo.add(append_combo); forTAZ.add(taz_combo); forDirectory.add(excel_directory); forTAZ.setEnabled(false); submit.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { HashMap<String, Double> vals = new HashMap<String, Double>(); try { //Opens excel workbook and extracts information from first two columns InputStream inp = new FileInputStream(excel_directory.getText()); HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(inp); int row = 0; Sheet sheet = wb.getSheetAt(0); while (sheet.getRow(row) != null) { int taz = (int) sheet.getRow(row).getCell(0).getNumericCellValue(); double val = sheet.getRow(row).getCell(1).getNumericCellValue(); vals.put(taz + "", val); row++; } } catch (Exception ex) { ex.printStackTrace(); System.out.println("Excel Failed"); System.exit(0); } //Appends the data based on the combo box selection switch (append_combo.getSelectedIndex()) { case 0: for (String tz : vals.keySet()) { TAZ loc = localTaz.get(tz); loc.setAcres(vals.get(tz)); try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } } break; case 1: for (String tz : vals.keySet()) { TAZ loc = localTaz.get(tz); loc.setArea(vals.get(tz)); try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } } break; /////////////////////////BY DEFAULT THE YEAR 2010 IS USED FOR POPULATION, HOUSING, and EMPLOYMENT//////////////////////////////// case 2: System.out.println("Population"); for (String tz : vals.keySet()) { TAZ loc = localTaz.get(tz); HashMap<Integer, Double> population = new HashMap<Integer, Double>(); population.put(2010, vals.get(tz)); loc.addPopulationData(population); try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } } break; case 3: System.out.println("Employment"); for (String tz : vals.keySet()) { TAZ loc = localTaz.get(tz); HashMap<Integer, Double> employment = new HashMap<Integer, Double>(); employment.put(2010, vals.get(tz)); loc.addEmploymentData(employment); try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } } break; case 4: System.out.println("Housing"); for (String tz : vals.keySet()) { TAZ loc = localTaz.get(tz); HashMap<Integer, Double> housing = new HashMap<Integer, Double>(); housing.put(2010, vals.get(tz)); loc.addHousingData(housing); try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } } break; case 5: TAZ loc = localTaz.get(Integer.parseInt(tazForCombo.get(taz_combo.getSelectedIndex()))); for (String tz : vals.keySet()) { loc.setDriveTime(tz, vals.get(tz)); } try { loc.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } break; case 6: System.out.println(tazForCombo.get(taz_combo.getSelectedIndex())); TAZ curr = localTaz.get(Integer.parseInt(tazForCombo.get(taz_combo.getSelectedIndex()))); System.out.println("CURRENT: " + curr.getTAZ()); for (String tz : vals.keySet()) { System.out.println("ITERATING"); curr.setTransitDuration(tz, vals.get(tz).longValue()); } try { curr.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } break; case 7: TAZ fTime = localTaz.get(Integer.parseInt(tazForCombo.get(taz_combo.getSelectedIndex()))); for (String tz : vals.keySet()) fTime.setWalkTime(tz, vals.get(tz).longValue()); try { fTime.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } break; case 8: TAZ fDist = localTaz.get(Integer.parseInt(tazForCombo.get(taz_combo.getSelectedIndex()))); for (String tz : vals.keySet()) fDist.setWalkDistance(tz, vals.get(tz)); try { fDist.toXML("C:/testingAmmendment/"); } catch (IOException e1) { System.out.println("Failed To Output"); } break; default: break; } JOptionPane.showMessageDialog(null, "Alterations Completed"); } }); mainPanel.setLayout(new BoxLayout(mainPanel, BoxLayout.Y_AXIS)); mainPanel.add(forCombo); mainPanel.add(forTAZ); mainPanel.add(forDirectory); mainPanel.add(submit); mainFrame.setTitle("Configuration"); mainFrame.add(mainPanel); mainFrame.pack(); mainFrame.setLocationRelativeTo(null); mainFrame.setVisible(true); mainFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); }
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// w w w . j a va 2 s . com * @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:mongodbutils.Filehandler.java
public boolean processFile(String filePath, MongodbConnection mc, String strdbName, String strCollName) throws IOException { this.mc = mc; FileInputStream fileIn = null; try {//from w ww . j av a 2 s. c om fileIn = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Object objReturn = null; //Read in first row as field names Row rowH = sheet.getRow(sheet.getFirstRowNum()); String fields[] = new String[sheet.getRow(0).getLastCellNum()]; for (Cell cell : rowH) { objReturn = null; objReturn = getCellValue(cell); fields[cell.getColumnIndex()] = objReturn.toString(); } //loop thru all cells with values int rowcount = 0; for (Row row : sheet) { if (row.getRowNum() == 0) { continue; //skip first row } JSONObject obj = new JSONObject(); for (Cell cell : row) { if (fields.length < cell.getColumnIndex()) { continue; //only export column if we have header set } objReturn = null; objReturn = getCellValue(cell); if (!objReturn.toString().equals("")) { if (objReturn instanceof Double) { obj.put(fields[cell.getColumnIndex()], objReturn); } else if (objReturn instanceof String) { if (objReturn.toString().contains("$date")) { JSONParser parser = new JSONParser(); try { obj.put(fields[cell.getColumnIndex()], parser.parse(objReturn.toString())); } catch (ParseException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } } else { obj.put(fields[cell.getColumnIndex()], objReturn); } } } } rowcount += 1; mc.insertJSON(strdbName, strCollName, obj.toJSONString()); } return true; } catch (FileNotFoundException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, e); } finally { if (fileIn != null) { fileIn.close(); } } return false; }
From source file:mx.dr.util.report.impl.PoiService.java
License:Open Source License
/** * @see mx.dr.util.report.IPoiService#doReport(InputStream, Object, OutputStream) *//*from ww w . jav a2 s.co m*/ public void doReport(InputStream is, Object dto, OutputStream out) throws Exception { POIFSFileSystem fs = null; HSSFWorkbook wb = null; fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); DRCoordinateReport anotCoordenada; DRCoordinateLabelReport anotEtiqueta; DRRelativeCoordinateReport anotRelativa; Object valor; Object valor2; HSSFRow row; HSSFCell cerda; int offset; CellDTO celdaDto; for (Method method : dto.getClass().getMethods()) { anotEtiqueta = method.getAnnotation(DRCoordinateLabelReport.class); if (anotEtiqueta != null) { dto.getClass().getMethod("set" + method.getName().substring(3), CellDTO.class).invoke(dto, new CellDTO(sheet.getRow(anotEtiqueta.y()).getCell(anotEtiqueta.x()).getCellStyle(), sheet.getRow(anotEtiqueta.y()).getCell(anotEtiqueta.x()).getRichStringCellValue())); } } for (Method method : dto.getClass().getMethods()) { anotCoordenada = method.getAnnotation(DRCoordinateReport.class); if (anotCoordenada != null) { valor = method.invoke(dto); if (valor != null) { cerda = sheet.getRow(anotCoordenada.y()).getCell(anotCoordenada.x()); ingresaValor(cerda, valor); if (valor instanceof List) { offset = anotCoordenada.y(); int index = 0; while (index < ((List) valor).size()) { Object detalle = ((List) valor).get(index); row = sheet.createRow(index + offset); for (Method methodMan : detalle.getClass().getMethods()) { anotCoordenada = methodMan.getAnnotation(DRCoordinateReport.class); if (anotCoordenada != null) { cerda = row.createCell(anotCoordenada.x()); valor2 = methodMan.invoke(detalle); if (valor2 != null) { ingresaValor(cerda, valor2); } } } index++; } index -= 1; for (Method m : dto.getClass().getMethods()) { anotEtiqueta = m.getAnnotation(DRCoordinateLabelReport.class); anotRelativa = m.getAnnotation(DRRelativeCoordinateReport.class); if (anotEtiqueta != null) { row = sheet.getRow(anotEtiqueta.y() + index); if (row == null) { row = sheet.createRow(anotEtiqueta.y() + index); } cerda = row.createCell(anotEtiqueta.x()); celdaDto = (CellDTO) m.invoke(dto); cerda.setCellStyle(celdaDto.getStyle()); cerda.setCellValue(celdaDto.getLabel()); } else if (anotRelativa != null) { row = sheet.getRow(anotRelativa.y() + index); if (row == null) { row = sheet.createRow(anotRelativa.y() + index); } cerda = row.createCell(anotRelativa.x()); valor2 = m.invoke(dto); if (valor2 != null) { ingresaValor(cerda, valor2); } } } } } } } //String res = "/mailConfig.properties"; //tempPath=PoiService.class.getResource(res).getPath().replaceFirst(res, "") // + "/../../" + Labels.getLabel("parametro.adjuntos.folder") + "/" + archivo; //FileUtils.writeToFile(archivo,new ByteArrayInputStream(doc.getDataContent())); //tempPath = this.getPath(TEMP_DIR)+"\\"+archivo; wb.write(out);//new FileOutputStream(tempPath ,false)); }
From source file:mytest.testJUnitFloatEN.java
@Test public void testJUnitFloatEN() throws Exception { try {//from w ww . ja v a2 s.c o m convert.text.NumberFormat w = new convert.text.NumberFormat("en"); w.loadDirectory(); InputStream in = new FileInputStream( (getClass().getResource("/datatest/DataExel_FLOAT_EN.xls").getPath())); HSSFWorkbook wb = new HSSFWorkbook(in); Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); String inString = "", inNumber = ""; while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); Cell cell = cells.next(); int cellType = cell.getCellType(); inNumber = (cell.getStringCellValue()); inNumber = inNumber.substring(1, inNumber.length() - 1); cells.hasNext(); cell = cells.next(); inString = cell.getStringCellValue(); System.out.println(inNumber + "\notvet: " + inString); String s = inNumber; String result = w.format(inNumber); System.out.println("result " + result); assertEquals(" ?: " + inNumber, inString, result); } } catch (NullPointerException e) { throw new RuntimeException( "? ? ??, \n" + e.toString()); } }
From source file:mytest.testJUnitFloatRU.java
@Test public void testJUnitFloatRU() throws Exception { try {/*from w w w . j a v a 2s.com*/ convert.text.NumberFormat w = new convert.text.NumberFormat("ru"); w.loadDirectory(); InputStream in = new FileInputStream( (getClass().getResource("/datatest/DataExel_FLOAT_RU.xls").getPath())); HSSFWorkbook wb = new HSSFWorkbook(in); Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); String inString = "", inNumber = ""; while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); Cell cell = cells.next(); int cellType = cell.getCellType(); inNumber = (cell.getStringCellValue()); inNumber = inNumber.substring(1, inNumber.length() - 1); cells.hasNext(); cell = cells.next(); inString = cell.getStringCellValue(); System.out.println(inNumber + "\notvet: " + inString); String s = inNumber; String result = w.format(inNumber); System.out.println("result " + result); assertEquals(" ?: " + inNumber, inString, result); } } catch (NullPointerException e) { throw new RuntimeException( "? ? ??, \n" + e.toString()); } }
From source file:mytest.testJUnitIntegerEN.java
@Test public void testJUnitIntegerRU() throws Exception { try {/*from w w w.j a v a2 s . c o m*/ convert.text.NumberFormat w = new convert.text.NumberFormat("en"); w.loadDirectory(); InputStream in = new FileInputStream( (getClass().getResource("/datatest/DataExel_INTEGER_EN.xls").getPath())); HSSFWorkbook wb = new HSSFWorkbook(in); Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); String inString = "", inNumber = ""; while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); Cell cell = cells.next(); int cellType = cell.getCellType(); inNumber = (cell.getStringCellValue()); inNumber = inNumber.substring(1, inNumber.length() - 1); cells.hasNext(); cell = cells.next(); inString = cell.getStringCellValue(); System.out.println(inNumber + "\notvet: " + inString); String s = inNumber; String result = w.format(inNumber); System.out.println("result " + result); assertEquals(" ?: " + inNumber, inString, result); } } catch (NullPointerException e) { throw new RuntimeException( "? ? ??, \n" + e.toString()); } }
From source file:mytest.testJUnitIntegerRU.java
@Test public void testJUnitIntegerRU() throws Exception { try {/*w ww . j ava 2s. com*/ convert.text.NumberFormat w = new convert.text.NumberFormat("ru"); w.loadDirectory(); InputStream in = new FileInputStream( (getClass().getResource("/datatest/DataExel_INTEGER_RU.xls").getPath())); HSSFWorkbook wb = new HSSFWorkbook(in); Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); String inString = "", inNumber = ""; while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); Cell cell = cells.next(); int cellType = cell.getCellType(); inNumber = (cell.getStringCellValue()); inNumber = inNumber.substring(1, inNumber.length() - 1); cells.hasNext(); cell = cells.next(); inString = cell.getStringCellValue(); System.out.println(inNumber + "\notvet: " + inString); String s = inNumber; String result = w.format(inNumber); System.out.println("result " + result); assertEquals(" ?: " + inNumber, inString, result); } } catch (NullPointerException e) { throw new RuntimeException( "? ? ??, \n" + e.toString()); } }
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Generates the actual workbook of data. * * @param timelordData the data to generate a workbook for * @return the workbook//w w w . j a v a2s. c o m */ protected HSSFWorkbook generateWorkbook(TimelordData timelordData) { HSSFWorkbook wb = new HSSFWorkbook(); // Build the Map of the Styles that will be applied to cells // in the workbook Map<String, HSSFCellStyle> styleMap = buildStyleMap(wb); Map<String, List<String>> sheetToNotes = new TreeMap<String, List<String>>(new DateComparator()); // Since there is an issue re-ordering sheets after they // have been created. First create the book with all needed // sheets preCreateAllSheets(wb, timelordData, sheetToNotes, styleMap); // After all the sheets have been pre-created, iterate through all // the tasks to add them into the sheets. int rowNum = addAllTasks(wb, timelordData, sheetToNotes, styleMap); // This section applies all the styles, creates the footers and adds // the notes onto the sheet. for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); String sheetName = wb.getSheetName(i); createFooterRows(sheet, rowNum, styleMap); // This will apply styles to the rows that had no task associated // for a given week. for (int j = 1; j < rowNum - 1; j++) { HSSFRow row = sheet.getRow(j); if (row == null) { row = sheet.createRow(j); row.setHeight((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle")); cell.setCellValue(""); cell = row.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle")); cell.setCellFormula("SUM(B" + (j + 1) + ":H" + (j + 1) + ")"); } } List<String> noteList = sheetToNotes.get(sheetName); createNotesRows(sheet, noteList); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); } // Finally order the sheets properly if (logger.isDebugEnabled()) { logger.debug("Re-ordering sheets under final order."); } return wb; }
From source file:net.intelliant.marketing.ContactListServices.java
License:Open Source License
@SuppressWarnings("unchecked") private static Map<String, Object> createRecords(GenericDelegator delegator, Locale locale, GenericValue mailerImportMapper, String userLoginId, String contactListId, String excelFilePath) throws GenericEntityException, FileNotFoundException, IOException { boolean transaction = false; int rowIndex = 0, totalCount = 0, failureCount = 0; String ofbizEntityName = mailerImportMapper.getString("ofbizEntityName"); String importMapperId = mailerImportMapper.getString("importMapperId"); String isFirstRowHeader = mailerImportMapper.getString("isFirstRowHeader"); Map<String, Map<Integer, String>> failureReport = new LinkedHashMap<String, Map<Integer, String>>(); Map<Integer, String> failureReportDetails = new LinkedHashMap<Integer, String>(); Map<String, Object> columnMappings = UtilImport.getActiveColumnMappings(delegator, importMapperId); HSSFWorkbook excelDocument = new HSSFWorkbook(new FileInputStream(excelFilePath)); HSSFSheet excelSheet = excelDocument.getSheetAt(0); Iterator<HSSFRow> excelRowIterator = excelSheet.rowIterator(); if (isFirstRowHeader.equalsIgnoreCase("Y")) { if (excelRowIterator.hasNext()) { excelRowIterator.next();/*from www .j av a 2 s . com*/ rowIndex++; } } while (excelRowIterator.hasNext()) { try { transaction = TransactionUtil.begin(); rowIndex++; totalCount++; failureReportDetails = new HashMap<Integer, String>(); GenericValue customEntityObj = insertIntoConfiguredCustomEntity(delegator, locale, userLoginId, ofbizEntityName, excelRowIterator.next(), columnMappings, failureReportDetails); String recipientId = customEntityObj.getString("recipientId"); createCLRecipientRelation(delegator, contactListId, recipientId); createCampaignLines(delegator, contactListId, recipientId, customEntityObj.getDate(dateOfOperationColumnName)); } catch (GenericEntityException gee) { Debug.logError(gee, MODULE); if (transaction) { TransactionUtil.rollback(); } failureReport.put(String.valueOf(rowIndex - 1), failureReportDetails); failureCount++; } catch (Exception e) { Debug.logError(e, MODULE); if (transaction) { TransactionUtil.rollback(); } failureReport.put(String.valueOf(rowIndex - 1), failureReportDetails); failureCount++; } finally { if (transaction) { TransactionUtil.commit(); } } } Map<String, Object> results = ServiceUtil.returnSuccess(); results.put("totalCount", totalCount); results.put("failureCount", failureCount); results.put("failureReport", failureReport); return results; }