Re: Find String value from existing excel file

Andrew C. Oliver Sat, 02 Nov 2002 13:19:56 -0800

Jeff Blackwell wrote:

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.

Its a new 2.0 feature.  Therefore its available in the development builds.

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.

Well its already been done. it just won't roll back into the 1.5.x series because its a new feature and required changes to POIFS. Now the second issue, is the online documentation is for the CVS HEAD.
The best way to get this into a "stable release" is to contribute to the formula and graphing effort.

-Andy

-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;

/**
* Title: 
* Description: 
* Copyright: Copyright (c) 2002
* Company: 
* @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>



--
To unsubscribe, e-mail:   <mailto:poi-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:poi-user-help@;jakarta.apache.org>

Re: Find String value from existing excel file

Reply via email to