Example usage for org.apache.poi.ss.usermodel CellStyle setAlignment

List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment

Introduction

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

Prototype

void setAlignment(HorizontalAlignment align);

Source Link

Document

set the type of horizontal alignment for the cell

Usage

From source file:FormatStatics.HighlightStyle.java

/**
 * A simple highlight style that returns a bright yellow highlight cell style
 * @param wb The current workbook object
 * @return The desired yellow cell style
 *///from   w ww .j  av  a  2 s .com
public static CellStyle YellowBoldHighlight(Workbook wb) {
    CellStyle style;
    Font highlightFont = wb.createFont();
    highlightFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = BorderedStyle.createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(highlightFont);
    return style;
}

From source file:forseti.reportes.JReportesDlg.java

License:Open Source License

public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb)
        throws ServletException, IOException {
    JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet");
    JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1");
    JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1");
    Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1");
    Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2");
    Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3");
    Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1");
    Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2");
    Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3");
    JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1");
    JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2");
    JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3");
    JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1");
    JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2");
    JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3");

    String fsi_filtro = (String) request.getAttribute("fsi_filtro");

    Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report()));

    short nrow = 0;
    Row row = sheet.createRow(nrow++);/*from  w  w w.ja  v a 2s  .co m*/
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    Cell cell = row.createCell(0);
    cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    JRepCellStyles cellStyles = new JRepCellStyles(wb);
    /*
    Font fenc = wb.createFont();
    fenc.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fenc.setColor(HSSFColor.WHITE.index);
            
    Font fnorm = wb.createFont();
    fnorm.setColor(HSSFColor.BLACK.index);
            
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(font);
            
    if(alin != null)
    {
      if(alin.equals("center"))
     cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
      else if(alin.equals("right"))
     cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
      else
     cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
    }
            
    if(tipocel != null)
    {
      if(tipocel.equals("encabezado"))
      {
     cellStyle.setBorderTop(CellStyle.BORDER_THIN);
     cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
     cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
     cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
             
      }
      else if(tipocel.equals("agregado"))
      {
     cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
     cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
      }
    }
    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    */

    if (m_bSelectL1.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL1.getNumRows(); i++) {
            if (m_colL1.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL2.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL2.getNumRows(); i++) {
            if (m_colL2.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL3.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL3.getNumRows(); i++) {
            if (m_colL3.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName().toUpperCase(), "general",
                        "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL1.booleanValue()) {
        for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) {
            Row rowl1 = sheet.createRow(nrow++);
            for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) {
                if (m_colL1.getAbsRow(CL1).getWillShow())
                    JUtil.DatoXLS(cellStyles, rowl1, CL1,
                            m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()),
                            m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(),
                            m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request);
            }

            // Nivel 2
            if (m_bSelectL2.booleanValue()) {
                for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) {
                    Row rowl2 = sheet.createRow(nrow++);
                    for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) {
                        if (m_colL2.getAbsRow(CL2).getWillShow())
                            JUtil.DatoXLS(cellStyles, rowl2, CL2,
                                    m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2)
                                            .getSTS(m_colL2.getAbsRow(CL2).getColName()),
                                    m_colL2.getAbsRow(CL2).getFormat(),
                                    m_colL2.getAbsRow(CL2).getBindDataType(),
                                    m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request);
                    }

                    // Nivel 3
                    if (m_bSelectL3.booleanValue()) {
                        for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                .getNumRows(); RL3++) {
                            Row rowl3 = sheet.createRow(nrow++);
                            for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) {
                                if (m_colL3.getAbsRow(CL3).getWillShow())
                                    JUtil.DatoXLS(cellStyles, rowl3, CL3,
                                            m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                                    .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()),
                                            m_colL3.getAbsRow(CL3).getFormat(),
                                            m_colL3.getAbsRow(CL3).getBindDataType(),
                                            m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request);

                            }

                        }
                        if (m_bComputeL3.booleanValue()) {
                            for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                    .getNumRows(); RC3++) {
                                Row rowc3 = sheet.createRow(nrow++);
                                for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) {
                                    if (m_colCL3.getAbsRow(CC3).getWillShow())
                                        JUtil.DatoXLS(cellStyles, rowc3, CC3,
                                                m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                                        .getAbsRow(RC3)
                                                        .getSTS(m_colCL3.getAbsRow(CC3).getColName()),
                                                m_colCL3.getAbsRow(CC3).getFormat(),
                                                m_colCL3.getAbsRow(CC3).getBindDataType(),
                                                m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc",
                                                request);
                                }

                            }
                        } // Fin SI CL3
                    } // Fin SI L3
                }
                if (m_bComputeL2.booleanValue()) {
                    for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) {
                        Row rowc2 = sheet.createRow(nrow++);
                        for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) {
                            if (m_colCL2.getAbsRow(CC2).getWillShow())
                                JUtil.DatoXLS(cellStyles, rowc2, CC2,
                                        m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2)
                                                .getSTS(m_colCL2.getAbsRow(CC2).getColName()),
                                        m_colCL2.getAbsRow(CC2).getFormat(),
                                        m_colCL2.getAbsRow(CC2).getBindDataType(),
                                        m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request);
                        }
                    }
                } // Fin SI CL2
            } // Fin SI L2
        }
        if (m_bComputeL1.booleanValue()) {
            for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) {
                Row rowc1 = sheet.createRow(nrow++);
                for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) {
                    if (m_colCL1.getAbsRow(CC1).getWillShow())
                        JUtil.DatoXLS(cellStyles, rowc1, CC1,
                                m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()),
                                m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(),
                                m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request);
                }
            }
        } // Fin SI CL1
    } // Fin SI L1

    int colsmer;
    if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL1.getNumRows() - 1;
    else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL2.getNumRows() - 1;
    else
        colsmer = m_colL3.getNumRows() - 1;

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer)));

}

From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java

License:Apache License

/**
 * Initialisation des styles de cellule/*w  w w .j  a  v a2  s  .  c  om*/
 */
