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.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();//from   w ww. ja v  a 2s.  c  om

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }/*w  ww. java 2 s . c  o m*/
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}

From source file:com.blackducksoftware.tools.commonframework.standard.workbook.ExcelWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {
    FileOutputStream outputStream = null;
    try {//from w w  w .  j av a2  s  .  c  o m
        outputStream = new FileOutputStream(filePath);
        wb.write(outputStream);
        outputStream.close();
    } finally {
        try {
            outputStream.close();
        } catch (IOException e) { // ignore
        }
    }

}

From source file:com.bonsoft.test.Report.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    ArrayList<ReportLine> lines = new ArrayList<>();
    try {/*  ww w  .  jav  a  2s  . c  om*/
        Orgs org = (Orgs) jComboBox1.getSelectedItem();
        Stores store = (Stores) jComboBox2.getSelectedItem();
        Calendar period = Calendar.getInstance();
        try {
            period.setTime((new SimpleDateFormat("dd.MM.yyyy")).parse(jFormattedTextField1.getText()));
        } catch (ParseException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }

        Connection connection = null;
        Statement statement = null;
        ResultSet result = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://88.201.248.46:5432/personal", "vitaly",
                    "m127rqu4");
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        String sql = "select operations.descr, date_part('hour', mhr_period) as hr, sum(mhr_qty) as cnt from mhr, operations "
                + "where mhr.operation_id = operations.id and mhR_qty > 0 and mhr.org_id = " + org.getId()
                + " and store_id = " + store.getId() + " and " + "date_part('day', mhr_period) = "
                + period.get(Calendar.DAY_OF_MONTH) + " and date_part('month', mhr_period) = "
                + (period.get(Calendar.MONTH) + 1) + " and date_part('year', mhr_period) = "
                + period.get(Calendar.YEAR)
                + " and mhr.operation_id in (select id from operations) group by operations.descr, hr having count(mhr_qty) > 0 order by "
                + "operations.descr, hr";
        try {
            result = statement.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("?: " + org.getName() + ", id = " + org.getId());
        System.out.println(": " + store.getDescr() + ", id = " + store.getId());
        System.out.println(": " + period.get(Calendar.DAY_OF_MONTH));
        System.out.println("??: " + (period.get(Calendar.MONTH) + 1));
        System.out.println(": " + period.get(Calendar.YEAR));
        String oldDescr = "";
        ReportLine line = null;
        while (result.next()) {
            String descr = result.getString("descr");
            double hr = result.getDouble("hr");
            double cnt = result.getDouble("cnt");
            cnt = Math.ceil(cnt);
            if (oldDescr.equals(descr)) {
                line.add(hr, cnt);
            } else {
                oldDescr = descr;
                line = new ReportLine();
                line.setName(descr);
                line.add(hr, cnt);
                lines.add(line);
            }
        }
        result.close();
        statement.close();
        connection.close();
    } catch (SQLException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("");
    Row title = sheet.createRow(0);
    Cell cell = title.createCell(0);
    cell.setCellValue("?");
    Row row = null;
    int x = 0, y = 0;
    for (ReportLine line : lines) {
        row = sheet.createRow(++y);
        cell = row.createCell(0);
        cell.setCellValue(line.getName());
        for (int i = 0; i < line.getLen(); i++) {
            x = line.getHours().get(i) - ReportLine.getMinH() + 1;
            cell = title.createCell(x);
            cell.setCellValue(line.getHours().get(i) + ":00");
            cell = row.createCell(x);
            cell.setCellValue(line.getCounts().get(i));
        }
    }
    sheet.autoSizeColumn(0);
    try (FileOutputStream fileExcel = new FileOutputStream("Report.xls")) {
        workbook.write(fileExcel);
    } catch (IOException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.catexpress.util.FormatosPOI.java

public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores)
        throws FileNotFoundException, IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet;// www.  j a v a2 s. c o m
    int cont = 0;
    for (Proveedor proveedor : proveedores) {
        sheet = wb.createSheet(proveedor.getNombre());
        Row rTitulo = sheet.createRow(0);
        CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                6 //last column  (0-based)
        );
        sheet.addMergedRegion(craTitulo);
        Cell titulo = rTitulo.createCell(0);
        titulo.setCellValue("SOLICITUD DE MERCANC?A");
        titulo.setCellStyle(estiloHeader(wb, TITULO));
        rTitulo.setHeightInPoints(20);

        Row rUsuario = sheet.createRow(1);
        CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6);
        sheet.addMergedRegion(craUsuario);
        Cell usuario = rUsuario.createCell(0);
        usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno()
                + " " + solicitud.getUsuario().getApMaterno()).toUpperCase());
        usuario.setCellStyle(estiloHeader(wb, USUARIO));
        rUsuario.setHeightInPoints(25);

        Row rSucursal = sheet.createRow(2);
        CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6);
        sheet.addMergedRegion(craSucursal);
        Cell sucursal = rSucursal.createCell(0);
        sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre());
        sucursal.setCellStyle(estiloHeader(wb, SUCURSAL));
        RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb);
        RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb);
        RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb);
        RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb);
        rSucursal.setHeightInPoints(20);

        Row rBlank = sheet.createRow(3);
        Cell blank;
        for (int i = 0; i <= 6; i++) {
            blank = rBlank.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rFecha = sheet.createRow(4);
        Cell labelFecha = rFecha.createCell(0);
        labelFecha.setCellValue("FECHA:");
        labelFecha.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3);
        sheet.addMergedRegion(craFecha);
        Cell fecha = rFecha.createCell(1);
        fecha.setCellValue(solicitud.getFechaSolicitud());
        fecha.setCellStyle(estiloHeader(wb, FECHA));
        for (int i = 4; i <= 6; i++) {
            blank = rFecha.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rVigencia = sheet.createRow(5);
        Cell labelVigencia = rVigencia.createCell(0);
        labelVigencia.setCellValue("VIGENCIA:");
        labelVigencia.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3);
        sheet.addMergedRegion(craVigencia);
        Cell vigencia = rVigencia.createCell(1);
        Calendar clndr = Calendar.getInstance();
        clndr.setTime(solicitud.getFechaSolicitud());
        clndr.add(Calendar.DAY_OF_MONTH, 3);
        vigencia.setCellValue(clndr.getTime());
        vigencia.setCellStyle(estiloHeader(wb, FECHA));
        blank = rVigencia.createCell(4);
        blank.setCellStyle(estiloVacio(wb));
        Cell labelNoPedido = rVigencia.createCell(5);
        labelNoPedido.setCellValue("PEDIDO No:");
        labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO));
        Cell noPedido = rVigencia.createCell(6);
        noPedido.setCellValue(solicitud.getId());
        noPedido.setCellStyle(estiloCuadro(wb, AMARILLO));

        Row rHoja = sheet.createRow(6);
        for (int i = 0; i <= 4; i++) {
            blank = rHoja.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelHoja = rHoja.createCell(5);
        labelHoja.setCellValue("HOJA:");
        labelHoja.setCellStyle(estiloCuadro(wb, LABEL));
        Cell hoja = rHoja.createCell(6);
        hoja.setCellValue(++cont + "/" + proveedores.size());
        hoja.setCellStyle(estiloCuadro(wb, LABEL));

        Row rProveedor = sheet.createRow(7);
        CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2);
        sheet.addMergedRegion(craProveedor);
        Cell prov = rProveedor.createCell(0);
        prov.setCellValue(proveedor.getNombre());
        prov.setCellStyle(estiloProveedor(wb));
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rProveedor2 = sheet.createRow(8);
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor2.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rTotales = sheet.createRow(9);
        for (int i = 0; i <= 1; i++) {
            blank = rTotales.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelTotales = rTotales.createCell(2);
        labelTotales.setCellValue("TOTALES: ");
        labelTotales.setCellStyle(estiloTotales(wb));
        blank = rTotales.createCell(3);
        blank.setCellStyle(estiloTotales(wb));
        Cell totalSolicitado = rTotales.createCell(4);
        totalSolicitado.setCellStyle(estiloTotales(wb));
        totalSolicitado.setCellType(CellType.FORMULA);
        totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalSurtido = rTotales.createCell(5);
        totalSurtido.setCellStyle(estiloTotales(wb));
        totalSurtido.setCellType(CellType.FORMULA);
        totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalNegado = rTotales.createCell(6);
        totalNegado.setCellStyle(estiloTotales(wb));
        totalNegado.setCellType(CellType.FORMULA);
        totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")");

        Row rColumnas = sheet.createRow(10);
        Cell labelCodigo = rColumnas.createCell(0);
        labelCodigo.setCellValue("CODIGO");
        labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelOpciones = rColumnas.createCell(1);
        labelOpciones.setCellValue("OPCIONES");
        labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelModelo = rColumnas.createCell(2);
        labelModelo.setCellValue("MODELO / MATERIAL / COLOR");
        labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelTalla = rColumnas.createCell(3);
        labelTalla.setCellValue("TALLA");
        labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSolicitado = rColumnas.createCell(4);
        labelSolicitado.setCellValue("SOLICITADO");
        labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSurtido = rColumnas.createCell(5);
        labelSurtido.setCellValue("SURTIDO");
        labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelNegado = rColumnas.createCell(6);
        labelNegado.setCellValue("NEGADO");
        labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA));

        Row rValues = sheet.createRow(11);
        Cell codigo;
        Cell opciones;
        Cell modelo;
        Cell talla;
        Cell solicitado;
        Cell surtido;
        Cell negado;
        for (Dsolicitud detalle : solicitud.getDetalles()) {
            if (detalle.getProducto().getProvedor().equals(proveedor)) {
                codigo = rValues.createCell(0);
                codigo.setCellValue(detalle.getProducto().getCBarras());
                codigo.setCellStyle(estiloColumnas(wb, 0));
                opciones = rValues.createCell(1);
                opciones.setCellValue(" - ");
                opciones.setCellStyle(estiloColumnas(wb, 0));
                modelo = rValues.createCell(2);
                modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / "
                        + detalle.getProducto().getColor().getNombre());
                modelo.setCellStyle(estiloColumnas(wb, 0));
                talla = rValues.createCell(3);
                talla.setCellValue(detalle.getProducto().getTalla().getNombre());
                talla.setCellStyle(estiloColumnas(wb, 0));
                solicitado = rValues.createCell(4);
                solicitado.setCellValue(detalle.getCantidad());
                solicitado.setCellStyle(estiloColumnas(wb, 0));
                surtido = rValues.createCell(5);
                surtido.setCellStyle(estiloColumnas(wb, SURTIDO));
                negado = rValues.createCell(6);
                negado.setCellStyle(estiloColumnas(wb, 0));
            }
        }

        for (int i = 0; i <= 6; i++) {
            sheet.autoSizeColumn(i, true);
        }
    }
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls");
    wb.write(fileOut);
    fileOut.close();
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

