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 ("");
*/
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());
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>
