;-) too late.... I forgot I'd just answered ;-)
Jeff Blackwell wrote:
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>
--
To unsubscribe, e-mail: <mailto:poi-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>
