[Groovy] 创建Excel,追加Excel

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
package ScriptLibrary
 
import java.awt.Color
import java.awt.GraphicsConfiguration.DefaultBufferCapabilities;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFColor
import org.apache.poi.xssf.usermodel.XSSFDataFormat
import org.apache.poi.xssf.usermodel.XSSFFont
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem
 
 
class WriteExcel {
 
    def testRunner
    def context
    def log
     
 
    WriteExcel(testRunner,context,log) {
        this.testRunner = testRunner
        this.context = context
        this.log = log
    }
 
    def parseFailMessage(String failMessage,def extraInfoMap){
        //获取Test Suite和Test Case的名字
        def currentStepIndex = context.currentStepIndex
        def testCaseName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getName()
        def testSuiteName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getParent().getName()
         
        //解析extraInfoMap
        def valueList = extraInfoMap.values()
         
        //根据;分隔每一条错误信息
        ArrayList failMessageList = new ArrayList()
        String[] failMessageArray = failMessage.split(";")
         
        //遍历每一条错误信息，解析出每一列的值，便于写入Excel
        for(int i=0;i<failMessageArray.size();i++){
            //获取第i条错误信息
            String oneFailMessage = failMessageArray[i]
             
            //从Expected:处进行分隔
            int expectedIndex = oneFailMessage.indexOf("Expected:")
            //从got:处进行分隔
            int gotIndex = oneFailMessage.indexOf("got:")
             
            //解析错误信息中打印的JsonPath
            String detailedJsonPath = oneFailMessage.substring(0,expectedIndex)
            //解析JsonPath中的叶子节点
            String lastLeafNode = detailedJsonPath.reverse().split("\\.")[0].reverse()
            //解析Expected的值
            String expectedValue = oneFailMessage.substring(expectedIndex,gotIndex).split(":")[1]
            //解析Actual的值
            String actualValue = oneFailMessage.substring(gotIndex).split(":")[1]
             
            //把需要输出的所有数据点拼接成一个list
            def errorMessage = [testSuiteName,testCaseName]
            errorMessage = errorMessage+valueList
            errorMessage = errorMessage+[lastLeafNode,expectedValue,actualValue,detailedJsonPath]
             
            //把每条单独的list拼接成一个总的list返回
            failMessageList.add(errorMessage)
        }
        return failMessageList
    }
     
