Export to file writer using java

export data to file using java
simple java programe to export to filewriter
how to export to filewriter using java source code
/**
* @author JohnJustin
www.johnjustin.tk
*
*/

public class ExportFileWriter{
 
 int intPageNumber = 0;
 java.sql.ResultSet rsExportResult = null;
 ResultSetBuilder resultBuilder = ResultSetBuilderFactory.getInstance();
 
 POIExcelWriter poiExcelWriter = null;
 TextWriter textWriter = null;

 ExportBuilder exportBuilder = null;
 
 private int intRowIndex = 0;

 public ExportFileWriter(ExportBuilder exportBuilder, TextWriter textWriter){
  this.textWriter = textWriter;
  this.exportBuilder = exportBuilder;
 }

 public ExportFileWriter(ExportBuilder exportBuilder, POIExcelWriter poiExcelWriter){
  this.exportBuilder = exportBuilder;
  this.poiExcelWriter = poiExcelWriter;
 }

 /*
 * @Method : setTextWriter
 * @param1 : TextWriter
 * sets the text writer
 */
 public void setTextWriter(TextWriter textWriter){
  this.textWriter = textWriter;
 }
 
 /*
 * @Method : setPOIExcelWriter
 * @param1 : POIExcelWriter poiExcelWriter
 * sets the poi excel writer
 */
 public void setPOIExcelWriter(POIExcelWriter poiExcelWriter){
  this.poiExcelWriter = poiExcelWriter;
 }
 
 /*
 * @Method : setExportBuilder
 * @param1 : ExportBuilder exportBuilder
 * sets the ExportBuilder instance
 */
 public void setExportBuilder(ExportBuilder exportBuilder){
  this.exportBuilder = exportBuilder;
 }
 
 /*
 * @Method : writeToTextFile
 * @param : no param
 * write the contents to the text file.
 */
 public boolean writeToTextFile(){
  java.io.BufferedWriter bufferedWriter = new java.io.BufferedWriter(textWriter);
  try{
   int numberOfColumns = 0;
   /*if paging is enabled, we are getting the current page and querying the page counted.*/
   if(exportBuilder.isPagingEnabled()){
    int intCurrentPage = resultBuilder.getCurrentPage();
    if(intCurrentPage != -1) rsExportResult = resultBuilder.getResultSet(rsExportResult, intCurrentPage);
   }else{
    rsExportResult = resultBuilder.getResultSet(rsExportResult);
   }

   if(rsExportResult != null){

    /*determining the fields to be printed as header and getting the number of columns and printing the header.*/
    String strHeader[] = null;
    if(exportBuilder.getHeader() != null) strHeader = exportBuilder.getHeader();
    if(strHeader == null && exportBuilder.getExportFields() != null) strHeader = exportBuilder.getExportFields();
    
    if(exportBuilder.getExportFields() == null || exportBuilder.getFieldPosition() == null){
     java.sql.ResultSetMetaData rsMetaData = rsExportResult.getMetaData();
     numberOfColumns = rsMetaData.getColumnCount();
     if(strHeader == null){
      strHeader = new String[numberOfColumns];
      for(int index=0; index<numberOfColumns; index++){
       strHeader[index] = rsMetaData.getColumnName(index + 1);
      }
     }
    }
    if(exportBuilder.getExportFields() != null){
     numberOfColumns = exportBuilder.getExportFields().length;     
    }else if(exportBuilder.getFieldPosition() != null){
     numberOfColumns = exportBuilder.getFieldPosition().length;
    }
    for(int index=0; index<strHeader.length; index++){
     if(index == 0){
      bufferedWriter.write(strHeader[index]);
     }else{
      bufferedWriter.write(exportBuilder.getDelimiter() + strHeader[index]);
     }
    }
    bufferedWriter.write("\r\n");
    strHeader = null;
    /*end of determining the fields to be printed as header and getting the number of columns and printing the header*/
    /*writing the header */    
    
    if(exportBuilder.isPagingEnabled()){
     int intCurrentPage = 0;
     do{
      writeResultSetToTextFile(rsExportResult, bufferedWriter, numberOfColumns);
      if(rsExportResult != null) rsExportResult.close();
      intCurrentPage = resultBuilder.getCurrentPage();
      if(intCurrentPage == -1) break;
      rsExportResult = resultBuilder.getResultSet(rsExportResult, intCurrentPage);
     }while(rsExportResult != null);
    }else{
     writeResultSetToTextFile(rsExportResult, bufferedWriter, numberOfColumns);
     if(rsExportResult != null) rsExportResult.close();
    }
   }
  }catch(Exception e){
   System.out.println("Error in writeToTextFile() ------ " + e);
  }
  try{
   if(bufferedWriter != null) bufferedWriter.close();
  }catch(Exception ee){
  }
  return(true);
 }
 
