1.1maven依赖
开发环境:Idea
创建Maven项目 在pom中写入依赖:
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.3.6</version>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpcore</artifactId>
<version>4.3.3</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>5.6.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.6.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-params</artifactId>
<version>5.6.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testng</groupId>
<artifactId>testng</artifactId>
<version>6.10</version>
</dependency>
1.2 工具类
public class FileUtil {
/**
* 获取文件名
* @param filePath
* @return
*/
public static String getFileName(String filePath) {
File file = new File(filePath);
String fileName = file.getName();
return fileName;
}
/**
* 创建文件
* @param filePath 路径
* @param titleStr 标题
*/
public static void creatExcel(String filePath,String titleStr) {
String path = filePath.substring(0,filePath.lastIndexOf("/"));
System.out.println("文件夹为:"+path);
File dir = new File(path);
if (dir.exists()) {
if (dir.isDirectory()) {
System.out.println("dir exists");
}else {
System.out.println("the same name file exists, can not create dir");
}
}else {
System.out.println("dir not exists, create it ...");
dir.mkdir();
}
File file = new File(filePath);
if(!file.exists()) {
//创建excel工作簿
HSSFWorkbook workbook=new HSSFWorkbook();
//创建工作表sheet
HSSFSheet sheet=workbook.createSheet("result");
//创建第一行
HSSFRow row=sheet.createRow(0);
//创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
Cell cell = null;
//插入第一行数据的表头
String[] title = titleStr.split(",");
for(int i=0;i<title.length;i++){
cell=row.createCell(i);
cell.setCellValue(title[i]);
}
FileOutputStream stream=null;
try {
//将excel写入
stream = new FileOutputStream(filePath);
workbook.write(stream);
stream.flush();
stream.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
if(stream!=null){
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 更新文件
* @param fileName
* @param sheetName
* @param colName
* @param row
* @param str
* @throws Exception
*/
public static void ExcelUpdateByColname(String fileName,String sheetName,String colName,String row,String str) throws Exception {
int tranrow = Integer.parseInt(row);
InputStream is = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheet(sheetName);
Row titleRow = sheet.getRow(0);
//sheet页标题栏处理
Map cellIndexAndCellNameMapping = new HashMap();
int lastCellNum = titleRow.getLastCellNum();
for(int i = 0;i<lastCellNum;i++){
//拿到cell列对象
Cell cell = titleRow.getCell(i);
cell.setCellType(CellType.STRING);
//获取单元格内容
String cellValue = cell.getStringCellValue();
cellIndexAndCellNameMapping.put(cellValue,i);
}
int trancol = (int) cellIndexAndCellNameMapping.get(colName);
Row sheetRow = sheet.getRow(tranrow);
if(sheetRow == null){
sheetRow = sheet.createRow(tranrow);
}
Cell sheetCell = sheetRow.getCell(trancol);
if(sheetCell == null) {
sheetCell = sheetRow.createCell(trancol);
}
sheetCell.setCellValue(str);
FileOutputStream os = new FileOutputStream(fileName);
wb.write(os);
is.close();
os.close();
}
/**
* 行列号更新
* @param fileName
* @param sheetName
* @param col
* @param row
* @param str
* @throws Exception
*/
public void ExcelUpdate(String fileName,String sheetName,String col,String row,String str) throws Exception {
int tranCol = Integer.parseInt(col);
int tranRow = Integer.parseInt(row);
InputStream is = new FileInputStream(fileName);
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheet(sheetName);
Row sheetRow = sheet.getRow(tranRow);
if(sheetRow == null){
sheetRow = sheet.createRow(tranRow);
}
Cell sheetCell = sheetRow.getCell(tranCol);
if(sheetCell == null) {
sheetCell = sheetRow.createCell(tranCol);
}
sheetCell.setCellValue(str);
FileOutputStream os = new FileOutputStream(fileName);
wb.write(os);
is.close();
os.close();
}
/**
* 将字符换写进文件里
* @param json
* @param fileName
*/
public static void writeIntoText(String json, String fileName) {
File dirfile = new File(fileName);
BufferedWriter writer=null;
if(dirfile.isDirectory()){
try {
throw new FileNotFoundException();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}else {
if (!dirfile.exists()) {
dirfile.mkdirs();
}
}
try {
writer = new BufferedWriter(new FileWriter(dirfile));
writer.write(JsonUtil.getJSONString(JsonUtil.StringTransForjSON(json)));
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 读取文件 json字符串
* @param fileName
*/
public static String readFile(String fileName){
File file =new File(fileName);
StringBuffer stringBuffer =new StringBuffer();
if(!file.exists()){
try {
throw new FileNotFoundException("文件查找不到");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
try {
BufferedReader reader =new BufferedReader(new FileReader(file));
String s;
while ((s =reader.readLine())!=null){
stringBuffer.append(s);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return stringBuffer.toString();
}
}
public class JsonUtil {
//静态
public static ObjectMapper objectMapper = new ObjectMapper();
public static Logger logger=Logger.getLogger(JsonUtil.class);
/**
* 将map转json字符串
* @Author 鹿少年 2020年9月6日 下午9:37:52
* @param map
* @return
*/
public static String MapToJsonString(Map<String, Object> map) {
String jsonStr = new String();
try {
jsonStr = objectMapper.writeValueAsString(map);
} catch (JsonProcessingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return jsonStr;
}
/**
* 将对象转换成json字符串。
* @Author 鹿少年 2020年9月6日 下午9:48:49
* @param object
* @return
*/
public static String ObjectToJson(Object object) {
String result = null;
try {
result = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(object);
} catch (JsonProcessingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
/**
* json结果集转对象
* @param jsonData
* @param beanType
* @param <T>
* @return
*/
public static <T> T jsonToPojo(String jsonData, Class<T> beanType) {
try {
T t = objectMapper.readValue(jsonData, beanType);
return t;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* json转map
* @Author 鹿少年 2020年9月18日 上午9:12:20
* @param jsonData
* @return
*/
public static Map<String, String> JSONStringToMap(String jsonData) {
Map<String, String> map = null;
try {
map = objectMapper.readValue(jsonData, Map.class);
} catch (JsonParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (JsonMappingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return map;
}
/**
* 将字符串数组转换为object数组
* @param <T>
* @Author 鹿少年 2020年9月18日 上午9:29:09
* @param jsonData
* @param beanType
* @return
*/
@SuppressWarnings("unchecked")
public static <T> T[] JsonArrayStringToObajectArray(String jsonData, Class<T[]> beanType) {
T[] array = null;
try {
array = (T[]) objectMapper.readValue(jsonData, beanType);
} catch (IOException e) {
e.printStackTrace();
}
return array;
}
/**
* 将json数据转换成pojo对象list
* @Author 鹿少年 2020年9月18日 下午1:46:45
* @param jsonData
* @param beanType
* @return
*/
public static <T> List<T> jsonArrayToList(String jsonData, Class<T[]> beanType) {
try {
return Arrays.asList(objectMapper.readValue(jsonData, beanType));
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* jsonarray转list,不可以转map
* @Author 鹿少年 2020年9月18日 下午1:11:20
* @param jsonData
* @param T
* @return
*/
public static <T> List<T> JsonArrayToList(String jsonData, TypeReference T) {
try {
return (List<T>) objectMapper.readValue(jsonData, T);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* fastjson转换
*/
/**
* json字符串转JSONObject
* @param jsonString
* @return
*/
public static JSONObject StringTransForjSON(String jsonString) {
JSONObject jsonObject = JSONObject.parseObject(jsonString);
return jsonObject;
}
/**
* list字符串转JSON字符串
*
* @param lists
* @return
*/
public static String listTransforTOJSON(List<Object> lists) {
String str = JSON.toJSON(lists).toString();
return str;
}
/**
* map转JSON
*
* @param map
* @return
*/
public static JSONObject mapTransTOJSON(Map<String, Object> map) {
JSONObject json = new JSONObject(map);
return json;
}
/**
* json字符串转Object实体类
* @param str
* @return
*/
public static <T> T JAVAObjectTransToJSON(String str, Class<T> beanType) {
T t=JSONObject.parseObject(str,beanType);
return t;
}
/**
* json字符串转 Object实体List数组
* @param str
* @return
*/
public static List<Object> getListFromJSON(String str){
List<Object> list1 = JSONObject.parseObject(str, List.class);
return list1;
}
/**
* JSon字符串转Map
* @param str
* @return
*/
public static Map<String,Object> getMapFromJSON(String jsonmap,String str){
Map<String,Object> map1 = JSONObject.parseObject(jsonmap, Map.class);
Iterator iterator = map1.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry entry= (Map.Entry) iterator.next();
System.out.println("key :"+entry.getKey()+" value: " + entry.getValue());
}
return map1;
}
/**
* 得到格式化的jSON字符串
* @param jsonObject
* @return
*/
public static String getJSONString(JSONObject jsonObject){
return JSON.toJSONString(jsonObject, SerializerFeature.PrettyFormat);
}
/**
* string转格式化string
* @param jsonObject
* @return
*/
public static String getJSONString(Object jsonObject){
return JSON.toJSONString(jsonObject, SerializerFeature.PrettyFormat);
}
}
public class ExcelUtil {
public static Logger logger=Logger.getLogger(ExcelUtil.class);
/**
* 读取Excel文件
* @param filePath
* @param sheetName
* @return
* @throws EncryptedDocumentException
* @throws IOException
*/
public static List<Map<String ,String>> readExcel(String filePath, String sheetName) throws EncryptedDocumentException, IOException {
List<Map<String, String>> list = new LinkedList<Map<String,String>>();
//列名称
List<String>listname=new LinkedList<String>();
File file=new File(filePath);
if(!file.exists()) {
throw new FileNotFoundException("文件不存在");
}
InputStream ins=new FileInputStream(new File(filePath));
Workbook workbook= WorkbookFactory.create(ins);
Sheet sheet=workbook.getSheet(sheetName);
//拿到行号
int rownum=sheet.getLastRowNum();
Row row=sheet.getRow(0);
int colnum=row.getLastCellNum();
logger.info("总列数"+colnum);
for(int i=0;i<colnum;i++) {
Cell cell=row.getCell(i);
listname.add(i, getCellFormatValue(cell));
}
logger.info("Excel列标题名称"+listname.toString());
for(int i=1;i<=rownum;i++) {
Row lrow=sheet.getRow(i);
Map<String,String>map=new LinkedHashMap<String, String>();
for(int j=0;j<colnum;j++) {
Cell ccell=lrow.getCell(j);
map.put(listname.get(j), getCellFormatValue(ccell));
}
list.add(map);
}
return list;
}
/**
* 取得列对于值
* 得到
* @param cell
* @return
*/
public static String getCellFormatValue(Cell cell) {
if(cell!=null&&!StringUtils.isBlank(cell.toString())) {
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
return "";
}
/**
* 输出Excel表格数据
* @param datas
*/
public static void printExcelData( List<Map<String ,String>>datas) {
for(int i=0;i<datas.size();i++) {
Map<String ,String>map=new LinkedHashMap<String, String>();
map=datas.get(i);
Iterator<?> it=map.entrySet().iterator();
while(it.hasNext()) {
@SuppressWarnings("rawtypes")
Map.Entry entry = (Map.Entry)it.next();
String key=String.valueOf(entry.getKey());
String value=String.valueOf(entry.getValue());
logger.info("key:"+key+ " value:"+value);
}
}
}
/**
* 拿到表格标题
* @param fileName
* @param sheetName
* @return
* @throws EncryptedDocumentException
* @throws IOException
*/
public static String[] getTitle(String fileName,String sheetName) throws EncryptedDocumentException, IOException {
InputStream ins=new FileInputStream(new File(fileName));
Workbook workbook=WorkbookFactory.create(ins);
Sheet sheet=workbook.getSheet(sheetName);
Row titile=sheet.getRow(0);
int lastColum=titile.getLastCellNum();
String[]titleList=new String[lastColum];
for(int i=0;i<lastColum;i++) {
Cell cell=titile.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
String value=cell.getStringCellValue();
value=value.substring(0,value.indexOf("("));
titleList[i]=value;
}
return titleList;
}
/**
* List转Object 便于参数化
* @param datas
* @return
*/
public static Object[][] getExcelData(List<Map<String ,String>>datas){
int length=datas.size();
int len=datas.get(0).size();
logger.info("length:"+length+"len:"+len);
Object [][] ob=new Object[length][len];
for(int i=0;i<length;i++) {
int j=0;
Map<String ,String>map=datas.get(i);
for(String key:map.keySet()) {
ob[i][j++]=map.get(key);
}
}
return ob;
}
/**
* 得到sheet页
* @param fileName
* @param sheetName
* @return
*/
public static Sheet getSheet(String fileName, String sheetName){
InputStream ins= null;
Sheet sheet=null;
try {
ins = new FileInputStream(new File(fileName));
Workbook workbook=WorkbookFactory.create(ins);
sheet =workbook.getSheet(sheetName);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return sheet;
}
/**
* 根据行号 列标题读取指定Excel内容
* @param fileName
* @param sheetName sheet页
* @param rowloum 行名称
* @param coloum 列名称
* @return
* @throws EncryptedDocumentException
* @throws IOException
*/
public static Object[][] ReadExcelTo(String fileName,String sheetName,String rowloum[],String coloum[]) throws EncryptedDocumentException, IOException {
Object[][] ob=new Object[rowloum.length][coloum.length];
String title[]=getTitle(fileName, sheetName);
Sheet sheet=getSheet(fileName,sheetName);
Row titleRow=sheet.getRow(0);
//sheet页标题栏处理
Map<String,String> cellIndexAndCellNameMapping = new HashMap();
int lastCellNum = titleRow.getLastCellNum();
for(int i = 0;i<lastCellNum;i++){
//拿到cell列对象
Cell cell = titleRow.getCell(i);
cell.setCellType(CellType.STRING);
//获取单元格内容
String cellValue = cell.getStringCellValue();
cellValue=cellValue.substring(0,cellValue.indexOf("("));
cellIndexAndCellNameMapping.put(cellValue,String.valueOf(i));
}
for(int i=0;i<rowloum.length;i++) {
int tranRowindex=Integer.valueOf(rowloum[i]);//行号减1
Row row=sheet.getRow(tranRowindex);
for(int j=0;j<coloum.length;j++) {
String titlename=coloum[j];
logger.info("titlename:"+titlename);
logger.info("colNum:"+cellIndexAndCellNameMapping.get(titlename));
int ColIndex= Integer.parseInt(cellIndexAndCellNameMapping.get(titlename));
logger.info("ColIndex"+ColIndex);
Cell cell=row.getCell(ColIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
ob[i][j]=cell.getStringCellValue();
}
}
return ob;
}
}