tools.poi.ExcelDataSource Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of java-autotest-tool Show documentation
Show all versions of java-autotest-tool Show documentation
This is an integration of autotest tools
package tools.poi;
/**
* Created by zhengyu06 on 2017/9/12
*/
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Reporter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;
public class ExcelDataSource {
private Workbook excelWBook = null;
private Sheet excelWSheet = null;
private Row excelWRow = null;
private Cell excelWCell = null;
private String path = "";
public ExcelDataSource(String path, String sheetName){
this.path = path;
boolean isE2007 = false; //判断是否是excel2007格式
try(FileInputStream excelFile = new FileInputStream(path)){ //path includes the filename
if(path.endsWith("xlsx"))
isE2007 = true;
else if (!path.endsWith("xls"))
Reporter.log("文件地址指向的文件并非Excel文件。");
//根据文件格式(2003或者2007)来初始化
if(isE2007) {
this.excelWBook = new XSSFWorkbook(excelFile);
Reporter.log("XLSX Workbook has been created.");
} else {
this.excelWBook = new HSSFWorkbook(excelFile);
Reporter.log("XLS Workbook has been created.");
}
this.excelWSheet = excelWBook.getSheet(sheetName);
Reporter.log(sheetName + " Sheet has been created.");
}catch(Exception e){
e.printStackTrace();
}
}
public void setForceFormulaRecalculation(boolean flag){
this.excelWBook.setForceFormulaRecalculation(flag);
}
public ExcelDataSource(int version){//此构造函数是新构建一个Excel
if(version < 2007)
this.excelWBook = new HSSFWorkbook();
else
this.excelWBook = new XSSFWorkbook();
}
public int excelGetRows(){
int rows = 0;
try{
rows = excelWSheet.getPhysicalNumberOfRows();
// Reporter.log(excelWSheet.getLastRowNum()+" "+excelWSheet.getFirstRowNum());
}catch(Exception e){
e.printStackTrace();
}
return rows;
}
public int excelGetCols(int rowNum){
int cols = 0;
try{
cols = excelWSheet.getRow(rowNum).getPhysicalNumberOfCells();
}catch(Exception e){
e.printStackTrace();
}
return cols;
}
public Cell excelGetCell(int rowNum, int colNum){
try{
excelWCell = excelWSheet.getRow(rowNum).getCell(colNum);//这个是第几行第几列.
}catch(Exception e){
e.printStackTrace();
}
return excelWCell;
}
public String getCellData(int rowNum, int colNum){
String cellData = "";
try{
excelWCell = excelWSheet.getRow(rowNum).getCell(colNum);
if(excelWCell!=null){
if(excelWCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(excelWCell))
cellData = HSSFDateUtil.getJavaDate(excelWCell.getNumericCellValue()).toString();
else
cellData = Double.toString(excelWCell.getNumericCellValue());//getNumericCellValue返回double
}else
cellData = excelWCell.getStringCellValue()==null?"":excelWCell.getStringCellValue();
}
}catch(Exception e){
e.printStackTrace();
}
return cellData;
}
public String getNumericCellData(int rowNum, int colNum){
String cellData = "";
try{
excelWCell = excelWSheet.getRow(rowNum).getCell(colNum);
cellData = Double.toString(excelWCell.getNumericCellValue());
cellData = cellData==null?"":cellData;
}catch (Exception e){
e.printStackTrace();
}
return cellData;
}
public void setCellValue(int rowNum, int colNum, String cellValue){
excelWCell = excelWSheet.getRow(rowNum).getCell(colNum);
excelWCell.setCellValue(cellValue);
}
public void createSheet(String sheetName){//创建一个叫做sheetName的Sheet
try{
if(excelWBook!=null){
excelWSheet = (Sheet) excelWBook.createSheet(sheetName);
}
}catch(Exception e){
e.printStackTrace();
}
}
public void createOneRow(int rowNum){
excelWRow = excelWSheet.createRow(rowNum);//创建第几行
}
@SuppressWarnings("deprecation")
public void createOneCell(int colNum){
excelWCell = excelWRow.createCell(colNum,Cell.CELL_TYPE_STRING);//创建第几列的一个Cell
}
public void createOneCell(int rowNum, int colNum){//在第几行的第几列上创建一个Cell
excelWRow = excelWSheet.getRow(rowNum);
excelWCell = excelWRow.createCell(colNum,Cell.CELL_TYPE_STRING);
}
public void createRowCellValue(int rowNum, int colNum, String cellValue){
excelWRow = excelWSheet.createRow(rowNum);//创建第几行
excelWCell = excelWRow.createCell(colNum,Cell.CELL_TYPE_STRING);
excelWCell.setCellValue(cellValue);
}
public void createCellValue(int rowNum, int colNum, String cellValue){
excelWRow = excelWSheet.getRow(rowNum);
excelWCell = excelWRow.createCell(colNum,Cell.CELL_TYPE_STRING);
excelWCell.setCellValue(cellValue);
}
public void saveExcel(String filePath){//此filePath是到文件名截止
File excelFile = new File(filePath);
try(FileOutputStream fos = new FileOutputStream(excelFile)){
excelWBook.write(fos);
Reporter.log("Excel("+filePath+") Creation and Save have been successful!", true);
}catch(Exception e){
e.printStackTrace();
Reporter.log("Excel Creation got failure!", true);
}
}
public void changeJSONObjectToExcel(JSONObject jsonObject, String excelFilePath) throws ExecutionException, InterruptedException {
/* 此方法只支持三层，其中第二层为JSONArray */
ThreadPoolExecutor executorService = (ThreadPoolExecutor) Executors.newFixedThreadPool(jsonObject.size());
/*ThreadPoolExecutor 为 ExecutorService的实现类。*/
List> futureList = new ArrayList<>();
for (Map.Entry entry : jsonObject.entrySet()){
Sheet currSheet = this.excelWBook.createSheet(entry.getKey());
JSONArray currJSONArray = (JSONArray) entry.getValue();
if (!currJSONArray.isEmpty()){
JSONObject titleJSONObject = (JSONObject) currJSONArray.get(0);
Future future = executorService.submit(new Callable() {
@Override
public Void call() {
int currRowNum = 0;
int currColumnNum = 0;
Row row = currSheet.createRow(currRowNum);//创建第0行。
List titleList = new ArrayList<>();
for (Map.Entry titleEntry : titleJSONObject.entrySet()){
String currTitle = titleEntry.getKey();
row.createCell(currColumnNum, CellType.STRING).setCellValue(currTitle);
titleList.add(currTitle);
currColumnNum++;
}
String currVal = "";
for (Object currObject : currJSONArray){
JSONObject currJSONObject = (JSONObject) currObject;
currRowNum++;
Row currRow = currSheet.createRow(currRowNum);
for (int i = 0; i< titleList.size(); i++){
currVal = currJSONObject.get(titleList.get(i)).toString();
if (currVal.length() > 32767){
currVal = currVal.substring(0, 32767);
}
currRow.createCell(i, CellType.STRING).setCellValue(currVal);
}
}
return null;
}
});
futureList.add(future);
}
}
executorService.shutdown();//禁止提交新任务。
for (int i=0; i entry : jsonObject.entrySet()){
Sheet currSheet = this.excelWBook.createSheet(entry.getKey());
JSONArray currJSONArray = (JSONArray) JSONArray.toJSON(entry.getValue());
if (!currJSONArray.isEmpty()){
JSONObject titleJSONObject = (JSONObject) currJSONArray.get(0);
int currRowNum = 0;
int currColumnNum = 0;
Row row = currSheet.createRow(currRowNum);//创建第0行。
List titleList = new ArrayList<>();
for (Map.Entry titleEntry : titleJSONObject.entrySet()){
String currTitle = titleEntry.getKey();
row.createCell(currColumnNum, CellType.STRING).setCellValue(currTitle);
titleList.add(currTitle);
currColumnNum++;
}
String currVal = "";
for (Object currObject : currJSONArray){
JSONObject currJSONObject = (JSONObject) currObject;
currRowNum++;
Row currRow = currSheet.createRow(currRowNum);
for (int i = 0; i< titleList.size(); i++){
/*if (titleList == null){
System.out.println("titleList is null.");
}else{
System.out.println("titleList = "+titleList);
}
if (currJSONObject == null){
System.out.println("currJSONObject is null.");
}else {
System.out.println("currJSONObject = "+currJSONObject);
}*/
//上方只为Debug
/*
* titleList = [系统名称, 数量, 屏蔽推送原因]
* currJSONObject = {"数量":1,"屏蔽推送原因":"存在 已ACK 同类告警"}
* 有这种情况，所以下方必须判是否obj为空。
* */
Object obj = currJSONObject.get(titleList.get(i));
currVal = obj == null ? "" : obj.toString();
if (currVal.length() > 32767){
currVal = currVal.substring(0, 32767);
}
currRow.createCell(i, CellType.STRING).setCellValue(currVal);
}
}
}
}
this.saveExcel(excelFilePath);
}
public Font setBoldFont(){
Font font = excelWBook.createFont();//创建字体
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("JasonFont");
return font;
}
public Font setThinFont(){
Font font = excelWBook.createFont();//创建字体
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
font.setFontName("JasonFont");
return font;
}
public void setBoldStyle(){
CellStyle cellStyle = excelWBook.createCellStyle();//创建格式
//设置居中
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置边框
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setFont(setBoldFont());
excelWCell.setCellStyle(cellStyle);
}
public void setBoldStyleWithoutBorder(){
CellStyle cellStyle = excelWBook.createCellStyle();//创建格式
//设置居中
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(setBoldFont());
excelWCell.setCellStyle(cellStyle);
}
public void setGreyStyleWithoutBorder(){
CellStyle cellStyle = excelWBook.createCellStyle();//创建格式
//设置居中
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(setBoldFont());
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//ForegroundColor是背景的前景色
excelWCell.setCellStyle(cellStyle);
}
public void setThinStyle(){
CellStyle cellStyle = excelWBook.createCellStyle();//创建格式
//设置居中
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置边框
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setFont(setThinFont());
excelWCell.setCellStyle(cellStyle);
}
public void setFaultStyle(){
CellStyle cellStyle = excelWBook.createCellStyle();//创建格式
//设置居中
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
//设置边框
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setFont(setThinFont());
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());//ForegroundColor是背景的前景色
excelWCell.setCellStyle(cellStyle);
}
public void mergeCell(int firstRow, int lastRow, int firstCol, int lastCol){
CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
this.excelWSheet.addMergedRegion(cra);
}
public void autoOpen(String path){
int i = 0;
while(i<10){
Reporter.log("Excel要自动打开啦~~");
i++;
}
try{
Runtime.getRuntime().exec("cmd /c start "+path);
}catch(IOException e){
e.printStackTrace();
}
}
public void deleteExcelFile(String path){
File file = new File(path);
if (file.exists()){
file.delete();
}else
Reporter.log(path+" 文件不存在。");
}
public String getCalculatedCellData(int rowNum, int colNum){
String value = "";
FormulaEvaluator evaluator = null;
if (path.endsWith("xlsx"))
evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) this.excelWBook);
else if (path.endsWith("xls"))
evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) this.excelWBook);
excelWCell = excelWSheet.getRow(rowNum).getCell(colNum);
if (evaluator != null){
CellValue cellValue = evaluator.evaluate(excelWCell);//用FormulaEvaluator类的evaluate(Workbook)方法获取计算字段的值。
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Reporter.log("Boolean Value is: "+cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
Double doubleVal = cellValue.getNumberValue();
Reporter.log("Double Value is: "+doubleVal);
value = Double.toString(doubleVal);
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue();
Reporter.log(value);
break;
case Cell.CELL_TYPE_BLANK:
Reporter.log("Blank Value");
break;
case Cell.CELL_TYPE_ERROR:
Reporter.log("Error");
break;
// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
break;
default:
Reporter.log("根据CellValue的类型未找到匹配。");
}
}else{
Reporter.log("FormulaEvaluator类的对象evaluator为null。");
}
return value;
}
public int setSheetHeaderOrFooter(int firstRow, int lastRow, int firstCol, int lastCol, String headerInfo){
this.createRowCellValue(firstRow, firstCol, headerInfo);
this.mergeCell(firstRow, lastRow, firstCol, lastCol);
this.setGreyStyleWithoutBorder();
return lastRow+1;
}
public void tearDown(){
if (this.excelWBook != null){
try {
this.excelWBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}