Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static void createBoxContentsSpreadsheet(File outpath, ArrayList<String> array) throws IOException {
    InputStream in = null;//  ww w. ja va  2 s . c o m
    in = FormUtils.class.getResourceAsStream("/forms/ods/box_input.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("Input");
        FileOutputStream fileOut = new FileOutputStream(outpath);

        String boxInfo = array.remove(0);
        String boxName = boxInfo.split(":")[0];
        String boxAlias = boxInfo.split(":")[1];
        XSSFRow row1 = sheet.createRow(1);
        XSSFCell cellA = row1.createCell(0);
        cellA.setCellValue(boxName);
        XSSFCell cellB = row1.createCell(1);
        cellB.setCellValue(boxAlias);

        int i = 4; // start on row 4 of the sheet
        for (String item : array) {
            String position = item.split(":")[0];
            String name = item.split(":")[1];
            String alias = item.split(":")[2];

            XSSFRow row = sheet.createRow(i);
            cellA = row.createCell(0);
            cellA.setCellValue(position);
            cellB = row.createCell(1);
            cellB.setCellValue(name);
            XSSFCell cellC = row.createCell(2);
            cellC.setCellValue(alias);
            i++;
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }
}

From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java

License:Apache License

public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception {

    this.wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    XSSFSheet sheet = wb.createSheet(sheetTitle);
    XSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);/*  w  w  w  . ja  v  a 2s  . c o  m*/
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //header row
    XSSFRow headerRow = sheet.createRow(0);
    //headerRow.setHeightInPoints(40);

    XSSFCell headerCell;
    for (int j = 0; j < titles.length; j++) {
        headerCell = headerRow.createCell(j);
        headerCell.setCellValue(titles[j]);
        //headerCell.setCellStyle(styles.get("header"));
    }

    // data rows
    // Create a row and put some cells in it. Rows are 0 based.
    // Then set value for that created cell
    for (int k = 0; k < tableData.length; k++) {
        XSSFRow row = sheet.createRow(k + 1); // data starts from row 1   
        for (int l = 0; l < tableData[k].length; l++) {
            XSSFCell cell = row.createCell(l);
            String cellStr = null;

            try {
                cellStr = tableData[k][l].toString();
            } catch (Exception e) {
                cellStr = "";
            }

            //System.out.println("cell " + l + ":  " + cellStr);

            // make hyperlink in cell
            if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) {

                //need to encode URI for this version of ExcelWorkBook
                cellStr = URIUtil.encodePath(cellStr, "UTF-8");

                cellStr = cellStr.replace("%3F", "?"); // so that url link would work

                //System.out.println("cellStr: " + cellStr);
                XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);

                url_link.setAddress(cellStr);

                cell.setCellValue(cellStr);
                cell.setHyperlink(url_link);
            } else {
                cell.setCellValue(cellStr);
            }

            //System.out.println((String)tableData[k][l]);
        }
    }
}

From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.xssf.impl.WorkbookXSSFImpl.java

License:Open Source License

public void visit(SetCellValue setCellValue) {
    XSSFSheet xssfSheet = workbook.getSheet(setCellValue.getSheet().getName());
    XSSFRow xssfRow = xssfSheet.getRow(setCellValue.getRow());
    if (xssfRow == null && setCellValue.getNewValue() != null) {
        xssfRow = xssfSheet.createRow(setCellValue.getRow());
    }//from   w  w w.java2 s  . c o m
    XSSFCell xssfCell = xssfRow.getCell(setCellValue.getCol());
    if (xssfCell == null && setCellValue.getNewValue() != null) {
        xssfCell = xssfRow.createCell(setCellValue.getCol());
    }
    if (xssfCell != null) {
        if (setCellValue.getNewValue() != null) {
            xssfCell.setCellValue(new XSSFRichTextString(setCellValue.getNewValue().toString()));
        } else {
            xssfRow.removeCell(xssfCell);
        }
    }
}

From source file:util.ExcelConverter.java

