aspose file tools*
The moose likes Java in General and the fly likes Setting a Variable Cell size dynamically for Excel. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Setting a Variable Cell size dynamically for Excel." Watch "Setting a Variable Cell size dynamically for Excel." New topic
Author

Setting a Variable Cell size dynamically for Excel.

shiva valanukonda
Greenhorn

Joined: Sep 27, 2010
Posts: 25

Hi,

I am writing a Result Set data into an Excel sheet. I am able to write into an Excel sheet but Row cells are not formatted properly (merged with other ).
I want to put a cell size dynamically based on the Result set data.


Here is my code .I have tried to do some Styles on the Row Head.. but i want to write the data in variable size based on the Result set data..


***************


package com.test.classes;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

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.hssf.util.HSSFColor;

public class CreateExcelFile{
public static void main(String[]args){

ConnectionUtil conn = new ConnectionUtil();
ResultSet rs;
Statement st;
try{
String filename="C:\\Documents and Settings\\shiva\\Desktop\\test\\data1.xls" ;
HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("RegisterGoods");

//


//
HSSFRow rowhead= sheet.createRow((short)0);

HSSFCell cell = rowhead.createCell((short) 0);
cell.setCellValue("SNo");

//apply some colors from the standard palette,
// as in the previous examples.
//we'll use red text on a lime background

HSSFCellStyle style = hwb.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);


HSSFFont font = hwb.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);

cell.setCellStyle(style);
//

//rowhead.createCell((short) 0).setCellValue("SNo");
rowhead.createCell((short) 1).setCellValue("PRODUCT");
rowhead.createCell((short) 2).setCellValue("QUANTITY");
rowhead.createCell((short) 3).setCellValue("AMOUNT No");

HSSFCell cellA =rowhead.createCell((short) 4);
cellA.setCellValue("PURPOSE");

HSSFCellStyle style1 = hwb.createCellStyle();
style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style1.setVerticalAlignment(HSSFCellStyle.BORDER_THICK);
style1.setBorderBottom(HSSFCellStyle.BORDER_THICK);
style1.setBorderLeft(HSSFCellStyle.BORDER_THICK);
style1.setBorderRight(HSSFCellStyle.BORDER_THICK);


HSSFFont font1 = hwb.createFont();
font1.setColor(HSSFColor.BLACK.index);
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style1.setFont(font1);

cellA.setCellStyle(style1);

rowhead.createCell((short) 5).setCellValue("PURCHASEDON");


Connection Excon = conn.makeConnection();
st=Excon.createStatement();
rs=st.executeQuery("select * from registergoods");
int i=1;
while(rs.next()){
HSSFRow row= sheet.createRow((short)i);
row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("id")));
row.createCell((short) 1).setCellValue(rs.getString("PRODUCT"));
row.createCell((short) 2).setCellValue(Integer.toString(rs.getInt("QUANTITY")));
row.createCell((short) 3).setCellValue(Integer.toString(rs.getInt("AMOUNT")));
row.createCell((short) 4).setCellValue(rs.getString("PURPOSE"));
row.createCell((short) 5).setCellValue(rs.getString("PURCHASEDON"));


i++;
}
FileOutputStream fileOut = new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");
rs.close();
st.close();
Excon.close();

} catch ( Exception ex ) {
System.out.println(ex);

}
}
}

*******************


Thanks

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Setting a Variable Cell size dynamically for Excel.