Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createSheet.

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:fi.semantum.strategia.Updates.java

License:Open Source License

private static void updateQueryGrid(final Main main, final FilterState state) {

    main.gridPanelLayout.removeAllComponents();
    main.gridPanelLayout.setMargin(false);

    final List<String> keys = state.reportColumns;
    if (keys.isEmpty()) {
        Label l = new Label("Kysely ei tuottanut yhtn tulosta.");
        l.addStyleName(ValoTheme.LABEL_BOLD);
        l.addStyleName(ValoTheme.LABEL_HUGE);
        main.gridPanelLayout.addComponent(l);
        return;/* www. j  ava  2s  . c o m*/
    }

    final IndexedContainer container = new IndexedContainer();

    for (String key : keys) {
        container.addContainerProperty(key, String.class, "");
    }

    rows: for (Map<String, ReportCell> map : state.report) {
        Object item = container.addItem();
        for (String key : keys)
            if (map.get(key) == null)
                continue rows;

        for (Map.Entry<String, ReportCell> entry : map.entrySet()) {
            @SuppressWarnings("unchecked")
            com.vaadin.data.Property<String> p = container.getContainerProperty(item, entry.getKey());
            p.setValue(entry.getValue().get());
        }

    }

    HorizontalLayout hl = new HorizontalLayout();
    hl.setWidth("100%");

    final TextField filter = new TextField();
    filter.addStyleName(ValoTheme.TEXTFIELD_TINY);
    filter.setInputPrompt("rajaa hakutuloksia - kirjoitetun sanan tulee lyty rivin teksteist");
    filter.setWidth("100%");

    final Image clipboard = new Image();
    clipboard.setSource(new ThemeResource("page_white_excel.png"));
    clipboard.setHeight("24px");
    clipboard.setWidth("24px");

    hl.addComponent(filter);
    hl.setExpandRatio(filter, 1.0f);
    hl.setComponentAlignment(filter, Alignment.BOTTOM_CENTER);
    hl.addComponent(clipboard);
    hl.setComponentAlignment(clipboard, Alignment.BOTTOM_CENTER);
    hl.setExpandRatio(clipboard, 0.0f);

    main.gridPanelLayout.addComponent(hl);
    main.gridPanelLayout.setExpandRatio(hl, 0f);

    filter.addValueChangeListener(new ValueChangeListener() {

        private static final long serialVersionUID = 3033918399018888150L;

        @Override
        public void valueChange(ValueChangeEvent event) {
            container.removeAllContainerFilters();
            container.addContainerFilter(new QueryFilter(filter.getValue(), true, false));
        }
    });

    AbsoluteLayout abs = new AbsoluteLayout();
    abs.setSizeFull();

    final Grid queryGrid = new Grid(container);
    queryGrid.setSelectionMode(SelectionMode.NONE);
    queryGrid.setHeightMode(HeightMode.CSS);
    queryGrid.setHeight("100%");
    queryGrid.setWidth("100%");

    for (String key : keys) {
        Column col = queryGrid.getColumn(key);
        col.setExpandRatio(1);
    }

    abs.addComponent(queryGrid);

    OnDemandFileDownloader dl = new OnDemandFileDownloader(new OnDemandStreamSource() {

        private static final long serialVersionUID = 981769438054780731L;

        File f;
        Date date = new Date();

        @Override
        public InputStream getStream() {

            String uuid = UUID.randomUUID().toString();
            File printing = new File(Main.baseDirectory(), "printing");
            f = new File(printing, uuid + ".xlsx");

            Workbook w = new XSSFWorkbook();
            Sheet sheet = w.createSheet("Sheet1");
            Row header = sheet.createRow(0);
            for (int i = 0; i < keys.size(); i++) {
                Cell cell = header.createCell(i);
                cell.setCellValue(keys.get(i));
            }

            int row = 1;
            rows: for (Map<String, ReportCell> map : state.report) {
                for (String key : keys)
                    if (map.get(key) == null)
                        continue rows;

                Row r = sheet.createRow(row++);
                int column = 0;
                for (int i = 0; i < keys.size(); i++) {
                    Cell cell = r.createCell(column++);
                    ReportCell rc = map.get(keys.get(i));
                    cell.setCellValue(rc.getLong());
                }

            }

            try {
                FileOutputStream s = new FileOutputStream(f);
                w.write(s);
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

            try {
                return new FileInputStream(f);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }

            throw new IllegalStateException();

        }

        @Override
        public void onRequest() {
            // TODO Auto-generated method stub

        }

        @Override
        public long getFileSize() {
            return f.length();
        }

        @Override
        public String getFileName() {
            return "Strategiakartta_" + Utils.dateString(date) + ".xlsx";
        }

    });

    dl.getResource().setCacheTime(0);
    dl.extend(clipboard);

    main.gridPanelLayout.addComponent(abs);
    main.gridPanelLayout.setExpandRatio(abs, 1f);

}

From source file:fi.thl.pivot.export.XlsxExporter.java

private void doExport(Model model, OutputStream out) throws IOException {

    Map<String, ?> params = model.asMap();
    Workbook wb = new XSSFWorkbook();

    createExportStyles(wb);//ww w . ja va 2s .  com

    Sheet sheet = wb.createSheet(
            WorkbookUtil.createSafeSheetName(((Label) params.get("cubeLabel")).getValue(language)));

    Pivot pivot = (Pivot) params.get("pivot");
    int rowNumber = 0;
    boolean showCodes = params.containsKey("sc");

    rowNumber = createColumnHeaders(pivot, sheet, showCodes);
    rowNumber = printData(sheet, pivot, rowNumber, showCodes);
    mergeRowHeaders(sheet, pivot);
    rowNumber = printFilters(params, sheet, rowNumber, pivot.getColumnCount() + pivot.getColumns().size());
    printCopyrightNotice(sheet, rowNumber, params, pivot.getColumnCount() + pivot.getColumns().size());
    printCurrentMeasureIfOnlyOneMeasureShown(params, sheet, pivot);
    mergeTopLeftCorner(sheet, pivot);

    autosizeColumns(sheet, pivot);
    sheet.createFreezePane(pivot.getRows().size(), pivot.getColumns().size());

    wb.write(out);
    wb.close();
}

From source file:file.save.spreadsheet.model.SaveXlsxFileModel.java

License:Open Source License

/**
 * Set the path to the file that defines the new Model.
 * @param path to the file containing the model data
 *//*from w  w  w .  j a  va  2s. c  om*/
@Override
public void setModel(String path, double[][] matrix) throws IOException, InvalidFormatException {
    this.matrix = matrix;

    Workbook wb = new XSSFWorkbook();

    Sheet sheet = wb.createSheet("Simulyn");

    for (int i = 0; i < this.matrix.length; i++) {
        sheet.createRow(i);
        for (int j = 0; j < this.matrix[i].length; j++) {
            sheet.getRow(i).createCell(j, Cell.CELL_TYPE_NUMERIC).setCellValue(this.matrix[i][j]);
        }
    }

    FileOutputStream outPutStream = null;
    try {
        outPutStream = new FileOutputStream(path);
        wb.write(outPutStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (outPutStream != null) {
            try {
                outPutStream.flush();
                outPutStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:FormatConvert.exceloperation.ExcelOperation.java

public static void MergeExcel(String sourceDir, String targetexcel) {
    try {//w  w w .j  a va  2 s .  co m
        File[] filelist = FilelistReader.getFileList(sourceDir);
        FileOutputStream fileOut = new FileOutputStream(targetexcel);
        Workbook wb = new XSSFWorkbook();
        for (int i = 0; i < filelist.length; i++) {
            File f = filelist[i];
            if (f.getName().endsWith(".xlsx")) {
                FileInputStream is = (new FileInputStream(f));
                XSSFWorkbook wb2 = new XSSFWorkbook(is);
                XSSFSheet sheet = wb2.getSheetAt(0);
                Sheet sheetnew = wb.createSheet(f.getName());
                //System.out.println(sheet.get);
                Util.copySheets((XSSFSheet) sheetnew, sheet);

            } else if (f.getName().endsWith(".xls")) {
                FileInputStream is = (new FileInputStream(f));
                HSSFWorkbook wb2 = new HSSFWorkbook(is);
                HSSFSheet sheet = wb2.getSheetAt(0);
                Sheet sheetnew = wb.createSheet(f.getName());
                Util.copySheets((HSSFSheet) sheetnew, sheet);
            }
        }
        wb.write(fileOut);

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

From source file:FormatConvert.tab2excel.java

public void tab2excel() throws FileNotFoundException, IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet1 = wb.createSheet("input");

    CellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    BufferedReader br = null;/*from   w  w  w .  jav  a  2  s .  c  o  m*/
    try {
        br = new BufferedReader(new FileReader(new File(input)));
        String[] str;
        while (br.ready()) {
            str = br.readLine().split(seperator);

            Row row = sheet1.createRow(rowIndex);
            for (int i = 0; i < str.length; i++) {
                row.createCell(i).setCellValue(str[i]);
            }
            rowIndex++;
        }
        br.close();
    } catch (FileNotFoundException ex) {
        System.out.println(input + " is not found! please check your filepath ");
    } catch (IOException ex) {
        System.out.println("IO error");
    }

    FileOutputStream fileOut = new FileOutputStream(output + ".xlsx");
    wb.write(fileOut);
    fileOut.close();
    System.out.println("Convert finished. The output file is named as " + output + ".xlsx");
}

From source file:forseti.reportes.JReportesDlg.java

License:Open Source License

public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb)
        throws ServletException, IOException {
    JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet");
    JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1");
    JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1");
    Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1");
    Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2");
    Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3");
    Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1");
    Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2");
    Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3");
    JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1");
    JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2");
    JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3");
    JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1");
    JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2");
    JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3");

    String fsi_filtro = (String) request.getAttribute("fsi_filtro");

    Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report()));

    short nrow = 0;
    Row row = sheet.createRow(nrow++);//from   w ww .  j a v a 2  s .  c  o  m
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    Cell cell = row.createCell(0);
    cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    JRepCellStyles cellStyles = new JRepCellStyles(wb);
    /*
    Font fenc = wb.createFont();
    fenc.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fenc.setColor(HSSFColor.WHITE.index);
            
    Font fnorm = wb.createFont();
    fnorm.setColor(HSSFColor.BLACK.index);
            
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(font);
            
    if(alin != null)
    {
      if(alin.equals("center"))
     cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
      else if(alin.equals("right"))
     cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
      else
     cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
    }
            
    if(tipocel != null)
    {
      if(tipocel.equals("encabezado"))
      {
     cellStyle.setBorderTop(CellStyle.BORDER_THIN);
     cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
     cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
     cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
             
      }
      else if(tipocel.equals("agregado"))
      {
     cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
     cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
      }
    }
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    */

    if (m_bSelectL1.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL1.getNumRows(); i++) {
            if (m_colL1.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL2.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL2.getNumRows(); i++) {
            if (m_colL2.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL3.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL3.getNumRows(); i++) {
            if (m_colL3.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL1.booleanValue()) {
        for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) {
            Row rowl1 = sheet.createRow(nrow++);
            for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) {
                if (m_colL1.getAbsRow(CL1).getWillShow())
                    JUtil.DatoXLS(cellStyles, rowl1, CL1,
                            m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()),
                            m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(),
                            m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request);
            }

            // Nivel 2
            if (m_bSelectL2.booleanValue()) {
                for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) {
                    Row rowl2 = sheet.createRow(nrow++);
                    for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) {
                        if (m_colL2.getAbsRow(CL2).getWillShow())
                            JUtil.DatoXLS(cellStyles, rowl2, CL2,
                                    m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2)
                                            .getSTS(m_colL2.getAbsRow(CL2).getColName()),
                                    m_colL2.getAbsRow(CL2).getFormat(),
                                    m_colL2.getAbsRow(CL2).getBindDataType(),
                                    m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request);
                    }

                    // Nivel 3
                    if (m_bSelectL3.booleanValue()) {
                        for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                .getNumRows(); RL3++) {
                            Row rowl3 = sheet.createRow(nrow++);
                            for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) {
                                if (m_colL3.getAbsRow(CL3).getWillShow())
                                    JUtil.DatoXLS(cellStyles, rowl3, CL3,
                                            m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                                    .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()),
                                            m_colL3.getAbsRow(CL3).getFormat(),
                                            m_colL3.getAbsRow(CL3).getBindDataType(),
                                            m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request);

                            }

                        }
                        if (m_bComputeL3.booleanValue()) {
                            for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                    .getNumRows(); RC3++) {
                                Row rowc3 = sheet.createRow(nrow++);
                                for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) {
                                    if (m_colCL3.getAbsRow(CC3).getWillShow())
                                        JUtil.DatoXLS(cellStyles, rowc3, CC3,
                                                m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                                        .getAbsRow(RC3)
                                                        .getSTS(m_colCL3.getAbsRow(CC3).getColName()),
                                                m_colCL3.getAbsRow(CC3).getFormat(),
                                                m_colCL3.getAbsRow(CC3).getBindDataType(),
                                                m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc",
                                                request);
                                }

                            }
                        } // Fin SI CL3
                    } // Fin SI L3
                }
                if (m_bComputeL2.booleanValue()) {
                    for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) {
                        Row rowc2 = sheet.createRow(nrow++);
                        for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) {
                            if (m_colCL2.getAbsRow(CC2).getWillShow())
                                JUtil.DatoXLS(cellStyles, rowc2, CC2,
                                        m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2)
                                                .getSTS(m_colCL2.getAbsRow(CC2).getColName()),
                                        m_colCL2.getAbsRow(CC2).getFormat(),
                                        m_colCL2.getAbsRow(CC2).getBindDataType(),
                                        m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request);
                        }
                    }
                } // Fin SI CL2
            } // Fin SI L2
        }
        if (m_bComputeL1.booleanValue()) {
            for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) {
                Row rowc1 = sheet.createRow(nrow++);
                for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) {
                    if (m_colCL1.getAbsRow(CC1).getWillShow())
                        JUtil.DatoXLS(cellStyles, rowc1, CC1,
                                m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()),
                                m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(),
                                m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request);
                }
            }
        } // Fin SI CL1
    } // Fin SI L1

    int colsmer;
    if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL1.getNumRows() - 1;
    else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL2.getNumRows() - 1;
    else
        colsmer = m_colL3.getNumRows() - 1;

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer)));

}

