javapoi-hssf
how to create dynamic rows of records for creating xls file using list
Here after fetching records from database I have added data in some list and I have set some session variables for them so that I can access in another method by using get(key) method of session which I am successful to do so .Now what I want is I want to create dynamic records by setting this list value in row but I am unable to do so.It creates file but there is no record displayed .Below is my code:
package com.ca.actions;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;
import com.ca.database.Database;
import com.ca.pojo.Event;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import com.opensymphony.xwork2.ActionSupport;
import com.opensymphony.xwork2.Preparable;
public class DataForGeneralReportsAction extends ActionSupport implements
Preparable, SessionAware {
private List<String> eventsGeneral = new ArrayList<String>();
private List<String> companiesGeneral = new ArrayList<String>();
private SessionMap<String, Object> sessionMapGeneral;
List<String> eventIdList = new ArrayList<String>();
List<String> eventNameList = new ArrayList<String>();
List<String> eventVenueList = new ArrayList<String>();
List<String> eventTimeList = new ArrayList<String>();
List<String> companyNameList = new ArrayList<String>();
List<String> totalAmountList = new ArrayList<String>();
List<String> receivedAmountList = new ArrayList<String>();
List<String> balanceAmountList = new ArrayList<String>();
List<String> eventTdsList = new ArrayList<String>();
List<String> paymentDateList = new ArrayList<String>();
List<String> chequeDdList = new ArrayList<String>();
private String eventGeneral = null;
private String companyGeneral = null;
List<Event> dataForGeneralReports;
public List<String> getEventIdList() {
return eventIdList;
}
public void setEventIdList(List<String> eventIdList) {
this.eventIdList = eventIdList;
}
public List<String> getEventNameList() {
return eventNameList;
}
public void setEventNameList(List<String> eventNameList) {
this.eventNameList = eventNameList;
}
public List<String> getEventVenueList() {
return eventVenueList;
}
public void setEventVenueList(List<String> eventVenueList) {
this.eventVenueList = eventVenueList;
}
public List<String> getEventTimeList() {
return eventTimeList;
}
public void setEventTimeList(List<String> eventTimeList) {
this.eventTimeList = eventTimeList;
}
public List<String> getCompanyNameList() {
return companyNameList;
}
public void setCompanyNameList(List<String> companyNameList) {
this.companyNameList = companyNameList;
}
public List<String> getTotalAmountList() {
return totalAmountList;
}
public void setTotalAmountList(List<String> totalAmountList) {
this.totalAmountList = totalAmountList;
}
public List<String> getReceivedAmountList() {
return receivedAmountList;
}
public void setReceivedAmountList(List<String> receivedAmountList) {
this.receivedAmountList = receivedAmountList;
}
public List<String> getBalanceAmountList() {
return balanceAmountList;
}
public void setBalanceAmountList(List<String> balanceAmountList) {
this.balanceAmountList = balanceAmountList;
}
public List<String> getEventTdsList() {
return eventTdsList;
}
public void setEventTdsList(List<String> eventTdsList) {
this.eventTdsList = eventTdsList;
}
public List<String> getPaymentDateList() {
return paymentDateList;
}
public void setPaymentDateList(List<String> paymentDateList) {
this.paymentDateList = paymentDateList;
}
public List<String> getChequeDdList() {
return chequeDdList;
}
public void setChequeDdList(List<String> chequeDdList) {
this.chequeDdList = chequeDdList;
}
public SessionMap<String, Object> getSessionMapGeneral() {
return sessionMapGeneral;
}
public void setSessionMapGeneral(
SessionMap<String, Object> sessionMapGeneral) {
this.sessionMapGeneral = sessionMapGeneral;
}
public String getEventGeneral() {
return eventGeneral;
}
public void setEventGeneral(String eventGeneral) {
this.eventGeneral = eventGeneral;
}
public String getCompanyGeneral() {
return companyGeneral;
}
public void setCompanyGeneral(String companyGeneral) {
this.companyGeneral = companyGeneral;
}
public List<Event> getDataForGeneralReports() {
return dataForGeneralReports;
}
public void setDataForGeneralReports(List<Event> dataForGeneralReports) {
this.dataForGeneralReports = dataForGeneralReports;
}
public List<String> getEventsGeneral() {
return eventsGeneral;
}
public void setEventsGeneral(List<String> eventsGeneral) {
this.eventsGeneral = eventsGeneral;
}
public List<String> getCompaniesGeneral() {
return companiesGeneral;
}
public void setCompaniesGeneral(List<String> companiesGeneral) {
this.companiesGeneral = companiesGeneral;
}
public DataForGeneralReportsAction() {
// TODO Auto-generated constructor stub
}
@Override
public void prepare() throws Exception {
// TODO Auto-generated method stub
Connection con = null;
try {
con = new Database().Get_Connection();
// load companies
PreparedStatement ps = con
.prepareStatement("SELECT DISTINCT company_name FROM event");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
companiesGeneral.add(rs.getString("company_name"));
}
// load events
ps = con.prepareStatement("SELECT DISTINCT event_name FROM event");
rs = ps.executeQuery();
while (rs.next()) {
eventsGeneral.add(rs.getString("event_name"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
con.close();
}
}
@Override
public String execute() throws Exception {
Connection con = null;
try {
con = new Database().Get_Connection();
// load the table. The first time the table is loaded completely
String sql = "SELECT EVENT_ID, EVENT_NAME, COMPANY_NAME,EVENT_VENUE,TOTAL_AMOUNT,RECEIVED_AMOUNT,EVENT_TDS,BALANCE_AMOUNT,CHEQUE_DD_NO,"
+ "date_format(PAYMENT_DATE,'%d/%m/%Y') as dateAsPayment,EVENT_TIME "
+ "FROM event";
String where = "";
// if instead this action has been called from the JSP page,
// the result is filtered on event and company:
if (eventGeneral != null && companyGeneral != null) {
where = " WHERE event_name = ? AND company_name = ?";
}
// load companies
PreparedStatement ps = con.prepareStatement(sql + where);
if (where.length() > 0) {
ps.setString(1, eventGeneral);
ps.setString(2, companyGeneral);
}
dataForGeneralReports = new ArrayList<Event>();
ResultSet rs = ps.executeQuery();
int i, j = 0;
while (rs.next()) {
dataForGeneralReports.add(new Event(rs.getString("EVENT_ID"),
rs.getString("EVENT_NAME"), rs
.getString("COMPANY_NAME"), rs
.getString("EVENT_VENUE"), rs
.getString("EVENT_TIME"), rs
.getString("TOTAL_AMOUNT"), rs
.getString("RECEIVED_AMOUNT"), rs
.getString("CHEQUE_DD_NO"), rs
.getString("dateAsPayment"), rs
.getString("BALANCE_AMOUNT"), rs
.getString("EVENT_TDS")));
eventIdList.add(rs.getString("EVENT_ID"));
eventNameList.add(rs.getString("EVENT_NAME"));
companyNameList.add(rs.getString("COMPANY_NAME"));
eventVenueList.add(rs.getString("EVENT_VENUE"));
eventTimeList.add(rs.getString("EVENT_TIME"));
totalAmountList.add(rs.getString("TOTAL_AMOUNT"));
receivedAmountList.add(rs.getString("RECEIVED_AMOUNT"));
chequeDdList.add(rs.getString("CHEQUE_DD_NO"));
paymentDateList.add(rs.getString("dateAsPayment"));
eventTdsList.add(rs.getString("EVENT_TDS"));
balanceAmountList.add(rs.getString("BALANCE_AMOUNT"));
}
sessionMapGeneral.put("eventIdPdf", eventIdList);
sessionMapGeneral.put("eventNamePdf", eventNameList);
sessionMapGeneral.put("companyNamePdf", companyNameList);
sessionMapGeneral.put("eventVenuePdf", eventVenueList);
sessionMapGeneral.put("eventTimePdf", eventTimeList);
sessionMapGeneral.put("totalAmountPdf", totalAmountList);
sessionMapGeneral.put("receivedAmountPdf", receivedAmountList);
sessionMapGeneral.put("chequeDdPdf", chequeDdList);
sessionMapGeneral.put("paymentDatePdf", paymentDateList);
sessionMapGeneral.put("eventTdsPdf", eventTdsList);
sessionMapGeneral.put("balanceAmountPdf", balanceAmountList);
} catch (Exception e) {
e.printStackTrace();
} finally {
con.close();
}
return SUCCESS;
}
public String generatePdfGeneral() throws Exception {
System.out.println(sessionMapGeneral.get("eventIdPdf"));
Document document = new Document(PageSize.A4_LANDSCAPE, 50, 50, 50, 50);
float[] columnWidths = { 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5 };
PdfWriter writer = PdfWriter.getInstance(document,
new FileOutputStream("D:\\GeneralReports.pdf"));
PdfPTable table = new PdfPTable(11);
table.setSpacingBefore(25);
table.setWidthPercentage(100);
table.setSpacingAfter(25);
table.setWidths(columnWidths);
PdfPCell c1 = new PdfPCell(new Phrase("Event ID "));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Event Name "));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Event Time"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Event Venue"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Company Name"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Total Amount"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Received Amount"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Cheque/DD Number"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Payment Date"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Event TDS"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
c1 = new PdfPCell(new Phrase("Balance Amount"));
c1.setHorizontalAlignment(Element.ALIGN_CENTER);
table.addCell(c1);
table.setHeaderRows(1);
PdfPCell cell = new PdfPCell();
List<String> list = (List<String>) sessionMapGeneral.get("eventIdPdf");
for (String item : list) {
cell.addElement(new Paragraph(item));
}
PdfPCell cell1 = new PdfPCell();
List<String> list1 = (List<String>) sessionMapGeneral
.get("eventNamePdf");
for (String item : list1) {
cell1.addElement(new Paragraph(item));
}
table.addCell(cell1);
PdfPCell cell2 = new PdfPCell();
List<String> list2 = (List<String>) sessionMapGeneral
.get("eventTimePdf");
for (String item : list2) {
cell2.addElement(new Paragraph(item));
}
table.addCell(cell2);
PdfPCell cell3 = new PdfPCell();
List<String> list3 = (List<String>) sessionMapGeneral
.get("eventVenuePdf");
for (String item : list1) {
cell3.addElement(new Paragraph(item));
}
table.addCell(cell3);
PdfPCell cell4 = new PdfPCell();
List<String> list4 = (List<String>) sessionMapGeneral.get("eventIdPdf");
for (String item : list4) {
cell4.addElement(new Paragraph(item));
}
table.addCell(cell4);
PdfPCell cell5 = new PdfPCell();
List<String> list5 = (List<String>) sessionMapGeneral
.get("companyNamePdf");
for (String item : list5) {
cell5.addElement(new Paragraph(item));
}
table.addCell(cell5);
PdfPCell cell6 = new PdfPCell();
List<String> list6 = (List<String>) sessionMapGeneral
.get("totalAmountPdf");
for (String item : list6) {
cell6.addElement(new Paragraph(item));
}
table.addCell(cell6);
PdfPCell cell7 = new PdfPCell();
List<String> list7 = (List<String>) sessionMapGeneral
.get("receivedAmountPdf");
for (String item : list7) {
cell7.addElement(new Paragraph(item));
}
table.addCell(cell7);
PdfPCell cell8 = new PdfPCell();
List<String> list8 = (List<String>) sessionMapGeneral
.get("chequeDdPdf");
for (String item : list8) {
cell8.addElement(new Paragraph(item));
}
table.addCell(cell8);
PdfPCell cell9 = new PdfPCell();
List<String> list9 = (List<String>) sessionMapGeneral
.get("paymentDatePdf");
for (String item : list9) {
cell9.addElement(new Paragraph(item));
}
table.addCell(cell9);
PdfPCell cell10 = new PdfPCell();
List<String> list10 = (List<String>) sessionMapGeneral
.get("eventTdsPdf");
for (String item : list10) {
cell10.addElement(new Paragraph(item));
}
table.addCell(cell10);
PdfPCell cell11 = new PdfPCell();
List<String> list11 = (List<String>) sessionMapGeneral
.get("balanceAmountPdf");
for (String item : list11) {
cell11.addElement(new Paragraph(item));
}
table.addCell(cell11);
document.open();
document.add(table);
document.close();
return "success";
}
public String generateGeneralXls() throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FirstSheet");
HSSFRow rowhead = sheet.createRow((short) 0);
rowhead.createCell(0).setCellValue("Event ID");
rowhead.createCell(1).setCellValue("Event Name");
rowhead.createCell(2).setCellValue("Event Time");
rowhead.createCell(3).setCellValue("Event Venue");
rowhead.createCell(4).setCellValue("Company Name");
rowhead.createCell(5).setCellValue("Total Amount");
rowhead.createCell(6).setCellValue("Received Amount");
rowhead.createCell(7).setCellValue("Payment Date");
rowhead.createCell(8).setCellValue("Cheque/DD No.");
rowhead.createCell(9).setCellValue("Event TDS");
rowhead.createCell(10).setCellValue("Balance Amount");
FileOutputStream fileOut;
fileOut = new FileOutputStream("D:\\Samplmgjkm.xls");
// HSSFRow row1 = sheet.createRow((short) 1);
System.out.println(sessionMapGeneral.size());
for (int i = 1; i <= sessionMapGeneral.size(); i++) {
HSSFRow row1 = sheet.createRow((short) i);
row1.createCell(i-1).setCellValue(
sessionMapGeneral.get("eventIdPdf").toString());
row1.createCell(i).setCellValue(
sessionMapGeneral.get("eventNamePdf").toString());
}
/*
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
* row1.createCell(1).setCellValue(
* sessionMapGeneral.get("eventNamePdf").toString());
*/
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
return "success";
}
@Override
public void setSession(Map<String, Object> map) {
// TODO Auto-generated method stub
sessionMapGeneral = (SessionMap) map;
}
}
I have edited my code where I get result in string but all records are displayed in single cell. I want each record in new cell.I have attached image of how it looks. 
No error is displayed. Please help me to solve my problem.
Solution
First poposal : use event object.You build a list of Event object you could use and put in SessionMap and if you want, build the other list using list of Events)
here and Exemple assuming you have getter in Event Object :
public String generateGeneralXls() throws Exception {
try {
String titles="Event ID,Event Name,Event Time,Event Venue,Company Name,Total Amount,Received Amount,Payment Date,Cheque/DD No,Event TDS,Balance Amount";
String[]arrTiltes=titles.split(",");
FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
int row=0;
HSSFRow rowTitle = worksheet.createRow(row);
// set titles
for(int i=0;i<arrTiltes.length;i++){
HSSFCell cellTitle=rowTitle.createCell(i);
cellTitle.setCellValue(arrTiltes[i]);
}
//setting values
row++;
for(Event dataValue:dataForGeneralReports){
HSSFRow rowValue = worksheet.createRow(row);
HSSFCell cell0 = rowValue.createCell(0);
cell0.setCellValue(dataValue.getEventID());
HSSFCell cell1 = rowValue.createCell(1);
cell1.setCellValue(dataValue.getEventName());
HSSFCell cell2 = rowValue.createCell(2);
cell2.setCellValue(dataValue.getEventTime());
HSSFCell cell3 = rowValue.createCell(3);
cell3.setCellValue(dataValue.getEventVenue());
HSSFCell cell4 = rowValue.createCell(4);
cell4.setCellValue(dataValue.getCompanyName());
HSSFCell cell5 = rowValue.createCell(5);
cell5.setCellValue(dataValue.getTotalAmount());
HSSFCell cell6 = rowValue.createCell(6);
cell6.setCellValue(dataValue.getReceivedAmount());
HSSFCell cell7 = rowValue.createCell(7);
cell7.setCellValue(dataValue.getPaymentDate());
HSSFCell cell8 = rowValue.createCell(8);
cell8.setCellValue(dataValue.getChequeDDNo());
HSSFCell cell9 = rowValue.createCell(9);
cell9.setCellValue(dataValue.getEventTDS());
HSSFCell cell10 = rowValue.createCell(10);
cell10.setCellValue(dataValue.getBalanceAmount());
row++;
}
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
return "success";
}
you can use the same idea for you PDF. be carreful in your code for PDF, i suspect you don't have results expected.
you have forgotten adding cell on table ( first cell on your code)
you have 12 cells however you declare 11:
i sugest you o remove "block" for
PdfPCell cell4
second proposal : if you want really use your lists, then iterate over each list to get first value in each of them, second value of each of them etc..
Exemple assuming all list have the same size (coming from your resultset):
public String generateGeneralXlsOther() throws Exception {
try {
String titles="eventIdPdf,eventNamePdf,companyNamePdf,eventVenuePdf,eventTimePdf,totalAmountPdf,receivedAmountPdf,receivedAmountPdf,chequeDdPdf,paymentDatePdf,eventTdsPdf,balanceAmountPdf";
String[]arrTiltes=titles.split(",");
FileOutputStream fileOut = new FileOutputStream("poi-testOtehr.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
HSSFRow rowTitle = worksheet.createRow(0);
// set titles
for(int i=0;i<arrTiltes.length;i++){
HSSFCell cellTitle=rowTitle.createCell(i);
cellTitle.setCellValue(arrTiltes[i]);
}
int size=((List<String>) sessionMapGeneral.get(arrTiltes[0])).size();
for(int row=0;row<size;row++){
HSSFRow rowValue = worksheet.createRow(row+1);
int cell=0;
for(int i=0;i<arrTiltes.length;i++){
List<String> theList=(List<String>) sessionMapGeneral.get(arrTiltes[i]);
HSSFCell cell0 = rowValue.createCell(cell);
cell0.setCellValue(theList.get(row));
cell++;
}
}
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
return "success";
}
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices