Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:com.sec.ose.osi.report.standard.SummarySheetTemplate.java

License:Open Source License

/**
 * write summary info//  w  ww .j a  v a  2s  .c om
 * 
 * @param summaryRowList
 */
public void writeSummaryRow(ArrayList<SummaryRow> summaryRowList) {
    CellStyle rightStyle = getCellStyle(WHITE, getFont(FONT_BLACK, (short) 10, false));
    rightStyle.setAlignment(CellStyle.ALIGN_RIGHT);

    int curRow = ROW_START;
    for (SummaryRow summaryRow : summaryRowList) {
        Row row = sheet.createRow(curRow++);

        Cell cell = row.createCell(ISheetTemplate.COL_A);
        cell.setCellValue(summaryRow.getClassification());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_B);
        cell.setCellValue(summaryRow.getProjectName());
        cell.setCellStyle(leftStyle);

        cell = row.createCell(ISheetTemplate.COL_C);
        cell.setCellValue(summaryRow.getScanDate());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_D);
        cell.setCellValue(DateUtil.translateTimeFormatToColon(summaryRow.getScanTime()));
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_E);
        cell.setCellValue(summaryRow.getPendingFileCount());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_F);
        cell.setCellValue(summaryRow.getPendingPercent());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_G);
        cell.setCellValue(summaryRow.getTotalFileCount());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_H);
        cell.setCellValue(summaryRow.getExceptionalFileCount());
        cell.setCellStyle(normalStyle);

        cell = row.createCell(ISheetTemplate.COL_I);
        cell.setCellValue(summaryRow.getBytes());
        cell.setCellStyle(rightStyle);

        cell = row.createCell(ISheetTemplate.COL_J);
        cell.setCellValue(summaryRow.getTotalIdentifiedFileCount());
        cell.setCellStyle(normalStyle);

        int percent = (summaryRow.getCurrentPendingFileCount() * 100 / summaryRow.getPendingFileCount());
        cell = row.createCell(ISheetTemplate.COL_K);
        cell.setCellValue(summaryRow.getCurrentPendingFileCount() + " (" + percent + "%)");
        cell.setCellStyle(normalStyle);
    }

}

From source file:com.seer.datacruncher.profiler.spring.ExporterController.java

License:Open Source License

public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    String type = CommonUtil.notNullValue(request.getParameter("exportaction"));
    String columns = CommonUtil.notNullValue(request.getParameter("exportcolumns"));
    String data = CommonUtil.notNullValue(request.getParameter("exportdata"));

    if (type.equals("csv")) {
        PrintWriter out = response.getWriter();
        response.setContentType("application/csv");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.csv"); // set the file
        // name to
        // whatever
        // required..
        out.println(columns.replace("&&&&&", ","));
        for (String strData : data.split("@@@@@")) {
            out.println(strData.replace("&&&&&", ","));
        }/*from   w w w  .java2  s .c o  m*/
        out.flush();
        out.close();
    } else if (type.equals("xml")) {
        PrintWriter out = response.getWriter();
        response.setContentType("text/xml");
        response.setHeader("content-disposition", "attachment;filename=analysis_data.xml"); // set the file
        // name to
        // whatever
        // required..
        try {
            StringBuffer xml = new StringBuffer("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
            xml.append("<table><header>");
            String colArr[] = columns.split("&&&&&");
            for (String col : colArr) {
                xml.append("<columnName>" + col + "</columnName>");
            }
            xml.append("</header>");

            for (String strData : data.split("@@@@@")) {
                xml.append("<row>");
                int ind = 0;
                for (String val : strData.split("&&&&&")) {
                    xml.append("<" + colArr[ind] + ">" + val + "</" + colArr[ind] + "/>");
                    ind++;
                }
                xml.append("</row>");
            }
            xml.append("</table>");
            out.print(xml.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        out.flush();
        out.close();
    } else if (type.equals("excel")) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=analysis_data.xls");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        String colArr[] = columns.split("&&&&&");
        short ind = 0;
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        Row row = sheet.createRow(0);
        for (String col : colArr) {
            Cell cell = row.createCell(ind);
            cell.setCellValue(col);
            cell.setCellStyle(style);
            ind++;
        }
        ind = 1;
        for (String strData : data.split("@@@@@")) {
            Row valRow = sheet.createRow(ind);
            short cellInd = 0;
            for (String val : strData.split("&&&&&")) {
                valRow.createCell(cellInd).setCellValue(val);
                cellInd++;
            }
            ind++;
        }

        // Write the output to a file
        OutputStream resOout = response.getOutputStream();
        wb.write(resOout);
        resOout.close();

    }

    return null;
}

From source file:com.shiyq.poi.HSSFTest.java

private static void setSheet(Sheet sheet, int startRow, int endRow, int startColumn, int endColumn,
        String describe, CellStyle descStyle) {
    Row row = sheet.getRow(startRow);/*from   w w w .ja  va  2 s .co m*/
    if (Objects.isNull(row)) {
        row = sheet.createRow(startRow);
    }
    Cell cell = row.createCell(startColumn);
    cell.setCellStyle(descStyle);
    cell.setCellValue(describe);
    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startColumn, endColumn));
}

From source file:com.siemens.sw360.exporter.ExcelExporter.java

License:Open Source License

/**
 * Write the values into the row, setting the cells to the given style
 *///from  w  ww  . j  a  va 2  s  .  co  m
private void fillRow(Row row, List<String> values, CellStyle style) {
    for (int column = 0; column < nColumns; column++) {
        Cell cell = row.createCell(column);
        cell.setCellValue(values.get(column));
        cell.setCellStyle(style);
    }
}

From source file:com.sigueme.frontend.controller.ExportController.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//from  www.ja v  a 2 s.  c o  m
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());

    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellValue(cell.getStringCellValue().toUpperCase());
            cell.setCellStyle(style);

        }
    }
}

From source file:com.skt.adcas.lte.action.DownLinkByNMSAction.java

private static void createCell(Workbook wb, Row row, short column, String value, short halign) {
    Cell cell = row.createCell(column);
    cell.setCellValue(value);/* w  w  w  .j a  v a  2 s. c  o m*/
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cell.setCellStyle(cellStyle);

}

From source file:com.skt.adcas.lte.action.DownLinkByNMSAction.java

private static void createCell(Workbook wb, Row row, short column, double value, short halign) {
    Cell cell = row.createCell(column);
    cell.setCellValue(value);/*from www  .  ja va2  s .  c o  m*/
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cell.setCellStyle(cellStyle);

}

From source file:com.skt.adcas.lte.action.DownLinkByNMSAction.java

private static void createCell(Workbook wb, Row row, short column, short halign, short valign, String text) {
    Cell cell = row.createCell(column);
    cell.setCellValue(text);//from  w ww  . jav  a 2 s. co m
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}

From source file:com.softtek.mdm.web.institution.UserController.java

/**
  * /*from   ww w.j  a  va 2  s. co  m*/
  * 
  * @param request
  * @param response
  * @throws Exception
  */
@RequestMapping(value = "/exportuser", method = RequestMethod.GET)
@ResponseBody
public void exportuser(String groupid, HttpServletRequest request, HttpServletResponse response,
        HttpSession session) throws Exception {
    @SuppressWarnings("unchecked")
    List<StructureModel> list = (List<StructureModel>) session
            .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString());
    Integer id = Integer.parseInt(groupid);
    String name = structureService.queryNameById(id);
    request.setAttribute("name", name);
    List<Integer> idList = new ArrayList<Integer>();
    structureService.queryAllChildrenId(id, list, idList);
    idList.add(id);
    // ?ID?
    List<UserExportModel> userExportModel = userService.exportUsersById(idList);
    // ??
    for (int i = 0; i < idList.size(); i++) {
        StructureModel temp = structureService.getParents(idList.get(i));
        String belongStr = temp.getName();
        if (temp != null) {
            for (int j = 0; j < userExportModel.size(); j++) {
                if (userExportModel.get(j).getGroup_id().equals(idList.get(i))) {
                    // String belongStr = temp.getName();
                    while (temp.getParent() != null) {
                        belongStr = StringUtil.insert(belongStr, temp.getParent().getName() + "/");
                        temp = temp.getParent();
                    }
                    userExportModel.get(j).setGroup_name(belongStr);
                }
            }
        }
    }

    ExportData exportData = new ExportData();
    String headers[][] = {
            { messageSource.getMessage("web.institution.usercontroller.export.users.label1", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label2", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label3", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label4", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label5", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label6", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label7", null,
                    LocaleContextHolder.getLocale()), "String" } };
    SXSSFWorkbook workbook = exportData.getwb(headers, "sheet1");
    int currentRow = 1;
    Sheet sheet = workbook.getSheetAt(0);
    CellStyle cellStyle = workbook.createCellStyle();
    // cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    for (int i = 0; i < userExportModel.size(); i++) {
        Row row = sheet.createRow(currentRow);
        for (int j = 0; j < 7; j++) {
            Cell cell = row.createCell(j);
            if (j == 0) {
                cell.setCellValue(userExportModel.get(i).getGroup_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 1) {
                cell.setCellValue(userExportModel.get(i).getUser_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 2) {
                cell.setCellValue(userExportModel.get(i).getReal_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 3) {
                cell.setCellValue(userExportModel.get(i).getPhone());
                cell.setCellStyle(cellStyle);
            }
            if (j == 4) {
                cell.setCellValue(userExportModel.get(i).getEmail());
                cell.setCellStyle(cellStyle);
            }
            if (j == 5) {
                cell.setCellValue(userExportModel.get(i).getMark());
                cell.setCellStyle(cellStyle);
            }
            if (j == 6) {
                String sex = messageSource.getMessage("web.institution.usercontroller.sex.woman", null,
                        LocaleContextHolder.getLocale());

                if (userExportModel.get(i).getGender().equals("1")) {
                    sex = messageSource.getMessage("web.institution.usercontroller.sex.man", null,
                            LocaleContextHolder.getLocale());
                }

                cell.setCellValue(sex);
                cell.setCellStyle(cellStyle);
            }
        }
        currentRow++;
    }
    // XSSFWorkbook workbook = new XSSFWorkbook();
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-msdownload");
    OutputStream os = null;
    String fileName = messageSource.getMessage("web.institution.usercontroller.export.users.model", null,
            LocaleContextHolder.getLocale());
    response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    os = response.getOutputStream();
    workbook.write(os);
    os.flush();
    os.close();
}

From source file:com.solidmaps.webapp.report.TimesheetDemo.java

License:Apache License

public void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//from  w w w. j a  v a2 s  . c om
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "/home/brunorocca/Desktop/mapa_teste.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}