From source file:fsi_admin.reportes.JReportesDlg.java

License:Open Source License

public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb)
        throws ServletException, IOException {
    JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet");
    JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1");
    JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1");
    Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1");
    Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2");
    Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3");
    Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1");
    Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2");
    Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3");
    JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1");
    JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2");
    JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3");
    JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1");
    JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2");
    JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3");

    String fsi_filtro = (String) request.getAttribute("fsi_filtro");

    Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report()));

    short nrow = 0;
    Row row = sheet.createRow(nrow++);//from  ww  w  .j  a  v  a 2  s .  co  m
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    Cell cell = row.createCell(0);
    cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    JRepCellStyles cellStyles = new JRepCellStyles(wb);

    if (m_bSelectL1.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL1.getNumRows(); i++) {
            if (m_colL1.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL2.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL2.getNumRows(); i++) {
            if (m_colL2.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL3.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL3.getNumRows(); i++) {
            if (m_colL3.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL1.booleanValue()) {
        for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) {
            Row rowl1 = sheet.createRow(nrow++);
            for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) {
                if (m_colL1.getAbsRow(CL1).getWillShow())
                    JUtil.DatoXLS(cellStyles, rowl1, CL1,
                            m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()),
                            m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(),
                            m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request);
            }

            // Nivel 2
            if (m_bSelectL2.booleanValue()) {
                for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) {
                    Row rowl2 = sheet.createRow(nrow++);
                    for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) {
                        if (m_colL2.getAbsRow(CL2).getWillShow())
                            JUtil.DatoXLS(cellStyles, rowl2, CL2,
                                    m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2)
                                            .getSTS(m_colL2.getAbsRow(CL2).getColName()),
                                    m_colL2.getAbsRow(CL2).getFormat(),
                                    m_colL2.getAbsRow(CL2).getBindDataType(),
                                    m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request);
                    }

                    // Nivel 3
                    if (m_bSelectL3.booleanValue()) {
                        for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                .getNumRows(); RL3++) {
                            Row rowl3 = sheet.createRow(nrow++);
                            for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) {
                                if (m_colL3.getAbsRow(CL3).getWillShow())
                                    JUtil.DatoXLS(cellStyles, rowl3, CL3,
                                            m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                                    .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()),
                                            m_colL3.getAbsRow(CL3).getFormat(),
                                            m_colL3.getAbsRow(CL3).getBindDataType(),
                                            m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request);

                            }

                        }
                        if (m_bComputeL3.booleanValue()) {
                            for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                    .getNumRows(); RC3++) {
                                Row rowc3 = sheet.createRow(nrow++);
                                for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) {
                                    if (m_colCL3.getAbsRow(CC3).getWillShow())
                                        JUtil.DatoXLS(cellStyles, rowc3, CC3,
                                                m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                                        .getAbsRow(RC3)
                                                        .getSTS(m_colCL3.getAbsRow(CC3).getColName()),
                                                m_colCL3.getAbsRow(CC3).getFormat(),
                                                m_colCL3.getAbsRow(CC3).getBindDataType(),
                                                m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc",
                                                request);
                                }
                            }
                        } // Fin SI CL3
                    } // Fin SI L3
                }
                if (m_bComputeL2.booleanValue()) {
                    for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) {
                        Row rowc2 = sheet.createRow(nrow++);
                        for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) {
                            if (m_colCL2.getAbsRow(CC2).getWillShow())
                                JUtil.DatoXLS(cellStyles, rowc2, CC2,
                                        m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2)
                                                .getSTS(m_colCL2.getAbsRow(CC2).getColName()),
                                        m_colCL2.getAbsRow(CC2).getFormat(),
                                        m_colCL2.getAbsRow(CC2).getBindDataType(),
                                        m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request);
                        }
                    }
                } // Fin SI CL2
            } // Fin SI L2
        }

        if (m_bComputeL1.booleanValue()) {
            for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) {
                Row rowc1 = sheet.createRow(nrow++);
                for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) {
                    if (m_colCL1.getAbsRow(CC1).getWillShow())
                        JUtil.DatoXLS(cellStyles, rowc1, CC1,
                                m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()),
                                m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(),
                                m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request);
                }
            }
        } // Fin SI CL1
    } // Fin SI L1

    int colsmer;
    if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL1.getNumRows() - 1;
    else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL2.getNumRows() - 1;
    else
        colsmer = m_colL3.getNumRows() - 1;

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer)));

}

