Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook close

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook close

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. close

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook close.

Prototype

@Override
    public void close() throws IOException

Source Link

Usage

From source file:CreatePivotTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();/*from w  w w.j a va  2  s  .c o  m*/

    //Create some data to build the pivot table on
    setCellData(sheet);

    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);

    FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

From source file:TaskFetcher.java

static HashMap<String, String> fetchActiveTasks(String username, String excel_path) {
    HashMap<String, String> activetasks = new HashMap<String, String>();
    FileInputStream file = null;//  w  w w . java2  s. co  m
    try {
        file = new FileInputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(file);
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    if (rowIterator.hasNext())
        rowIterator.next(); //skipping the first row of heading
    while (rowIterator.hasNext()) //loop over all entries in the excel sheet
    {
        Row row = rowIterator.next();
        Cell cell = row.getCell(0);
        if (username.equalsIgnoreCase(cell.getStringCellValue())) {
            String task;
            String status;
            String totTimeString = null;
            String latestTimeStamp;
            double totTime;
            task = row.getCell(1).getStringCellValue();
            status = row.getCell(2).getStringCellValue();
            latestTimeStamp = row.getCell(3).getStringCellValue();
            if (status.equalsIgnoreCase("Paused") || status.equalsIgnoreCase("Deferred")) {
                if (row.getCell(4) != null)
                    totTimeString = row.getCell(4).getStringCellValue();
            } else if (status.equalsIgnoreCase("In-Progress")) {
                if (row.getCell(4) != null)
                    totTimeString = row.getCell(4).getStringCellValue();
                if (totTimeString != null) {
                    totTime = Double.parseDouble(totTimeString);
                } else {
                    totTime = 0;
                }
                DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
                Date currentTimestamp = new Date();
                Date latestTimeStampObj = null;
                df.format(currentTimestamp);
                try {
                    latestTimeStampObj = df.parse(latestTimeStamp);
                } catch (ParseException ex) {
                    Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
                }
                long timeDifference = currentTimestamp.getTime() - latestTimeStampObj.getTime();
                long divisor = 60 * 60 * 1000;
                double diffHours = ((double) timeDifference / (double) divisor);
                totTime += diffHours;
                totTimeString = String.valueOf(totTime);
            }
            if (status != null && !status.equalsIgnoreCase("completed") && task != null
                    && totTimeString != null) {
                activetasks.put(task, status + ":" + totTimeString);
            }
        }
    }
    try {
        workbook.close();
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }
    return activetasks;
}

From source file:TaskFetcher.java

static HashMap<String, String> fetchAllTasks(String username, String excel_path) {
    HashMap<String, String> alltasks = new HashMap<String, String>();
    FileInputStream file = null;/*www  .j a  va2  s  .  c  o  m*/
    try {
        file = new FileInputStream(new File(excel_path));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(file);
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }

    //Get first sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(1);
    Iterator<Row> rowIterator = sheet.iterator();
    if (rowIterator.hasNext())
        rowIterator.next(); //skipping the first row of heading
    while (rowIterator.hasNext()) //loop over all entries in the excel sheet
    {
        Row row = rowIterator.next();
        Cell cell = row.getCell(0);
        if (username.equalsIgnoreCase(cell.getStringCellValue())) {
            String task;
            String status;
            String timestamp;
            String comments;
            task = row.getCell(1).getStringCellValue();
            status = row.getCell(2).getStringCellValue();
            timestamp = row.getCell(3).getStringCellValue();
            comments = row.getCell(4).getStringCellValue();
            alltasks.put(timestamp, task + ":" + status + ":" + comments);
        }
    }
    try {
        workbook.close();
    } catch (IOException ex) {
        Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex);
    }
    return alltasks;
}

From source file:at.metalab.m68k.dmnimport.DmnHelper.java

License:Apache License

public static DmnDecision parseXlsx(String resourceName) throws IOException {
    InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(resourceName);

    XSSFWorkbook workBook = null;
    try {//from  www  . j  a va2s.c o m
        workBook = new XSSFWorkbook(in);

        XSSFSheet sheet = workBook.getSheetAt(0);

        String tableName = DmnXslx.getTableName(sheet);
        String tableHitPolicy = DmnXslx.getTableHitpolicy(sheet);

        List<Column> columns = DmnHelper.buildColumns(sheet);
        List<RowData> rows = DmnHelper.buildRows(columns, sheet);

        return buildDmnDecision(tableName, tableHitPolicy, columns, rows);
    } finally {
        if (workBook != null) {
            workBook.close();
        }
    }
}

