java下载excel导入excel读取excel数据,将数据插入数据库

10:如果你想导出excel表格,请链接:http://blog.csdn.net/xuanzhangran/article/details/54709221

导入的效果如下:
1:开始页面
这里写图片描述
2:点击下载Excel模板按钮实现的效果:
这里写图片描述
3:选中保存文件,点击确定,将文件保存下来。
4:点击预览,弹出本地文件,选择刚才保存的excel文件:
这里写图片描述
5:选中一个excel文件,点击导入Excel按钮,提示导入的数据信息:
这里写图片描述

6:注意事项以及代码如下:

注:1:后台代码72行,文件的存储位置:必须在webapp下建立upload/excel。
2:下载excel表格的工具类:ExportExcelSeedBack,已经在最下边写好了。
3: 读取excel数据的工具类:ImportExcelUtil也在下边写好了。
4:最重要的一点:在下载,读取excel的时候,需要下载的三个poi-jia包:下载路径:无需c币,无需积分,直接下载解压缩:
http://download.csdn.net/detail/xuanzhangran/9813051
5:请仔细操作,代码很简单,都已经封装好了。就是有点多。愿君成功。

前台页面:

<!--下载excel-->
<input type="button" class="searchbtn search" value="下载Excel模板" onclick="downExcel()">
<!--导入excel-->
<div class="row" id="div3" style="width:450px;">
                    <br><span class="personattr" style="display: inline;">批量导入用户信息:</span>
                    <input type="hidden" name="excelUrl"  id="excelUrl"/>
            		 <a href="javascript:void(0);" class="btn_addPic">
					     <span id="imgTitle" style="color: red ">上传excel表格前,请先下载excel模板</span>					    					   						
			         	<input type="file" name="logoFile" id="logoFile" onchange="setImg(this);" class="selectedLogoImgId">
			         </a>
                <input type="hidden" name="excelUrlShow" id="excelUrlShow" style="width: 315px;">
                </div>
<!--读取excel-->
            <input type="submit" class="searchbtn search" value="导入用户信息" onclick="insertExcel()">

js代码:

//下载excel
function downExcel(){
		window.location.href = "/KaiTong/downExcel.html";//在后台代码里
}
/**
 * 点击预览,导入excel
 */
function setImg(obj){//用于进行excel上传,返回地址
	var f=$(obj).val();
	if(f == null || f ==undefined || f == ''){
		return false;
	}
	if(!/\.(?:xls|xlsx)$/.test(f))
    {
    	alertLayel("类型必须是excel表格(.xls|xlsx)格式");
    	$(obj).val('');
    	return false;
    }
	var data = new FormData();
	$.each($(obj)[0].files,function(i,file){
		data.append('file', file);
	});
	$.ajax({
		type: "POST",
		url: "/KaiTong/uploadImg.html",
		data: data,
		cache: false,
		contentType: false,    //不可缺
		processData: false,    //不可缺
		dataType:"json",
		success: function(suc) {
			if(suc.code==0){
				$("#excelUrl").val(suc.message);//将地址存储好
				$("#excelUrlShow").val(suc.message);//显示excel
			}else{
				alertLayel("上传失败");
				$("#url").val("");
				$(obj).val('');
			}
		},
		error: function(XMLHttpRequest, textStatus, errorThrown) {
			alertLayel("上传失败,请检查网络后重试");
			$("#url").val("");
			$(obj).val('');
		}
	});
}

/**
 * 导入excel数据
 */
function insertExcel(){
	var url = $("#excelUrlShow").val();
	if(url == null || url == ''){
		alertLayel("请选择Excel文件");
		return false;
	}
	$.ajax({
		type: "POST",
		url: "/KaiTong/insertUserExcel.html?url="+url,
		cache: false,
		contentType: false,    
		processData: false,    
		dataType:"json",
		success: function(obj) {
		    var total = obj.result.total;
			var num = obj.result.num;
			var shibai = total - num;
			if(obj.code == 1){
				alertLayel("一共"+total+"条数据,成功导入"+num+"条数据,失败"+shibai+"条数据");//成功
			}else if(obj.code == 2){
				alertLayel(obj.message);//失败
			}else if(obj.code == 3){
				alertLayel("Excel中没有数据");//无数据
			}
			else if(obj.code == 0){
				alertLayel("系统异常,请稍候操作");
			}
		},
		error: function(XMLHttpRequest, textStatus, errorThrown) {
			alertLayel("上传失败,请检查网络后重试");
			$("#url").val("");
			$(obj).val('');
		}
	});
}

