springmvc导入Excel例子

1.controler

<span style="font-size:18px;">/**  * @author jiangzeyun  * @startTime  2014-09-06  * @endTime    2014-09-08  * @param request  * @param response  */ @RequestMapping(method=RequestMethod.POST, value="/uploadExcel")    public void uploadExcel(HttpServletRequest request, HttpServletResponse response) {  MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  MultipartFile multipartFile = multipartRequest.getFile("Filedata");  String sourceName = multipartFile.getOriginalFilename(); // 原始文件名  String fileType = sourceName.substring(sourceName.lastIndexOf("."));  //System.out.println("上传的文件名为:"+sourceName+"类型为:"+fileType);  String proId=request.getParameter("proId");  String proName=request.getParameter("proName");  String settleType=request.getParameter("settleType");  //安置类型：1表示货币安置  2.房源安置  //System.out.println("项目Id为:"+proId+"项目名称为:"+proName);   String base = request.getSession().getServletContext().getRealPath("/") + "attachments" + File.separator + "uploadedExcel";  File file = new File(base);  if(!file.exists()){   file.mkdirs();  }  try{   String path=base + File.separator + sourceName;   multipartFile.transferTo(new File(path));   //service.insert("insertAttachment", attach);   //上传成功后读取Excel表格里面的数据   System.out.println("路径是"+path);   File read=new File(path);Map<String, CollectiveSettle> map = ImportExcel.readExcelFile(path);   int flag=ParseMap.updateMap(map, proId, proName, settleType,service);   if(1==flag){    response.getWriter().print("{success:true}");   }else{    response.getWriter().print("{success:false}");   }  }catch (Exception e) {   try {    response.getWriter().print("{success:false}");   } catch (IOException e1) {    e1.printStackTrace();   }  } }</span>

2.调用方法

<span style="font-size:18px;">import java.io.FileInputStream;import java.text.SimpleDateFormat;import java.util.HashMap;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.ldacms.domain.proCol.CollectiveSettle;import com.ldacms.domain.proCol.DelayTransition;import com.ldacms.util.PrimaryUtils;import org.apache.poi.ss.usermodel.Row;public class ImportExcel {// 判断一个字符串是否是整数public static boolean isDig(String number) {try {Double.valueOf(number);return true;} catch (Exception e) {return false;}}// 判断是否为整数jiangzeyun 14-10-31 addpublic static boolean isEqual2(String num) {try {Double.valueOf(num);return true;} catch (Exception e) {return false;}}public static String readValue(Cell cell) {String value = "";if (cell == null) {return "NULL";}int rowType = cell.getCellType();if (rowType == Cell.CELL_TYPE_STRING) {value = cell.getStringCellValue();} else if (rowType == Cell.CELL_TYPE_NUMERIC) {if (HSSFDateUtil.isCellDateFormatted(cell)) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String date = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();value = date.toString();} else {Number v = cell.getNumericCellValue();// 如果是身份证(第三列)（因为身份证全是数字）则把小数部分去掉if (cell.getColumnIndex() == 3) {double va = Double.valueOf(v.toString());int val = (int) va;value = String.valueOf(val);} else {value = v.toString();}}}return value;}// 读取单元格的数据,把一行行的数据保存到map中，用map来过滤重复身份证的数据public static Map<String, CollectiveSettle> readExcelFile(String fileName) {// 创建对Excel工作薄文件的引用HSSFWorkbook workbook = null;HashMap<String, CollectiveSettle> map = new HashMap<String, CollectiveSettle>();try {FileInputStream in = new FileInputStream(fileName);Workbook wb;if (fileName.endsWith(".xls")) {// Excel2003wb = new HSSFWorkbook(in);} else {// Excel 2007wb = new XSSFWorkbook(in);}// 创建对工作表的引用Sheet sheet = wb.getSheetAt(0);// 遍历所有单元格，读取单元格int row_num = sheet.getLastRowNum();// 项目名称String proName = "";for (int i = 1; i < row_num; i++) {Row row = sheet.getRow(i);// 获得项目名称if (1 == i) {proName = readValue(row.getCell(0));} else if (i > 2) {// 取得第0个单元格的值String zeroValue = readValue(row.getCell(0));if ("NULL".equals(zeroValue) || !isEqual2(zeroValue)) {break;} else {// 得到每一行的单元格CollectiveSettle settle = new CollectiveSettle();// 生成pmrowId和treeIdsettle.setPmrowId(PrimaryUtils.generateGuid());settle.setTreeId(settle.getPmrowId());settle.setProName(proName);settle.setMname(readValue(row.getCell(1)));// 身份证settle.setMcardId(readValue(row.getCell(3)));// 与户主的关系settle.setRelationship(readValue(row.getCell(4)));// pname;户主名称settle.setPname(readValue(row.getCell(5)));// 货币补偿String money = readValue(row.getCell(8));if (money.equals("NULL") || money == null|| money == "") {settle.setPayMoney(0.0);} else {settle.setPayMoney(Double.valueOf(money));}settle.setRemark(readValue(row.getCell(10)));map.put(readValue(row.getCell(3)), settle);}}}} catch (Exception e) {e.printStackTrace();} finally {}return map;}// 读取单元格的数据,把一行行的数据保存到map中，用map来过滤重复身份证的数据public static Map<String, CollectiveSettle> readHouseExcelFile(String fileName) {// 创建对Excel工作薄文件的引用HSSFWorkbook workbook = null;HashMap<String, CollectiveSettle> map = new HashMap<String, CollectiveSettle>();try {FileInputStream in = new FileInputStream(fileName);Workbook wb;if (fileName.endsWith(".xls")) {// Excel2003wb = new HSSFWorkbook(in);} else {// Excel 2007wb = new XSSFWorkbook(in);}// 创建对工作表的引用Sheet sheet = wb.getSheetAt(0);// 遍历所有单元格，读取单元格int row_num = sheet.getLastRowNum();// 项目名称String proName = "";for (int i = 1; i < row_num; i++) {Row row = sheet.getRow(i);// 获得项目名称if (1 == i) {proName = readValue(row.getCell(0));} else if (i > 2) {// 取得第0个单元格的值String zeroValue = readValue(row.getCell(0));if ("NULL".equals(zeroValue)) {break;} else {// 得到每一行的单元格CollectiveSettle settle = new CollectiveSettle();// 生成pmrowId和treeIdsettle.setPmrowId(PrimaryUtils.generateGuid());settle.setTreeId(settle.getPmrowId());settle.setProName(proName);settle.setMname(readValue(row.getCell(1)));// 身份证settle.setMcardId(readValue(row.getCell(3)));// 与户主的关系settle.setRelationship(readValue(row.getCell(4)));// pname;户主名称settle.setPname(readValue(row.getCell(5)));// 选房号settle.setChooseHouse(readValue(row.getCell(9)));settle.setRemark(readValue(row.getCell(11)));map.put(readValue(row.getCell(3)), settle);}}}} catch (Exception e) {e.printStackTrace();} finally {}return map;}/********************************************* 过度延期费用Excel表格读取 ***************************************************************/// 读取单元格的数据,把一行行的数据保存到map中，用map来过滤重复身份证的数据public static Map<String, DelayTransition> readDelayTranExcelFile(String fileName) {// 创建对Excel工作薄文件的引用HSSFWorkbook workbook = null;HashMap<String, DelayTransition> map = new HashMap<String, DelayTransition>();try {FileInputStream in = new FileInputStream(fileName);Workbook wb;if (fileName.endsWith(".xls")) {// Excel2003wb = new HSSFWorkbook(in);} else {// Excel 2007wb = new XSSFWorkbook(in);}// 创建对工作表的引用Sheet sheet = wb.getSheetAt(1);// 遍历所有单元格，读取单元格int row_num = sheet.getLastRowNum();// 项目名称String proName = "";System.out.println("总行数---" + row_num);for (int i = 0; i < row_num; i++) {Row row = sheet.getRow(i);// 获得项目名称if (0 == i) {proName = readValue(row.getCell(0));System.out.println(proName);} else if (i > 3)// 取得第0个单元格的值// String zeroValue=readValue(row.getCell(0));{String zeroValue = readValue(row.getCell(0));System.out.println("第一个数字式:" + zeroValue);if (isDig(zeroValue)) {// 得到每一行的单元格DelayTransition delayTran = new DelayTransition();// 生成pmrowId和treeIddelayTran.setPmrowId(PrimaryUtils.generateGuid());delayTran.setTreeId(delayTran.getPmrowId());delayTran.setProName(proName);delayTran.setPname(readValue(row.getCell(1)));delayTran.setMcardId(readValue(row.getCell(2)));// 这里的问题主要是有些是用Excel中的计算出来的，导入时候出现问题// System.out.println("户主"+readValue(row.getCell(1))+"身份证号码"+readValue(row.getCell(2))+"补偿面积"+readValue(row.getCell(3)));System.out.println("第" + i + "行" + "第三列----------"+ readValue(row.getCell(3)));delayTran.setCompeneArea(Double.valueOf(readValue(row.getCell(3))).doubleValue());delayTran.setDownDate(readValue(row.getCell(4)));// System.out.println(readValue(row.getCell(5)));// 先将String转化成float，然后再强制转化成整型System.out.println("应安置总人" + readValue(row.getCell(5))+ "货币安置人数" + readValue(row.getCell(6))+ "已住房安置人数" + readValue(row.getCell(7))+ "合计已安置人数" + readValue(row.getCell(8)));delayTran.setShouldPaceCount((int) Double.parseDouble(readValue(row.getCell(5))));delayTran.setMoneyPaceCount((int) Double.parseDouble(readValue(row.getCell(6))));delayTran.setHousePaceCount((int) Double.parseDouble(readValue(row.getCell(7))));int alreadyPaceCount = (int) Double.parseDouble(readValue(row.getCell(6)))+ (int) Double.parseDouble(readValue(row.getCell(7)));delayTran.setAlreadyPaceCount(alreadyPaceCount);int notPaceCount = (int) Double.parseDouble(readValue(row.getCell(5)))- alreadyPaceCount;System.out.println(notPaceCount);delayTran.setNotPaceCount(notPaceCount);// 这里需要将string转换成doubule类型double compeneArea = Double.valueOf(readValue(row.getCell(3))).doubleValue();int shouldPaceCount = (int) Double.parseDouble(readValue(row.getCell(5)));double area = (compeneArea / shouldPaceCount)* notPaceCount;delayTran.setArea(area);double monthPayMoney = 12 * area;delayTran.setMonthPayMoney(monthPayMoney);double thisPayMoney = monthPayMoney * 3;delayTran.setThisPayMoney(thisPayMoney);delayTran.setRemark(readValue(row.getCell(13)));// 过滤身份证信息map.put(readValue(row.getCell(2)), delayTran);} else {break;}}}} catch (Exception e) {e.printStackTrace();} finally {}return map;}public static void main(String[] args) {}}</span>