 public boolean writeToExcelFile(){
  try{
   int numberOfColumns = 0;   
   /*if paging is enabled, we are getting the current page and querying the page counted.*/
   if(exportBuilder.isPagingEnabled()){
    int intCurrentPage = resultBuilder.getCurrentPage();
    if(intCurrentPage != -1) rsExportResult = resultBuilder.getResultSet(rsExportResult, intCurrentPage);
   }else{
    rsExportResult = resultBuilder.getResultSet(rsExportResult);
   }

   if(rsExportResult != null){    
    poiExcelWriter.resetSheetIndex();
    poiExcelWriter.setSheet();
    
    /*determining the fields to be printed as header and getting the number of columns and printing the header.*/
    String[] strHeader = null;
    if(exportBuilder.getHeader() != null) strHeader = exportBuilder.getHeader();
    if(strHeader == null && exportBuilder.getExportFields() != null) strHeader = exportBuilder.getExportFields();
    
    if(exportBuilder.getExportFields() == null || exportBuilder.getFieldPosition() == null){
     java.sql.ResultSetMetaData rsMetaData = rsExportResult.getMetaData();
     numberOfColumns = rsMetaData.getColumnCount();
     if(strHeader == null){
      strHeader = new String[numberOfColumns];
      for(int index=0; index<numberOfColumns; index++){
       strHeader[index] = rsMetaData.getColumnName(index + 1);
      }
     }
    }
    if(exportBuilder.getExportFields() != null){
     numberOfColumns = exportBuilder.getExportFields().length;     
    }else if(exportBuilder.getFieldPosition() != null){
     numberOfColumns = exportBuilder.getFieldPosition().length;
    }

    poiExcelWriter.insertRow(intRowIndex);
    for(int index=0; index<strHeader.length; index++){
     poiExcelWriter.writeData(index, intRowIndex, strHeader[index]);
    }
    strHeader = null;
    intRowIndex++;
    /*end of determining the fields to be printed as header and getting the number of columns and printing the header*/
    /*writing the header */    

    /*writing the data*/
    if(exportBuilder.isPagingEnabled()){
     int intCurrentPage = 0;
     do{
      writeResultSetToExcelFile(rsExportResult, numberOfColumns);
      if(rsExportResult != null) rsExportResult.close();
      intCurrentPage = resultBuilder.getCurrentPage();
      if(intCurrentPage == -1) break;
      rsExportResult = resultBuilder.getResultSet(rsExportResult, intCurrentPage);
     }while(rsExportResult != null);
    }else{
     writeResultSetToExcelFile(rsExportResult, numberOfColumns);
     if(rsExportResult != null) rsExportResult.close();
    }
   }
  }catch(Exception e){
   System.out.println("Error in writeToExcelFile() ------ " + e);
  }
  try{
   if(poiExcelWriter != null) poiExcelWriter.closeExcel();
  }catch(Exception ee){
  }
  return(true);
 }

 private void writeResultSetToTextFile(java.sql.ResultSet rsExportResult, java.io.BufferedWriter bufferedWriter, int numberOfColumns){
  try{
   while(rsExportResult.next()){
    if(numberOfColumns != 0){
     for(int intFieldPostion = 0; intFieldPostion < numberOfColumns; intFieldPostion++){
      if(exportBuilder.getExportFields() != null){
       if(intFieldPostion == 0){
        bufferedWriter.write(rsExportResult.getString(exportBuilder.getExportFields()[intFieldPostion]));
       }else{
        bufferedWriter.write(exportBuilder.getDelimiter() + rsExportResult.getString(exportBuilder.getExportFields()[intFieldPostion]));
       }
      }else if(exportBuilder.getFieldPosition() != null){
       if(intFieldPostion == 0){
        bufferedWriter.write(rsExportResult.getString(exportBuilder.getFieldPosition()[intFieldPostion]));
       }else{
        bufferedWriter.write(exportBuilder.getDelimiter() + rsExportResult.getString(exportBuilder.getFieldPosition()[intFieldPostion]));
       }
      }else{
       if(intFieldPostion == 0){
        bufferedWriter.write(rsExportResult.getString(intFieldPostion + 1));
       }else{
        bufferedWriter.write(exportBuilder.getDelimiter() + rsExportResult.getString(intFieldPostion + 1));
       }
      }
     }
     bufferedWriter.write("\r\n");
    }
   }
  }catch(Exception e){
   System.out.println("Error in writeResultSetToTextFile() --------- " + e);
  }
 }

