Example usage for org.apache.poi.hssf.usermodel HSSFCell setCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. setCellValue

Introduction

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

Prototype

@SuppressWarnings("fallthrough")
public void setCellValue(boolean value)

Source Link

Document

set a boolean value for the cell

Usage

From source file:edu.aplic.utilerias.GenerarArchivoXlsUtil.java

public static void crearXlsCompras(File file, Compra[] aCompras, Usuario oUsuario) throws IOException {

    FileOutputStream elFichero = null;
    // Se crea el libro
    HSSFWorkbook libro = new HSSFWorkbook();

    // Se crea una hoja dentro del libro
    HSSFSheet hoja = libro.createSheet();
    HSSFRow fila = null;//from   w  ww. jav  a2s  .  co m
    HSSFCell celda = null;

    fila = hoja.createRow(0);
    celda = fila.createCell(0);
    hoja.autoSizeColumn(0);
    celda.setCellValue(new HSSFRichTextString("Reporte Compras"));

    if (oUsuario != null) {
        celda = fila.createCell(1);
        hoja.autoSizeColumn(0);
        celda.setCellValue(new HSSFRichTextString("Usuario: " + oUsuario.getNombreCompleto()));
    }

    // Se crea una fila dentro de la hoja
    fila = hoja.createRow(1);
    // Se crea una celda dentro de la fila
    celda = fila.createCell(0);
    hoja.autoSizeColumn(0);
    // Se crea el contenido de la celda y se mete en ella.
    //HSSFRichTextString texto = new HSSFRichTextString("hola mundo");
    celda.setCellValue(new HSSFRichTextString("Fecha"));

    // Se crea una celda dentro de la fila
    celda = fila.createCell(1);
    // Se crea el contenido de la celda y se mete en ella.
    //HSSFRichTextString texto = new HSSFRichTextString("hola mundo");
    celda.setCellValue(new HSSFRichTextString("Total"));

    ProductoComprado oProductoComprado = new ProductoComprado();
    ProductoComprado[] aProductoComprado = null;

    double nTotal3 = 0.0D;
    int rows = 1;
    if (aCompras != null && aCompras.length > 0) {
        for (Compra oCompraTemp : aCompras) {
            ++rows;
            hoja.autoSizeColumn(0);
            fila = hoja.createRow(rows);

            //                Usuario oUsuario = new Usuario();
            //                oUsuario = oCompraTemp.getUsuario();    
            aProductoComprado = oCompraTemp.getProductoComprado();

            // Se crea una celda dentro de la fila
            celda = fila.createCell(0);
            // Se crea el contenido de la celda y se mete en ella.
            celda.setCellValue(new HSSFRichTextString(oUtil.ordenarDiaMesAnio(oCompraTemp.getFecha())));
            // Se crea una celda dentro de la fila
            celda = fila.createCell(1);
            // Se crea el contenido de la celda y se mete en ella.
            celda.setCellValue(new HSSFRichTextString(formato.format(aProductoComprado[0].getMonto())));
            nTotal3 += aProductoComprado[0].getMonto();
        }
        ++rows;
        fila = hoja.createRow(rows);
        // Se crea una celda dentro de la fila
        celda = fila.createCell(0);
        // Se crea el contenido de la celda y se mete en ella.
        //HSSFRichTextString texton = new HSSFRichTextString("value");
        celda.setCellValue(new HSSFRichTextString("Total"));
        // Se crea una celda dentro de la fila
        celda = fila.createCell(1);
        // Se crea el contenido de la celda y se mete en ella.
        celda.setCellValue(new HSSFRichTextString(formato.format(nTotal3)));
        //this.campoTotal1.setText(formato.format(nTotal));   
    }
    // Se salva el libro.
    try {
        //FileOutputStream elFichero = new FileOutputStream("ventas"+new Date().toString()+".xls");
        elFichero = new FileOutputStream(file);

        libro.write(elFichero);
        JOptionPane.showMessageDialog(null, "Reporte de compras generado correctamente \n " + file.getPath());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        elFichero.close();
    }
}

