You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by fachhoch <fa...@gmail.com> on 2011/01/20 05:51:52 UTC
new bee error when opening excel
I am new bee trying to play with poi to create excel sheet.
I am trying this code
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
public class Test {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
String tittle[]={"a1","a2","a3"};
List<String[]> data= new ArrayList<String[]>();
data.add(new String[]{"value11","value12","value13"});
data.add(new String[]{"value21","value22","value23"});
byte[] excel=getExcelBytes(tittle, data, "test");
FileOutputStream fileOutputStream= new
FileOutputStream("e:\\temp\\test.xls");
fileOutputStream.write(excel);
}
public static byte[] getExcelBytes(String title[], List datalist, String
sheetName){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow[] row = new HSSFRow[datalist.size() + 1];
HSSFCell[] valueCell = new HSSFCell[title.length];
row[0] = sheet.createRow(0);
HSSFCell[] headerCell = new HSSFCell[title.length];
for (int i = 0; i < title.length; i++) {
headerCell[i] = row[0].createCell(i);
headerCell[i].setCellValue(title[i]);
}
Iterator iterator = datalist.iterator();
int n_row = 1;
while (iterator.hasNext()) {
String data[] = (String[]) iterator.next();
row[n_row] = sheet.createRow(n_row);
for (int n_cel = 0; n_cel < data.length; n_cel++) {
valueCell[n_cel] = row[n_row].createCell(n_cel);
valueCell[n_cel].setCellValue(data[n_cel]);
}
n_row++;
}
//sheet.setGridsPrinted(false);
return wb.getBytes();
}
}
when I open the generated excel it complains
Microsoft Office Excel has encountered a problem and needs to close. We are
sorry for the inconvenience.
about unexpected error and it restarts it does it for 2 times and repairs
excel by itself and opens it , please tell me what am I doing wrong ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/new-bee-error-when-opening-excel-tp3349074p3349074.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: new bee error when opening excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Sorry, I have no real idea as whenever I had to store a document into a
database, I used one of two options; either storing a pointer to the file or
storing the file itself as a BLOB. It may well be that you need to stream
the workbook out as a temporary file and then read it back in again - the
FileInputStream allows you, for instance, to read the files contents as
bytes; using this you could assemble an in memory array and then store this
into the database. Alternatively, take a look at using
PipedInput/OutputStreams. Typically, these are used as linked pairs for
inter-process communication and I could imagime one thread constructing the
workbook and writing it to a PipedOutputStream whilst another thread reads
from the linked PipedInputStream writing the data to a byte array.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/new-bee-error-when-opening-excel-tp3349074p3350008.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: new bee error when opening excel
Posted by fachhoch <fa...@gmail.com>.
I tried to write using wb.write(new FIleOutPutStream("...")) it worked.My
case I want to save it to database as a bytearray not to the filesystem ,
so I tried wb.getBytes() , please suugest what option I have to save
the excel to database as a bytearray ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/new-bee-error-when-opening-excel-tp3349074p3349527.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: new bee error when opening excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Looking at your code, I suspect that you have simply misunderstood the way
POI works. When you call the write method on a workbook object - the
HSSFWorkbook instance - it needs to process the various data structures that
have been created - the sheets, cells, etc - in order to generate the file.
What you have done by creating a method that returns bytes is simply
prevented this process from occurring. The write method will see a stream of
bytes but cannot know what they represent.
If you are starting out with POI, then I would change your code and create a
method that performs the whole process from start to end, Pass the List into
it to create the cells and the name of the file but include in that method
the creation of the HSSFWorkbook, the addition of all of the sheets, rows,
and cells and the write method. It would look a little like this;
private void createWorkbook(List data, String filename) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet One");
HSSFRow row = null;
HSSFCell cell = null;
// Iterate through your data creating rows and cells and needed;
FileOutputStream fos = new FileOutputStream(new File(filename));
workbook.write(fos);
}
and that ought to do it - and of course I have not included any exception
handlng here as it is just a simple example.
Once you understand how POI works, then you can create methods to, for
example, set up all of you cell styles before you begin building the
workbook, create sheets from the data you supply, etc. If you need further
help there are lots of examples along with a quicku guide, how to and faq at
the Apache POI website.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/new-bee-error-when-opening-excel-tp3349074p3349226.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org