Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getCreationHelper

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getCreationHelper

Introduction

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

Prototype

@Override
public XSSFCreationHelper getCreationHelper()

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances for XSSF.

Usage

From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java

License:Open Source License

@SuppressWarnings("unchecked")
private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass,
        String i18nPrefix) {/*from   www .  j  a v a 2s.  co m*/
    String name = I18nProperties.getCaption(i18nPrefix);
    String safeName = WorkbookUtil.createSafeSheetName(name);
    XSSFSheet sheet = workbook.createSheet(safeName);

    // Create
    XSSFTable table = sheet.createTable();
    String safeTableName = safeName.replaceAll("\\s", "_");
    table.setName(safeTableName);
    table.setDisplayName(safeTableName);

    XssfHelper.styleTable(table, 1);

    int columnCount = EntityColumn.values().length;
    int rowNumber = 0;
    // header
    XSSFRow headerRow = sheet.createRow(rowNumber++);
    for (EntityColumn column : EntityColumn.values()) {
        table.addColumn();
        String columnCaption = column.toString();
        columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase();
        headerRow.createCell(column.ordinal()).setCellValue(columnCaption);
    }

    // column width
    sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30);
    sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60);
    sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10);
    sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45);
    sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10);

    CellStyle defaultCellStyle = workbook.createCellStyle();
    defaultCellStyle.setWrapText(true);

    List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>();

    for (Field field : entityClass.getDeclaredFields()) {
        if (java.lang.reflect.Modifier.isStatic(field.getModifiers()))
            continue;
        XSSFRow row = sheet.createRow(rowNumber++);

        // field name
        XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal());
        fieldNameCell.setCellValue(field.getName());

        // value range
        XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal());
        fieldValueCell.setCellStyle(defaultCellStyle);
        Class<?> fieldType = field.getType();
        if (fieldType.isEnum()) {
            // use enum type name - values are added below
            //            Object[] enumValues = fieldType.getEnumConstants();
            //            StringBuilder valuesString = new StringBuilder();
            //            for (Object enumValue : enumValues) {
            //               if (valuesString.length() > 0)
            //                  valuesString.append(", ");
            //               valuesString.append(((Enum) enumValue).name());
            //            }
            //            fieldValueCell.setCellValue(valuesString.toString());
            fieldValueCell.setCellValue(fieldType.getSimpleName());
            if (!usedEnums.contains(fieldType)) {
                usedEnums.add((Class<Enum<?>>) fieldType);
            }
        } else if (EntityDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (ReferenceDto.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", ""));
        } else if (String.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("text"));
        } else if (Date.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("date"));
        } else if (Number.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(I18nProperties.getCaption("number"));
        } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) {
            fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString());
        }

        // caption
        XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal());
        captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), ""));

        // description
        XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal());
        descriptionCell.setCellStyle(defaultCellStyle);
        descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), ""));

        // required
        XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal());
        if (field.getAnnotation(Required.class) != null)
            requiredCell.setCellValue(true);

        // diseases
        XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal());
        diseasesCell.setCellStyle(defaultCellStyle);
        Diseases diseases = field.getAnnotation(Diseases.class);
        if (diseases != null) {
            StringBuilder diseasesString = new StringBuilder();
            for (Disease disease : diseases.value()) {
                if (diseasesString.length() > 0)
                    diseasesString.append(", ");
                diseasesString.append(disease.toShortString());
            }
            diseasesCell.setCellValue(diseasesString.toString());
        } else {
            diseasesCell.setCellValue("All");
        }

        // outbreak
        XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal());
        if (field.getAnnotation(Outbreaks.class) != null)
            outbreakCell.setCellValue(true);
    }

    AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0),
            new CellReference(rowNumber - 1, columnCount - 1));
    table.setCellReferences(reference);
    table.getCTTable().addNewAutoFilter();

    for (Class<Enum<?>> usedEnum : usedEnums) {
        rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum);
    }

    return sheet;
}

From source file:foodbankyfs.FbMainFx.java

