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

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

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

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java

License:Apache License

private void initStyles(HSSFWorkbook wb) {
    // cache styles used to write text into cells
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();//w  w w.  j  ava  2 s.c  o  m
    font.setBold(true);
    style.setFont(font);
    styles.put(STYLE_BOLD, style);

    style = wb.createCellStyle();
    font = wb.createFont();
    font.setItalic(true);
    style.setFont(font);
    styles.put(STYLE_ITALIC, style);

    style = wb.createCellStyle();
    font = wb.createFont();
    font.setItalic(true);
    font.setColor(Font.COLOR_RED);
    style.setFont(font);
    styles.put(STYLE_REVIEW, style);

    style = wb.createCellStyle();
    style.setFillPattern(FillPatternType.FINE_DOTS);
    style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    styles.put(STYLE_MISSING, style);

    style = wb.createCellStyle();
    style.setFillPattern(FillPatternType.FINE_DOTS);
    style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFont(font);
    styles.put(STYLE_MISSING_REVIEW, style);

    style = wb.createCellStyle();
    HSSFCreationHelper createHelper = wb.getCreationHelper();
    style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-dd-mm hh:mm"));
    styles.put(STYLE_DATETIME, style);
}

From source file:demo.admin.controller.UserController.java

@RequestMapping(value = "/user/downloadData")
@VerifyAuthentication(Trader = true, Admin = true, Operation = true)
public HttpEntity<byte[]> downloadUserData(String status, String securephone,
        @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
        @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate)
        throws IOException, DocumentException {
    if (securephone != null && securephone != "") {
        securephone = Where.$like$(securephone);
    }//from   ww  w .  java2 s.co  m
    List<Map<String, Object>> users = userMapper.userExport(status, securephone, startDate, endDate);
    String type = status + "?";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(type);
    HSSFRow row = sheet.createRow(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    sheet.setVerticallyCenter(true);
    sheet.setHorizontallyCenter(true);
    String[] excelHeader = { "??", "???", "??", "", "??" };
    for (int i = 0; i < excelHeader.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(excelHeader[i]);
        cell.setCellStyle(cellStyle);
        sheet.autoSizeColumn(i, true);
    }
    for (int i = 0; i < users.size(); i++) {
        Map<String, Object> resultSet = users.get(i);
        sheet.autoSizeColumn(i, true);
        row = sheet.createRow(i + 1);
        row.setRowStyle(cellStyle);
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(String.valueOf(resultSet.get("name")));
        row.createCell(2).setCellValue(String.valueOf(resultSet.get("address")));
        row.createCell(3).setCellValue(String.valueOf(resultSet.get("registertime")));
        row.createCell(4).setCellValue(String.valueOf(resultSet.get("securephone")));

    }
    File file = File.createTempFile(".xls", ".xls");
    OutputStream out = new FileOutputStream(file);
    wb.write(out);
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attachment",
            URLEncoder.encode(type, "UTF-8") + LocalDate.now() + ".xls");
    return new HttpEntity<byte[]>(FileUtils.readFileToByteArray(file), headers);
}

From source file:demo.poi.BigExample.java

License:Apache License

public static void main(String[] args) throws IOException {
    int rownum;/* www  . j ava2s  . c o m*/

    // create a new file
    FileOutputStream out = new FileOutputStream("target/bigworkbook.xls");
    // create a new workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet s = wb.createSheet();
    // declare a row object reference
    HSSFRow r = null;
    // declare a cell object reference
    HSSFCell c = null;
    // create 3 cell styles
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    // create 2 fonts objects
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it red
    f.setColor(HSSFColor.RED.index);
    // make it bold
    //arial is the default font
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it the color at palette index 0xf (white)
    f2.setColor(HSSFColor.WHITE.index);
    //make it bold
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set cell stlye
    cs.setFont(f);
    //set the cell format see HSSFDataFromat for a full list
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));

    //set a thin border
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // set foreground fill to red
    cs2.setFillForegroundColor(HSSFColor.RED.index);

    // set the font
    cs2.setFont(f2);

    // set the sheet name to HSSF Test
    wb.setSheetName(0, "HSSF Test");
    // create a sheet with 300 rows (0-299)
    for (rownum = 0; rownum < 300; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 50 cells (0-49) (the += 2 becomes apparent later
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
            }

            // create a string cell (see why += 2 in the
            c = r.createCell(cellnum + 1);

            // set the cell's string value to "TEST"
            c.setCellValue("TEST");
            // make this column a bit wider
            s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this to the white on red cell style
                // we defined above
                c.setCellStyle(cs2);
            }

        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(CellStyle.BORDER_THICK);

    //create 50 cells
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet

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

