Excel解析字段导入数据库

 

思路：通过上传文件上传Excel到某路径下，在从路径解析excel值入库。

表格样式:

pom.xml

	    <dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>2.3</version>
		</dependency>
	    <dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>

package com.kero99.utils;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
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.xssf.usermodel.XSSFWorkbook;

import com.kero99.pojo.Lconstructionproject;
import com.kero99.pojo.Ltconstructionprojectfapf;

public class ImportExcel {
		public static void main(String[] args) throws ParseException {
			
			File file = new File("E:\\shape文件\\excel\\项目及方案批复信息 - 副本.xlsx");
		//	File file = new File("E:\\shape文件\\excel\\项目及方案批复信息.xlsx");
			//File file = new File("E:\\shape文件\\excel\\项目及方案基本信息-excel - 副本.xlsx");
			XSSFWorkbook workbook;
			  try {
				 workbook =new XSSFWorkbook(FileUtils.openInputStream(file));
				//第二种获取方式通过index 获取第一个表
		        Sheet sheet = workbook.getSheetAt(0);
		        int lastRowNum = sheet.getLastRowNum();
		        //从第三行开始导入
		        int firstRowNum=3;
		        List<Map<Integer,String>> result=new ArrayList<Map<Integer,String>>();
		        Map<Integer,String> map=null;
		        //解析数据
		        for(int i=firstRowNum;i<=lastRowNum;i++){
		        	map=new HashMap<Integer,String>();
		            Row row = sheet.getRow(i);     		         
		            //获取当前最后单元格列号
		        	int lastCellNum = row.getLastCellNum();
		        	System.out.println(lastCellNum+"lastCellNum");
		            for(int j=0;j<lastCellNum;j++){
		                Cell cell = row.getCell(j);
		                cell.setCellType(Cell.CELL_TYPE_STRING);
		                String value = cell.getStringCellValue();		                
		                map.put(j, value);
		                System.out.print(value + "  ");//注意value后面的空格
		               
		            }
		            result.add(map);
		        }
		        //数据入库
		        for(Map<Integer,String> eachValue :result){
		        	Ltconstructionprojectfapf fapf=new Ltconstructionprojectfapf();
		        	Lconstructionproject project=new Lconstructionproject();
		        	//六位行政区划代码
		        	project.setApprovalDept(eachValue.get(0));
		        	//见字典表
		        	project.setLevel(eachValue.get(1));
		        	//发改委项目编码
		        	project.setFgwCode(eachValue.get(2));
		        	//生产建设项目名称
		        	project.setName(eachValue.get(3));
		        	//项目所属行业
		        	project.setIndustry(eachValue.get(4));
		        	//项目类型
		        	project.setProjecttype(eachValue.get(5));
		        	//项目性质
		        	project.setNature(eachValue.get(6));
		        	project.setProjectscale(eachValue.get(7));
		        	project.setTotalinvestment(Double.valueOf(eachValue.get(8).toString()));
		        	project.setBuildinvestment(Double.valueOf(eachValue.get(9).toString()));
		        	project.setMonitoryear(Integer.valueOf(eachValue.get(10)));
		        	project.setProjelocation(eachValue.get(11));
		        	project.setStarttime(CommonUtil.stringToDate_2(eachValue.get(12).toString()));
		        	project.setEndtime(CommonUtil.stringToDate_2(eachValue.get(13).toString()));
		        	fapf.setReplydate(CommonUtil.stringToDate_2(eachValue.get(14).toString()));
		        	fapf.setReplynum(eachValue.get(15));
		        	fapf.setReplyunit(eachValue.get(16));
		        	System.out.println(project);
		        	System.out.println(fapf);
		        	//保存接口
		        }
		        
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	
}