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:com.ferid.app.classroom.statistics.StatisticsFragment.java

License:Apache License

/**
 * Converts all attendances into excel format
 *///from  w ww.  j  a v a2 s  . co m
private void convertToExcel() {
    //create workbook
    Workbook wb = new HSSFWorkbook();

    for (Classroom classroom : classroomArrayList) { //each sheet

        //create sheet
        Sheet sheet = wb.createSheet(classroom.getName());

        //header
        HashMap<String, Integer> date_column_map = new HashMap<>();
        ArrayList<String> dates = new ArrayList<>();
        int rowNumber = 0;
        int colNumber = 1;
        Row row = sheet.createRow(rowNumber);

        //dates columns
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId() && !dates.contains(attendance.getDateTime())) {

                Cell cellDate = row.createCell(colNumber);
                cellDate.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellDate.setCellValue(attendance.getDateTime());

                dates.add(attendance.getDateTime());
                date_column_map.put(attendance.getDateTime(), colNumber);

                //set width of the dates columns
                sheet.setColumnWidth(colNumber,
                        getResources().getInteger(R.integer.statistics_excel_column_width_dates));

                colNumber++;
            }
        }

        //set width of the students column
        //it is always the first column
        sheet.setColumnWidth(0, getResources().getInteger(R.integer.statistics_excel_column_width_students));

        //students list at the left column
        HashMap<Integer, Integer> student_row_map = new HashMap<>();
        ArrayList<Integer> studentIds = new ArrayList<>();
        rowNumber = 1;
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()
                    && !studentIds.contains(attendance.getStudentId())) { //another student

                row = sheet.createRow(rowNumber);

                Cell cellStudent = row.createCell(0);
                cellStudent.setCellStyle(ExcelStyleManager.getHeaderCellStyle(wb));

                cellStudent.setCellValue(attendance.getStudentName());

                studentIds.add(attendance.getStudentId());
                student_row_map.put(attendance.getStudentId(), rowNumber);

                rowNumber++;
            }
        }

        //now get column number from date columns
        //and get row number from student rows
        //match row-column pair and print into cell
        for (Attendance attendance : attendanceArrayList) {

            if (classroom.getId() == attendance.getClassroomId()) {
                rowNumber = student_row_map.get(attendance.getStudentId());
                colNumber = date_column_map.get(attendance.getDateTime());

                row = sheet.getRow(rowNumber);

                Cell cellPresence = row.createCell(colNumber);
                cellPresence.setCellStyle(ExcelStyleManager.getContentCellStyle(wb));

                cellPresence.setCellValue(attendance.getPresent());
            }
        }
    }

    if (classroomArrayList.size() > 0) {
        writeIntoFile(wb);
    }

    swipeRefreshLayout.setRefreshing(false);
}

From source file:com.firstonesoft.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else/*from w  w w.  j a  v  a 2  s.  c o  m*/
        wb = new XSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

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

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

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

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

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

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

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

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

    // Write the output to a file
    String file = "D://timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelMgr.java

License:Apache License

/**
 * workbook/*from   w w w  .  j  av a  2 s .com*/
 *
 * @param workbook
 * @param sheetname
 * @return
 */
