java将查询结果写入到Excel表中

maven依赖

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

 

代码

  1 package com.lwl.util;
  2 
  3 import com.lwl.entity.LogForInterface;
  4 import com.lwl.entity._12Cdoman;
  5 import com.test.entity.EmployeeBasicInformation;
  6 import jxl.Workbook;
  7 import jxl.write.Label;
  8 import jxl.write.WritableSheet;
  9 import jxl.write.WritableWorkbook;
 10 import java.io.File;
 11 import java.util.ArrayList;
 12 
 13 /** 将结果写到Excel中
 14  * @author liuwenlong
 15  * @create 2021-11-06 13:46:18
 16  */
 17 @SuppressWarnings("all")
 18 public class Excel {
 19 
 20     //将12C运维周报报错前十的总结果存储到excel中
 21     public static void excelOutFor12cZhouBao(ArrayList<_12Cdoman> arrayList) {
 22         WritableWorkbook bWorkbook = null;
 23         try {
 24             // 创建Excel对象
 25             bWorkbook = Workbook.createWorkbook(new File("D:/12C运维周报/12C运维周报.xls"));
 26             // 通过Excel对象创建一个选项卡对象
 27             WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
 28             //使用循环将数据读出
 29             for (int i = 0; i < arrayList.size(); i++) {
 30                 _12Cdoman _12cDoman = arrayList.get(i);
 31                 Label label = new Label(0, i, String.valueOf(_12cDoman.getServicename()));
 32                 Label label1 = new Label(1, i, String.valueOf(_12cDoman.getRequestcount()));
 33                 Label label2 = new Label(2, i, String.valueOf(_12cDoman.getErrorcount()));
 34                 sheet.addCell(label);
 35                 sheet.addCell(label1);
 36                 sheet.addCell(label2);
 37             }
 38 
 39             // 创建一个单元格对象，第一个为列，第二个为行，第三个为值
 40             Label label = new Label(0, 2, "test");
 41             // 将创建好的单元格放入选项卡中
 42             //sheet.addCell(label);
 43             // 写如目标路径
 44             bWorkbook.write();
 45 
 46         } catch (Exception e) {
 47             // TODO Auto-generated catch block
 48             e.printStackTrace();
 49         } finally {
 50             try {
 51                 bWorkbook.close();
 52             } catch (Exception e) {
 53                 // TODO Auto-generated catch block
 54                 e.printStackTrace();
 55             }
 56         }
 57 
 58     }
 59 
 60 
 61 
 62 
 63     //将单个接口查询到的结果存放excel中
 64     public static void excelOutFor12cZhouBaoForSingle(ArrayList<LogForInterface> arrayList,String interfaceNumber) {
 65         WritableWorkbook bWorkbook = null;
 66         try {
 67             // 创建Excel对象
 68             bWorkbook = Workbook.createWorkbook(new File("D:/12C运维周报/"+interfaceNumber+".xls"));
 69             // 通过Excel对象创建一个选项卡对象
 70             WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
 71             //使用循环将数据读出
 72             for (int i = 0; i < arrayList.size(); i++) {
 73                 LogForInterface logForInterface = arrayList.get(i);
 74                 Label label1 = new Label(0, i, String.valueOf(logForInterface.getID()));
 75                 Label label2 = new Label(1, i, String.valueOf(logForInterface.getLOGUID()));
 76                 Label label3 = new Label(2, i, String.valueOf(logForInterface.getBIZTRANSACTIONID()));
 77                 Label label4 = new Label(3, i, String.valueOf(logForInterface.getSERVICENAME()));
 78                 Label label5 = new Label(4, i, String.valueOf(logForInterface.getSERVICETYPE()));
 79                 Label label6 = new Label(5, i, String.valueOf(logForInterface.getSERVICEPATH()));
 80                 Label label7 = new Label(6, i, String.valueOf(logForInterface.getCONSUMER()));
 81                 Label label8 = new Label(7, i, String.valueOf(logForInterface.getTIMESTAMP()));
 82                 Label label9 = new Label(8, i, String.valueOf(logForInterface.getCODE()));
 83                 Label label10 = new Label(9, i, String.valueOf(logForInterface.getINFO()));
 84                 Label label11 = new Label(10, i, String.valueOf(logForInterface.getCOUNT()));
 85                 Label label12 = new Label(11, i, String.valueOf(logForInterface.getCOMMENTS()));
 86                 Label label13 = new Label(12, i, String.valueOf(logForInterface.getSERVICEOPT()));
 87                 Label label14 = new Label(13, i, String.valueOf(logForInterface.getERRORLOCATION()));
 88                 Label label15 = new Label(14, i, String.valueOf(logForInterface.getCREATEON()));
 89                 Label label16 = new Label(15, i, String.valueOf(logForInterface.getCREATEBY()));
 90                 Label label17 = new Label(16, i, String.valueOf(logForInterface.getSTAGE()));
 91                 Label label18 = new Label(17, i, String.valueOf(logForInterface.getPROTOCOL()));
 92                 Label label19 = new Label(18, i, String.valueOf(logForInterface.getPROVIDER()));
 93                 Label label20 = new Label(19, i, String.valueOf(logForInterface.getMESSAGEREQ()));
 94                 Label label21 = new Label(20, i, String.valueOf(logForInterface.getMESSAGERESP()));
 95                 sheet.addCell(label1);
 96                 sheet.addCell(label2);
 97                 sheet.addCell(label3);
 98                 sheet.addCell(label4);
 99                 sheet.addCell(label5);
100                 sheet.addCell(label6);
101                 sheet.addCell(label7);
102                 sheet.addCell(label8);
103                 sheet.addCell(label9);
104                 sheet.addCell(label10);
105                 sheet.addCell(label11);
106                 sheet.addCell(label12);
107                 sheet.addCell(label13);
108                 sheet.addCell(label14);
109                 sheet.addCell(label15);
110                 sheet.addCell(label16);
111                 sheet.addCell(label17);
112                 sheet.addCell(label18);
113                 sheet.addCell(label19);
114                 sheet.addCell(label20);
115                 sheet.addCell(label21);
116             }
117 
118             // 创建一个单元格对象，第一个为列，第二个为行，第三个为值
119             Label label = new Label(0, 2, "test");
120             // 将创建好的单元格放入选项卡中
121             //sheet.addCell(label);
122             // 写如目标路径
123             bWorkbook.write();
124 
125         } catch (Exception e) {
126             // TODO Auto-generated catch block
127             e.printStackTrace();
128         } finally {
129             try {
130                 bWorkbook.close();
131             } catch (Exception e) {
132                 // TODO Auto-generated catch block
133                 e.printStackTrace();
134             }
135         }
136 
137     }
138 }

 