From source file:ga_assignment.GeneticAlgorithm.java

public static void main(String[] args) throws FileNotFoundException, IOException {

    GeneticAlgorithm ga = new GeneticAlgorithm();

    // Reads in training set
    Rule[] rules;//  w w w .ja  v  a  2 s .c  o  m
    rules = new Rule[32];
    Scanner inin = new Scanner(new FileReader("data1.txt"));
    for (int x = 0; x < 32; x++) {
        Rule rule = new Rule();
        rule.setCondition(inin.next());
        rule.setOutput(inin.nextInt());
        rules[x] = rule;
    }

    //set up export to excel
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("sheet");
    Row row = sheet.createRow((short) 0);
    row.createCell(0).setCellValue(wb.getCreationHelper().createRichTextString("Generations"));
    row.createCell(1).setCellValue(wb.getCreationHelper().createRichTextString("Best Fitness"));
    row.createCell(2).setCellValue(wb.getCreationHelper().createRichTextString("Mean Fitness"));
    int rowNumber = 1;

    Random rand = new Random();
    Individual in = new Individual();
    FitnessCalculator fit = new FitnessCalculator();

    ArrayList<Individual> population = new ArrayList<>();
    ArrayList<Individual> offspring = new ArrayList<>();

    int i;
    int j;
    int[] gene;
    gene = new int[ga.N];
    int tempFitness = 0;
    int totalFitness = 0;

    int optimalFitness = (ga.N / 6) * ga.P;
    System.out.println("OPTIMAL FITNESS = " + optimalFitness);

    //
    //
    //
    //
    //
    //
    // ======================================= INITIALISATION =======================================
    for (i = 0; i < ga.P; i++) { // for i is less than the population limit
        tempFitness = 0;

        for (j = 0; j < ga.N; j++) { // for j is less than the number of genes in an individual
            gene[j] = rand.nextInt(2) % 2; // population[individual] gets genes (10010) set
        } // repeat until 5 individuals have 5 genes

        // converts gene to string
        String geno = "";
        for (int a : gene) {
            geno += Integer.toString(a);
        }

        // creates a new individual
        Individual ind = new Individual();
        ind.setGene(geno);
        ind.setFitness(tempFitness);
        population.add(ind);
    }

    // gets total fitness of original population
    for (Individual fitt : population) {
        fit.fitnessCalculator(fitt, rules);
        totalFitness = fitt.getFitness() + totalFitness;
        System.out.println("gene = " + fitt.getGene() + " fit = " + fitt.getFitness());
    }
    System.out.println("Initial Total Fitness = " + totalFitness + "\n");

    //
    //
    //
    //
    //
    //
    // ======================================= PARENT SELECTION =======================================
    // 
    for (j = 0; j < ga.G; j++) {
        if (totalFitness < 500000) {
            totalFitness = 0;

            // Loop through Population
            for (i = 0; i < ga.P; i++) {

                Individual winner1;

                // chooses X individuals at random     
                Individual parent1 = population.get(new Random().nextInt(population.size()));
                Individual parent2 = population.get(new Random().nextInt(population.size()));

                // compares parents based on fitness
                if (parent1.getFitness() >= parent2.getFitness()) {
                    winner1 = parent1;
                } else {
                    winner1 = parent2;
                }

                offspring.add(winner1);

            }

            //
            //
            //
            //
            //
            //
            // ======================================= CROSSOVER / MUTATION =======================================
            for (i = 0; i < ga.P; i++) {

                // get random bit from individual
                int pos1 = new Random().nextInt(offspring.size());
                Individual child1 = offspring.get(pos1);

                int pos2 = new Random().nextInt(offspring.size());
                Individual child2 = offspring.get(pos2);

                // Crossover between two individuals
                in.Crossover(child1, child2);

                // Mutation of individual 1
                String ds = child1.getGene();
                Individual c1 = new Individual();
                c1.setGene(ds);
                in.Mutator(c1, pos1);
                offspring.remove(pos1);
                offspring.add(c1);

                // Calculate fitness of both individuals
                fit.fitnessCalculator(c1, rules);

            }

            //
            //
            //
            //
            //
            //
            // ======================================= RESET POPULATION =======================================

            // calculates offspring's total fitness
            for (Individual fitt : offspring) {
                totalFitness = fitt.getFitness() + totalFitness;
            }

            // REMOVE WORST AND ADD BEST
            // finds best fitness from population
            Individual best = in;
            int bestFitness = 0;
            for (Individual ind : population) {
                if (ind.getFitness() > bestFitness) {
                    bestFitness = ind.getFitness();
                    best = ind;
                }
            }

            // finds the worst fitness
            String p = "d";
            int low = 0;
            int worstFitness = 33;
            // find lowest fitness in offspring 
            for (int lo = 0; lo < offspring.size(); lo++) {
                if (offspring.get(lo).getFitness() < worstFitness) {
                    worstFitness = offspring.get(lo).getFitness();
                    p = offspring.get(lo).getGene();
                    low = lo;
                }
            }
            System.out.println("Lowest Fitness " + worstFitness);

            // Replace worst fitness with best from parent population
            offspring.remove(low);
            offspring.add(best);

            // Find the best individual in the offspring
            bestFitness = 0;
            for (Individual ind : offspring) {
                if (ind.getFitness() > bestFitness) {
                    bestFitness = ind.getFitness();
                    p = ind.gene;
                }
            }

            // Calculate the mean fitness
            int meanFitness = 0;
            meanFitness = totalFitness / ga.P;

            System.out.println("Best Individual: " + p + " f: " + bestFitness);
            System.out.println("Total Offspring Fitness: " + totalFitness + " Mean: " + meanFitness + " (Run "
                    + (j + 1) + ")\n");

            //export to excel
            Row row1 = sheet.createRow((short) rowNumber);
            row1.createCell(0).setCellValue((j + 1));
            row1.createCell(1).setCellValue(bestFitness);
            row1.createCell(2).setCellValue(meanFitness);
            rowNumber++;

            population.clear();

            //Move offspring into population
            for (int a = 0; a < offspring.size(); a++) {
                Individual transfer = offspring.get(a);
                population.add(transfer);
            }

            //Clear offspring
            offspring.clear();

        } else {
            for (Individual fitt : population) {

                System.out.println("gene = " + fitt.getGene() + " fit = " + fitt.getFitness());
            }

            //export to excel
            FileOutputStream fileOut = new FileOutputStream("GA_Results.xls");
            wb.write(fileOut);
            fileOut.close();

            System.out.println("\nComplete");
            System.exit(0);
        }
    }

    //export to excel
    FileOutputStream fileOut = new FileOutputStream("GA_Results.xls");
    wb.write(fileOut);
    fileOut.close();

}

