读取excel表格并且解析数据进行批量插入

直接上代码

package ffcs.cn.peam.function.controller;

import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import ffcs.cn.peam.function.domain.ExcelRecord;



@Controller
@RequestMapping(value = "peam/function")
public class FunctionController {

	@RequestMapping("function")
	public String index() {
		System.out.println("这是我的首页");
		return "function/function";
	}

	//*****************对excel文件进行处理********************
	@RequestMapping("solveExcel")
	public List<ExcelRecord> solveExcel(){
		String url="D:\\File_Save_Space\\upload\\20200821\\blshbcsj\\20200821\\1597979717375.xls";
		Workbook workbook = getWorkbook(url);//获取excel
		Sheet sheet = getSheet(workbook);//获取excel的第一个sheet
		
		List<ExcelRecord> records = new ArrayList<ExcelRecord>();
		resovleCells(sheet,records);
		workbook.close();
		return records;
	}
	
	//************根据路径获取excel文件****************
	private Workbook getWorkbook(String uri) {
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(new File(uri));
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}
	
	//************获取excel的第一页sheet****************
	private Sheet getSheet(Workbook workbook) {
		Sheet sheet = workbook.getSheet(0);
		return sheet;
	}
	
	
	//************解析时间格式****************
	private Date convertDate4JXL(Date date) throws ParseException{
		if(date==null)
	        return null;
       TimeZone gmt = TimeZone.getTimeZone("GMT");
       DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.getDefault());
       dateFormat.setTimeZone(gmt);
       String str = dateFormat.format(date);
      
       TimeZone local = TimeZone.getDefault();
       dateFormat.setTimeZone(local);
       return dateFormat.parse(str);
	}
	
	
	
	//*********************解析excel*********************
	private void resovleCells(Sheet sheet,List<ExcelRecord> records) {
		
		int rowSize = sheet.getRows();//行
		int columnSize = sheet.getColumns();//列
		//这里如果excel前面四行是标题，读取数据则会从第五行开始读取
		for(int rowNum=4; rowNum<rowSize; rowNum++){
			//检查是否整行都是空的
			boolean flag=false;
			for(int i=0;i<columnSize;i++){
				String con=sheet.getCell(i,rowNum).getContents();
				if(null!=con&&!"".equals(con.trim())){
					flag=true;
					break;
				}
			}
			
			//如果都为空，直接读取下一行
			if(!flag){
				continue;
			}
			
			//记录当前所在行数
			ExcelRecord record = new ExcelRecord();
			record.setRowNum(rowNum);

			
			//读取日期格式的数据,格式正确则插入，格式错误则停止并且记录错误
			String measureTime="";
			try {
				measureTime = convertDate4JXL(((DateCell) sheet.getCell(0,rowNum)).getDate()).toLocaleString();
			} catch (Exception e) {
				SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				measureTime = sheet.getCell(0,rowNum).getContents();
				try {
					formatter.parse(measureTime);
				} catch (ParseException e1) {
					record.setReason("时间格式不正确！");
					record.setTime(sheet.getCell(0,rowNum).getContents());
					/*continue;*/
				}
			}
			record.setTime(measureTime);
			
			//读取数据并且插入
			String value=sheet.getCell(1,rowNum).getContents();
			record.setValue(Integer.parseInt(value));
			
			//将每一行插入到集合中
			records.add(record);
			
			//批量操作插入数据（这里没有对格式错误的数据进行剔除，需要的自行剔除）
			save(records);
		}
	}
	
	
	//**********************批量插入数据*******************************
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public void save(List<ExcelRecord> records) {
		final List<ExcelRecord> values = records; 
		String sql=" insert into records (T1.TIME,T1.VALUE) values(?,?)";
			
		jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter(){   
				//执行次数 
	            public int getBatchSize() {   
	                 return values.size();    
	            }   
	            
	            //执行参数
	            public void setValues(PreparedStatement ps, int i)   
	                    throws SQLException {
	            	String sTime=values.get(i).getTime();
	            	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	            	Date time=new Date();
					try {
						time = formatter.parse(sTime);
					} catch (ParseException e) {
						e.printStackTrace();
					}
	            	
	            	ps.setTimestamp(1, new Timestamp(time.getTime())); 
	            	ps.setInt(2,values.get(i).getValue());
	            	
	            }    
	      });  
	}
	
	
	
	
	
	
	
}