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:archivocsv.models.Reporte.java

public static ArrayList<Reporte> generarReporteExcel(String anio, String mes) throws IOException {
    ArrayList<Reporte> archivoExcel = new ArrayList<>();
    try {/* w w  w  .  j a  v a 2  s  .  co  m*/
        FileChooser elegirArchivo = new FileChooser();
        FileChooser.ExtensionFilter filtroExt = new FileChooser.ExtensionFilter("Archivos Excel (*.xls)",
                "*.xls");
        elegirArchivo.getExtensionFilters().add(filtroExt);
        File archivo = elegirArchivo.showSaveDialog(archivoCsv.getVentanaPrincipal());
        archivo.createNewFile();
        Workbook libro = new HSSFWorkbook();
        FileOutputStream enviar = new FileOutputStream(archivo);
        Sheet hoja = libro.createSheet("Reportes");
        Sheet hoja2 = libro.createSheet("Entradas Tarde");
        Row fila = hoja.createRow(0);
        Row fila2 = hoja2.createRow(0);
        for (int c = 0; c < 5; c++) {
            Cell celda = fila.createCell(c);
            if (c == 0) {
                celda.setCellValue("ID");
            }
            if (c == 1) {
                celda.setCellValue("Nombre");
            }
            if (c == 2) {
                celda.setCellValue("Entrada");
            }
            if (c == 3) {
                celda.setCellValue("Salida");
            }
            if (c == 4) {
                celda.setCellValue("Horas Trabajadas");
            }
        }
        for (int c2 = 0; c2 < 3; c2++) {
            Cell celda2 = fila2.createCell(c2);
            if (c2 == 0) {
                celda2.setCellValue("ID");
            }
            if (c2 == 1) {
                celda2.setCellValue("Nombre");
            }
            if (c2 == 2) {
                celda2.setCellValue("Entrada");
            }
        }
        Connection con = Helper.getConnection();
        String entrada = "SELECT * FROM t_marcacion WHERE marcacion LIKE '%/" + mes + "/" + anio + "%'"
                + " AND marcacion LIKE '%a%'";
        ResultSet rs = con.createStatement().executeQuery(entrada);
        ResultSet rs3 = con.createStatement().executeQuery(entrada);
        int contar = 0;
        int contar2 = 1;
        while (rs.next()) {
            contar += 1;
            Row filaDatos = hoja.createRow(contar);
            Cell celdaID = filaDatos.createCell(0);
            Cell celdaNombre = filaDatos.createCell(1);
            Cell celdaEntrada = filaDatos.createCell(2);
            Cell celdaSalida = filaDatos.createCell(3);
            Cell celdaHoras = filaDatos.createCell(4);
            Reporte reporte = new Reporte();
            reporte.setEntrada(rs.getString("marcacion"));
            celdaEntrada.setCellValue(reporte.getEntrada());
            reporte.setId(rs.getString("id"));
            celdaID.setCellValue(reporte.getId());
            reporte.setNombre(rs.getString("nombre"));
            celdaNombre.setCellValue(reporte.getNombre());
            //Query que verifica si existe el registro de salida
            String coincidir = "SELECT * FROM t_marcacion WHERE id=" + rs.getString("id")
                    + " AND marcacion LIKE '%" + rs.getString("marcacion").substring(0, 10) + "%'"
                    + " AND marcacion LIKE '%p%'";
            ResultSet rs2 = con.createStatement().executeQuery(coincidir);
            if (rs2.next()) {
                reporte.setSalida(rs2.getString("marcacion"));
                celdaSalida.setCellValue(reporte.getSalida());
                String horaInicial = rs.getString("marcacion").substring(11, 15) + " AM";
                String horaFinal = rs2.getString("marcacion").substring(11, 15) + " PM";
                if (horaFinal.substring(0, 2).equals("12")) {
                    horaFinal = rs2.getString("marcacion").substring(11, 15) + " AM";
                }
                DateFormat sdf = new SimpleDateFormat("KK:mm a");
                Date date = sdf.parse(horaInicial);
                Date date2 = sdf.parse(horaFinal);

                double hrsInicialMs = date.getTime();
                double hrsFinalMs = date2.getTime();
                double diferencia = hrsFinalMs - hrsInicialMs;
                double resta = (diferencia / (1000 * 60 * 60));
                int primerNumero = (int) resta;
                double segundoNumero = resta - primerNumero;
                int convertirNumero = (int) (segundoNumero * 60);
                int restarAlumerzo = primerNumero - 1;
                if (convertirNumero == 0) {
                    reporte.setHorasTrabajadas(restarAlumerzo + ":00");
                    celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                } else {
                    if (convertirNumero > 0 & convertirNumero < 10) {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":0" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    } else {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    }
                }
            } else {
                reporte.setSalida("Sin registro");
                celdaSalida.setCellValue(reporte.getSalida());
                reporte.setHorasTrabajadas("Sin registro");
                celdaHoras.setCellValue(reporte.getHorasTrabajadas());
            }
        }
        while (rs3.next()) {
            Row filaDatos2 = hoja2.createRow(contar2);
            Cell celdaId = filaDatos2.createCell(0);
            Cell celdaNombre = filaDatos2.createCell(1);
            Cell celdaMarcacion = filaDatos2.createCell(2);
            Reporte reporte = new Reporte();
            DateFormat sdf = new SimpleDateFormat("KK:mm a");
            String horaInicio = "08:00 AM";
            String horaFin = "12:00 PM";
            String horaEntrada = rs3.getString("marcacion");
            if (horaEntrada.substring(11, 12).equals("0")) {
                horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
            } else {
                if (horaEntrada.substring(11, 13).equals("10") || horaEntrada.substring(11, 13).equals("11")) {
                    horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
                } else {
                    horaEntrada = rs3.getString("marcacion").substring(11, 15) + " AM";
                }
            }
            Date hrInicio = sdf.parse(horaInicio);
            Date hrFin = sdf.parse(horaFin);
            Date hrEntrada = sdf.parse(horaEntrada);
            Calendar calInicio = new GregorianCalendar();
            Calendar calFin = new GregorianCalendar();
            Calendar calEntrada = new GregorianCalendar();
            calInicio.setTime(hrInicio);
            calFin.setTime(hrFin);
            calEntrada.setTime(hrEntrada);

            if (calEntrada.after(calInicio) & calEntrada.before(calFin)) {
                contar2 += 1;
                reporte.setId(rs3.getString("id"));
                celdaId.setCellValue(reporte.getId());
                reporte.setNombre(rs3.getString("nombre"));
                celdaNombre.setCellValue(reporte.getNombre());
                reporte.setEntrada(rs3.getString("marcacion"));
                celdaMarcacion.setCellValue(reporte.getEntrada());
                archivoExcel.add(reporte);
            }
        }
        hoja.setColumnWidth(0, 850);
        hoja.setColumnWidth(1, 3000);
        hoja.setColumnWidth(2, 6000);
        hoja.setColumnWidth(3, 6000);
        hoja.setColumnWidth(4, 4000);
        hoja2.setColumnWidth(0, 850);
        hoja2.setColumnWidth(1, 3000);
        hoja2.setColumnWidth(2, 6000);
        libro.write(enviar);
        enviar.close();
        Desktop.getDesktop().open(archivo);
    } catch (Exception e) {

    }
    return archivoExcel;
}

From source file:at.fh.swenga.firefighters.report.ExcelFireEngineReportView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    List<FireEngineModel> fireEngines = (List<FireEngineModel>) model.get("fireEngines");

    // create a worksheet
    Sheet sheet = workbook.createSheet("FireEngine Report");

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);/*  w  w  w.j  a  v a2  s  . c  o  m*/

    // create a new row in the worksheet
    Row headerRow = sheet.createRow(0);

    // create a new cell in the row
    Cell cell0 = headerRow.createCell(0);
    cell0.setCellValue("ID");
    cell0.setCellStyle(style);

    // create a new cell in the row
    Cell cell1 = headerRow.createCell(1);
    cell1.setCellValue("Modell");
    cell1.setCellStyle(style);

    // create a new cell in the row
    Cell cell2 = headerRow.createCell(2);
    cell2.setCellValue("Kennzeichen");
    cell2.setCellStyle(style);

    // create a new cell in the row
    Cell cell3 = headerRow.createCell(3);
    cell3.setCellValue("Leistung");
    cell3.setCellStyle(style);

    // create a new cell in the row
    Cell cell4 = headerRow.createCell(4);
    cell4.setCellValue("Baujahr");
    cell4.setCellStyle(style);

    // create a new cell in the row
    Cell cell5 = headerRow.createCell(5);
    cell5.setCellValue("Aktiv");
    cell5.setCellStyle(style);

    // create a new cell in the row
    Cell cell6 = headerRow.createCell(6);
    cell6.setCellValue("Funktion");
    cell6.setCellStyle(style);

    // create a new cell in the row
    Cell cell7 = headerRow.createCell(7);
    cell7.setCellValue("Feuerwehr");
    cell7.setCellStyle(style);

    // create multiple rows with fireEngines data
    int rowNum = 1;
    for (FireEngineModel fireEngine : fireEngines) {
        // create the row data
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(fireEngine.getId());
        row.createCell(1).setCellValue(fireEngine.getModel());
        row.createCell(2).setCellValue(fireEngine.getLicensePlate());
        row.createCell(3).setCellValue(fireEngine.getPerformance());
        row.createCell(4).setCellValue(fireEngine.getBuildYear());
        row.createCell(5).setCellValue(fireEngine.getActive());
        row.createCell(6).setCellValue(fireEngine.getAbbreviation().getAbbreviation());
        row.createCell(7).setCellValue(fireEngine.getFireBrigade().getName());
    }

    // adjust column width to fit the content
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
    sheet.autoSizeColumn((short) 7);

}

