• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Jj Roberts
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Himai Minh
  • Carey Brown
  • salvin francis

Setting a Variable Cell size dynamically for Excel.

 
Greenhorn
Posts: 26
Hibernate jQuery Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
All of the following truths are shameless lies. But what about this tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic