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

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

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

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:com.wantdo.stat.excel.poi_src.formula.SettingExternalFunction.java

License:Apache License

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

    Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook()

    // register the add-in
    wb.addToolPack(new BloombergAddIn());

    Sheet sheet = wb.createSheet();//from ww  w  .j a v a2  s  . c  o m
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
    row.createCell(1).setCellFormula(
            "BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") ");
    row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") ");

    FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx");
    wb.write(out);
    out.close();

}

From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java

License:Apache License

public static void main(String[] args) {
    if (args.length != 4)
        usage("need four command arguments");

    String type = args[0];//from  w  w  w  .j ava  2  s . c o m
    long timeStarted = System.currentTimeMillis();
    Workbook workBook = createWorkbook(type);
    boolean isHType = workBook instanceof HSSFWorkbook;

    int rows = parseInt(args[1], "Failed to parse rows value as integer");
    int cols = parseInt(args[2], "Failed to parse cols value as integer");
    boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0;

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

    Sheet sheet = workBook.createSheet("Main Sheet");

    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));

    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;
        }

        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            Cell cell = row.createCell(colIndex);
            String address = new CellReference(cell).formatAsString();
            switch (colIndex) {
            case 0:
                // column A: default number format
                cell.setCellValue(value++);
                break;
            case 1:
                // column B: #,##0
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("#,##0.00"));
                break;
            case 2:
                // column C: $#,##0.00
                cell.setCellValue(value++);
                cell.setCellStyle(styles.get("$#,##0.00"));
                break;
            case 3:
                // column D: red bold text on yellow background
                cell.setCellValue(address);
                cell.setCellStyle(styles.get("red-bold"));
                break;
            case 4:
                // column E: boolean
                // TODO booleans are shown as 1/0 instead of TRUE/FALSE
                cell.setCellValue(rowIndex % 2 == 0);
                break;
            case 5:
                // column F:  date / time
                cell.setCellValue(calendar);
                cell.setCellStyle(styles.get("m/d/yyyy"));
                calendar.roll(Calendar.DAY_OF_YEAR, -1);
                break;
            case 6:
                // column F: formula
                // TODO formulas are not yet supported  in SXSSF
                //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")");
                //break;
            default:
                cell.setCellValue(value++);
                break;
            }
        }
        rowIndexInSheet++;
    }
    if (saveFile) {
        String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]);
        try {
            FileOutputStream out = new FileOutputStream(fileName);
            workBook.write(out);
            out.close();
        } catch (IOException ioe) {
            System.err
                    .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage());
        }
    }
    long timeFinished = System.currentTimeMillis();
    System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds");
}

From source file:com.webbfontaine.valuewebb.report.fcvrprocessing.FcvrTimeReportEngine.java

License:Open Source License

private static void exportWorkBook(Workbook workbook) throws IOException {
    try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
        workbook.write(outputStream);
        Utils.sendBytes(outputStream.toByteArray(), "fcvr_processing_time_report.xls",
                "application/vnd.ms-excel");
    }/*from  w  ww  . j  a  va2  s  .  c o m*/
}

From source file:com.wuliu.biz.util.export.strategy.CarIndexExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {

    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }/*from ww w .  j a  v a 2 s . c om*/

    File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
    InputStream inp = new FileInputStream(template);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    fillSheet(sheet, mergedOrders);

    File file = new File(folderPath, getName(mergedOrders));
    try {
        FileOutputStream outputStream = new FileOutputStream(file);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
        wb.close();
        System.out.println("success");
    } catch (Exception e) {
        System.out.println("It cause Error on WRITTING excel workbook: ");
        e.printStackTrace();
    }
    return file.getAbsolutePath();
}

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {
    File folder = createFolder(folderPath);
    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }//from w w  w .j a v a  2 s . c  o m

    for (List<WuliuMergedOrderModel> item : mergedOrderLists) {
        File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
        InputStream inp = new FileInputStream(template);
        Workbook wb = WorkbookFactory.create(inp);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        fillSheet(sheet, item);
        evaluate(sheet, evaluator);

        File file = new File(folder, getName(item));
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }
    }
    return folder.getAbsolutePath();
}

From source file:com.ykun.commons.utils.excel.ExcelUtils.java

License:Apache License

/**
 * xlsheaders//from w ww.j a  v  a 2 s. c  o m
 *
 * @param list    the list
 * @param headers the headers
 * @param out     the out
 */
