Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createSheet

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

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

Usage

From source file:Directorio.CarDir.java

private void generarXLS() {
    try {/*ww w  .  j  a v a2 s  . c o  m*/
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Datos");
        for (int i = 0; i < strRuta.size(); i++) {

            File archivoXml = new File(strRuta.get(i));

            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document document = builder.parse(archivoXml);
            NodeList nodeList = document.getElementsByTagName("*");

            HSSFRow rowTag = sheet.createRow(0); //Primera fila imprime Tags
            HSSFRow Row = sheet.createRow(i + 1); //Segunda fila imprime valor de los Tags y sucesivamente a la siguiente

            for (int j = 0; j < strTags.size(); j++) {
                String tagSel = strTags.get(j);
                for (int k = 0; k < nodeList.getLength(); k++) //Nombre de los Nodos
                {
                    String nodo = nodeList.item(k).getNodeName();

                    if (tagSel.equals(nodo)) {
                        HSSFCell Cell = rowTag.createCell(j);
                        Cell.setCellValue(tagSel);
                    }

                    for (int l = 0; l < nodeList.item(k).getChildNodes().getLength(); l++) //Valor de los Nodos
                    {
                        if (tagSel.equals(nodo)) {
                            HSSFCell Cell = Row.createCell(j);
                            Cell.setCellValue(nodeList.item(k).getChildNodes().item(l).getNodeValue());
                        }
                    }

                }

            }
            FileOutputStream output = new FileOutputStream(new File(rutXls.getText() + "\\Sheet.xls"));
            workbook.write(output);
            output.close();
        }

    } catch (IOException e) {
        System.out.println("IOException " + e.getMessage());
    } catch (ParserConfigurationException e) {
        System.out.println("ParserConfigurationException " + e.getMessage());
    } catch (SAXException e) {
        System.out.println("SAXException " + e.getMessage());
    }
}

From source file:Documentos.ClaseAlmacenGeneral.java

public void crearExcel() {
    try {//from  w  w  w .  j  a v a2  s. com

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true,
                true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idProducto, nombre, cantidad,UnidadExistencia,minStock  where tipoProducto=2 from producto");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                String minStock = String.valueOf(resultado.getInt("minStock"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + UnidadExistencia + " - " + minStock);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, UnidadExistencia);
                creandoCelda(wb, fila, 4, minStock);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:Documentos.ClaseAlmacenProducto.java

public void crearExcel() {
    try {//w ww.jav a  2s.  co m

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos existentes-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Precio de Venta", true,
                true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Ganancia", true, true);
        createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idproducto,nombre,format(cantidad,0) as cantidad,preciocompra,precioVenta,concat('$ ',format(((precioventa-preciocompra)*cantidad),2)) as ganancia, UnidadExistencia from producto where tipoProducto = 3;");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String precioVenta = String.valueOf(resultado.getString("precioVenta"));
                String ganancia = String.valueOf(resultado.getString("ganancia"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                //String Image = String.valueOf(resultado.getBlob("Image"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + precioVenta + " - " + ganancia + " - " + UnidadExistencia);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, precioVenta);
                creandoCelda(wb, fila, 4, ganancia);
                creandoCelda(wb, fila, 5, UnidadExistencia);
                //creandoCelda(wb, fila, 5, Image);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Productos" + ControllerFechas.getFechaActual()
                + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:Documentos.ClaseAlmacenXLS.java

public void crearExcel() {
    try {/*  w ww  .  j av  a2  s. c  o  m*/

        // Defino el Libro de Excel
        HSSFWorkbook wb = new HSSFWorkbook();

        // Creo la Hoja en Excel
        Sheet sheet1 = wb.createSheet("Productos");
        Sheet sheet2 = wb.createSheet("hoja2");
        Sheet sheet3 = wb.createSheet("hoja3");

        // quito las lineas del libro para darle un mejor acabado
        //            sheet.setDisplayGridlines(false);
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        // creo una nueva fila
        Row trow = sheet1.createRow((short) 0);
        createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,
                "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual());

        // Creo la cabecera de mi listado en Excel
        Row row = sheet1.createRow((short) 2);

        createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true,
                true);
        createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true);
        createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true);
        createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true);
        createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true,
                true);

        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", "");

        try ( // Creamos un Statement para poder hacer peticiones a la bd
                Statement stat = con.createStatement()) {
            ResultSet resultado = stat.executeQuery(
                    "select idProducto, nombre, cantidad,UnidadExistencia,minStock from producto where tipoProducto=2 ");
            while (resultado.next()) {

                //creamos la fila
                Row fila = sheet1.createRow(3 + i);

                String idProducto = String.valueOf(resultado.getString("idProducto"));
                String nombre = String.valueOf(resultado.getString("nombre"));
                String cantidad = String.valueOf(resultado.getInt("cantidad"));
                String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia"));
                String minStock = String.valueOf(resultado.getInt("minStock"));
                // Creo las celdas de mi fila, se puede poner un diseo a la celda
                System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - "
                        + UnidadExistencia + " - " + minStock);

                creandoCelda(wb, fila, 0, idProducto);
                creandoCelda(wb, fila, 1, nombre);
                creandoCelda(wb, fila, 2, cantidad);
                creandoCelda(wb, fila, 3, UnidadExistencia);
                creandoCelda(wb, fila, 4, minStock);
                i++;
            }
        }
        con.close();

        //            Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que 
        //            la celda se acomode segn su tamao
        Sheet ssheet = wb.getSheetAt(0);
        ssheet.autoSizeColumn(0);
        ssheet.autoSizeColumn(1);
        ssheet.autoSizeColumn(2);
        ssheet.autoSizeColumn(3);
        ssheet.autoSizeColumn(4);
        ssheet.autoSizeColumn(5);
        ssheet.autoSizeColumn(6);
        ssheet.autoSizeColumn(7);

        //Ajustando la hoja de una pagina

        Sheet sheet = wb.createSheet("format sheet");
        PrintSetup ps = sheet.getPrintSetup();
        sheet.setAutobreaks(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);

        //Area de impresion
        wb.setPrintArea(0, 0, 1, 0, 9);

        String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports"
                + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls";
        //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls";

        try (FileOutputStream fileOut = new FileOutputStream(strRuta)) {
            wb.write(fileOut);
        }
        JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta);

    } catch (Exception e) {
        e.printStackTrace();
        //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo");
    }
}

