You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by jwala <ma...@gmail.com> on 2011/08/31 12:26:21 UTC
How to add Cell Comments to excel using ooxml format and POI
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to add Cell Comments to excel using ooxml format and POI
Posted by jwala <ma...@gmail.com>.
I have to generate large excel file having more than 100000 rows. POI is
prone to java heap space exception for such large excel file.Thats why I
need to generate excel file using ooxml.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-add-Cell-Comments-to-excel-using-ooxml-format-and-POI-tp4753649p4758885.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to add Cell Comments to excel using ooxml format and POI
Posted by Nick Burch <ni...@alfresco.com>.
On Wed, 31 Aug 2011, jwala wrote:
> 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
You'll likely want to use the regular XSSF usermodel for adding comments
(they're not the simplest things to add so you don't want to do it by
hand)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org