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

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

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

Introduction

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

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

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  ww  . j  av a2 s. 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  ww  .j  av a  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.gov.ofwat.RefTest.java

License:Open Source License

public void writeXLS() throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    // create a new sheet
    Sheet s = wb.createSheet();/*ww  w  .j a va 2 s .  c  o  m*/
    // declare a row object reference
    Row r = null;
    // declare a cell object reference
    Cell c = null;
    // create 2 cell styles
    XSSFCellStyle cs = wb.createCellStyle();

    XSSFCellStyle cs2 = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();

    // create 2 fonts objects
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // Set font 1 to 12 point type, blue and bold
    f.setFontHeightInPoints((short) 12);
    f.setColor(IndexedColors.RED.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Set font 2 to 10 point type, red and bold
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.RED.getIndex());
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Set cell style and formatting
    cs.setFont(f);
    cs.setDataFormat(df.getFormat("#,##0.0"));

    // Set the other cell style and formatting
    cs2.setBorderBottom(cs2.BORDER_THIN);
    cs2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    cs2.setFont(f2);

    // Define a few rows
    for (int rownum = 0; rownum < 30; rownum++) {
        r = s.createRow(rownum);
        for (int cellnum = 0; cellnum < 10; cellnum += 2) {
            c = r.createCell(cellnum);
            Cell c2 = r.createCell(cellnum + 1);

            c.setCellValue((double) rownum + (cellnum / 10));
            c2.setCellValue(creationHelper.createRichTextString("Hello! " + cellnum));
        }
    }

    File file = new File("d:\\out.xls");
    FileOutputStream fos = new FileOutputStream(file);
    wb.write(fos);
    //      fos.write(wb.getBytes());
    //      fos.flush();
    //      fos.close();

}