private byte[] writeExcel(Workbook workBook) throws IOException {
    ByteArrayOutputStream bytes = new ByteArrayOutputStream();
    workBook.write(bytes);
    return bytes.toByteArray();
}

From source file:com.cisco.ca.cstg.pdi.utils.Util.java

public static void json2Excel(OutputStream output, String json, String[] properties, String[] columnsNames)
        throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Row header = sheet.createRow(0);/*from   www  .ja  v  a 2s  .  c o m*/

    for (int i = 0; i < columnsNames.length; i++) {
        String string = columnsNames[i];
        Cell cell = header.createCell(i);
        RichTextString text = new XSSFRichTextString(string);
        cell.setCellValue(text);
    }

    LOGGER.info("Writing on workbook.");
    try {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = mapper.readTree(json);

        int i = 0;
        for (JsonNode jsonNode2 : jsonNode) {
            Row row = sheet.createRow(++i);

            for (int j = 0; j < properties.length; j++) {
                String string = properties[j];
                Cell cell = row.createCell(j);
                RichTextString text = new XSSFRichTextString(jsonNode2.get(string).getTextValue());
                cell.setCellValue(text);
            }
        }
    } catch (JsonProcessingException e) {
        LOGGER.error(e.getMessage(), e);
    }

    workbook.write(output);
    workbook.close();
    LOGGER.info("Written on workbook.");
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

