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 <[email protected]> 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: [email protected]
> For additional commands, e-mail: [email protected]
>
>
