poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rohilla, Vishal" <vishal.rohi...@compuware.com>
Subject RE: How to use POI (Newbie)
Date Fri, 26 Jan 2007 14:31:12 GMT
Thanks Mike,
 
It helped. When I gave the absolute path it created the file at the appropriate location.
I also noticed w/o absolute location it was creating the file in my weblogic domains directory.
 
Now it raises the question, in production it is not a good idea to create the file at some
location at user's box. Should one use ServletOutputStream over FileOutputStream in this case?
 
Thanks again for all the help.
 
Vishal

________________________________

From: Mike.Haller@innovations.de [mailto:Mike.Haller@innovations.de]
Sent: Thu 1/25/2007 10:21 AM
To: poi-user@jakarta.apache.org
Subject: RE: How to use POI (Newbie)



The code worked for me. Since you're using a FileOutputStream in a
Servlet and not using an absolute target path but just a relative
filename, I assume you just looked in the wrong place for the file. Or
you code was not even called. Out of topic.

Attached, my slightly reformatted code which worked.

--
Mike Haller
Innovations

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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;

public class ReportAction {

        public static void main(String[] args) throws Exception {
                short rownum;
                FileOutputStream out = new
FileOutputStream("c:\\workbook.xls");
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet s = wb.createSheet();
                HSSFRow r = null;
                HSSFCell c = null;
                HSSFCellStyle cs = wb.createCellStyle();
                HSSFCellStyle cs2 = wb.createCellStyle();
                HSSFCellStyle cs3 = wb.createCellStyle();
                HSSFDataFormat df = wb.createDataFormat();
                HSSFFont f = wb.createFont();
                HSSFFont f2 = wb.createFont();
                f.setFontHeightInPoints((short) 12);
                f.setColor((short) 0xc);
                f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                f2.setFontHeightInPoints((short) 10);
                f2.setColor((short) HSSFFont.COLOR_RED); // make it bold
                f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                f2.setStrikeout(true);
                cs.setFont(f);
                cs.setDataFormat(df.getFormat("#,##0.0"));
                cs2.setBorderBottom(cs2.BORDER_THIN);
                cs2.setFillPattern((short)
HSSFCellStyle.SOLID_FOREGROUND); // set the
       
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
                cs2.setFont(f2);
                wb.setSheetName(0,
"\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
                                +
"\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430",
                                HSSFWorkbook.ENCODING_UTF_16);
                for (rownum = (short) 0; rownum < 30; rownum++) {
                        r = s.createRow(rownum);
                        if ((rownum % 2) == 0) {
                                r.setHeight((short) 0x249);
                        }
                        for (short cellnum = (short) 0; cellnum < 10;
cellnum += 2) {
                                c = r.createCell(cellnum);
                                c
                                                .setCellValue(rownum
                                                                * 10000
                                                                +
cellnum
                                                                +
(((double) rownum / 1000) + ((double) cellnum / 10000)));
                                String cellValue;
                                c = r.createCell((short) (cellnum + 1));
                                if ((rownum % 2) == 0) {
                                        c.setCellStyle(cs);
       
c.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);
                                        c.setCellValue("Test");
                                } else {
                                        c.setCellStyle(cs2);
       
c.setEncoding(HSSFCell.ENCODING_UTF_16);
       
c.setCellValue("\u0422\u0435\u0441\u0442");
                                }
                                s.setColumnWidth((short) (cellnum + 1),
                                                (short) ((50 * 8) /
((double) 1 / 20)));
                        }
                }
                rownum++;
                rownum++;
                r = s.createRow(rownum);
                cs3.setBorderBottom(cs3.BORDER_THICK);
                for (short cellnum = (short) 0; cellnum < 50; cellnum++)
{
                        c = r.createCell(cellnum);
                        c.setCellStyle(cs3);
                }
                s = wb.createSheet();
                s.setSelected(true);
                wb.setSheetName(1, "DeletedSheet");
                try {
                        wb.write(out);
                } finally {
                        out.close();
                }
        }
}
-----Original Message-----
From: Rohilla, Vishal [mailto:vishal.rohilla@compuware.com]
Sent: Thursday, January 25, 2007 4:08 PM
To: POI Users List
Subject: RE: How to use POI (Newbie)

Yes I did close the fileoutputstream in the finally block. For the
reference here is the code:

public class ReportAction extends Action implements GlobalValues {

 public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request,
   HttpServletResponse response)throws Exception  {
  short rownum;
//   create a new file
  FileOutputStream out = new FileOutputStream("workbook.xls");
//   create a new workbook
  HSSFWorkbook wb = new HSSFWorkbook();
//   create a new sheet
  HSSFSheet s = wb.createSheet();
//   declare a row object reference
  HSSFRow r = null;
//   declare a cell object reference
  HSSFCell c = null;
//   create 3 cell styles
  HSSFCellStyle cs = wb.createCellStyle();
  HSSFCellStyle cs2 = wb.createCellStyle();
  HSSFCellStyle cs3 = wb.createCellStyle();
  HSSFDataFormat df = wb.createDataFormat();
//   create 2 fonts objects
  HSSFFont f = wb.createFont();
  HSSFFont f2 = wb.createFont();
//  set font 1 to 12 point type
  f.setFontHeightInPoints((short) 12);
//  make it blue
  f.setColor( (short)0xc );
//   make it bold
//  arial is the default font
  f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//  set font 2 to 10 point type
  f2.setFontHeightInPoints((short) 10);
//  make it red
  f2.setColor( (short)HSSFFont.COLOR_RED ); //  make it bold
  f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  f2.setStrikeout( true );
//  set cell stlye
  cs.setFont(f);
//  set the cell format
  cs.setDataFormat(df.getFormat("#,##0.0"));
//  set a thin border
  cs2.setBorderBottom(cs2.BORDER_THIN);
//  fill w fg fill color
  cs2.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND); //  set
the cell format to text see HSSFDataFormat for a full list
  cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
//   set the font
  cs2.setFont(f2);
//   set the sheet name in Unicode
  wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
+

"\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430",
                  HSSFWorkbook.ENCODING_UTF_16 );
//   in case of compressed Unicode
//   wb.setSheetName(0, "HSSF Test",
HSSFWorkbook.ENCODING_COMPRESSED_UNICODE );
//   create a sheet with 30 rows (0-29)
  for (rownum = (short) 0; rownum < 30; rownum++)
  {
      // create a row
      r = s.createRow(rownum);
      // on every other row
      if ((rownum % 2) == 0)
      {
          // make the row height bigger  (in twips - 1/20 of a point)
          r.setHeight((short) 0x249);
      }
      //r.setRowNum(( short ) rownum);
      // create 10 cells (0-9) (the += 2 becomes apparent later
      for (short cellnum = (short) 0; cellnum < 10; cellnum += 2)
      {
          // create a numeric cell
          c = r.createCell(cellnum);
          // do some goofy math to demonstrate decimals
          c.setCellValue(rownum * 10000 + cellnum
                  + (((double) rownum / 1000)
                  + ((double) cellnum / 10000)));
          String cellValue;
          // create a string cell (see why += 2 in the
          c = r.createCell((short) (cellnum + 1));
         
          // on every other row
          if ((rownum % 2) == 0)
          {
              // set this cell to the first cell style we defined
              c.setCellStyle(cs);
              // set the cell's string value to "Test"
              c.setEncoding( HSSFCell.ENCODING_COMPRESSED_UNICODE );
              c.setCellValue( "Test" );
          }
          else
          {
              c.setCellStyle(cs2);
              // set the cell's string value to
"\u0422\u0435\u0441\u0442"
              c.setEncoding( HSSFCell.ENCODING_UTF_16 );
              c.setCellValue( "\u0422\u0435\u0441\u0442" );
          }

          // make this column a bit wider
          s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) /
((double) 1 / 20)));
      }
  }
//  draw a thick black border on the row at the bottom using BLANKS
//   advance 2 rows
  rownum++;
  rownum++;
  r = s.createRow(rownum);
//   define the third style to be the default
//   except with a thick black border at the bottom
  cs3.setBorderBottom(cs3.BORDER_THICK);
//  create 50 cells
  for (short cellnum = (short) 0; cellnum < 50; cellnum++)
  {
      //create a blank type cell (no value)
      c = r.createCell(cellnum);
      // set it to the thick black border style
      c.setCellStyle(cs3);
  }
//  end draw thick black border