@SuppressWarnings("unchecked")
public void writeExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    Workbook book = null;
    InputStream is = null;/*  w  ww . j  a  v  a 2 s  .  c  o  m*/
    try {
        is = execBean.getInputStream();
    } catch (FileNotFoundException e) {

    }
    if (is != null) {
        book = WorkbookFactory.create(is);
        is.close();
    } else {
        book = new HSSFWorkbook();
    }
    CreationHelper createHelper = book.getCreationHelper();
    CellStyle dateStyle = book.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));

    CellStyle numStyle = book.createCellStyle();
    numStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.00000000"));

    CellStyle intNumStyle = book.createCellStyle();
    intNumStyle.setDataFormat(createHelper.createDataFormat().getFormat("0"));

    List<Object> result = context.getAttribute(execBean.getParamName());
    String[] columns = execBean.getColumns();
    if (execBean.getColumns() != null) {
        columns = execBean.getColumns();
    } else {
        columns = ((List<String>) context.getAttribute(execBean.getColumnsVarName())).toArray(new String[] {});
    }
    String sheetName;
    if (StringUtils.isNotEmpty(execBean.getSheetName())) {
        sheetName = execBean.getSheetName();
    } else {
        sheetName = context.getAttribute(execBean.getSheetVarName()).toString();
    }
    // int number = book.getNumberOfSheets();
    Sheet sheet = book.createSheet(sheetName);
    int rowNum = 0;
    Row firstRow = sheet.createRow(rowNum++);
    for (int i = 0; i < columns.length; i++) {
        Cell cell = firstRow.createCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(columns[i]);
    }

    for (int i = 0; i < result.size(); i++) {
        Object row = result.get(i);
        Row dataRow = sheet.createRow(rowNum++);

        for (int j = 0; j < columns.length; j++) {
            Object obj = PropertyUtils.getNestedProperty(row, columns[j]);
            Cell cell = dataRow.createCell(j);
            if (obj == null) {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                if (obj instanceof String) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(obj.toString());
                } else if (obj instanceof Date) {
                    cell.setCellValue((Date) obj);
                    cell.setCellStyle(dateStyle);
                } else if (obj instanceof Integer || obj instanceof Long) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(intNumStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else if (obj instanceof Number) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(numStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else {
                    throw new DataProcessException("??Excel?");
                }
            }
        }
    }

    OutputStream os = null;
    try {
        os = execBean.getOutputStream();
        book.write(os);
    } finally {
        if (os != null) {
            os.close();
        }
    }
}

From source file:com.coast.controler.Controler.java

public static void writeProductsToExcel(ArrayList<Product> products, String inFile, String outFile,
        ResultMSG resultMSG) throws Exception {
    int sum = 0;//from www .java  2 s  .co m
    InputStream is = null;
    OutputStream os = null;
    try {
        File f = new File(outFile);
        f.delete();
        is = new FileInputStream(new File(inFile));
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);
        String brand = null;
        Iterator<Product> iter = products.iterator();
        while (iter.hasNext()) {
            Product product = iter.next();
            ProductToSAPUtil sapUtil = new ProductToSAPUtil(product);

            //
            if (product.getSnCode().equals("K630207E00")) {
                System.out.println("error:" + product);
            }
            String fullSize = product.getSize() + "(" + sapUtil.getInternationalSize() + ")";
            int thatRowNum = getRowNum(sheet, product.getSnCode(), product.getColorCode(), fullSize);
            if (thatRowNum == 0) {
                String notFoundMsg = "SAP?sn=" + product.getSnCode() + " color="
                        + product.getColorCode() + " size=" + fullSize + " amount=" + product.getAmount()
                        + "\n";
                resultMSG.setErrorMessage(resultMSG.getErrorMessage() + notFoundMsg);
            } else {
                sheet.getRow(thatRowNum).createCell(6).setCellValue((int) product.getAmount());
                sum += product.getAmount();
            }
        }

        //
        os = new FileOutputStream(new File(outFile));
        wb.write(os);
        os.flush();
        resultMSG.setWriteMessage("?,:" + sum + "");
    } catch (Exception e) {
        e.printStackTrace();
        resultMSG.setWriteMessage(",:" + sum + ",:" + e.toString());
    } finally {
        is.close();
        os.close();
    }
}

From source file:com.coastshop.web.ShopinSAPByDateServlet.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from  w  ww.j  a  v  a 2  s  .  c om*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    OutputStream os = null;
    InputStream is = null;
    try {
        String tmpfile = request.getServletContext().getRealPath("/") + "tmp.xlsx";
        os = response.getOutputStream();
        is = new FileInputStream(tmpfile);
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String date = df.format(new Date());
        Date begindate = df.parse(request.getParameter("date1"));
        Date enddate = df.parse(request.getParameter("date2"));
        List<OutList> outlists = ServiceFactory.getOutListServiceInstance().getFromDate(begindate, enddate);
        List<StoreOutProductInfo> findSAPByLists = ServiceFactory.getStoreOutServiceInstance()
                .findSAPByLists(outlists);
        SapWorkbook swb = SAPUtil.getWorkbook(findSAPByLists, request.getServletContext().getRealPath("/"));
        Workbook wb = swb.getWorkbook();
        //download
        String brand = "";
        String filename = URLEncoder.encode(brand + "_SAP_" + date + ".xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment; filename=" + filename);
        wb.write(os);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (os != null) {
            os.close();
        }
        if (is != null) {
            is.close();
        }
    }
}