Search...
FAQs
Subscribe
Pie
FAQs
Recent topics
Flagged topics
Hot topics
Best topics
Search...
Search within Open Source Projects
Search Coderanch
Advance search
Google search
Register / Login
Post Reply
Bookmark Topic
Watch Topic
New Topic
programming forums
Java
Mobile
Certification
Databases
Caching
Books
Engineering
Micro Controllers
OS
Languages
Paradigms
IDEs
Build Tools
Frameworks
Application Servers
Open Source
This Site
Careers
Other
Pie Elite
all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Campbell Ritchie
Jeanne Boyarsky
Ron McLeod
Paul Clapham
Liutauras Vilda
Sheriffs:
paul wheaton
Rob Spoor
Devaka Cooray
Saloon Keepers:
Stephan van Hulst
Tim Holloway
Carey Brown
Frits Walraven
Tim Moores
Bartenders:
Mikalai Zaikin
Forum:
Other Open Source Projects
Generate large excel using POI
Jwala Prakash
Greenhorn
Posts: 5
posted 12 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Hi,
I am using the following code to generate excel.
http://www.docjar.com/html/api/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.html
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.DateUtil; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class BigGridDemo { private static final String XML_ENCODING = "UTF-8"; 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(); XSSFSheet sheet = wb.createSheet("Big Grid"); Map<String, XSSFCellStyle> styles = createStyles(wb); //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); //save the template FileOutputStream os = new FileOutputStream("template.xlsx"); wb.write(os); os.close(); //Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); //Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream("big-grid.xlsx"); substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); out.close(); } /** * Create a library of cell styles. */ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("date", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("header", style5); return styles; } private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception { Random rnd = new Random(); Calendar calendar = Calendar.getInstance(); SpreadsheetWriter sw = new SpreadsheetWriter(out); sw.beginSheet(); //insert header row sw.insertRow(0); int styleIndex = styles.get("header").getIndex(); sw.createCell(0, "Title", styleIndex); sw.createCell(1, "% Change", styleIndex); sw.createCell(2, "Ratio", styleIndex); sw.createCell(3, "Expenses", styleIndex); sw.createCell(4, "Date", styleIndex); sw.endRow(); //write data rows for (int rownum = 1; rownum < 100000; rownum++) { sw.insertRow(rownum); sw.createCell(0, "Hello, " + rownum + "!"); sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex()); sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex()); sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex()); sw.createCell(4, calendar, styles.get("date").getIndex()); sw.endRow(); calendar.roll(Calendar.DAY_OF_YEAR, 1); } sw.endSheet(); } /** * * @param zipfile the template file * @param tmpfile the XML file with the sheet data * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml * @param out the stream to write the result to */ private static void substitute(File zipfile, File tmpfile, String entry, 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(!ze.getName().equals(entry)){ zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = zip.getInputStream(ze); copyStream(is, zos); is.close(); } } zos.putNextEntry(new ZipEntry(entry)); InputStream is = new FileInputStream(tmpfile); 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=\""+XML_ENCODING+"\"?>" + "<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); } } }
How to add Cell Comments using above code?
Cell-Comment.png
Cell Comment
Ulf Dittmer
Rancher
Posts: 43081
77
posted 12 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
Can't say I understand what's with the hand-generated XML, but the normal way to add cell comments is described in
http://poi.apache.org/spreadsheet/quick-guide.html#CellComments
Jwala Prakash
Greenhorn
Posts: 5
posted 12 years ago
Number of slices to send:
Optional 'thank-you' note:
Send
I have ooxml format for cell comment :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <authors><author/></authors> <commentList> <comment ref="E3" authorId="0"> <text> <r> <rPr> <sz val="10"/> <rFont val="Arial"/> </rPr> <t>Please select appropriate option</t> </r> </text> </comment> <comment ref="E4" authorId="0"> <text> <r> <rPr> <sz val="10"/> <rFont val="Arial"/> </rPr> <t>Please mark yes against your choice</t> </r> </text> </comment> </commentList> </comments>
How to use it?
Whatever you say buddy! And I believe this tiny ad too:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
reply
Bookmark Topic
Watch Topic
New Topic
Boost this thread!
Similar Threads
ZipInputStream: getting file size of -1 when reading
After Unzip delete that file programatically??
Unzip problem .. please its urgent
Generate large excel using POI
read Zipfile within a zipfile
More...