protected void initStyles() {
    CellStyle defaultStyle = workbook.createCellStyle();
    defaultStyle.setFont(getFont(FONT_NORMAL_NAME));
    setStyleFillForegroundColor(defaultStyle, colorRegistry, HSSFColor.WHITE.index);
    defaultStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    defaultStyle.setBorderBottom(CellStyle.BORDER_THIN);
    setStyleBottomBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderLeft(CellStyle.BORDER_THIN);
    setStyleLeftBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderRight(CellStyle.BORDER_THIN);
    setStyleRightBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setBorderTop(CellStyle.BORDER_THIN);
    setStyleTopBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX);
    defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    defaultStyle.setWrapText(true);
    registerStyle(STYLE_DEFAULT_NAME, defaultStyle);

    CellStyle styleHeader = workbook.createCellStyle();
    styleHeader.setAlignment(CellStyle.ALIGN_CENTER);
    styleHeader.setFont(getFont(FONT_HEADER_NAME));
    setStyleFillForegroundColor(styleHeader, colorRegistry, HEADER_BACKGROUND_COLOR_INDEX);
    styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleHeader.setBorderBottom(CellStyle.BORDER_THIN);
    setStyleBottomBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderLeft(CellStyle.BORDER_THIN);
    setStyleLeftBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderRight(CellStyle.BORDER_THIN);
    setStyleRightBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setBorderTop(CellStyle.BORDER_THIN);
    setStyleTopBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX);
    styleHeader.setDataFormat((short) 0);
    styleHeader.setWrapText(true);
    registerStyle(STYLE_HEADER_NAME, styleHeader);

    CellStyle styleOdd = cloneStyle(defaultStyle);
    registerStyle(STYLE_STANDARD_NAME + ROW_ODD_NAME, styleOdd);

    CellStyle styleEven = cloneStyle(styleOdd);
    setStyleFillForegroundColor(styleEven, colorRegistry, EVEN_ROW_BACKGROUND_COLOR_INDEX);
    registerStyle(STYLE_STANDARD_NAME + ROW_EVEN_NAME, styleEven);

    // styles pour les nombres entiers
    short integerFormatIndex = dataFormat.getFormat(integerDataFormat);

    CellStyle styleOddInteger = cloneStyle(styleOdd);
    styleOddInteger.setAlignment(CellStyle.ALIGN_RIGHT);
    styleOddInteger.setDataFormat(integerFormatIndex);
    registerStyle(STYLE_INTEGER_NAME + ROW_ODD_NAME, styleOddInteger);

    CellStyle styleEvenInteger = cloneStyle(styleEven);
    styleEvenInteger.setAlignment(CellStyle.ALIGN_RIGHT);
    styleEvenInteger.setDataFormat(integerFormatIndex);
    registerStyle(STYLE_INTEGER_NAME + ROW_EVEN_NAME, styleEvenInteger);

    // styles pour les nombres dcimaux
    short decimalFormatIndex = dataFormat.getFormat(decimalDataFormat);

    CellStyle styleOddDecimal = cloneStyle(styleOdd);
    styleOddDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
    styleOddDecimal.setDataFormat(decimalFormatIndex);
    registerStyle(STYLE_DECIMAL_NAME + ROW_ODD_NAME, styleOddDecimal);

    CellStyle styleEvenDecimal = cloneStyle(styleEven);
    styleEvenDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
    styleEvenDecimal.setDataFormat(decimalFormatIndex);
    registerStyle(STYLE_DECIMAL_NAME + ROW_EVEN_NAME, styleEvenDecimal);

    // styles pour les dates
    short dateFormatIndex = dataFormat.getFormat(dateDataFormat);

    CellStyle styleOddDate = cloneStyle(styleOdd);
    styleOddDate.setDataFormat(dateFormatIndex);
    registerStyle(STYLE_DATE_NAME + ROW_ODD_NAME, styleOddDate);

    CellStyle styleEvenDate = cloneStyle(styleEven);
    styleEvenDate.setDataFormat(dateFormatIndex);
    registerStyle(STYLE_DATE_NAME + ROW_EVEN_NAME, styleEvenDate);

    // styles pour les dates avec heure
    short dateTimeFormatIndex = dataFormat.getFormat(dateTimeDataFormat);

    CellStyle styleOddDateTime = cloneStyle(styleOdd);
    styleOddDateTime.setDataFormat(dateTimeFormatIndex);
    registerStyle(STYLE_DATE_TIME_NAME + ROW_ODD_NAME, styleOddDateTime);

    CellStyle styleEvenDateTime = cloneStyle(styleEven);
    styleEvenDateTime.setDataFormat(dateTimeFormatIndex);
    registerStyle(STYLE_DATE_TIME_NAME + ROW_EVEN_NAME, styleEvenDateTime);

    // styles pour les pourcentages
    short percentFormatIndex = dataFormat.getFormat(percentDataFormat);

    CellStyle styleOddPercent = cloneStyle(styleOdd);
    styleOddPercent.setDataFormat(percentFormatIndex);
    registerStyle(STYLE_PERCENT_NAME + ROW_ODD_NAME, styleOddPercent);

    CellStyle styleEvenPercent = cloneStyle(styleEven);
    styleEvenPercent.setDataFormat(percentFormatIndex);
    registerStyle(STYLE_PERCENT_NAME + ROW_EVEN_NAME, styleEvenPercent);

    short percentRelativeFormatIndex = dataFormat.getFormat(percentRelativeDataFormat);

    CellStyle styleOddPercentRelative = cloneStyle(styleOdd);
    styleOddPercentRelative.setDataFormat(percentRelativeFormatIndex);
    registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_ODD_NAME, styleOddPercentRelative);

    CellStyle styleEvenPercentRelative = cloneStyle(styleEven);
    styleEvenPercentRelative.setDataFormat(percentRelativeFormatIndex);
    registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_EVEN_NAME, styleEvenPercentRelative);

    // styles pour les liens
    CellStyle styleOddLink = cloneStyle(styleOdd);
    styleOddLink.setFont(getFont(FONT_LINK_NAME));
    registerStyle(STYLE_LINK_NAME + ROW_ODD_NAME, styleOddLink);

    CellStyle styleEvenLink = cloneStyle(styleEven);
    styleEvenLink.setFont(getFont(FONT_LINK_NAME));
    registerStyle(STYLE_LINK_NAME + ROW_EVEN_NAME, styleEvenLink);

    // styles pour les tailles de fichiers
    short fileSizeFormatIndex = dataFormat.getFormat(fileSizeDataFormat);

    CellStyle styleOddFileSize = cloneStyle(styleOdd);
    styleOddFileSize.setDataFormat(fileSizeFormatIndex);
    registerStyle(STYLE_FILE_SIZE_NAME + ROW_ODD_NAME, styleOddFileSize);

    CellStyle styleEvenFileSize = cloneStyle(styleEven);
    styleEvenFileSize.setDataFormat(fileSizeFormatIndex);
    registerStyle(STYLE_FILE_SIZE_NAME + ROW_EVEN_NAME, styleEvenFileSize);
}