From source file:at.mukprojects.exclycore.dao.ExclyCoreWriterTest.java

License:Open Source License

/**
 * Tests the XLSXWriter functions.//  w w w .  j  a v a2  s .  c  o m
 */
@Test
public void testXLSXWriter() throws Exception {
    XSSFWorkbook workbook = writer.createWorkbook(outputFile, false);
    workbook.createSheet("Sheet New");
    writer.closeWorkbook();

    assertTrue(outputTestFile.exists());

    workbook = writer.createWorkbook(outputFile, true);
    workbook.createSheet("Sheet Add");
    writer.closeWorkbook();

    assertTrue(outputTestFile.exists());

    FileInputStream inputStream = new FileInputStream(outputTestFile);
    workbook = new XSSFWorkbook(inputStream);

    assertTrue(workbook.getNumberOfSheets() == 2);

    workbook.close();
}

From source file:automatedhgl.AutomatedHGL.java

public static void main(String[] args) {

    try {//from  ww  w. j ava  2  s. co m

        FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME));

        //create workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

        //get first desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //create workbook instance to output excel file
        XSSFWorkbook workbookHGL = new XSSFWorkbook();

        //create sheet in output excel file
        XSSFSheet sheetHGL = workbookHGL.createSheet("HGL");

        //iterate through each row one by one
        Iterator<Row> rowiterator = sheet.iterator();

        while (rowiterator.hasNext()) {
            Row row = rowiterator.next();

            //for each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                if (row.getRowNum() > 7 && count < 23) //to filter column headings
                {

                    //check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        count++;

                        //assign get value to correct variable
                        if (count == 1) {
                            InletStr = cell.getNumericCellValue();
                        } else if (count == 2) {
                            OutWSE = cell.getNumericCellValue();
                        }

                        System.out.print(cell.getNumericCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_STRING:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getStringCellValue();}*/

                        System.out.print(cell.getStringCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/

                        System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") ");
                        break;
                    }
                }

                else {
                    count = 0; //reset the count at the end of the row
                }

            }

            System.out.println("return");
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    //Output Excel file

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
    Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 },
            { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 },
            { "String", "Non-Primitive", "No fixed size" } };

    int rowNum = 0;
    System.out.println("Creating excel");

    for (Object[] datatype : datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field : datatype) {
            Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.print(InletStr + " ");
    System.out.print(OutWSE + " ");
    System.out.println("HGL Done");

}

From source file:br.com.jinsync.controller.ExportExcelBook.java

License:Open Source License

@Override
protected Object doInBackground() throws Exception {

    final String dir = System.getProperty("user.dir") + "\\layouts";

    File arqProp = new File(dir);
    if (!arqProp.exists()) {
        arqProp.mkdirs();/*w w  w . j  av  a  2s  .  co  m*/
    }

    String nameFile = this.nameFile;

    int lin = 0;
    int col = 0;
    int pos = 0;
    int posFim = 0;

    pos = nameFile.lastIndexOf("(");
    if (pos > 0) {
        posFim = nameFile.lastIndexOf(")");
        nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_layout" + ".xlsx";
    } else {
        pos = nameFile.lastIndexOf("\\");
        if (pos > 0) {
            posFim = nameFile.lastIndexOf(".");
            if (posFim > 0) {
                nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_layout" + ".xlsx";
            } else {
                nameFile = dir + nameFile.substring(pos) + "_layout" + ".xlsx";
            }
        }
    }

    FileOutputStream out;

    try {
        out = new FileOutputStream(nameFile);
        TableModel model = tableName.getModel();

        XSSFWorkbook wb = new XSSFWorkbook(); // Criando area de trabalho
        // para o excel
        XSSFSheet s = wb.createSheet(); // criando uma nova sheet

        XSSFFont f = wb.createFont();
        XSSFFont f2 = wb.createFont();

        XSSFCellStyle cs = wb.createCellStyle();
        XSSFCellStyle cs2 = wb.createCellStyle();
        XSSFCellStyle cs3 = wb.createCellStyle();
        XSSFCellStyle cs4 = wb.createCellStyle();

        f.setFontHeightInPoints((short) 8);
        f2.setFontHeightInPoints((short) 8);

        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setBoldweight(Font.BOLDWEIGHT_NORMAL);

        f.setFontName("Courier New");
        f2.setFontName("Courier New");

        XSSFRow r = null; // Criando uma referencia para Linha
        XSSFCell c = null; // Referencia para Celula

        cs.setFont(f);
        cs2.setFont(f2);
        cs3.setFont(f2);
        cs4.setFont(f);

        cs2.setAlignment(CellStyle.ALIGN_LEFT);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs4.setAlignment(CellStyle.ALIGN_RIGHT);

        r = s.createRow(lin);
        for (int i = 1; i < model.getColumnCount(); i++) {
            c = r.createCell(col);
            c.setCellStyle(cs);
            c.setCellValue(model.getColumnName(i));
            col = col + 1;
        }

        col = 0;
        for (int i = 0; i < model.getRowCount(); i++) {
            lin = lin + 1;
            r = s.createRow(lin);
            col = 0;
            for (int j = 1; j < model.getColumnCount(); j++) {
                String valor = model.getValueAt(i, j).toString();
                if (valor == null) {
                    valor = "";
                }

                c = r.createCell(col);
                c.setCellStyle(cs2);

                if (valor.matches("-?\\d+(\\.\\d+)?")) {
                    c.setCellValue(Double.parseDouble(valor));
                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                    c.setCellStyle(cs4);
                } else {
                    c.setCellValue(valor);
                }
                col = col + 1;
            }
        }

        for (int i = 0; i <= model.getColumnCount(); i++) {
            s.autoSizeColumn(i);
        }

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

        Desktop desktop = Desktop.getDesktop();
        desktop.open(new File(nameFile));

    } catch (FileNotFoundException e) {
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        e.printStackTrace();
    } catch (IOException e) {
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        e.printStackTrace();
    }

    return null;

}