From source file:devcup.search4u.xlsview.ConvertResultsToXLS.java

public void createXLSTable() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Result sheet");

    //create headerRow
    Row headerRow = sheet.createRow(0);/*from w w w  . j  a  v  a  2 s . co  m*/

    HSSFCellStyle headerStyle = workbook.createCellStyle();
    HSSFCellStyle style = workbook.createCellStyle();

    HSSFFont partFont = workbook.createFont();
    //partFont.setItalic(true);
    partFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    partFont.setUnderline(HSSFFont.U_DOUBLE);

    HSSFFont defaultFont = workbook.createFont();
    defaultFont.setFontHeightInPoints((short) 10);
    defaultFont.setFontName("Arial");
    defaultFont.setColor(HSSFColor.BLACK.index);
    defaultFont.setItalic(false);

    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 11);
    font.setFontName("Arial");
    font.setColor(HSSFColor.BLUE_GREY.index);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setItalic(true);

    //style=(HSSFCellStyle) headerRow.getRowStyle();       
    //style.setFillPattern(CellStyle.SOLID_FOREGROUND);       
    headerStyle.setWrapText(true);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
    //style.setFillBackgroundColor(HSSFColor.ROYAL_BLUE.index);
    //style.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
    //style.setHidden(false);
    //style.setIndention(indent);
    headerStyle.setFont(font);

    //create a new cell in headerRow
    Cell headerCell;
    headerCell = headerRow.createCell(0);
    headerCell.setCellValue("?");
    headerCell.setCellStyle(headerStyle);
    //sheet.autoSizeColumn(0);
    sheet.setColumnWidth(0, 7000);

    headerCell = headerRow.createCell(1);
    headerCell.setCellValue("  ");
    headerCell.setCellStyle(headerStyle);
    //sheet.autoSizeColumn(1);
    sheet.setColumnWidth(1, 12000);

    /*headerCell = headerRow.createCell(2);
    headerCell.setCellValue("?  ");
    headerCell.setCellStyle(style);
    sheet.autoSizeColumn(2);
    //sheet.setColumnWidth(2, 7000);
    */

    headerCell = headerRow.createCell(2);
    headerCell.setCellValue(" ?");
    headerCell.setCellStyle(headerStyle);
    //sheet.autoSizeColumn(3);
    sheet.setColumnWidth(2, 18000);

    style.setWrapText(true);
    style.setFont(defaultFont);

    //create another Rows
    int rowNum = 1;
    for (SearchResult searchResult : searchResultsList) {
        for (String key : searchResult.getRelevDocsPath()) {
            //for (String key : searchResult.getDocumentsFragments().keySet()) {
            for (ObjectPair<Integer, String> pair : searchResult.getDocumentsFragments().get(key)) {

                Row row = sheet.createRow(rowNum++);

                Cell cell;
                cell = row.createCell(0);
                cell.setCellValue(searchResult.getQuery());
                cell.setCellStyle(style);

                cell = row.createCell(1);
                cell.setCellValue(key);
                cell.setCellStyle(style);

                //cell = row.createCell(2);
                //cell.setCellValue(pair.getFirst());
                //cell.setCellStyle(style);

                cell = row.createCell(2);
                cell.setCellValue(labeledStringToRichTextString(pair.getSecond(), partFont));
                cell.setCellStyle(style);

            }
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(new File(resultXMLPath));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:Documentos.ClaseAlmacenGeneral.java

private static void createTituloCell(HSSFWorkbook wb, Row row, int column, short halign, short valign,
        String strContenido) {/*  ww  w .  j  av a  2  s. co  m*/
    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);
    cell.setCellValue(ch.createRichTextString(strContenido));

    HSSFFont cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 11);
    cellFont.setFontName(HSSFFont.FONT_ARIAL);
    cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cellStyle.setFont(cellFont);
    cell.setCellStyle(cellStyle);

}

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 www.ja va  2 s.  c  o  m*/
        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.aplic.utilerias.GenerarArchivoXlsUtil.java