From source file:fsi_admin.reportes.JReportesDlg.java

License:Open Source License

public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb)
        throws ServletException, IOException {
    JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet");
    JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1");
    JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1");
    Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1");
    Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2");
    Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3");
    Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1");
    Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2");
    Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3");
    JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1");
    JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2");
    JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3");
    JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1");
    JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2");
    JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3");

    String fsi_filtro = (String) request.getAttribute("fsi_filtro");

    Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report()));

    short nrow = 0;
    Row row = sheet.createRow(nrow++);//from  w w w  . j a v  a2s .co  m
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    Cell cell = row.createCell(0);
    cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    JRepCellStyles cellStyles = new JRepCellStyles(wb);

    if (m_bSelectL1.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL1.getNumRows(); i++) {
            if (m_colL1.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL2.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL2.getNumRows(); i++) {
            if (m_colL2.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL3.booleanValue()) {
        Row rowl = sheet.createRow(nrow++);
        for (int i = 0; i < m_colL3.getNumRows(); i++) {
            if (m_colL3.getAbsRow(i).getWillShow()) {
                JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName(), "general", "STRING",
                        m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request);
                //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN);
                //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN);
            }
        }
    }

    if (m_bSelectL1.booleanValue()) {
        for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) {
            Row rowl1 = sheet.createRow(nrow++);
            for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) {
                if (m_colL1.getAbsRow(CL1).getWillShow())
                    JUtil.DatoXLS(cellStyles, rowl1, CL1,
                            m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()),
                            m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(),
                            m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request);
            }

            // Nivel 2
            if (m_bSelectL2.booleanValue()) {
                for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) {
                    Row rowl2 = sheet.createRow(nrow++);
                    for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) {
                        if (m_colL2.getAbsRow(CL2).getWillShow())
                            JUtil.DatoXLS(cellStyles, rowl2, CL2,
                                    m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2)
                                            .getSTS(m_colL2.getAbsRow(CL2).getColName()),
                                    m_colL2.getAbsRow(CL2).getFormat(),
                                    m_colL2.getAbsRow(CL2).getBindDataType(),
                                    m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request);
                    }

                    // Nivel 3
                    if (m_bSelectL3.booleanValue()) {
                        for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                .getNumRows(); RL3++) {
                            Row rowl3 = sheet.createRow(nrow++);
                            for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) {
                                if (m_colL3.getAbsRow(CL3).getWillShow())
                                    JUtil.DatoXLS(cellStyles, rowl3, CL3,
                                            m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3()
                                                    .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()),
                                            m_colL3.getAbsRow(CL3).getFormat(),
                                            m_colL3.getAbsRow(CL3).getBindDataType(),
                                            m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request);

                            }

                        }
                        if (m_bComputeL3.booleanValue()) {
                            for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                    .getNumRows(); RC3++) {
                                Row rowc3 = sheet.createRow(nrow++);
                                for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) {
                                    if (m_colCL3.getAbsRow(CC3).getWillShow())
                                        JUtil.DatoXLS(cellStyles, rowc3, CC3,
                                                m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3()
                                                        .getAbsRow(RC3)
                                                        .getSTS(m_colCL3.getAbsRow(CC3).getColName()),
                                                m_colCL3.getAbsRow(CC3).getFormat(),
                                                m_colCL3.getAbsRow(CC3).getBindDataType(),
                                                m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc",
                                                request);
                                }
                            }
                        } // Fin SI CL3
                    } // Fin SI L3
                }
                if (m_bComputeL2.booleanValue()) {
                    for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) {
                        Row rowc2 = sheet.createRow(nrow++);
                        for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) {
                            if (m_colCL2.getAbsRow(CC2).getWillShow())
                                JUtil.DatoXLS(cellStyles, rowc2, CC2,
                                        m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2)
                                                .getSTS(m_colCL2.getAbsRow(CC2).getColName()),
                                        m_colCL2.getAbsRow(CC2).getFormat(),
                                        m_colCL2.getAbsRow(CC2).getBindDataType(),
                                        m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request);
                        }
                    }
                } // Fin SI CL2
            } // Fin SI L2
        }

        if (m_bComputeL1.booleanValue()) {
            for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) {
                Row rowc1 = sheet.createRow(nrow++);
                for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) {
                    if (m_colCL1.getAbsRow(CC1).getWillShow())
                        JUtil.DatoXLS(cellStyles, rowc1, CC1,
                                m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()),
                                m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(),
                                m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request);
                }
            }
        } // Fin SI CL1
    } // Fin SI L1

    int colsmer;
    if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL1.getNumRows() - 1;
    else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows())
        colsmer = m_colL2.getNumRows() - 1;
    else
        colsmer = m_colL3.getNumRows() - 1;

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer)));

}