From source file:br.com.jinsync.controller.ExportExcelFile.java

License:Open Source License

@Override
protected Object doInBackground() throws Exception {

    int valuePgsBar = 0;
    int qtdTotLin = 0;

    final String dir = System.getProperty("user.dir") + "\\file";

    File arqProp = new File(dir);
    if (!arqProp.exists()) {
        arqProp.mkdirs();/*from  w  w  w .  j  av a2s.c  o m*/
    }

    String nameFile = name;

    int lin = 0;
    int col = 0;
    int pos = 0;
    int posEnd = 0;

    pos = nameFile.lastIndexOf("(");
    if (pos > 0) {
        posEnd = nameFile.lastIndexOf(")");
        nameFile = dir + "\\" + nameFile.substring(pos + 1, posEnd) + "_file" + ".xlsx";
    } else {
        pos = nameFile.lastIndexOf("\\");
        if (pos > 0) {
            posEnd = nameFile.lastIndexOf(".");
            if (posEnd > 0) {
                nameFile = dir + "\\" + nameFile.substring(pos + 1, posEnd) + "_file" + ".xlsx";
            } else {
                nameFile = dir + nameFile.substring(pos) + "_file" + ".xlsx";
            }
        }
    }

    FileOutputStream out;

    try {
        out = new FileOutputStream(nameFile);
        TableModel model = tableName.getModel();

        XSSFWorkbook wb = new XSSFWorkbook(); // Criando area de trabalho
        // para o excel
        XSSFSheet s = wb.createSheet(); // criando uma nova sheet

        XSSFFont f = wb.createFont();
        XSSFFont f2 = wb.createFont();

        XSSFCellStyle cs = wb.createCellStyle();
        XSSFCellStyle cs2 = wb.createCellStyle();
        XSSFCellStyle cs3 = wb.createCellStyle();
        XSSFCellStyle cs4 = wb.createCellStyle();

        f.setFontHeightInPoints((short) 8);
        f2.setFontHeightInPoints((short) 8);

        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setBoldweight(Font.BOLDWEIGHT_NORMAL);

        f.setFontName("Courier New");
        f2.setFontName("Courier New");

        XSSFRow r = null; // Criando uma referencia para Linha
        XSSFCell c = null; // Referencia para Celula

        cs.setFont(f);
        cs2.setFont(f2);
        cs3.setFont(f2);
        cs4.setFont(f2);

        cs2.setAlignment(CellStyle.ALIGN_LEFT);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs4.setAlignment(CellStyle.ALIGN_RIGHT);

        r = s.createRow(lin);
        for (int i = 1; i < model.getColumnCount(); i++) {
            c = r.createCell(col);
            c.setCellStyle(cs);
            c.setCellValue(model.getColumnName(i));
            col = col + 1;
        }

        col = 0;
        qtdTotLin = model.getRowCount() - 1;

        if (qtdTotLin == 0) {
            qtdTotLin = 1;
        }

        for (int i = 0; i < model.getRowCount(); i++) {
            valuePgsBar = (i * 100) / qtdTotLin;
            lin = lin + 1;
            r = s.createRow(lin);
            col = 0;
            for (int j = 1; j < model.getColumnCount(); j++) {
                String valor = model.getValueAt(i, j).toString();
                if (valor == null) {
                    valor = "";
                }

                c = r.createCell(col);
                c.setCellStyle(cs2);

                valor = valor.trim();
                if (valor.matches("-?\\d+(\\.\\d+)?")) {
                    c.setCellValue(Double.parseDouble(valor));
                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                    c.setCellStyle(cs4);
                } else {
                    c.setCellValue(valor);
                }
                col = col + 1;
                setProgress(valuePgsBar);
            }
        }

        for (int i = 0; i <= model.getColumnCount(); i++) {
            s.autoSizeColumn(i);
        }

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

        Desktop desktop = Desktop.getDesktop();
        desktop.open(new File(nameFile));

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        e.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    return null;
}

From source file:br.com.jinsync.controller.ExportExcelString.java

License:Open Source License

@Override
protected Object doInBackground() throws Exception {

    final String dir = System.getProperty("user.dir") + "\\string";

    File arqProp = new File(dir);
    if (!arqProp.exists()) {
        arqProp.mkdirs();//  ww  w  . j av a  2  s .co  m
    }

    String nameFile = this.nameFile;

    int lin = 0;
    int col = 0;
    int pos = 0;
    int posFim = 0;
    int valDec = 0;

    pos = nameFile.lastIndexOf("(");
    if (pos > 0) {
        posFim = nameFile.lastIndexOf(")");
        nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_string" + ".xlsx";
    } else {
        pos = nameFile.lastIndexOf("\\");
        if (pos > 0) {
            posFim = nameFile.lastIndexOf(".");
            if (posFim > 0) {
                nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_string" + ".xlsx";
            } else {
                nameFile = dir + nameFile.substring(pos) + "_string" + ".xlsx";
            }
        }
    }

    FileOutputStream out;

    try {
        out = new FileOutputStream(nameFile);
        TableModel model = tableName.getModel();

        XSSFWorkbook wb = new XSSFWorkbook(); // Criando area de trabalho
        // para o excel
        XSSFSheet s = wb.createSheet(); // criando uma nova sheet

        XSSFFont f = wb.createFont();
        XSSFFont f2 = wb.createFont();

        XSSFCellStyle cs = wb.createCellStyle();
        XSSFCellStyle cs2 = wb.createCellStyle();
        XSSFCellStyle cs3 = wb.createCellStyle();
        XSSFCellStyle cs4 = wb.createCellStyle();

        f.setFontHeightInPoints((short) 8);
        f2.setFontHeightInPoints((short) 8);

        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setBoldweight(Font.BOLDWEIGHT_NORMAL);

        f.setFontName("Courier New");
        f2.setFontName("Courier New");

        XSSFRow r = null; // Criando uma referencia para Linha
        XSSFCell c = null; // Referencia para Celula

        cs.setFont(f);
        cs2.setFont(f2);
        cs3.setFont(f2);
        cs4.setFont(f);

        cs2.setAlignment(CellStyle.ALIGN_LEFT);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs4.setAlignment(CellStyle.ALIGN_RIGHT);

        r = s.createRow(lin);

        r = s.createRow(0); // Criando a primeira linha na LINHA zero, que
        // seria o nmero 1
        c = r.createCell(0); // Criando a celula na posicao ZERO, que seria
                             // A, com referencia na linha zero acima =
                             // A1
        c.setCellStyle(cs);
        c.setCellValue(Language.stringFieldName);

        c = r.createCell(1);
        c.setCellStyle(cs);
        c.setCellValue(Language.stringType);

        c = r.createCell(2);
        c.setCellStyle(cs);
        c.setCellValue(Language.stringLength);

        c = r.createCell(3);
        c.setCellStyle(cs);
        c.setCellValue(Language.stringDecimal);

        c = r.createCell(4);
        c.setCellStyle(cs);
        c.setCellValue(Language.stringTotal);

        c = r.createCell(5);
        c.setCellStyle(cs4);
        c.setCellValue(Language.stringContent);

        col = 0;
        lin = lin + 1;
        for (int i = 0; i < model.getColumnCount(); i++) {

            r = s.createRow(lin);
            c = r.createCell(col);
            c.setCellStyle(cs2);
            c.setCellValue(model.getColumnName(i));

            col = col + 1;

            c = r.createCell(col);
            c.setCellStyle(cs2);
            c.setCellValue(tipoConteudo.get(i));

            col = col + 1;
            c = r.createCell(col);
            c.setCellStyle(cs2);
            if (tamanhoConteudo.get(i).matches("-?\\d+(\\.\\d+)?")) {
                c.setCellValue(Double.parseDouble(tamanhoConteudo.get(i)));
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellStyle(cs3);
            } else {
                c.setCellValue(tamanhoConteudo.get(i));
            }

            col = col + 1;
            c = r.createCell(col);
            c.setCellStyle(cs2);
            if (decimalConteudo.get(i).matches("-?\\d+(\\.\\d+)?")) {
                c.setCellValue(Integer.parseInt(decimalConteudo.get(i)));
                valDec = Integer.parseInt(decimalConteudo.get(i));
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellStyle(cs3);
            } else {
                c.setCellValue(decimalConteudo.get(i));
                valDec = 0;
            }

            col = col + 1;
            c = r.createCell(col);
            c.setCellStyle(cs2);
            c.setCellValue(totalConteudo.get(i));

            col = col + 1;
            c = r.createCell(col);
            c.setCellStyle(cs2);

            String valor = model.getValueAt(0, i).toString().trim();
            if (valor == null) {
                valor = "";
            }

            if (valor.matches("-?\\d+(\\.\\d+)?")) {

                if (Double.parseDouble(valor) > 0 && valDec > 0) {
                    double val2 = Double.parseDouble(valor) / (Math.pow(10, valDec));
                    NumberFormat format = NumberFormat.getInstance();
                    format.setMinimumFractionDigits(2);
                    format.setMaximumFractionDigits(valDec);
                    c.setCellValue(format.format(val2).toString());

                } else {
                    c.setCellValue(Double.parseDouble(valor));
                }

                // c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellStyle(cs3);
            } else {
                c.setCellValue(valor);
            }

            lin = lin + 1;
            col = 0;
        }

        for (int i = 0; i <= model.getColumnCount(); i++) {
            s.autoSizeColumn(i);
        }

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

        Desktop desktop = Desktop.getDesktop();
        desktop.open(new File(nameFile));

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        //e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        JOptionPane.showMessageDialog(null, e.getLocalizedMessage());
        //e.printStackTrace();
    }

    return null;
}

From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java

License:Apache License

/**
 * mtodo cria bytes de documento Excel/*from w ww.  j  a v  a  2  s . c  om*/
 * @param titles
 * @param columnIndex
 * @param dataRows
 * @param locale
 * @return
 * @throws Exception
 */
public static byte[] createExcelBytes(String[] titles, String[] columnIndex,
        List<HashMap<String, Object>> dataRows, Locale locale) throws Exception {

    //Workbook wb = new HSSFWorkbook();
    XSSFWorkbook wb = new XSSFWorkbook();
    byte[] outBytes;

    try {
        HashMap<String, CellStyle> styles = createStyles(wb);
        Sheet sheet = wb.createSheet("Tab 1");

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);

        for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) {
            Cell cell = headerRow.createCell(indexColumn);
            cell.setCellValue(titles[indexColumn]);

            if ((titles.length - 1) < indexColumn) {
                cell.setCellValue("");
            } else
                cell.setCellValue(titles[indexColumn]);

            cell.setCellStyle(styles.get("header"));
        }

        //freeze the first row
        sheet.createFreezePane(0, 1);

        Row row;
        Cell cell;
        int rownum = 1;//devido constar titulo, comea do indice 1

        ValueCellUtil vcutil = new ValueCellUtil(locale);

        for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) {

            row = sheet.createRow(rownum);
            HashMap<String, Object> dataRow = dataRows.get(indexRow);

            if (dataRow == null)
                continue;

            List<String> keysAttribs = null;

            if (columnIndex.length == 0) {
                keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0]));
                Collections.reverse(keysAttribs);
            } else {
                keysAttribs = Arrays.asList(columnIndex);
            }

            int colCt = 0;

            for (String keyAttrib : keysAttribs) {

                cell = row.createCell(colCt);
                String styleName;
                cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString());

                //zebrando tabela
                if (indexRow % 2 == 0) {
                    // even row
                    styleName = "cell_normal_even";
                } else {
                    // odd row
                    styleName = "cell_normal_odd";
                }

                if (indexRow == 0) {
                    //setando auto ajuste
                    sheet.autoSizeColumn(colCt);
                }

                cell.setCellStyle(styles.get(styleName));
                colCt++;
            }
        }

        sheet.setZoom(75); //75% scale

        // Write the output to a file
        // write for return byte[]
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            wb.write(out);
            outBytes = out.toByteArray();
        } finally {
            out.close();
        }
    } finally {
        wb.close();
    }

    return outBytes;
}