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


Win a copy of Java 8 in Action this week in the Java 8 forum!
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.
 
Similar Threads
Issue with Download using ZipOutputStream
facing diffculty to highlight background color of an excel cell which is not reflected in file
writing into excel sheet
Save File on Client Machine
How to connect to Open Office Database using JDBC connection in Java