public static <T> void export(List<T> list, List<String> headers, OutputStream out) {
    // ?
    if (list == null || list.size() == 0) {
        return;
    }

    try {
        Workbook workbook = new XSSFWorkbook(); // XSSFWorkbook
        Sheet sheet = workbook.createSheet(); // ?Sheet

        // ?
        int rowNo = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        if (headers != null && headers.size() > 0) {
            Row row = sheet.createRow(rowNo++);
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(headers.get(i));
            }
        }

        // ?
        CellStyle normalStyle = createNormalStyle(workbook);
        for (T t : list) {
            Row row = sheet.createRow(rowNo++);
            Field[] fields = t.getClass().getDeclaredFields();
            int column = 0;
            for (int i = 0; i < fields.length; i++) {
                Object value;
                Field field = fields[i];
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField != null && !excelField.ignore()) {
                    String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable?
                    Method method = t.getClass().getMethod(methodName, new Class[] {});
                    value = method.invoke(t, new Object[] {});
                } else if (excelField != null && excelField.ignore()) {
                    continue;
                } else {
                    String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable?
                    Method method = t.getClass().getMethod(methodName, new Class[] {});
                    value = method.invoke(t, new Object[] {});
                }
                row.setRowStyle(normalStyle);
                addCell(row, column++, value, excelField);
            }
        }
        workbook.write(out);
    } catch (Exception e) {
        logger.error("Export error:", e);
        throw new RuntimeException(e);
    }
}

From source file:com.zhangyue.zeus.service.impl.ExportDataServiceImpl.java

License:Open Source License

@Override
public void downloadData(OutputStream os, String hdfsResultLocation, boolean isDownload) {
    List<String> resultList = readResultData(hdfsResultLocation, isDownload);
    if (resultList.size() == 0) {
        return;/*from  w w  w.  j a  v a 2  s .co  m*/
    }
    Workbook book = new HSSFWorkbook();
    Sheet sheet = book.createSheet(Constants.SHEET_NAME);
    sheet.setColumnWidth(0, 10000);
    for (int i = 0; i < resultList.size(); i++) {
        Row row = sheet.createRow(i);
        if (null == resultList.get(i)) {
            continue;
        }
        String[] items = resultList.get(i).split(Constants.HIVE_RESULT_TAG);
        for (int j = 0; j < items.length; j++) {
            row.createCell(j).setCellValue(items[j]);
        }
    }
    try {
        book.write(os);
        os.flush();
        os.close();
    } catch (IOException e) {
        LOG.error("Export excel error!!", e);
    }
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

public static <T> void write(String sheetName, Class<T> clazz, List<T> list, OutputStream out) {
    if (out == null) {
        return;//  w  w  w .  j a v a 2  s  .co  m
    }
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);
    // 15
    sheet.setDefaultColumnWidth(15);

    Drawing drawing = sheet.createDrawingPatriarch();

    List<String> header = buildHeader(workbook, sheet, clazz);

    buildBody(workbook, sheet, drawing, header, list);

    try {
        workbook.write(out);
    } catch (IOException ex) {
        // TODO Auto-generated catch block
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.zxy.commons.poi.excel.ExcelUtils.java

License:Apache License

/**
 * export//from   w  ww. j  a v a2 s.co m
 * 
 * @param sheetName sheet name
 * @param table table
 * @param outputPath output path
 * @throws IOException IOException
 */
public static void export(String sheetName, Table<Integer, String, String> table, String outputPath)
        throws IOException {
    Workbook wb = ExcelUtils.exportExcel(sheetName, table);
    OutputStream out = null;
    try {
        out = new FileOutputStream(outputPath);
        wb.write(out);
    } finally {
        if (out != null) {
            out.close();
        }
    }
}

From source file:Compras.altaCompras.java

   private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed
    // TODO add your handling code here:
    javax.swing.JFileChooser jF1= new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;/*w w  w .j  a v a2 s.c  o  m*/
    if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION)
    {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        File archivoXLS = new File(ruta+".xls");
        try
        {
            if(archivoXLS.exists())
            archivoXLS.delete();
            archivoXLS.createNewFile();
            Workbook libro = new HSSFWorkbook();
            FileOutputStream archivo = new FileOutputStream(archivoXLS);
            Sheet hoja = libro.createSheet("Muestras");
                
            org.apache.poi.ss.usermodel.Font font10 = libro.createFont();
            font10.setFontHeightInPoints((short)10);
            font10.setFontName("Arial");
            font10.setItalic(false);
            font10.setBold(false);
            font10.setColor(new HSSFColor.YELLOW().getIndex());
                
                
            CellStyle titulo = libro.createCellStyle();
            CellStyle contenido = libro.createCellStyle();
            CellStyle firma = libro.createCellStyle();
            CellStyle costado = libro.createCellStyle();
            CellStyle derecha = libro.createCellStyle();
            CellStyle derecha_borde = libro.createCellStyle();
                
            titulo.setFont(font10);
            titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex());
            titulo.setAlignment(CellStyle.ALIGN_CENTER);
                
            contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderTop(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderRight(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT);
                
            derecha.setAlignment(CellStyle.ALIGN_RIGHT);
                
            firma.setBorderTop(HSSFCellStyle.BORDER_THIN);
            firma.setAlignment(CellStyle.ALIGN_CENTER);
                
            //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            hoja.setColumnWidth(0, 3000);
            hoja.setColumnWidth(2, 3000);
            hoja.setColumnWidth(3, 8000);
            hoja.setColumnWidth(4, 5000);
            try
            {
                InputStream is = new FileInputStream("imagenes/grande300115.jpg");
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                is.close();
                CreationHelper helper = libro.getCreationHelper();
                Drawing drawing = hoja.createDrawingPatriarch();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(0);
                anchor.setRow1(0);
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                pict.resize();
            }catch(Exception e){e.printStackTrace();}
            Row r7 = hoja.createRow(7);
            r7.createCell(0).setCellValue("ORDEN:");
            r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden()));
            r7.createCell(2).setCellValue("Hrs. Entrega:");
            r7.createCell(3).setCellValue("");
            hoja.addMergedRegion(new CellRangeAddress(7,7,4,7));
            r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS");
            r7.getCell(4).setCellStyle(derecha);
                
            Date fecha = new Date();
            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS
            String valor=dateFormat.format(fecha);
            Row r8 = hoja.createRow(8);
            r8.createCell(0).setCellValue("Supervisor:");
            hoja.addMergedRegion(new CellRangeAddress(8,8,1,3));
            r8.createCell(1).setCellValue("");
            r8.createCell(4).setCellValue("F. Elaboracin:");
            r8.createCell(5).setCellValue(valor);
                
            Row r9 = hoja.createRow(9);
            r9.createCell(0).setCellValue("Comprador:");
            hoja.addMergedRegion(new CellRangeAddress(9,9,1,3));
            r9.createCell(1).setCellValue("");
            r9.createCell(4).setCellValue("F. Entrega:");
            r9.createCell(5).setCellValue("");
                
            Row r10 = hoja.createRow(10);
            r10.createCell(0).setCellValue("Cantidad");
            r10.getCell(0).setCellStyle(titulo);
            hoja.addMergedRegion(new CellRangeAddress(10,10,1,7));
            r10.createCell(1).setCellValue("Descripcin");
            r10.getCell(1).setCellStyle(titulo);
                
            int ren=11;
            for(int r=0;r<(t_datos.getRowCount());r++)
            {
                if((boolean)t_titulos.getValueAt(r, 3)==true)
                {
                    Row fila = hoja.createRow(ren);
                    Cell celda = fila.createCell(0);
                    celda.setCellStyle(derecha_borde);
                    Cell celda1 = fila.createCell(1);
                    celda1.setCellStyle(contenido);
                    fila.createCell(2).setCellStyle(contenido);
                    fila.createCell(3).setCellStyle(contenido);
                    fila.createCell(4).setCellStyle(contenido);
                    fila.createCell(5).setCellStyle(contenido);
                    fila.createCell(6).setCellStyle(contenido);
                    fila.createCell(7).setCellStyle(contenido);
                    //Cell celda8 = fila.createCell(8);
                    //celda8.setCellStyle(costado);
                    try{
                        celda.setCellValue(t_datos.getValueAt(r, 14).toString());
                        hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7));
                        celda1.setCellValue(t_titulos.getValueAt(r, 2).toString());
                        //celda8.setCellValue("");
                    }catch(Exception e){
                        celda.setCellValue("");
                    }
                    ren++;
                }
            }
                
            Row rx = hoja.createRow(ren+5);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2));
            rx.createCell(0).setCellValue("Recibe Muestras");
            rx.getCell(0).setCellStyle(firma);
            rx.createCell(1).setCellStyle(firma);
            rx.createCell(2).setCellStyle(firma);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7));
            rx.createCell(5).setCellValue("Entrega Muestras");
            rx.getCell(5).setCellStyle(firma);
            rx.createCell(6).setCellStyle(firma);
            rx.createCell(7).setCellStyle(firma);
                
            libro.write(archivo);
            archivo.close();
            Desktop.getDesktop().open(archivoXLS);
        }catch(Exception e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto");
        }
    }
}