Ignore my prior post... I was catching up and didn't see Andy's reply. Thanks!
-----Original Message----- From: Andrew C. Oliver [mailto:acoliver@;apache.org] Sent: Wednesday, October 30, 2002 6:39 AM To: POI Users List Subject: Re: Find String value from existing excel file No its 1.8-dev. Macro preservation was regarded as a feature and requied POIFS changes so I regarded that too risky to put in the production version. In retrospect the POIFS changes ended up being "safe" (1 calculated byte in the file changed!) and the HSSF changes worked like a charm, but the default behavior was changed so its probably still the right decision. So either you have to use a dev release or wait for 2.0. (Which will be out when glen gets off his butt and finishes graphing... ;-p ) -Andy Glen Stampoultzis wrote: > 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/HSSFWor >> 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> -- To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org> For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>
