Excel functionalities using java

This is used to generate password protected excel sheet with some cells editable and non editable cells. Some functions are used to create dropdown in a cell with constaraints and to create hyperlink in a cell pointing to the desired URL,to apply font to the cells according to the users' requirement,create freeze pane in a sheet, make a sheet in a workbook as hidden,to create comment for a cell and Also,to create merged cell .

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Comment;

/**
 * This class is used to generate password protected  excel sheet with some cells editable and non editable cells.
 * Some functions are used to create dropdown in a cell  with constaraints and to create hyperlink in a cell pointing to the desired URL,to apply  font to the cells according to the users' requirement,create freeze pane in a sheet,
 * make a sheet in a workbook as hidden,to create comment for a cell and Also,to create merged cell . 
 *
 */


@SuppressWarnings("deprecation")
public class ProtectedSheet {
 public static void main(String arg[]) {
  try{
   //reading the file provide the appropriate path
   FileOutputStream out=new FileOutputStream("protected.xls");
   //Creating Workbook
   Workbook wb = new HSSFWorkbook();
   Sheet sheet = wb.createSheet("employeedetails");//sheet is created 
   Sheet sheet1=wb.createSheet("HiddenSheet");
   //setSheetHidden function is used to make the  sheet as hidden
   wb.setSheetHidden(1,true);
   //creating CellStyle  to apply it into cell
   CellStyle style = wb.createCellStyle();
   CellStyle style1 = wb.createCellStyle();
   //Font is used for setting the font to a cell
   Font font = wb.createFont();
   font.setBoldweight(Font.BOLDWEIGHT_BOLD);
   style.setFont(font);
   style.setLocked(true);
   style1.setFont(font);
   style1.setLocked(false);//setLocked function is used to make a cell lock or unlock ,if it false means the cell value is editable
   style1.setWrapText(true);// setWrapText function is used to display the cell value in one line
   CreationHelper createHelper = wb.getCreationHelper();
   //cell style for hyperlinks

   CellStyle hlink_style = wb.createCellStyle();
   Font hlink_font = wb.createFont();
   hlink_font.setUnderline(Font.U_SINGLE);
   hlink_font.setColor(IndexedColors.BLUE.getIndex());
   hlink_style.setFont(hlink_font);
   //filling the excel sheet
   for(int i=0;i<10;i++)
   {
    Row row = sheet.createRow(i);
    Cell cell =row.createCell((short)0);
    cell.setCellValue("EmployeeName");
    cell.setCellStyle(style);
    cell=row.createCell((short)1);
    cell.setCellValue("Employee ID");
    cell.setCellStyle(style);
    cell=row.createCell((short)2);
    cell.setCellValue("Project");
    cell.setCellStyle(style1);//Applying style to a cell
    cell=row.createCell((short)3);
    cell.setCellValue("ProjectDuration");//Applying style to a cell
    cell.setCellStyle(style1);
    //Creating dropdown in a sheet in the  required  position
    CellRangeAddressList addressList = new  CellRangeAddressList(i, i, 3, 3);
    //dropdown is taking explicit values mentioned in the String Array
    DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"1", "2", "3"});

    HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
    dataValidation.setSuppressDropDownArrow(false);
    ((HSSFSheet) sheet).addValidationData(dataValidation);
    cell=row.createCell((short)4);
    cell.setCellStyle(style1);
    CellRangeAddressList addressList1 = new  CellRangeAddressList(i, i, 4, 4);
    //adding constraint to the dropdown depending the value of the previous dropdown the value will be displayed in the curent dropdown
    DVConstraint dvConstraint1=DVConstraint.createFormulaListConstraint("IF(INDIRECT(\"D\"&ROW())=1,'employeedetails'!$A$1:$B$1,IF(INDIRECT(\"D\"&ROW())=2,"+
    "'employeedetails'!$A$1:$B$1,IF(INDIRECT(\"D\"&ROW())=3,'employeedetails'!$A$1:$B$1,)))");

    HSSFDataValidation dataValidation1 = new HSSFDataValidation(addressList1, dvConstraint1);
    dataValidation1.setSuppressDropDownArrow(false);
    ((HSSFSheet) sheet).addValidationData(dataValidation1);
    cell=row.createCell((short)5);
    cell.setCellValue("GOOGLE");
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("https://www.google.com");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    cell=row.createCell((short)6);
    cell.setCellValue("Merged cell");
    //Merging the cells
    sheet.addMergedRegion(new CellRangeAddress(i,i,6,7));
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = createHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex()+5);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum()+5);
    //Adding comment to a cell
    Comment comment =drawing.createCellComment(anchor);
    RichTextString str = createHelper.createRichTextString("Hello, World!");//createRichTextString function is used to add comment for a cell
    comment.setString(str);
    cell.setCellComment(comment);
   }
   //Creating freezepane for a sheet
   sheet.createFreezePane(0, 5, 0,5 );
   //Protecting a sheet with Password
   ((HSSFSheet)sheet).protectSheet("DocuShare");
   System.out.println("Protected Sheet is created");
   wb.write(out);
   out.close();
  } catch(Exception e){
   e.printStackTrace();
  }
 }

}