From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java

License:Open Source License

public static void saveExperimentToExcel(Experiment experiment, String filename) throws IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Quokka Multi-Sample Scan");
    int rowCounter = 0;
    if (experiment.isControlledEnvironment()) {
        for (ControlledAcquisition acquisition : experiment.getAcquisitionGroups()) {
            for (SampleEnvironment sampleEnvironment : experiment.getSampleEnvironments()) {
                Row row = sheet.createRow(rowCounter++);
                Cell cell = row.createCell(0);
                cell.setCellValue(sampleEnvironment.getControllerId());
                cell = row.createCell(1);
                cell.setCellValue(acquisition.getEnvSettings().get(sampleEnvironment).getPreset());
            }/*from w  w  w .j  a v  a 2  s .c  om*/
            rowCounter = saveAcquisitionToExcel(acquisition, sheet, rowCounter);
        }
    } else {
        rowCounter = saveAcquisitionToExcel(experiment.getNormalAcquisition(), sheet, rowCounter);
    }
    FileOutputStream fileOut = new FileOutputStream(filename);
    workbook.write(fileOut);
    fileOut.close();
}

From source file:balony.tableWriter.java

public static void writeTable(JFrame parent, Object[][] tableData, String[] colNames) {
    String opts[] = { "Tab-delimited text (raw)", "Excel .xls", "Excel 2007+ .xlsx" };
    int i = JOptionPane.showOptionDialog(parent, "Choose output format:", "Export Table",
            JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null, opts, opts[0]);
    if (i == JOptionPane.CLOSED_OPTION) {
        return;/*from   w ww  . j av  a 2s  .c o m*/
    }

    String[] chars = { ":", "[", "]", "/", "\\", "?", "*" };
    String fname = parent.getTitle();
    for (String c : chars) {
        fname = fname.replace(c, "");
    }

    JFileChooser jfc = new JFileChooser();
    if (i == 0) {
        jfc.setFileFilter(new FileNameExtensionFilter("Tab-delimited text files", "txt"));
        jfc.setSelectedFile(new File(fname.concat(".txt")));
    }

    if (i == 1) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xls files", "xls"));
        jfc.setSelectedFile(new File(fname.concat(".xls")));
    }

    if (i == 2) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xlsx files", "xlsx"));
        jfc.setSelectedFile(new File(fname.concat(".xlsx")));
    }

    int rv = jfc.showSaveDialog(parent);
    if (rv == JFileChooser.APPROVE_OPTION) {
        File f = jfc.getSelectedFile();

        try {

            if (i == 0) {
                BufferedWriter out = new BufferedWriter(new FileWriter(f));
                for (String columnName : colNames) {
                    out.write(columnName);
                    out.write("\t");
                }
                out.newLine();
                for (Object[] tableData1 : tableData) {
                    for (int k = 0; k < colNames.length; k++) {
                        if (tableData1[k] != null) {
                            out.write(tableData1[k].toString());
                        }
                        out.write("\t");
                    }
                    out.newLine();
                }
                out.close();
            }

            if (i > 0) {

                Workbook wb;

                if (i == 1) {
                    wb = new HSSFWorkbook();
                } else {
                    wb = new XSSFWorkbook();
                }

                Sheet sheet = wb.createSheet(fname);
                Row r;
                CellStyle style;
                sheet.createFreezePane(0, 1);

                for (int j = 0; j < tableData.length; j++) {
                    r = sheet.createRow(j + 1);
                    for (int k = 0; k < colNames.length; k++) {

                        if (tableData[j][k] != null) {
                            Cell c = r.createCell(k);

                            if (tableData[j][k] instanceof Integer) {
                                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                int v = ((Integer) tableData[j][k]);
                                c.setCellValue(v);
                            } else {
                                if (tableData[j][k] instanceof Double) {
                                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                    double v = ((Double) tableData[j][k]);
                                    c.setCellValue(v);
                                } else {

                                    c.setCellType(Cell.CELL_TYPE_STRING);
                                    c.setCellValue(tableData[j][k].toString());

                                }
                            }
                        }
                    }
                }

                r = sheet.createRow(0);
                style = wb.createCellStyle();
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                style.setFont(font);

                for (int k = 0; k < colNames.length; k++) {
                    Cell c = r.createCell(k);
                    c.setCellType(Cell.CELL_TYPE_STRING);
                    c.setCellStyle(style);
                    c.setCellValue(colNames[k]);
                }

                FileOutputStream fos = new FileOutputStream(f);
                wb.write(fos);
                fos.close();
            }

        } catch (Exception ex) {
            Logger.getLogger(dataTable.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(parent, "File error: ".concat(ex.getLocalizedMessage()), "File Error",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        int n = JOptionPane.showOptionDialog(parent, "File saved. Open in default application?", "Message",
                JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, null, null);
        if (n == JOptionPane.YES_OPTION) {
            try {
                Desktop d = Desktop.getDesktop();
                d.open(f);
            } catch (IOException e) {
                System.out.println(e.getLocalizedMessage());
            }
        }
    }
}

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*/*from   ww w .  j  ava2  s . c  om*/
    Use XSSF for xlsx format and for xls use HSSF
     */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
     */
    Sheet songsSheet = workbook.createSheet("Albums");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
     */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
     */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style2.setAlignment(style2.ALIGN_CENTER);

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
     */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
     */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Bandaru, Sreekanth");

    /*
    Applying underline to Name
     */
    my_font.setUnderline(XSSFFont.U_SINGLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    combined.setAlignment(combined.ALIGN_CENTER);

    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
     */
    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3");

    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIME.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Fenre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
     */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
     */
    int mainCellIndex = 0;
    CellStyle style4 = workbook.createCellStyle();
    XSSFFont my_font2 = (XSSFFont) workbook.createFont();
    my_font2.setBold(true);
    style4.setFont(my_font2);
    rowMain.setRowStyle(style4);
    rowMain.createCell(mainCellIndex++).setCellValue("SNO");
    rowMain.createCell(mainCellIndex++).setCellValue("Genre");
    rowMain.createCell(mainCellIndex++).setCellValue("Rating");
    rowMain.createCell(mainCellIndex++).setCellValue("Movie Name");
    rowMain.createCell(mainCellIndex++).setCellValue("Director");
    rowMain.createCell(mainCellIndex++).setCellValue("Release Date");

    /*
    populating cell values
     */
    int rowIndex = 4;
    int sno = 1;
    for (Song song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
             */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
             */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
             */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
             */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
             */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
             */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();

                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                 */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
     */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:biz.webgate.dominoext.poi.component.kernel.simpleviewexport.WorkbooklExportProcessor.java

License:Apache License

public void process2HTTP(ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr,
        DateTimeHelper dth) {//from   w w w. j  a  v a 2  s  . c om
    try {
        String strFileName = uis.getDownloadFileName();

        Workbook wbCurrent = null;
        if (strFileName.toLowerCase().endsWith(".xlsx")) {
            wbCurrent = new XSSFWorkbook();
        } else {
            wbCurrent = new HSSFWorkbook();
        }
        HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
        CreationHelper cr = wbCurrent.getCreationHelper();
        CellStyle csDate = wbCurrent.createCellStyle();
        csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));

        CellStyle csDateTime = wbCurrent.createCellStyle();
        csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));

        CellStyle csTime = wbCurrent.createCellStyle();
        csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));

        hsCS.put("DATE", csDate);
        hsCS.put("TIME", csTime);
        hsCS.put("DATETIME", csDateTime);

        Sheet sh = wbCurrent.createSheet("SVE Export");
        int nRowCount = 0;

        // BUILDING HEADER
        if (uis.isIncludeHeader()) {
            Row rw = sh.createRow(nRowCount);
            int nCol = 0;
            for (ExportColumn expColumn : expModel.getColumns()) {
                rw.createCell(nCol).setCellValue(expColumn.getColumnName());
                nCol++;
            }
            nRowCount++;
        }
        // Processing Values
        for (ExportDataRow expRow : expModel.getRows()) {
            Row rw = sh.createRow(nRowCount);
            int nCol = 0;
            for (ExportColumn expColumn : expModel.getColumns()) {
                Cell clCurrent = rw.createCell(nCol);
                setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
                nCol++;
            }
            nRowCount++;
        }
        for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
            sh.autoSizeColumn(nCol);
        }
        if (strFileName.toLowerCase().endsWith(".xlsx")) {
            hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        } else if (strFileName.toLowerCase().endsWith("xls")) {
            hsr.setContentType("application/vnd.ms-excel");

        } else {
            hsr.setContentType("application/octet-stream");
        }
        hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
        OutputStream os = hsr.getOutputStream();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wbCurrent.write(bos);
        bos.writeTo(os);
        os.close();
    } catch (Exception e) {
        ErrorPageBuilder.getInstance().processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
    }
}

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