From source file:edu.aplic.utilerias.GenerarArchivoXlsUtil.java

public static void crearXlsGastos(File file, Gasto[] aGastos, Usuario oUsuario) throws IOException {

    FileOutputStream elFichero = null;
    // Se crea el libro
    HSSFWorkbook libro = new HSSFWorkbook();

    // Se crea una hoja dentro del libro
    HSSFSheet hoja = libro.createSheet();
    HSSFRow fila = null;/*from w  w  w . j av  a  2 s. com*/
    HSSFCell celda = null;

    fila = hoja.createRow(0);
    celda = fila.createCell(0);
    hoja.autoSizeColumn(0);
    celda.setCellValue(new HSSFRichTextString("Reporte Gastos"));

    if (oUsuario != null) {
        celda = fila.createCell(1);
        hoja.autoSizeColumn(0);
        celda.setCellValue(new HSSFRichTextString("Usuario: " + oUsuario.getNombreCompleto()));
    }

    // Se crea una fila dentro de la hoja
    fila = hoja.createRow(1);
    // Se crea una celda dentro de la fila
    celda = fila.createCell(0);
    hoja.autoSizeColumn(0);
    // Se crea el contenido de la celda y se mete en ella.
    //HSSFRichTextString texto = new HSSFRichTextString("hola mundo");
    celda.setCellValue(new HSSFRichTextString("Fecha"));

    // Se crea una celda dentro de la fila
    celda = fila.createCell(1);
    // Se crea el contenido de la celda y se mete en ella.
    //HSSFRichTextString texto = new HSSFRichTextString("hola mundo");
    celda.setCellValue(new HSSFRichTextString("Total"));

    double nTotal = 0.0D;
    int rows = 1;
    if (aGastos != null && aGastos.length > 0) {
        for (Gasto oGastoTemp : aGastos) {
            ++rows;
            hoja.autoSizeColumn(0);
            fila = hoja.createRow(rows);

            //                Usuario oUsuario = new Usuario();
            //                oUsuario = oGastoTemp.getUsuario();    

            // Se crea una celda dentro de la fila
            celda = fila.createCell(0);
            // Se crea el contenido de la celda y se mete en ella.
            celda.setCellValue(new HSSFRichTextString(oUtil.ordenarDiaMesAnio(oGastoTemp.getFecha())));
            // Se crea una celda dentro de la fila
            celda = fila.createCell(1);
            // Se crea el contenido de la celda y se mete en ella.
            celda.setCellValue(new HSSFRichTextString(formato.format(oGastoTemp.getMonto())));
            nTotal += oGastoTemp.getMonto();
        }
        ++rows;
        fila = hoja.createRow(rows);
        // Se crea una celda dentro de la fila
        celda = fila.createCell(0);
        // Se crea el contenido de la celda y se mete en ella.
        //HSSFRichTextString texton = new HSSFRichTextString("value");
        celda.setCellValue(new HSSFRichTextString("Total"));
        // Se crea una celda dentro de la fila
        celda = fila.createCell(1);
        // Se crea el contenido de la celda y se mete en ella.
        celda.setCellValue(new HSSFRichTextString(formato.format(nTotal)));
        //this.campoTotal1.setText(formato.format(nTotal));   
    }
    // Se salva el libro.
    try {
        //FileOutputStream elFichero = new FileOutputStream("ventas"+new Date().toString()+".xls");
        elFichero = new FileOutputStream(file);

        libro.write(elFichero);
        JOptionPane.showMessageDialog(null, "Reporte de gastos generado correctamente \n " + file.getPath());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        elFichero.close();
    }
}

From source file:edu.corgi.uco.Secretary.java