private List<String[]> saveSpreadsheetData() {

    // Copy spreadsheet contents into memory
    try {//www. ja  v  a  2 s. co m

        // Initialize xls reading objects
        FileInputStream fileInputStream = new FileInputStream(spreadsheet);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet worksheet = workbook.getSheet(FbConstants.SHEET_NAME);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        List<String[]> tmpData = new ArrayList();

        // Save XSSF objects for rewrite
        wrksheet = worksheet;
        wrkbook = workbook;

        // Iterate through all rows in the sheet
        for (int rowNum = FbConstants.DATA_ROW_START_INDEX; rowNum < worksheet.getLastRowNum(); rowNum++) {

            // Initialize array that will store cell contents
            String values[] = new String[FbConstants.NUMBER_OF_COLUMNS];
            XSSFRow row = worksheet.getRow(rowNum);

            // Iterate through cells in each row and store values to an array
            for (int cellNum = 0; cellNum < FbConstants.NUMBER_OF_COLUMNS; cellNum++) {
                XSSFCell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK);

                String value = "";

                if (cell != null) {

                    if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateInCell(cell);
                    }
                    // If cell type is numeric convert the number value to a string
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                        double tmpVal = cell.getNumericCellValue();
                        value = String.format("%.0f", tmpVal);
                    }
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        value = cell.getStringCellValue().toLowerCase();

                    }

                }

                // If a cell row has an empty ID do not include it in data
                if (cellNum == 0 && value.equals("")) {
                    break;
                }

                // Initialize value to 0 if cell is empty
                if (value.isEmpty()) {

                    // If value is from email or notes field then put empty instead
                    if (cellNum == FbConstants.EMAIL_FIELD || cellNum == FbConstants.NOTES_FIELD) {
                        value = "empty";
                    } else {
                        value = "0";
                    }

                }

                // Store value in array
                values[cellNum] = value;

            }

            // Store array of values in list
            tmpData.add(values);

        }

        return tmpData;

    } catch (IOException e) {
        System.err.println(e);
    }

    return null;
}

From source file:fyp.POI.POI.java

public static void main(String[] args) throws Exception {
    FileInputStream fis = new FileInputStream(new File("FA_AAX.xlsx"));
    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);//w ww.  java2s .c  om
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    CellReference cr = new CellReference("C4");
    Row row = sheet.getRow(cr.getRow());
    Cell cell = row.getCell(cr.getCol());
    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        boolean truefalse = cellValue.getBooleanValue();
        System.out.println("Boolean" + truefalse);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        double NumericCheck = cellValue.getNumberValue();
        if (NumericCheck % 1 == 0) {
            int Integer = (int) NumericCheck;
            System.out.println("Integer" + Integer);
        } else {
            double Dbl = NumericCheck;
            System.out.println("Double" + Dbl);
        }
        break;

    case Cell.CELL_TYPE_STRING:
        String str = cellValue.getStringValue();
        System.out.println("String" + str.substring(3));
        break;

    case Cell.CELL_TYPE_BLANK:
        System.out.println("Blank");
        break;

    case Cell.CELL_TYPE_ERROR:
        break;
    }
    fis.close();
}

From source file:fyp.POI.POIFunction.java

public double poiToGetDbl(String FileAddress, String CellDetails, int SheetNum) throws NullPointerException {
    double result = 0.0;
    try {//ww w  . ja  v  a 2  s .  c  o m
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            //System.out.println("CELL "+ cell);
            checkNullNumeric(cell);
            CellValue cellValue = evaluator.evaluate(cell);

            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                result = 0.0;
                break;

            case Cell.CELL_TYPE_ERROR:
                result = 0.0;
                break;

            case Cell.CELL_TYPE_NUMERIC:
                result = cellValue.getNumberValue();
                break;

            }
        } catch (NullPointerException n) {
            result = 0.0;
        }
        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return result;
}

From source file:fyp.POI.POIFunction.java

public double poiToGetInt(String FileAddress, String CellDetails, int SheetNum) throws FileNotFoundException {
    double result = 0.0;

    try {//from   w  w w. java 2 s.  c  om
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            checkNullNumeric(cell);
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                result = cellValue.getNumberValue();
                break;

            case Cell.CELL_TYPE_BLANK:
                result = 0;
                break;

            case Cell.CELL_TYPE_ERROR:
                result = 0;
                break;
            }
        } catch (NullPointerException n) {
            result = 0;
        }

        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return (int) result;
}

From source file:fyp.POI.POIFunction.java

