You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Purna Chandra <dp...@steelwedge.com> on 2011/01/27 08:55:55 UTC
Re: Question on XSSF memory workarounds (i.e. BigGridDemo)
Hi Alex,
Thanks for your help in generating multiple sheets using
BigGridDemo model. I successfully able to generate excel sheets with
multiple sheets of huge data.
I extended Yegor's code for writing data to a workbook of already
existing template. For that i am using the existing template's header and
footer parts instead of hard coding it. For that i implemented the below
methods.
//Method to use top portion of sheet.xml file what ever it is there for
existing template. This will use the //xml part of existing template from
top to first occurrence of </row> tag which means it will use the style //of
header rows of existing template.
public void beginSheet(File zipfile, String ref) throws IOException {
_out.write(""+getBeginSheet(zipfile, ref));
}
public String getBeginSheet(File zipfile, String ref) throws IOException{
StringBuffer sbStart = new StringBuffer();
ZipFile zip = new ZipFile(zipfile);
// @SuppressWarnings("unchecked")
Enumeration en = (Enumeration) zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = (ZipEntry)en.nextElement();
if(ref.equals(ze.getName())){
String key = ze.getName();
//sbStart = sbStart.append(sbRows);
File sheetFile = new File(key);
InputStream is1 = zip.getInputStream(ze);
BufferedReader br1 = new BufferedReader(new
InputStreamReader(is1));
boolean start = true;
boolean end = false;
String line="";
int index;
while(br1.readLine()!=null){
line = br1.readLine();
if(line.indexOf("</row>")!=-1 ||
line.indexOf("</sheetData>")!=-1){
if(line.indexOf("</row>")!=-1){
index = line.indexOf("</row>");
sbStart.append(line.substring(0, index));
sbStart.append("</row>");
start = false;
}
if(line.indexOf("</sheetData>")!=-1){
start=false;
end = true;
}
}else if(line.indexOf("<sheetData/>")!=-1){
index = line.indexOf("<sheetData/>");
sbStart.append(line.substring(0, index));
sbStart.append("<sheetData>");
start = false;
}else if(start){
sbStart.append(line);
}
}
br1.close();
is1.close();
StringBuffer sb = new StringBuffer();
sb.append(sbStart);
}
}
return sbStart.toString();
}
//Method to use bottom portion of sheet.xml file what ever it is there for
existing template. This will use //the xml part of existing template from
</sheetData> or <sheetData/> to till end of the file. So by this //the
style for existing template wont be modified.
public void endSheet(File zipfile, String ref) throws IOException {
_out.write(""+getEndSheet(zipfile, ref));
}
public String getEndSheet(File zipfile, String ref) throws IOException{
StringBuffer sbStart = new StringBuffer();
StringBuffer sbEnd = new StringBuffer();
ZipFile zip = new ZipFile(zipfile);
// @SuppressWarnings("unchecked")
Enumeration en = (Enumeration) zip.entries();
int i=1;
while (en.hasMoreElements()) {
ZipEntry ze = (ZipEntry)en.nextElement();
if(ref.equals(ze.getName())){
String key = ze.getName();
//sbStart = sbStart.append(sbRows);
File sheetFile = new File(key);
InputStream is1 = zip.getInputStream(ze);
BufferedReader br1 = new BufferedReader(new
InputStreamReader(is1));
boolean start = true;
boolean end = false;
String line="";
int index;
while(br1.readLine()!=null){
line = br1.readLine();
if(line.indexOf("</row>")!=-1 ||
line.indexOf("</sheetData>")!=-1){
if(line.indexOf("</sheetData>")!=-1){
index = line.indexOf("</sheetData>");
sbEnd.append(line.substring(index,line.length()));
end = true;
}
}else if(line.indexOf("<sheetData/>")!=-1){
line=line.replaceAll("<sheetData/>", "</sheetData>");
index = line.indexOf("</sheetData>");
sbEnd.append(line.substring(index,line.length()));
end = true;
}else{
if(end){
sbEnd.append(line);
}
}
}
br1.close();
is1.close();
StringBuffer sb = new StringBuffer();
sb.append(sbEnd);
return sbEnd.toString();
}
}
I am successfully able to generate data to existing templates using this way
without effecting the styles of the sheets.
But i am facing problem when i need to generate Headers of a sheet
dynamically for existing template for which styles are not previously
defined. The problem is while opening the excel workbook i am getting popup
message contains "Excel found unreadable content in 'BigGridDemo.xlsx'. Do
you want to recover the contents of this workbook? If you trust the source
of this workbook, click Yes'.
When i click on Yes the excel is opening with full expected data in all
sheets but giving one more pop up with message "Repaired Records: Cell
information from /xl/worksheets/sheet1.xml part".
This message only occurring when i tried to generate dynamic headers for
existing template. Yegor following the below format for writing column
header part of sheet1.xml
<c r="A1" t="inlineStr" s="37"><is><t>data</t></is></c>
Is this the correct way to represent the headers or we need to give those
reference of headers in sharedStrings.xml? I tried this way manually giving
references to the headers in sharedStrings.xml but i am not succeeded. Can
any one help me to sort this problem.
Thanks,
Purna.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Question-on-XSSF-memory-workarounds-i-e-BigGridDemo-tp2310217p3359318.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