private void processSpreadSheet(Spreadsheet spCurrent, Workbook wbCurrent, FacesContext context)
        throws POIException {
    // Checking for Replacement Values
    Logger logCurrent = LoggerFactory.getLogger(this.getClass().getCanonicalName());

    logCurrent.finer("Proccess Spread Sheet");
    String strName = spCurrent.getName();
    Sheet shProcess = wbCurrent.getSheet(strName);
    if (shProcess == null && !spCurrent.isCreate()) {
        return;// w ww. ja  v a  2s  .c  om
    }
    if (shProcess == null) {
        shProcess = wbCurrent.createSheet(strName);
    }

    logCurrent.finer("Proccess Cell Values");
    if (spCurrent.getCellValues() != null) {
        for (ICellValue iCV : spCurrent.getCellValues()) {
            if (iCV instanceof CellBookmark) {
                CellBookmark cb = (CellBookmark) iCV;
                if (StringUtil.isNotEmpty(cb.getName())) {
                    findAndReplaceAll(shProcess, "<<" + cb.getName() + ">>", cb.getValue());
                }
            }
            if (iCV instanceof CellValue) {
                CellValue cv = (CellValue) iCV;
                setCellValue(shProcess, cv.getRowNumber(), cv.getColumnNumber(), cv.getValue(),
                        cv.isCellFormula(), cv.getPoiCellStyle());
            }
        }
    }

    logCurrent.finer("Proccess ExportDefinition");
    if (spCurrent.getExportDefinitions() != null) {
        for (IListDataExporter lstExport : spCurrent.getExportDefinitions()) {
            if (lstExport instanceof Data2ColumnExporter) {
                if (lstExport.getDataSource() != null) {
                    EmbeddedDataSourceExportProcessor.getInstance().processExportCol(
                            (Data2ColumnExporter) lstExport, shProcess, context,
                            ((Data2ColumnExporter) lstExport).getVar(),
                            ((Data2ColumnExporter) lstExport).getIndex());
                } else {
                    XPagesDataSourceExportProcessor.getInstances().processExportCol(
                            (Data2ColumnExporter) lstExport, shProcess, context,
                            ((Data2ColumnExporter) lstExport).getVar(),
                            ((Data2ColumnExporter) lstExport).getIndex());
                }
            } else if (lstExport instanceof Data2RowExporter) {
                if (lstExport.getDataSource() != null) {
                    EmbeddedDataSourceExportProcessor.getInstance().processExportRow(
                            (Data2RowExporter) lstExport, shProcess, context,
                            ((Data2RowExporter) lstExport).getVar(), ((Data2RowExporter) lstExport).getIndex());
                } else {
                    XPagesDataSourceExportProcessor.getInstances().processExportRow(
                            (Data2RowExporter) lstExport, shProcess, context,
                            ((Data2RowExporter) lstExport).getVar(), ((Data2RowExporter) lstExport).getIndex());
                }

            }
        }
    }
}