public String poiToGetString(String FileAddress, String CellDetails, int SheetNum)
        throws FileNotFoundException {
    String result = "NOTFOUND";
    try {/* www .  j  av a  2s.c  o m*/
        FileInputStream fis = new FileInputStream(new File(FileAddress));
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(fis);
        } catch (IOException e) {
            System.out.print("Error detected: " + e);
        }
        XSSFSheet sheet = wb.getSheetAt(SheetNum);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        try {
            CellReference cr = new CellReference(CellDetails);
            Row row = sheet.getRow(cr.getRow());
            Cell cell = row.getCell(cr.getCol());
            checkNullString(cell);
            CellValue cellValue = evaluator.evaluate(cell);

            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                result = cellValue.getStringValue();
                break;

            case Cell.CELL_TYPE_BLANK:
                result = " ";
                break;

            case Cell.CELL_TYPE_ERROR:
                result = " ";
                break;
            }
        } catch (NullPointerException n) {
            result = " ";
        }
        fis.close();
    } catch (IOException e) {
        System.out.print("Error detected: " + e);
    }
    return result;
}

From source file:helpers.Excel.ExcelDataFormat.java

private Object unmarshalXLSX(Exchange exchng, InputStream in) throws Exception {

    XSSFWorkbook workbook = new XSSFWorkbook(in);
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    //Get first sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    if (importType != ImportType.FORMATTED) {
        return marshalAsArray(sheet.iterator());
    } else {/*from w w w.  ja  v a 2  s .  c om*/
        OneExcel excel = new OneExcel();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator);
            logger.info("Loading sheet:" + i);
            logger.info("Data:" + onesheet.data.size());
            if (onesheet.data.size() > 0)
                excel.sheets.add(onesheet);
        }
        logger.info("Total sheets:" + excel.sheets.size());

        ArrayList<HashMap<String, Object>> resu = excel.GenerateResult();
        HashMap<String, Object> mappings = excel.GenerateMappings();

        exchng.getOut().setHeader("mappings", mappings);
        exchng.getOut().setHeader("xlsdata", resu);

        return resu;

    }
}

From source file:icalendarconverter.ExcelConverter.java

