List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:com.springapp.mvc.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w ww .ja va2 s .c om if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); //calendar.setTime(fmt.parse("9-11")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { //calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.syncnapsis.utils.data.ExcelHelper.java
License:Open Source License
public static void main(String[] args) throws Exception { String fileName = "testdata.xls"; HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("src/main/resources/" + fileName)); HSSFSheet sheet = workbook.getSheet("Benutzer"); HSSFRow row = sheet.getRow(0);/*from w w w . j a v a 2s .c o m*/ Cell cell; for (int i = 2; i < 256; i++) { try { cell = row.getCell(i); if (cell == null) cell = row.createCell(i); cell.setCellFormula("IF(ISBLANK(Benutzer!A" + (i - 1) + "),\"\",Benutzer!A" + (i - 1) + ")"); } catch (Exception e) { e.printStackTrace(); } } workbook.write(new FileOutputStream("src/main/resources/new.xls")); }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();// w w w . ja v a2s . c o m XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Cell cell; Row row = getRow(sheet, initialRow); cell = row.createCell(initialColumn); cell.setCellValue("Hojalteria y pintura"); XSSFCellStyle cellStyle = wb.createCellStyle(); addHeaderStyle(cellStyle, wb); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); for (int i = 1; i < 3; i++) { cell = row.createCell(initialColumn + i); cellStyle = wb.createCellStyle(); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); } //merge de celdas sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based) initialRow, //last row (0-based) initialColumn, //first column (0-based) initialColumn + 2 //last column (0-based) )); //detalle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); Cell inicio = null; Cell fin = null; for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) { initialRow = initialRow + 1; row = getRow(sheet, initialRow); //tipo cell = row.createCell(initialColumn); cell.setCellValue(x.getTipo()); //descripcion cell = row.createCell(initialColumn + 1); cell.setCellValue(x.getDescripcion()); //costo cell = row.createCell(initialColumn + 2); cell.setCellValue(x.getCosto()); cell.setCellStyle(cellStyle); if (inicio == null) { inicio = cell; } } if (inicio != null) { fin = cell; } initialRow = initialRow + 1; row = getRow(sheet, initialRow); cell = row.createCell(initialColumn + 1); cell.setCellValue("Total"); cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell = row.createCell(initialColumn + 2); cell.setCellStyle(cellStyle); if (inicio != null) { String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")"; cell.setCellFormula(formula); contexto.put("totalHojalateria", cell); } else { cell.setCellValue(0.0); } r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 2); BordeSeccion sinTotal = new BordeSeccion(r); sinTotal.setLowerRow(r.getLowerRow() - 1); paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal); return r; }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();// w ww. j ava2s .co m XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Cell cell; Row row = getRow(sheet, initialRow); cell = row.createCell(initialColumn); cell.setCellValue("Mecanica"); XSSFCellStyle cellStyle = wb.createCellStyle(); addHeaderStyle(cellStyle, wb); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); for (int i = 1; i < 3; i++) { cell = row.createCell(initialColumn + i); cellStyle = wb.createCellStyle(); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); } //merge de celdas sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based) initialRow, //last row (0-based) initialColumn, //first column (0-based) initialColumn + 2 //last column (0-based) )); //detalle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); Cell inicio = null; Cell fin = null; for (RegistroCostoRC x : datos.getRegistroMecanica()) { initialRow = initialRow + 1; row = getRow(sheet, initialRow); //tipo cell = row.createCell(initialColumn); cell.setCellValue(x.getTipo()); //descripcion cell = row.createCell(initialColumn + 1); cell.setCellValue(x.getDescripcion()); //costo cell = row.createCell(initialColumn + 2); cell.setCellValue(x.getCosto()); cell.setCellStyle(cellStyle); if (inicio == null) { inicio = cell; } } if (inicio != null) { fin = cell; } initialRow = initialRow + 1; row = getRow(sheet, initialRow); cell = row.createCell(initialColumn + 1); cell.setCellValue("Total"); cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell = row.createCell(initialColumn + 2); cell.setCellStyle(cellStyle); if (inicio != null) { String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")"; cell.setCellFormula(formula); contexto.put("totalMecanica", cell); } else { cell.setCellValue(0.0); } r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 2); BordeSeccion sinTotal = new BordeSeccion(r); sinTotal.setLowerRow(r.getLowerRow() - 1); paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal); return r; }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.TotalServicioReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();/*from ww w . ja v a 2s.co m*/ XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Row row = getRow(sheet, initialRow); row.createCell(initialColumn).setCellValue("Total del servicio:"); //calculo del total Cell totalMecanica = (Cell) contexto.get("totalMecanica"); Cell totalHojalateria = (Cell) contexto.get("totalHojalateria"); Cell cell = row.createCell(initialColumn + 1); if (totalMecanica != null && totalHojalateria != null) { String formula = getSimpleReference(totalMecanica) + "+" + getSimpleReference(totalHojalateria); cell.setCellFormula(formula); } else { if (totalMecanica != null) { String formula = getSimpleReference(totalMecanica); cell.setCellFormula(formula); } if (totalHojalateria != null) { String formula = getSimpleReference(totalHojalateria); cell.setCellFormula(formula); } } if (totalMecanica == null && totalHojalateria == null) { cell.setCellValue(0d); } XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell.setCellStyle(cellStyle); r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 1); return r; }
From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java
License:Open Source License
/** * Set's cell value for the newCell. It will be the same as shiftedCell with * updated references./* ww w . j a v a2 s.com*/ * * @param shiftedCell * Source cell * @param newCell * Resulting new cell */ private void shiftFormula(Cell shiftedCell, Cell newCell) { try { if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) { // shift column indexes int collDiff = newCell.getColumnIndex() - shiftedCell.getColumnIndex(); Matcher matcher = rowShiftPattern.matcher(shiftedCell.getCellFormula()); String originalFormula = shiftedCell.getCellFormula(); StringBuilder newFormula = new StringBuilder(); int lastEnd = 0; while (matcher.find()) { String s = matcher.group(); String replacement; if (!s.startsWith("$")) { String oldIndexString = s.replaceAll("[$]{0,1}\\d+", ""); int columnIndex = SpreadsheetUtil.getColHeaderIndex(oldIndexString); columnIndex += collDiff; replacement = s.replace(oldIndexString, SpreadsheetUtil.getColHeader(columnIndex)); } else { // if column has a '$' reference shouldn't change replacement = s; } newFormula.append(originalFormula.substring(lastEnd, matcher.start())); newFormula.append(replacement); lastEnd = matcher.end(); } newFormula.append(originalFormula.substring(lastEnd)); newCell.setCellFormula(newFormula.toString()); } else { // shift row indexes int rowDiff = newCell.getRowIndex() - shiftedCell.getRowIndex(); Matcher matcher = rowShiftPattern.matcher(shiftedCell.getCellFormula()); String originalFormula = shiftedCell.getCellFormula(); StringBuilder newFormula = new StringBuilder(); int lastEnd = 0; while (matcher.find()) { String s = matcher.group(); String rowString = s.replaceAll("[$]{0,1}[a-zA-Z]+", ""); String replacement; if (!rowString.startsWith("$")) { int row = Integer.parseInt(rowString); row += rowDiff; replacement = s.replace(rowString, Integer.toString(row)); } else { // if row has a '$' reference shouldn't change replacement = s; } newFormula.append(originalFormula.substring(lastEnd, matcher.start())); newFormula.append(replacement); lastEnd = matcher.end(); } newFormula.append(originalFormula.substring(lastEnd)); newCell.setCellFormula(newFormula.toString()); } } catch (Exception e) { LOGGER.log(Level.FINE, e.getMessage(), e); // TODO visualize shifting error newCell.setCellFormula(shiftedCell.getCellFormula()); } spreadsheet.getCellValueManager().getFormulaEvaluator().notifySetFormula(newCell); }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Updates the cell value and type, causes a recalculation of all the values * in the cell.//from ww w . java2 s. c om * * If there is a {@link CellValueHandler} defined, then it is used. * * Cells starting with "=" or "+" will be created/changed into FORMULA type. * * Cells that are existing and are NUMERIC type will be parsed according to * their existing format, or if that fails, as Double. * * Cells not containing any letters and containing at least one number will * be created/changed into NUMERIC type (formatting is not changed). * * Existing Boolean cells will be parsed as Boolean. * * For everything else and if any of the above fail, the cell will get the * STRING type and the value will just be a string, except empty values will * cause the cell type to be BLANK. * * @param col * Column index of target cell, 1-based * @param row * Row index of target cell, 1-based * @param value * The new value to set to the target cell, formulas will start * with an extra "=" or "+" */ public void onCellValueChange(int col, int row, String value) { Workbook workbook = spreadsheet.getWorkbook(); // update cell value final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row - 1); if (r == null) { r = activeSheet.createRow(row - 1); } Cell cell = r.getCell(col - 1); String formattedCellValue = null; int oldCellType = -1; // capture cell value to history CellValueCommand command = new CellValueCommand(spreadsheet); command.captureCellValues(new CellReference(row - 1, col - 1)); spreadsheet.getSpreadsheetHistoryManager().addCommand(command); boolean updateHyperlinks = false; if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet, col - 1, row - 1, value, getFormulaEvaluator(), formatter)) { Exception exception = null; try { // handle new cell creation SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory(); final Locale spreadsheetLocale = spreadsheet.getLocale(); if (cell == null) { cell = r.createCell(col - 1); } else { // modify existing cell, possibly switch type formattedCellValue = getFormattedCellValue(cell); final String key = SpreadsheetUtil.toKey(col, row); oldCellType = cell.getCellType(); if (!sentCells.remove(key)) { sentFormulaCells.remove(key); } // Old value was hyperlink => needs refresh if (cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCellFormula().startsWith("HYPERLINK")) { updateHyperlinks = true; } } if (formulaFormatter.isFormulaFormat(value)) { if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) { spreadsheet.removeInvalidFormulaMark(col, row); getFormulaEvaluator().notifyUpdateCell(cell); cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale)); getFormulaEvaluator().notifySetFormula(cell); if (value.startsWith("=HYPERLINK(") && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) { // set the cell style to link cell CellStyle hyperlinkCellStyle; if (hyperlinkStyleIndex == -1) { hyperlinkCellStyle = styler.createHyperlinkCellStyle(); hyperlinkStyleIndex = -1; } else { hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex); } cell.setCellStyle(hyperlinkCellStyle); styler.cellStyleUpdated(cell, true); updateHyperlinks = true; } } else { // it's formula but invalid cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } else { spreadsheet.removeInvalidFormulaMark(col, row); Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale); Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale); if (value.isEmpty()) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else if (percentage != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); CellStyle cs = cell.getCellStyle(); if (cs == null) { cs = workbook.createCellStyle(); cell.setCellStyle(cs); } if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) { cs.setDataFormat(workbook.createDataFormat() .getFormat(spreadsheet.getDefaultPercentageFormat())); styler.cellStyleUpdated(cell, true); } cell.setCellValue(percentage); } else if (numVal != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numVal); } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) { cell.setCellValue(Boolean.parseBoolean(value)); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); } getFormulaEvaluator().notifyUpdateCell(cell); } } catch (FormulaParseException fpe) { try { exception = fpe; // parses formula cell.setCellFormula(value.substring(1).replace(" ", "")); } catch (FormulaParseException fpe2) { exception = fpe2; /* * We could force storing the formula even if it is invalid. * Instead, just store it as the value. Clearing the formula * makes sure the value is displayed as-is. */ cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } catch (NumberFormatException nfe) { exception = nfe; cell.setCellValue(value); } catch (Exception e) { exception = e; cell.setCellValue(value); } if (cell != null) { markCellForUpdate(cell); if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell)) || oldCellType != cell.getCellType()) { fireCellValueChangeEvent(cell); } } if (exception != null) { LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " (" + exception.getMessage() + ")", exception); } } spreadsheet.updateMarkedCells(); if (updateHyperlinks) { spreadsheet.loadHyperLinks(); } }
From source file:com.vaadin.addon.spreadsheet.command.CellValueCommand.java
License:Open Source License
/** * Sets the given value to the cell at the given coordinates. * //from w ww . j av a 2 s . c om * @param row * Row index, 0-based * @param col * Column index, 0-based * @param value * Value to set to the cell * @param cellsToUpdate * List of cells that need updating at the end. If the cell value * is modified, the cell is added to this list. * @return Previous value of the cell or null if not available */ protected Object updateCellValue(int row, int col, Object value, List<Cell> cellsToUpdate) { Cell cell = getCell(row, col); Object oldValue = getCellValue(cell); if (value == null && cell == null) { return null; // nothing to do } if (cell == null && value != null) { // create cell Row row2 = getSheet().getRow(row); if (row2 == null) { row2 = getSheet().createRow(row); } cell = row2.createCell(col); } if (value == null) { // delete if (cell == null || cell.getCellStyle().getIndex() == 0) { getSheet().getRow(row).removeCell(cell); if (!spreadsheet.isRerenderPending()) { spreadsheet.markCellAsDeleted(cell, false); } } else { cell.setCellValue((String) null); if (!spreadsheet.isRerenderPending()) { cellsToUpdate.add(cell); } } } else { if (value instanceof String) { if (((String) value).startsWith("=")) { try { cell.setCellFormula(((String) value).substring(1)); } catch (FormulaParseException fpe) { cell.setCellValue((String) value); } } else { cell.setCellValue((String) value); } } else if (value instanceof Byte) { cell.setCellErrorValue((Byte) value); } else if (value instanceof Double) { cell.setCellValue((Double) value); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } if (!spreadsheet.isRerenderPending()) { cellsToUpdate.add(cell); } } return oldValue; }
From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java
License:Open Source License
/** * Creates a new Formula type cell with the given formula. * /*from w w w . j av a 2 s. c om*/ * After all editing is done, call {@link #refreshCells(Cell...)()} or * {@link #refreshAllCellValues()} to make sure client side is updated. * * @param row * Row index of the new cell, 0-based * @param col * Column index of the new cell, 0-based * @param formula * The formula to set to the new cell (should NOT start with "=" * nor "+") * @return The newly created cell * @throws IllegalArgumentException * If columnIndex < 0 or greater than the maximum number of * supported columns (255 for *.xls, 1048576 for *.xlsx) */ public Cell createFormulaCell(int row, int col, String formula) throws IllegalArgumentException { final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row); if (r == null) { r = activeSheet.createRow(row); } Cell cell = r.getCell(col); if (cell == null) { cell = r.createCell(col, Cell.CELL_TYPE_FORMULA); } else { final String key = SpreadsheetUtil.toKey(col + 1, row + 1); valueManager.clearCellCache(key); cell.setCellType(Cell.CELL_TYPE_FORMULA); } cell.setCellFormula(formula); valueManager.cellUpdated(cell); return cell; }
From source file:Compras.generaCotizacion.java
void exel() { h = new Herramientas(this.user, 0); h.session(sessionPrograma);// ww w . j a va 2s . co m javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser(); jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) { ruta = jF1.getSelectedFile().getAbsolutePath(); if (ruta != null) { if (ruta.endsWith(".xls") == true) archivoXLS = new File(ruta); else archivoXLS = new File(ruta + ".xls"); try { if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Cotizacion"); Font font = libro.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Arial"); font.setItalic(false); font.setBold(true); Font font10 = libro.createFont(); font10.setFontHeightInPoints((short) 10); font10.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font10.setColor(new HSSFColor.YELLOW().getIndex()); Font font11 = libro.createFont(); font11.setFontHeightInPoints((short) 10); font11.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font11.setColor(new HSSFColor.BLACK().getIndex()); // Fonts are set into a style so create a new one to use. CellStyle style = libro.createCellStyle(); CellStyle desBloqueo = libro.createCellStyle(); CellStyle desBloqueo1 = libro.createCellStyle(); CellStyle desBloqueoFecha = libro.createCellStyle(); style.setFont(font); desBloqueo.setFont(font10); desBloqueo.setLocked(false); desBloqueo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); desBloqueo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); desBloqueo1.setFont(font11); desBloqueo1.setLocked(false); desBloqueo1.setFillBackgroundColor(new HSSFColor.WHITE().getIndex()); desBloqueoFecha.setFont(font10); desBloqueoFecha.setLocked(false); desBloqueoFecha.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); desBloqueoFecha.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); desBloqueoFecha.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction().begin(); Orden ord = (Orden) session.get(Orden.class, Integer.parseInt(orden)); Configuracion con = (Configuracion) session.get(Configuracion.class, 1); hoja.setColumnWidth(5, 15000); Row r0 = hoja.createRow(0); Cell celdaTitulo = r0.createCell(0); celdaTitulo.setCellValue(con.getEmpresa()); celdaTitulo.setCellStyle(style); Row r1 = hoja.createRow(1); r1.createCell(6).setCellValue("Orden de Taller:"); r1.createCell(7).setCellValue(orden); Row r2 = hoja.createRow(2); r2.createCell(0).setCellValue("Marca:"); r2.createCell(1).setCellValue(ord.getMarca().getMarcaNombre()); r2.createCell(6).setCellValue("N Serie:"); r2.createCell(7).setCellValue(ord.getNoSerie()); Row r3 = hoja.createRow(3); r3.createCell(0).setCellValue("Tipo:"); r3.createCell(1).setCellValue(ord.getTipo().getTipoNombre()); r3.createCell(6).setCellValue("N Motor:"); r3.createCell(7).setCellValue(ord.getNoMotor()); Row r4 = hoja.createRow(4); r4.createCell(0).setCellValue("NP:"); r4.createCell(1).setCellValue(t_datos1.getValueAt(t_datos1.getSelectedRow(), 1).toString()); r4.createCell(2).setCellValue("Proveedor:"); r4.createCell(3).setCellValue(t_datos1.getValueAt(t_datos1.getSelectedRow(), 2).toString()); r4.createCell(6).setCellValue("Modelo:"); r4.createCell(7).setCellValue("" + ord.getModelo()); Row r5 = hoja.createRow(5); r5.createCell(0).setCellValue( "**********************************************************************************[Nota: Solo puedes editar las celdas de color]*******************************************************************************"); Row r6 = hoja.createRow(6); r6.createCell(0).setCellValue("Partida"); r6.createCell(1).setCellValue("sub"); r6.createCell(2).setCellValue("Cantidad"); r6.createCell(3).setCellValue("U/Medida"); r6.createCell(4).setCellValue("N Parte"); r6.createCell(5).setCellValue("Descripcion"); r6.createCell(6).setCellValue("Instruccin"); r6.createCell(7).setCellValue("Precio c/u"); r6.createCell(8).setCellValue("T o t a l"); r6.createCell(9).setCellValue("Origen"); r6.createCell(10).setCellValue("Pazo"); Row r7 = hoja.createRow(7); r7.createCell(0).setCellValue( "**********************************************************************************************************************************************************************************************************************"); List misCotizaciones = null; Query query = session.createQuery("SELECT DISTINCT par FROM Partida par " + "RIGHT JOIN FETCH par.partidaCotizacions partC " + "RIGHT JOIN partC.cotizacion cot " + "where cot.idCotizacion=" + t_datos1.getValueAt(t_datos1.getSelectedRow(), 0).toString() + " order by par.idEvaluacion asc, par.subPartida asc"); //misCotizaciones=c.addOrder(Order.asc("idCotizacion")).list(); misCotizaciones = query.list(); if (misCotizaciones.size() > 0) { for (int i = 0; i < misCotizaciones.size(); i++) { Partida Part = (Partida) misCotizaciones.get(i); Row fila = hoja.createRow(i + 8); fila.createCell(0).setCellValue(Part.getIdEvaluacion()); fila.createCell(1).setCellValue(Part.getSubPartida()); fila.createCell(2).setCellValue(Part.getCant()); fila.createCell(3).setCellValue(Part.getMed()); Cell aux = fila.createCell(4); aux.setCellStyle(desBloqueo); if (Part.getEjemplar() != null) aux.setCellValue(Part.getEjemplar().getIdParte()); else aux.setCellValue(""); fila.createCell(5).setCellValue(Part.getCatalogo().getNombre()); int fil = i + 9; Cell a10 = fila.createCell(6); a10.setCellStyle(desBloqueo1); a10.setCellValue(Part.getInstruccion()); Cell a6 = fila.createCell(7); a6.setCellStyle(desBloqueo); a6.setCellValue(""); Cell celForm = fila.createCell(8); celForm.setCellType(HSSFCell.CELL_TYPE_FORMULA); celForm.setCellFormula("H" + fil + "*C" + fil); if (Part.isOri() == true) fila.createCell(9).setCellValue("Ori"); else if (Part.isNal() == true) fila.createCell(9).setCellValue("Nal"); else if (Part.isDesm() == true) fila.createCell(9).setCellValue("Des"); else fila.createCell(9).setCellValue(""); Cell a9 = fila.createCell(10); a9.setCellValue(""); a9.setCellStyle(desBloqueo); } } hoja.protectSheet("04650077"); libro.write(archivo); archivo.close(); Desktop.getDesktop().open(archivoXLS); if (session != null) if (session.isOpen()) session.close(); } catch (Exception e) { System.out.println(e); e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto"); } } } }