后台代码:

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.fastjson.JSON;
import com.idorabox.core.utils.DateUtil;
import com.idorabox.core.utils.ResponseResult;
import com.idorabox.sou.web.util.ExportExcelSeedBack;
import com.idorabox.sou.web.util.ImportExcelUtil;
@Controller
@RequestMapping("/KaiTong")
public class KaiTongAction {
	 /**
	    * 下载excel表格
	    * @param response
	    */
@RequestMapping("/downExcel.html")
public void xiazai(HttpServletResponse response){
			//导出文件的标题
			String title=DateUtil.format(new Date(), "yyyyMMdd")+".xls";
			//设置表格标题行
			String[] headers = new String[] {"序号","省","市", "区","小区", "楼号","单元","房间号","姓名","手机号","小区注册账号"};
			List<Object[]> dataList = new ArrayList<Object[]>();
			OutputStream out=null;
			try {
					//防止中文乱码
				String headStr = "attachment; filename=\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\"";
				response.setContentType("octets/stream");
				response.setContentType("APPLICATION/OCTET-STREAM");
				response.setHeader("Content-Disposition", headStr);
		  	    out = response.getOutputStream();
			    ExportExcelSeedBack ex = new ExportExcelSeedBack(title, headers, dataList);   // 没有标题
				ex.export(out);
				} catch (Exception e) {
					e.printStackTrace();
				}
    }

/**
 * 上传excel表格
 * @param file
 * @param request
 * @return
 */
@ResponseBody
@RequestMapping("/uploadImg.html")
public String uploadPicture(
			@RequestParam(value="file",required=false)MultipartFile file,
			HttpServletRequest request){
		File targetFile=null;
		String msg="";//返回存储路径
		int code=1;
		String fileName=file.getOriginalFilename();//获取文件名加后缀
		if(fileName!=null&&fileName!=""){	
			//String returnUrl = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath() +"/upload/excel/";//存储路径
	        String path = request.getSession().getServletContext().getRealPath("upload/excel"); //文件存储位置
	        String fileF = fileName.substring(fileName.lastIndexOf("."), fileName.length());//文件后缀
	        fileName=new Date().getTime()+"_"+new Random().nextInt(1000)+fileF;//新的文件名
	     
	        //先判断文件是否存在
			String fileAdd = DateUtil.format(new Date(),"yyyyMMdd");
			File file1 =new File(path+"/"+fileAdd); 
			//如果文件夹不存在则创建    
			if(!file1 .exists()  && !file1 .isDirectory()){       
			    file1 .mkdir();  
			}
			targetFile = new File(file1, fileName);
	//        targetFile = new File(path, fileName);
	        try {
	        	file.transferTo(targetFile);
	//        	msg=returnUrl+fileName;
	//        	msg=returnUrl+fileAdd+"/"+fileName;
	        	msg=path+"/"+fileAdd+"/"+fileName;
	        	code=0;
	        } catch (Exception e) {
	        	e.printStackTrace();
	        }
		}
		return JSON.toJSONString(ResponseResult.result(code, msg));
	}
/**
 * 通过表格的路径,批量导入用户的信息到数据库
 * @param url
 * @param request
 * @return
 */
@ResponseBody
@RequestMapping("/insertUserExcel.html")
public String insertUserExcel(HttpServletRequest request,
	@RequestParam(value="url",required=false)String url){
	File file = new File(url);  
    List<List<Object>> dataList;
    SysUser user = new SysUser();
    SysUserDarea userDarea = new SysUserDarea();
    SysUserDoor sysUserDoor = new SysUserDoor();
    Integer code = 0;
	String message = null;
	Integer num1 = 0;
	Integer total = 0;
	Map<String, Object> map = new HashMap<String, Object>();
	try {
		dataList = ImportExcelUtil.importExcel(file);
		total = dataList.size()-1;//表格中的数据
		if(dataList != null && dataList.size()>1){
			for (int i = 1; i < dataList.size(); i++) {
				if(dataList.get(0).size() != dataList.get(i).size() && dataList.get(0).size() != 11){//将标题的长度与每一行数据的长度进行比较
					continue;//如果二者不相等说明这一行有空值的存在,或者表格不正确
				}
				Object proviceName = dataList.get(i).get(1);
				Object cityName = dataList.get(i).get(2);
				Object areaName = dataList.get(i).get(3);
				Object raionName = dataList.get(i).get(4);
				Object louName = dataList.get(i).get(5);
				Object dareaName = dataList.get(i).get(6);
				Object roomNumber = dataList.get(i).get(7);
				Object userName = dataList.get(i).get(8);
				Object phone = dataList.get(i).get(9);
				Object wyPhone = dataList.get(i).get(10);
				map.put("proviceName", proviceName);
				map.put("cityName", cityName);
				map.put("areaName", areaName);
				map.put("raionName", raionName);//小区
				map.put("louName", louName);
				map.put("dareaName", dareaName);//单元
				map.put("wyPhone", wyPhone);//小区账号
					user.setPhone((String)phone);
					user.setUserName((String)userName);
					user.setCreateDateTime(new Date());
					user.setUpdateTime(new Date());
					user.setStatus((byte)1);
					user.setInvalid((byte)0);
					num1 += sysUserService.insertSelective(user);
						}
			     }
			}
			if(num1 >0){
				code = 1;
			}else{
			    code = 2;
			    message = "导入数据失败,没有对应的单元与物业账号,或者手机号信息错误";
			}
	    }else if(dataList == null || dataList.size() == 1){
	    	code = 3;
	    }
	} catch (IOException e) {
		e.printStackTrace();
	} 
	Map<String, Object> result = new HashMap<String, Object>();
	result.put("total", total);
	result.put("num", num1);
	return JSON.toJSONString(ResponseResult.result(code, message,result));
}

}

引用的工具类:ExportExcelSeedBack :

package com.idorabox.sou.web.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 导出Excel公共方法
 * @version 1.0
 * 
 * @author qinzz
 *
 */
public class ExportExcelSeedBack {
	
	//显示的导出表的标题
	private String title;
	//导出表的列名
	private String[] rowName ;
	
	private List<Object[]>  dataList = new ArrayList<Object[]>();
	
	HttpServletResponse  response;
	
	
	//构造方法,传入要导出的数据
	public ExportExcelSeedBack(String title,String[] rowName,List<Object[]> dataList){
		this.dataList = dataList;
		this.rowName = rowName;
		this.title = title;
	}
			
	/*
	 * 导出数据
	 * */
	public void export(OutputStream out) throws Exception{
		try{
			HSSFWorkbook workbook = new HSSFWorkbook();						// 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title);		 			// 创建工作表
			
			// 产生表格标题行
//	        HSSFRow rowm = sheet.createRow(0);
//	        HSSFCell cellTiltle = rowm.createCell(0);
	        
	        //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
	        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
	        HSSFCellStyle style = this.getStyle(workbook);					//单元格样式对象
	        
//	        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));//合并单元格  
//	        cellTiltle.setCellStyle(columnTopStyle);
//	        cellTiltle.setCellValue(title);
    		
			// 定义所需列数
			int columnNum = rowName.length;
			HSSFRow rowRowName = sheet.createRow(0);				// 在索引2的位置创建行(最顶端的行开始的第二行)
			
			// 将列头设置到sheet的单元格中
			for(int n=0;n<columnNum;n++){
				HSSFCell  cellRowName = rowRowName.createCell(n);				//创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				//设置列头单元格的数据类型
    			HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
    			cellRowName.setCellValue(text);									//设置列头单元格的值
    			cellRowName.setCellStyle(columnTopStyle);						//设置列头单元格样式
    		}
			
    		//将查询出的数据设置到sheet对应的单元格中
			for(int i=0;i<dataList.size();i++){
				
				Object[] obj = dataList.get(i);//遍历每个对象
				HSSFRow row = sheet.createRow(i+1);//创建所需的行数(从第二行开始写数据)
				
				for(int j=0; j<obj.length; j++){
					HSSFCell  cell = null;   //设置单元格的数据类型
					if(j == 0){
						cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(i+1);	
					}else{
						cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
						if(!"".equals(obj[j]) && obj[j] != null){
							cell.setCellValue(obj[j].toString());						//设置单元格的值
						}
					}
					cell.setCellStyle(style);									//设置单元格样式
				}
			}
			//让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
	            int columnWidth = sheet.getColumnWidth(colNum) / 256;
	            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
	                HSSFRow currentRow;
	                //当前行未被使用过
	                if (sheet.getRow(rowNum) == null) {
	                    currentRow = sheet.createRow(rowNum);
	                } else {
	                    currentRow = sheet.getRow(rowNum);
	                }
//	                if (currentRow.getCell(colNum) != null) {
//	                    HSSFCell currentCell = currentRow.getCell(colNum);
//	                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//	                        int length = currentCell.getStringCellValue().getBytes().length;
//	                        if (columnWidth < length) {
//	                            columnWidth = length;
//	                        }
//	                    }
//	                }
	                if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = 0;
                            try {
                                length = currentCell.getStringCellValue().getBytes().length;
                            } catch (Exception e) {
                            	e.printStackTrace();
                            }
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
	                
	            }
	            if(colNum == 0){
	            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
	            }else{
	            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
	            }
	        }
			if(workbook !=null){
				try{
			        workbook.write(out);
			    }catch (IOException e) {
			        e.printStackTrace();
			    }
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			out.close();
		}
		
	}
	
	/* 
	 * 列头单元格样式
	 */    
  	public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
  		
  		  // 设置字体
    	  HSSFFont font = workbook.createFont();
    	  //设置字体大小
    	  font.setFontHeightInPoints((short)11);
    	  //字体加粗
    	  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	  //设置字体名字 
    	  font.setFontName("Courier New");
    	  //设置样式; 
    	  HSSFCellStyle style = workbook.createCellStyle();
    	  //设置底边框; 
    	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	  //设置底边框颜色;  
    	  style.setBottomBorderColor(HSSFColor.BLACK.index);
    	  //设置左边框;   
    	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	  //设置左边框颜色; 
    	  style.setLeftBorderColor(HSSFColor.BLACK.index);
    	  //设置右边框; 
    	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	  //设置右边框颜色; 
    	  style.setRightBorderColor(HSSFColor.BLACK.index);
    	  //设置顶边框; 
    	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	  //设置顶边框颜色;  
    	  style.setTopBorderColor(HSSFColor.BLACK.index);
    	  //在样式用应用设置的字体;  
    	  style.setFont(font);
    	  //设置自动换行; 
    	  style.setWrapText(false);
    	  //设置水平对齐的样式为居中对齐;  
    	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	  //设置垂直对齐的样式为居中对齐; 
    	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	  
    	  return style;
    	  
  	}
  	
  	/*  
	 * 列数据信息单元格样式
	 */  
  	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
	  	  // 设置字体
	  	  HSSFFont font = workbook.createFont();
	  	  //设置字体大小
	  	  //font.setFontHeightInPoints((short)10);
	  	  //字体加粗
	  	  //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	  	  //设置字体名字 
	  	  font.setFontName("Courier New");
	  	  //设置样式; 
	  	  HSSFCellStyle style = workbook.createCellStyle();
	  	  //设置底边框; 
	  	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	  	  //设置底边框颜色;  
	  	  style.setBottomBorderColor(HSSFColor.BLACK.index);
	  	  //设置左边框;   
	  	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	  	  //设置左边框颜色; 
	  	  style.setLeftBorderColor(HSSFColor.BLACK.index);
	  	  //设置右边框; 
	  	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	  	  //设置右边框颜色; 
	  	  style.setRightBorderColor(HSSFColor.BLACK.index);
	  	  //设置顶边框; 
	  	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	  	  //设置顶边框颜色;  
	  	  style.setTopBorderColor(HSSFColor.BLACK.index);
	  	  //在样式用应用设置的字体;  
	  	  style.setFont(font);
	  	  //设置自动换行; 
	  	  style.setWrapText(false);
	  	  //设置水平对齐的样式为居中对齐;  
	  	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  	  //设置垂直对齐的样式为居中对齐; 
	  	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	  	 
	  	  return style;
  	
  	}
}