public void downloadReportData() throws IOException {
    if (!Students.isEmpty()) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("First Name");
        HSSFCell cell01 = row0.createCell(1);
        cell01.setCellValue("Last Name");
        HSSFCell cell02 = row0.createCell(2);
        cell02.setCellValue("Email");
        HSSFCell cell03 = row0.createCell(3);
        cell03.setCellValue("UCO ID");
        HSSFCell cell04 = row0.createCell(4);
        cell04.setCellValue("Appointment Date");

        for (int x = 0; x < Students.size(); x++) {
            HSSFRow row = sheet.createRow(x + 1);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(Students.get(x).getStudentFirstName());
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(Students.get(x).getStudentLastName());
            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(Students.get(x).getStudentEmail());
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(Students.get(x).getUcoID());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(Students.get(x).getMeetingDate().toString());
        }/*from  w ww  . j  a  v a2  s .  c om*/

        FacesContext facesContext = FacesContext.getCurrentInstance();
        ExternalContext externalContext = facesContext.getExternalContext();
        externalContext.setResponseContentType("application/vnd.ms-excel");
        externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"report.xls\"");

        workbook.write(externalContext.getResponseOutputStream());
        facesContext.responseComplete();

    }

}

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  w  w. jav a2s .  co 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.xml.Summary3ReportGenerator.java