public List<ScheduleClass> readExcel() throws FileNotFoundException, IOException {
    ArrayList<ScheduleClass> scheduleList = new ArrayList<>();

    FileInputStream fis = new FileInputStream(pathFile);

    XSSFWorkbook wb = new XSSFWorkbook(fis);
    XSSFSheet sheet = wb.getSheetAt(0);//from  w w w.  jav  a 2 s .  c om
    Iterator<Row> rowIterator = sheet.iterator();

    CellRangeAddress add;
    int colNoIdx = 0;
    ArrayList<String> dosen = new ArrayList<>();
    ArrayList<Integer> idxDosen = new ArrayList<>();
    ArrayList<Integer> colDosen = new ArrayList<>();
    ArrayList<String> location = new ArrayList<>();
    int idxNumber = 0;
    ArrayList<Integer> locationIdx = new ArrayList<>();
    outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) {
        row = sheet.getRow(j);
        for (int f = 0; f < row.getLastCellNum(); f++) {
            Cell cell = row.getCell(f);
            if (cell.getCellType() == Cell.CELL_TYPE_STRING
                    && cell.getStringCellValue().equalsIgnoreCase("No.")) {
                rowNoIdx = j;
                colNoIdx = cell.getColumnIndex();
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING
                    && cell.getStringCellValue().equalsIgnoreCase("Nama Mata Kuliah")) {
                colMatkulIdx = cell.getColumnIndex();
                break outerloop;
            }

        }
    }
    //System.out.println("col matkul = "+colMatkulIdx);
    System.out.println("sheet = " + sheet.getLastRowNum());
    outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) {
        outerloop: for (int j = 0; j < row.getLastCellNum(); j++) {
            row = sheet.getRow(i);
            if (row == null) {
                i = sheet.getLastRowNum();
                break outerloop2;
            }
            Cell cell = row.getCell(j);
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3
                    && cell.getCellType() != Cell.CELL_TYPE_BLANK
                    && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) {
                i = sheet.getLastRowNum();
                break outerloop2;
            }
            if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3
                    && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                i = i + 1;
                break outerloop;
            }

            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) {
                if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                    i = i + 1;
                    break outerloop;
                }
                String delims = "[,. ]";
                String[] sumary = cell.getStringCellValue().split(delims);
                for (int l = 0; l < sumary.length; l++) {
                    if (sumary[l].equalsIgnoreCase("Mrt")) {
                        sumary[l] = "3";
                    }
                    if (sumary[l].equalsIgnoreCase("Okt")) {
                        sumary[l] = "10";
                    }
                    if (sumary[l].equalsIgnoreCase("`16")) {
                        sumary[l] = "2016";
                    }
                }

                lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]),
                        Integer.parseInt(sumary[2]));
                // System.out.println("LC = "+lc);

                //                        sp = new SimpleDateFormat("EEEE, MMMM d, yyyy");
                //                        String b = sumary[3] + "/" + sumary[2] + "/" + sumary[5];
                //                        date = new Date(b);
                //System.out.println(sp.format(date));
            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) {
                if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) {
                    i = i + 1;
                    break outerloop;
                } else {
                    if (cell.getStringCellValue().equalsIgnoreCase("Shift 1")
                            || cell.getStringCellValue().equalsIgnoreCase("Shift 2")) {
                        CellReference cr = new CellReference(cell.getRowIndex() + 1, cell.getColumnIndex());
                        Row row2 = sheet.getRow(cr.getRow());
                        Cell c = row2.getCell(cr.getCol());
                        String delimsJam = "[-]";
                        String[] arrJam = c.getStringCellValue().split(delimsJam);
                        for (int k = 0; k < arrJam.length; k++) {
                            arrJam[k] = arrJam[k].replace('.', ':');
                        }
                        //                                indoFormatter = DateTimeFormatter
                        //                                        .ofLocalizedTime(FormatStyle.SHORT)
                        //                                        .withLocale(Locale.getDefault());
                        //System.out.println("I3 = " + i);
                        lt = LocalTime.parse(arrJam[0]);
                        //System.out.println(lt+"-"+lt.plusHours(2)); 

                    } else {
                        String delimsJam = "[-]";
                        String[] arrJam = cell.getStringCellValue().split(delimsJam);
                        for (int k = 0; k < arrJam.length; k++) {
                            arrJam[k] = arrJam[k].replace('.', ':');
                        }
                        //                                indoFormatter = DateTimeFormatter
                        //                                        .ofLocalizedTime(FormatStyle.SHORT)
                        //                                        .withLocale(Locale.getDefault());
                        //System.out.println("I3 = " + i);
                        lt = LocalTime.parse(arrJam[0]);
                        //System.out.println(lt+"-"+lt.plusHours(2)); 
                    }

                }

            }
            if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == colMatkulIdx) {
                subject = cell.getStringCellValue();
                //System.out.println("Subject = "+subject);
            }

            if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colMatkulIdx + 1
                    && cell.getColumnIndex() < row.getLastCellNum()) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    //                        location.add(String.valueOf((int)cell.getNumericCellValue()));
                    //                        locationIdx.add(cell.getColumnIndex());
                }
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().contains(":")) {
                        String[] splt = cell.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add(splt2[l].trim());
                            location.add("Lab");
                            //System.out.println(splt2[l] + "= lab");
                        }
                    } else {
                        CellReference cr = new CellReference(1, cell.getColumnIndex());
                        Row row2 = sheet.getRow(cr.getRow());
                        Cell c = row2.getCell(cr.getCol());
                        if (!cell.getStringCellValue().isEmpty()) {
                            dosen.add(cell.getStringCellValue().trim());
                            location.add(String.valueOf((int) c.getNumericCellValue()).trim());
                        }

                        //System.out.print(cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " ");
                    }

                }
                if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) {
                    CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex());
                    Row row2 = sheet.getRow(cr.getRow());
                    Cell c = row2.getCell(cr.getCol());
                    CellReference cr2 = new CellReference(1, cell.getColumnIndex());
                    Row row3 = sheet.getRow(cr2.getRow());
                    Cell c2 = row3.getCell(cr2.getCol());
                    if (c.getStringCellValue().contains(":")) {
                        String[] splt = c.getStringCellValue().split(":");
                        String[] splt2 = splt[1].split(",");
                        for (int l = 0; l < splt2.length; l++) {
                            dosen.add("".trim());
                            location.add("");
                            //System.out.println(splt2[l] + "= lab");
                        }
                    } else {
                        if (!c.getStringCellValue().isEmpty()) {
                            dosen.add("");
                            location.add("");
                        }

                        //System.out.print(c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " ");
                    }
                }
                //                   scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j)));
            }
            //                System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(i)+",location = "+location.get(i));
            //                scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j)));

        }

        for (int j = 0; j < dosen.size(); j++) {
            //System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(j)+",location = "+location.get(j));
            scheduleList
                    .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j)));
        }
        dosen.clear();
        location.clear();

    }

    return mergeringList(scheduleList);
}

