Glen (or anyone else?) Did I misread something? I was using 1.5.1, and I understood that macro preservation was not in there. Is there a later stable version that _has_ macro preservation? Did I miss something? From the docs it sounds like macro-anything is off the table for a long time.
Assuming that macro preservation is not in the later versions, how nasty of an issue do you think it is to add? I'm willing to take a swing at it. That said, I'm new to POI, so it might take me some effort. I don't personally need macro preservation, I just need to be able to read a file with macros and write one without them that doesn't blow up. That's probably a strange case, I know, so I assume that preservation is the way to go. -Jeff -----Original Message----- From: Glen Stampoultzis [mailto:gstamp@;iprimus.com.au] Sent: Monday, October 28, 2002 10:58 PM To: POI Users List Subject: RE: Find String value from existing excel file Which version are you using? I might be wrong but I think macro preservation was not available in 1.5.x Regards, Glen At 08:09 AM 28/10/2002 -0800, you wrote: >Peter - > >I have exactly the same situation. Somewhere in the documentation (I forget >where...) it states that macros will read ok, but will screw up on write. >This could be a big thing for my use. Any of the POI developers know if >this is a tough thing to fix? I'd be glad to take a crack at fixing it, if >it wasn't a killer... By fix, I mean adjust the code so that macros would >not be written out, but would not corrupt the file. > >Thoughts? > >Jeff Blackwell >Tenacity Software, Inc. >[EMAIL PROTECTED] >www.tenacityinc.com >916.705.0161 > > >-----Original Message----- >From: news [mailto:news@;main.gmane.org]On Behalf Of Peter Boivin >Sent: Monday, October 28, 2002 8:00 AM >To: [EMAIL PROTECTED] >Subject: Re: Find String value from existing excel file > > >Hey ... The documents helped me out. However, I am coming across another >problem which I think I know the answer to. When I alter my excel >document, which has macros embedded in it, using POI, the document becomes >corrupt. I read on the website that POI does not yet work with macros. Is >this what you mean by "it does not work with macros"? Or am I experiencing >something different? >When I alter another spreadsheet that has no macros or formatting of any >type, the excel document remains intact. >"Andrew C. Oliver" <[EMAIL PROTECTED]> wrote in message >news:3DBB5625.5060301@;apache.org... > > see below: > > > > Peter Boivin wrote: > > > > >Here is the full code.... It includes alot of experimentation...so just > > >ignore the commented out code > > > > > >package Testing; > > > > > >/** > > > * <p>Title: </p> > > > * <p>Description: </p> > > > * <p>Copyright: Copyright (c) 2002</p> > > > * <p>Company: </p> > > > * @author Peter Boivin > > > * @version 1.0 > > > */ > > > > > > /* > > >Create Excel-formatted data > > > > > >Until recently, the most common way to create a Microsoft Excel file in a > > >Java application was to create a comma separated values (CSV) file in a > > >servlet or JSP and return it to the browser as MIME-type, text/csv. The > > >browser would then call Excel and the CSV would be displayed. > > > > > >There is now a project that provides Java developers with a real tool for > > >creating Excel files. It's the most mature part of a new Jakarta project > > >named Poor Obfuscation Implementation (POI). The Excel component of POI >is > > >named Horrible Spreadsheet Format (HSSF). > > > > > >While HSSF provides many different ways of interacting with the engine, >the > > >one we'll focus on is the easy high-level user API. > > > > > >Here's a simple example that creates a matrix of values in an Excel >sheet: > > >*/ > > > > > >import org.apache.poi.poifs.filesystem.*; > > >import java.io.*; > > >import java.nio.*; > > >import java.nio.channels.*; > > >import java.nio.charset.*; > > >import java.util.regex.*; > > > > > > > > >import com.borland.dbswing.*; > > >import com.borland.dx.dataset.*; > > >import com.borland.dx.sql.dataset.*; > > >import org.apache.poi.hssf.usermodel.*; > > > > > > > > > > > >// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar. > > >public class PoiTest { > > > private Database Album_Songs = new Database(); > > > private QueryDataSet queryDataSet1 = new QueryDataSet(); > > > private DBDisposeMonitor dBDisposeMonitor1 = new DBDisposeMonitor(); > > > private Column Album_ID = new Column(); > > > > > > static public void main(String[] args) throws Exception { > > > short s = 8; > > > File file = new File ("DECEMBER -2001-20561.xls"); > > >// FileInputStream fis = new FileInputStream("test.xls"); > > > FileInputStream fis = new FileInputStream(file); > > > HSSFWorkbook wb1 = new HSSFWorkbook(fis); > > > HSSFSheet sh1 = wb1.getSheetAt(5); > > > System.out.println("" + wb1.getSheetName(5)); > > > HSSFRow row = null; > > > HSSFCell cell = null; > > > row = sh1.getRow(9); > > > row.setRowNum(s); > > > cell = row.getCell(s); > > > HSSFCellStyle cellst = cell.getCellStyle(); > > >// System.out.println("" + cellst.getDataFormat()); > > > cellst.setFillBackgroundColor(s); > > > > > > > > > > > > /* > > > // Create a pattern to match comments > > > Pattern p = > > > Pattern.compile("RECEIVABLE/", Pattern.MULTILINE);// //.*$ > > > FileInputStream fis1 = new FileInputStream(); > > > FileChannel fc = fis1.getChannel(); > > > > > >// File fo = new File ("C://NumbersGameGrepped.txt"); > > >// fo.delete(); > > >// FileWriter fw = new FileWriter (fo); > > >// BufferedWriter bw = new BufferedWriter(fw); > > > > > > // Get a CharBuffer from the source file > > > ByteBuffer bb = > > > fc.map(FileChannel.MapMode.READ_ONLY, 0, (int)fc.size()); > > > Charset cs = Charset.forName("8859_1"); > > > CharsetDecoder cd = cs.newDecoder(); > > > CharBuffer cb = cd.decode(bb); > > > > > > // Run some matches > > > Matcher m = p.matcher(cb); > > > System.out.println("" + m.group()); > > > StringBuffer sbr = new StringBuffer (""); > > >*/ > > > > > > > > > > > > > > You can't use "Print Stream". It munges up the file. This is a binary > > file. you don't need character twiddling. use iether a plain > > FileOutputStream or OutputStream or something. > > > > > PrintStream outStream = new PrintStream(new > > >FileOutputStream(file,true)); > > > short z = 0; > > > System.out.println("" + cell.getStringCellValue()); > > > cell.setEncoding(z); > > > cell.setCellValue(52); > > > System.out.println("" + cell.getNumericCellValue()) > > > > > what is the purpose of this ? outstream.write(54)? > > use this: > > >http://jakarta.apache.org/poi/javadocs/org/apache/poi/hssf/usermodel/HSSFWo r >kbook.html#write(java.io.OutputStream) > > You REAAALY need to look at the examples here: > > http://jakarta.apache.org/poi/hssf/quick-guide.html > > > > >; > > > outStream.write(54); > > > > > >// HSSFSheet s = wb.createSheet(); > > >// HSSFSheet s1 = wb.createSheet(); > > >// wb.setSheetName(0, "Matrix3"); > > >// wb.setSheetName(1, "Matrix4"); > > >// for (int h = 0; h < 10; h++) { > > >// HSSFSheet sh = wb.createSheet("M" + h); > > >// wb.setSheetName( h, "Mo" + (h + 1) ); > > >// } > > >/* > > > for(short i=0; i<50; i++) { > > > HSSFRow row = s.createRow(i); > > > > > > for(short j=0; j<50; j++) { > > > HSSFCell cell = row.createCell(j); > > > cell.setCellValue(""+i+","+j); > > > } > > > } > > > > > > wb.setSheetName(1, "Matrix2"); > > > for(short i=0; i<50; i++) { > > > HSSFRow row = s.createRow(i); > > > > > > for(short j=0; j<50; j++) { > > > HSSFCell cell = row.createCell(j); > > > cell.setCellValue(""+i+","+j); > > > } > > > } > > > */ > > > outStream.flush(); > > > outStream.close(); > > > } > > > public void setWkBkSht (HSSFWorkbook wb, HSSFSheet hssfSheet, int p0, > > >String p1) { > > > HSSFSheet s = hssfSheet; > > > HSSFWorkbook hssfwb = wb; > > > s = hssfwb.createSheet(); > > > hssfwb.setSheetName(p0,p1); > > > } > > > > > > public PoiTest() { > > > try { > > > jbInit(); > > > } > > > catch(Exception e) { > > > e.printStackTrace(); > > > } > > > } > > > private void jbInit() throws Exception { > > > Album_ID.setColumnName("Album_ID"); > > > Album_ID.setDataType(com.borland.dx.dataset.Variant.INT); > > > Album_ID.setPreferredOrdinal(3); > > > Album_ID.setTableName("albums"); > > > Album_ID.setServerColumnName("Album_ID"); > > > Album_ID.setSqlType(4); > > > queryDataSet1.setQuery(new > > >com.borland.dx.sql.dataset.QueryDescriptor(Album_Songs, "SELECT > > >albums.Artist_Composer,albums.ID,albums.Album_Title,albums.Album_ID " + > > > "FROM albums", null, true, Load.ALL)); > > > Album_Songs.setConnection(new > > >com.borland.dx.sql.dataset.ConnectionDescriptor("jdbc:odbc:Music_Songs", > > >"administrator", "marcia", false, "org.gjt.mm.mysql.Driver")); > > > queryDataSet1.setColumns(new Column[] {Album_ID}); > > > } > > >} > > > > > > > > >"Jeff Blackwell" <[EMAIL PROTECTED]> wrote in message > > >news:JBENKKCBIOPDLCNMFHNKEEHCCBAA.jblackwell@;tenacityinc.com... > > > > > > > > >>Could you include a code snippet of what you're trying? And what's not > > >>working. If you're setting the cell value as described below, and >writing > > >>the workbook back out, then only problem remaining is if the workbook > > >>doesn't open when you open it in Excel. Or are you getting an error on >the > > >>call to setCellValue? > > >> > > >>-Jeff > > >> > > >>-----Original Message----- > > >>From: news [mailto:news@;main.gmane.org]On Behalf Of Peter Boivin > > >>Sent: Saturday, October 26, 2002 2:49 PM > > >>To: [EMAIL PROTECTED] > > >>Subject: Re: Find String value from existing excel file > > >> > > >> > > >>The problem is that the excel spreadsheet is not saving the insert from > > >> > > >> > > >the > > > > > > > > >>database. As matter of fact, I am not even trying to put the value from > > >> > > >> > > >the > > > > > > > > >>database in yet, I am simply doing "cell.setCellValue(52);". > > >> > > >>"Andrew C. Oliver" <[EMAIL PROTECTED]> wrote in message > > >>news:ape0g6$l04$1@;main.gmane.org... > > >> > > >> > > >>>okay so what exactly is the problem? > > >>> > > >>>Peter Boivin wrote: > > >>> > > >>> > > >>> > > >>>>That is more or less what I am trying to do.... What I will do after I > > >>>>find > > >>>>the header is go to the cell below it and fill in data that came from > > >>>> > > >>>> > > >a > > > > > > > > >>>>database. > > >>>>"Jeff Blackwell" wrote in message > > >>>>news:JBENKKCBIOPDLCNMFHNKCEGPCBAA.jblackwell@;tenacityinc.com... > > >>>> > > >>>> > > >>>> > > >>>>>Peter - > > >>>>> > > >>>>>I'm a newbie here as well, but I may have some thoughts that can > > >>>>> > > >>>>> > > >help. > > > > > > > > >>It > > >> > > >> > > >>>>>sounds like you want to do the following: > > >>>>> > > >>>>>1) Search through an XLS file and find the a cell with a specific > > >>>>> > > >>>>> > > >value > > > > > > > > >>>>>(e.g. "Header") > > >>>>>2) Change that value to some other value (e.g. "Foobar") > > >>>>>3) Save the XLS file back to disk. > > >>>>> > > >>>>>Is that what you're trying to do? > > >>>>> > > >>>>>-Jeff > > >>>>> > > >>>>>Jeff Blackwell > > >>>>>Tenacity Software, Inc. > > >>>>>[EMAIL PROTECTED] > > >>>>>www.tenacityinc.com > > >>>>>916.705.0161 > > >>>>> > > >>>>> > > >>>>>-----Original Message----- > > >>>>>From: Andrew C. Oliver [mailto:andy@;superlinksoftware.com] > > >>>>>Sent: Friday, October 25, 2002 5:51 AM > > >>>>>To: POI Users List > > >>>>>Subject: Re: Find String value from existing excel file > > >>>>> > > >>>>> > > >>>>>Whoa.. You're using a printwriter on a binary file? You realize > > >>>>> > > >>>>> > > >this > > > > > > > > >>>>>tries to do text conversion right? That doesn't work with binary > > >>>>> > > >>>>> > > >>files. > > >> > > >> > > >>>>>If thats NOT what you mean, please read your message below and ask > > >>>>>yourself: "if I read this message would I know what the problem > > >>>>> > > >>>>> > > >was?" > > > > > > > > >>>>>Information missing: what is wrong, at what point is it failing, > > >>>>> > > >>>>> > > >what > > > > > > > > >>>>>do you mean by "commit the changes" and what error your getting, what > > >>>>>are you doing, is this in a jsp page, servlet, what? > > >>>>> > > >>>>>-Andy > > >>>>> > > >>>>>Peter Boivin wrote: > > >>>>> > > >>>>> > > >>>>> > > >>>>> > > >>>>>>Andy, > > >>>>>>With this preexisting excel file, am I able to update a cell then > > >>>>>> > > >>>>>> > > >>commit > > >> > > >> > > >>>>>the > > >>>>> > > >>>>> > > >>>>> > > >>>>>>changes? I have tried using PrintStream (using flush and close)but > > >>>>>> > > >>>>>> > > >>with > > >> > > >> > > >>>>no > > >>>> > > >>>> > > >>>> > > >>>>>>success. If it is possible, could you please give this newbie a > > >>>>>> > > >>>>>> > > >clue > > > > > > > > >>as > > >> > > >> > > >>>>to > > >>>> > > >>>> > > >>>> > > >>>>>>how to go about it? > > >>>>>> > > >>>>>>-Peter > > >>>>>>"Andrew C. Oliver" wrote in message > > >>>>>>news:3DB86D02.4030206@;superlinksoftware.com... > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>>>Not really because the logic for doing so isn't different than the > > >>>>>>> > > >>>>>>> > > >>>>logic > > >>>> > > >>>> > > >>>>>>>you'd have to do. (So no efficiency gain would be made) > > >>>>>>> > > >>>>>>>The method we'd have to do is to go through every cell in the > > >>>>>>> > > >>>>>>> > > >>workbook > > >> > > >> > > >>>>>>>finding the ones with that value. This isn't a very efficient > > >>>>>>> > > >>>>>>> > > >thing > > > > > > > > >>to > > >> > > >> > > >>>>>>>do. Doing it with the high level API isn't really slower for this. > > >>>>>>> > > >>>>>>> > > >>So > > >> > > >> > > >>>>>>>the short answer: no, iterate through them until you find it. > > >>>>>>> > > >>>>>>>-Andy > > >>>>>>> > > >>>>>>> > > >>>>>>>Peter Boivin wrote: > > >>>>>>> > > >>>>>>> > > >>>>>>> > > >>>>>>> > > >>>>>>> > > >>>>>>> > > >>>>>>>>I am working with a pre-existing excel file. I need to find a > > >>>>>>>> > > >>>>>>>> > > >>header > > >> > > >> > > >>>>for > > >>>> > > >>>> > > >>>> > > >>>>>>a > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>>>>column (ex. "Receiving"). The problem is that the header may not > > >>>>>>>> > > >>>>>>>> > > >>>>be on > > >>>> > > >>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>the > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>>>>first row of the excel file. I won't know the row number or the > > >>>>>>>> > > >>>>>>>> > > >>>>column > > >>>> > > >>>> > > >>>>>>>>number. Is there a way to search for a string value using poi > > >>>>>>>> > > >>>>>>>> > > >that > > > > > > > > >>>>will > > >>>> > > >>>> > > >>>> > > >>>>>>>>return to me the row and column of the string value? > > >>>>>>>> > > >>>>>>>>Kindly, > > >>>>>>>>Peter > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>>-- > > >>>>>>>>To unsubscribe, e-mail: > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>>>>For additional commands, e-mail: > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>> > > >>>>>> > > >>>>>>-- > > >>>>>>To unsubscribe, e-mail: > > >>>>>> > > >>>>>> > > >>>> > > >>>> > > >>>> > > >>>>>>For additional commands, e-mail: > > >>>>>> > > >>>>>> > > >>>> > > >>>> > > >>>> > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>-- > > >>>>>To unsubscribe, e-mail: > > >>>>>For additional commands, e-mail: > > >>>>> > > >>>>> > > >> > > >> > > >> > > >>-- > > >>To unsubscribe, e-mail: ><mailto:poi-user-unsubscribe@;jakarta.apache.org> > > >>For additional commands, e-mail: ><mailto:poi-user-help@;jakarta.apache.org> > > >> > > >> > > > > > > > > > > > > > > > > > >-- > > >To unsubscribe, e-mail: ><mailto:poi-user-unsubscribe@;jakarta.apache.org> > > >For additional commands, e-mail: ><mailto:poi-user-help@;jakarta.apache.org> > > > > > > > > > > > > > > > > > >-- >To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> >For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org> > > >-- >To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> >For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org> -- To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org> -- To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>
