need help regarding POI workbook ?

[naved momin]

can any one help please i have an requirement where i need to import data from the sql and make a excel report of that
i m using POI workbook , but it is creating a problem for me
the problem is that
when i create a new cell the old cell value disappears from the excel document
i m putting the code , hope any one can find out whats wrong in my code

package ReportGeneratingPackage;

import businessmanagementproject.DatabasePackage.Database;
import com.sun.security.auth.module.NTSystem;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;

/**
 *
 * @author Admin
 */
public class EmployeesReport extends Database{

    /**
     * @param args the command line arguments
     */
    
    
    HSSFWorkbook workbook ;
    HSSFSheet sheet;
    NTSystem nTSystem = new NTSystem();
    String username = nTSystem.getName();
    File outLOcation = new File("C:/Users/" + username + "/Desktop/book.xls");
    File spaceInfraIconFile = new File("C:/Users/" + username + "/Documents/NetBeansProjects/BusinessManagementProject/icons/image001.jpg");
    FileOutputStream out;
    
    public EmployeesReport() {
        
        
        workbook = new HSSFWorkbook();
        
        sheet = workbook.createSheet("Employee Report");
        try {
            out = new FileOutputStream(outLOcation);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        saveingCompanyIcon();
        
        leavingOneBlankLine();
        
        writingColumnNames();
        
        makingNameColumn();
        
        try {
            workbook.write(out);
        } catch (IOException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            out.flush();
        } catch (IOException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            out.close();
        } catch (IOException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void takingEmpDetailsFromDB()
    {
        Statement statement = null;
        ResultSet resultSet;
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        String quary = "Select * from emp_Info";
        try {
            resultSet = statement.executeQuery(quary);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        
    }
     
    
    
    public static void main(String[] args) {
        // TODO code application logic here
        new EmployeesReport();
        
    }

    private void saveingCompanyIcon() {
        int addMergedRegion = sheet.addMergedRegion(new CellRangeAddress(0, (short)5, 0, (short)8));
        
        HSSFRow headerRow = sheet.createRow(addMergedRegion);
        HSSFCell headerCell = headerRow.createCell(0);
        FileInputStream inputImage = null ;
        try {
            inputImage = new FileInputStream(spaceInfraIconFile);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        byte[] bytes = null ;
        try {
            bytes = IOUtils.toByteArray(inputImage);
        } catch (IOException ex) {
            Logger.getLogger(EmployeesReport.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        int picIndex = workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
        
        
        CreationHelper helper = workbook.getCreationHelper();
        
        Drawing drawing = sheet.createDrawingPatriarch();
        
        ClientAnchor anchor = helper.createClientAnchor();
        
        anchor.setCol1(0);
        anchor.setRow1(addMergedRegion);
        
        org.apache.poi.ss.usermodel.Picture pic = drawing.createPicture(anchor, picIndex);
        
        pic.resize(1);
        
        
    }

    private void leavingOneBlankLine() {
        int addMergedRegion = sheet.addMergedRegion(new CellRangeAddress(6, (short)8, 0, (short)8));
        HSSFRow row = sheet.createRow(addMergedRegion);
        HSSFCell col = row.createCell(0);
        
        
        
    }

    private void writingColumnNames() {
        
        List column = new ArrayList();
        column.add("ID");
        column.add("Name");
        column.add("Gender");
        column.add("DOB");
        column.add("DOJ");
        column.add("Department");
        column.add("Address");
        column.add("Contact");
        column.add("Salary");
        column.add("DOL");
        
        HSSFRow idRow = sheet.createRow(9);
        HSSFCell idCell = idRow.createCell(0);
        idCell.setCellValue("ID");
        
//        HSSFCellStyle style = workbook.createCellStyle();
//        
//        HSSFFont font = workbook.createFont();
//        
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//        
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        
//        style.setFont(font);
//        
//        idCell.setCellStyle(style);
        
        
        
        
        
    }

    private void makingNameColumn() {
        
        HSSFRow nameRow = sheet.createRow(9);
        HSSFCell nameCell = nameRow.createCell(1);
        nameCell.setCellValue("Name");
        
//        HSSFCellStyle style = workbook.createCellStyle();
//        
//        HSSFFont font = workbook.createFont();
//        
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//        
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        
//        style.setFont(font);
//        
//        nameCell.setCellStyle(style);
        
        
    }
}

please help me guyz i need to complete this project by sunday
if you know any other free open source project that might help me writing excel documents than that will be awfull.

[Ben Ooms]

I don't know if this is related to your problem but I see that you use the same row twice:

at line 174:

HSSFRow idRow = sheet.createRow(9);  

and at line 198:

HSSFRow nameRow = sheet.createRow(9);

This means that after writing your columnnames at row 9 when you write the data rows row 9 is overwriten. I think the code at line 198 should be:

HSSFRow nameRow = sheet.createRow(10);

regards,
Ben