I have code to Write to xls file usng POI but I am getting input for which we may get lacs of rows,hence it may exceed the max size of the workbook. In that case I need to write the data to a new workbook and continue doing this everytime data exhausts the workbook size.My questions: 1) How do I come to know when a workbook is exhausted 2) how should I crate a new file 3)where should I keep my data while I track the no. of xls's.
Thanks!
If the input data is exceeding the max size of the workbook, you can use Big Grid Demo style for writing the data to the workbook. This will allow you to write unlimited data to the workbook.
/**
*
* @param zipfile the template file
* @param sheets the Map with
* key "name of the sheet entry to substitute
* (e.g. xl/worksheets/sheet1.xml, xl/worksheets/sheet2.xml etc)"
* and value "XML file with the sheet data"
* @param out the stream to write the result to
*/
private static void substitute(File zipfile, Map<String, File> sheets, OutputStream out) 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())){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
for (Map.Entry<String, File> entry : sheets.entrySet()) {
// System.out.println("Key -->"+entry.getKey());
zos.putNextEntry(new ZipEntry(entry.getKey()));
InputStream is = new FileInputStream(entry.getValue());
copyStream(is, zos);
is.close();
}
zos.close();
}
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
}
/**
* Writes spreadsheet data in a Writer.
* (YK: in future it may evolve in a full-featured API for streaming data in Excel)
*/
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
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("<sheetData>\n");
}
public void endSheet() throws IOException {
_out.write("</sheetData>");
_out.write("</worksheet>");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("<row r=\""+(rownum+1)+"\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("</row>\n");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<is><t>"+value+"</t></is>");
_out.write("</c>");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<v>"+value+"</v>");
_out.write("</c>");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
You just need to call substitute() method while writing the data to the workbook,
ServletOutputStream out = null;
out = response.getOutputStream();
substitute(new File("template.xlsm"), sheets, out);
out.flush();
out.close();