License:BSD License

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

    // creating the workbook and the spreadsheet
    try {/* ww w. j  av  a2s . 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.jhu.cvrg.services.nodeDataService.DataStaging.java

License:Open Source License

private HSSFSheet consolidateAlgorithmFiles(int algorithmCode, HSSFSheet sheet, String subjectIds,
        String userId, String fileNames, boolean isPublic, ApacheCommonsFtpWrapper ftpClient) {

    boolean firstToken = true;
    StringTokenizer tokenizer = new StringTokenizer(subjectIds, "^");
    StringTokenizer fileTokenizer = new StringTokenizer(fileNames, "^");
    String currentSubject = null;
    int lineNumber = 0;
    String headerIndicator = null;
    switch (algorithmCode) {
    case 0://from   www . j a  v  a  2  s . c  o m
        headerIndicator = CHESNOKOV_HEADER_INDICATOR;
        break;
    case 1:
        headerIndicator = BERGER_HEADER_INDICATOR;
        break;
    }
    String directory = localFtpRoot + sep + userId + utils.generateTimeStamp();
    while (tokenizer.hasMoreTokens() && fileTokenizer.hasMoreTokens()) {
        currentSubject = tokenizer.nextToken();
        System.out.println(currentSubject);
        String file = fileTokenizer.nextToken();
        String bareFile = file.substring(file.lastIndexOf("/") + 1);
        try {
            ftpClient.downloadFile(file, directory + bareFile);
            FileReader fi = new FileReader(directory + bareFile);
            BufferedReader br = new BufferedReader(fi);
            String thisLine = null, value = null;
            int inputLineNumber = 0;

            while ((thisLine = br.readLine()) != null) {
                boolean isHeader = false;
                isHeader = thisLine.contains(headerIndicator);
                if (thisLine != null && thisLine.length() > 0 && (firstToken || !isHeader)) {
                    HSSFRow rowOut = sheet.createRow(lineNumber);
                    StringTokenizer rowTokenizer = new StringTokenizer(thisLine, ",");
                    HSSFCell cellOut;
                    int colNumber = 0;
                    while (rowTokenizer.hasMoreTokens()) {

                        cellOut = rowOut.createCell(colNumber);
                        String replacePipes = rowTokenizer.nextToken();
                        if (isHeader) {

                            replacePipes = replacePipes.replaceAll("\\|", ",");
                            value = replacePipes;
                            StringTokenizer temp = new StringTokenizer(replacePipes, ",");
                            if (temp.countTokens() > 1) {

                                HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                                link.setAddress(temp.nextToken());
                                cellOut.setHyperlink(link);
                                value = temp.nextToken().trim();

                            }
                            cellOut.setCellValue(value);
                            sheet.setColumnWidth(colNumber, (value.length() + 3) * 256);

                        } else {

                            cellOut.setCellValue(replacePipes);
                            if (sheet.getColumnWidth(colNumber) / 256 < replacePipes.length())
                                sheet.setColumnWidth(colNumber, replacePipes.length() * 256);
                        }
                        colNumber++;
                    }
                    lineNumber++;
                }
                inputLineNumber++;

            }
            fi.close();
            br.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //}
        firstToken = false;

    }

    return sheet;
}

From source file:edu.ku.brc.dbsupport.TableModel2Excel.java

License:Open Source License

/**
 * Converts a tableModel to an Excel Spreadsheet.
 * @param toFile the file object to write it to.
 * @param title the title of the spreadsheet.
 * @param tableModel the table model/*w w w  . j  a v  a 2  s . co  m*/
 * @return a file to a spreadsheet
 */
public static File convertToExcel(final File toFile, final String title, final TableModel tableModel) {
    if (toFile == null) {
        UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_EXISTS",
                toFile != null ? toFile.getAbsolutePath() : "");
        return null;
    }

    if (tableModel != null && tableModel.getRowCount() > 0) {
        try {
            // create a new file
            FileOutputStream out;
            try {
                out = new FileOutputStream(toFile);

            } catch (FileNotFoundException ex) {
                UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_WRITE",
                        toFile != null ? toFile.getAbsolutePath() : "");
                return null;
            }

            // create a new workbook
            HSSFWorkbook wb = new HSSFWorkbook();

            // create a new sheet
            HSSFSheet sheet = wb.createSheet();
            // declare a row object reference

            // Header Captions
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            // create a style for the header cell
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            headerStyle.setFont(headerFont);
            setBordersOnStyle(headerStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            short numColumns = (short) tableModel.getColumnCount();

            HSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < numColumns; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                headerCell.setCellStyle(headerStyle);

                //add the date to the header cell
                headerCell.setCellValue(tableModel.getColumnName(i));
                sheet.setColumnWidth((short) i, (short) (30 * 256));
            }

            //--------------------------
            // done header
            //--------------------------

            // create 3 cell styles
            HSSFCellStyle oddCellStyle = wb.createCellStyle();
            HSSFCellStyle evenCellStyle = wb.createCellStyle();

            setBordersOnStyle(oddCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);
            setBordersOnStyle(evenCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            // create 2 fonts objects
            HSSFFont cellFont = wb.createFont();
            //set font 1 to 12 point type
            cellFont.setFontHeightInPoints((short) 11);
            oddCellStyle.setFont(cellFont);
            evenCellStyle.setFont(cellFont);

            evenCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            oddCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            oddCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
            evenCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

            oddCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            evenCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            // set the sheet name to HSSF Test
            wb.setSheetName(0, title);

            for (short rownum = 0; rownum < (short) tableModel.getRowCount(); rownum++) {
                // create a row
                HSSFRow row = sheet.createRow(rownum + 1);

                for (short cellnum = (short) 0; cellnum < numColumns; cellnum++) {
                    // create a numeric cell
                    HSSFCell cell = row.createCell(cellnum);

                    Object dataVal = tableModel.getValueAt(rownum, cellnum);
                    cell.setCellValue(dataVal != null ? dataVal.toString() : "");

                    // on every other row
                    cell.setCellStyle((rownum % 2) == 0 ? evenCellStyle : oddCellStyle);
                }
            }

            // write the workbook to the output stream
            // close our file (don't blow out our file handles
            wb.write(out);
            out.close();

        } catch (Exception ex) {
            edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(TableModel2Excel.class, ex);
            log.error("convertToExcel", ex); //$NON-NLS-1$
        }
    }
    return toFile;
}

From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSExport.java

License:Open Source License

/**
 * calls HSSFCell.setCellValue //from www.j a v  a2  s  . c o  m
 * 
 * Since all data is treated as string data by the WB and is not validated until an upload is attempted,
 * Validation and type-checking is no longer performed here since it could lead to loss of data 
 * in the exported file.
 * 
 * @param cell
 * @param value
 */
protected void setCellValue(final HSSFCell cell, final String value) {
    cell.setCellValue(new HSSFRichTextString(value));
}

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);
    // ? // w ww  .  java  2s.c  om
    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);
    // ? /*from   w  ww .ja v a 2  s.co  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();
    }

    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();
    }

}