Unable to write write data to excel sheet with row no constant and increasing cell no

I have Written a Java Program to fetch data from SQL and write the same to Excel sheet, the following program works only if I uncomment the line i don know Why it is happening like that please help me to correct the Program.

 package com.selenium.examples;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

public class SQLtoExcelSheet {

    public static void main(String[] args) throws SQLException, IOException {


        Workbook excel=new XSSFWorkbook();
        Sheet sheet= excel.createSheet("SQLResults");
        int excelRowNo=0;
        int excelCellNo=0;


        String connectionUrl = "jdbc:sqlserver://localhost;user=sa;password=abc@123";
        Connection con=DriverManager.getConnection(connectionUrl);

        Statement stmt=con.createStatement();

        String SQL="use BonusingCore;"+"select * from MachineMaster;";
        ResultSet result=stmt.executeQuery(SQL);
        ResultSetMetaData mataData=result.getMetaData();
        int columnCount=mataData.getColumnCount();
        result.next();

        //first row should be Column label
        for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
            Row excelRow=sheet.createRow(excelRowNo);
            Cell excelCell=excelRow.createCell(excelCellNo);
            excelCell.setCellValue(mataData.getColumnName(sqlColumnNumber));
            excelCellNo++;
            excelRowNo++;
            sheet.autoSizeColumn(excelCellNo);
        }
        FileOutputStream flout=new FileOutputStream("C:\\Users\\Illusion\\workspace\\SeleniumExamples\\testData\\output.xlsx");
        excel.write(flout);
        flout.close();
    }
}

Solution

If you want to write some data in some of the column of any particular row, you need to use createRow() once and createCell() all the time, if none of them is initialized.

In the code

 for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
          //creating a new row by createRow()... this will remove previous instance
        Row excelRow=sheet.createRow(excelRowNo);
          // creating a new column by createCell()
        Cell excelCell=excelRow.createCell(excelCellNo);
    ......

It seems that for each cell, you are creating the same row again and again so the previous data get lost. Instead you need to create row just once and use the same row for creating all the cells.

e.g.

         //creating a new row by createRow()
        Row excelRow=sheet.createRow(excelRowNo);

 for(int sqlColumnNumber=1;sqlColumnNumber<=columnCount;sqlColumnNumber++ ){
           // creating a new column by createCell()
        Cell excelCell=excelRow.createCell(excelCellNo);

Hope you have understood what I want to say...Good Luck..