From source file:eancode.SearchEanPanelNormal.java

public void exportXLS(JTable table, String path) throws IOException, JRException {

    String headervalue[] = { "ID", "matecode", "Description", "Good", "Dmg", "Leak", "Brkn", "Total", "Origin",
            "Eancode", "Weight", "Remark", "Lotnum", "Doctype", "Docnum", "Grnnum", "Docstatus", "Grnstatus" };
    try {//from  w  w w  .ja  v  a 2 s . com
        HSSFWorkbook fWorkbook = new HSSFWorkbook();
        HSSFSheet fSheet = fWorkbook.createSheet("Pur Sheet");
        HSSFFont sheetTitleFont = fWorkbook.createFont();
        File file = new File(path);
        HSSFCellStyle cellStyle = fWorkbook.createCellStyle();

        sheetTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //sheetTitleFont.setColor();
        TableModel model = table.getModel();

        //                   for (int j = 0; j < model.getColumnCount(); j++) {
        //                             cell = fRow.createCell((short) j);
        //                             cell.setCellValue(model.getColumnName(j));
        //                            cell.setCellStyle(cellStyle);
        //
        //                        }

        for (int i = 0; i < model.getRowCount(); i++) {
            HSSFRow fRow = fSheet.createRow((short) i);
            for (int j = 0; j < model.getColumnCount(); j++) {
                HSSFCell cell = fRow.createCell((short) j);
                if (i == 0) {
                    cell.setCellValue(headervalue[j]);
                    cell.setCellStyle(cellStyle);

                } else {
                    cell.setCellValue(model.getValueAt(i, j).toString());
                    cell.setCellStyle(cellStyle);
                }
            }
        }

        FileOutputStream fileOutputStream;
        fileOutputStream = new FileOutputStream(file);
        BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream);
        fWorkbook.write(bos);
        bos.close();
        fileOutputStream.close();
    } catch (Exception e) {

    }

}

From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java

License:BSD License