读取excel的工具类:ImportExcelUtil

import java.io.File;    
import java.io.FileInputStream;    
import java.io.FileNotFoundException;    
import java.io.IOException;    
import java.text.DecimalFormat;    
import java.text.SimpleDateFormat;    
import java.util.LinkedList;    
import java.util.List;    

import org.apache.poi.hssf.usermodel.HSSFCell;    
import org.apache.poi.hssf.usermodel.HSSFDateUtil;    
import org.apache.poi.hssf.usermodel.HSSFRow;    
import org.apache.poi.hssf.usermodel.HSSFSheet;    
import org.apache.poi.hssf.usermodel.HSSFWorkbook;    
import org.apache.poi.xssf.usermodel.XSSFCell;    
import org.apache.poi.xssf.usermodel.XSSFRow;    
import org.apache.poi.xssf.usermodel.XSSFSheet;    
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  


/**
 * 
    * @ClassName: ImportExcelUtil
    * @Description: excel 导入数据
    * @author qinzz
    * @date 2017年1月17日 
    * @time 上午10:28:48
 */
public class ImportExcelUtil {
	public static List<List<Object>> importExcel(File file) throws IOException{    
		   String fileName = file.getName();    
		   String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);    
		   if("xls".equals(extension)){    
		    return read2003Excel(file);    
		   }else if("xlsx".equals(extension)){    
		    return read2007Excel(file);    
		   }else{
		    throw new IOException("不支持的文件类型");    
		   }    
		}  
	
	
	
		/**  
		* 读取 office 2003 excel  
		* @throws IOException   
		* @throws FileNotFoundException */    
		private static List<List<Object>> read2003Excel(File file) throws IOException{    
		   List<List<Object>> list = new LinkedList<List<Object>>();    
		   HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));    
		   HSSFSheet sheet = hwb.getSheetAt(0);    
		   Object value = null;    
		   HSSFRow row = null;    
		   HSSFCell cell = null;     
		   for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){    
		    row = sheet.getRow(i);    
		    if (row == null) {    
		     continue;    
		    }    
		    List<Object> linked = new LinkedList<Object>();    
		    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {    
		     cell = row.getCell(j);    
		     if (cell == null) {    
		      continue;    
		     }    
		     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
		     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
		     DecimalFormat nf = new DecimalFormat("0");// 格式化数字    
		     switch (cell.getCellType()) {    
		     case XSSFCell.CELL_TYPE_STRING:    
		    //  System.out.println(i+"行"+j+" 列 is String type");    
		      value = cell.getStringCellValue();    
		      break;    
		     case XSSFCell.CELL_TYPE_NUMERIC:    
		   //   System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
		      if("@".equals(cell.getCellStyle().getDataFormatString())){    
		         value = df.format(cell.getNumericCellValue());    
		      } else if("General".equals(cell.getCellStyle().getDataFormatString())){    
		         value = nf.format(cell.getNumericCellValue());    
		      }else{    
		        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
		      }    
		      break;    
		     case XSSFCell.CELL_TYPE_BOOLEAN:    
		   //   System.out.println(i+"行"+j+" 列 is Boolean type");    
		      value = cell.getBooleanCellValue();    
		      break;    
		     case XSSFCell.CELL_TYPE_BLANK:    
		   //   System.out.println(i+"行"+j+" 列 is Blank type");    
		      value = "";    
		      break;    
		     default:    
		   //   System.out.println(i+"行"+j+" 列 is default type");    
		      value = cell.toString();    
		     }    
		     if (value == null || "".equals(value)) {    
		      continue;    
		     }    
		     linked.add(value);      
		   }    
		    list.add(linked);    
		   }    
		   return list;    
		}
		
		
		/**  
		* 读取Office 2007 excel  
		* */    
		private static List<List<Object>> read2007Excel(File file) throws IOException {    
		   List<List<Object>> list = new LinkedList<List<Object>>();    
		   // 构造 XSSFWorkbook 对象,strPath 传入文件路径    
		   XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));    
		   // 读取第一章表格内容    
		   XSSFSheet sheet = xwb.getSheetAt(0);    
		   Object value = null;    
		   XSSFRow row = null;    
		   XSSFCell cell = null;    
		   for (int i = sheet.getFirstRowNum(); i <= sheet    
		     .getPhysicalNumberOfRows(); i++) {    
		    row = sheet.getRow(i);    
		    if (row == null) {    
		     continue;    
		    }    
		    List<Object> linked = new LinkedList<Object>();    
		    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {    
		     cell = row.getCell(j);    
		     if (cell == null) {    
		      continue;    
		     }    
		     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
		     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
		     DecimalFormat nf = new DecimalFormat("0");// 格式化数字    
		     switch (cell.getCellType()) {    
		     case XSSFCell.CELL_TYPE_STRING:    
		   //   System.out.println(i+"行"+j+" 列 is String type");    
		      value = cell.getStringCellValue();    
		      break;    
		     case XSSFCell.CELL_TYPE_NUMERIC:    
		   //   System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
		      if("@".equals(cell.getCellStyle().getDataFormatString())){    
		        value = df.format(cell.getNumericCellValue());    
		      } else if("General".equals(cell.getCellStyle().getDataFormatString())){    
		        value = nf.format(cell.getNumericCellValue());    
		      }else{    
		       value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
		      }    
		      break;    
		     case XSSFCell.CELL_TYPE_BOOLEAN:    
		   //   System.out.println(i+"行"+j+" 列 is Boolean type");    
		      value = cell.getBooleanCellValue();    
		      break;    
		     case XSSFCell.CELL_TYPE_BLANK:    
//		      System.out.println(i+"行"+j+" 列 is Blank type");    
		      value = "";    
		      break;    
		     default:    
		   //   System.out.println(i+"行"+j+" 列 is default type");    
		      value = cell.toString();    
		     }    
		     if (value == null || "".equals(value)) {    
		      continue;    
		     }    
		     linked.add(value);    
		    }    
		    list.add(linked);    
		   }    
		   return list;    
		}    
		
		 //读取excel里的字段