From source file:util.ExcelConverter.java

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

    try {//  www  .j  av  a  2s. 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

public void compareXlsxBatch(String xlsxFileName, Map<String, Map<String, String>> legitLvMap) {
    //Map<String, Map<String, String>> LegitLvMap = 
    File xlsxFile = new File(xlsxFileName);
    try {/*from ww w  .  j  a  va2 s  .  c  om*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            // List<String> keyList = new ArrayList<>(); //keep track info of each column
            while (it.hasNext()) {
                //keyList.add(it.next().getStringCellValue());   
                it.next();
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length) { // correct xlsx file                 
                int rowNum = 1;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    //row.getRowStyle();
                    rowNum++;
                }
                for (int i = 1; i < rowNum; i++) {
                    Row row = mySheet.getRow(i);
                    foregroundColorSetUp(row, myWorkBook, numCell, legitLvMap); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;

                String targetFile = null;
                if (xlsxFileName.contains(".xlsx")) {
                    targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
                } else {
                    targetFile = xlsxFileName + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();

            } else {
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(BatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:utilities.DebtMgmtBatchInDJMS.java

public void compareXlsxBatch(String xlsxFileName) {
    File xlsxFile = new File(xlsxFileName);
    try {/*from w ww . java 2  s. c  o  m*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            //                Iterator<Cell> it = headerRow.cellIterator();
            //                int numCell = 0;
            //               // List<String> keyList = new ArrayList<>(); //keep track info of each column
            //                while(it.hasNext()){
            //                    //keyList.add(it.next().getStringCellValue());   
            //                    it.next();
            //                    numCell++;
            //                }

            //  if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length){  // correct xlsx file                 
            int rowNum = 1;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //row.getRowStyle();
                rowNum++;
            }
            for (int i = 0; i < rowNum; i++) {
                Row row = mySheet.getRow(i);
                foregroundColorSetUp(row, myWorkBook); //check each row and update foreground color
            }

            fis.close();
            FileOutputStream output;

            String targetFile = null;
            if (xlsxFileName.contains(".xlsx")) {
                targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
            } else {
                targetFile = xlsxFileName + "COLORED.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();
            //
            //                } else {
            //                     JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            //                }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

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  . ja va2 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:utils.ReadWriteExcelFile.java

public static void writeXLSXFile(String aFile, String aSheet) throws IOException {

    String excelFileName = aFile;//name of excel file

    String sheetName = aSheet;//name of sheet

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);

    //iterating r number of rows
    //      for (int r=0;r < 5; r++ )
    //      {//from   w w w. ja va2 s .c  o m
    //         XSSFRow row = sheet.createRow(r);
    //
    //         //iterating c number of columns
    //         for (int c=0;c < 5; c++ )
    //         {
    //            XSSFCell cell = row.createCell(c);
    //   
    //            cell.setCellValue("Cell "+r+" "+c);
    //         }
    //      }

    FileOutputStream fileOut = new FileOutputStream(excelFileName);

    //write this workbook to an Outputstream.
    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:vd6_celltype.Celltype.java

/**
 * @param args the command line arguments
 *//*from ww w.  j  a  v  a 2 s.c om*/
public static void main(String[] args) throws IOException {
    // TODO code application logic here
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet spreadsheet = workbook.createSheet("cell types");
    XSSFRow row = spreadsheet.createRow((short) 2);
    row.createCell(0).setCellValue("Type of Cell");
    row.createCell(1).setCellValue("cell value");
    row = spreadsheet.createRow((short) 3);
    row.createCell(0).setCellValue("set cell type BLANK");
    row.createCell(1);
    row = spreadsheet.createRow((short) 4);
    row.createCell(0).setCellValue("set cell type BOOLEAN");
    row.createCell(1).setCellValue(true);
    row = spreadsheet.createRow((short) 5);
    row.createCell(0).setCellValue("set cell type ERROR");
    row.createCell(1).setCellValue(XSSFCell.CELL_TYPE_ERROR);//default = 5
    row = spreadsheet.createRow((short) 6);
    row.createCell(0).setCellValue("set cell type date");
    row.createCell(1).setCellValue(new Date());
    row = spreadsheet.createRow((short) 7);
    row.createCell(0).setCellValue("set cell type numeric");
    row.createCell(1).setCellValue(20);
    row = spreadsheet.createRow((short) 8);
    row.createCell(0).setCellValue("set cell type string");
    row.createCell(1).setCellValue("A String");
    FileOutputStream out = new FileOutputStream(new File("src\\vd6_celltype\\typesofcells.xlsx"));
    workbook.write(out);
    out.close();
    System.out.println("typesofcells.xlsx written successfully");
}

From source file:vd7_cellstyle.CellStyle.java

public static void main(String[] args) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet spreadsheet = workbook.createSheet("cellstyle");
    XSSFRow row = spreadsheet.createRow((short) 1); //row 2 in excel

    //SET HEIGHT OF ROW 2 (in excel)
    row.setHeight((short) 800);
    XSSFCell cell = (XSSFCell) row.createCell((short) 1);

    cell.setCellValue("test of merging");
    //MEARGING CELLS 
    //this statement for merging cells
    spreadsheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based)
            1, //last row (0-based)
            1, //first column (0-based)
            4 //last column (0-based)
    ));//w w w  .  j av a 2s.  com

    //CELL Alignment
    row = spreadsheet.createRow(5); //row 6 (in excel)
    cell = (XSSFCell) row.createCell(0);
    row.setHeight((short) 800);
    // Top Left alignment 
    XSSFCellStyle style1 = workbook.createCellStyle();
    spreadsheet.setColumnWidth(0, 8000);
    style1.setAlignment(XSSFCellStyle.ALIGN_LEFT);
    style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
    cell.setCellValue("Top Left");
    //apply the style
    cell.setCellStyle(style1);

    row = spreadsheet.createRow(6);//row 7 in excel
    cell = (XSSFCell) row.createCell(1);
    row.setHeight((short) 800);
    // Center Align Cell Contents 
    XSSFCellStyle style2 = workbook.createCellStyle();
    style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cell.setCellValue("Center Aligned");
    //apply the style
    cell.setCellStyle(style2);

    row = spreadsheet.createRow(7); //row 8 in excel
    cell = (XSSFCell) row.createCell(2);
    row.setHeight((short) 800);
    // Bottom Right alignment 
    XSSFCellStyle style3 = workbook.createCellStyle();
    style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
    cell.setCellValue("Bottom Right");
    //apply the style
    cell.setCellStyle(style3);

    row = spreadsheet.createRow(8);//row 9 in excel
    cell = (XSSFCell) row.createCell(3);
    // Justified Alignment (cn ?u trong )
    XSSFCellStyle style4 = workbook.createCellStyle();
    style4.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
    style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
    cell.setCellValue("Contents are Justified in Alignment");
    cell.setCellStyle(style4);

    //CELL BORDER
    row = spreadsheet.createRow((short) 9); //row 10 in excel
    row.setHeight((short) 800);
    cell = (XSSFCell) row.createCell((short) 0);
    cell.setCellValue("BORDER");
    XSSFCellStyle style5 = workbook.createCellStyle();
    //set bottom border which is thick line
    style5.setBorderBottom(XSSFCellStyle.BORDER_THICK);
    //set color of bottom border
    style5.setBottomBorderColor(IndexedColors.BLUE.getIndex());
    style5.setBorderLeft(XSSFCellStyle.BORDER_DOUBLE);
    style5.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style5.setBorderRight(XSSFCellStyle.BORDER_HAIR);
    style5.setRightBorderColor(IndexedColors.RED.getIndex());
    style5.setBorderTop(XSSFCellStyle.BIG_SPOTS);
    style5.setTopBorderColor(IndexedColors.CORAL.getIndex());
    cell.setCellStyle(style5);

    //Fill Colors
    //background color
    row = spreadsheet.createRow((short) 10);
    cell = (XSSFCell) row.createCell((short) 1);
    XSSFCellStyle style6 = workbook.createCellStyle();
    style6.setFillBackgroundColor(HSSFColor.LEMON_CHIFFON.index);
    style6.setFillPattern(XSSFCellStyle.LESS_DOTS);
    spreadsheet.setColumnWidth(1, 8000);
    cell.setCellValue("FILL BACKGROUNG/FILL PATTERN");
    cell.setCellStyle(style6);

    FileOutputStream out = new FileOutputStream(new File("src\\vd7_cellstyle\\cellstyle.xlsx"));
    workbook.write(out);
    out.close();
    System.out.println("cellstyle.xlsx written successfully");
}