private MemoryFileBuffer writeXls(IWContext iwc) throws IOException {
MemoryFileBuffer buffer = new MemoryFileBuffer();
MemoryOutputStream mos = new MemoryOutputStream(buffer);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Rejected students list");
int rowNum = 0;
HSSFRow row = sheet.createRow((short) rowNum++);
fillHeaderRow(wb, sheet, row);
// retrieve data
Collection schoolChoices = getSchoolChoices(iwc);
// fill data into rows
if (!schoolChoices.isEmpty()) {
User applicant;
School school;
Address address;
Phone phone;
SchoolChoice choice;
String name = null;
for (Iterator iter = schoolChoices.iterator(); iter.hasNext(); ) {
choice = (SchoolChoice) iter.next();
row = sheet.createRow((short) rowNum++);
try {
applicant = getUserBusiness(iwc).getUser(choice.getChildId());
school =
getSchoolCommuneBusiness(iwc)
.getSchoolBusiness()
.getSchool(new Integer(choice.getCurrentSchoolId()));
address = getUserBusiness(iwc).getUsersMainAddress(applicant);
name = getSchoolCommuneBusiness(iwc).getUserBusiness().getNameLastFirst(applicant, true);
row.createCell((short) 0).setCellValue(name);
if (applicant.getPersonalID() != null) {
row.createCell((short) 1)
.setCellValue(PersonalIDFormatter.format(applicant.getPersonalID(), this.locale));
}
String emails = this.getParentsEmails(iwc, applicant);
if (emails != null) {
row.createCell((short) 2).setCellValue(emails);
}
if (address != null) {
row.createCell((short) 3).setCellValue(address.getStreetAddress());
row.createCell((short) 4).setCellValue(address.getPostalCode().getPostalCode());
row.createCell((short) 5).setCellValue(address.getCity());
}
try {
phone = getUserBusiness(iwc).getUsersHomePhone(applicant);
// getUserBusiness(iwc).getUsers
if (phone != null && phone.getNumber() != null) {
row.createCell((short) 6).setCellValue(phone.getNumber());
}
} catch (NoPhoneFoundException npfe) {
npfe.printStackTrace();
}
String genderString = null;
if (PIDChecker.getInstance().isFemale(applicant.getPersonalID())) {
genderString = this.iwrb.getLocalizedString("school.girl", "Girl");
} else {
genderString = this.iwrb.getLocalizedString("school.boy", "Boy");
}
row.createCell((short) 7).setCellValue(genderString);
if (school != null) {
String schoolName = school.getName();
row.createCell((short) 8).setCellValue(schoolName);
}
String rejectionDateString =
getLocalizedTimestamp(iwc, getRejectionTimestamp(iwc, choice));
if (rejectionDateString != null) {
row.createCell((short) 9).setCellValue(rejectionDateString);
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
wb.write(mos);
buffer.setMimeType("application/x-msexcel");
return buffer;
}
public static MemoryFileBuffer writeStickerList(Report report, ReportInfo info) {
Connection Conn = null;
Statement stmt = null;
ResultSet RS = null;
MemoryFileBuffer buffer = new MemoryFileBuffer();
MemoryOutputStream mos = new MemoryOutputStream(buffer);
try {
String[] Headers = report.getHeaders();
int Hlen = Headers.length;
String sql = report.getSQL();
// String file = realpath;
List cinfos = ReportFinder.listOfReportColumnInfo(report.getID());
ReportColumnInfo rinfo;
String[] endstrings = new String[Hlen];
Font[] fonts = new Font[Hlen];
int[] spans = new int[Hlen];
int listsize = cinfos != null ? cinfos.size() : 0;
for (int i = 0; i < Hlen; i++) {
if (i < listsize) {
rinfo = (ReportColumnInfo) cinfos.get(i);
fonts[i] = getFont(rinfo);
/** @todo endstring fix */
endstrings[i] = "\n";
spans[i] = rinfo.getColumnSpan();
} else {
fonts[i] = getFont(null);
endstrings[i] = "\n";
spans[i] = 1;
}
}
Conn = com.idega.util.database.ConnectionBroker.getConnection();
stmt = Conn.createStatement();
RS = stmt.executeQuery(sql);
StickerList list = new StickerList();
list.setStickerHeight(info.getHeight());
list.setStickerWidth(info.getWidth());
list.setBorder(info.getBorder());
list.setRotation(info.getLandscape());
list.setPageSize(ReportFinder.getPageSize(info.getPagesize()));
Paragraph parag;
while (RS.next()) {
parag = new Paragraph();
for (int i = 1; i <= Hlen; i++) {
String s = RS.getString(i);
// if(!RS.wasNull())
if (s != null) {
parag.add(new Chunk(s, fonts[i - 1]));
}
// parag.add(new Chunk(RS.getString(i),fonts[i-1]));
parag.add(endstrings[i - 1]);
}
list.add(parag);
}
StickerWriter.print(mos, list);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
// do not hide an existing exception
try {
if (RS != null) {
RS.close();
}
} catch (SQLException resultCloseEx) {
System.err.println("[StickerReport] result set could not be closed");
resultCloseEx.printStackTrace(System.err);
}
// do not hide an existing exception
try {
if (stmt != null) {
stmt.close();
ConnectionBroker.freeConnection(Conn);
}
} catch (SQLException statementCloseEx) {
System.err.println("[StickerReport] statement could not be closed");
statementCloseEx.printStackTrace(System.err);
}
}
buffer.setMimeType("application/pdf");
return buffer;
}