POI设置EXCEL单元格格式为文本.docx
- 文档编号:7806696
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:16
- 大小:60.53KB
POI设置EXCEL单元格格式为文本.docx
《POI设置EXCEL单元格格式为文本.docx》由会员分享,可在线阅读,更多相关《POI设置EXCEL单元格格式为文本.docx(16页珍藏版)》请在冰豆网上搜索。
POI设置EXCEL单元格格式为文本
实际开发过程中通常用到的就是从数据库导出EXCEL表格了,JXL可以这样做,其实POI也可以(关于JXL与POI的异同可访问我之前总结的文章),之前写过POI对七种文档(当然也包括EXCEL)的内容读取操作的文章,这次要写的就非常重要了,就是开发中经常会用到的POI读取数据库导出EXCEL的操作,所谓导出EXCEL也就是生成带数据内容的新的EXCEL文件
目前的POI版本是3.7
下载地址:
#POI-3.7
必须包只有一个:
poi-3.7-.jar
整理思路:
1)数据库中的字段对应EXCEL的最顶层一行各个CELL名称[也就是上面图片中序号版本...的]
2)将每个数据一次插入到对应名称CELL的对应记录位置
3)为了方便操作,顶层的cell各个名称可以抽取出来成为一个单独类
具体代码
第一部分:
单独的EXCEL表头类
publicclassCachetable{
Java代码
1.// Fields
2.private int recnum;
3.private String devIp;
4.private String srcaddr;
5.private String dstaddr;
6.private String nexthop;
7.private String input;
8.private String output;
9.private String dpkts;
10.private String doctets;
11.private String sstart;
12.private String dstport;
13.private String prot;
14.private String tos;
15.private String srcas;
16.private String dstas;
17.private String pduversion;
18./** default constructor */
19.public Cachetable() {
20.}
21./** full constructor */
22.public Cachetable(int recnum, String devIp, String srcaddr, String dstaddr, String nexthop, String input, String output, String dpkts, String doctets, String sstart, String dstport, String prot, String tos, String srcas, String dstas,String pduversion) {
23. this.recnum = recnum;
24. this.devIp = devIp;
25. this.srcaddr = srcaddr;
26. this.dstaddr = dstaddr;
27. this.nexthop = nexthop;
28. this.input = input;
29. this.output = output;
30. this.dpkts = dpkts;
31. this.doctets = doctets;
32. this.sstart = sstart;
33. this.dstport = dstport;
34. this.prot = prot;
35. this.tos = tos;
36. this.srcas = srcas;
37. this.dstas = dstas;
38. this.pduversion = pduversion;
39.}
40.public int getRecnum() {
41. return this.recnum;
42.}
43.public void setRecnum(int recnum) {
44. this.recnum= recnum;
45.}
46.public String getDevIp() {
47. return this.devIp;
48.}
49.public void setDevIp(String devIp) {
50. this.devIp = devIp;
51.}
52.public String getSrcaddr() {
53. return this.srcaddr;
54.}
55.public void setSrcaddr(String srcaddr) {
56. this.srcaddr = srcaddr;
57.}
58.public String getDstaddr() {
59. return this.dstaddr;
60.}
61.public void setDstaddr(String dstaddr) {
62. this.dstaddr = dstaddr;
63.}
64.public String getNexthop() {
65. return this.nexthop;
66.}
67.public void setNexthop(String nexthop) {
68. this.nexthop = nexthop;
69.}
70.public String getInput() {
71. return this.input;
72.}
73.public void setInput(String input) {
74. this.input = input;
75.}
76.public String getOutput() {
77. return this.output;
78.}
79.public void setOutput(String output) {
80. this.output = output;
81.}
82.public String getDpkts() {
83. return this.dpkts;
84.}
85.public void setDpkts(String dpkts) {
86. this.dpkts = dpkts;
87.}
88.public String getDoctets() {
89. return this.doctets;
90.}
91.public void setDoctets(String doctets) {
92. this.doctets = doctets;
93.}
94.public String getSstart() {
95. return this.sstart;
96.}
97.public void setSstart(String sstart) {
98. this.sstart = sstart;
99.}
100.public String getDstport() {
101. return this.dstport;
102.}
103.public void setDstport(String dstport) {
104. this.dstport = dstport;
105.}
106.public String getProt() {
107. return this.prot;
108.}
109.public void setProt(String prot) {
110. this.prot = prot;
111.}
112.public String getTos() {
113. return this.tos;
114.}
115.public void setTos(String tos) {
116. this.tos = tos;
117.}
118.public String getSrcas() {
119. return this.srcas;
120.}
121.public void setSrcas(String srcas) {
122. this.srcas = srcas;
123.}
124.public String getDstas() {
125. return this.dstas;
126.}
127.public void setDstas(String dstas) {
128. this.dstas = dstas;
129.}
130.public String getPduversion() {
131. return this.pduversion;
132.}
133.public void setPduversion(String pduversion) {
134. this.pduversion = pduversion;
135.}
第二部分:
具体的POI操作生成EXCEL类
【我这里只是个示例,没连数据库,直接运行即可,如果想连,稍微变动一点即可】
Java代码
1.package ;
2.import ;
3.import ;
4.import ;
5.import ;
6.import ;
7.import ;
8.import ;
9.import ;
10.import ;
11.import ;
12.import ;
13.import ;
14.import ;
15.import ;
16.import ;
17.import ;
18.public class ExcelOut {
19. //表头
20. public static final String[] tableHeader = {"序号","版本","接收时刻","设备","入接口","出接口",
21. "源IP","目的IP","下一跳","协议","端口","对端端口","TOS","源AS","目的AS","TCP_FLAG","pad1","pad2"};
22. //创建工作本 TOS
23. public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
24. //创建表
25. public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");
26. //表头的单元格个数目
27. public static final short cellNumber = (short)tableHeader.length;
28. //数据库表的列数
29. public static final int columNumber = 1;
30. /**
31. * 创建表头
32. * @return
33. */
34. public static void createTableHeader()
35. {
36. HSSFHeader header = demoSheet.getHeader();
37. header.setCenter("世界五百强企业名次表");
38. HSSFRow headerRow = demoSheet.createRow((short) 0);
39. for(int i = 0;i < cellNumber;i++)
40. {
41. HSSFCell headerCell = headerRow.createCell((short) i);
42. headerCell.setCellType(HSSFCell.CELL_TYPE_STRING);
43. headerCell.setCellValue(tableHeader[i]);
44. }
45. }
46. /**
47. * 创建行
48. * @param cells
49. * @param rowIndex
50. */
51. public static void createTableRow(List
52. {
53. //创建第rowIndex行
54. HSSFRow row = demoSheet.createRow((short) rowIndex);
55. for(int i = 0;i < cells.size();i++)
56. {
57. //创建第i个单元格
58. HSSFCell cell = row.createCell(i);
59. if(cell.getCellType()!
=1){
60. cell.setCellType(HSSFCell.CELL_TYPE_STRING);
61. }
62. //新增的四句话,设置CELL格式为文本格式
63. HSSFCellStyle cellStyle2 = demoWorkBook.createCellStyle();
64. HSSFDataFormat format = demoWorkBook.createDataFormat();
65. cellStyle2.setDataFormat(format.getFormat("@"));
66. cell.setCellStyle(cellStyle2);
67. cell.setCellValue(cells.get(i));
68. cell.setCellType(HSSFCell.CELL_TYPE_STRING);
69. }
70. }
71. /**
72. * USE:
用于获取Cachetable的数据。
。
。
假数据。
到时候:
你连接数据库的到List
共生成
73. * 100条数据.相当于100行
74. *
75. * @return
76. */
77. public List
78. List
79. for (int j = 0; j < 300; j++) {
80. Cachetable tb = new Cachetable();
81. tb.setRecnum(j + 1);
82. tb.setDevIp("JavaCrazyer");
83. tb.setSrcaddr("北京");
84. tb.setDstaddr("xxx");
85. tb.setNexthop("yy");
86. tb.setInput("123");
87. tb.setOutput("");
88. tb.setDpkts("what are you doing?
");
89. tb.setDoctets("who are you?
");
90. tb.setSstart("Oh sure!
");
91. tb.setProt("One");
92. tb.setTos("two");
93. tb.setSrcas("three");
94. tb.setDstas("four");
95. tb.setPduversion("不知道");
96. cacheList.add(tb);
97. }
98. return cacheList;
99. }
100. /**
101. * 创建整个Excel表
102. * @throws SQLException
103. *
104. */
105. public void createExcelSheet() throws SQLException{
106. createTableHeader();
107. int rowIndex=1;
108. List
109. for(int j=0;j 110. List 111. for(int i=1;i<=columNumber;i++){ 112. listRead.add(list.get(i).getDevIp()); 113. listRead.add(list.get(i).getSrcaddr()); 114. listRead.add(list.get(i).getDstaddr()); 115. listRead.add(list.get(i).getNexthop()); 116. listRead.add(list.get(i).getInput()); 117. listRead.add(list.get(i).getOutput()); 118. listRead.add(list.get(i).getDpkts()); 119. listRead.add(list.get(i).getDoctets()); 120. listRead.add(list.get(i).getSstart()); 121. listRead.add(list.get(i).getProt()); 122. listRead.add(list.get(i).getTos()); 123. listRead.add(list.get(i).getSrcas()); 124. listRead.add(list.get(i).getDstas()); 125. listRead.add(list.get(i).getPduversion()); 126. listRead.add(rowIndex+""); 127. } 128. createTableRow(listRead,(short)rowIndex); 129. r
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- POI 设置 EXCEL 单元格 格式 文本