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

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

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

Introduction

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

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:campmanager.CampUI.java

public void exportToExcel() {
    JFrame parentFrame = new JFrame();
    File fileToSave = null;/*from   w w w .  j av  a  2s. c o  m*/
    ;
    JFileChooser fileChooser = new JFileChooser();
    fileChooser.setDialogTitle("Export to Excel");

    int userSelection = fileChooser.showSaveDialog(parentFrame);

    if (userSelection == JFileChooser.APPROVE_OPTION) {
        fileToSave = fileChooser.getSelectedFile();
        // System.out.println("Save as file: " + fileToSave.getAbsolutePath());
    }
    if (fileToSave != null) {

        String fileName = fileToSave.getAbsolutePath();
        if (!fileName.endsWith(".xls"))
            fileName += ".xls";
        try {
            DefaultTableModel dtm = (DefaultTableModel) jTable_records.getModel();
            Workbook wb = new HSSFWorkbook();
            CreationHelper createhelper = wb.getCreationHelper();
            Sheet sheet = wb.createSheet("new sheet");
            Row row = null;
            Cell cell = null;
            row = sheet.createRow(0);

            HSSFFont font = (HSSFFont) wb.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();

            style.setFont(font);

            for (int t = 0; t < dtm.getColumnCount() - 1; t++) {
                cell = row.createCell(t);
                cell.setCellStyle(style);
                cell.setCellValue(dtm.getColumnName(t));
            }

            HSSFCellStyle style_gray = (HSSFCellStyle) wb.createCellStyle();
            style_gray.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
            // style_gray.setFillPattern(CellStyle.ALT_BARS);
            for (int i = 1; i <= dtm.getRowCount(); i++) {
                row = sheet.createRow(i);

                for (int j = 0; j < dtm.getColumnCount() - 1; j++) {
                    cell = row.createCell(j);
                    if (i % 2 == 0)
                        cell.setCellStyle(style_gray);
                    cell.setCellValue(dtm.getValueAt(i - 1, j).toString());

                }
            }

            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

}

From source file:cartel.DynamicDegreeCalculation.java

License:Open Source License

public void compute(Graph graph) throws FileNotFoundException, IOException {

    Map<String, Integer> weightedDegreeForOneCountryfForOneYear;

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("weighted degree");
    Row row;//  www . j a v a2 s  .com
    Cell cell;
    Map<String, Integer> countryIndices = new TreeMap();

    int index = 0;
    for (String country : CartelDynamic.europeanCountries) {
        index++;
        countryIndices.put(country, index);
    }

    //COLUMNS HEADER
    row = sheet.createRow((short) 0);
    index = 1;
    for (int i = 1948; i < 2009; i++) {
        cell = row.createCell(index);
        index++;
        cell.setCellValue(String.valueOf(i));
    }

    //CREATING EMPTY CELLS FOR EACH ROW
    for (String country : countryIndices.keySet()) {
        row = sheet.createRow((countryIndices.get(country)));
        index = 0;
        for (int i = 1948; i <= 2009; i++) {
            row.createCell(index);
            index++;
        }
    }

    //FILLING FIRST COLUMN WITH COUNTRIES
    for (String country : countryIndices.keySet()) {
        row = sheet.getRow(countryIndices.get(country));
        row.getCell(0).setCellValue(country);
    }

    int indexYear = 1;
    for (int i = 1948; i < 2009; i++) {
        weightedDegreeForOneCountryfForOneYear = new TreeMap();
        for (Node node : graph.getNodes()) {
            String nodeLabel = node.getLabel();
            int sumDegrees = 0;

            for (Edge edge : graph.getAllEdges()) {
                if (!edge.getSource().getLabel().equals(nodeLabel)
                        & !edge.getTarget().getLabel().equals(nodeLabel)) {
                    continue;
                }
                if (edge.getSource().getLabel().equals(edge.getTarget().getLabel())) {
                    continue;
                }
                AttributeValueList attributeValueList = edge.getAttributeValues();
                for (AttributeValue attributeValue : attributeValueList) {
                    if (!attributeValue.getAttribute().getTitle().equals("freq")) {
                        continue;
                    }

                    if (((Integer) attributeValue.getStartValue()) != i) {
                        continue;
                    }
                    sumDegrees = sumDegrees + Integer.parseInt(attributeValue.getValue());
                }
            }
            sumDegrees = sumDegrees / 2;
            row = sheet.getRow(countryIndices.get(nodeLabel));
            cell = row.getCell(indexYear);
            cell.setCellValue(String.valueOf(sumDegrees));

        }
        indexYear++;
    }
    String pathFile = "D:/workbook weighted degree.xlsx";
    FileOutputStream fileOut = new FileOutputStream(pathFile);

    wb.write(fileOut);

    fileOut.close();

}

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);/*from w w w .  ja v a  2s .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);/* w w  w .  j a v  a  2 s .  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.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * Excel ????List<Map<String K,String V>>
 *
 * @param filepath    ?/*from w w  w .  j a  v a  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

/**
 * Excle//from  ww  w . j ava  2  s .c om
 *
 * @param filepath    ?
 * @param sheetTitle  Sheet??
 * @param fieldTitles Sheet????
 * @param objList     ??
 * @param objClass    ???
 * @param fieldNames  ?objClassfield??
 */
public static void writeExcel(String filepath, String sheetTitle, String fieldTitles, List<?> objList,
        Class<?> objClass, 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();

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

        createTitle(sheet, fieldTitles);
        createBody(sheet, objList, objClass, fieldNames);
        // ?Excel
        saveExcelFile(workbook, filepath);
    }

}

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);//  ww w .java  2s  .  c o m

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

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

