DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG� RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT <http://issues.apache.org/bugzilla/show_bug.cgi?id=34564>. ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND� INSERTED IN THE BUG DATABASE.
http://issues.apache.org/bugzilla/show_bug.cgi?id=34564 Summary: Unable to save excel file after generating it with HSSF Product: POI Version: 2.5 Platform: All OS/Version: Windows XP Status: NEW Severity: critical Priority: P2 Component: HSSF AssignedTo: [email protected] ReportedBy: [EMAIL PROTECTED] 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; } } -- Configure bugmail: http://issues.apache.org/bugzilla/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug, or are watching the assignee. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
