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 11:06:12 UTC
Writing header row giving problem for multi sheets workbook using
BigGridDemo
Hi,
I used Yegor's BigGridDemo model for writing huge data to excel workbooks.
And i feel thanks to Alex Ciarlillo for his implementation to work for
multiple sheets. Now i am successfully able to generate plain huge data to
excel multi sheet workbooks.
But my intension is to use an existing template of already defined styles,
headers, pivote tables and need to write data to those sheets. 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 portions
instead of once again freshly writing it which is static for any type of
workbook. 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 write data into 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 through code for existing template. 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 expected data in all sheets
but giving one more pop up with message "Repaired Records: Cell information
from /xl/worksheets/sheet1.xml part". My intension is not to show this pop
up when users try to open the generated excel.
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/Writing-header-row-giving-problem-for-multi-sheets-workbook-using-BigGridDemo-tp3359460p3359460.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