public static File createXlsx(String[] header, String[][] data, String path) {

    try {/*from  w  w w . j  a v  a 2 s .c  o  m*/
        XSSFWorkbook xwb = new XSSFWorkbook();
        XSSFSheet sheet = xwb.createSheet();

        CellStyle cellStyle = xwb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyle.setAlignment(CellStyle.VERTICAL_TOP);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setWrapText(false);

        Font bold = xwb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        bold.setFontHeightInPoints((short) 10);

        CellStyle cellStyleHeader = xwb.createCellStyle();
        cellStyleHeader.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyleHeader.setAlignment(CellStyle.VERTICAL_TOP);
        cellStyleHeader.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyleHeader.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyleHeader.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyleHeader.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyleHeader.setFont(bold);
        cellStyleHeader.setWrapText(false);

        XSSFRow row;
        Cell cell;

        //header
        row = sheet.createRow(0);
        for (int i = 0; i < header.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(cellStyleHeader);
            cell.setCellValue(header[i]);
        }

        int colCount = header.length;
        int no = 1;

        for (String[] obj : data) {
            row = sheet.createRow(no);
            for (int i = 0; i < colCount; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(obj[i]);
            }
            no++;
        }

        for (int i = 0; i < header.length; i++) {
            sheet.autoSizeColumn(i);
        }

        File newFile = new File(path);
        try (FileOutputStream fileOut = new FileOutputStream(path)) {
            xwb.write(fileOut);
        }

        return newFile;
    } catch (IOException e) {
        return null;
    }
}

From source file:Utilities.BatchInDJMSHelper.java

private void legendBuilder(XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Row row1;/*from  w  ww .  ja v a2  s. c o  m*/
    Row row2;
    Row row3;
    Row row4;
    System.out.println("BatchInDJMSHelper.java: Line number in xlsx" + mySheet.getPhysicalNumberOfRows());
    // Create a row and put some cells in it.
    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y) {
        row1 = mySheet.getRow(GlobalVar.LEGEND_Y);
    } else {
        row1 = mySheet.createRow(GlobalVar.LEGEND_Y);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 1) {
        row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1);
    } else {
        row2 = mySheet.createRow(GlobalVar.LEGEND_Y + 1);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 2) {
        row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2);
    } else {
        row3 = mySheet.createRow(GlobalVar.LEGEND_Y + 2);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 3) {
        row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3);
    } else {
        row4 = mySheet.createRow(GlobalVar.LEGEND_Y + 3);
    }
    // Row row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1);
    //        Row row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2);
    //        Row row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3);

    int col1 = GlobalVar.LEGEND_X;
    int col2 = GlobalVar.LEGEND_X + 1;
    int col3 = GlobalVar.LEGEND_X + 2;
    int col4 = GlobalVar.LEGEND_X + 3;

    //// row 1
    CellStyle style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.ETS_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Cell cell = row1.createCell(col1);
    cell.setCellValue("ETS");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.BAD_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col2);
    cell.setCellValue("Bad");
    cell.setCellStyle(style);
    style = myWorkBook.createCellStyle();

    style.setFillForegroundColor(GlobalVar.OVERLAP_LV_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col3);
    cell.setCellValue("Overlap Lv");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.DUPLICATE_LV_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col4);
    cell.setCellValue("Duplicate Lv");
    cell.setCellStyle(style);

    //            case INPROCESSING_ERR: return INPROCESSING_COLOR;
    //            case AFTER_PCS_ERR: return AFTER_PCS_COLOR;     
    //// row 2
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.WRONG_SSN_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col1);
    cell.setCellValue("Wrong SSN");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.DUPLICATE_CTRL_NUM_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col2);
    cell.setCellValue("Duplicate CtrlNum");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.INPROCESSING_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col3);
    cell.setCellValue("Inprocessing");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.AFTER_PCS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col4);
    cell.setCellValue("After PCS");
    cell.setCellStyle(style);

    // row3
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.IN_CYCLE_DUPLICATE_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col1);
    cell.setCellValue("In-cycle duplicates/Invalid first five");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col2);
    cell.setCellValue("Auditor deleted");
    cell.setCellStyle(style);

}

From source file:Utility.CSV_File_Generator.java

