generate xls file in java | poi hssf | generate excel file in java | java code to create a excel sheet

generate xls file in java | poi hssf | generate excel file in java | java code to create a excel sheet

Here code that generete Excel (.xls) file automatically.
you have to built by your self by your prices.
also calculate your price setting and getting Report by your custom requirement.
in that case you have connect by your database and set the values.
you have buit .jar and getting Report file automatically.

Requrie library :  ApachePOI

                             Poi-ooxml (if Xml Error)

 

package excelgeneratedemo;

import java.io.FileOutputStream;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

/**

 *

 * @author vishal.khokhar

 */

public class ExcelGenerator {

    DateFormat df = new SimpleDateFormat("MM_dd_yyyy");

    String fileName = "Full_Annual_Report_" + df.format(new Date());

    Workbook wb = new HSSFWorkbook();

    Sheet sheet = wb.createSheet(fileName);

    public static void main(String[] args) {

        ExcelGenerator excelGeneratorObj = new ExcelGenerator();

        excelGeneratorObj.getxls();

    }

    public void getxls() {

        try {

            //Create Style

            CellStyle style = wb.createCellStyle();

            CellStyle style1 = wb.createCellStyle();

            CellStyle style2 = wb.createCellStyle();

            //Font setting

            Font headerFont = wb.createFont();

            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

            style.setFont(headerFont);

            //Alignment center

            style.setAlignment(CellStyle.ALIGN_CENTER);

            //fill color

            style.setFillForegroundColor(HSSFColor.BLUE_GREY.index);

            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            

            style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

            style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            style1.setFont(headerFont);

            style1.setAlignment(CellStyle.ALIGN_CENTER);

            

            //Cell Border

            style.setBorderBottom(CellStyle.BORDER_THIN);

            style.setBorderLeft(CellStyle.BORDER_THIN);

            style.setBorderRight(CellStyle.BORDER_THIN);

            style.setBorderTop(CellStyle.BORDER_THIN);

            style1.setBorderBottom(CellStyle.BORDER_THIN);

            style1.setBorderLeft(CellStyle.BORDER_THIN);

            style1.setBorderRight(CellStyle.BORDER_THIN);

            style1.setBorderTop(CellStyle.BORDER_THIN);

            style2.setBorderBottom(CellStyle.BORDER_THIN);

            style2.setBorderLeft(CellStyle.BORDER_THIN);

            style2.setBorderRight(CellStyle.BORDER_THIN);

            style2.setBorderTop(CellStyle.BORDER_THIN);

            int i = 2;

            Row row2 = sheet.createRow((short) 0);

            row2.createCell(7).setCellValue("First Month (in Cr.)");

            row2.createCell(10).setCellValue("Second Month (in Cr.)");

            row2.createCell(13).setCellValue("Third Month (in Cr.)");

            sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 7,9));

            sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 10, 12));

            sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 13, 15));

            row2.getCell(7).setCellStyle(style);

            row2.getCell(10).setCellStyle(style);

            row2.getCell(13).setCellStyle(style);

            Row row1 = sheet.createRow((short) 1);

            row1.createCell(0).setCellValue("#");

            row1.createCell(1).setCellValue("Category");

            row1.createCell(2).setCellValue("Revenue");

            row1.createCell(3).setCellValue("Foreign Ex.");

            row1.createCell(4).setCellValue("Current Ex.");

            row1.createCell(5).setCellValue("Notations");

          

            row1.createCell(7).setCellValue("Income");

            row1.createCell(8).setCellValue("Expense");

            row1.createCell(9).setCellValue("Status");

            row1.createCell(10).setCellValue("Income");

            row1.createCell(11).setCellValue("Expense");

            row1.createCell(12).setCellValue("Status");

            row1.createCell(13).setCellValue("Income");

            row1.createCell(14).setCellValue("Expense");

            row1.createCell(15).setCellValue("Status");

            //Set All Cell on Style

            for (int tmpi = 0; tmpi <= 15; tmpi++) {

                if(tmpi==6){

                    continue;

                }

                row1.getCell(tmpi).setCellStyle(style1);

            }

            for (int j = 0; j < 10; j++) {

                Row row = sheet.createRow((short) i);

                row.createCell(0).setCellValue(i-1);

                row.createCell(1).setCellValue("Data 1");

                row.createCell(2).setCellValue("Data 2");

                row.createCell(3).setCellValue("Data 3");

                row.createCell(4).setCellValue("Data 4");

                row.createCell(5).setCellValue("Data 5");

                

                int firstMonthIncome= (int)(Math.random()*1000);

                int firstMonthExpense= (int)(Math.random()*1000);

                

                int secMonthIncome= (int)(Math.random()*1000);

                int secMonthExpense= (int)(Math.random()*1000);

                

                int thirdMonthIncome= (int)(Math.random()*1000);

                int thirdMonthExpense= (int)(Math.random()*1000);

                

                row.createCell(7).setCellValue(String.valueOf(firstMonthIncome));

                row.createCell(8).setCellValue(String.valueOf(firstMonthExpense));

                row.createCell(9).setCellValue(String.valueOf(firstMonthIncome-firstMonthExpense));

                

                row.createCell(10).setCellValue(String.valueOf(secMonthIncome));

                row.createCell(11).setCellValue(String.valueOf(secMonthExpense));

                row.createCell(12).setCellValue(String.valueOf(secMonthIncome-secMonthExpense));

                

                row.createCell(13).setCellValue(String.valueOf(thirdMonthIncome));

                row.createCell(14).setCellValue(String.valueOf(thirdMonthExpense));

                row.createCell(15).setCellValue(String.valueOf(thirdMonthIncome-thirdMonthExpense));

                

                row.getCell(0).setCellStyle(style1);

                for (int tmpi = 1; tmpi <= 5; tmpi++) {

                    row.getCell(tmpi).setCellStyle(style2);

                }

                for (int tmpi = 7; tmpi <= 15; tmpi++) {

                    row.getCell(tmpi).setCellStyle(style2);

                }

                i++;

            }

            for (int tmpi = 0; tmpi <= 58; tmpi++) {

                sheet.autoSizeColumn(tmpi);

            }

            // Write the output to a file

            FileOutputStream fileOut = new FileOutputStream(fileName + ".xls", Boolean.FALSE);

            wb.write(fileOut);

            fileOut.close();

            System.out.println(fileName+".xls  Generated Successfully");

            

        } catch (Exception ex) {

            ex.printStackTrace();

        }

    }

}

OUTPUT :