Example usage for org.apache.poi.xssf.usermodel XSSFRow createCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. createCell

Introduction

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

Prototype

@Override
public XSSFCell createCell(int columnIndex)

Source Link

Document

Use this to create new cells within the row and return it.

Usage

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

License:Open Source License

public static void createSampleExportForm(File outpath, JSONArray jsonArray) throws Exception {
    InputStream in = null;/*from  w  w  w . jav  a  2  s.co m*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_samples.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("samples_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        int i = 5;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                String dnaOrRNA = sampleinwell.split(":")[5];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                XSSFCell cellG = row.createCell(6);
                XSSFCell cellH = row.createCell(7);
                XSSFCell cellI = row.createCell(8);
                XSSFCell cellL = row.createCell(11);
                if ("R".equals(dnaOrRNA)) {
                    cellG.setCellValue("NA");
                    cellL.setCellFormula("1000/H" + (i + 1));
                } else if ("D".equals(dnaOrRNA)) {
                    cellH.setCellValue("NA");
                    cellI.setCellValue("NA");
                    cellL.setCellFormula("1000/G" + (i + 1));
                }
                XSSFCell cellM = row.createCell(12);
                cellM.setCellFormula("50-L" + (i + 1));
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

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

License:Open Source License

public static void createLibraryPoolExportFormFromWeb(File outpath, JSONArray jsonArray, String indexFamily)
        throws Exception {
    InputStream in = null;/* w w w  .  j a  va2s  .  c o m*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_libraries_pools.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("library_pool_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        XSSFRow row2 = sheet.getRow(1);

        int i = 6;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("paired".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellA = row2.createCell(0);
                row2cellA.setCellValue(jsonObject.getString("value"));
            } else if ("platform".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellB = row2.createCell(1);
                row2cellB.setCellValue(jsonObject.getString("value"));
            } else if ("type".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellC = row2.createCell(2);
                row2cellC.setCellValue(jsonObject.getString("value"));
            } else if ("selection".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellD = row2.createCell(3);
                row2cellD.setCellValue(jsonObject.getString("value"));
            } else if ("strategy".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellE = row2.createCell(4);
                row2cellE.setCellValue(jsonObject.getString("value"));
            }
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                if (indexFamily != null) {
                    XSSFCell cellJ = row.createCell(9);
                    cellJ.setCellValue(indexFamily);
                }
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

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;/*from w w  w .  j  a  va 2s  . co  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);//  www  . j  a  v a  2s .co 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());
    }// www.  ja v a 2  s . com
    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:uscraper.UScraper.java

public static void CareerBuilderScraper(String path, int totalPage, String[] states) {
    setLoggerOff();/* w w  w . j ava 2s.c  om*/

    try {
        outstream = new FileOutputStream(new File(path));
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet("CareerBuilder");

        webClient = new WebClient();
        webClient.getOptions().setJavaScriptEnabled(false);
        webClient.getOptions().setTimeout(86400000);// 1 day
        page = webClient.getPage("http://www.careerbuilder.com/");

        HtmlForm form = page.getFirstByXPath("//form[@action='http://www.careerbuilder.com/jobs']");
        HtmlElement button = (HtmlElement) page.createElement("button");
        button.setAttribute("type", "submit");
        form.appendChild(button);

        HtmlTextInput what = (HtmlTextInput) page.getElementByName("keywords");
        HtmlTextInput where = (HtmlTextInput) page.getElementByName("location");
        what.setValueAttribute("");

        for (String state : states) {
            where.setValueAttribute(state);
            //where.setValueAttribute("Connecticut");
            //System.out.println(page.asXml());System.exit(0);
            page = button.click();
            webClient.waitForBackgroundJavaScript(1000);
            int pageNum = 1;
            int rowNum = 0;
            while (pageNum <= totalPage) {
                List<?> aTitle = page.getByXPath("//div[@class='job-row']/div/div/h2/a");
                for (int i = 0; i < aTitle.size(); i++) {
                    HtmlAnchor a = (HtmlAnchor) aTitle.get(i);
                    XSSFRow row = (XSSFRow) sheet.createRow(rowNum);
                    XSSFCell cellSource = row.createCell(0);
                    cellSource.setCellValue("CareerBuilder");
                    XSSFCell cellTitle = row.createCell(1);
                    cellTitle.setCellValue(a.asText());

                    HtmlPage descriptionPage = a.click();
                    webClient.waitForBackgroundJavaScript(1000);
                    HtmlDivision div = (HtmlDivision) descriptionPage.getByXPath("//div[@class='description']")
                            .get(0);
                    XSSFCell cellDesc = row.createCell(8);
                    cellDesc.setCellValue(div.asText());

                    rowNum++;
                }

                webClient.waitForBackgroundJavaScript(1000);
                List<?> hCompany = page.getByXPath(
                        "//div[@class='job-row']/div/div[@class='columns large-2 medium-3 small-12']/h4");
                rowNum = 0;
                for (int i = 0; i < hCompany.size(); i++) {
                    HtmlElement e = (HtmlElement) hCompany.get(i);
                    XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
                    XSSFCell cellCompany = row.createCell(2);
                    cellCompany.setCellValue(e.asText());

                    rowNum++;
                }

                webClient.waitForBackgroundJavaScript(1000);
                List<?> hLocation = page.getByXPath(
                        "//div[@class='job-row']/div[@class='row job-information']/div[@class='columns end large-2 medium-3 small-12']/h4[@class='job-text']");
                rowNum = 0;
                for (int i = 0; i < hLocation.size(); i++) {
                    HtmlElement e = (HtmlElement) hLocation.get(i);
                    XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
                    XSSFCell cellLocation = row.createCell(3);
                    cellLocation.setCellValue(e.asText());
                    System.out.println(row.getCell(3));

                    rowNum++;
                }

                webClient.waitForBackgroundJavaScript(1000);
                List<?> hInfo = page
                        .getByXPath("//div[@class='job-row']/div/div[@class='columns medium-6 large-8']/h4");
                rowNum = 0;
                for (int i = 0; i < hInfo.size(); i++) {
                    HtmlElement e = (HtmlElement) hInfo.get(i);
                    XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
                    XSSFCell cellInfo = row.createCell(4);
                    cellInfo.setCellValue(e.asText());

                    rowNum++;
                }

                webClient.waitForBackgroundJavaScript(1000);
                List<?> emElapsedTime = page
                        .getByXPath("//div[@class='job-row']/div/div/div[@class='show-for-medium-up']/em");
                rowNum = 0;
                for (int i = 0; i < emElapsedTime.size(); i++) {
                    HtmlElement e = (HtmlElement) emElapsedTime.get(i);
                    XSSFRow row = (XSSFRow) sheet.getRow(rowNum);
                    XSSFCell cellElapsedTime = row.createCell(5);
                    cellElapsedTime.setCellValue(e.asText());

                    String timeStamp = new SimpleDateFormat("yyyyMMdd_HHmmss")
                            .format(Calendar.getInstance().getTime());
                    XSSFCell cellDateTime = row.createCell(6);
                    cellDateTime.setCellValue(timeStamp);
                    XSSFCell cellState = row.createCell(7);
                    cellState.setCellValue(state);

                    rowNum++;
                }

                HtmlAnchor next = (HtmlAnchor) page.getElementById("next-button");
                next.click();
                pageNum++;
            }
        }
        workbook.write(outstream);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (workbook != null)
                workbook.close();
            if (outstream != null)
                outstream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return;
}

From source file:util.ExcelConverter.java

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

    try {/*from  w  ww  .jav a  2 s.  com*/
        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:Utility.CSV_File_Generator.java

public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) {
    FileOutputStream out = null;/* ww w . j a  v  a  2  s .  c  o m*/
    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 ww.ja va  2s .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
    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 w  w.j  a  va 2  s .com
    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);
}