From source file:bloodbank.Simulation.java

/**
 *
 * @param args/*from w w  w .  j a v a2  s .com*/
 * @throws IOException
 */
public static void main(String[] args) throws IOException {
    //from 8am to 20pm 
    Random rng = new Random();

    Distribution plasmaInter = new DiscreteUniformDistribution(6, 6, rng);
    Distribution[] wholeInter = new Distribution[24];
    Distribution[] procedures = new Distribution[10];
    constructDistribution(wholeInter, procedures, rng);

    Simulation sim = new Simulation(plasmaInter, wholeInter, procedures);
    // Create the sheet
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    int runs = 1;
    Row row = sheet.createRow((short) 0);
    Cell cell = row.createCell(0);
    cell.setCellValue(createHelper.createRichTextString("Total running time"));
    cell = row.createCell(1);
    cell.setCellValue(createHelper.createRichTextString("P pre-donation sojourn time"));
    cell = row.createCell(2);
    cell.setCellValue(createHelper.createRichTextString("W pre-donation sojourn time"));
    cell = row.createCell(3);
    cell.setCellValue(createHelper.createRichTextString("P total sojourn time"));
    cell = row.createCell(4);
    cell.setCellValue(createHelper.createRichTextString("W total sojourn time"));
    cell = row.createCell(5);
    cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at registration"));
    cell = row.createCell(6);
    cell.setCellValue(createHelper.createRichTextString("Qmean # P&W at questionnaire"));
    cell = row.createCell(7);
    cell.setCellValue(createHelper.createRichTextString("Qmean # P at (pre-)interview"));
    cell = row.createCell(8);
    cell.setCellValue(createHelper.createRichTextString("Qmean # W at (pre-)interview"));
    cell = row.createCell(9);
    cell.setCellValue(createHelper.createRichTextString("# of available doctors"));

    cell = row.createCell(10);
    cell.setCellValue(createHelper.createRichTextString("Qmean # P at pre-donation room"));
    cell = row.createCell(11);
    cell.setCellValue(createHelper.createRichTextString("Qmean # W at pre-donation room"));
    cell = row.createCell(12);
    cell.setCellValue(createHelper.createRichTextString("Qmean # P at donation room"));
    cell = row.createCell(13);
    cell.setCellValue(createHelper.createRichTextString("Qmean # W at donation room"));

    for (int i = 0; i < 16; i++) {
        /*cell=row.createCell(13+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Pl " + "hr " + (8 + i)));//donor room Plasma
        cell=row.createCell(13+1*16+i);cell.setCellValue(createHelper.createRichTextString("BedOcc.Wh " + "hr" + (8 + i)));//donor room Whole
        cell=row.createCell(13+2*16+i);cell.setCellValue(createHelper.createRichTextString("Queue0" + "hr" + (8 + i)));//Queue lenght reception
        cell=row.createCell(13+3*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocPl" + "hr" + (8 + i)));//Queue length doctor plasma
        cell=row.createCell(13+4*16+i);cell.setCellValue(createHelper.createRichTextString("QueueDocWh" + "hr" + (8 + i)));//Queue length doctor whole
        cell=row.createCell(13+5*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl PreDon" + "hr" + (8 + i)));
        cell=row.createCell(13+6*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Pl TotDon" + "hr" + (8 + i)));
        cell=row.createCell(13+7*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh PreDon" + "hr" + (8 + i)));
        cell=row.createCell(13+8*16+i);cell.setCellValue(createHelper.createRichTextString("SJT Wh TotDon" + "hr" + (8 + i)));
        cell=row.createCell(13+9*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonPl" + "hr" + (8 + i)));
        cell=row.createCell(13+10*16+i);cell.setCellValue(createHelper.createRichTextString("QueuePreDonWh" + "hr" + (8 + i)));   
        cell=row.createCell(13+11*16+i);cell.setCellValue(createHelper.createRichTextString("QuestionNaire" + "hr" + (8 + i))); */
        cell = row.createCell(13 + 1 * 16 + i);
        cell.setCellValue(createHelper.createRichTextString("AvailableNurse" + "hr" + (8 + i)));
        cell = row.createCell(13 + 2 * 16 + i);
        cell.setCellValue(createHelper.createRichTextString("P Wait for connect" + "hr" + (8 + i)));
        cell = row.createCell(13 + 3 * 16 + i);
        cell.setCellValue(createHelper.createRichTextString("W Wait for connect" + "hr" + (8 + i)));
        cell = row.createCell(13 + 4 * 16 + i);
        cell.setCellValue(createHelper.createRichTextString("Wait for disconnect" + "hr" + (8 + i)));
    }

    //other measures can be added, see all measures in line 364-379, as well as variance  
    while (runs <= 10000) {//runs=10000 costs 9 seconds
        sim.simulate(sheet, runs);
        runs++;
    }
    FileOutputStream fileOut = new FileOutputStream("correct.xls");//name of the excel file
    wb.write(fileOut);
    fileOut.close();
}