public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) {
    FileOutputStream out = null;/*from  w  w  w  . jav  a  2 s  .  c  om*/
    try {

        out = new FileOutputStream(new File(file_details("Excel_Traffic_Rows")));

        int col_index;
        //Create blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        XSSFSheet intermediate = workbook.createSheet("Test Data");
        //Create row object
        Row row;
        XSSFRow intermediate_row;

        XSSFRow actual_row = original_sheet.getRow(index);

        //This data needs to be written (Object[])
        TreeMap<String, TreeMap<String, Cell>> row_map = new TreeMap<String, TreeMap<String, Cell>>();

        if (index == 0) {
            TreeMap<String, Cell> cols = new TreeMap<String, Cell>();

            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            int i = 1;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cols.put(Integer.toString(i++), cell);
            }
            row_map.put("0", cols);

            Set<String> keyid = row_map.get("0").keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = cols.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            row_map.clear();
        } else {
            int count = 0;
            while (count < index) {
                Iterator<Row> rowIterator = resultSheet.iterator();
                while (rowIterator.hasNext()) {
                    //Reading a row from the existing result sheet
                    TreeMap<String, Cell> data_row = new TreeMap<String, Cell>();
                    row = rowIterator.next();
                    Iterator<Cell> cell = row.cellIterator();
                    col_index = 0;
                    while (cell.hasNext()) {
                        Cell c = cell.next();
                        data_row.put(Integer.toString(col_index++), c);
                    }

                    row_map.put(Integer.toString(count), data_row);
                    count++;
                }
                //writing the row read into the new workbook(intermediate)
                Set<String> keyid = row_map.keySet();
                for (String key : keyid) {
                    int column_counter = 0;
                    intermediate_row = intermediate.createRow(Integer.parseInt(key));
                    TreeMap<String, Cell> map = row_map.get(key);
                    Set<String> row_data = map.keySet();
                    for (String cell_data : row_data) {
                        Cell original = map.get(cell_data);
                        Cell new_cell = intermediate_row.createCell(column_counter++);
                        switch (original.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            new_cell.setCellValue(original.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            new_cell.setCellValue(original.getStringCellValue());
                            break;
                        }
                    }
                }
            }
            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            TreeMap<String, Cell> required_data = new TreeMap<String, Cell>();

            int i = 0;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                required_data.put(Integer.toString(i++), cell);
            }
            row_map.put(Integer.toString(index), required_data);

            required_data = row_map.get(Integer.toString(index));

            Set<String> keyid = required_data.keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = required_data.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            out.flush();
            row_map.clear();
        }
        out.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Traffic Data is: " + index + " row.");
}

From source file:vd10_workbook.AbilityManagement.java

public void createWorkSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("kha_nang");
    int startRow = 0;
    XSSFRow row = sheet.createRow((short) startRow);

    //== THE TITLE ==//
    //SET HEIGHT OF ROW 2 (in excel)
    row.setHeight((short) 500);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Kh nng lm vic");

    //MEARGING CELLS 
    //this statement for merging cells
    CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based)
            startRow, //last row (0-based)
            0, //first column (0-based)
            2 //last column (0-based)
    );//  w  w w. j  av a 2  s  .c om
    sheet.addMergedRegion(cellRangeAddress);

    // Center Align Cell Contents 
    XSSFCellStyle align = workbook.createCellStyle();
    align.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cell.setCellStyle(align);

    //set border
    this.setRegionBoder(cellRangeAddress, workbook, sheet);

    //==THE LABELS ==//
    //STT
    row = sheet.createRow((short) startRow + 1);
    row.setHeight((short) 400);
    cell = (XSSFCell) row.createCell((short) 0);

    cell.setCellValue("STT");
    this.setThickBorder(cell, workbook);
    this.setBackGroundColor(cell, workbook);

    //ID_NHAN_VIEN
    sheet.setColumnWidth(1, 5000);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("ID Nhn vin");
    this.setThickBorder(cell, workbook);
    this.setBackGroundColor(cell, workbook);

    //ID_NGOAI_NGU
    sheet.setColumnWidth(2, 5000);
    cell = (XSSFCell) row.createCell((short) 2);
    cell.setCellValue("ID Ngoi ng");
    this.setThickBorder(cell, workbook);
    this.setBackGroundColor(cell, workbook);

    //fill out the rows
    for (int i = 0; i < this.list.size(); i++) {
        row = sheet.createRow((short) startRow + 2 + i);
        cell = (XSSFCell) row.createCell((short) 0);
        this.setThinBorder(cell, workbook);
        cell.setCellValue(i + 1);

        cell = (XSSFCell) row.createCell((short) 1);
        this.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getEmployeeID());

        cell = (XSSFCell) row.createCell((short) 2);
        this.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getLanguageID());

    }
}