License:Apache License

@SuppressWarnings("squid:S3776")
protected Workbook createSpreadsheet(ManagedProcess report) {
    Workbook wb = new XSSFWorkbook();

    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }/*w  w  w.j  ava  2 s. c o  m*/
    Sheet sheet = wb.createSheet(name);
    sheet.createFreezePane(0, 1, 0, 1);

    Row headerRow = sheet.createRow(0);
    CellStyle headerStyle = createHeaderStyle(wb);
    CellStyle dateStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss"));

    for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) {
        Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells());
        headerCell.setCellValue(columnName);
        headerCell.setCellStyle(headerStyle);
    }

    Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList();
    //make rows, don't forget the header row
    for (ArchivedProcessFailure error : rows) {
        Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        Cell c;

        c = row.createCell(0);
        c.setCellValue(error.time);
        c.setCellStyle(dateStyle);
        c = row.createCell(1);
        c.setCellValue(error.nodePath);
        c = row.createCell(2);
        c.setCellValue(error.error);
        c = row.createCell(3);
        c.setCellValue(error.stackTrace);
    }
    autosize(sheet, 4);
    sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3));
    return wb;
}

From source file:com.AllenBarr.CallSheetGenerator.Generator.java

License:Open Source License

public int generateSheet(File file, Contributor contrib) {
    //create workbook file
    final String fileName = file.toString();
    final Workbook wb;
    if (fileName.endsWith(".xlsx")) {
        wb = new XSSFWorkbook();
    } else if (fileName.endsWith(".xls")) {
        wb = new HSSFWorkbook();
    } else {/*from   w ww  .ja  v a 2 s  .  co m*/
        return 1;
    }
    //create sheet
    final Sheet sheet = wb.createSheet("Call Sheet");
    final Header header = sheet.getHeader();
    header.setCenter("Anderson for Iowa Call Sheet");
    //add empty cells
    final Row[] row = new Row[22 + contrib.getDonationsLength()];
    final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()];
    for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) {
        row[i] = sheet.createRow((short) i);
        for (int j = 0; j < 6; j++) {
            cell[j][i] = row[i].createCell(j);
        }
    }
    //populate cells with data
    //column 1
    cell[0][0].setCellValue(contrib.getName());
    cell[0][3].setCellValue("Sex:");
    cell[0][4].setCellValue("Party:");
    cell[0][5].setCellValue("Phone #:");
    cell[0][6].setCellValue("Home #:");
    cell[0][7].setCellValue("Cell #:");
    cell[0][8].setCellValue("Work #:");
    cell[0][10].setCellValue("Email:");
    cell[0][12].setCellValue("Employer:");
    cell[0][13].setCellValue("Occupation:");
    cell[0][15].setCellValue("Past Contact:");
    cell[0][17].setCellValue("Notes:");
    cell[0][21].setCellValue("Contribution History:");
    //column 2
    cell[1][3].setCellValue(contrib.getSex());
    cell[1][4].setCellValue(contrib.getParty());
    cell[1][5].setCellValue(contrib.getPhone());
    cell[1][6].setCellValue(contrib.getHomePhone());
    cell[1][7].setCellValue(contrib.getCellPhone());
    cell[1][8].setCellValue(contrib.getWorkPhone());
    cell[1][9].setCellValue("x" + contrib.getWorkExtension());
    cell[1][10].setCellValue(contrib.getEmail());
    cell[1][12].setCellValue(contrib.getEmployer());
    cell[1][13].setCellValue(contrib.getOccupation());
    cell[1][17].setCellValue(contrib.getNotes());
    //column 4
    cell[3][3].setCellValue("Salutation:");
    cell[3][4].setCellValue("Age:");
    cell[3][5].setCellValue("Spouse:");
    cell[3][7].setCellValue("Address:");
    cell[3][10].setCellValue("TARGET:");
    //column 5
    cell[4][0].setCellValue("VANID:");
    cell[4][3].setCellValue(contrib.getSalutation());
    cell[4][4].setCellValue(contrib.getAge());
    cell[4][5].setCellValue(contrib.getSpouse());
    cell[4][7].setCellValue(contrib.getStreetAddress());
    cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip());
    //column 6
    cell[5][0].setCellValue(contrib.getVANID());
    //contribution cells
    for (int i = 0; i < contrib.getDonationsLength(); i++) {
        cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate());
        cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient());
        cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount());
    }

    //format cells
    //Name cell
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
    final CellStyle leftBoldUnderline14Style = wb.createCellStyle();
    final Font boldUnderline14Font = wb.createFont();
    boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldUnderline14Font.setUnderline(Font.U_SINGLE);
    boldUnderline14Font.setFontHeightInPoints((short) 14);
    boldUnderline14Font.setFontName("Garamond");
    leftBoldUnderline14Style.setFont(boldUnderline14Font);
    leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT);
    cell[0][0].setCellStyle(leftBoldUnderline14Style);
    //field name cells
    final CellStyle rightBold10Style = wb.createCellStyle();
    final Font bold10Font = wb.createFont();
    bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold10Font.setFontHeightInPoints((short) 10);
    bold10Font.setFontName("Garamond");
    rightBold10Style.setFont(bold10Font);
    rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 3; i < 22; i++) {
        cell[0][i].setCellStyle(rightBold10Style);
    }
    sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1));
    for (int i = 3; i < 11; i++) {
        cell[3][i].setCellStyle(rightBold10Style);
    }
    cell[4][0].setCellStyle(rightBold10Style);
    //field content cells
    final CellStyle left10Style = wb.createCellStyle();
    final Font garamond10Font = wb.createFont();
    garamond10Font.setFontHeightInPoints((short) 10);
    garamond10Font.setFontName("Garamond");
    left10Style.setFont(garamond10Font);
    left10Style.setAlignment(CellStyle.ALIGN_LEFT);
    for (int i = 3; i < 5; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //phone number cells
    final CellStyle phoneStyle = wb.createCellStyle();
    phoneStyle.setFont(garamond10Font);
    phoneStyle.setAlignment(CellStyle.ALIGN_LEFT);
    final CreationHelper createHelper = wb.getCreationHelper();
    phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####"));
    for (int i = 5; i < 9; i++) {
        cell[1][i].setCellStyle(phoneStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

    }
    cell[1][9].setCellStyle(left10Style);
    //email through past contact
    for (int i = 10; i < 16; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //notes
    CellStyle noteStyle = wb.createCellStyle();
    noteStyle.cloneStyleFrom(left10Style);
    noteStyle.setWrapText(true);
    cell[1][17].setCellStyle(noteStyle);
    //column E
    for (int i = 3; i < 11; i++) {
        cell[4][i].setCellStyle(left10Style);
    }
    //VanID Cell
    final CellStyle right10Style = wb.createCellStyle();
    right10Style.setFont(garamond10Font);
    right10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    cell[5][0].setCellStyle(right10Style);
    //Notes cell
    sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5));
    //contribution cells
    final CellStyle date10Style = wb.createCellStyle();
    date10Style.setFont(garamond10Font);
    date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
    date10Style.setBorderBottom(CellStyle.BORDER_THIN);
    date10Style.setBorderTop(CellStyle.BORDER_THIN);
    date10Style.setBorderLeft(CellStyle.BORDER_THIN);
    date10Style.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle contributionStyle = wb.createCellStyle();
    contributionStyle.cloneStyleFrom(left10Style);
    contributionStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contributionStyle.setBorderTop(CellStyle.BORDER_THIN);
    contributionStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contributionStyle.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle money10Style = wb.createCellStyle();
    money10Style.setFont(garamond10Font);
    money10Style.setDataFormat(
            createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    money10Style.setBorderBottom(CellStyle.BORDER_THIN);
    money10Style.setBorderTop(CellStyle.BORDER_THIN);
    money10Style.setBorderLeft(CellStyle.BORDER_THIN);
    money10Style.setBorderRight(CellStyle.BORDER_THIN);
    for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) {
        cell[0][i].setCellStyle(date10Style);
        cell[1][i].setCellStyle(contributionStyle);
        cell[2][i].setCellStyle(contributionStyle);
        cell[3][i].setCellStyle(contributionStyle);
        cell[4][i].setCellStyle(contributionStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4));
        cell[5][i].setCellStyle(money10Style);

    }
    //resize columns
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    try {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        return 1;
    } catch (IOException ex) {
        return 1;
    }

    return 0;
}

From source file:com.alvexcore.repo.documents.generation.ExportDataListToXlsx.java

License:Open Source License

protected Workbook createXlsx(JSONArray rows, String XLS_SHEET_NAME) {

    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(XLS_SHEET_NAME);

    for (int k = 0; k < rows.size(); k++) {
        Row row = sheet.createRow((short) k);
        JSONArray cells = (JSONArray) rows.get(k);
        for (int c = 0; c < cells.size(); c++) {
            String displayValue;//from   w  w  w.ja  v a  2  s. c om
            Object item = cells.get(c);
            if (item == null)
                displayValue = I18NUtil.getMessage("label.empty");
            else if (item instanceof Boolean)
                displayValue = (Boolean) item ? I18NUtil.getMessage("label.yes")
                        : I18NUtil.getMessage("label.no");
            else
                displayValue = item.toString();
            row.createCell(c).setCellValue(createHelper.createRichTextString(displayValue));
        }
    }

    return wb;
}