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 BigGridDemo.java 1. import java.io.*; 2. import java.util.*; 3. import java.util.zip.ZipEntry; 4. import java.util.zip.ZipFile; 5. import java.util.zip.ZipOutputStream; 6. 7. import org.apache.poi.ss.usermodel.DateUtil; 8. import org.apache.poi.ss.usermodel.IndexedColors; 9. import org.apache.poi.ss.util.CellReference; 10. import org.apache.poi.xssf.usermodel.XSSFCellStyle; 11. import org.apache.poi.xssf.usermodel.XSSFDataFormat; 12. import org.apache.poi.xssf.usermodel.XSSFFont; 13. import org.apache.poi.xssf.usermodel.XSSFSheet; 14. import org.apache.poi.xssf.usermodel.XSSFWorkbook; 15. 16. public class BigGridDemo { 17. private static final String XML_ENCODING = "UTF-8"; 18. 19. public static void main(String[] args) throws Exception { 20. 21. // Step 1. Create a template file. Setup sheets and workbook-level objects such as 22. // cell styles, number formats, etc. 23. 24. XSSFWorkbook wb = new XSSFWorkbook(); 25. XSSFSheet sheet = wb.createSheet("Big Grid"); 26. 27. Map<String, XSSFCellStyle> styles = createStyles(wb); 28. //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml 29. String sheetRef = sheet.getPackagePart().getPartName().getName(); 30. 31. //save the template 32. FileOutputStream os = new FileOutputStream("template.xlsx"); 33. wb.write(os); 34. os.close(); 35. 36. //Step 2. Generate XML file. 37. File tmp = File.createTempFile("sheet", ".xml"); 38. Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); 39. generate(fw, styles); 40. fw.close(); 41. 42. //Step 3. Substitute the template entry with the generated data 43. FileOutputStream out = new FileOutputStream("big-grid.xlsx"); 44. substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); 45. out.close(); 46. } 47. 48. /** 49. * Create a library of cell styles. 50. */ 51. private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){ 52. Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); 53. XSSFDataFormat fmt = wb.createDataFormat(); 54. 55. XSSFCellStyle style1 = wb.createCellStyle(); 56. style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 57. style1.setDataFormat(fmt.getFormat("0.0%")); 58. styles.put("percent", style1); 59. 60. XSSFCellStyle style2 = wb.createCellStyle(); 61. style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); 62. style2.setDataFormat(fmt.getFormat("0.0X")); 63. styles.put("coeff", style2); 64. 65. XSSFCellStyle style3 = wb.createCellStyle(); 66. style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 67. style3.setDataFormat(fmt.getFormat("$#,##0.00")); 68. styles.put("currency", style3); 69. 70. XSSFCellStyle style4 = wb.createCellStyle(); 71. style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); 72. style4.setDataFormat(fmt.getFormat("mmm dd")); 73. styles.put("date", style4); 74. 75. XSSFCellStyle style5 = wb.createCellStyle(); 76. XSSFFont headerFont = wb.createFont(); 77. headerFont.setBold(true); 78. style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); 79. style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); 80. style5.setFont(headerFont); 81. styles.put("header", style5); 82. 83. return styles; 84. } 85. 86. private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception { 87. 88. Random rnd = new Random(); 89. Calendar calendar = Calendar.getInstance(); 90. 91. SpreadsheetWriter sw = new SpreadsheetWriter(out); 92. sw.beginSheet(); 93. 94. //insert header row 95. sw.insertRow(0); 96. int styleIndex = styles.get("header").getIndex(); 97. sw.createCell(0, "Title", styleIndex); 98. sw.createCell(1, "% Change", styleIndex); 99. sw.createCell(2, "Ratio", styleIndex); 100. sw.createCell(3, "Expenses", styleIndex); 101. sw.createCell(4, "Date", styleIndex); 102. 103. sw.endRow(); 104. 105. //write data rows 106. for (int rownum = 1; rownum < 100000; rownum++) { 107. sw.insertRow(rownum); 108. 109. sw.createCell(0, "Hello, " + rownum + "!"); 110. sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex()); 111. sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex()); 112. sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex()); 113. sw.createCell(4, calendar, styles.get("date").getIndex()); 114. 115. sw.endRow(); 116. 117. calendar.roll(Calendar.DAY_OF_YEAR, 1); 118. } 119. sw.endSheet(); 120. } 121. 122. /** 123. * 124. * @param zipfile the template file 125. * @param tmpfile the XML file with the sheet data 126. * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml 127. * @param out the stream to write the result to 128. */ 129. private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException { 130. ZipFile zip = new ZipFile(zipfile); 131. 132. ZipOutputStream zos = new ZipOutputStream(out); 133. 134. @SuppressWarnings("unchecked") 135. Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries(); 136. while (en.hasMoreElements()) { 137. ZipEntry ze = en.nextElement(); 138. if(!ze.getName().equals(entry)){ 139. zos.putNextEntry(new ZipEntry(ze.getName())); 140. InputStream is = zip.getInputStream(ze); 141. copyStream(is, zos); 142. is.close(); 143. } 144. } 145. zos.putNextEntry(new ZipEntry(entry)); 146. InputStream is = new FileInputStream(tmpfile); 147. copyStream(is, zos); 148. is.close(); 149. 150. zos.close(); 151. } 152. 153. private static void copyStream(InputStream in, OutputStream out) throws IOException { 154. byte[] chunk = new byte[1024]; 155. int count; 156. while ((count = in.read(chunk)) >=0 ) { 157. out.write(chunk,0,count); 158. } 159. } 160. 161. /** 162. * Writes spreadsheet data in a Writer. 163. * (YK: in future it may evolve in a full-featured API for streaming data in Excel) 164. */ 165. public static class SpreadsheetWriter { 166. private final Writer _out; 167. private int _rownum; 168. 169. public SpreadsheetWriter(Writer out){ 170. _out = out; 171. } 172. 173. public void beginSheet() throws IOException { 174. _out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" + 175. "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\";>" ); 176. _out.write("<sheetData>\n"); 177. } 178. 179. public void endSheet() throws IOException { 180. _out.write("</sheetData>"); 181. _out.write("</worksheet>"); 182. } 183. 184. /** 185. * Insert a new row 186. * 187. * @param rownum 0-based row number 188. */ 189. public void insertRow(int rownum) throws IOException { 190. _out.write("<row r=\""+(rownum+1)+"\">\n"); 191. this._rownum = rownum; 192. } 193. 194. /** 195. * Insert row end marker 196. */ 197. public void endRow() throws IOException { 198. _out.write("</row>\n"); 199. } 200. 201. public void createCell(int columnIndex, String value, int styleIndex) throws IOException { 202. String ref = new CellReference(_rownum, columnIndex).formatAsString(); 203. _out.write("<c r=\""+ref+"\" t=\"inlineStr\""); 204. if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\""); 205. _out.write(">"); 206. _out.write("<is><t>"+value+"</t></is>"); 207. _out.write("</c>"); 208. } 209. 210. public void createCell(int columnIndex, String value) throws IOException { 211. createCell(columnIndex, value, -1); 212. } 213. 214. public void createCell(int columnIndex, double value, int styleIndex) throws IOException { 215. String ref = new CellReference(_rownum, columnIndex).formatAsString(); 216. _out.write("<c r=\""+ref+"\" t=\"n\""); 217. if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\""); 218. _out.write(">"); 219. _out.write("<v>"+value+"</v>"); 220. _out.write("</c>"); 221. } 222. 223. public void createCell(int columnIndex, double value) throws IOException { 224. createCell(columnIndex, value, -1); 225. } 226. 227. public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { 228. createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); 229. } 230. } 231. } How to add Cell Comments using above code? http://apache-poi.1045710.n5.nabble.com/file/n4753649/comment.jpg -- View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-add-Cell-Comments-to-excel-using-ooxml-format-and-POI-tp4753649p4753649.html Sent from the POI - Dev mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