From source file:br.com.algoritmo.compilacao.CompilaXlsx.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;
    Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>();
    data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921",
            "SOFTWARE", null, null, null });
    data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676",
            "SOFTWARE", null, null, null });
    data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970",
            "CIVIL", null, null, null });
    data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA",
            null, null, null });/*from   w  w  w  .  jav  a 2  s.c o m*/
    data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null,
            null, null });
    data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE",
            null, null, null });
    data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null,
            null, null });
    data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE",
            null, null, null });
    data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null,
            null, null });
    data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null,
            null, null });
    data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051",
            "MEDICINA", null, null, null });
    data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA",
            null, null, null });
    data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570",
            "CINCIA DA COMPUTAO", null, null, null });
    data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042",
            "SOFTWARE", null, null, null });
    data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286",
            "ENERGIA", null, null, null });
    data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null,
            null, null });

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet aba1 = wb.createSheet("Percentual de similaridade 1");
    PrintSetup printSetup = aba1.getPrintSetup();
    printSetup.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    Sheet aba2 = wb.createSheet("Percentual de similaridade 2");
    PrintSetup printSetup2 = aba2.getPrintSetup();
    printSetup2.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    //title row
    Row titleRow = aba1.createRow(0);
    titleRow.setHeightInPoints(15);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(
            "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos");
    titleCell.setCellStyle(styles.get("title"));
    aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

    //header row
    Row headerRow = aba1.createRow(1);
    headerRow.setHeightInPoints(15);
    Cell headerCell;
    for (int i = 1; i <= titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i - 1]);
        headerCell.setCellStyle(styles.get("header"));
    }

    Row headerBase = aba1.createRow(2);
    headerBase.setHeightInPoints(15);
    Cell headerCellBase;
    for (int i = 1; i <= base.length; i++) {
        headerCellBase = headerBase.createCell(i);
        headerCellBase.setCellValue(base[i - 1]);
        headerCellBase.setCellStyle(styles.get("header1"));
    }

    Row headerDestino = aba1.createRow(4);
    headerDestino.setHeightInPoints(15);
    Cell headerCellDestino;
    for (int i = 1; i <= destino.length; i++) {
        headerCellDestino = headerDestino.createCell(i);
        headerCellDestino.setCellValue(destino[i - 1]);
        headerCellDestino.setCellStyle(styles.get("header1"));
    }

    /*int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
            
    rownum = 3;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles1.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
    */
    //set sample data
    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;
    for (Integer key : keyset) {
        Row row = aba1.createRow(3 + rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
        if (row.getRowNum() == 3) {
            rownum++;
        }
    }
    //finally set column widths, the width is measured in units of 1/256th of a character width
    aba1.setColumnWidth(0, 2 * 256); //2 characters wide
    aba1.setColumnWidth(1, 26 * 256); //26 characters wide
    aba1.setColumnWidth(2, 20 * 256); //20 characters wide
    aba1.setColumnWidth(3, 18 * 256); //18 characters wide
    aba1.setColumnWidth(4, 20 * 256); //20 characters wide
    for (int i = 5; i < 9; i++) {
        aba1.setColumnWidth(i, 15 * 256); //6 characters wide
    }

    // Write the output to a file
    String file = "Sada/Percentual de similaridade.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:br.ufal.cideei.util.count.SummaryBuilder.java

License:Open Source License

public static void buildSummary(String splShortName)
        throws InvalidFormatException, FileNotFoundException, IOException {

    // final String userHomeFolder = System.getProperty("user.home").substring(3);
    String userHomeFolder = "C:\\tst";
    final String output = userHomeFolder + File.separator + "summ.xls";
    File outputFile = new File(output);
    Workbook outputWorkbook;
    if (!outputFile.exists()) {
        outputFile.createNewFile();//from   w w  w  .  j  ava2  s  . c om
        outputWorkbook = new HSSFWorkbook();
    } else {
        FileInputStream inputFileStream = new FileInputStream(outputFile);
        outputWorkbook = WorkbookFactory.create(inputFileStream);
    }

    {
        List<String> referencesForRDA3 = new ArrayList<String>();
        List<String> referencesForUVA3 = new ArrayList<String>();
        List<String> referencesForRDA2 = new ArrayList<String>();
        List<String> referencesForUVA2 = new ArrayList<String>();
        String fileName = "fs-" + splShortName + ".xls";
        String filePath = userHomeFolder + File.separator;
        String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls";
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName)));
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row headerRow = sheet.getRow(0);
            for (Cell cell : headerRow) {
                String stringCellValue = cell.getStringCellValue();
                if (stringCellValue.equals("rd")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("rd (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA3.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA3.add(cellRefForAnotherSheet);
                }
            }
        }
        if (outputWorkbook.getSheet(splShortName) != null) {
            outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName));
        }
        Sheet outputSheet = outputWorkbook.createSheet(splShortName);
        Row RDA2Row = outputSheet.createRow(0);
        RDA2Row.createCell(0).setCellValue("RD A2");
        for (int i = 0; i < referencesForRDA2.size(); i++) {
            Cell createdCell = RDA2Row.createCell(i + 1);
            System.out.println(referencesForRDA2.get(i));
            createdCell.setCellType(Cell.CELL_TYPE_FORMULA);
            createdCell.setCellValue(referencesForRDA2.get(i));
        }
        Row UVA2Row = outputSheet.createRow(1);
        UVA2Row.createCell(0).setCellValue("UV A2");
        for (int i = 0; i < referencesForUVA2.size(); i++) {
            Cell createdCell = UVA2Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA2.get(i));
        }
        Row RDA3Row = outputSheet.createRow(2);
        RDA3Row.createCell(0).setCellValue("RD A3");
        for (int i = 0; i < referencesForRDA3.size(); i++) {
            Cell createdCell = RDA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForRDA3.get(i));
        }
        Row UVA3Row = outputSheet.createRow(3);
        UVA3Row.createCell(0).setCellValue("UV A3");
        for (int i = 0; i < referencesForUVA3.size(); i++) {
            Cell createdCell = UVA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA3.get(i));
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
    outputWorkbook.write(fileOutputStream);
    fileOutputStream.close();
}