 private void writeResultSetToExcelFile(java.sql.ResultSet rsExportResult, int numberOfColumns){
  try{
   while(rsExportResult.next()){
    if(intRowIndex != 0 && (intRowIndex % 65536) == 0){
     poiExcelWriter.setSheet();
     intRowIndex = 0;
    }
    poiExcelWriter.insertRow(intRowIndex);
    if(numberOfColumns != 0){
     for(int intFieldPostion = 0; intFieldPostion < numberOfColumns; intFieldPostion++){
      if(exportBuilder.getExportFields() != null){       
       poiExcelWriter.writeData(intFieldPostion, intRowIndex, rsExportResult.getString(exportBuilder.getExportFields()[intFieldPostion]));
      }else if(exportBuilder.getFieldPosition() != null){
       poiExcelWriter.writeData(intFieldPostion, intRowIndex, rsExportResult.getString(exportBuilder.getFieldPosition()[intFieldPostion]));
      }else{       
       poiExcelWriter.writeData(intFieldPostion, intRowIndex, rsExportResult.getString(intFieldPostion + 1));       
      }
     }
     intRowIndex++;
    }
   }
  }catch(Exception e){
   System.out.println("Error in writeResultSetToExcelFile() --------- " + e);
  }
 }
}

Read More

Export to excel using java and poi

simple Java program to export a data to excel file using apache poi.
with the help of these below java program you can easily export
your data from database to excel file with the help of below java source code
/**
* @author JohnJustin
www.johnjustin.tk
*
*/

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.poifs.filesystem.*;
import java.io.*;

public class  POIExcelWriter{ 
 private POIFSFileSystem poiStream = null;
 private HSSFWorkbook document = null;
 private HSSFSheet sheet = null;
 private HSSFRow excelRow = null;
 private HSSFCell cell = null; 
 private HSSFCellStyle style = null;
 private HSSFFont font = null;

 String strExcelFile = "";
 int intSheetIndex = 0;
 
 public POIExcelWriter(){}

 public POIExcelWriter(String strExcelFile){
  openExcelFile(strExcelFile);
 }

 public boolean openExcelFile(String strExcelFile){
  try{
   this.strExcelFile = strExcelFile;
   document = new HSSFWorkbook();
   java.io.FileOutputStream fileOut = new java.io.FileOutputStream(strExcelFile);
   document.write(fileOut);
   fileOut.close();

   poiStream = new POIFSFileSystem(new FileInputStream(strExcelFile));
   document = new HSSFWorkbook(poiStream);
   return(true);
  }catch(Exception e){
   System.out.println(e);
  }
  return(false);
 }

 public boolean openExcelFile(String strExcelFile, boolean blnFlag){
  try{
   this.strExcelFile = strExcelFile;
   poiStream = new POIFSFileSystem(new FileInputStream(strExcelFile));
   document = new HSSFWorkbook(poiStream);
   return(true);
  }catch(Exception e){
   System.out.println(e);
  }
  return(false);
 }
 
 /* @Method : resetSheetIndex
 * @Param : no param
 * @Description : Method will reset the global index variable with 0
 */
 public void resetSheetIndex(){
  intSheetIndex = 0;
 }

 /* @Method : setSheet
 * @Param : no param
 * @Description : Method will try to get the sheet at specified index. If the sheet is nulll
 *      will try to create new sheet.
 */
 public void setSheet(){
  try{
   sheet = document.getSheetAt(intSheetIndex);
   if(sheet == null){
    sheet = document.createSheet();
   }
  }catch(Exception e){
   sheet = document.createSheet();
  }
  intSheetIndex++;
  System.out.println("intSheetIndex >> " + intSheetIndex + " sheet >> " + sheet);
 }
 