public void testGenerateEXCEL() throws Exception {
    HealthcareSite hcs = healthcareSiteDao.getById(1100);

    DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
    Date startDate = (Date) format.parse("01/11/1990");
    Date endDate = (Date) format.parse("01/01/2007");
    String grantNumber = "GRANT-NO 1232";

    Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate);
    String reportingSource = healthcareSiteDao.getById(1000).getName();
    summary3Report.setReportingSource(reportingSource);

    summaryReportService.buildSummary3Report(summary3Report);

    String xmlString = summaryReportService.generateXML(summary3Report);
    File file = new File("testReport.xml");

    FileWriter fileWriter = new FileWriter(file);
    fileWriter.write(xmlString);/*from w ww.  j a va  2  s  .  c o m*/
    fileWriter.flush();
    fileWriter.close();
    System.out.println(xmlString);

    // creating the workbook and the spreadsheet

    try {

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        InputStream inputStream = new FileInputStream(file);
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

        PrintSetup printSetup = spreadSheet.getPrintSetup();
        printSetup.setLandscape(true);
        spreadSheet.setFitToPage(true);
        spreadSheet.setHorizontallyCenter(true);

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls");
        FileOutputStream output = new FileOutputStream(outputFile);
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }

}

From source file:edu.duke.cabig.c3pr.web.report.AdvanceSearchResultsExportController.java

License:BSD License

private void createExcelFile(List<AdvancedSearchRow> searchResultsRowList,
        List<ViewColumn> resultsViewColumnList) throws Exception {
    String tempDir = System.getProperty("java.io.tmpdir");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Search Results");

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 0);

    int viewColumnCount = 0;
    for (ViewColumn viewColumn : resultsViewColumnList) {
        row.createCell((short) viewColumnCount).setCellValue(viewColumn.getColumnTitle());
        viewColumnCount++;//from w  w w  . j  a v a2s .com
    }

    int rowCount = 1;
    for (AdvancedSearchRow searchRow : searchResultsRowList) {
        int columnCount = 0;
        row = sheet.createRow((short) rowCount++);
        for (AdvancedSearchColumn searchColumn : searchRow.getColumnList()) {
            if (searchColumn.getValue() != null)
                row.createCell((short) columnCount++).setCellValue(searchColumn.getValue().toString());
            else
                row.createCell((short) columnCount++).setCellValue("");
        }
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(tempDir + File.separator + XLS_SEARCH_RESULTS_FILENAME);
    wb.write(fileOut);
    fileOut.close();
}

From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java

License:BSD License

public void generateExcel(String summary3ReportXml, String file) throws Exception {

    // creating the workbook and the spreadsheet
    try {/*from ww w. j a v a 2 s  .c  o  m*/

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        String fileName = "";

        fileName = file;

        InputStream inputStream = new FileInputStream((fileName + ".xml"));
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

        PrintSetup printSetup = spreadSheet.getPrintSetup();
        printSetup.setLandscape(true);
        spreadSheet.setFitToPage(true);
        spreadSheet.setHorizontallyCenter(true);

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel")));
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }
}

From source file:edu.swjtu.excel.ExportCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? //ww  w. jav  a2  s  . c  o m
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    ArrayList<Car> list = null;
    list = new CarDaoImpl().getAllCar(con);

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    //?
    //      FileOutputStream fout = new FileOutputStream("E:/car_data.xls");  
    //      wb.write(fout);  
    //      fout.close();  

    String myexcel = "All_Car";
    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

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

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:edu.swjtu.excel.ExportConCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? // ww  w . j ava  2  s. com
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    String condition = request.getParameter("condition");
    String sea_condition = request.getParameter("sea_condition");
    String search_cho = null;

    ArrayList<Car> list = null;
    CarDaoImpl carr = new CarDaoImpl();

    if (condition.equals("0")) {
        list = carr.getAllCar(con);
        search_cho = "All";
    } else if (condition.equals("1")) {
        list = carr.getCarByLicensePlate_V(sea_condition, con);
        search_cho = "LicensePlate";
    } else if (condition.equals("2")) {
        list = carr.getCarByBrand_V(sea_condition, con);
        search_cho = "Brand";
    } else if (condition.equals("3")) {
        list = carr.getCarByDriver_V(sea_condition, con);
        search_cho = "Driver";
    } else if (condition.equals("4")) {
        list = carr.getCarByArrangeId_V(sea_condition, con);
        search_cho = "ArrangeId";
    } else if (condition.equals("5")) {
        list = carr.getCarByNumber_V(sea_condition, con);
        search_cho = "Number";
    } else if (condition.equals("6")) {
        list = carr.getCarByDrivingLicense_V(sea_condition, con);
        search_cho = "DrivingLicenseber";
    } else if (condition.equals("7")) {
        list = carr.getCarByLicense_V(sea_condition, con);
        search_cho = "License";
    } else {
    }

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    String myexcel = search_cho + "_Car";

    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

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

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}