From source file:GeMSE.IO.Exporter.java

License:Open Source License

private void ExportToExcelWorkbook(File file) {
    Workbook wb = new HSSFWorkbook();

    try {//from   ww w.  j  a v  a 2s  .com
        try (FileOutputStream fileOut = new FileOutputStream(file)) {
            Sheet sheet = wb.createSheet("GeMSE_sheet");

            //Create 2D Cell Array
            Row[] row = new Row[_data.length];
            Cell[][] cell = new Cell[row.length][];

            for (int i = 0; i < row.length; i++) {
                row[i] = sheet.createRow(i);
                cell[i] = new Cell[_data[i].length];

                for (int j = 0; j < cell[i].length; j++) {
                    cell[i][j] = row[i].createCell(j);
                    cell[i][j].setCellValue(_data[i][j]);
                }
            }

            wb.write(fileOut);
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(Exporter.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Exporter.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:GestoSAT.Albaran.java

License:Open Source License

public String albaran2XLSX() {
    String archivo = "Albaran" + (new Date()).getTime() + ".xlsx";
    try {//w  w  w  .  j a va  2s .  c  o  m
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Albarn");
        Row row;
        Cell cell;

        Oficina o = this.getGestoSAT().getEmpleado().getOficina();

        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Empresa");
        cell = row.createCell(1);
        cell.setCellValue(o.getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Direccin");
        row = sheet.createRow((short) 3);
        cell = row.createCell(0);
        cell.setCellValue(o.getProvincia());
        cell = row.createCell(1);
        cell.setCellValue(o.getPoblacion());
        cell = row.createCell(2);
        cell.setCellValue(o.getCalle());
        cell = row.createCell(3);
        cell.setCellValue(o.getNumero());

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 7);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());

        row = sheet.createRow((short) 9);
        cell = row.createCell(0);
        cell.setCellValue("Datos Albarn");

        row = sheet.createRow((short) 10);
        cell = row.createCell(0);
        cell.setCellValue("Concepto");
        cell = row.createCell(1);
        cell.setCellValue(this.concepto);
        row = sheet.createRow((short) 11);
        cell = row.createCell(0);
        cell.setCellValue("Observaciones");
        cell = row.createCell(1);
        cell.setCellValue(this.getObservaciones());

        row = sheet.createRow((short) 13);
        cell = row.createCell(0);
        cell.setCellValue("Provincia");
        cell = row.createCell(1);
        cell.setCellValue("Poblacin");
        cell = row.createCell(2);
        cell.setCellValue("Calle");
        cell = row.createCell(3);
        cell.setCellValue("Nmero");
        cell = row.createCell(4);
        cell.setCellValue("Escalera");
        cell = row.createCell(5);
        cell.setCellValue("Piso");
        cell = row.createCell(6);
        cell.setCellValue("Puerta");

        row = sheet.createRow((short) 14);
        cell = row.createCell(0);
        cell.setCellValue(this.provincia);
        cell = row.createCell(1);
        cell.setCellValue(this.poblacion);
        cell = row.createCell(2);
        cell.setCellValue(this.calle);
        cell = row.createCell(3);
        cell.setCellValue(this.numero);
        cell = row.createCell(4);
        cell.setCellValue(this.escalera);
        cell = row.createCell(5);
        cell.setCellValue(this.piso);
        cell = row.createCell(6);
        cell.setCellValue(this.puerta);

        float total = 0;
        DecimalFormat df = new DecimalFormat("0.00");

        if (!this.trabajoRealizado.isEmpty()) {
            row = sheet.createRow((short) 16);
            cell = row.createCell(0);
            cell.setCellValue("Trabajos presupuestados");

            row = sheet.createRow((short) 18);
            cell = row.createCell(0);
            cell.setCellValue("Nombre");
            cell = row.createCell(1);
            cell.setCellValue("Precio h");
            cell = row.createCell(2);
            cell.setCellValue("Horas");
            cell = row.createCell(3);
            cell.setCellValue("Total");

            Iterator itTrabajos = this.trabajoRealizado.entrySet().iterator();
            for (int index = 19; itTrabajos.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itTrabajos.next();
                Trabajo trabajo = (Trabajo) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(
                        trabajo.getEmpleado().getNombre() + " " + trabajo.getEmpleado().getApellidos());
                cell = row.createCell(1);
                cell.setCellValue(trabajo.getEmpleado().getPrecioHora());
                cell = row.createCell(2);
                cell.setCellValue(trabajo.getHoras());
                cell = row.createCell(3);
                cell.setCellValue(df.format(trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras()));
                total += trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras();
            }
        }
        int valueIndex = row.getRowNum();

        if (!this.materialUtilizado.isEmpty()) {
            valueIndex++;
            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Materiales presupuestados");

            valueIndex++;
            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("#");
            cell = row.createCell(1);
            cell.setCellValue("Nombre");
            cell = row.createCell(2);
            cell.setCellValue("Precio Ud");
            cell = row.createCell(3);
            cell.setCellValue("Cantidad");
            cell = row.createCell(4);
            cell.setCellValue("Total");

            Iterator itMateriales = this.materialUtilizado.entrySet().iterator();
            valueIndex++;
            for (int index = valueIndex; itMateriales.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itMateriales.next();
                MaterialTrabajos material = (MaterialTrabajos) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(aux.getKey().toString());
                cell = row.createCell(1);
                cell.setCellValue(material.getStock().getNombre());
                cell = row.createCell(2);
                cell.setCellValue(material.getStock().getPrecioUnidad());
                cell = row.createCell(3);
                cell.setCellValue(material.getCantidad());
                cell = row.createCell(4);
                cell.setCellValue(df.format(material.getStock().getPrecioUnidad() * material.getCantidad()));
                total += material.getStock().getPrecioUnidad() * material.getCantidad();
                valueIndex = index;
            }
        }

        float iva = this.getGestoSAT().getIva() / (float) 100;
        valueIndex++;
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(iva * total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("Total sin I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex + 1);
        cell = row.createCell(0);
        cell.setCellValue("Total");
        cell = row.createCell(1);
        cell.setCellValue(df.format(this.total));

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}