Re: Find String value from existing excel file

Andrew C. Oliver Sat, 26 Oct 2002 20:01:35 -0700

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/HSSFWorkbook.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>

Re: Find String value from existing excel file

Reply via email to