From source file:vd10_workbook.AssignedVoteManagement.java

public void createWorkSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("phieu_phan_cong");
    int startRow = 0;
    XSSFRow row = sheet.createRow((short) startRow);

    //== THE TITLE ==//
    //SET HEIGHT OF ROW 2 (in excel)
    row.setHeight((short) 500);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Phiu phn cng");

    //MEARGING CELLS 
    //this statement for merging cells
    CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based)
            startRow, //last row (0-based)
            0, //first column (0-based)
            4 //last column (0-based)
    );//from w ww. j  a va  2  s  . co  m
    sheet.addMergedRegion(cellRangeAddress);
    // Center Align Cell Contents 
    XSSFCellStyle align = workbook.createCellStyle();
    align.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cell.setCellStyle(align);

    //set border
    AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet);

    //==THE LABELS ==//
    //STT
    row = sheet.createRow((short) startRow + 1);
    row.setHeight((short) 400);
    cell = (XSSFCell) row.createCell((short) 0);

    cell.setCellValue("STT");
    AbilityManagement.setThickBorder(cell, workbook);

    //Ngy bt u
    sheet.setColumnWidth(1, 5000);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("Ngy bt u");
    AbilityManagement.setThickBorder(cell, workbook);

    //S ngy
    sheet.setColumnWidth(2, 5000);
    cell = (XSSFCell) row.createCell((short) 2);
    cell.setCellValue("S ngy");
    AbilityManagement.setThickBorder(cell, workbook);

    //ID nhn vin
    sheet.setColumnWidth(3, 5000);
    cell = (XSSFCell) row.createCell((short) 3);
    cell.setCellValue("ID nhn vin");
    AbilityManagement.setThickBorder(cell, workbook);

    //ID loi cng vic
    sheet.setColumnWidth(4, 5000);
    cell = (XSSFCell) row.createCell((short) 4);
    cell.setCellValue("ID loi cng vic");
    AbilityManagement.setThickBorder(cell, workbook);

    //fill out the rows
    for (int i = 0; i < this.list.size(); i++) {
        row = sheet.createRow((short) startRow + 2 + i);
        cell = (XSSFCell) row.createCell((short) 0);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(i + 1);

        cell = (XSSFCell) row.createCell((short) 1);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getStartDate().toString());

        cell = (XSSFCell) row.createCell((short) 2);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getNumberOfdate());

        cell = (XSSFCell) row.createCell((short) 3);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getEmployeeID());

        cell = (XSSFCell) row.createCell((short) 4);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getWorkTypeID());
    }

    //== calculate sum of days ==//
    row = sheet.createRow((short) startRow + 2 + this.list.size());
    cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Tng");
    //merge cells at column 1 & 2
    cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based)
            startRow + 2 + this.list.size(), //last row (0-based)
            0, //first column (0-based)
            1 //last column (0-based)
    );
    sheet.addMergedRegion(cellRangeAddress);
    //set border
    RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
    RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
    RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
    RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
    cell = row.createCell((short) 2);
    cell.setCellFormula("SUM(C" + (startRow + 1 + 2) + ":C" + (startRow + 2 + this.list.size()) + ")");
    AbilityManagement.setThickBorder(cell, workbook);
}

From source file:vd10_workbook.Company.java

