Create Excel (xlsx) in java using Apache poi XSSFWorkbook

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

CellRangeAddressList(-1, -1, i, i); -: usually its (0,0,0,0) but here using (-1,-1,i,i) in this first 2 (-1,-1) are for rows, and other two(i,i) for column locations, -1,-1 means it will go out of limit , in each new line dropdown option will be available , else it can be restricted with any +ve number, 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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcel {

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

}

private static void createExcel() {

String fileName = "FileName.xlsx";
String fileLoaction = "C:\\Users\\"+System.getProperty("user.name")+"\\Downloads\\"+fileName;

String[] columns = { "column1", "column2", "column3",
                                   "column4", "column5", "column6"};

try {
XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("sheet");
           
            XSSFRow rowhead = sheet.createRow(0);
           
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
           
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);
           
            CellRangeAddressList addressList = null;
            DataValidationConstraint dataValidationConstraint = null;
            DataValidation dataValidation = null;
            DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
           
             for(int i=0;i<columns.length;i++) {
                   rowhead.createCell(i).setCellValue(columns[i]);
                   if(columns[i].equals("column5")) {
                  addressList = new CellRangeAddressList(-1, -1, i, i);
            dataValidationConstraint = validationHelper.createExplicitListConstraint(
                                                                new String[] { "10", "20", "30" });
                   dataValidation = validationHelper.createValidation(
                                                dataValidationConstraint, addressList);
            sheet.addValidationData(dataValidation);
}
else if (columns[i].equals("column6")) {
addressList = new CellRangeAddressList(-1, -1, i, i);
dataValidationConstraint = validationHelper.createExplicitListConstraint(
                                                                      new String[] { "40", "50", "60" });
dataValidation = validationHelper.createValidation(
                                                     dataValidationConstraint, addressList);
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();
}
}
}