Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ??//from  ww w.jav a  2 s.  c om
 * @param object
 * @param row
 * @param params
 * @param pojoClass
 * @return
 */
private boolean verifyingDataValidity(Object object, Row row, ImportParams params, Class<?> pojoClass) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerfiy()) {
        String errorMsg = PoiValidationUtil.validation(object);
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (params.getVerifyHanlder() != null) {
        ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null)
                cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (cell != null)
        cell.setCellStyle(errorCellStyle);
    return isAdd;
}

From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java

License:Apache License

private static double populateCell(Map<String, CellStyle> styles, double value, Calendar calendar, int rowIndex,
        Row row, int colIndex) {
    Cell cell = row.createCell(colIndex);
    String address = new CellReference(cell).formatAsString();
    switch (colIndex) {
    case 0:/*w  ww  .  j a  va 2s .c o m*/
        // column A: default number format
        cell.setCellValue(value++);
        break;
    case 1:
        // column B: #,##0
        cell.setCellValue(value++);
        cell.setCellStyle(styles.get("#,##0.00"));
        break;
    case 2:
        // column C: $#,##0.00
        cell.setCellValue(value++);
        cell.setCellStyle(styles.get("$#,##0.00"));
        break;
    case 3:
        // column D: red bold text on yellow background
        cell.setCellValue(address);
        cell.setCellStyle(styles.get("red-bold"));
        break;
    case 4:
        // column E: boolean
        // TODO booleans are shown as 1/0 instead of TRUE/FALSE
        cell.setCellValue(rowIndex % 2 == 0);
        break;
    case 5:
        // column F:  date / time
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("m/d/yyyy"));
        calendar.roll(Calendar.DAY_OF_YEAR, -1);
        break;
    case 6:
        // column F: formula
        // TODO formulas are not yet supported  in SXSSF
        //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")");
        //break;
    default:
        cell.setCellValue(value++);
        break;
    }
    return value;
}

From source file:cn.edu.pku.lib.dataverse.ManageUserGroupPage.java