实体类

  1 package com.lwl.entity;
  2 
  3 import javax.persistence.Column;
  4 import javax.persistence.Entity;
  5 import java.util.Date;
  6 
  7 /**
  8  * 根据事务ID/接口编号查询接口调用信息
  9  *
 10  * @author liuwenlong
 11  * @create 2021-10-28 10:27:14
 12  */
 13 
 14 @SuppressWarnings("all")
 15 @Entity 
 16 public class LogForInterface {
 17 
 18     private long ID;
 19     private String LOGUID;
 20     private String BIZTRANSACTIONID;
 21     private String SERVICENAME;
 22     private String SERVICETYPE;
 23     private String SERVICEPATH;
 24     private String CONSUMER;
 25     private String TIMESTAMP;
 26     private String CODE;
 27     private String INFO;
 28     private String COUNT;
 29     private String COMMENTS;
 30     private String SERVICEOPT;
 31     private String ERRORLOCATION;
 32     private String CREATEON;
 33     private String CREATEBY;
 34     private String STAGE;
 35     private String PROTOCOL;
 36     private String PROVIDER;
 37     private String MESSAGEREQ;
 38     private String MESSAGERESP;
 39 
 40     public LogForInterface() {
 41     }
 42 
 43     public LogForInterface(long ID, String LOGUID, String BIZTRANSACTIONID, String SERVICENAME, String SERVICETYPE, String SERVICEPATH, String CONSUMER, String TIMESTAMP, String CODE, String INFO, String COUNT, String COMMENTS, String SERVICEOPT, String ERRORLOCATION, String CREATEON, String CREATEBY, String STAGE, String PROTOCOL, String PROVIDER, String MESSAGEREQ, String MESSAGERESP) {
 44         this.ID = ID;
 45         this.LOGUID = LOGUID;
 46         this.BIZTRANSACTIONID = BIZTRANSACTIONID;
 47         this.SERVICENAME = SERVICENAME;
 48         this.SERVICETYPE = SERVICETYPE;
 49         this.SERVICEPATH = SERVICEPATH;
 50         this.CONSUMER = CONSUMER;
 51         this.TIMESTAMP = TIMESTAMP;
 52         this.CODE = CODE;
 53         this.INFO = INFO;
 54         this.COUNT = COUNT;
 55         this.COMMENTS = COMMENTS;
 56         this.SERVICEOPT = SERVICEOPT;
 57         this.ERRORLOCATION = ERRORLOCATION;
 58         this.CREATEON = CREATEON;
 59         this.CREATEBY = CREATEBY;
 60         this.STAGE = STAGE;
 61         this.PROTOCOL = PROTOCOL;
 62         this.PROVIDER = PROVIDER;
 63         this.MESSAGEREQ = MESSAGEREQ;
 64         this.MESSAGERESP = MESSAGERESP;
 65     }
 66 
 67     public long getID() {
 68         return ID;
 69     }
 70 
 71     public void setID(long ID) {
 72         this.ID = ID;
 73     }
 74 
 75     public String getLOGUID() {
 76         return LOGUID;
 77     }
 78 
 79     public void setLOGUID(String LOGUID) {
 80         this.LOGUID = LOGUID;
 81     }
 82 
 83     public String getBIZTRANSACTIONID() {
 84         return BIZTRANSACTIONID;
 85     }
 86 
 87     public void setBIZTRANSACTIONID(String BIZTRANSACTIONID) {
 88         this.BIZTRANSACTIONID = BIZTRANSACTIONID;
 89     }
 90 
 91     public String getSERVICENAME() {
 92         return SERVICENAME;
 93     }
 94 
 95     public void setSERVICENAME(String SERVICENAME) {
 96         this.SERVICENAME = SERVICENAME;
 97     }
 98 
 99     public String getSERVICETYPE() {
100         return SERVICETYPE;
101     }
102 
103     public void setSERVICETYPE(String SERVICETYPE) {
104         this.SERVICETYPE = SERVICETYPE;
105     }
106 
107     public String getSERVICEPATH() {
108         return SERVICEPATH;
109     }
110 
111     public void setSERVICEPATH(String SERVICEPATH) {
112         this.SERVICEPATH = SERVICEPATH;
113     }
114 
115     public String getCONSUMER() {
116         return CONSUMER;
117     }
118 
119     public void setCONSUMER(String CONSUMER) {
120         this.CONSUMER = CONSUMER;
121     }
122 
123     public String getTIMESTAMP() {
124         return TIMESTAMP;
125     }
126 
127     public void setTIMESTAMP(String TIMESTAMP) {
128         this.TIMESTAMP = TIMESTAMP;
129     }
130 
131     public String getCODE() {
132         return CODE;
133     }
134 
135     public void setCODE(String CODE) {
136         this.CODE = CODE;
137     }
138 
139     public String getINFO() {
140         return INFO;
141     }
142 
143     public void setINFO(String INFO) {
144         this.INFO = INFO;
145     }
146 
147     public String getCOUNT() {
148         return COUNT;
149     }
150 
151     public void setCOUNT(String COUNT) {
152         this.COUNT = COUNT;
153     }
154 
155     public String getCOMMENTS() {
156         return COMMENTS;
157     }
158 
159     public void setCOMMENTS(String COMMENTS) {
160         this.COMMENTS = COMMENTS;
161     }
162 
163     public String getSERVICEOPT() {
164         return SERVICEOPT;
165     }
166 
167     public void setSERVICEOPT(String SERVICEOPT) {
168         this.SERVICEOPT = SERVICEOPT;
169     }
170 
171     public String getERRORLOCATION() {
172         return ERRORLOCATION;
173     }
174 
175     public void setERRORLOCATION(String ERRORLOCATION) {
176         this.ERRORLOCATION = ERRORLOCATION;
177     }
178 
179     public String getCREATEON() {
180         return CREATEON;
181     }
182 
183     public void setCREATEON(String CREATEON) {
184         this.CREATEON = CREATEON;
185     }
186 
187     public String getCREATEBY() {
188         return CREATEBY;
189     }
190 
191     public void setCREATEBY(String CREATEBY) {
192         this.CREATEBY = CREATEBY;
193     }
194 
195     public String getSTAGE() {
196         return STAGE;
197     }
198 
199     public void setSTAGE(String STAGE) {
200         this.STAGE = STAGE;
201     }
202 
203     public String getPROTOCOL() {
204         return PROTOCOL;
205     }
206 
207     public void setPROTOCOL(String PROTOCOL) {
208         this.PROTOCOL = PROTOCOL;
209     }
210 
211     public String getPROVIDER() {
212         return PROVIDER;
213     }
214 
215     public void setPROVIDER(String PROVIDER) {
216         this.PROVIDER = PROVIDER;
217     }
218 
219     public String getMESSAGEREQ() {
220         return MESSAGEREQ;
221     }
222 
223     public void setMESSAGEREQ(String MESSAGEREQ) {
224         this.MESSAGEREQ = MESSAGEREQ;
225     }
226 
227     public String getMESSAGERESP() {
228         return MESSAGERESP;
229     }
230 
231     public void setMESSAGERESP(String MESSAGERESP) {
232         this.MESSAGERESP = MESSAGERESP;
233     }
234 
235     @Override
236     public String toString() {
237         return "LogForInterface{" +
238                 "ID=" + ID +
239                 ", LOGUID='" + LOGUID + '\'' +
240                 ", BIZTRANSACTIONID='" + BIZTRANSACTIONID + '\'' +
241                 ", SERVICENAME='" + SERVICENAME + '\'' +
242                 ", SERVICETYPE='" + SERVICETYPE + '\'' +
243                 ", SERVICEPATH='" + SERVICEPATH + '\'' +
244                 ", CONSUMER='" + CONSUMER + '\'' +
245                 ", TIMESTAMP='" + TIMESTAMP + '\'' +
246                 ", CODE='" + CODE + '\'' +
247                 ", INFO='" + INFO + '\'' +
248                 ", COUNT='" + COUNT + '\'' +
249                 ", COMMENTS='" + COMMENTS + '\'' +
250                 ", SERVICEOPT='" + SERVICEOPT + '\'' +
251                 ", ERRORLOCATION='" + ERRORLOCATION + '\'' +
252                 ", CREATEON='" + CREATEON + '\'' +
253                 ", CREATEBY='" + CREATEBY + '\'' +
254                 ", STAGE='" + STAGE + '\'' +
255                 ", PROTOCOL='" + PROTOCOL + '\'' +
256                 ", PROVIDER='" + PROVIDER + '\'' +
257                 ", MESSAGEREQ='" + MESSAGEREQ + '\'' +
258                 ", MESSAGERESP='" + MESSAGERESP + '\'' +
259                 '}';
260     }
261 }

 

调用

1         Excel excel = new Excel();
2         // 根据 接口编号 循环查询 该接口错误信息
3         for (int i = 0; i < result.size(); i++) {
4             ArrayList<LogForInterface> list1 = null;
5             list1 = i12CdomanMapper.selectLogForSingleInterface(result.get(i).getServicename(), startDate, endDate);
6             System.out.println("查询的接口编号：" + result.get(i).getServicename());
7             System.out.println(list1);
8             excel.excelOutFor12cZhouBaoForSingle(list1, result.get(i).getServicename());
9         }

 

查询结果：

 

 

结果执行后保存到Excel中

 

 

posted @ 2021-11-06 18:35  勤快的懒羊羊  阅读(738)  评论(0)  编辑  收藏  举报