java 关于xlsx，xls 和 csv 文件的数据解析
1、适用于xlsx 和 xls
<!--xlsx和xls文件pom依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
package com.test.demo.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @program: demo
* @description: java解析Excel(xls, xlsx)
* @author: ZhuGaoPo
* @version:1.0
* @create: 2019-11-20 16:34
*/
public class XlsxUtils {
public static void main(String[] args) {
String filePath = "E:\\file\\csv\\test1.xlsx";
String []columns = {"姓名","性别","电话号码"};
List<Map<String,String>> list = getExcel(filePath, columns);
//遍历解析出来的list
for (Map<String,String> map : list) {
System.out.println(map.get(columns[0]));
for (Map.Entry<String,String> entry : map.entrySet()) {
System.out.print(entry.getKey()+":"+entry.getValue()+",");
}
}
}
/**
*读取excel
* @param filePath
* @return
*/
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;
}
/**
*获取excel 的内容
* @param cell
* @return
*/
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
switch(cell.getCellType()){
//数字
case Cell.CELL_TYPE_NUMERIC:{
Double value = cell.getNumericCellValue();
BigDecimal bd1 = new BigDecimal(Double.toString(value));
// 去掉后面无用的零 如小数点后面全是零则去掉小数点
cellValue = bd1.toPlainString().replaceAll("0+?$", "").replaceAll("[.]$", "");
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;
}
/**
* 获取解析后的list
* @param filePath 路径
* @param columns 表头
* @return
*/
public static List<Map<String,String>> getExcel(String filePath, String []columns) {
Workbook wb =null;
Sheet sheet = null;
Row row = null;
List<Map<String,String>> list = null;
String cellData = null;
// String columns[] = {"姓名","电话号码"};
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 column = row.getPhysicalNumberOfCells();
for (int i = 1; i< rowNum; i++) {
Map<String,String> map = new LinkedHashMap<>();
row = sheet.getRow(i);
if(row !=null){
for (int j=0;j<column;j++){
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
}else{
break;
}
list.add(map);
}
}
return list;
}
}2、适用于csv
package com.test.demo.util;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
* @program: outbound_server
* @description: 导入csv
* @author: ZhuGaoPo
* @version:1.0
* @create: 2019-11-11 11:41
*/
public class CsvUtils {
/**
* CSV文件导出
* @param file csv文件（路径+文件名）, csv文件不会自动创建
* @param dataList 数据
* @return
*/
public static boolean exportCsv(File file, List<String> dataList) {
boolean isSuccess = false;
//创建文件流,赋值为null
FileOutputStream outputStream = null;
OutputStreamWriter outputStreamWriter = null;
BufferedWriter bufferedWriter = null;
//操作
try {
outputStream = new FileOutputStream(file);
outputStreamWriter = new OutputStreamWriter(outputStream);
bufferedWriter = new BufferedWriter(bufferedWriter);
if (dataList != null && !dataList.isEmpty()) {
for (String date : dataList) {
//添加数据
bufferedWriter.append(date).append("\r");
}
}
isSuccess = true;
} catch (Exception e) {
isSuccess = false;
} finally {
if (bufferedWriter != null) {
try {
bufferedWriter.close();
bufferedWriter = null;
} catch (IOException e) {
e.printStackTrace();
}
}
if (outputStreamWriter != null) {
try {
outputStreamWriter.close();
outputStreamWriter = null;
} catch (IOException e) {
e.printStackTrace();
}
}
if (outputStream != null) {
try {
outputStream.close();
outputStream = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
return isSuccess;
}
/**
* CSV文件导入
* @param file csv文件（路径+文件）
* @return
*/
public static List<String> importCsv(File file) {
//数据
List<String> dataList = new ArrayList<String>();
BufferedReader bufferedReader = null;
try {
//为文件流赋数据
// bufferedReader = new BufferedReader(new FileReader(file));
// DataInputStream in = new DataInputStream(new FileInputStream(file));
bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "GBK"));
String line;
while ((line = bufferedReader.readLine()) != null) {
dataList.add(line);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bufferedReader != null) {
try {
bufferedReader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return dataList;
}
/**
* * CSV文件导出测试
*/
public static void exportCsvTest(List<String> dataList) {
boolean isSuccess = CsvUtils.exportCsv(new File("F:/CSVTest.csv"), dataList);
System.out.println(isSuccess);
}
public static void main(String[] args) {
String fileName = "E:\\file\\csv\\test0\\test0.csv";
List<String> list = CsvUtils.importCsv(new File(fileName));
// List<TelNumber> dataList = new ArrayList<>(16);
if (list != null && !list.isEmpty()) {
for(int i=1; i< list.size();i++){
System.out.println(list.get(i).split(",").length);
/* TelNumber telNumber = new TelNumber();
telNumber.setNumber(list.get(i).split(",")[0]);
dataList.add(telNumber);*/
}
/* System.out.println(dataList);*/
}
}
}