java csv xls_java 关于xlsx（xls） 和 csv 文件的数据解析

1、适用于xlsx 和 xls

org.apache.poi

poi-ooxml

3.17

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> list = getExcel(filePath, columns);

//遍历解析出来的list

for (Map map : list) {

System.out.println(map.get(columns[0]));

for (Map.Entry 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> getExcel(String filePath, String []columns) {

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List> list = null;

String cellData = null;

// String columns[] = {"姓名","电话号码"};

wb = readExcel(filePath);

if(wb != null){

//用来存放表中数据

list = new ArrayList>();

//获取第一个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 map = new LinkedHashMap<>();

row = sheet.getRow(i);

if(row !=null){

for (int j=0;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 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 importCsv(File file) {

//数据

List dataList = new ArrayList();

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 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 list = CsvUtils.importCsv(new File(fileName));

// List 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);*/

}

}

}