|
锁定老帖子 主题：POI读取Excel浅谈
精华帖 (0) :: 良好帖 (4) :: 新手帖 (4) :: 隐藏帖 (16)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2010-09-13
wenty09 写道
huahuashijie888 写道
我们也用这个导出，以前遇到过数字导出的时候，用excel打开，在单元格总有个绿色小箭头，说是该值不能解析为数字。。小问题，后来也没处理
绿色箭头是表示单元格里的数字是字符串吧~在excel里可以转换啊
|
| 返回顶楼 | |
|
发表时间：2010-09-13
xtuhuajx 写道 当excel数据量比较大的时候怎么处理啊？比如5万行300列。
EXCEL数据量很大的话，对内存的消耗比较大，因为POI每个CELL都是对象，尽量分表或者分文件比较好 |
| 返回顶楼 | |
|
发表时间：2010-09-13
karidyang 写道 xtuhuajx 写道 当excel数据量比较大的时候怎么处理啊？比如5万行300列。
EXCEL数据量很大的话，对内存的消耗比较大，因为POI每个CELL都是对象，尽量分表或者分文件比较好 直接操作流吧...... |
| 返回顶楼 | |
|
发表时间：2010-09-13
xtuhuajx 写道 当excel数据量比较大的时候怎么处理啊？比如5万行300列。 同样问题等待中。
9820行X110列数据量，在本地输出到Excel没问题，部署到WebSphere时报OutOfMemoryError错误。 目前有种比较好的解决方法是： 分段输出到Excel，然后合并成一个大的Excel，可以解决在输出过程中Cell创建而导致的内存溢出。 但我在本地输出一个35M的Excel文件，然后再从Web服务器上读取，读取过程中同样发生了OutOfMemoryError。 因为POI对Excel文件的操作都是在内存中进行的，不论你用多少行，POI是一次性将所有数据都载入到内存中，从而导致OutOfMemory。 问：有没有一种API是类似Stream形式的，读哪一行才载入到内存。 |
| 返回顶楼 | |
|
发表时间：2010-09-13
jychenok 写道 karidyang 写道 xtuhuajx 写道 当excel数据量比较大的时候怎么处理啊？比如5万行300列。
EXCEL数据量很大的话，对内存的消耗比较大，因为POI每个CELL都是对象，尽量分表或者分文件比较好 直接操作流吧...... 如何操作？ |
| 返回顶楼 | |
|
发表时间：2010-09-13
最后修改：2010-09-13
wenty09 写道
huahuashijie888 写道
我们也用这个导出，以前遇到过数字导出的时候，用excel打开，在单元格总有个绿色小箭头，说是该值不能解析为数字。。小问题，后来也没处理
绿色箭头是表示单元格里的数字是字符串吧~在excel里可以转换啊
short fmt = format.getFormat("#,##0.00");
HSSFCellStyle style = wb.createCellStyle();
style.setDataFormat(fmt)
然后吧 style 设置为 单元格的 样式 就不会有 “转换为数字” 的提示了！ |
| 返回顶楼 | |
|
发表时间：2010-09-13
曾经简单的用了一下，现在还行进一步学习
|
| 返回顶楼 | |
|
发表时间：2010-09-14
最后修改：2010-09-14
jychenok 写道
karidyang 写道
xtuhuajx 写道
当excel数据量比较大的时候怎么处理啊？比如5万行300列。
EXCEL数据量很大的话，对内存的消耗比较大，因为POI每个CELL都是对象，尽量分表或者分文件比较好 直接操作流吧......
public static void main(String[] args) {
try {//
File file = new File("C:\\test.xls");
file.createNewFile();
BufferedWriter output = new BufferedWriter(new FileWriter(file));
//文件前面的固定格式部份
output.write("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n");
output.write("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n");
output.write("xmlns=\"http://www.w3.org/TR/REC-html40\">\n");
output.write("\n");
output.write("<head>\n");
output.write("<meta http-equiv=Content-Type content=\"text/html; charset=gb2312\">\n");
output.write("<meta name=ProgId content=Excel.Sheet>\n");
output.write("<meta name=Generator content=\"Microsoft Excel 11\">\n");
output.write("<!--[if gte mso 9]><xml>\n");
output.write(" <o:DocumentProperties>\n");
output.write(" <o:Created>1996-12-17T01:32:42Z</o:Created>\n");
output.write(" <o:LastSaved>2010-09-14T01:20:26Z</o:LastSaved>\n");
output.write(" <o:Version>11.5606</o:Version>\n");
output.write(" </o:DocumentProperties>\n");
output.write(" <o:OfficeDocumentSettings>\n");
output.write(" <o:RemovePersonalInformation/>\n");
output.write(" </o:OfficeDocumentSettings>\n");
output.write("</xml><![endif]-->\n");
output.write("<style>\n");
output.write("<!--table\n");
output.write(" {mso-displayed-decimal-separator:\"\\.\";\n");
output.write(" mso-displayed-thousand-separator:\"\\,\";}\n");
output.write("@page\n");
output.write(" {margin:1.0in .75in 1.0in .75in;\n");
output.write(" mso-header-margin:.5in;\n");
output.write(" mso-footer-margin:.5in;}\n");
output.write("tr\n");
output.write(" {mso-height-source:auto;\n");
output.write(" mso-ruby-visibility:none;}\n");
output.write("col\n");
output.write(" {mso-width-source:auto;\n");
output.write(" mso-ruby-visibility:none;}\n");
output.write("br\n");
output.write(" {mso-data-placement:same-cell;}\n");
output.write(".style0\n");
output.write(" {mso-number-format:General;\n");
output.write(" text-align:general;\n");
output.write(" vertical-align:bottom;\n");
output.write(" white-space:nowrap;\n");
output.write(" mso-rotate:0;\n");
output.write(" mso-background-source:auto;\n");
output.write(" mso-pattern:auto;\n");
output.write(" color:windowtext;\n");
output.write(" font-size:12.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" border:none;\n");
output.write(" mso-protection:locked visible;\n");
output.write(" mso-style-name:常规;\n");
output.write(" mso-style-id:0;}\n");
output.write("td\n");
output.write(" {mso-style-parent:style0;\n");
output.write(" padding-top:1px;\n");
output.write(" padding-right:1px;\n");
output.write(" padding-left:1px;\n");
output.write(" mso-ignore:padding;\n");
output.write(" color:windowtext;\n");
output.write(" font-size:12.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" mso-number-format:General;\n");
output.write(" text-align:general;\n");
output.write(" vertical-align:bottom;\n");
output.write(" border:none;\n");
output.write(" mso-background-source:auto;\n");
output.write(" mso-pattern:auto;\n");
output.write(" mso-protection:locked visible;\n");
output.write(" white-space:nowrap;\n");
output.write(" mso-rotate:0;}\n");
output.write("ruby\n");
output.write(" {ruby-align:left;}\n");
output.write("rt\n");
output.write(" {color:windowtext;\n");
output.write(" font-size:9.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" mso-char-type:none;\n");
output.write(" display:none;}\n");
output.write("-->\n");
output.write("</style>\n");
output.write("<!--[if gte mso 9]><xml>\n");
output.write(" <x:ExcelWorkbook>\n");
output.write(" <x:ExcelWorksheets>\n");
output.write(" <x:ExcelWorksheet>\n");
output.write(" <x:Name>Sheet1</x:Name>\n");
output.write(" <x:WorksheetOptions>\n");
output.write(" <x:DefaultRowHeight>285</x:DefaultRowHeight>\n");
output.write(" <x:CodeName>Sheet1</x:CodeName>\n");
output.write(" <x:Selected/>\n");
output.write(" <x:Panes>\n");
output.write(" <x:Pane>\n");
output.write(" <x:Number>3</x:Number>\n");
output.write(" <x:ActiveRow>4</x:ActiveRow>\n");
output.write(" <x:ActiveCol>3</x:ActiveCol>\n");
output.write(" </x:Pane>\n");
output.write(" </x:Panes>\n");
output.write(" <x:ProtectContents>False</x:ProtectContents>\n");
output.write(" <x:ProtectObjects>False</x:ProtectObjects>\n");
output.write(" <x:ProtectScenarios>False</x:ProtectScenarios>\n");
output.write(" </x:WorksheetOptions>\n");
output.write(" </x:ExcelWorksheet>\n");
output.write(" </x:ExcelWorksheets>\n");
output.write(" <x:WindowHeight>4530</x:WindowHeight>\n");
output.write(" <x:WindowWidth>8505</x:WindowWidth>\n");
output.write(" <x:WindowTopX>480</x:WindowTopX>\n");
output.write(" <x:WindowTopY>120</x:WindowTopY>\n");
output.write(" <x:AcceptLabelsInFormulas/>\n");
output.write(" <x:ProtectStructure>False</x:ProtectStructure>\n");
output.write(" <x:ProtectWindows>False</x:ProtectWindows>\n");
output.write(" </x:ExcelWorkbook>\n");
output.write("</xml><![endif]-->\n");
output.write("</head>\n");
output.write("\n");
output.write("<body link=blue vlink=purple>\n");
output.write("\n");
//table里面写内容
output.write("<table x:str border=0 cellpadding=0 cellspacing=0 width=288 style='border-collapse:\n");
output.write(" collapse;table-layout:fixed;width:216pt'>\n");
output.write(" <col width=72 span=4 style='width:54pt'>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 align=right width=72 style='height:14.25pt;width:54pt' x:num>1</td>\n");
//数字
output.write(" <td align=right width=72 style='width:54pt' x:num>12</td>\n");
output.write(" <td align=right width=72 style='width:54pt' x:num>467</td>\n");
output.write(" <td align=right width=72 style='width:54pt' x:num>890</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 align=right style='height:14.25pt' x:num>1</td>\n");
output.write(" <td align=right x:num>123</td>\n");
output.write(" <td align=right x:num>489</td>\n");
output.write(" <td align=right x:num>234</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
//字符
output.write(" <td height=19 style='height:14.25pt'>1q</td>\n");
output.write(" <td>qw</td>\n");
output.write(" <td>qw</td>\n");
output.write(" <td>wq</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 style='height:14.25pt'>dg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 style='height:14.25pt'>dd</td>\n");
output.write(" <td>dd</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>212e</td>\n");
output.write(" </tr>\n");
//后面的固定格式部分
output.write(" <![if supportMisalignedColumns]>\n");
output.write(" <tr height=0 style='display:none'>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" </tr>\n");
output.write(" <![endif]>\n");
output.write("</table>\n");
output.write("\n");
output.write("</body>\n");
output.write("\n");
output.write("</html>\n");
output.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
|
| 返回顶楼 | |
|
发表时间：2010-09-14
thanks very much that's good |
| 返回顶楼 | |
|
发表时间：2010-12-09
xtuhuajx 写道
jychenok 写道
karidyang 写道
xtuhuajx 写道
当excel数据量比较大的时候怎么处理啊？比如5万行300列。
EXCEL数据量很大的话，对内存的消耗比较大，因为POI每个CELL都是对象，尽量分表或者分文件比较好 直接操作流吧......
public static void main(String[] args) {
try {//
File file = new File("C:\\test.xls");
file.createNewFile();
BufferedWriter output = new BufferedWriter(new FileWriter(file));
//文件前面的固定格式部份
output.write("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n");
output.write("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n");
output.write("xmlns=\"http://www.w3.org/TR/REC-html40\">\n");
output.write("\n");
output.write("<head>\n");
output.write("<meta http-equiv=Content-Type content=\"text/html; charset=gb2312\">\n");
output.write("<meta name=ProgId content=Excel.Sheet>\n");
output.write("<meta name=Generator content=\"Microsoft Excel 11\">\n");
output.write("<!--[if gte mso 9]><xml>\n");
output.write(" <o:DocumentProperties>\n");
output.write(" <o:Created>1996-12-17T01:32:42Z</o:Created>\n");
output.write(" <o:LastSaved>2010-09-14T01:20:26Z</o:LastSaved>\n");
output.write(" <o:Version>11.5606</o:Version>\n");
output.write(" </o:DocumentProperties>\n");
output.write(" <o:OfficeDocumentSettings>\n");
output.write(" <o:RemovePersonalInformation/>\n");
output.write(" </o:OfficeDocumentSettings>\n");
output.write("</xml><![endif]-->\n");
output.write("<style>\n");
output.write("<!--table\n");
output.write(" {mso-displayed-decimal-separator:\"\\.\";\n");
output.write(" mso-displayed-thousand-separator:\"\\,\";}\n");
output.write("@page\n");
output.write(" {margin:1.0in .75in 1.0in .75in;\n");
output.write(" mso-header-margin:.5in;\n");
output.write(" mso-footer-margin:.5in;}\n");
output.write("tr\n");
output.write(" {mso-height-source:auto;\n");
output.write(" mso-ruby-visibility:none;}\n");
output.write("col\n");
output.write(" {mso-width-source:auto;\n");
output.write(" mso-ruby-visibility:none;}\n");
output.write("br\n");
output.write(" {mso-data-placement:same-cell;}\n");
output.write(".style0\n");
output.write(" {mso-number-format:General;\n");
output.write(" text-align:general;\n");
output.write(" vertical-align:bottom;\n");
output.write(" white-space:nowrap;\n");
output.write(" mso-rotate:0;\n");
output.write(" mso-background-source:auto;\n");
output.write(" mso-pattern:auto;\n");
output.write(" color:windowtext;\n");
output.write(" font-size:12.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" border:none;\n");
output.write(" mso-protection:locked visible;\n");
output.write(" mso-style-name:常规;\n");
output.write(" mso-style-id:0;}\n");
output.write("td\n");
output.write(" {mso-style-parent:style0;\n");
output.write(" padding-top:1px;\n");
output.write(" padding-right:1px;\n");
output.write(" padding-left:1px;\n");
output.write(" mso-ignore:padding;\n");
output.write(" color:windowtext;\n");
output.write(" font-size:12.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" mso-number-format:General;\n");
output.write(" text-align:general;\n");
output.write(" vertical-align:bottom;\n");
output.write(" border:none;\n");
output.write(" mso-background-source:auto;\n");
output.write(" mso-pattern:auto;\n");
output.write(" mso-protection:locked visible;\n");
output.write(" white-space:nowrap;\n");
output.write(" mso-rotate:0;}\n");
output.write("ruby\n");
output.write(" {ruby-align:left;}\n");
output.write("rt\n");
output.write(" {color:windowtext;\n");
output.write(" font-size:9.0pt;\n");
output.write(" font-weight:400;\n");
output.write(" font-style:normal;\n");
output.write(" text-decoration:none;\n");
output.write(" font-family:宋体;\n");
output.write(" mso-generic-font-family:auto;\n");
output.write(" mso-font-charset:134;\n");
output.write(" mso-char-type:none;\n");
output.write(" display:none;}\n");
output.write("-->\n");
output.write("</style>\n");
output.write("<!--[if gte mso 9]><xml>\n");
output.write(" <x:ExcelWorkbook>\n");
output.write(" <x:ExcelWorksheets>\n");
output.write(" <x:ExcelWorksheet>\n");
output.write(" <x:Name>Sheet1</x:Name>\n");
output.write(" <x:WorksheetOptions>\n");
output.write(" <x:DefaultRowHeight>285</x:DefaultRowHeight>\n");
output.write(" <x:CodeName>Sheet1</x:CodeName>\n");
output.write(" <x:Selected/>\n");
output.write(" <x:Panes>\n");
output.write(" <x:Pane>\n");
output.write(" <x:Number>3</x:Number>\n");
output.write(" <x:ActiveRow>4</x:ActiveRow>\n");
output.write(" <x:ActiveCol>3</x:ActiveCol>\n");
output.write(" </x:Pane>\n");
output.write(" </x:Panes>\n");
output.write(" <x:ProtectContents>False</x:ProtectContents>\n");
output.write(" <x:ProtectObjects>False</x:ProtectObjects>\n");
output.write(" <x:ProtectScenarios>False</x:ProtectScenarios>\n");
output.write(" </x:WorksheetOptions>\n");
output.write(" </x:ExcelWorksheet>\n");
output.write(" </x:ExcelWorksheets>\n");
output.write(" <x:WindowHeight>4530</x:WindowHeight>\n");
output.write(" <x:WindowWidth>8505</x:WindowWidth>\n");
output.write(" <x:WindowTopX>480</x:WindowTopX>\n");
output.write(" <x:WindowTopY>120</x:WindowTopY>\n");
output.write(" <x:AcceptLabelsInFormulas/>\n");
output.write(" <x:ProtectStructure>False</x:ProtectStructure>\n");
output.write(" <x:ProtectWindows>False</x:ProtectWindows>\n");
output.write(" </x:ExcelWorkbook>\n");
output.write("</xml><![endif]-->\n");
output.write("</head>\n");
output.write("\n");
output.write("<body link=blue vlink=purple>\n");
output.write("\n");
//table里面写内容
output.write("<table x:str border=0 cellpadding=0 cellspacing=0 width=288 style='border-collapse:\n");
output.write(" collapse;table-layout:fixed;width:216pt'>\n");
output.write(" <col width=72 span=4 style='width:54pt'>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 align=right width=72 style='height:14.25pt;width:54pt' x:num>1</td>\n");
//数字
output.write(" <td align=right width=72 style='width:54pt' x:num>12</td>\n");
output.write(" <td align=right width=72 style='width:54pt' x:num>467</td>\n");
output.write(" <td align=right width=72 style='width:54pt' x:num>890</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 align=right style='height:14.25pt' x:num>1</td>\n");
output.write(" <td align=right x:num>123</td>\n");
output.write(" <td align=right x:num>489</td>\n");
output.write(" <td align=right x:num>234</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
//字符
output.write(" <td height=19 style='height:14.25pt'>1q</td>\n");
output.write(" <td>qw</td>\n");
output.write(" <td>qw</td>\n");
output.write(" <td>wq</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 style='height:14.25pt'>dg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" </tr>\n");
output.write(" <tr height=19 style='height:14.25pt'>\n");
output.write(" <td height=19 style='height:14.25pt'>dd</td>\n");
output.write(" <td>dd</td>\n");
output.write(" <td>dfg</td>\n");
output.write(" <td>212e</td>\n");
output.write(" </tr>\n");
//后面的固定格式部分
output.write(" <![if supportMisalignedColumns]>\n");
output.write(" <tr height=0 style='display:none'>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" <td width=72 style='width:54pt'></td>\n");
output.write(" </tr>\n");
output.write(" <![endif]>\n");
output.write("</table>\n");
output.write("\n");
output.write("</body>\n");
output.write("\n");
output.write("</html>\n");
output.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
学习了 谢谢 |
| 返回顶楼 | |