 /* @Method : insertRow
 * @Param : rowindex
 * @Description : Method will insert new row if the row does not exists.
 */
 public void insertRow(int row){
  if(sheet != null){
   if(sheet.getRow(row) != null) excelRow = sheet.getRow(row);
   else excelRow = sheet.createRow(row);
  }
 }

 /* @Method : insertRow
 * @Param : rowindex
 * @Description : Method will insert new row if the row does not exists.
 */
 public void insertRow(short row){
  if(sheet != null){
   if(sheet.getRow(row) != null) excelRow = sheet.getRow(row);
   else excelRow = sheet.createRow(row);
  }
 }

 /* @Method : insertRow
 * @Param : rowindex
 * @Description : Method will insert new row if the row does not exists.
 */
 public void insertRow(int row, boolean blnAppend){
  if(sheet != null){
   excelRow = sheet.createRow((short)row);
  }
 }
 
 /* @Method : writeData
 * @Param1 : col
 * @Param2 : row
 * @Param3 : data
 * @Description : Method will write float data in specified row and column.
 */
 public void writeData(int col, int row, Object data){
  excelRow = sheet.getRow(row);
  cell = excelRow.createCell(col);
  if(data instanceof Boolean) {
   cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
   cell.setCellValue(Boolean.getBoolean(data.toString()));
  } else if(data instanceof String) {
   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
   cell.setCellValue(new HSSFRichTextString(data.toString()));
  } else if(data instanceof Integer) {
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(Double.parseDouble(data.toString()));
  } else if(data instanceof Double) {
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(Double.parseDouble(data.toString()));
  } else if(data instanceof Float) {
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(Float.parseFloat(data.toString()));
  } else if(data instanceof Long) {
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(Long.parseLong(data.toString()));
  }
 }
  
 /* @Method : fillColor
 * @Param1 : sindex
 * @Param2 : startcol
 * @Param3 : endcol
 * @Param4 : row
 * @Description : Method will fill the color in specified row and column.
 */ 
 public void fillColor(short sindex, int row, int startcol, int endcol){
  try{
   excelRow = sheet.getRow(row);
   style = document.createCellStyle();
   
   style.setFillForegroundColor(sindex);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   for(int index=startcol; index <= endcol; index++){
    cell = excelRow.getCell(index);
    if(cell == null) cell = excelRow.createCell(index);
    cell.setCellStyle(style);
   }
  }catch(Exception e){
   System.out.println("Error in fillColor () --- " + e);
  }
 }
 
 public void fillColor(short sindex, int row, int startcol, int endcol, int red, int green, int blue) {
  //creating a custom palette for the workbook
     HSSFPalette palette = document.getCustomPalette();

     //replacing the standard red with freebsd.org red
     palette.setColorAtIndex(sindex, (byte) red, (byte) green, (byte) blue);
     excelRow = sheet.getRow(row);
  style = document.createCellStyle();
  
  style.setFillForegroundColor(sindex);
  style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  for(int index=startcol; index <= endcol; index++){
   cell = excelRow.getCell(index);
   if(cell == null) cell = excelRow.createCell(index);
   cell.setCellStyle(style);
  }
 }
 
 public void setBorder(int row, int startCol, int endCol, boolean left, boolean top, boolean right, boolean bottom) {
  excelRow = sheet.getRow(row);
  for(int index = startCol; index <= endCol; index++) {
   cell = excelRow.getCell(index);
   if(cell == null) cell = excelRow.createCell(index);
   
   HSSFCellStyle cellStyle = cell.getCellStyle();
   
   if(left) cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   if(top) cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   if(right) cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   if(bottom) cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   
   cell.setCellStyle(cellStyle);
  }
 }
 
 public void setBorder(int row, int col, boolean left, boolean top, boolean right, boolean bottom) {
  excelRow = sheet.getRow(row);
  cell = excelRow.getCell(col);
  if(cell == null) cell = excelRow.createCell(col);
  
  HSSFCellStyle cellStyle = cell.getCellStyle();
  
  if(left) cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  if(top) cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  if(right) cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  if(bottom) cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  
  cell.setCellStyle(cellStyle);
  
 } 
 /* @Method : merge
 * @Param : firstRow, lastRow, firstCol, lastCol
 * @Description : Merges the selected rows/columns to one
 */ 
 public void merge(int firstRow, int lastRow, int firstCol, int lastCol) {
  sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
 }
 
