I have a servlet which generates the excel file and I use an excel templete to push data from my servlet to the excel file. The problem is that I can't save the excel file, well I can save it to disk but the file is very strange( not an excel file ). My servlet is as follows: /* * Copyright (c) 2002, Nordea * All rights reserved. */ package no.nordea.mo.es.web.servlet; import java.text.ParsePosition; import com.nordea.coffee.util.Log; import com.nordea.coffee.util.LogMessage; import no.nordea.mo.common.model.CustomerModel; import no.nordea.mo.common.model.GeoAddressModel; import no.nordea.mo.common.model.PersonModel; import no.nordea.mo.engagement.model.EngagementModel; import no.nordea.mo.engagement.web.EngagementForm; import no.nordea.mo.es.model.TxModel; import no.nordea.mo.es.web.bean.currentAccTrans; import no.nordea.mo.es.web.forms.ComplexSearchForm; import no.nordea.mo.es.web.forms.ESSessionStateForm; import no.nordea.mo.ks.model.KSEngagementModel; //import org.apache.poi.hssf.usermodel.*; //import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import no.nordea.mo.es.api.ESService; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import no.nordea.mo.common.util.Props; import java.math.BigDecimal; import no.nordea.mo.es.web.actions.performSearchAction; import no.nordea.mo.es.command.TransactionSearchCommand; import no.nordea.mo.es.detail.EntryDetail; import no.nordea.mo.es.detail.EntryPk; import no.nordea.mo.es.detail.TransactionSearchDetail; import no.nordea.mo.es.detail.TransactionSearchPk; import com.nordea.coffee.CfApplicationException; import java.text.SimpleDateFormat; import no.nordea.mo.common.util.KwsConstants; import no.nordea.mo.es.bean.LabelValueBean; import java.util.Calendar; /** * Describe what this class does * * @author AC08772 * @version 4.0 $Revision: 1.9 $ */ public class ExcelServlet extends HttpServlet { private final static String CONTENT_TYPE = "application/vnd.ms-excel"; private static transient Log log = Log.getInstance (ExcelServlet.class.getName()); private static boolean isTest = false; private ServletOutputStream out; //Initialize global variables /** * Describe what the method does * * @exception ServletException Describe the exception */ public void init() throws ServletException { } public void doDelete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { log.debug(new LogMessage("doDelete", "method entered")); } //Process the HTTP Get request /** * Describe what the method does * * @param request Describe what the parameter does * @param response Describe what the parameter does * @exception ServletException Describe the exception * @exception IOException Describe the exception */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { log.debug(new LogMessage("doGet", "method entered")); out = response.getOutputStream(); response.setContentType(CONTENT_TYPE); ESSessionStateForm essForm = (ESSessionStateForm) request.getSession ().getAttribute("essForm"); EngagementForm engagementForm = (EngagementForm) request.getSession ().getAttribute("engagementForm"); ComplexSearchForm complexForm = (ComplexSearchForm) essForm.getSearchForms(); // Get the request variable String accountIndex = request.getParameter("accountIndex"); HSSFWorkbook workbook = null; int index = 0; try { fetchResults(complexForm); if(accountIndex != null) { index = Integer.parseInt(accountIndex); workbook = createExcelDocument(essForm, complexForm, engagementForm, index); } else { workbook = createExcelDocument(essForm, complexForm, engagementForm); } workbook.write(out); out.close(); } catch (Exception e) { log.error(new LogMessage("doGet", "Error creating excel document")); } } //Clean up resources /** * Describe what the method does * */ public void destroy() { } public static void main(String[] argvs) { ExcelServlet servlet = new ExcelServlet(); ESSessionStateForm essForm = new ESSessionStateForm(); // All the engagement models we need EngagementModel model = new EngagementModel(); EngagementForm form = new EngagementForm(); KSEngagementModel ksEngagementModel = new KSEngagementModel(); // The data model we need PersonModel personmodel = new PersonModel(); GeoAddressModel geoaddressmodel = new GeoAddressModel(); CustomerModel customermodel = new CustomerModel(); // Create a person personmodel.setFirstName("Tor"); personmodel.setLastName("Major"); // Give him an address geoaddressmodel.setPostalArea("Oslo"); geoaddressmodel.setPostalCode("0107"); geoaddressmodel.setStreetAddress("Essendropsgate 7"); // Add the information to the customer model customermodel.setPerson(personmodel); customermodel.setAddress(geoaddressmodel); // Add the customer model to the KS model to simulate a retrival from Host ksEngagementModel.setCustomer(customermodel); // Prepare the Engagement model with the fake host result and place in the engagement form model.setKs(ksEngagementModel); form.setEngagementModel(model); // Simulate a fake search result ComplexSearchForm complexsearchform = new ComplexSearchForm(); /* Calendar fromDate = Calendar.getInstance(); fromDate.set(2002, 11, 1); Calendar toDate = Calendar.getInstance(); toDate.set(2002, 12, 31); complexsearchform.setStartDate(fromDate.getTime()); complexsearchform.setEndDate(toDate.getTime());*/ complexsearchform.setStartdate("2002-11-01"); complexsearchform.setEnddate("2002-12-31"); complexsearchform.addAccountToList("600308080800"); complexsearchform.setAmountFrom("0"); complexsearchform.setAmountTo("99999999"); complexsearchform.setSelectedCurrency("NOK"); currentAccTrans cacctrans = new currentAccTrans(); cacctrans.setAccountNr("600308080800"); TxModel txmodel = new TxModel(); txmodel.setAmount(100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-1000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(4000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); complexsearchform.addToResultTrans(cacctrans); complexsearchform.setDebit(true); // ############################# Account number two complexsearchform.addAccountToList("600308080900"); cacctrans = new currentAccTrans(); cacctrans.setAccountNr("600308080900"); txmodel = new TxModel(); txmodel.setAmount(100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-1000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(4000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); complexsearchform.addToResultTrans(cacctrans); // ############################# Account number two complexsearchform.addAccountToList("600308080300"); cacctrans = new currentAccTrans(); cacctrans.setAccountNr("600308080300"); txmodel = new TxModel(); txmodel.setAmount(100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-100); txmodel.setEntryDate(new Date()); txmodel.setTxType("Kort"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(-1000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); txmodel = new TxModel(); txmodel.setAmount(4000); txmodel.setEntryDate(new Date()); txmodel.setTxType("Betaling"); txmodel.setTransTypeTXT("Testing transaction Negative"); cacctrans.addTxModelToList(txmodel); complexsearchform.addToResultTrans(cacctrans); String accounts[] = {"600308080800", "600308080900", "600308080300"}; complexsearchform.setSelectedAccount(accounts); try { servlet.fetchResults(complexsearchform); servlet.createExcelDocument(essForm, complexsearchform, form); //servlet.createJXExcelDocument(essForm, complexsearchform, form); } catch (Exception ex) { ex.printStackTrace(); } } public HSSFWorkbook createExcelDocument(ESSessionStateForm essForm, ComplexSearchForm complexForm, EngagementForm engagementform) throws Exception { return createExcelDocument(essForm, complexForm, engagementform, -1); } public HSSFWorkbook createExcelDocument(ESSessionStateForm essForm, ComplexSearchForm complexForm, EngagementForm engagementform, int index) throws Exception { try { POIFSFileSystem fsfilesystem = new POIFSFileSystem(new FileInputStream(Props.getProperty("mo", "excel.template"))); HSSFWorkbook workbook = new HSSFWorkbook(fsfilesystem); // Get engagement model EngagementModel engagementmodel = engagementform.getEngagementModel (); // Ok we now have a workbook based on our template, we need to get the number of accounts available ArrayList accountList = complexForm.getAccountList(); int numberOfAccounts = accountList.size(); HSSFSheet movementssheet = workbook.getSheet("Bevegelser"); // Check that the template is valid if (movementssheet != null) { // Fetch the Customer information first CustomerModel customerModel = engagementmodel.getCustomer(); GeoAddressModel geoAddressModel = customerModel.getAddress(); PersonModel personModel = customerModel.getPerson(); // Set the First and last name HSSFRow row = movementssheet.getRow(4); HSSFCell cell = row.getCell((short) 1); cell.setCellValue(personModel.getFirstName() + " " + personModel.getLastName()); // Set the street address row = movementssheet.getRow(5); cell = row.getCell((short) 1); cell.setCellValue(geoAddressModel.getStreetAddress()); // Set postcode and postplace row = movementssheet.getRow(6); cell = row.getCell((short) 1); cell.setCellValue(geoAddressModel.getPostalCode() + " " + geoAddressModel.getPostalArea()); // Set from and to date for the search row = movementssheet.getRow(5); cell = row.getCell((short) 7); cell.setCellValue(complexForm.getStartdate()); cell = row.getCell((short) 9); cell.setCellValue(complexForm.getEnddate()); Iterator i = null; // Ok we want to ensure that we only pick one account if it is specified if(index != -1) { // Fetch the account we want to look up //String accountString = (String) complexForm.getAccountList().get(index); String accountString = ((currentAccTrans)complexForm.getResultTrans().get(index)).getAccountNr(); // Create an arrayList to ensure that we get what we need ArrayList newList = new ArrayList(); // Iterate through all the results [accouts] i = complexForm.getResultTrans ().iterator(); while(i.hasNext()) { // Find the account we want and ensure that only that one is shown currentAccTrans accTrans = (currentAccTrans) i.next(); if (accTrans.getAccountNr().equals(accountString)) newList.add (accTrans); } i = newList.iterator(); } else { i = complexForm.getResultTrans().iterator(); } // OK we need to check how many accounts we have int accountpointer = 8; while (i.hasNext()) { // Get the account object currentAccTrans accTrans = (currentAccTrans) i.next(); // Copying from the template to the actual workbook HSSFSheet templatesheet = workbook.getSheet("Template"); for (int rownr = 0; rownr < 6; rownr++) { int numberofrowsrepeated = 0; int numberoftransactions = 1; if (rownr == 2) { numberoftransactions = accTrans.getTxModelList ().size(); } // Copy the row a #numberofrowsrepeated times for (; numberofrowsrepeated < numberoftransactions; numberofrowsrepeated++, accountpointer++) { HSSFRow selectedrow = templatesheet.getRow(rownr); HSSFRow copytorow = movementssheet.getRow (accountpointer); // Insert a fresh row if none exists if (copytorow == null) copytorow = movementssheet.createRow((short) accountpointer); // Set the hight to reflect the original rows height copytorow.setHeight(selectedrow.getHeight()); // Ensure that all cells are transfered correctly with information about state for (int columnnr = 0; columnnr < 10; columnnr++) { HSSFCell selectedcell = selectedrow.getCell ((short) columnnr); HSSFCell copytocell = copytorow.createCell ((short) columnnr); // Copy the content of the cell if (selectedcell != null) { if (selectedcell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { copytocell.setCellValue (selectedcell.getBooleanCellValue()); } else if (selectedcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { copytocell.setCellValue (selectedcell.getNumericCellValue()); } else if (selectedcell.getCellType() == HSSFCell.CELL_TYPE_STRING) { copytocell.setCellValue (selectedcell.getStringCellValue()); } else if (selectedcell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { copytocell.setCellValue (selectedcell.getCellFormula()); } // Transfer the cell style and ensure that wrap is off copytocell.setCellStyle (selectedcell.getCellStyle()); HSSFCellStyle style = copytocell.getCellStyle(); style.setWrapText(false); } } } // Add the account info if (rownr == 0) { row = movementssheet.getRow(accountpointer - 1); cell = row.getCell((short) 5); cell.setCellValue("Konto nummer: " + accTrans.getAccountNr()); } } // Get the transactions Iterator iTxModel = accTrans.getTxModelList().iterator(); // Set the starting point for the actual writing of the data int startpoint = accountpointer - accTrans.getTxModelList ().size() - 3; // Iterate over all the transactions while (iTxModel.hasNext()) { TxModel model = (TxModel) iTxModel.next(); HSSFRow moverow = movementssheet.getRow(startpoint); HSSFCell movecell = moverow.getCell((short) 1); movecell.setCellValue(model.getMyEntryDate()); movecell = moverow.getCell((short) 3); movecell.setCellValue(model.getTxType()); movecell = moverow.getCell((short) 5); movecell.setCellValue(model.getTransTypeTXT()); double innamount = 0, outamount = 0; if (model.getAmount() >= 0) { innamount = model.getAmount(); outamount = 0; movecell = moverow.getCell((short) 9); movecell.setCellValue(innamount); } else { innamount = 0; outamount = model.getAmount(); movecell = moverow.getCell((short) 7); movecell.setCellValue(outamount); } // Write the amount into the excel spreadheet cell startpoint++; } // Now we need to specify the formulas so that the values are computed correctly HSSFRow choosenrow = movementssheet.getRow(startpoint); // Chose the sub sum row, now insert the formulas neccessary HSSFCell outformulacell = choosenrow.getCell((short) 7); outformulacell.setCellFormula("SUM(H" + (startpoint - accTrans.getTxModelList().size() + 1) + ":H" + startpoint + ")"); HSSFCell informulacell = choosenrow.getCell((short) 9); informulacell.setCellFormula("SUM(J" + (startpoint - accTrans.getTxModelList().size() + 1) + ":J" + startpoint + ")"); // Chose the last row and insert the final summation choosenrow = movementssheet.getRow(startpoint + 2); HSSFCell balancecell = choosenrow.getCell((short) 9); balancecell.setCellFormula("H" + (startpoint + 1) + " + J" + (startpoint + 1)); // Insert to empty rows to ensure that we have space between accounts if there are more than one movementssheet.createRow((short)accountpointer); movementssheet.createRow((short)accountpointer + 1); // Adjust start pointer accountpointer += 2; } } // Delete template sheet to ensure that its not available int sheetIndex = workbook.getSheetIndex("Template"); workbook.removeSheetAt(sheetIndex); movementssheet.setSelected(true); // workbook.createSheet(""); return workbook; } catch (IOException e) { e.printStackTrace(); //To change body of catch statement use Options | File Templates. return null; } } public void fetchResults(ComplexSearchForm complexSearchForm) throws CfApplicationException { String METHOD_NAME = "fetchResults"; TransactionSearchCommand tc = new TransactionSearchCommand(); TransactionSearchDetail detail = null; BigDecimal fromAmount = null, toAmount = null; Character sign = null; complexSearchForm.clearResultTrans(); /* * Debit Credit */ if (complexSearchForm.getDebit() && ! complexSearchForm.getCredit()) { sign = TransactionSearchPk.DEBIT; fromAmount = performSearchAction.findFromAmount (sign.charValue(), complexSearchForm.getAmountFrom()); toAmount = performSearchAction.findToAmount(sign.charValue (), complexSearchForm.getAmountTo()); } else if (complexSearchForm.getCredit() && ! complexSearchForm.getDebit()) { sign = TransactionSearchPk.CREDIT; fromAmount = performSearchAction.findFromAmount (sign.charValue(), complexSearchForm.getAmountFrom()); toAmount = performSearchAction.findToAmount(sign.charValue (), complexSearchForm.getAmountTo()); } else if (complexSearchForm.getDebit() && complexSearchForm.getCredit()) { sign = TransactionSearchPk.DEBIT_CREDIT; fromAmount = performSearchAction.findFromAmount (sign.charValue(), complexSearchForm.getAmountFrom()); toAmount = performSearchAction.findToAmount(sign.charValue (), complexSearchForm.getAmountTo()); } else { log.error(new LogMessage(METHOD_NAME, "You have to choose either Debit, Credit or both")); throw new CfApplicationException ("kws.es.chooseDebitCredit", "You have to choose either Debit, Credit or both"); } // Perform the search and get the detail object for (int i = 0; i < complexSearchForm.getSelectedAccount ().length; i++) { int transNr = 0; BigDecimal accountNo = new BigDecimal (complexSearchForm.getSelectedAccount()[i]); SimpleDateFormat formatter = new SimpleDateFormat (KwsConstants.DATEFORMAT_FULL); Date startDate = formatter.parse(complexSearchForm.getStartdate(), new ParsePosition(0)); Date endDate = formatter.parse(complexSearchForm.getEnddate(), new ParsePosition(0)); TransactionSearchPk pk = new TransactionSearchPk(accountNo, startDate, endDate, fromAmount, toAmount, sign, complexSearchForm.getSelectedCurrency(), complexSearchForm.getSearchText(), TransactionSearchPk.BA, new Integer(Integer.MAX_VALUE)); tc.setPrimaryKey(pk); detail = (TransactionSearchDetail) tc.execute(); log.debug(new LogMessage("performExecute", "transactions retrieve time: " + detail.getExecutionTime())); ArrayList entryDetails = detail.getEntryDetails(); currentAccTrans currAccTrans = new currentAccTrans(); currAccTrans.setAccountNr(accountNo.toString()); log.debug("Total number of entries: " + detail.getTOT_NUM_ENTRIES()); currAccTrans.setTOT_NUM_ENTRIES(detail.getTOT_NUM_ENTRIES ()); currAccTrans.setNumOfTrans(detail.getTOT_NUM_ENTRIES()); currAccTrans.setNumOfEntries(new Integer (Integer.MAX_VALUE)); currAccTrans.setPk((TransactionSearchPk) detail.getPrimaryKey()); pk = (TransactionSearchPk) detail.getPrimaryKey(); currAccTrans.setPageNumber(pk.getCurrentPage()); for (Iterator iter = entryDetails.iterator(); iter.hasNext (); ) { EntryDetail entrydetail = (EntryDetail) iter.next(); TxModel model = new TxModel(); model.setAmount(entrydetail.getENTR_AMOUNT ().doubleValue()); model.setTxType(entrydetail.getTEXTCODE_TEXT()); model.setPaymentDate(entrydetail.getPAID_DATE()); model.setEntryDate(entrydetail.getBOBA_BOOKING_DATE()); SimpleDateFormat df = new SimpleDateFormat (KwsConstants.DATEFORMAT_FULL); model.addToTransTypefRest(new LabelValueBean ("OPPDRAGSDATO:", df.format(entrydetail.getPAID_DATE()).toString())); model.setTranNr(transNr); model.setExpand(false); String transTypeTXT = ""; String friTXT = ""; for (Iterator iter1 = entrydetail.getTEXT_ENTRIES ().iterator(); iter1.hasNext(); ) { String entryText = (String) iter1.next(); if (entryText.substring(0, 3).equalsIgnoreCase ("TXT")) { transTypeTXT += entryText.substring(3, entryText.length()); transTypeTXT += " "; } else if (entryText.indexOf((char) 141) != -1) { model.addToTransTypefRest (performSearchAction.constructLabelValueBean(entryText)); } else { if (entryText.substring(0, 3).equalsIgnoreCase ("BGR")) { friTXT += entryText.substring(3, entryText.length()); friTXT += " "; } if (entryText.substring(0, 3).equalsIgnoreCase ("VBL")) { model.addToTransTypefRest(new LabelValueBean("VALUTATRANS :", entryText.substring(3, entryText.length()))); } model.setTransTypeWithoutText (entryText.substring(3, entryText.length())); } model.setTransTypeTXT(transTypeTXT); } if (transTypeTXT.equals("")) { model.setTransTypeTXT(friTXT); } currAccTrans.setBalance(entrydetail.getENTR_AMOUNT ().doubleValue()); currAccTrans.addTxModelToList(model); transNr++; } complexSearchForm.addToResultTrans(currAccTrans); } } public void setIsTest ( boolean isTest) { this.isTest = isTest; } }
Created attachment 14782 [details] Generated excel file Generated excel file
Created attachment 14783 [details] Template file Template file
This bug, as attached, fails to show any bug in POI. The code attached fails to compile, making it impossible to make sense out of it. "Very strange" is not something anyone can debug.
I am adding a very simple code which will compile and I am facing same problem with this simple code. The genarated excel file looks very strange. Could it be something with my excel template ? package no.nordea.klow.web.actions; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.ServletOutputStream; import javax.servlet.ServletException; import java.io.IOException; import java.io.FileInputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class ExcelServlet extends HttpServlet { private final static String CONTENT_TYPE = "application/vnd.ms-excel"; private ServletOutputStream out; public void init() throws ServletException { } public void doDelete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("doDelete method entered"); } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("doGet method entered"); out = response.getOutputStream(); response.setContentType(CONTENT_TYPE); HSSFWorkbook workbook = null; try { POIFSFileSystem fsfilesystem = new POIFSFileSystem(new FileInputStream("C:\\MO\\nordea.xls")); workbook = new HSSFWorkbook(fsfilesystem); workbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } }