java excel导入_Java 导入Excel文件到数据库

packageedu.sjtu.erplab.poi;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.HashMap;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;/*** 操作Excel表格的功能类*/

public classExcelReader {privatePOIFSFileSystem fs;privateHSSFWorkbook wb;privateHSSFSheet sheet;privateHSSFRow row;/*** 读取Excel表格表头的内容

*@paramInputStream

*@returnString 表头内容的数组*/

publicString[] readExcelTitle(InputStream is) {try{

fs= newPOIFSFileSystem(is);

wb= newHSSFWorkbook(fs);

}catch(IOException e) {

e.printStackTrace();

}

sheet= wb.getSheetAt(0);

row= sheet.getRow(0);//标题总列数

int colNum =row.getPhysicalNumberOfCells();

System.out.println("colNum:" +colNum);

String[] title= newString[colNum];for (int i = 0; i < colNum; i++) {//title[i] = getStringCellValue(row.getCell((short) i));

title[i] = getCellFormatValue(row.getCell((short) i));

}returntitle;

}/*** 读取Excel数据内容

*@paramInputStream

*@returnMap 包含单元格数据内容的Map对象*/

public MapreadExcelContent(InputStream is) {

Map content = new HashMap();

String str= "";try{

fs= newPOIFSFileSystem(is);

wb= newHSSFWorkbook(fs);

}catch(IOException e) {

e.printStackTrace();

}

sheet= wb.getSheetAt(0);//得到总行数

int rowNum =sheet.getLastRowNum();

row= sheet.getRow(0);int colNum =row.getPhysicalNumberOfCells();//正文内容应该从第二行开始,第一行为表头的标题

for (int i = 1; i <= rowNum; i++) {

row=sheet.getRow(i);int j = 0;while (j

str += getCellFormatValue(row.getCell((short) j)).trim() + " ";

j++;

}

content.put(i, str);

str= "";

}returncontent;

}/*** 获取单元格数据内容为字符串类型的数据

*

*@paramcell Excel单元格

*@returnString 单元格数据内容*/

privateString getStringCellValue(HSSFCell cell) {

String strCell= "";switch(cell.getCellType()) {caseHSSFCell.CELL_TYPE_STRING:

strCell=cell.getStringCellValue();break;caseHSSFCell.CELL_TYPE_NUMERIC:

strCell=String.valueOf(cell.getNumericCellValue());break;caseHSSFCell.CELL_TYPE_BOOLEAN:

strCell=String.valueOf(cell.getBooleanCellValue());break;caseHSSFCell.CELL_TYPE_BLANK:

strCell= "";break;default:

strCell= "";break;

}if (strCell.equals("") || strCell == null) {return "";

}if (cell == null) {return "";

}returnstrCell;

}/*** 获取单元格数据内容为日期类型的数据

*

*@paramcell

* Excel单元格

*@returnString 单元格数据内容*/

privateString getDateCellValue(HSSFCell cell) {

String result= "";try{int cellType =cell.getCellType();if (cellType ==HSSFCell.CELL_TYPE_NUMERIC) {

Date date=cell.getDateCellValue();

result= (date.getYear() + 1900) + "-" + (date.getMonth() + 1)+ "-" +date.getDate();

}else if (cellType ==HSSFCell.CELL_TYPE_STRING) {

String date=getStringCellValue(cell);

result= date.replaceAll("[年月]", "-").replace("日", "").trim();

}else if (cellType ==HSSFCell.CELL_TYPE_BLANK) {

result= "";

}

}catch(Exception e) {

System.out.println("日期格式不正确!");

e.printStackTrace();

}returnresult;

}/*** 根据HSSFCell类型设置数据

*@paramcell

*@return

*/

privateString getCellFormatValue(HSSFCell cell) {

String cellvalue= "";if (cell != null) {//判断当前Cell的Type

switch(cell.getCellType()) {//如果当前Cell的Type为NUMERIC

caseHSSFCell.CELL_TYPE_NUMERIC:caseHSSFCell.CELL_TYPE_FORMULA: {//判断当前的cell是否为Date

if(HSSFDateUtil.isCellDateFormatted(cell)) {//如果是Date类型则，转化为Data格式//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00//cellvalue = cell.getDateCellValue().toLocaleString();//方法2：这样子的data格式是不带带时分秒的：2011-10-12

Date date =cell.getDateCellValue();

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");

cellvalue=sdf.format(date);

}//如果是纯数字

else{//取得当前Cell的数值

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

}break;

}//如果当前Cell的Type为STRIN

caseHSSFCell.CELL_TYPE_STRING://取得当前的Cell字符串

cellvalue =cell.getRichStringCellValue().getString();break;//默认的Cell值

default:

cellvalue= " ";

}

}else{

cellvalue= "";

}returncellvalue;

}public static voidmain(String[] args) {try{//对读取Excel表格标题测试

InputStream is = new FileInputStream("d:\\test2.xls");

ExcelReader excelReader= newExcelReader();

String[] title=excelReader.readExcelTitle(is);

System.out.println("获得Excel表格的标题:");for(String s : title) {

System.out.print(s+ " ");

}//对读取Excel表格内容测试

InputStream is2 = new FileInputStream("d:\\test2.xls");

Map map =excelReader.readExcelContent(is2);

System.out.println("获得Excel表格的内容:");for (int i = 1; i <= map.size(); i++) {

System.out.println(map.get(i));

}

}catch(FileNotFoundException e) {

System.out.println("未找到指定路径的文件!");

e.printStackTrace();

}

}

}