From source file:icalendarconverter.ReadExcel.java

public static void main(String[] args) throws Exception {
    File src = new File(
            "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx");
    //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx");
    FileInputStream fis = new FileInputStream(src);
    XSSFWorkbook wb = new XSSFWorkbook(fis);

    XSSFSheet sheet1 = wb.getSheetAt(0);
    Iterator<Row> rowIterator = sheet1.iterator();

    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    // suppose your formula is in A3
    CellReference cellReference = new CellReference("A3");

    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            CellValue cellValue = evaluator.evaluate(cell);
            switch (evaluator.evaluateInCell(cell).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print((int) cell.getNumericCellValue() + " \t\t ");
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue() + " \t\t ");
                break;
            }/*from  ww w .j av  a  2  s  .c o m*/
        }
        System.out.println();
    }
    fis.close();
}

From source file:ke.co.tawi.babblesms.server.servlet.export.excel.inbox.ExportExcel.java

License:Open Source License

/**
 * Returns MS Excel file of the data specified for exporting.
 * @param List<IncomingLog>//from  ww w . ja va2s  .  com
 * Method create excelSheets and sends them
 ****/
public void createExcelSheets(List<IncomingLog> InLog) throws IOException {
    List<Phone> phoneList;
    //String cont = null;

    XSSFWorkbook xf = new XSSFWorkbook();
    XSSFCreationHelper ch = xf.getCreationHelper();

    XSSFSheet s = xf.createSheet();
    //create the first row
    XSSFRow r1 = s.createRow(0);
    XSSFCell c11 = r1.createCell(0);
    c11.setCellValue(ch.createRichTextString("*"));
    XSSFCell c12 = r1.createCell(1);
    c12.setCellValue(ch.createRichTextString("Message"));
    XSSFCell c13 = r1.createCell(2);
    c13.setCellValue(ch.createRichTextString("Source"));
    XSSFCell c14 = r1.createCell(3);
    c14.setCellValue(ch.createRichTextString("Destination"));
    XSSFCell c15 = r1.createCell(4);
    c15.setCellValue(ch.createRichTextString("Network"));
    XSSFCell c16 = r1.createCell(5);
    c16.setCellValue(ch.createRichTextString("Time (" + timezoneFormatter.format(new Date()) + ") Time Zone"));
    XSSFCell c17 = r1.createCell(6);
    c17.setCellValue(ch.createRichTextString("Message Id"));

    int i = 1;
    //create other rows
    for (IncomingLog log : InLog) {
        phoneList = phnDAO.getPhones(log.getOrigin());

        XSSFRow r = s.createRow(i);
        //row number
        XSSFCell c1 = r.createCell(0);
        c1.setCellValue(i + pageno);

        //get message  
        XSSFCell c2 = r.createCell(1);
        c2.setCellValue(ch.createRichTextString(log.getMessage()));

        //get phone numbers
        XSSFCell c3 = r.createCell(2);
        if (phoneList.size() > 0) {
            for (Phone phone : phoneList) {
                Contact contacts = ctDAO.getContact(phone.getContactUuid());
                c3.setCellValue(ch.createRichTextString(contacts.getName()));
            }
        } else {
            c3.setCellValue(ch.createRichTextString(log.getOrigin()));
        }

        //get destination   
        XSSFCell c4 = r.createCell(3);
        c4.setCellValue(ch.createRichTextString(log.getDestination()));

        //get network name    
        XSSFCell c5 = r.createCell(4);
        c5.setCellValue(ch.createRichTextString(networkHash.get(log.getNetworkUuid())));

        //get date 
        XSSFCell c6 = r.createCell(5);
        c6.setCellValue(ch.createRichTextString("" + dateFormatter.format(log.getLogTime())));

        //get message id
        XSSFCell c7 = r.createCell(6);
        c7.setCellValue(ch.createRichTextString(log.getUuid()));
        i++;

    }
    xf.write(out);
    out.flush();
    out.close();
}