private File generateExcelRequestJoinGroupLogFile() {
    //excel workbook
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("Groups' user member"));
    Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale();

    //generate header
    String heads = ResourceBundle.getBundle("Bundle", locale)
            .getString("dataverse.permissions.groups.member.header");
    String[] array = heads.split(",");
    Row row = sheet.createRow(0);
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);/* ww w.  j  a v a  2  s  . c om*/
    }

    //generate logs
    Set<AuthenticatedUser> authUsersSet = explicitGroup.getContainedAuthenticatedUsers();
    int j = 1;
    Cell cell;
    for (AuthenticatedUser user : authUsersSet) {
        row = sheet.createRow(j);
        if (user.isBuiltInUser()) {
            BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier());
            cell = row.createCell(0);
            cell.setCellValue(b.getUserName());
            cell = row.createCell(1);
            cell.setCellValue(b.getLastName());
            cell = row.createCell(2);
            cell.setCellValue(b.getFirstName());
            cell = row.createCell(3);
            if (b.getUserType() == UserType.ORDINARY)
                cell.setCellValue("ORDINARY");
            else if (b.getUserType() == UserType.ADVANCE)
                cell.setCellValue("ADVANCE");
            else
                cell.setCellValue("");
            cell = row.createCell(4);
            cell.setCellValue(b.getAffiliation());
            cell = row.createCell(5);
            cell.setCellValue(b.getPosition());
            cell = row.createCell(6);
            cell.setCellValue(b.getDepartment());
            cell = row.createCell(7);
            cell.setCellValue(b.getEmail());
            cell = row.createCell(8);
            cell.setCellValue(b.getSpeciality());
            cell = row.createCell(9);
            cell.setCellValue(b.getResearchInterest());
            cell = row.createCell(10);
            cell.setCellValue(b.getGender());
            cell = row.createCell(11);
            cell.setCellValue(b.getEducation());
            cell = row.createCell(12);
            cell.setCellValue(b.getProfessionalTitle());
            cell = row.createCell(13);
            cell.setCellValue(b.getSupervisor());
            cell = row.createCell(14);
            cell.setCellValue(b.getCertificateType());
            cell = row.createCell(15);
            cell.setCellValue(b.getCertificateNumber());
            cell = row.createCell(16);
            cell.setCellValue(b.getOfficePhone());
            cell = row.createCell(17);
            cell.setCellValue(b.getCellphone());
            cell = row.createCell(18);
            cell.setCellValue(b.getOtherEmail());
            cell = row.createCell(19);
            cell.setCellValue(b.getCountry());
            cell = row.createCell(20);
            cell.setCellValue(b.getProvince());
            cell = row.createCell(21);
            cell.setCellValue(b.getCity());
            cell = row.createCell(22);
            cell.setCellValue(b.getAddress());
            cell = row.createCell(23);
            cell.setCellValue(b.getZipCode());
            cell = row.createCell(24);
            cell.setCellValue("Built In");
        } else if (user.isPKUIAAAUser()) {
            PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier());
            cell = row.createCell(0);
            cell.setCellValue(p.getUserName());
            cell = row.createCell(1);
            cell.setCellValue(p.getLastName());
            cell = row.createCell(2);
            cell.setCellValue(p.getFirstName());
            cell = row.createCell(3);
            if (p.getUserType() == UserType.ORDINARY)
                cell.setCellValue("ORDINARY");
            else if (p.getUserType() == UserType.ADVANCE)
                cell.setCellValue("ADVANCE");
            else
                cell.setCellValue("");
            cell = row.createCell(4);
            cell.setCellValue(p.getAffiliation());
            cell = row.createCell(5);
            cell.setCellValue(p.getPosition());
            cell = row.createCell(6);
            cell.setCellValue(p.getDepartment());
            cell = row.createCell(7);
            cell.setCellValue(p.getEmail());
            cell = row.createCell(8);
            cell.setCellValue(p.getSpeciality());
            cell = row.createCell(9);
            cell.setCellValue(p.getResearchInterest());
            cell = row.createCell(10);
            cell.setCellValue(p.getGender());
            cell = row.createCell(11);
            cell.setCellValue(p.getEducation());
            cell = row.createCell(12);
            cell.setCellValue(p.getProfessionalTitle());
            cell = row.createCell(13);
            cell.setCellValue(p.getSupervisor());
            cell = row.createCell(14);
            cell.setCellValue(p.getCertificateType());
            cell = row.createCell(15);
            cell.setCellValue(p.getCertificateNumber());
            cell = row.createCell(16);
            cell.setCellValue(p.getOfficePhone());
            cell = row.createCell(17);
            cell.setCellValue(p.getCellphone());
            cell = row.createCell(18);
            cell.setCellValue(p.getOtherEmail());
            cell = row.createCell(19);
            cell.setCellValue(p.getCountry());
            cell = row.createCell(20);
            cell.setCellValue(p.getProvince());
            cell = row.createCell(21);
            cell.setCellValue(p.getCity());
            cell = row.createCell(22);
            cell.setCellValue(p.getAddress());
            cell = row.createCell(23);
            cell.setCellValue(p.getZipCode());
            cell = row.createCell(24);
            cell.setCellValue("PKU IAAA");
        }
        j++;
    }

    String filesRootDirectory = System.getProperty("dataverse.files.directory");
    if (filesRootDirectory == null || filesRootDirectory.equals("")) {
        filesRootDirectory = "/tmp/files";
    }
    File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID());
    try (FileOutputStream out = new FileOutputStream(file)) {
        wb.write(out);
        return file;
    } catch (IOException ioe) {
        logger.log(Level.SEVERE, null, ioe);
    }
    if (file.exists()) {
        file.delete();
    }
    return null;
}

From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java

private File generateExcelDownloadLogFile() {
    //excel workbook
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("File Download Statistic"));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
    Locale local = FacesContext.getCurrentInstance().getViewRoot().getLocale();

    //generate header
    String heads = ResourceBundle
            .getBundle("Bundle", FacesContext.getCurrentInstance().getViewRoot().getLocale())
            .getString("log.filedownload.header");
    String[] array = heads.split(",");
    Row row = sheet.createRow(0);
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);//from ww w. j  ava 2s. co m
    }

    //generate logs
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    final long size = 100L;
    UsageLogSearchQuery query = queryForFile.clone();
    query.setSize(size);
    query.setDateHistogramInterval(null);
    UsageLogSearchResult searchResult = null;
    int i = 0;
    int j = 1;
    Cell cell;
    do {
        query.setFrom(i * size);
        searchResult = usageLogSearchService.search(query);
        List<EventLog> logs = searchResult.getEventLogs();
        for (EventLog log : logs) {
            row = sheet.createRow(j);
            AuthenticatedUser user;
            if (log.getUserId().equals(":guest")
                    || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) {
                cell = row.createCell(0);
                cell.setCellValue(log.getDate());
                cell.setCellStyle(cellStyle);
                cell = row.createCell(1);
                cell.setCellValue(log.getIp());
                cell = row.createCell(2);
                cell.setCellValue(log.getContinent());
                cell = row.createCell(3);
                cell.setCellValue(log.getCountry());
                cell = row.createCell(4);
                cell.setCellValue(log.getSubdivision());
                cell = row.createCell(5);
                cell.setCellValue(log.getCity());
                cell = row.createCell(6);
                cell.setCellValue(log.getUserId());
                cell = row.createCell(7);
                cell.setCellValue(log.getUserName());
                cell = row.createCell(8);
                cell.setCellValue(log.getAffiliation());
                cell = row.createCell(9);
                cell.setCellValue(log.getPosition());
                cell = row.createCell(10);
                cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local));
                cell = row.createCell(11);
                cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel());
            } else {
                if (user.isBuiltInUser()) {
                    BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier());
                    cell = row.createCell(0);
                    cell.setCellValue(log.getDate());
                    cell.setCellStyle(cellStyle);
                    cell = row.createCell(1);
                    cell.setCellValue(log.getIp());
                    cell = row.createCell(2);
                    cell.setCellValue(log.getContinent());
                    cell = row.createCell(3);
                    cell.setCellValue(log.getCountry());
                    cell = row.createCell(4);
                    cell.setCellValue(log.getSubdivision());
                    cell = row.createCell(5);
                    cell.setCellValue(log.getCity());

                    cell = row.createCell(6);
                    cell.setCellValue(log.getUserId());
                    cell = row.createCell(7);
                    cell.setCellValue(log.getUserName());
                    cell = row.createCell(8);
                    cell.setCellValue(b.getAffiliation());
                    cell = row.createCell(9);
                    cell.setCellValue(b.getPosition());
                    cell = row.createCell(10);
                    cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local));
                    cell = row.createCell(11);
                    cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel());

                    cell = row.createCell(12);
                    cell.setCellValue(b.getDepartment());
                    cell = row.createCell(13);
                    cell.setCellValue(b.getEmail());
                    cell = row.createCell(14);
                    cell.setCellValue(b.getSpeciality());
                    cell = row.createCell(15);
                    cell.setCellValue(b.getResearchInterest());
                    cell = row.createCell(16);
                    cell.setCellValue(b.getGender());
                    cell = row.createCell(17);
                    cell.setCellValue(b.getEducation());

                    cell = row.createCell(18);
                    cell.setCellValue(b.getProfessionalTitle());
                    cell = row.createCell(19);
                    cell.setCellValue(b.getSupervisor());
                    cell = row.createCell(20);
                    cell.setCellValue(b.getCertificateType());
                    cell = row.createCell(21);
                    cell.setCellValue(b.getCertificateNumber());
                    cell = row.createCell(22);
                    cell.setCellValue(b.getOfficePhone());
                    cell = row.createCell(23);
                    cell.setCellValue(b.getCellphone());

                    cell = row.createCell(24);
                    cell.setCellValue(b.getOtherEmail());
                    cell = row.createCell(25);
                    cell.setCellValue(b.getCountry());
                    cell = row.createCell(26);
                    cell.setCellValue(b.getProvince());
                    cell = row.createCell(27);
                    cell.setCellValue(b.getCity());
                    cell = row.createCell(28);
                    cell.setCellValue(b.getAddress());
                    cell = row.createCell(29);
                    cell.setCellValue(b.getZipCode());

                    cell = row.createCell(30);
                    cell.setCellValue("Built In");
                } else if (user.isPKUIAAAUser()) {
                    PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier());
                    cell = row.createCell(0);
                    cell.setCellValue(log.getDate());
                    cell.setCellStyle(cellStyle);
                    cell = row.createCell(1);
                    cell.setCellValue(log.getIp());
                    cell = row.createCell(2);
                    cell.setCellValue(log.getContinent());
                    cell = row.createCell(3);
                    cell.setCellValue(log.getCountry());
                    cell = row.createCell(4);
                    cell.setCellValue(log.getSubdivision());
                    cell = row.createCell(5);
                    cell.setCellValue(log.getCity());

                    cell = row.createCell(6);
                    cell.setCellValue(log.getUserId());
                    cell = row.createCell(7);
                    cell.setCellValue(log.getUserName());
                    cell = row.createCell(8);
                    cell.setCellValue(p.getAffiliation());
                    cell = row.createCell(9);
                    cell.setCellValue(p.getPosition());
                    cell = row.createCell(10);
                    cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local));
                    cell = row.createCell(11);
                    cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel());

                    cell = row.createCell(12);
                    cell.setCellValue(p.getDepartment());
                    cell = row.createCell(13);
                    cell.setCellValue(p.getEmail());
                    cell = row.createCell(14);
                    cell.setCellValue(p.getSpeciality());
                    cell = row.createCell(15);
                    cell.setCellValue(p.getResearchInterest());
                    cell = row.createCell(16);
                    cell.setCellValue(p.getGender());
                    cell = row.createCell(17);
                    cell.setCellValue(p.getEducation());

                    cell = row.createCell(18);
                    cell.setCellValue(p.getProfessionalTitle());
                    cell = row.createCell(19);
                    cell.setCellValue(p.getSupervisor());
                    cell = row.createCell(20);
                    cell.setCellValue(p.getCertificateType());
                    cell = row.createCell(21);
                    cell.setCellValue(p.getCertificateNumber());
                    cell = row.createCell(22);
                    cell.setCellValue(p.getOfficePhone());
                    cell = row.createCell(23);
                    cell.setCellValue(p.getCellphone());

                    cell = row.createCell(24);
                    cell.setCellValue(p.getOtherEmail());
                    cell = row.createCell(25);
                    cell.setCellValue(p.getCountry());
                    cell = row.createCell(26);
                    cell.setCellValue(p.getProvince());
                    cell = row.createCell(27);
                    cell.setCellValue(p.getCity());
                    cell = row.createCell(28);
                    cell.setCellValue(p.getAddress());
                    cell = row.createCell(29);
                    cell.setCellValue(p.getZipCode());

                    cell = row.createCell(30);
                    cell.setCellValue("PKU IAAA");
                }
            }
            j++;
        }
        i++;
    } while (i < searchResult.getPages());

    String filesRootDirectory = System.getProperty("dataverse.files.directory");
    if (filesRootDirectory == null || filesRootDirectory.equals("")) {
        filesRootDirectory = "/tmp/files";
    }
    File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID());
    try (FileOutputStream out = new FileOutputStream(file)) {
        wb.write(out);
        return file;
    } catch (IOException ioe) {
        logger.log(Level.SEVERE, null, ioe);
    }
    if (file.exists()) {
        file.delete();
    }
    return null;
}

From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java

private File generateExcelRequestJoinGroupLogFile() {
    //excel workbook
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("User Join Group Statistic"));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
    Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale();

    //generate header
    String heads = ResourceBundle.getBundle("Bundle", locale).getString("log.requestjoingroup.header");
    String[] array = heads.split(",");
    Row row = sheet.createRow(0);
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);/*  w w  w .ja  v  a2 s. c  o m*/
    }

    //generate logs
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    final long size = 100L;
    UsageLogSearchQuery query = queryForGroup.clone();
    query.setSize(size);
    query.setDateHistogramInterval(null);
    UsageLogSearchResult searchResult = null;
    int i = 0;
    int j = 1;
    Cell cell;
    do {
        query.setFrom(i * size);
        searchResult = usageLogSearchService.search(query);
        List<EventLog> logs = searchResult.getEventLogs();
        for (EventLog log : logs) {
            row = sheet.createRow(j);
            AuthenticatedUser user;
            if (log.getUserId().equals(":guest")
                    || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) {
                cell = row.createCell(0);
                cell.setCellValue(log.getDate());
                cell.setCellStyle(cellStyle);
                cell = row.createCell(1);
                cell.setCellValue(log.getIp());
                cell = row.createCell(2);
                cell.setCellValue(log.getContinent());
                cell = row.createCell(3);
                cell.setCellValue(log.getCountry());
                cell = row.createCell(4);
                cell.setCellValue(log.getSubdivision());
                cell = row.createCell(5);
                cell.setCellValue(log.getCity());
                cell = row.createCell(6);
                cell.setCellValue(log.getUserId());
                cell = row.createCell(7);
                cell.setCellValue(log.getUserName());
                cell = row.createCell(8);
                cell.setCellValue(log.getAffiliation());
                cell = row.createCell(9);
                cell.setCellValue(log.getPosition());
                cell = row.createCell(10);
                cell.setCellValue(getDisplayString(log.getEventType()));
                cell = row.createCell(11);
                cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName());
            } else {
                if (user.isBuiltInUser()) {
                    BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier());
                    cell = row.createCell(0);
                    cell.setCellValue(log.getDate());
                    cell.setCellStyle(cellStyle);
                    cell = row.createCell(1);
                    cell.setCellValue(log.getIp());
                    cell = row.createCell(2);
                    cell.setCellValue(log.getContinent());
                    cell = row.createCell(3);
                    cell.setCellValue(log.getCountry());
                    cell = row.createCell(4);
                    cell.setCellValue(log.getSubdivision());
                    cell = row.createCell(5);
                    cell.setCellValue(log.getCity());

                    cell = row.createCell(6);
                    cell.setCellValue(log.getUserId());
                    cell = row.createCell(7);
                    cell.setCellValue(log.getUserName());
                    cell = row.createCell(8);
                    cell.setCellValue(b.getAffiliation());
                    cell = row.createCell(9);
                    cell.setCellValue(b.getPosition());
                    cell = row.createCell(10);
                    cell.setCellValue(getDisplayString(log.getEventType()));
                    cell = row.createCell(11);
                    cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName());

                    cell = row.createCell(12);
                    cell.setCellValue(b.getDepartment());
                    cell = row.createCell(13);
                    cell.setCellValue(b.getEmail());
                    cell = row.createCell(14);
                    cell.setCellValue(b.getSpeciality());
                    cell = row.createCell(15);
                    cell.setCellValue(b.getResearchInterest());
                    cell = row.createCell(16);
                    cell.setCellValue(b.getGender());
                    cell = row.createCell(17);
                    cell.setCellValue(b.getEducation());

                    cell = row.createCell(18);
                    cell.setCellValue(b.getProfessionalTitle());
                    cell = row.createCell(19);
                    cell.setCellValue(b.getSupervisor());
                    cell = row.createCell(20);
                    cell.setCellValue(b.getCertificateType());
                    cell = row.createCell(21);
                    cell.setCellValue(b.getCertificateNumber());
                    cell = row.createCell(22);
                    cell.setCellValue(b.getOfficePhone());
                    cell = row.createCell(23);
                    cell.setCellValue(b.getCellphone());

                    cell = row.createCell(24);
                    cell.setCellValue(b.getOtherEmail());
                    cell = row.createCell(25);
                    cell.setCellValue(b.getCountry());
                    cell = row.createCell(26);
                    cell.setCellValue(b.getProvince());
                    cell = row.createCell(27);
                    cell.setCellValue(b.getCity());
                    cell = row.createCell(28);
                    cell.setCellValue(b.getAddress());
                    cell = row.createCell(29);
                    cell.setCellValue(b.getZipCode());

                    cell = row.createCell(30);
                    cell.setCellValue("Built In");
                } else if (user.isPKUIAAAUser()) {
                    PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier());
                    cell = row.createCell(0);
                    cell.setCellValue(log.getDate());
                    cell.setCellStyle(cellStyle);
                    cell = row.createCell(1);
                    cell.setCellValue(log.getIp());
                    cell = row.createCell(2);
                    cell.setCellValue(log.getContinent());
                    cell = row.createCell(3);
                    cell.setCellValue(log.getCountry());
                    cell = row.createCell(4);
                    cell.setCellValue(log.getSubdivision());
                    cell = row.createCell(5);
                    cell.setCellValue(log.getCity());

                    cell = row.createCell(6);
                    cell.setCellValue(log.getUserId());
                    cell = row.createCell(7);
                    cell.setCellValue(log.getUserName());
                    cell = row.createCell(8);
                    cell.setCellValue(p.getAffiliation());
                    cell = row.createCell(9);
                    cell.setCellValue(p.getPosition());
                    cell = row.createCell(10);
                    cell.setCellValue(getDisplayString(log.getEventType()));
                    cell = row.createCell(11);
                    cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName());

                    cell = row.createCell(12);
                    cell.setCellValue(p.getDepartment());
                    cell = row.createCell(13);
                    cell.setCellValue(p.getEmail());
                    cell = row.createCell(14);
                    cell.setCellValue(p.getSpeciality());
                    cell = row.createCell(15);
                    cell.setCellValue(p.getResearchInterest());
                    cell = row.createCell(16);
                    cell.setCellValue(p.getGender());
                    cell = row.createCell(17);
                    cell.setCellValue(p.getEducation());

                    cell = row.createCell(18);
                    cell.setCellValue(p.getProfessionalTitle());
                    cell = row.createCell(19);
                    cell.setCellValue(p.getSupervisor());
                    cell = row.createCell(20);
                    cell.setCellValue(p.getCertificateType());
                    cell = row.createCell(21);
                    cell.setCellValue(p.getCertificateNumber());
                    cell = row.createCell(22);
                    cell.setCellValue(p.getOfficePhone());
                    cell = row.createCell(23);
                    cell.setCellValue(p.getCellphone());

                    cell = row.createCell(24);
                    cell.setCellValue(p.getOtherEmail());
                    cell = row.createCell(25);
                    cell.setCellValue(p.getCountry());
                    cell = row.createCell(26);
                    cell.setCellValue(p.getProvince());
                    cell = row.createCell(27);
                    cell.setCellValue(p.getCity());
                    cell = row.createCell(28);
                    cell.setCellValue(p.getAddress());
                    cell = row.createCell(29);
                    cell.setCellValue(p.getZipCode());

                    cell = row.createCell(30);
                    cell.setCellValue("PKU IAAA");
                }
            }
            j++;
        }
        i++;
    } while (i < searchResult.getPages());

    String filesRootDirectory = System.getProperty("dataverse.files.directory");
    if (filesRootDirectory == null || filesRootDirectory.equals("")) {
        filesRootDirectory = "/tmp/files";
    }
    File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID());
    try (FileOutputStream out = new FileOutputStream(file)) {
        wb.write(out);
        return file;
    } catch (IOException ioe) {
        logger.log(Level.SEVERE, null, ioe);
    }
    if (file.exists()) {
        file.delete();
    }
    return null;
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * Excel ????List<Map<String K,String V>>
 *
 * @param filepath    ?/*w  ww  .  j  a va 2s .  co m*/
 * @param sheetTitle  Sheet??
 * @param fieldTitles Sheet????
 * @param objList     ??
 * @param fieldNames  ?objClassfield??
 */
public static void writeExcel(String filepath, String sheetTitle, String fieldTitles,
        List<Map<String, String>> objList, String fieldNames) {
    Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
    for (int j = 0; j < wbs.length; j++) {
        Workbook workbook = wbs[j];
        CreationHelper creationHelper = workbook.getCreationHelper();

        // ExcelSheet
        Sheet sheet = workbook.createSheet(sheetTitle);
        workbook.setSheetName(0, sheetTitle);

        // Sheet
        createTitle(sheet, fieldTitles);

        // Sheet?
        String[] strArray = fieldNames.split(",");
        for (int objIndex = 0; objIndex < objList.size(); objIndex++) {
            Map<String, String> map = objList.get(objIndex);
            Row row = sheet.createRow(objIndex + 1);
            for (int cellNum = 0; cellNum < strArray.length; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellType(CellType.STRING);
                if (map.get(strArray[cellNum]) != null)
                    cell.setCellValue(map.get(strArray[cellNum]).toString());
                else {
                    cell.setCellValue("");
                }
            }
        }

        // ?Excel
        saveExcelFile(workbook, filepath);
    }

}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * Excel?sheet?//w  w  w.j a v  a 2 s. c o m
 *
 * @param sheet       Excelsheet
 * @param fieldTitles sheet?(sheet?)
 */
private static void createTitle(Sheet sheet, String fieldTitles) {
    Row row = sheet.createRow(0);
    Cell cell;

    String[] strArray = fieldTitles.split(",");
    for (int i = 0; i < strArray.length; i++) {
        cell = row.createCell(i);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(strArray[i]);
    }
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * Excel?sheet//from  w  w w .  ja  v  a 2s.co  m
 *
 * @param sheet      sheet
 * @param objList    ??
 * @param objClass   ???
 * @param fieldNames ?objClassfield??
 */
private static void createBody(Sheet sheet, List<?> objList, Class<?> objClass, String fieldNames) {
    String[] targetMethod = fieldNames.split(",");
    Method[] ms = objClass.getMethods();
    Pattern pattern = Pattern.compile("^get.*");

    // objList?sheet
    for (int objIndex = 0; objIndex < objList.size(); objIndex++) {
        Object obj = objList.get(objIndex);
        Row row = sheet.createRow(objIndex + 1);
        // strBody?sheet
        for (int strIndex = 0; strIndex < targetMethod.length; strIndex++) {
            String targetMethodName = targetMethod[strIndex];
            // msstrBody
            for (int i = 0; i < ms.length; i++) {
                Method srcMethod = ms[i];
                if (pattern.matcher(srcMethod.getName()).matches()) {
                    int len = targetMethodName.indexOf(".") < 0 ? targetMethodName.length()
                            : targetMethodName.indexOf(".");
                    if (srcMethod.getName()
                            .equals(("get"
                                    + String.valueOf(targetMethodName.substring(0, len).charAt(0)).toUpperCase()
                                    + targetMethodName.substring(1, len)))) {
                        Cell cell = row.createCell(strIndex);
                        cell.setCellType(CellType.STRING);
                        try {
                            // 
                            if (targetMethodName.contains(".")) {
                                cell.setCellValue(referenceInvoke(targetMethodName, obj));
                                // 
                            } else {
                                cell.setCellValue((srcMethod.invoke(obj)).toString());
                            }
                        } catch (Exception e) {
                            throw new RuntimeException(e);
                        }
                    }
                }
            }
        }
    }

}

From source file:cn.org.vbn.util.LinkedDropDownLists.java

License:Apache License

/**
 * Called to populate the named areas/regions. The contents of the cells on
 * row one will be used to populate the first drop down list. The contents of
 * the cells on rows two, three and four will be used to populate the second
 * drop down list, just which row will be determined by the choice the user
 * makes in the first drop down list./*from   w  ww  . ja  va2s  .  co  m*/
 *
 * In all cases, the approach is to create a row, create and populate cells
 * with data and then specify a name that identifies those cells. With the
 * exception of the first range, the names that are chosen for each range
 * of cells are quite important. In short, each of the options the user
 * could select in the first drop down list is used as the name for another
 * range of cells. Thus, in this example, the user can select either
 * 'Animal', 'Vegetable' or 'Mineral' in the first drop down and so the
 * sheet contains ranges named 'ANIMAL', 'VEGETABLE' and 'MINERAL'.
 *
 * @param dataSheet An instance of a class that implements the Sheet Sheet
 *        interface (HSSFSheet or XSSFSheet).
 */
private static final void buildDataSheet(Sheet dataSheet) {
    Row row = null;
    Cell cell = null;
    Name name = null;

    // The first row will hold the data for the first validation.
    row = dataSheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellValue("Animal");
    cell = row.createCell(1);
    cell.setCellValue("Vegetable");
    cell = row.createCell(2);
    cell.setCellValue("Mineral");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$11:$C$11");
    name.setNameName("CHOICES");

    // The next three rows will hold the data that will be used to
    // populate the second, or linked, drop down list.
    row = dataSheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellValue("Lion");
    cell = row.createCell(1);
    cell.setCellValue("Tiger");
    cell = row.createCell(2);
    cell.setCellValue("Leopard");
    cell = row.createCell(3);
    cell.setCellValue("Elephant");
    cell = row.createCell(4);
    cell.setCellValue("Eagle");
    cell = row.createCell(5);
    cell.setCellValue("Horse");
    cell = row.createCell(6);
    cell.setCellValue("Zebra");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$12:$G$12");
    name.setNameName("ANIMAL");

    row = dataSheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellValue("Cabbage");
    cell = row.createCell(1);
    cell.setCellValue("Cauliflower");
    cell = row.createCell(2);
    cell.setCellValue("Potato");
    cell = row.createCell(3);
    cell.setCellValue("Onion");
    cell = row.createCell(4);
    cell.setCellValue("Beetroot");
    cell = row.createCell(5);
    cell.setCellValue("Asparagus");
    cell = row.createCell(6);
    cell.setCellValue("Spinach");
    cell = row.createCell(7);
    cell.setCellValue("Chard");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$13:$H$13");
    name.setNameName("VEGETABLE");

    row = dataSheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellValue("Bauxite");
    cell = row.createCell(1);
    cell.setCellValue("Quartz");
    cell = row.createCell(2);
    cell.setCellValue("Feldspar");
    cell = row.createCell(3);
    cell.setCellValue("Shist");
    cell = row.createCell(4);
    cell.setCellValue("Shale");
    cell = row.createCell(5);
    cell.setCellValue("Mica");
    name = dataSheet.getWorkbook().createName();
    name.setRefersToFormula("$A$14:$F$14");
    name.setNameName("MINERAL");
}

From source file:co.com.runt.runistac.logica.ReporteLogica.java

public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos)
        throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook(plantilla);
    XSSFSheet mySheet = wb.getSheetAt(0);
    for (int i = 0; i < mySheet.getLastRowNum(); i++) {
        Row row = mySheet.getRow(i);
        if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) {
            for (String key : parametros.keySet()) {
                String valor = row.getCell(0).getStringCellValue();
                valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key));
                row.getCell(0).setCellValue(valor);
            }//from  w ww .  j a  v  a  2s.  co m
        }
    }

    int rows = mySheet.getLastRowNum();
    int i = 0;
    Row base = mySheet.getRow(rows);
    CellStyle[] cs = null;
    if (!datos.isEmpty()) {
        int cant = datos.get(0).length;
        cs = new CellStyle[cant];
        for (int j = 0; j < cant; j++) {
            cs[j] = base.getCell(j).getCellStyle();
        }
    }
    for (Object[] o : datos) {
        Row row = mySheet.createRow(rows + i);
        for (int j = 0; j < o.length; j++) {
            Cell c = row.createCell(j);
            String value = "";
            if (o[j] != null) {
                if (o[j] instanceof String) {
                    value = (String) o[j];
                    c.setCellValue(value);
                } else if (o[j] instanceof Integer) {//integer
                    c.setCellValue((Integer) o[j]);
                } else if (o[j] instanceof Double) {
                    c.setCellValue((Double) o[j]);
                } else if (o[j] instanceof Float) {
                    c.setCellValue((Float) o[j]);
                } else if (o[j] instanceof BigDecimal) {
                    c.setCellValue(((BigDecimal) o[j]).doubleValue());
                } else if (o[j] instanceof Date) {
                    c.setCellValue(((Date) o[j]));
                } else if (o[j] instanceof BigInteger) {
                    c.setCellValue(((BigInteger) o[j]).intValue());
                } else {
                    c.setCellValue(o[j].toString());
                    System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass());
                }
            }
            c.setCellStyle(cs[j]);
        }
        i++;
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);

    return baos.toByteArray();
}