Thanks...I will look at the hyperlinks you sent me... "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>
