Create Excel (xls) in java using Apache poi HSSFWorkbook

Creating Excel (xls) in java using Apache poi HSSFWorkbook . in this excel few columns are having some filter or dropdown options.

CellRangeAddressList(0, 999999999, i, i) -: in this  0 is first row and 999999999 is highest range for the row, till this row dropdown option will be available, and (i,i) are those specific column in which dropdown is required, all the can be change according to choice, this is the method with arguments (public CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol))


Use HSSFWorkbook for xls and XSSFWorkbook for xlsx format.






import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddressList;

public class CreateExcel {

public static void main(String[] args) {
createExcel();
}

private static void createExcel() {

String fileName = "FileName.xls";
    String fileLoaction = "C:\\Users\\"+System.getProperty("user.name")+
                                         "\\Downloads\\"+fileName;
 
     String[] columns = { "column1", "column2", "column3",
                                        "column4", "column5", "column6"};

try {
HSSFWorkbook workbook = new HSSFWorkbook();
               HSSFSheet sheet = workbook.createSheet("sheet");
           
               HSSFRow rowhead = sheet.createRow((short)0);
           
               Font headerFont = workbook.createFont();
               headerFont.setBold(true);
           
               CellStyle headerCellStyle = workbook.createCellStyle();
               headerCellStyle.setFont(headerFont);
           
               CellRangeAddressList addressList;
               DVConstraint dvConstraint;
               DataValidation dataValidation;
           
              for(int i=0;i<columns.length;i++) {
                     rowhead.createCell(i).setCellValue(columns[i]);
                     if(columns[i].equals("column5")) {
            addressList = new CellRangeAddressList(0, 999999999, i, i);
            dvConstraint = DVConstraint.createExplicitListConstraint(
                                              new String[] { "10", "20", "30" });
                   dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                   dataValidation.setSuppressDropDownArrow(false);
                    sheet.addValidationData(dataValidation);
               }else if(columns[i].equals("column6")) {
                addressList = new CellRangeAddressList(0, 999999999, i, i);
                dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "40", "50", "60" });
                      dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                      dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
                }
                sheet.autoSizeColumn(i);
              }
           

               FileOutputStream fileOut = new FileOutputStream(fileLoaction);
               workbook.write(fileOut);

               workbook.close();
               fileOut.flush();
               fileOut.close();
               System.out.println("file created ");
           
}catch(Exception e ) {
e.printStackTrace();
}
}
   }