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);
    }
}
评论 21 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

转角人生

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值