//   demonstrate adding/naming and deleting a sheet
//   create a sheet, set its title then delete it
  s = wb.createSheet();
  s.setSelected(true);
  wb.setSheetName(1, "DeletedSheet");
//  wb.removeSheetAt(1);
//  end deleted sheet
//   write the workbook to the output stream
//   close our file (don't blow out our file handles
//  wb.write(out);
//  out.close();
//  ServletOutputStream out = response.getOutputStream();
  try
  {
//   response.setContentType("application/vnd.ms-excel");
//   response.setHeader("Content-disposition","attachment;filename" +
"workBook");
//   out.write(wb.getBytes());
   wb.write(out);
  }
  catch(IOException io)
  {
   ActionErrors errors = new ActionErrors();
   ActionError error = new ActionError("error.fileretrieve.ioexception",
io.getMessage());
   errors.add( ActionErrors.GLOBAL_ERROR, error);
   saveErrors( request, errors );
//   EWODatabaseLogger.getInstance().log(session, "File Retrieve",
io.getMessage());
//   eWOLogger.write(DEBUG,io.getMessage(),io);
   return mapping.findForward("error");      
  }
  finally
  {
   out.close();
  } 
           
  return mapping.findForward("success");  } }


________________________________

From: Mike.Haller@innovations.de [mailto:Mike.Haller@innovations.de]
Sent: Thu 1/25/2007 9:27 AM
To: poi-user@jakarta.apache.org
Subject: RE: How to use POI (Newbie)



1) Try any other mirror and look in the "dev" subfolder instead of the
"release" subfolder.
2) Did you close the FileOutputStream as described in the text above the
code sample?


--
Mike Haller
Innovations

-----Original Message-----
From: Rohilla, Vishal [mailto:vishal.rohilla@compuware.com]
Sent: Thursday, January 25, 2007 3:24 PM
To: POI Users List
Subject: RE: How to use POI (Newbie)

When I go to download the jars at Jakarta POI site at the following URL:
-

http://www.apache.org/dyn/closer.cgi/jakarta/poi/

I am not able to find poi-bin-3.0-alpha3-20061212 version. The version I
get is is

poi-bin-2.5.1-final-20040804.zip

In the sample code I tried to run the code to export the data to the
spreadsheet. The code is at the the following URL: -
http://jakarta.apache.org/poi/hssf/how-to.html in the heading  'User
API'  sub heading - 'Writing a new one'

It didn't through any error but also didn't create or open the new
spreadsheet.

Could you please throw some light about how should one proceed to use
HSSF to export data to excel spreadsheet?

Vishal







________________________________

From: Tahir Akhtar [mailto:tahir@spectrum-tech.com]
Sent: Thu 1/25/2007 2:17 AM
To: 'POI Users List'
Subject: RE: How to use POI (Newbie)



Hi Rohilla

1. Its advisable to use the latest release poi-bin-3.0-alpha3-20061212.
2. There is nothing special about poi jars. Just use it as you use any
java library i-e include it in your classpath(or out it in your
WEB-INF/lib) and reference the classes via import statement or fully
qualified class names in your code.

What error are you getting when trying to run the sample code?

Wassalam
Tahir

-----Original Message-----
From: Rohilla, Vishal [mailto:vishal.rohilla@compuware.com]
Sent: Thursday, January 25, 2007 2:31 AM
To: poi-user@jakarta.apache.org
Subject: How to use POI (Newbie)

I downloaded the POI code from the mirror: -

http://mirror.candidhosting.com/pub/apache/jakarta/poi/release/

In the downloaded code I found the following jars: -

1) poi-2.5.1-final-20040804.jar
2) poi-contrib-2.5.1-final-20040804.jar
3) poi-scratchpad-2.5.1-final-20040804.jar

Does anybody have any ideas about how to plugin poi jars and to start
using it for sample examples at least.

I have gone throgh the documentation couldn't find instructions abt it.

Your help will be highly appreciated.

P.S - I added poi-2.5.1-final-20040804.jar in my WAR file and run one of
the sample code  in "How To" Section to export the data to spreadsheet.
But no luck.

Thanks

The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.



---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/



The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/



The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/



The contents of this e-mail are intended for the named addressee only. It contains information
that may be confidential. Unless you are the named addressee or an authorized designee, you
may not copy or use it, or disclose it to anyone else. If you received it in error please
notify us immediately and then destroy it. 

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message