java 读取excel 数字_java读取excel表中的数据

导入依赖

org.apache.poi

poi

3.17-beta1

org.apache.poi

poi-ooxml

3.17-beta1

org.apache.poi

poi-ooxml-schemas

3.17-beta1

核心代码import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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 java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.math.BigDecimal;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.*;

public class Excel{

public List> read(String filePath,String columns[]) {

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List> list = null;

String cellData = null;

wb = readExcel(filePath);

if(wb != null){

//用来存放表中数据

list = new ArrayList>();

//获取第一个sheet

sheet = wb.getSheetAt(0);

//获取最大行数

int rownum = sheet.getPhysicalNumberOfRows();

//获取第一行

row = sheet.getRow(0);

//获取最大列数

int colnum = row.getPhysicalNumberOfCells();

for (int i = 1; i

Map map = new LinkedHashMap();

row = sheet.getRow(i);

if(row !=null){

for (int j=0;j

Cell cell = row.getCell(j);

cellData = getCellFormatValue(cell);

map.put(columns[j], cellData);

}

}else{

break;

}

list.add(map);

}

}

return list;

}

//读取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;

}

@SuppressWarnings("deprecation")

public static String getCellFormatValue(Cell cell){

String cellValue = "";

if(cell == null){

return cellValue;

}

//把数字当成String来读，避免出现1读成1.0的情况

if(cell.getCellType()==0) {

Date d=cell.getDateCellValue();

//如果是时间，则转为yyyy-MM-dd的格式。可以根据自己的需要转化

DateFormat df= new SimpleDateFormat("yyyy-MM-dd");

cellValue=df.format(d);

}else {

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

cell.setCellType(Cell.CELL_TYPE_STRING);

}

switch (cell.getCellType()){

case Cell.CELL_TYPE_NUMERIC: //数字

cellValue = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_STRING: //字符串

cellValue = String.valueOf(cell.getStringCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN: //Boolean

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA: //公式

cellValue = String.valueOf(cell.getCellFormula());

break;

case Cell.CELL_TYPE_BLANK: //空值

cellValue = "";

break;

case Cell.CELL_TYPE_ERROR: //故障

cellValue = "非法字符";

break;

default:

cellValue = "未知类型";

break;

}

}

return cellValue;

}

public static void main(String[] args){

Excel ex = new Excel();

String c[]= {"stuId","stuName","userName","carNo",

"carNo2","userPhone","carNumber","remark"};

List> list=ex.read("文件路径",c);

DateFormat df= new SimpleDateFormat("yyyy-MM-dd");

for (Map map : list) {

//通过健去获取值

String carNo = map.get("carNo");

}

}

}

去除数据库中某个字段相同，其他字段不相同的记录创建中间表，并获取值create table tmp as select min(主键) as col1 from 去重表名 GROUP BY 去重字段;删除重复值，已中间表中记录作为条件delete from 去重表名 where 主键 not in (select col1 from tmp);删除中间表drop table tmp;