public static <T extends ExcelSheet> T addSheet(Excel excel, String sheetname, Class<T> sheetClass) {
    T t = null;
    Workbook workbook = excel.getWorkbook();
    try {
        t = sheetClass.newInstance();
        Sheet sheet = workbook.createSheet(sheetname);
        t.setSheet(sheet);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return t;
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * workbook/*from  w  ww.j av  a2s. c  om*/
 *
 * @param workbook
 * @param sheetname
 * @return
 */
public static Sheet addSheet(Workbook workbook, String sheetname) {
    return workbook.createSheet(sheetname);
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ?Sheet?? ???//  w ww . j  a v a  2  s. c om
 *
 * @param fieldnames
 *            bean?????
 * @param list
 *            listbean??List<bean> list
 * @param sheetname
 *            ?sheet??sheetname
 * @return Sheet??
 */
public static Sheet addSheetWithData(Workbook workbook, List<String> fieldnames, List list, String sheetname) {
    if (fieldnames == null && list == null) {
        return null;
    }

    if (StringUtil.isNull(sheetname)) {
        if (logger.isEnabledFor(Priority.ERROR)) {
            logger.error("Sheet??");
        }
        return null;
    }

    Sheet sheet = workbook.createSheet(sheetname);

    Class clazz = list.get(0).getClass();

    // 
    addHeaderRow(sheet, 0, clazz, fieldnames);

    // ?
    addDataToSheet(sheet, clazz, fieldnames, list);

    // ?{???}
    return sheet;
}

From source file:com.github.camellabs.iot.cloudlet.geofencing.service.DefaultRouteService.java

License:Apache License

@Override
public byte[] exportRoutes(String client, String format) {
    try {//from w ww  .  j  av  a  2  s.  co  m
        List<List<String>> exportedRoutes = exportRoutes(client);
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("Routes report for " + client);

        for (int i = 0; i < exportedRoutes.size(); i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < exportedRoutes.get(i).size(); j++) {
                row.createCell(j).setCellValue(exportedRoutes.get(i).get(j));
            }
        }

        ByteArrayOutputStream xlsBytes = new ByteArrayOutputStream();
        workbook.write(xlsBytes);
        xlsBytes.close();
        return xlsBytes.toByteArray();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private void generateSheet(Workbook workbook, List<?> data, Class clazz, boolean isWriteHeader,
        String sheetName) throws Excel4JException {

    Sheet sheet;//from www . ja  va 2s . c om
    if (null != sheetName && !"".equals(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    Row row = sheet.createRow(0);
    List<ExcelHeader> headers = Utils.getHeaderList(clazz);
    if (isWriteHeader) {
        // 
        for (int i = 0; i < headers.size(); i++) {
            row.createCell(i).setCellValue(headers.get(i).getTitle());
        }
    }
    // ?
    Object _data;
    for (int i = 0; i < data.size(); i++) {
        row = sheet.createRow(i + 1);
        _data = data.get(i);
        for (int j = 0; j < headers.size(); j++) {
            row.createCell(j).setCellValue(
                    Utils.getProperty(_data, headers.get(j).getFiled(), headers.get(j).getWriteConverter()));
        }
    }

}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private void generateSheet(Workbook workbook, List<?> data, List<String> header, String sheetName) {

    Sheet sheet;/*w  w w. j av  a 2s.com*/
    if (null != sheetName && !"".equals(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }

    int rowIndex = 0;
    if (null != header && header.size() > 0) {
        // 
        Row row = sheet.createRow(rowIndex++);
        for (int i = 0; i < header.size(); i++) {
            row.createCell(i, CellType.STRING).setCellValue(header.get(i));
        }
    }
    for (Object object : data) {
        Row row = sheet.createRow(rowIndex++);
        if (object.getClass().isArray()) {
            for (int j = 0; j < Array.getLength(object); j++) {
                row.createCell(j, CellType.STRING).setCellValue(Array.get(object, j).toString());
            }
        } else if (object instanceof Collection) {
            Collection<?> items = (Collection<?>) object;
            int j = 0;
            for (Object item : items) {
                row.createCell(j++, CellType.STRING).setCellValue(item.toString());
            }
        } else {
            row.createCell(0, CellType.STRING).setCellValue(object.toString());
        }
    }
}

From source file:com.github.lucapino.sheetmaker.parsers.mediainfo.MediaInfoExtractor.java

private void print(String rootPath) throws Exception {
    FileFilter filter = new FileFilter() {

        @Override/*from   w w  w .  j a  v a  2s .  co m*/
        public boolean accept(File pathname) {
            String lowerCaseName = pathname.getName().toLowerCase();
            return (lowerCaseName.endsWith("ifo") && lowerCaseName.startsWith("vts"))
                    || lowerCaseName.endsWith("mkv") || lowerCaseName.endsWith("iso")
                    || lowerCaseName.endsWith("avi");
        }
    };
    // parse all the tree under rootPath
    File rootFolder = new File(rootPath);
    File[] files = rootFolder.listFiles();
    Arrays.sort(files);
    Map<File, List<File>> mediaMap = new TreeMap<>();
    for (File file : files) {
        System.out.println(file.getName());
        // name of the folder -> name of media
        List<File> fileList;
        if (file.isDirectory()) {
            fileList = recurseSubFolder(filter, file);
            if (!fileList.isEmpty()) {
                System.out.println("adding " + fileList);
                mediaMap.put(file, fileList);
            }
        } else {
            if (filter.accept(file)) {
                fileList = new ArrayList<>();
                fileList.add(file);
                System.out.println("adding " + fileList);
                mediaMap.put(file, fileList);
            }
        }
    }
    Set<File> fileNamesSet = mediaMap.keySet();
    File outputFile = new File("/home/tagliani/tmp/HD-report.xls");
    Workbook wb = new HSSFWorkbook(new FileInputStream(outputFile));
    Sheet sheet = wb.createSheet("Toshiba");

    MediaInfo MI = new MediaInfo();
    int j = 0;
    for (File mediaFile : fileNamesSet) {
        List<File> filesList = mediaMap.get(mediaFile);
        for (File fileInList : filesList) {
            List<String> audioTracks = new ArrayList<>();
            List<String> videoTracks = new ArrayList<>();
            List<String> subtitlesTracks = new ArrayList<>();
            MI.Open(fileInList.getAbsolutePath());

            String durationInt = MI.Get(MediaInfo.StreamKind.General, 0, "Duration", MediaInfo.InfoKind.Text,
                    MediaInfo.InfoKind.Name);
            System.out.println(fileInList.getName() + " -> " + durationInt);
            if (StringUtils.isNotEmpty(durationInt) && Integer.valueOf(durationInt) >= 60 * 60 * 1000) {

                Row row = sheet.createRow(j);
                String duration = MI.Get(MediaInfo.StreamKind.General, 0, "Duration/String",
                        MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                // Create a cell and put a value in it.
                row.createCell(0).setCellValue(WordUtils.capitalizeFully(mediaFile.getName()));
                if (fileInList.getName().toLowerCase().endsWith("iso")
                        || fileInList.getName().toLowerCase().endsWith("ifo")) {
                    row.createCell(1).setCellValue("DVD");
                } else {
                    row.createCell(1)
                            .setCellValue(FilenameUtils.getExtension(fileInList.getName()).toUpperCase());
                }
                row.createCell(2).setCellValue(FileUtils.byteCountToDisplaySize(FileUtils.sizeOf(mediaFile)));
                // row.createCell(3).setCellValue(fileInList.getAbsolutePath());
                row.createCell(3).setCellValue(duration);
                // MPEG-2 720x576 @ 25fps 16:9
                String format = MI.Get(MediaInfo.StreamKind.Video, 0, "Format", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(4).setCellValue(format);
                String width = MI.Get(MediaInfo.StreamKind.Video, 0, "Width", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                String height = MI.Get(MediaInfo.StreamKind.Video, 0, "Height", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(5).setCellValue(width + "x" + height);
                String fps = MI.Get(MediaInfo.StreamKind.Video, 0, "FrameRate", MediaInfo.InfoKind.Text,
                        MediaInfo.InfoKind.Name);
                row.createCell(6).setCellValue(fps);
                String aspectRatio = MI.Get(MediaInfo.StreamKind.Video, 0, "DisplayAspectRatio/String",
                        MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                row.createCell(7).setCellValue(aspectRatio);

                int audioStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Audio);
                for (int i = 0; i < audioStreamNumber; i++) {
                    String audioTitle = MI.Get(MediaInfo.StreamKind.Audio, i, "Title", MediaInfo.InfoKind.Text,
                            MediaInfo.InfoKind.Name);
                    String language = MI.Get(MediaInfo.StreamKind.Audio, i, "Language/String3",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    String codec = MI.Get(MediaInfo.StreamKind.Audio, i, "Codec", MediaInfo.InfoKind.Text,
                            MediaInfo.InfoKind.Name);
                    String channels = MI.Get(MediaInfo.StreamKind.Audio, i, "Channel(s)",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    String sampleRate = MI.Get(MediaInfo.StreamKind.Audio, i, "SamplingRate/String",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    // AC3 ITA 5.1 48.0KHz
                    StringBuilder sb = new StringBuilder();
                    if (StringUtils.isEmpty(audioTitle)) {
                        sb.append(codec).append(" ").append(language.toUpperCase()).append(" ")
                                .append(channels);
                    } else {
                        sb.append(audioTitle);
                    }
                    sb.append(" ").append(sampleRate);
                    audioTracks.add(sb.toString());
                }

                int textStreamNumber = MI.Count_Get(MediaInfo.StreamKind.Text);
                for (int i = 0; i < textStreamNumber; i++) {
                    String textLanguage = MI.Get(MediaInfo.StreamKind.Text, i, "Language/String",
                            MediaInfo.InfoKind.Text, MediaInfo.InfoKind.Name);
                    subtitlesTracks.add(textLanguage);
                }
                MI.Close();
                row.createCell(8).setCellValue(audioTracks.toString());
                row.createCell(9).setCellValue(subtitlesTracks.toString());
                j++;
            }

        }

        //            System.out.println(mediaName);
    }
    try (FileOutputStream fileOut = new FileOutputStream(outputFile)) {
        wb.write(fileOut);
    }

}

From source file:com.github.luischavez.lat.excel.Excel.java

License:Open Source License

public File generate(String groupName, LocalDateTime dateTime) {
    String datetime = dateTime.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME).replaceAll("-", "_")
            .replaceAll(":", "_").replaceAll("\\.", "_");
    String path = System.getProperty("user.dir").concat("/excel/").concat(datetime).concat("/");

    File file = new File(path);
    if (!file.exists()) {
        file.mkdirs();//  w ww  . ja v a  2 s.c om
    }

    file = new File(path.concat(groupName).concat(".xls"));

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(groupName);
    this.setContent(workbook, sheet, groupName);
    for (int i = 0; i < 11; i++) {
        sheet.autoSizeColumn(i);
    }
    try (FileOutputStream outputStream = new FileOutputStream(file)) {
        workbook.write(outputStream);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    }

    return file;
}