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);
}
}
}
0 comments:
Post a Comment