public static void crearXslVentasPorTipoProducto(File file, Venta[] aVentas, 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  ww  w.java 2  s . c o m*/
    HSSFCell celda = null;

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

    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"));

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

    // Style Cell 2B
    HSSFCellStyle cellStyle = libro.createCellStyle();
    cellStyle = libro.createCellStyle();
    cellStyle.setFillForegroundColor(org.apache.poi.hssf.util.HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderTop((short) 1); // single line border
    cellStyle.setBorderBottom((short) 1); // single line border

    ProductoVendido oProductoVendido = new ProductoVendido();
    ProductoVendido[] aProductoVendido = null;
    double nTotal = 0.0D;
    int rows = 1;
    String sFechaTemp = null;
    boolean bColor = true;
    if (aVentas != null && aVentas.length > 0) {
        boolean bPrimerFecha = true;
        for (Venta oVentaTemp : aVentas) {

            // Contador para inicializar la fecha del primer elemento
            if (bPrimerFecha) {
                sFechaTemp = oUtil.ordenarDiaMesAnio(oVentaTemp.getFecha());
                bPrimerFecha = false;
            } else {
                String sFechaFila = oUtil.ordenarDiaMesAnio(oVentaTemp.getFecha());

                if (!sFechaTemp.equals(sFechaFila)) {
                    bColor = !bColor;
                }

                if (bColor) {
                    cellStyle = libro.createCellStyle();
                    cellStyle.setFillForegroundColor(org.apache.poi.hssf.util.HSSFColor.GREY_25_PERCENT.index);
                    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    cellStyle.setBorderTop((short) 1); // single line border
                    cellStyle.setBorderBottom((short) 1); // single line border

                } else {
                    cellStyle = libro.createCellStyle();
                    cellStyle.setFillForegroundColor(org.apache.poi.hssf.util.HSSFColor.BLUE_GREY.index);
                    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    cellStyle.setBorderTop((short) 1); // single line border
                    cellStyle.setBorderBottom((short) 1); // single line border

                }

                sFechaTemp = sFechaFila;

            }

            ++rows;
            hoja.autoSizeColumn(0);
            fila = hoja.createRow(rows);
            //                Usuario oUsuario = new Usuario();
            //                oUsuario = oVentaTemp.getUsuario();    
            aProductoVendido = oVentaTemp.getProductoVendido();

            // 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(oVentaTemp.getFecha())));
            celda.setCellStyle(cellStyle);

            // 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(aProductoVendido[0].getImporte())));
            celda.setCellStyle(cellStyle);

            // Se crea una celda dentro de la fila
            celda = fila.createCell(2);
            // Se crea el contenido de la celda y se mete en ella.
            celda.setCellValue(new HSSFRichTextString(aProductoVendido[0].getProducto().getTipo()));
            celda.setCellStyle(cellStyle);

            nTotal += aProductoVendido[0].getImporte();
        }

        ++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 ventas generado correctamente \n " + file.getPath());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        elFichero.close();
    }
}

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 .ja v a 2s .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.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  av  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.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/*www.j  av  a2  s  .  c o 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;
}