    def createExcel(File createFile, def topRow) {
        String sheetName = "Automation Test";
        try {
             
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFCellStyle cellStyleString = setCellStyleString(workbook)
            XSSFCellStyle cellStyleTitle = setCellStyleTitle(workbook)
             
            int columnsNum = topRow.size();// calculate columns size by topRow
            XSSFSheet sheet = workbook.createSheet(sheetName);
 
            int rownum = 0;
            Row row = sheet.createRow(rownum++);
 
            int cellnum = 0;
            for (String cellString : topRow) {
 
                Cell cell = row.createCell(cellnum++);
                cell.setCellStyle(cellStyleString);
                if (rownum == 1) {
                    cell.setCellStyle(cellStyleTitle);
                }
                // remove DIV style
                if (cellString != null)
                    cellString = cellString.replaceAll('<(\\S*?)[^>]*>',"")
                cell.setCellValue(cellString);// insert value
            }
            sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
 
            FileOutputStream out = new FileOutputStream(createFile);
            workbook.write(out);
            out.flush();
            out.close();
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     
    //向Excel中追加数据
    def addExcel(String excelPath, ArrayList<String[]> failMessageList) throws IOException{
        int columnsNum = failMessageList[0].size();// calculate columns size first row
        FileInputStream fs = new FileInputStream(excelPath);//获取excel
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(0);//获取工作表
        XSSFRow row = sheet.getRow(0);//获取第一行(即:字段列头,便于赋值)
        int lastRowNum = sheet.getLastRowNum()
        FileOutputStream out = new FileOutputStream(excelPath);//向excel中添加数据
        for (int i = 0; i < failMessageList.size(); i++) {
            row = sheet.createRow(++lastRowNum);//在现有行号后追加数据
            String[] addOneRowData = failMessageList[i];
            for(int j=0;j<addOneRowData.size();j++){
                String str = addOneRowData[j];
                row.createCell(j).setCellValue(str);//设置单元格的数据
            }
        }
        sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
        wb.write(out);
        out.flush();
        out.close();
    }
 
    def getExcelName( ) {
        def currentStepIndex = context.currentStepIndex
        def testCaseName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getName()
        def testSuiteName = testRunner.testCase.getTestStepAt(currentStepIndex).getParent().getParent().getName()
        def excelName = testSuiteName + " _ " + testCaseName
        excelName = excelName.replace(",","").replace(":","=").replace("/","").replace("*","")
        return excelName
    }
     
    def fillInFailMessage(File createFile, ArrayList<String[]> failMessageList, String[] topRow) {
        String sheetName = "Automation Test";
        try {
             
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFCellStyle cellStyleString = setCellStyleString(workbook)
            XSSFCellStyle cellStyleTitle = setCellStyleTitle(workbook)
            XSSFCellStyle cellStyleFail = setCellStyleFail(workbook)
            XSSFCellStyle cellStyleNull = setCellStyleNull(workbook)
 
            failMessageList.add(0, topRow);
            int columnsNum = topRow.length;// calculate columns size by topRow
            XSSFSheet sheet = workbook.createSheet(sheetName);
 
            int rownum = 0;
            for (int i = 0; i < failMessageList.size(); i++) {
 
                String[] rowStrings = failMessageList.get(i);
                Row row = sheet.createRow(rownum++);
 
                int cellnum = 0;
                for (String cellString : rowStrings) {
 
                    Cell cell = row.createCell(cellnum++);
                    cell.setCellStyle(cellStyleString);
                    if (rownum == 1) {
                        cell.setCellStyle(cellStyleTitle);
 
                    } else if (cellString != null && cellString.contains("%") && cellnum == columnsNum) {
                        Number number = NumberFormat.getInstance().parse(cellString);
 
                        // If the actual deviation > 10%, change the cell color to red in the excel
                        if (number.doubleValue() > 10) {
                            cell.setCellStyle(cellStyleFail);
                        }
                    } else if ("null".equals(cellString) || "not null".equals(cellString)) {
                        // If cell value is "null" or "not null" change color to yellow in the excel
                        cell.setCellStyle(cellStyleNull);
                    }
                    // remove DIV style
                    if (cellString != null)
                        cellString = cellString.replaceAll('<(\\S*?)[^>]*>',"")
                    cell.setCellValue(cellString);// insert value
                }
            }
            sheet = setSheetStyle(sheet, columnsNum -1);// setting sheet style
 
            FileOutputStream out = new FileOutputStream(createFile);
            workbook.write(out);
            out.flush();
            out.close();
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     
    def XSSFSheet setSheetStyle(XSSFSheet sheet, int columnsNum) {
     
        sheet.createFreezePane(0, 1, 0, 1);
        String columnRange = "A1:" + (char) (65 + columnsNum) + "1";
        sheet.setAutoFilter(CellRangeAddress.valueOf(columnRange));
 
        for (int i = 0; i <= columnsNum; i++)
            sheet.autoSizeColumn(i);
 
        return sheet;
    }
     
    def setCellStyleString(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        return cellStyle;
    }
     
    def setCellStyleTitle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(131, 191, 90)));
        return cellStyle;
    }
     
    def setCellStyleFail(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 0, 0)));
        return cellStyle;
    }
     
    def setCellStyleNull(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont fontStyle = workbook.createFont();
        fontStyle.setColor(new XSSFColor(new Color(255, 0, 0)));
        fontStyle.setBold(true);
        cellStyle.setFont(fontStyle);
        return cellStyle;
    }
}