//		    public static void main(String[] args) throws IOException  { 
		    	excel 导入数据demo
//		        File file = new File("E:\\商城订单20170427.xls");  
//		        List<List<Object>> dataList=importExcel(file); 
//		        for (int i = 1; i < dataList.size(); i++) {
//		        	Object num = dataList.get(i).get(0); 
//		        	Object age = dataList.get(i).get(1);
//		        	Object class1 = dataList.get(i).get(2);
//		        	SuUser user = new SuUser();
//		        	System.out.println(Integer.parseInt((String)num));
//					System.out.println("------------------");
//				}
//		    }  
		    
}

工具类:ResponseResult

package com.idorabox.core.utils;

import java.util.Map;

public class ResponseResult {
    private Integer code;
    private String message;
    private String msg;
    private Map<String, Object> result;
    private static ResponseResult responseResult;

    public String getMsg() {
        return this.msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public Integer getCode() {
        return this.code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMessage() {
        return this.message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Map<String, Object> getResult() {
        return this.result;
    }

    public void setResult(Map<String, Object> result) {
        this.result = result;
    }

    public static ResponseResult result(int code, String message, Map<String, Object> map) {
        responseResult = new ResponseResult();
        responseResult.setCode(code);
        responseResult.setMessage(message);
        responseResult.setResult(map);
        return responseResult;
    }

    public static ResponseResult result(int code, String message) {
        responseResult = new ResponseResult();
        responseResult.setCode(code);
        responseResult.setMessage(message);
        return responseResult;
    }

    public ResponseResult() {
    }
}

工具类:ResponseResult

package com.idorabox.core.utils;

import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

public class DateUtil {
    public DateUtil() {
    }
    public static String format(Date dt) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(dt);
    }
}
相关推荐
Excel源代码ExcelCreate.java导出类和ExcelRead.java导入类 用到的poi底层架包有poi-3.6-20091214.jarpoi-contrib-3.6-20091214.jar poi-scratchpad-3.6-20091214.jar ExcelRead.java文件导入类 Public void importExcel(String file)读取文件方法一file指明要导入文件路径。 public void importExcel (File file) 读取文件方法二参数是file文件。 public void importExcel (InputStream filestream)读取文件方法三参数是文件流。 public List readRow (int sheetNumber,int rows), 读取第几张工作表的第几行数据返回一个list. (工作表起始行) ExcelCreate.java文件导出类 public ExcelCreate ()构造函数。 public void createSheet(String sheetName)创建工作表(工作表名称)。可创建多个工作表。 public static void setDefaultCellHighWidthInRange(short[] eachCellWidth, int high) 设置各列单元格宽度eachCellWidth[]指定的列的列号high设置的宽度。 public void addHeader (list rowvalues, Boolean isFilter)导入表头list是保存表头的集合isFilter是否过滤。 public void addRow (List rowvalues)数据导入Excel方法 public void insertRow (List rowvaluesInt rowIndex)数据导入Excel方法给指定的行后追加一条数据。 public void delRow(int row) 删除指定的行。 Public void setSelect (int row,int cells,List cellvalue)给指定的列设置下拉列表row是行号cells 是列号list是下拉列表的数据。 public void exportExcel (String file)导出文件的路径方法一file是要导出数据文件路径。 public void exportExcel (File file) 导出文件的路径方法二file是要导出的文件。 public void exportExcel (OutputStream outputstream) 导出文件的路径方法三outputstream是输出流。
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页