excel读取demo

Markland_l

于 2022-06-15 15:26:06 发布

阅读量152

点赞数

分类专栏：项目实践文章标签： java apache 开发语言

本文链接：https://blog.csdn.net/Markland_l/article/details/125297939

版权

项目实践专栏收录该内容

6 篇文章 0 订阅

订阅专栏

准备

需要先将excel文件保存为xlsx格式

导入包

<!-- poi  excel-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>
<!-- poi  excel-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

demo1

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Testpoi {

	public static void main(String[] args) {
		Workbook wb =null;
		Sheet sheet = null;
		Row row = null;
		List<Map<String,String>> list = null;
		String cellData = null;
		String filePath = "D:\\test.xlsx";
		String columns[] = {"@timestamp","message","level"};
		wb = readExcel(filePath);
		if(wb != null){
			//用来存放表中数据
			list = new ArrayList<Map<String,String>>();
			//获取第一个sheet
			sheet = wb.getSheetAt(0);
			//获取最大行数
			int rownum = sheet.getPhysicalNumberOfRows();
			//获取第一行
			row = sheet.getRow(0);
			//获取最大列数
			int colnum = row.getPhysicalNumberOfCells();
			for (int i = 1; i<rownum; i++) {
				Map<String,String> map = new LinkedHashMap<String,String>();
				row = sheet.getRow(i);
				if(row !=null){
					for (int j=0;j<colnum;j++){
						cellData = (String) getCellFormatValue(row.getCell(j));
						map.put(columns[j], cellData);
					}
				}else{
					break;
				}
				list.add(map);
			}
		}
		//遍历解析出来的list
		HashSet<String> set = new HashSet<>();
		for (Map<String,String> map : list) {
			int i = 0;
			for (Entry<String,String> entry : map.entrySet()) {
				i++;
				if (i==2) {
					String[] split = entry.getValue().split("/inner/pay?");
					String[] split1 = split[1].split("&");
					String[] split2 = split1[2].split("=");
					set.add(split2[1]);
				}
			}
		}
		for (String s : set) {
			System.out.println(s);
		}
	}
	//读取excel
	public static Workbook readExcel(String filePath){
		Workbook wb = null;
		if(filePath==null){
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			if(".xls".equals(extString)){
				return wb = new HSSFWorkbook(is);
			}else if(".xlsx".equals(extString)){
				return wb = new XSSFWorkbook(is);
			}else{
				return wb = null;
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}
	public static Object getCellFormatValue(Cell cell){
		Object cellValue = null;
		if(cell!=null){
			//判断cell类型
			switch(cell.getCellType()){
				case Cell.CELL_TYPE_NUMERIC:{
					cellValue = String.valueOf(cell.getNumericCellValue());
					break;
				}
				case Cell.CELL_TYPE_FORMULA:{
					//判断cell是否为日期格式
					if(DateUtil.isCellDateFormatted(cell)){
						//转换为日期格式YYYY-mm-dd
						cellValue = cell.getDateCellValue();
					}else{
						//数字
						cellValue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
				case Cell.CELL_TYPE_STRING:{
					cellValue = cell.getRichStringCellValue().getString();
					break;
				}
				default:
					cellValue = "";
			}
		}else{
			cellValue = "";
		}
		return cellValue;
	}

}

demo2

import com.al.risk.collection.common.util.JsonUtil;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.core.type.TypeReference;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;

/**
 * @Author: liusiping
 * @DateTime: 2022/6/15 10:46
 **/
public class Testpoi {
	public static void main(String[] args) throws Exception {
		Workbook wb =null;
		Sheet sheet = null;
		Row row = null;
		List<Map<String,String>> list = null;
		String cellData = null;
		// 指定文件
//		String filePath = "D:\\test.xlsx";
		// 指定文件目录
		File dir = new File("D:\\pdl");
		String[] children = dir.list();
		String columns[] = {"@timestamp","message","level"};
		for (int q = 0; q < children.length; q++) {
			if (children[q].contains("~")) {
				break;
			}
			wb = readExcel("D:\\pdl\\" + children[q]);
			if(wb != null){
				//用来存放表中数据
				list = new ArrayList<Map<String,String>>();
				//获取第一个sheet
				sheet = wb.getSheetAt(0);
				//获取最大行数
				int rownum = sheet.getPhysicalNumberOfRows();
				//获取第一行
				row = sheet.getRow(0);
				//获取最大列数
				int colnum = row.getPhysicalNumberOfCells();
				for (int i = 0; i<rownum; i++) {
					Map<String,String> map = new LinkedHashMap<String,String>();
					row = sheet.getRow(i);
					if(row !=null){
						for (int j=0;j<colnum;j++){
							cellData = (String) getCellFormatValue(row.getCell(j));
							String[] split = cellData.split("\"billId\":");
							if (split.length <= 1) {
								break;
							}
							String[] split1 = split[1].split(",");
							if (Objects.equals(split1[0], "332117920031186944")) {
								System.out.println(children[q]);
								System.out.println(i+1);
								System.out.println(cellData);
							}
						}
					}else{
						break;
					}
					list.add(map);
				}
			}
			System.out.println(children[q] +" 文件执行结束");
		}
		System.out.println("运行完毕");
	}


	//读取excel
	public static Workbook readExcel(String filePath){
		Workbook wb = null;
		if(filePath==null){
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			if(".xls".equals(extString)){
				return wb = new HSSFWorkbook(is);
			}else if(".xlsx".equals(extString)){
				return wb = new XSSFWorkbook(is);
			}else{
				return wb = null;
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}
	public static Object getCellFormatValue(Cell cell){
		Object cellValue = null;
		if(cell!=null){
			//判断cell类型
			switch(cell.getCellType()){
				case Cell.CELL_TYPE_NUMERIC:{
					cellValue = String.valueOf(cell.getNumericCellValue());
					break;
				}
				case Cell.CELL_TYPE_FORMULA:{
					//判断cell是否为日期格式
					if(DateUtil.isCellDateFormatted(cell)){
						//转换为日期格式YYYY-mm-dd
						cellValue = cell.getDateCellValue();
					}else{
						//数字
						cellValue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
				case Cell.CELL_TYPE_STRING:{
					cellValue = cell.getRichStringCellValue().getString();
					break;
				}
				default:
					cellValue = "";
			}
		}else{
			cellValue = "";
		}
		return cellValue;
	}

}