 /* @Method : setColumnWidth
 * @Param : col, width
 * @Description: Sets the column width
 */
 public void setColumnWidth(int col, int width) {
  sheet.setColumnWidth(col, width);
 }
 /* @Method : closeExcel
 * @Param : no param
 * @Description : Method will write the contents to excel file and closes the stream.
 */
 public void closeExcel(){
  try{
   // Rewrite the spreadsheet to the File System 
   FileOutputStream modSpreadsheet = new FileOutputStream(strExcelFile);
   document.write(modSpreadsheet);
   modSpreadsheet.close();
  }catch(Exception e){
  }
 }
 
 /*deprecated functions*/
 
 /* @Method : writeData
 * @Param1 : col
 * @Param2 : row
 * @Param3 : data
 * @Description : Method will write String data in specified row and column.
 */
 /**
  * @deprecated
  */
 public void writeData(int col, int row, String data){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);   
   cell.setCellType( HSSFCell.CELL_TYPE_STRING);
   cell.setCellValue(new HSSFRichTextString(data));
  }catch(Exception e){
   System.out.println("Error in writeData (String) --- " + e);
  }
 }
 
 /* @Method : writeData
 * @Param1 : col
 * @Param2 : row
 * @Param3 : data
 * @Description : Method will write integer data in specified row and column.
 */
 /**
  * @deprecated
  */
 public void writeData(int col, int row, int data){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);      
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(data);
  }catch(Exception e){
   System.out.println("Error in writeData (int) --- " + e);
  }
 }

 /* @Method : writeData
 * @Param1 : col
 * @Param2 : row
 * @Param3 : data
 * @Description : Method will write float data in specified row and column.
 */
 /**
  * @deprecated
  */
 public void writeData(int col, int row, float data){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);      
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(data);
  }catch(Exception e){
   System.out.println("Error in writeData (float) --- " + e);
  }
 }
 

 /* @Method : writeData
 * @Param1 : col
 * @Param2 : row
 * @Param3 : data
 * @Param4 : format
 * @Description : Method will write float data in specified row and column.
 */
 /**
  * @deprecated
  */
 public void writeData(int col, int row, float data, String strFormat){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);      
   cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
   cell.setCellValue(data);
   style = document.createCellStyle();
   style.setDataFormat(document.createDataFormat().getFormat(strFormat));
   cell.setCellStyle(style);
  }catch(Exception e){
   System.out.println("Error in writeData (float) + format --- " + e);
  }
 }
 
 /* @Method : fillColor
 * @Param1 : startcol
 * @Param2 : endcol
 * @Param3 : row
 * @Description : Method will fill the color in specified row and column.
 */
 /**
  * @deprecated
  */
 public void fillColor(int startcol, int endcol, int row){
  try{
   excelRow = sheet.getRow(row);
   style = document.createCellStyle();
   style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   for(int index=startcol; index <= endcol; index++){
    cell = excelRow.createCell(index);
    cell.setCellStyle(style);
   }
  }catch(Exception e){
   System.out.println("Error in fillColor () --- " + e);
  }
 }
 
 /* @Method : setCellFormula
 * @Param1 : col
 * @Param2 : row
 * @Param3 : strFormula
 * @Description : Method will set the formula
 */
 /**
  * @deprecated
  */
 public void setCellFormula(int col, int row, String strFormula){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);      
   cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
   cell.setCellFormula(strFormula);
   style = document.createCellStyle();
   style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   font = document.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
   cell.setCellStyle(style);
  }catch(Exception e){
   System.out.println("Error in fillColor () --- " + e);
  }
 }

 /* @Method : setCellFormula
 * @Param1 : col
 * @Param2 : row
 * @Param3 : strFormula
 * @Description : Method will set the formula
 */
 /**
  * @deprecated
  */
 public void setCellFormula(int col, int row, String strFormula, String strFormat){
  try{
   excelRow = sheet.getRow(row);
   cell = excelRow.createCell(col);      
   cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
   cell.setCellFormula(strFormula);
   style = document.createCellStyle();
   style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setDataFormat(document.createDataFormat().getFormat(strFormat));
   font = document.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
   cell.setCellStyle(style);
  }catch(Exception e){
   System.out.println("Error in fillColor () --- " + e);
  }
 }
 
}

Read More