You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Baji Shaik <sh...@gmail.com> on 2011/04/11 08:20:25 UTC
How To Set Freezing for BigGridDemo ?
Hi ,
I am using BigGridDemo.java for exporting data to xlsx .
I need to set freezing to the exported xlsx file .
i have tried something like :
public void addSheet(String name, XSSFWorkbook book, Map<String, File>
sheets) throws IOException {
XSSFSheet sheet = book.createSheet(name);
sheet.createFreezePane(3, 0,0,20);
String ref =
sheet.getPackagePart().getPartName().getName().substring(1);
File tmp = File.createTempFile("sheet" + (sheets.size() + 1),
".xml");
sheets.put(ref, tmp);
}
But i am unable to get it ..
Could you please tell me how apply freezing .
Thanks && Regards ,
Baji Shaik .
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-To-Set-Freezing-for-BigGridDemo-tp4295267p4295267.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: How To Set Freezing for BigGridDemo ?
Posted by 行健 <du...@gmail.com>.
Hey Baji,
I'm not sure how you gonna realize it specifically, but here is what I did:
I rewrite the beginSheet() method in "*BigGridDemo*.java" within "*
SpreadsheetWriter*" class to incorporate bunch of customized sheet features
required by my application at work:
*public* *void* beginSheet() *throws* IOException {
_out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<worksheet xmlns=\"
http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("<sheetViews><sheetView tabSelected=\"1\" showGridLines=\"0\"
workbookViewId=\"0\"><pane ySplit=\"8\" topLeftCell=\"A9\"
activePane=\"bottomLeft\" state=\"frozen\"/><selection pane=\"bottomLeft\"
activeCell=\"A9\" sqref=\"A9\"/></sheetView></sheetViews><sheetFormatPr
defaultRowHeight=\"15\"/><cols><col min=\"1\" max=\"36\" width=\"11.140625\"
customWidth=\"1\"/></cols>");
_out.write("<sheetData>\n");
}
as you can see, at the beginning of my sheet, I set things like: don't show
GridLines (showGridLines = 0), I freeze pane above row 9, (create a freeze
pane in Excel and check the xml file, then hardcoded what in that was how I
figured out those funky numbers)
Also I setup some column width, row Height....
For the same token, I achieved autofilter by two steps,
*First*, rewrite the *endSheet*() method in the same *SpreadsheetWriter *
class.
just for an example, input *autoFilterRange* could be: "A8:AJ4068"
*public* *void* endSheet(String autoFilterRange) *throws* IOException {
_out.write("</sheetData>");
_out.write("<autoFilter ref=\"" + autoFilterRange + "\"/>");
_out.write("</worksheet>");
}
And *Second*, autoFilter is very tricky, you need to also register the
autofilter at the xl/workbook.xml file also:
So I rewrote the "substitute" method:
*public* *static* *void* substitute(File zipfile, Map<String, File> sheets,
OutputStream out, String autoFilterXMLStr) *throws* IOException {
ZipFile zip =
*new* ZipFile(zipfile);
ZipOutputStream zos =
*new* ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
*while* (en.hasMoreElements()) {
ZipEntry ze = en.
*nextElement*();
*if*(!sheets.containsKey(ze.getName())){
*if*("xl/workbook.xml".equals(ze.getName())){
zos.putNextEntry(
*new* ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
BufferedReader br =
*new* BufferedReader(*new* InputStreamReader(is));
StringBuffer sb =
*new* StringBuffer();
String line =
*null*;
*while* ((line = br.readLine()) != *null*) {
sb.append(line +
"\n");}
br.close();
String wbXML = sb.toString();
wbXML = wbXML.replace(
"</sheets>", autoFilterXMLStr);
*byte*[] theByteArray = wbXML.getBytes();
zos.write(theByteArray);
is.close();
}
*else*{
zos.putNextEntry(
*new* ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
*copyStream*(is, zos);
is.close();
}
}
}
*for* (Map.Entry<String, File> entry : sheets.entrySet()) {
*log*.info(String.*format*("%-30s %s", "Start adding: ", entry.getKey()));
zos.putNextEntry(
*new* ZipEntry(entry.getKey()));
InputStream is =
*new* FileInputStream(entry.getValue());
*copyStream*(is, zos);
is.close();
}
zos.close();
}
*input autoFilterXMLStr , for example, can be:*
<definedNames><definedName name="_xlnm._FilterDatabase" localSheetId="4"
hidden="1">Sheet4_Name!$A$8:$AJ$108</definedName><definedNames>
if you miss this autofilter will crash the excel application, that's
actually a bug I submitted for POI 3.7, it's been fixed in POI 3.8 beta1,
you can find more details here:https://issues.apache.org/bugzilla
/show_bug.cgi?id=50314
Let me know
-Jack Duan
Key words: POI Freeze Panes freezing autofilter setAutoFilter
bigGridDemo.java bigGridDemo Excel spreadsheet
On Mon, Apr 11, 2011 at 2:20 AM, Baji Shaik <sh...@gmail.com> wrote:
>
> Hi ,
>
> I am using BigGridDemo.java for exporting data to xlsx .
> I need to set freezing to the exported xlsx file .
>
> i have tried something like :
>
> public void addSheet(String name, XSSFWorkbook book, Map<String,
> File>
> sheets) throws IOException {
> XSSFSheet sheet = book.createSheet(name);
> sheet.createFreezePane(3, 0,0,20);
> String ref =
> sheet.getPackagePart().getPartName().getName().substring(1);
> File tmp = File.createTempFile("sheet" + (sheets.size() +
> 1),
> ".xml");
> sheets.put(ref, tmp);
> }
>
>
> But i am unable to get it ..
> Could you please tell me how apply freezing .
>
> Thanks && Regards ,
> Baji Shaik .
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/How-To-Set-Freezing-for-BigGridDemo-tp4295267p4295267.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: How To Set Freezing for BigGridDemo ?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Thanks for that Jack. I knew someone had managed to get this to work but just
could not remember who it was.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-To-Set-Freezing-for-BigGridDemo-tp4295267p4296075.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: How To Set Freezing for BigGridDemo ?
Posted by 行健 <du...@gmail.com>.
I've done that, I can show you, I promised to send out the code but didn't
get the chance, I will do it asap.
Basically you have to write into .xml file like Mark said. I get the syntax.
On Mon, Apr 11, 2011 at 2:32 AM, Mark Beardsley <ma...@tiscali.co.uk>wrote:
> I was certain someone had done exactly this recently so my first step would
> be to search back toruhg the messages on the ists for the past few weeks
> and
> see if you can track them down. If you do, post a message onto that thread
> in the hope they are watching/listening to it and will respond. If you
> cannot find that poster then the best way to do what you are after is to
> modify the BigGridDemo program itself so that you create and then export
> the
> correct xml syntax to the file.
>
> 1. Using Excel, create a simple workbook that has a freeze pane set. Save
> that file to disc.
> 2. Rename the file you created in step one above, give it a .zip extension.
> 3. Unzip the renamed file into a folder on your machine.
> 4. Look through the xml markup in the various files to see what Excel has
> created to set the freeze pane.
> 5. Modify the BigGridDemo program so that it creates the same xml markup
> and
> exports it into the appropriate files.
>
> Once you have made this change, consider posting the information onto the
> list so that others can benefit from what you have done.
>
> Yours
>
> Mark B
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/How-To-Set-Freezing-for-BigGridDemo-tp4295267p4295276.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: How To Set Freezing for BigGridDemo ?
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I was certain someone had done exactly this recently so my first step would
be to search back toruhg the messages on the ists for the past few weeks and
see if you can track them down. If you do, post a message onto that thread
in the hope they are watching/listening to it and will respond. If you
cannot find that poster then the best way to do what you are after is to
modify the BigGridDemo program itself so that you create and then export the
correct xml syntax to the file.
1. Using Excel, create a simple workbook that has a freeze pane set. Save
that file to disc.
2. Rename the file you created in step one above, give it a .zip extension.
3. Unzip the renamed file into a folder on your machine.
4. Look through the xml markup in the various files to see what Excel has
created to set the freeze pane.
5. Modify the BigGridDemo program so that it creates the same xml markup and
exports it into the appropriate files.
Once you have made this change, consider posting the information onto the
list so that others can benefit from what you have done.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-To-Set-Freezing-for-BigGridDemo-tp4295267p4295276.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