Sorry, but it is still hard to see where exactly your problem is
located. If I run the code I get some files with some content, but no
error message, so what content do you not expect?
Thanks... Dominik.
On Fri, Aug 1, 2014 at 7:44 AM, Nalini Nizamkar
<nalini.nizam...@neobytes.com> wrote:
> Below is the code...
>
>
>
>
> import java.io.*;
> import java.util.*;
> import java.util.zip.ZipEntry;
> import java.util.zip.ZipFile;
> import java.util.zip.ZipOutputStream;
>
> import org.apache.poi.ss.usermodel.BorderStyle;
> import org.apache.poi.ss.usermodel.DateUtil;
> import org.apache.poi.ss.usermodel.IndexedColors;
> import org.apache.poi.ss.util.CellReference;
> import org.apache.poi.xssf.usermodel.*;
>
>
>
> public class BigGridDemoMultiSheet {
> public static void main(String[] args) throws Exception {
>
> // Step 1. Create a template file. Setup sheets and workbook-level
> objects such as
> // cell styles, number formats, etc.
>
> XSSFWorkbook wb = new XSSFWorkbook();
> Map<String, File> sheets = new HashMap<String, File>();
>
> addSheet("Summary Sheet", wb, sheets);
> addSheet("blah sheet", wb, sheets);
> addSheet("blah blah Sheet", wb, sheets);
> addSheet("blah blah Sheet2", wb, sheets);
>
> Map<String, XSSFCellStyle> styles = createStyles(wb);
>
> //save the template
> FileOutputStream os = new FileOutputStream("E:/test/template.xlsx");
> wb.write(os);
> os.close();
>
> // generate data for each sheet
> for (SortedMap.Entry<String, File> entry : sheets.entrySet()) {
> Writer fw = new FileWriter(entry.getValue());
> if(entry.getKey().contains("1")){
> generateSummary(fw, styles);
> }else{
> generateDetails(fw, styles);
> }
>
> fw.close();
> }
>
> //Step 3. Substitute the template entry with the generated data
> FileOutputStream out = new
> FileOutputStream("E:/test/MultiSheetBigGrid.xlsx");
> substitute(new File("E:/test/template.xlsx"), sheets, out);
> out.close();
> }
>
> private static void addSheet(String name, XSSFWorkbook book, Map<String,
> File> sheets) throws IOException {
> XSSFSheet sheet = book.createSheet(name);
> String ref =
> sheet.getPackagePart().getPartName().getName().substring(1);
> File tmp = File.createTempFile("sheet" + (sheets.size() + 1), ".xml");
> sheets.put(ref, tmp);
> }
>
> /**
> * Create a library of cell styles.
> */
> private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
> Map<String, XSSFCellStyle> styles = new HashMap<String,
> XSSFCellStyle>();
>
> XSSFCellStyle headerStyle = wb.createCellStyle();
> XSSFFont headerFont = wb.createFont();
> headerFont.setBold(true);
> headerStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
> headerStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
> headerStyle.setFont(headerFont);
> BorderStyle border = BorderStyle.THIN;
> headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
> headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
> headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
> headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
> headerStyle.setBorderBottom(border);
> headerStyle.setBorderTop(border);
> headerStyle.setBorderRight(border);
> headerStyle.setBorderLeft(border);
> styles.put("header", headerStyle);
>
> return styles;
> }
>
>
>
> private static void generateSummary(Writer out, Map<String,
> XSSFCellStyle> styles) throws Exception {
>
> SpreadsheetWriter sw = new SpreadsheetWriter(out);
> sw.beginSheet();
>
> //insert header row
> sw.insertRow(0);
> int styleIndex = styles.get("header").getIndex();
> sw.createCell(0, OFFICE_NAME, styleIndex);
> sw.createCell(1, PASSED, styleIndex);
> sw.createCell(2, FAILED, styleIndex);
> sw.createCell(3, INCOMPLETE, styleIndex);
> sw.createCell(4, PFC, styleIndex);
> sw.createCell(5, BCO, styleIndex);
> sw.createCell(6, TOTAL_SENT, styleIndex);
>
> sw.endRow();
>
> //write data rows
> for (int rownum = 1; rownum < 5; rownum++) {
> sw.insertRow(rownum);
>
> sw.createCell(0, "Hello, " + rownum + "!");
> sw.createCell(1, "Hello, " + rownum + "!");
> sw.createCell(2, "Hello, " + rownum + "!");
> sw.createCell(3, "Hello, " + rownum + "!");
> sw.createCell(4, "Hello, " + rownum + "!");
> sw.createCell(5, "Hello, " + rownum + "!");
> sw.createCell(6, "Hello, " + rownum + "!");
> sw.endRow();
>
> }
> sw.endSheet();
> }
>
> private static void generateDetails(Writer out, Map<String,
> XSSFCellStyle> styles) throws Exception {
>
> SpreadsheetWriter sw = new SpreadsheetWriter(out);
> sw.beginSheet();
>
> //insert header row
> sw.insertRow(0);
> int styleIndex = styles.get("header").getIndex();
> sw.createCell(0, OFFICE_CODE, styleIndex);
> sw.createCell(1, USER, styleIndex);
> sw.createCell(2, ACCOUNT, styleIndex);
> sw.createCell(3, ACTION_CODE, styleIndex);
> sw.createCell(4, DESCRIPTION, styleIndex);
>
>
> sw.endRow();
>
> //write data rows
> for (int rownum = 1; rownum < 5; rownum++) {
> sw.insertRow(rownum);
>
> sw.createCell(0, "Hello, " + rownum + "!");
> sw.createCell(1, "Hello, " + rownum + "!");
> sw.createCell(2, "Hello, " + rownum + "!");
> sw.createCell(3, "Hello, " + rownum + "!");
> sw.createCell(4, "Hello, " + rownum + "!");
> sw.endRow();
> }
> sw.endSheet();
> }
>
> /**
> *
> * @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();
> System.out.println(ze.getName());
> 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(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);
> }
> }
>
> }
>
>
>
> Thanks,
> Nalini Nizamkar
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
