List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook()
From source file:com.griffinslogistics.document.excel.BookLabelGenerator.java
public static void generateLabels(OutputStream outputStream, List<BookLabelModel> bookLabelModelList) { XSSFWorkbook workbook = new XSSFWorkbook(); for (BookLabelModel bookLabelModel : bookLabelModelList) { generate(workbook, bookLabelModel); }/*from w w w . j a v a2 s . c o m*/ try { workbook.write(outputStream); } catch (IOException ex) { Logger.getLogger(BookLabelGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.document.excel.BookLabelGenerator.java
public static void generateLabel(OutputStream outputStream, BookLabelModel bookLabelModel) { XSSFWorkbook workbook = new XSSFWorkbook(); generate(workbook, bookLabelModel);/*from w w w.ja v a 2 s . co m*/ try { workbook.write(outputStream); } catch (IOException ex) { Logger.getLogger(BookLabelGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.document.excel.CMRGenerator.java
public static void generateAll(OutputStream outputStream, List<BookspackageCMRModel> bookspackageCMRModels, Pulsiodetails pulsioDetails) {// ww w. j a v a 2s . c o m try { XSSFWorkbook workbook = new XSSFWorkbook(); for (BookspackageCMRModel bookspackageCMRModel : bookspackageCMRModels) { generate(workbook, bookspackageCMRModel, pulsioDetails); } workbook.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.document.excel.CMRGenerator.java
public static void generateSingle(OutputStream outputStream, BookspackageCMRModel bookspackageCMRModel, Pulsiodetails pulsioDetails) {// w w w. j av a 2 s . c om XSSFWorkbook workbook = new XSSFWorkbook(); generate(workbook, bookspackageCMRModel, pulsioDetails); try { workbook.write(outputStream); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.excel.BookLabelGenerator.java
public static void generateLabel(OutputStream outputStream, BookLabelModel bookLabelModel) { try {// w ww .j a va 2 s . co m XSSFWorkbook workbook = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(workbook); String title = bookLabelModel.getTitle().replace("/", "-"); bookLabelModel.setTitle(title); Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle()); for (int i = 0; i < 20; i++) { Row row = sheet.createRow(i); if (i != 0 && i != 10) { row.setHeightInPoints(25); } else { row.setHeightInPoints(12); } } //column widths sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 10000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 10000); generateHeaders(sheet, styles); generateAddress(sheet, styles, bookLabelModel); generateClient(sheet, styles, bookLabelModel); generateTransportation(sheet, styles, bookLabelModel); generateTitle(sheet, styles, bookLabelModel); generateCountPerBox(sheet, styles, bookLabelModel); generateCountPerAddress(sheet, styles, bookLabelModel); sheet.autoSizeColumn(1); sheet.autoSizeColumn(4); workbook.write(outputStream); } catch (IOException ex) { Logger.getLogger(BookLabelGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.excel.CMRGenerator.java
public static void generateAll(OutputStream outputStream, List<BookspackageCMRModel> bookspackageCMRModels, Pulsiodetails pulsioDetails) {//from www . j a v a2 s.c om try { XSSFWorkbook workbook = new XSSFWorkbook(); for (BookspackageCMRModel bookspackageCMRModel : bookspackageCMRModels) { XSSFSheet sheet = workbook.createSheet(bookspackageCMRModel.getPackageNumber()); setDefaultSheetStyles(sheet); Map<String, CellStyle> styles = createStyles(workbook); int currentRow = 2; Row headerRow = sheet.createRow(currentRow); generateHeaderRow(headerRow, styles, bookspackageCMRModel.getPackageNumber()); currentRow = generateSenderAndDriver(sheet, styles, currentRow); currentRow = generatePoint2Till9(sheet, styles, currentRow, bookspackageCMRModel.getDeliveryAddress()); double weight = bookspackageCMRModel.getTotalWeight(); Long totalBoxesCount = bookspackageCMRModel.getTotalBoxesCount(); currentRow = generatePoints10Till15(totalBoxesCount, weight, sheet, styles, currentRow); currentRow = generatePoint15Till19(sheet, styles, currentRow); currentRow = generatePoint20Till24(sheet, styles, currentRow, pulsioDetails); sheet.autoSizeColumn(1, true); sheet.autoSizeColumn(2, true); } workbook.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.excel.CMRGenerator.java
public static void generateSingle(OutputStream outputStream, BookspackageCMRModel bookspackageCMRModel, Pulsiodetails pulsioDetails) {// w ww . j av a2 s .co m try { XSSFWorkbook workbook = new XSSFWorkbook(); //Get first sheet from the workbook XSSFSheet sheet = workbook.createSheet(bookspackageCMRModel.getPackageNumber()); setDefaultSheetStyles(sheet); Map<String, CellStyle> styles = createStyles(workbook); int currentRow = 2; Row headerRow = sheet.createRow(currentRow); generateHeaderRow(headerRow, styles, bookspackageCMRModel.getPackageNumber()); currentRow = generateSenderAndDriver(sheet, styles, currentRow); currentRow = generatePoint2Till9(sheet, styles, currentRow, bookspackageCMRModel.getDeliveryAddress()); double weight = bookspackageCMRModel.getTotalWeight(); Long totalBoxesCount = bookspackageCMRModel.getTotalBoxesCount(); currentRow = generatePoints10Till15(totalBoxesCount, weight, sheet, styles, currentRow); currentRow = generatePoint15Till19(sheet, styles, currentRow); currentRow = generatePoint20Till24(sheet, styles, currentRow, pulsioDetails); workbook.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.guardias.excel.CalendarToExcel.java
License:Apache License
public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos, Medico AdministratorUser) throws IOException { Guardias[] lGuardias;/* w ww. j a v a 2 s . c o m*/ Gson gson = new GsonBuilder().create(); lGuardias = gson.fromJson(JSONContenidos, Guardias[].class); boolean xlsx = true; int year = calendar.get(Calendar.YEAR); int month = calendar.get(Calendar.MONTH); DateFormat _format = new SimpleDateFormat("yyyy-MM-dd"); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); calendar.setFirstDayOfWeek(Calendar.MONDAY); //create a sheet for each month Sheet sheet = wb.createSheet(_format.format(calendar.getTime())); CellStyle styleBORDER = wb.createCellStyle(); styleBORDER.setBorderRight(CellStyle.BORDER_THICK); styleBORDER.setBorderBottom(CellStyle.BORDER_THICK); styleBORDER.setBorderTop(CellStyle.BORDER_THICK); styleBORDER.setBorderLeft(CellStyle.BORDER_THICK); styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); // //turn off gridlines sheet.setDisplayGridlines(true); sheet.autoSizeColumn(0); sheet.setPrintGridlines(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //header with month titles Row monthRow = sheet.createRow(1); Font fontH = wb.createFont(); CellStyle CStyleH = wb.createCellStyle(); CStyleH.setBorderRight(CellStyle.BORDER_THICK); CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); fontH.setBold(true); CStyleH.setFont(fontH); for (int i = 0; i < days.length; i++) { Cell monthCell = monthRow.createCell(i); monthCell.setCellStyle(CStyleH); monthCell.setCellValue(days[i]); sheet.autoSizeColumn(i); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); Row rowGuardias; boolean bRowsCreated = false; // row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i); // Cell dayCell_2 = row.createCell(i*2 + 1); int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7; //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) { Font font = wb.createFont(); CellStyle CStyle = wb.createCellStyle(); short colorI = HSSFColor.AQUA.index; // presencia //font.set(colorI); CStyle.setFillForegroundColor(colorI); CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //CStyle.setBorderBottom( colorBorder); CStyle.setBorderRight(CellStyle.BORDER_THICK); CStyle.setBorderBottom(CellStyle.BORDER_THICK); CStyle.setBorderTop(CellStyle.BORDER_THICK); CStyle.setBorderLeft(CellStyle.BORDER_THICK); CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); //CStyle.setFont(font); dayCell_1.setCellValue(day); dayCell_1.setCellStyle(CStyle); sheet.autoSizeColumn(i); String _Dia = _format.format(calendar.getTime()); int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia for (int d = 0; d < lGuardias.length; d++) { Guardias oGuardias = lGuardias[d]; if (oGuardias.getDiaGuardia().equals(_Dia)) { if (!bRowsCreated) rowGuardias = sheet.createRow(rownum++); else rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont); Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i); // Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1); List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(), AdministratorUser.getServicioId()); Medico _oMedico = _lMedico.get(0); font = wb.createFont(); CStyle = wb.createCellStyle(); // PRESENCIA // LOCALIZADA //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre()); colorI = HSSFColor.LIGHT_ORANGE.index; // presencia if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase())) colorI = HSSFColor.GREEN.index; else if (oGuardias.getTipo() .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase())) colorI = HSSFColor.BLUE.index; else if (oGuardias.getTipo().equals("")) // residente colorI = HSSFColor.RED.index; font.setColor(colorI); CStyle.setFont(font); //CStyle.setBorderBottom( colorBorder); CStyle.setBorderRight(CellStyle.BORDER_THICK); CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre() + "[" + _oMedico.getIDMEDICO() + "]"); dayCell_1_GUARDIAS.setCellStyle(CStyle); DataRowCont++; } } bRowsCreated = true; // dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString()); calendar.set(Calendar.DAY_OF_MONTH, ++day); /*if(i == 0 || i == days.length-1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right"));*/ } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } // } // Write the output to a file String file = RutaFile; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }
From source file:com.hp.amss.util.HyperlinkExample.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);/*from w w w.j a v a2 s .c o m*/ Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //TODO cell.setCellValue(createHelper.createRichTextString("")); cell.setCellType(Cell.CELL_TYPE_STRING); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream("C:\\hyperinks.xlsx"); wb.write(out); out.close(); }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to./* w ww .j a va2 s .com*/ * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }