Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:cn.afterturn.easypoi.excel.export.ExcelExportService.java

License:Apache License

public void createSheetForMap(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList,
        Collection<?> dataSet) {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }/*from w  ww.j  a  v a2 s . com*/
    if (workbook == null || entity == null || entityList == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    super.type = entity.getType();
    if (type.equals(ExcelType.XSSF)) {
        MAX_NUM = 1000000;
    }
    if (entity.getMaxNum() > 0) {
        MAX_NUM = entity.getMaxNum();
    }
    Sheet sheet = null;
    try {
        sheet = workbook.createSheet(entity.getSheetName());
    } catch (Exception e) {
        // ????,???,???Sheet
        sheet = workbook.createSheet();
    }
    insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
}

From source file:cn.bzvs.excel.export.ExcelExportServer.java

License:Apache License

public void createSheetForMap(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList,
        Collection<?> dataSet) {
    if (workbook == null || entity == null || entityList == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//from  w ww  .jav a 2  s .c o  m
    super.type = entity.getType();
    if (type.equals(ExcelType.XSSF)) {
        MAX_NUM = 1000000;
    }
    if (entity.getMaxNum() > 0) {
        MAX_NUM = entity.getMaxNum();
    }
    Sheet sheet = null;
    try {
        sheet = workbook.createSheet(entity.getSheetName());
    } catch (Exception e) {
        // ????,???,???Sheet
        sheet = workbook.createSheet();
    }
    insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
}

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

License:Apache License

private static void addContent(Workbook workBook, boolean isHType, int rows, int cols) {
    Map<String, CellStyle> styles = createStyles(workBook);

    Sheet sheet = workBook.createSheet("Main Sheet");

    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));

    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;/*from ww  w . j  ava2 s. com*/
        }

        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            value = populateCell(styles, value, calendar, rowIndex, row, colIndex);
        }
        rowIndexInSheet++;
    }
}

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);//w w  w  .  j av a 2s .  co m
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);
    }

    //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);/* www  . jav a2  s .  c  om*/
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);
    }

    //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);//from w  w w.j  a v  a 2s .  c  o m
    for (int k = 0; k < array.length; k++) {
        Cell cell = row.createCell(k);
        cell.setCellValue(array[k]);
    }

    //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.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static <T> void buildExcelDocument(Class<T> elementType, Stream<T> content, @Nonnull Locale locale,
        Workbook workbook) {
    MetaInfo meta = MetaInfo.forType(elementType, locale);
    Sheet sheet = workbook.createSheet(elementType.getSimpleName());
    create(meta.getHeaderAsStream(), sheet.createRow(0));
    AtomicInteger counter = new AtomicInteger();
    content.forEach(entity -> create(meta.getFieldsAsStream().map(field -> fieldGet(field, entity)),
            sheet.createRow(counter.incrementAndGet())));
}

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

License:Apache License

/**
 * Excel ????List<Map<String K,String V>>
 *
 * @param filepath    ?/*from   w w w.j ava  2 s .c  om*/
 * @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.org.vbn.util.LinkedDropDownLists.java

License:Apache License

LinkedDropDownLists(String workbookName) {
    File file = null;//from   www.  j a  v  a  2 s . c o  m
    FileOutputStream fos = null;
    Workbook workbook = null;
    Sheet sheet = null;
    DataValidationHelper dvHelper = null;
    DataValidationConstraint dvConstraint = null;
    DataValidation validation = null;
    CellRangeAddressList addressList = null;
    try {

        // Using the ss.usermodel allows this class to support both binary
        // and xml based workbooks. The choice of which one to create is
        // made by checking the file extension.
        if (workbookName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }

        // Build the sheet that will hold the data for the validations. This
        // must be done first as it will create names that are referenced
        // later.
        sheet = workbook.createSheet("Linked Validations");
        LinkedDropDownLists.buildDataSheet(sheet);

        // Build the first data validation to occupy cell A1. Note
        // that it retrieves it's data from the named area or region called
        // CHOICES. Further information about this can be found in the
        // static buildDataSheet() method below.
        addressList = new CellRangeAddressList(0, 0, 0, 0);
        dvHelper = sheet.getDataValidationHelper();
        dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
        validation = dvHelper.createValidation(dvConstraint, addressList);
        sheet.addValidationData(validation);

        // Now, build the linked or dependent drop down list that will
        // occupy cell B1. The key to the whole process is the use of the
        // INDIRECT() function. In the buildDataSheet(0 method, a series of
        // named regions are created and the names of three of them mirror
        // the options available to the user in the first drop down list
        // (in cell A1). Using the INDIRECT() function makes it possible
        // to convert the selection the user makes in that first drop down
        // into the addresses of a named region of cells and then to use
        // those cells to populate the second drop down list.
        addressList = new CellRangeAddressList(0, 0, 1, 1);
        dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
        validation = dvHelper.createValidation(dvConstraint, addressList);
        sheet.addValidationData(validation);

        file = new File(workbookName);
        fos = new FileOutputStream(file);
        workbook.write(fos);
    } catch (IOException ioEx) {
        System.out.println("Caught a: " + ioEx.getClass().getName());
        System.out.println("Message: " + ioEx.getMessage());
        System.out.println("Stacktrace follws:.....");
        ioEx.printStackTrace(System.out);
    } finally {
        try {
            if (fos != null) {
                fos.close();
                fos = null;
            }
        } catch (IOException ioEx) {
            System.out.println("Caught a: " + ioEx.getClass().getName());
            System.out.println("Message: " + ioEx.getMessage());
            System.out.println("Stacktrace follws:.....");
            ioEx.printStackTrace(System.out);
        }
    }
}

From source file:com.adobe.acs.commons.mcp.impl.GenericReportExcelServlet.java

License:Apache License

private Workbook createSpreadsheet(GenericReport report) {
    Workbook wb = new XSSFWorkbook();

    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }//  w  w  w  .j  a  v a 2s.co  m
    XSSFSheet sheet = (XSSFSheet) wb.createSheet(name);

    XSSFRow headerRow = sheet.createRow(0);

    for (int c = 0; c < report.getColumnNames().size(); c++) {
        XSSFCell headerCell = headerRow.createCell(c);
        headerCell.setCellValue(report.getColumnNames().get(c));
    }

    List<ValueMap> rows = report.getRows();
    //make rows, don't forget the header row
    for (int r = 0; r < rows.size(); r++) {
        XSSFRow row = sheet.createRow(r + 1);

        //make columns
        for (int c = 0; c < report.getColumns().size(); c++) {
            String col = report.getColumns().get(c);
            XSSFCell cell = row.createCell(c);

            if (rows.get(r).containsKey(col)) {
                Object val = rows.get(r).get(col);
                if (val instanceof Number) {
                    Number n = (Number) val;
                    cell.setCellValue(((Number) val).doubleValue());
                } else {
                    cell.setCellValue(String.valueOf(val));
                }
            }
        }
    }
    return wb;
}