public void createWorkSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("cong_ty");
    int startRow = 0;
    XSSFRow row = sheet.createRow((short) startRow);

    //== THE TITLE ==//
    //SET HEIGHT OF ROW 2 (in excel)
    row.setHeight((short) 500);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("Thng tin cng ty");

    //MEARGING CELLS 
    //this statement for merging cells
    CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based)
            startRow, //last row (0-based)
            0, //first column (0-based)
            5 //last column (0-based)
    );//  w ww.  j ava2 s .co  m
    sheet.addMergedRegion(cellRangeAddress);
    // Center Align Cell Contents 
    XSSFCellStyle align = workbook.createCellStyle();
    align.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cell.setCellStyle(align);

    //set border
    //        AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet);

    //==THE LABELS ==//
    //STT
    row = sheet.createRow((short) startRow + 1);
    row.setHeight((short) 400);
    cell = (XSSFCell) row.createCell((short) 0);

    cell.setCellValue("STT");
    AbilityManagement.setThickBorder(cell, workbook);

    //Tn cng ty
    sheet.setColumnWidth(1, 5000);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("Tn");
    AbilityManagement.setThickBorder(cell, workbook);

    //?in thoi
    sheet.setColumnWidth(2, 5000);
    cell = (XSSFCell) row.createCell((short) 2);
    cell.setCellValue("?in thoi");
    AbilityManagement.setThickBorder(cell, workbook);

    //?a ch
    sheet.setColumnWidth(3, 10000);
    cell = (XSSFCell) row.createCell((short) 3);
    cell.setCellValue("?a ch");
    AbilityManagement.setThickBorder(cell, workbook);

    //Tui ti thiu
    sheet.setColumnWidth(4, 5000);
    cell = (XSSFCell) row.createCell((short) 4);
    cell.setCellValue("Tui ti thiu");
    AbilityManagement.setThickBorder(cell, workbook);

    //Tui ti a
    sheet.setColumnWidth(5, 5000);
    cell = (XSSFCell) row.createCell((short) 5);
    cell.setCellValue("Tui ti a");
    AbilityManagement.setThickBorder(cell, workbook);

    //== fill out the information ==//
    row = sheet.createRow((short) startRow + 2);
    cell = (XSSFCell) row.createCell((short) 0);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(1);

    //== content ==//
    //Tn
    cell = (XSSFCell) row.createCell((short) 1);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(this.name);

    //?in thoi
    cell = (XSSFCell) row.createCell((short) 2);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(this.phoneNumber);

    //?a ch
    cell = (XSSFCell) row.createCell((short) 3);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(this.address);

    //Tui ti thiu
    cell = (XSSFCell) row.createCell((short) 4);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(this.minAge);

    //Tui ti a
    cell = (XSSFCell) row.createCell((short) 5);
    AbilityManagement.setThinBorder(cell, workbook);
    cell.setCellValue(this.maxAge);
}

From source file:vd10_workbook.DepartmentManagement.java

public void createWorkSheet(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("don_vi");
    int startRow = 0;
    XSSFRow row = sheet.createRow((short) startRow);

    //== THE TITLE ==//
    //SET HEIGHT OF ROW 2 (in excel)
    row.setHeight((short) 500);
    XSSFCell cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("?n v");

    //MEARGING CELLS 
    //this statement for merging cells
    CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based)
            startRow, //last row (0-based)
            0, //first column (0-based)
            1 //last column (0-based)
    );//from   w ww  . j  ava  2  s . c om
    sheet.addMergedRegion(cellRangeAddress);
    // Center Align Cell Contents 
    XSSFCellStyle align = workbook.createCellStyle();
    align.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cell.setCellStyle(align);

    //set border
    AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet);

    //==THE LABELS ==//
    //STT
    row = sheet.createRow((short) startRow + 1);
    row.setHeight((short) 400);
    cell = (XSSFCell) row.createCell((short) 0);

    cell.setCellValue("STT");
    AbilityManagement.setThickBorder(cell, workbook);

    //Tn n v
    sheet.setColumnWidth(1, 5000);
    cell = (XSSFCell) row.createCell((short) 1);
    cell.setCellValue("Tn n v");
    AbilityManagement.setThickBorder(cell, workbook);

    //fill out the rows
    for (int i = 0; i < this.list.size(); i++) {
        row = sheet.createRow((short) startRow + 2 + i);
        cell = (XSSFCell) row.createCell((short) 0);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(i + 1);

        cell = (XSSFCell) row.createCell((short) 1);
        AbilityManagement.setThinBorder(cell, workbook);
        cell.setCellValue(this.list.get(i).getName().toString());

    }
}