From source file:GapAnalysis.gapAnalysis.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    //PrintWriter out = response.getWriter();
    try {/*from  ww  w .  j a v  a  2s .  c om*/

        ArrayList keyal = new ArrayList();
        ArrayList countyal = new ArrayList();
        ArrayList scountyal = new ArrayList();
        ArrayList facilal = new ArrayList();
        ArrayList yearmonthal = new ArrayList();
        ArrayList monthal = new ArrayList();
        ArrayList sectional = new ArrayList();

        XSSFWorkbook wb;

        String periodname = "";
        String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm");

        System.out.println(allpath);

        XSSFWorkbook workbook;
        String mydrive = allpath.substring(0, 1);
        // wb = new XSSFWorkbook( OPCPackage.open(allpath) );

        Date da = new Date();
        String dat2 = da.toString().replace(" ", "_");
        dat2 = dat2.toString().replace(":", "_");

        String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm";
        System.out.println("path:: " + np);
        // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm");
        String sr = getServletContext().getRealPath("/Gapanalysis.xlsm");
        //check if file exists

        //first time , it should create those folders that host the macro file
        File f = new File(np);
        if (!f.exists() && !f.isDirectory()) { /* do something */
            copytemplates ct = new copytemplates();
            ct.transfermacros(sr, np);
            //rem np is the destination file name  

            System.out.println("Copying macro template first time ..");

        } else
        //copy the file alone  
        {
            copytemplates ct = new copytemplates();
            //copy the agebased file only
            ct.copymacros(sr, np);

        }
        String filepth = np;

        File allpathfile = new File(filepth);

        OPCPackage pkg = OPCPackage.open(allpathfile);

        pathtodelete = filepth;
        wb = new XSSFWorkbook(pkg);

        dbConn conn = new dbConn();
        HashMap<String, String> rawdatahashmap = new HashMap<String, String>();

        int year = 0;
        String yearval = "";
        int prevyear = 0;

        String quarter = "";

        String yearmonth = "";
        String startyearmonth = "";
        String endyearmonth = "";

        yearval = request.getParameter("year").toString();

        System.out.println("YEARVAL" + yearval);
        year = Integer.parseInt(yearval);
        prevyear = year - 1;
        quarter = request.getParameter("quarter");
        periodname += yearval + "_";
        if (quarter.equals("1")) {
            startyearmonth = prevyear + "10";
            endyearmonth = prevyear + "12";
            periodname = prevyear + "_(Oct_Dec)";
        } else if (quarter.equals("2")) {
            startyearmonth = year + "01";
            endyearmonth = year + "03";
            periodname = yearval + "_(Jan-Mar)";
        } else if (quarter.equals("3")) {
            startyearmonth = year + "04";
            endyearmonth = year + "06";
            periodname = yearval + "_(Apr_Jun)";
        } else if (quarter.equals("4")) {
            startyearmonth = year + "07";
            endyearmonth = year + "09";
            periodname = yearval + "_(Jul_Sep)";
        }

        int colsmerging = 6;
        String Sections[] = { "ART", "HTC", "PMTCT" };
        String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" };
        String headergsn[] = { "County", "Sub-County", "Facility" };
        //if one wants gaps for one service area
        if (request.getParameterValues("gapsection") != null) {

            Sections = request.getParameterValues("gapsection");

        }
        //This is the loop that well use to create worksheets for each 

        String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and "
                + endyearmonth + " ";
        String gsnperiod = " 1=1  ";

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 18);
        font.setFontName("Cambria");
        font.setColor((short) 0000);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font font2 = wb.createFont();
        font2.setFontName("Cambria");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        CellStyle stborder = wb.createCellStyle();
        stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex1 = wb.createCellStyle();
        stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        CellStyle stylex2 = wb.createCellStyle();
        stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        CellStyle stylex3 = wb.createCellStyle();
        stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        Font fontx = wb.createFont();
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Cambria");
        stylex.setFont(fontx);
        stylex.setWrapText(true);
        stylex1.setFont(fontx);
        stylex1.setWrapText(true);

        stylex2.setFont(fontx);
        stylex2.setWrapText(true);

        //==================================================

        for (int a = 0; a < Sections.length; a++) {
            int column = 0;
            int Row = 3;

            Sheet shet = wb.createSheet(Sections[a]);

            Row rwx = shet.createRow(2);
            Row rw1 = null;
            Row rw2 = null;
            Row rw = shet.createRow(0);
            rw.setHeightInPoints(25);
            Cell cl0 = rw.createCell(0);
            cl0.setCellValue(Sections[a] + " GAP ANALYSIS");
            cl0.setCellStyle(stylex1);

            //this will depend on the length of the number of elements being checked

            for (int b = 1; b <= colsmerging; b++) {
                Cell clx = rw.createCell(b);
                clx.setCellValue("");
                clx.setCellStyle(stylex);
            }

            //now go to the database and do a query for each section
            int determinant = 2;
            String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' ";

            conn.rs = conn.st.executeQuery(getqueries);
            while (conn.rs.next()) {

                //if an excel sheet exists, then get the row number 1

                if (shet.getRow(1) != null) {
                    rw1 = shet.getRow(1);
                } else {
                    rw1 = shet.createRow(1);
                    rw1.setHeightInPoints(25);
                }

                //print blanks before printing real header
                //for gsns, we only print three columns and no period
                if (conn.rs.getString("id").equals("1")) {

                    for (int p = 0; p < headergsn.length; p++) {
                        Cell cl2 = rw1.createCell(column + p);
                        cl2.setCellValue("");
                        cl2.setCellStyle(stylex);
                        shet.setColumnWidth(column + p, 5000);
                    }
                } else {

                    for (int p = 0; p < headers.length; p++) {
                        Cell cl2 = rw1.createCell(column + p);
                        cl2.setCellValue("");
                        cl2.setCellStyle(stylex);
                        shet.setColumnWidth(column + p, 5000);
                    }

                }
                determinant++;
                if (determinant % 2 == 0) {

                    Cell cl1 = rw1.createCell(column);
                    cl1.setCellValue(conn.rs.getString("rule"));
                    cl1.setCellStyle(stylex3);

                } else {

                    Cell cl1 = rw1.createCell(column);
                    cl1.setCellValue(conn.rs.getString("rule"));
                    cl1.setCellStyle(stylex2);

                }

                //Create the column header  

                if (shet.getRow(2) != null) {
                    rw2 = shet.getRow(2);
                } else {
                    rw2 = shet.createRow(2);
                    rw2.setHeightInPoints(25);
                }
                if (conn.rs.getString("id").equals("1")) {

                    for (int p = 0; p < headergsn.length; p++) {
                        Cell cl2 = rw2.createCell(column + p);
                        cl2.setCellValue(headergsn[p]);
                        cl2.setCellStyle(stylex);
                    }
                } else {

                    for (int p = 0; p < headers.length; p++) {
                        Cell cl2 = rw2.createCell(column + p);
                        cl2.setCellValue(headers[p]);
                        cl2.setCellStyle(stylex);
                    }

                }

                String currentqry = conn.rs.getString("query");
                //process each query as you 
                //pass the necessary period parameters from the interface
                //rem each query ends with a 'and'
                if (conn.rs.getString("id").equals("1")) {
                    currentqry += gsnperiod;
                } else {

                    currentqry += period + " and subpartnera." + Sections[a] + "= 1 ";

                }

                System.out.println("" + currentqry);
                Row = 3;
                conn.rs1 = conn.st1.executeQuery(currentqry);

                while (conn.rs1.next()) {

                    if (shet.getRow(Row) != null) {
                        rwx = shet.getRow(Row);
                    } else {
                        rwx = shet.createRow(Row);
                        rwx.setHeightInPoints(25);
                    }
                    Cell cly = rwx.createCell(column);
                    cly.setCellValue(conn.rs1.getString("County"));
                    cly.setCellStyle(style2);

                    Cell cly2 = rwx.createCell(column + 1);
                    cly2.setCellValue(conn.rs1.getString("DistrictNom"));
                    cly2.setCellStyle(style2);//gsn sites do not have a yearmonth

                    Cell cly1 = rwx.createCell(column + 2);
                    cly1.setCellValue(conn.rs1.getString("SubPartnerNom"));
                    cly1.setCellStyle(style2);

                    //if the current list is not inclusive of GSNs

                    if (!conn.rs.getString(1).equals("1")) {

                        Cell cly3 = rwx.createCell(column + 3);
                        cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4)));
                        cly3.setCellStyle(style2);

                        //the month section

                        Cell cly3x = rwx.createCell(column + 4);
                        cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4)));
                        cly3x.setCellStyle(style2);

                        //my key is a 
                        String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_"
                                + conn.rs1.getString("yearmonth") + "_";
                        //add all the facilities at this point
                        //ignore the sites in ART since they are static
                        if (!keyal.contains(mykey)) {
                            keyal.add(mykey);
                            countyal.add(conn.rs1.getString("County"));
                            scountyal.add(conn.rs1.getString("DistrictNom"));
                            facilal.add(conn.rs1.getString("SubPartnerNom"));
                            sectional.add(Sections[a]);
                            yearmonthal.add(conn.rs1.getString("yearmonth"));
                            monthal.add(conn.rs1.getString("yearmonth").substring(4));

                        }

                    }

                    Row++;

                }

                if (conn.rs.getString(1).equals("1")) {
                    column += 3;
                } else {
                    column += 5;
                }
                if (conn.rs.getString("id").equals("1")) {
                    shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1));
                } else {
                    shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1));
                }

            } //end of all queries per section

            shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));

        } // end of sheets loop   

        //create a new sheet

        //county   subcounty   facility   yearmonth   section

        Sheet shet = wb.getSheet("Sheet1");

        Row rw = shet.createRow(0);
        Cell cl0 = rw.createCell(0);
        cl0.setCellValue("county");
        cl0.setCellStyle(stylex1);

        Cell cl1 = rw.createCell(1);
        cl1.setCellValue("subcounty");
        cl1.setCellStyle(stylex1);

        Cell cl2 = rw.createCell(2);
        cl2.setCellValue("facility");
        cl2.setCellStyle(stylex1);

        Cell cl3 = rw.createCell(3);
        cl3.setCellValue("year");
        cl3.setCellStyle(stylex1);

        Cell cl4 = rw.createCell(4);
        cl4.setCellValue("month");
        cl4.setCellStyle(stylex1);

        Cell cl5 = rw.createCell(5);
        cl5.setCellValue("section");
        cl5.setCellStyle(stylex1);

        for (int q = 0; q < keyal.size(); q++) {

            Row rwx = shet.createRow(q + 1);

            Cell cl01 = rwx.createCell(0);
            cl01.setCellValue(countyal.get(q).toString());
            cl01.setCellStyle(style2);

            Cell cl11 = rwx.createCell(1);
            cl11.setCellValue(scountyal.get(q).toString());
            cl11.setCellStyle(style2);

            Cell cl21 = rwx.createCell(2);
            cl21.setCellValue(facilal.get(q).toString());
            cl21.setCellStyle(style2);

            Cell cl31 = rwx.createCell(3);
            cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4)));
            cl31.setCellStyle(style2);

            Cell cl41 = rwx.createCell(4);
            cl41.setCellValue(new Integer(monthal.get(q).toString()));
            cl41.setCellStyle(style2);

            Cell cl51 = rwx.createCell(5);
            cl51.setCellValue(sectional.get(q).toString());
            cl51.setCellStyle(style2);

        }

        IdGenerator IG = new IdGenerator();
        String createdOn = IG.CreatedOn();

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
        outStream.close();
        pkg.close();

        if (conn.rs != null) {
            conn.rs.close();
        }
        if (conn.rs1 != null) {
            conn.rs1.close();
        }
        if (conn.st1 != null) {
            conn.st1.close();
        }
        if (conn.st != null) {
            conn.st.close();
        }

        File file = new File(pathtodelete);
        System.out.println("path: 2" + pathtodelete);

        if (file.delete()) {
            System.out.println(file.getName() + " is deleted!");
        } else {
            System.out.println("Delete operation  failed.");
        }

    } catch (SQLException ex) {
        Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex);
    } finally {

    }
}

From source file:gov.nih.nci.cadsr.cdecurate.test.TestSpreadsheetDownload.java

License:BSD License

private void createDownloadColumns(ArrayList<String[]> allRows) {
    final int MAX_ROWS = 65000;

    String sheetName = "Custom Download";
    int sheetNum = 1;
    String fillIn = "false";// set true to fill in all values.
    String[] columns = null;/*w w  w .ja v a2s  .c  om*/

    ArrayList<String> defaultHeaders = new ArrayList<String>();

    for (String cName : allExpandedColumnHeaders) {
        if (cName.endsWith("IDSEQ") || cName.startsWith("CD ")
                || cName.startsWith("Conceptual Domain")) { /* skip */
        } else {
            System.out.println("cName = " + cName);
            defaultHeaders.add(cName);
        }
    }
    columns = defaultHeaders.toArray(new String[defaultHeaders.size()]);

    int[] colIndices = new int[columns.length];
    for (int i = 0; i < columns.length; i++) {
        String colName = columns[i];
        if (columnHeaders.indexOf(colName) < 0) {
            String tempType = arrayColumnTypes.get(colName);
            int temp = columnTypes.indexOf(tempType);
            colIndices[i] = temp;
        } else {
            int temp = columnHeaders.indexOf(colName);
            colIndices[i] = temp;
        }
    }

    Workbook wb = new HSSFWorkbook();

    Sheet sheet = wb.createSheet(sheetName);
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle boldCellStyle = wb.createCellStyle();
    boldCellStyle.setFont(font);
    boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL);

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    String temp;
    for (int i = 0; i < columns.length; i++) {
        Cell cell = headerRow.createCell(i);
        temp = columns[i];
        cell.setCellValue(temp);
        cell.setCellStyle(boldCellStyle);
    }

    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row = null;
    Cell cell;
    int rownum = 1;
    int bump = 0;
    int i = 0;
    try {
        System.out.println("Total CDEs to download [" + allRows.size() + "]");
        for (i = 0; i < allRows.size(); i++, rownum++) {
            // Check if row already exists
            int maxBump = 0;
            if (sheet.getRow(rownum + bump) == null) {
                row = sheet.createRow(rownum + bump);
            }

            if (allRows.get(i) == null)
                continue;

            for (int j = 0; j < colIndices.length; j++) {

                cell = row.createCell(j);
                String currentType = columnTypes.get(colIndices[j]);
                if (currentType.endsWith("_T")) {
                    // Deal with CS/CSI
                    String[] originalArrColNames = typeMap.get(currentType).get(0);

                    // Find current column in original data

                    int originalColumnIndex = -1;
                    for (int a = 0; a < originalArrColNames.length; a++) {
                        if (columns[j].equals(originalArrColNames[a])) {
                            originalColumnIndex = a;
                            break;
                        }
                    }
                    // ArrayList<HashMap<String,ArrayList<String[]>>>
                    // arrayData1 =
                    // (ArrayList<HashMap<String,ArrayList<String[]>>>)arrayData;
                    HashMap<String, List<String[]>> typeArrayData = arrayData.get(i);
                    ArrayList<String[]> rowArrayData = (ArrayList<String[]>) typeArrayData.get(currentType);

                    if (rowArrayData != null) {
                        int tempBump = 0;
                        for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) {

                            String[] nestedData = rowArrayData.get(nestedRowIndex);
                            String data = "";
                            if (currentType.contains("DERIVED")) {
                                // Derived data element is special double
                                // nested, needs to be modified to be more
                                // general.

                                // General DDE information is in the first 4
                                // columns, but contained in the first row
                                // of the Row Array Data
                                if (originalColumnIndex < 5) {
                                    if (nestedRowIndex == 0)
                                        data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex]
                                                : nestedData[originalColumnIndex + 1];
                                } else {
                                    if (nestedRowIndex + 1 < rowArrayData.size()) {
                                        data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5];
                                    }
                                }

                            } else
                                data = nestedData[originalColumnIndex];
                            logger.debug(
                                    "at line 828 of TestSpreadsheetDownload.java*****" + data + currentType);
                            if (currentType.contains("VALID_VALUE")) {
                                data = AdministeredItemUtil.truncateTime(data);
                            }
                            cell.setCellValue(data);

                            tempBump++;

                            if (nestedRowIndex < rowArrayData.size() - 1) {
                                row = sheet.getRow(rownum + bump + tempBump);
                                if (row == null) {
                                    if (rownum + bump + tempBump >= MAX_ROWS) {
                                        sheet = wb.createSheet(sheetName + "_" + sheetNum);
                                        sheetNum++;
                                        rownum = 1;
                                        bump = 0;
                                        tempBump = 0;
                                    }
                                    row = sheet.createRow(rownum + bump + tempBump);
                                }

                                cell = row.createCell(j);

                            } else {
                                // Go back to top row
                                row = sheet.getRow(rownum + bump);
                                if (tempBump > maxBump)
                                    maxBump = tempBump;
                            }
                        }
                    }
                } else {
                    temp = allRows.get(i)[colIndices[j]];
                    logger.debug("at line 866 of TestSpreadsheetDownload.java*****" + temp + currentType);
                    if (currentType.equalsIgnoreCase("Date")) {
                        temp = AdministeredItemUtil.truncateTime(temp);
                    }
                    cell.setCellValue(temp);
                }

            }

            bump = bump + maxBump;

            if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) {
                sheet = fillInBump(sheet, i, rownum, bump, allRows, columnTypes, colIndices);
                rownum = rownum + bump;
                bump = 0;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        // Please specify the path below if needed, otherwise it will create in the root/dir where this test class is run
        fileOutputStream = new FileOutputStream("Test_Excel.xls");
        wb.write(fileOutputStream);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        /**
         * Close the fileOutputStream.
         */
        try {
            if (fileOutputStream != null) {
                fileOutputStream.close();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}

From source file:gov.nih.nci.cadsr.cdecurate.tool.CustomDownloadServlet.java

License:BSD License

private void createDownloadColumns(ArrayList<String[]> allRows) {
    final int MAX_ROWS = 65000;

    String sheetName = "Custom Download";
    int sheetNum = 1;

    String colString = (String) this.m_classReq.getParameter("cdlColumns");
    String fillIn = (String) this.m_classReq.getParameter("fillIn");

    ArrayList<String> allHeaders = (ArrayList<String>) m_classReq.getSession().getAttribute("headers");
    ArrayList<String> allExpandedHeaders = (ArrayList<String>) m_classReq.getSession()
            .getAttribute("allExpandedHeaders");
    ArrayList<String> allTypes = (ArrayList<String>) m_classReq.getSession().getAttribute("types");
    HashMap<String, ArrayList<String[]>> typeMap = (HashMap<String, ArrayList<String[]>>) m_classReq
            .getSession().getAttribute("typeMap");
    ArrayList<HashMap<String, ArrayList<String[]>>> arrayData = (ArrayList<HashMap<String, ArrayList<String[]>>>) m_classReq
            .getSession().getAttribute("arrayData");
    HashMap<String, String> arrayColumnTypes = (HashMap<String, String>) m_classReq.getSession()
            .getAttribute("arrayColumnTypes");

    String[] columns = null;/* ww w . j a v a 2  s . c om*/
    if (colString != null && !colString.trim().equals("")) {
        columns = colString.split(",");
    } else {
        ArrayList<String> defaultHeaders = new ArrayList<String>();

        for (String cName : allExpandedHeaders) {
            if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) {
                /*skip*/ } else {
                System.out.println("cName = " + cName);
                defaultHeaders.add(cName);
            }
        }
        columns = defaultHeaders.toArray(new String[defaultHeaders.size()]);

    }

    int[] colIndices = new int[columns.length];
    for (int i = 0; i < columns.length; i++) {
        String colName = columns[i];
        if (allHeaders.indexOf(colName) < 0) {
            String tempType = arrayColumnTypes.get(colName);
            int temp = allTypes.indexOf(tempType);
            colIndices[i] = temp;
        } else {
            int temp = allHeaders.indexOf(colName);
            colIndices[i] = temp;
        }
    }

    Workbook wb = new HSSFWorkbook();

    Sheet sheet = wb.createSheet(sheetName);
    Font font = wb.createFont(); //GF30779
    font.setBoldweight(Font.BOLDWEIGHT_BOLD); //GF30779
    CellStyle boldCellStyle = wb.createCellStyle(); //GF30779
    boldCellStyle.setFont(font); //GF30779
    boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); //GF30779

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    String temp;
    for (int i = 0; i < columns.length; i++) {
        Cell cell = headerRow.createCell(i);
        temp = columns[i];
        cell.setCellValue(temp);
        cell.setCellStyle(boldCellStyle); //GF30779
    }

    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row = null;
    Cell cell;
    int rownum = 1;
    int bump = 0;
    boolean fillRow = false;
    int i = 0;
    long startTime = System.currentTimeMillis();
    try {
        System.out.println("Total CDEs to download [" + allRows.size() + "]");
        for (i = 0; i < allRows.size(); i++, rownum++) {
            //Check if row already exists
            int maxBump = 0;
            if (sheet.getRow(rownum + bump) == null) {
                row = sheet.createRow(rownum + bump);
            }

            if (allRows.get(i) == null)
                continue;

            for (int j = 0; j < colIndices.length; j++) {

                cell = row.createCell(j);
                String currentType = allTypes.get(colIndices[j]);
                if (currentType.endsWith("_T")) {
                    //Deal with CS/CSI
                    String[] originalArrColNames = typeMap.get(currentType).get(0);

                    //Find current column in original data

                    int originalColumnIndex = -1;
                    for (int a = 0; a < originalArrColNames.length; a++) {
                        if (columns[j].equals(originalArrColNames[a])) {
                            originalColumnIndex = a;
                            break;
                        }
                    }

                    HashMap<String, ArrayList<String[]>> typeArrayData = arrayData.get(i);
                    ArrayList<String[]> rowArrayData = typeArrayData.get(currentType);

                    if (rowArrayData != null) {
                        int tempBump = 0;
                        for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) {

                            String[] nestedData = rowArrayData.get(nestedRowIndex);
                            String data = "";
                            if (currentType.contains("DERIVED")) {
                                //Derived data element is special double nested, needs to be modified to be more general.

                                //General DDE information is in the first 4 columns, but contained in the first row of the Row Array Data
                                if (originalColumnIndex < 5) {
                                    if (nestedRowIndex == 0)
                                        data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex]
                                                : nestedData[originalColumnIndex + 1]; //This skips the 2nd entry, description, which is not to be shown.
                                } else {
                                    if (nestedRowIndex + 1 < rowArrayData.size()) {
                                        data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5];
                                    }
                                }

                            } else
                                data = nestedData[originalColumnIndex];
                            logger.debug("at line 960 of CustomDownloadServlet.java*****" + data + currentType);
                            if (currentType.contains("VALID_VALUE")) { //GF30779
                                data = AdministeredItemUtil.truncateTime(data);
                            }
                            cell.setCellValue(data);

                            tempBump++;

                            if (nestedRowIndex < rowArrayData.size() - 1) {
                                row = sheet.getRow(rownum + bump + tempBump);
                                if (row == null) {
                                    if (rownum + bump + tempBump >= MAX_ROWS) {
                                        sheet = wb.createSheet(sheetName + "_" + sheetNum);
                                        sheetNum++;
                                        rownum = 1;
                                        bump = 0;
                                        tempBump = 0;
                                    }
                                    row = sheet.createRow(rownum + bump + tempBump);
                                }

                                cell = row.createCell(j);

                            } else {
                                //Go back to top row 
                                row = sheet.getRow(rownum + bump);
                                if (tempBump > maxBump)
                                    maxBump = tempBump;
                            }
                        }
                    }
                } else {
                    temp = allRows.get(i)[colIndices[j]];
                    logger.debug("at line 993 of CustomDownloadServlet.java*****" + temp + currentType);
                    if (currentType.equalsIgnoreCase("Date")) { //GF30779
                        temp = AdministeredItemUtil.truncateTime(temp);
                    }
                    cell.setCellValue(temp);
                }

            }

            bump = bump + maxBump;

            if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) {
                sheet = fillInBump(sheet, i, rownum, bump, allRows, allTypes, colIndices);
                rownum = rownum + bump;
                bump = 0;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    //      sheet.setZoom(3, 4); //GF30779

    // Write the output to response stream.
    try {
        m_classRes.setContentType("application/vnd.ms-excel");
        m_classRes.setHeader("Content-Disposition", "attachment; filename=\"customDownload.xls\"");

        OutputStream out = m_classRes.getOutputStream();
        wb.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:htmlparser.xls.XLSFile.java

public void createScoreTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color,
        int highlight) {

    String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getCompetitionName());
    this.scoresheet = this.excelfile.createSheet(sheetname);

    CreationHelper createHelper = this.excelfile.getCreationHelper();

    CellStyle cellStyle = this.excelfile.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

    int rows = 0;

    Row headline = this.scoresheet.createRow(rows);
    Cell cheadline = headline.createCell(0);
    cheadline.setCellValue(createHelper.createRichTextString(this.parser.getCompetitionName()));
    XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    XSSFFont fh = (XSSFFont) this.excelfile.createFont();
    fh.setFontHeightInPoints((short) 16);
    fh.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fh.setColor(title_bg_color);// w w w  . j av a 2  s. c  o m
    customstyle.setFont(fh);
    cheadline.setCellStyle(customstyle);
    int length = this.parser.getTeams().get(0).getData().size();
    CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length);
    this.scoresheet.addMergedRegion(headrow);
    RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile);
    RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile);
    RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile);
    RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile);
    RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile);
    RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile);
    RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile);
    RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile);
    rows++;

    Row colNms = this.scoresheet.createRow(rows++);
    customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    customstyle.setFillForegroundColor(title_bg_color);
    customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    XSSFFont f1 = (XSSFFont) this.excelfile.createFont();
    f1.setColor(title_font_color);
    f1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    customstyle.setFont(f1);
    int cCN = 0;
    for (String s : this.shColNms) {

        Cell c = colNms.createCell(cCN);
        c.setCellValue(createHelper.createRichTextString(s));
        c.setCellStyle(customstyle);
        cCN++;

    }

    double ordNum = 1;

    customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    customstyle.setFillForegroundColor(oddrow_color);
    customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    for (Team t : this.parser.getTeams()) {

        Row r = this.scoresheet.createRow(rows++);

        int cell = 0;

        Cell order = r.createCell(cell++);
        order.setCellValue(ordNum++);
        if (rows % 2 == 0)
            order.setCellStyle(customstyle);
        else
            order.setCellStyle(cellStyle);

        for (String s : t.getData()) {

            Cell c = r.createCell(cell);

            c.setCellValue(createHelper.createRichTextString(s));

            if (rows % 2 == 0)
                c.setCellStyle(customstyle);
            else
                c.setCellStyle(cellStyle);

            cell++;

        }

    }

    for (int i = 0; i <= length; i++) {

        this.scoresheet.autoSizeColumn(i);

    }

    if (highlight >= 0) {

        highlight += 2;

        Row r = this.scoresheet.getRow(highlight);
        customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
        customstyle.cloneStyleFrom(r.getCell(0).getCellStyle());
        Font bold = this.excelfile.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        customstyle.setFont(bold);

        for (Cell c : r) {
            c.setCellStyle(customstyle);
        }
    }

}

From source file:htmlparser.xls.XLSFile.java

public void createMatchTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color) {

    String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getTeamName());
    this.matchsheet = this.excelfile.createSheet(sheetname);

    CreationHelper createHelper = this.excelfile.getCreationHelper();

    CellStyle cellStyle = this.excelfile.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

    int rows = 0;

    Row headline = this.matchsheet.createRow(rows);
    Cell cheadline = headline.createCell(0);
    cheadline.setCellValue(createHelper.createRichTextString(this.parser.getTeamName()));
    XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    XSSFFont fh = (XSSFFont) this.excelfile.createFont();
    fh.setFontHeightInPoints((short) 16);
    fh.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fh.setColor(title_bg_color);/*from w ww.  j  ava2  s  .c  o  m*/
    customstyle.setFont(fh);
    cheadline.setCellStyle(customstyle);
    int length = this.parser.getMatches().get(0).getData().size();
    CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length - 1);
    this.matchsheet.addMergedRegion(headrow);
    RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile);
    RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile);
    RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile);
    RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile);
    RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile);
    RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile);
    RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.matchsheet, this.excelfile);
    RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.matchsheet, this.excelfile);
    rows++;

    Row colNms = this.matchsheet.createRow(rows++);
    customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    customstyle.setFillForegroundColor(title_bg_color);
    customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    XSSFFont f1 = (XSSFFont) this.excelfile.createFont();
    f1.setColor(title_font_color);
    f1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    customstyle.setFont(f1);
    int cCN = 0;
    for (String s : this.mhColNms) {

        Cell c = colNms.createCell(cCN);
        c.setCellValue(createHelper.createRichTextString(s));
        c.setCellStyle(customstyle);
        cCN++;

    }

    customstyle = (XSSFCellStyle) this.excelfile.createCellStyle();
    customstyle.cloneStyleFrom(cellStyle);
    customstyle.setFillForegroundColor(oddrow_color);
    customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    for (Match t : this.parser.getMatches()) {

        Row r = this.matchsheet.createRow(rows++);

        int cell = 0;

        for (String s : t.getData()) {

            Cell c = r.createCell(cell);

            c.setCellValue(createHelper.createRichTextString(s));

            if (rows % 2 == 0)
                c.setCellStyle(customstyle);
            else
                c.setCellStyle(cellStyle);

            cell++;

        }

    }

    for (int i = 0; i < length; i++) {

        this.matchsheet.autoSizeColumn(i);

    }

}

From source file:io.vulpine.lib.kalo.PoiConfig.java

License:Apache License

public CellStyle getHeaderStyle(final Workbook book, final Poi poi) {
    final CellStyle style = book.createCellStyle();
    final Font font = book.createFont();

    font.setFontName("Arial");
    font.setBold(true);/*w  w  w. j  a v  a 2s  .c om*/
    font.setFontHeightInPoints((short) 12);

    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(font);

    return style;
}