Poi导入excel

最新推荐文章于 2024-04-24 13:17:14 发布

hexu_blog

最新推荐文章于 2024-04-24 13:17:14 发布

阅读量240

点赞数

文章标签： poi excel导入数据

本文链接：https://blog.csdn.net/hexu_blog/article/details/85091694

版权

1.jsp页面

<form action="${pageContext.request.contextPath}/admin/uploadexcel" method="post" enctype="multipart/form-data" id="form2" οnsubmit="return check();">
       <table style="table-layout: fixed;" class="tablelist tablelistc">
           <tr>
               <td colspan="3" style="text-align: center;">
               
               <input id="upfile" type="file" name="file">
               </td>
           </tr>
           <tr>
               <td colspan="3" style="text-align: center;">
               <a href="downhouseexcel?excelName=houseowner">下载Excel导入模版</a><br>
               </td>
           </tr>
           <tr>
               <td colspan="3">
                   <input type="button" name="btn" id="btn" class="share-button-style" value="导入Excel" />
               </td>
           </tr>
       </table>
   </form>

2.js

   //ajax 方式上传文件操作
   $(document).ready(function(){
   $('#btn').click(function(){
       showMask();
       $('#btn').attr('disabled',"true");
       var $this = $(this).val('导入中...');
   if(checkData()){
   $('#form2').ajaxSubmit({
   url:'ajaxUploadHouseOwnerExcel',
   dataType: 'text',
   success: resutlMsg,
   error: errorMsg
   });
   function resutlMsg(msg){
   alert(msg);
   hideMask();
   $('#btn').removeAttr("disabled");
   $("#upfile").val("");
   $this.val('导入Excel');
   close_window('importhouseownerdata');
   $(".bodyid").mask("正在加载中...");
       $("#formid").submit();
   }
   function errorMsg(){
   alert("服务正忙！");
   hideMask();
   $('#btn').removeAttr("disabled");
   $this.val('导入Excel');
   }
   }
   });
   });

       //JS校验form表单信息
   function checkData(){
   var fileDir = $("#upfile").val();
   var suffix = fileDir.substr(fileDir.lastIndexOf("."));
   if("" == fileDir){
   alert("选择需要导入的Excel文件！");
   hideMask();
   $('#btn').removeAttr("disabled");
   $('#btn').val('导入Excel');
   return false;
   }
   if(".xls" != suffix && ".xlsx" != suffix ){
   alert("选择Excel格式的文件导入！");
   hideMask();
   $('#btn').removeAttr("disabled");
   $('#btn').val('导入Excel');
   return false;
   }
   return true;
   }

3.controller层

@SuppressWarnings("unchecked")
   @RequestMapping(value = "/admin/ajaxUploadHouseOwnerExcel", method = {RequestMethod.GET, RequestMethod.POST })
   public void importHouseOwner(
           HttpServletRequest request,HttpServletResponse response,HttpSession session)
                   throws Exception {
       response.setCharacterEncoding("utf-8");// 防止ajax接受到的中文信息乱码
       PrintWriter out = response.getWriter();

       MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
       InputStream in=null;
       MultipartFile file = multipartRequest.getFile("file");
       if(file.isEmpty()){
   throw new Exception("文件不存在！");
   }
       in = file.getInputStream();
       Workbook work = new ImportExcelUtil().getWorkbook(in,file.getOriginalFilename());

       List<TblHouseOwner> houseownerList = new ArrayList<TblHouseOwner>();
       TblHouseOwner houseOwner = null;
       int count = 0;// 业主导入数量
       // 得到一个工作表
       Sheet sheet = work.getSheetAt(0);
       // 获得表头
       //Row rowHead = sheet.getRow(0);
       // 获得数据的总行数
       int totalRowNum = sheet.getLastRowNum();
       // 获得所有数据
       for (int i = 1; i <= totalRowNum; i++) {
           // 获得第i行对象
           Row row = sheet.getRow(i);
           if(row.getPhysicalNumberOfCells()!=9)//每一行得到列数
               continue;
           HSSFDataFormatter dataFormatter = new HSSFDataFormatter();//设置读取小数为整数
           // 获得获得第i行第0列的 String类型对象
           Cell cell = row.getCell((short) 0);
           if(cell.toString().trim().equals("")||cell==null)continue;
           houseOwner = new TblHouseOwner();
           houseOwner.setOwnerid(WebUtil.getRandUserId());
           houseOwner.setOwnername(cell.toString());
           cell = row.getCell((short) 1);
           if(!isNumeric(cell)||cell==null)continue;
           houseOwner.setOwnerphone(cell.toString());
           cell = row.getCell((short) 2);
           if(!isNumeric(cell)||cell==null)continue;
           houseOwner.setIdcard(cell.toString());
           cell = row.getCell((short) 3);
           if(!isNumeric(cell)||cell==null)continue;
           houseOwner.setVid(cell.toString());
           cell = row.getCell((short) 4);
           if(!isNumeric(cell)||cell==null)continue;
           houseOwner.setBuildcode(cell.toString());
           cell = row.getCell((short) 5);
           if(!isNumeric(cell))continue;
           houseOwner.setUnitcode(cell.toString());
           cell = row.getCell((short) 6);
           if(!isNumeric(cell))continue;
           houseOwner.setHousecode(cell.toString());
           cell = row.getCell((short) 7);
           if(!isNumeric(cell))continue;
           houseOwner.setHouseareas(cell.toString());
           cell = row.getCell((short) 8);
           if(!isNumeric(cell))continue;
           houseOwner.setUnitprice(cell.toString());
           houseOwner.setMonthpayfee(WebUtil.monthPayfee(houseOwner.getHouseareas(), houseOwner.getUnitprice()));
           houseOwner.setYearpayfee(WebUtil.totalPayfee(houseOwner.getHouseareas(), houseOwner.getUnitprice()));
           houseOwner.setHousebilldate(WebUtil.getCurrentMonthOneNum());// 获取当月一号
           houseownerList.add(houseOwner);
       }
       if (houseownerList != null && houseownerList.size() > 0) {
           if (houseownerList.size() > 3000) {
               out.print("很抱歉，您本次导入的业主人数是" + houseownerList.size()+ "人，系统目前最大支持导入3000条数据！");
               out.flush();
               out.close();
               //inputStream.close();
               return;
           }
           // 批量插入数据库
           baseDao.insertBatchDate(houseownerList);
           count=houseownerList.size();
       }else {
           out.flush();
           out.close();
           //inputStream.close();
           return;
       }

       houseownerList = null;

       out.print("导入成功！本次共导入业主" + count + "人");
       out.flush();
       out.close();
   }

4.工具类

package com.estate.tools;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ImportExcelUtil {
   private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel



/**
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
* @param in,fileName
* @throws IOException
*//*
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;

POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook work = new HSSFWorkbook(fs);
HSSFSheet sheet= null;
HSSFRow row=null;
Cell cell = null;
//户主电话   身份证有无房屋   房屋缴费类型   房屋面积(m²)   房屋单价：m²/元   有无车位   车位费   车位缴费类型   单元(栋)

list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行从第6行开始遍历
for (int j = 6; j <=sheet.getLastRowNum(); j++){
   row = sheet.getRow(j);
//遍历所有的列 row.getFirstCellNum()
List<Object> li = new ArrayList<Object>();
for (int y =0 ; y < row.getLastCellNum(); y++) {
   if(y>=15) break;
   cell = row.getCell(y);
   if(cell!=null)
   {
       cell.setCellType(Cell.CELL_TYPE_STRING);//设置列值类型
       if(StringUtils.isEmpty(cell.getStringCellValue().trim().replaceAll(" ", "")))
       {
           cell.setCellValue("0");
       }else
       {
           cell.setCellValue(cell.getStringCellValue());
       }
       li.add(this.getCellValue(cell));
   }
   cell=null;
}

if(li.size()>=15)
{
   list.add(li);
}
}
}
//work.close();
return list;
} */



/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误！");
}
return wb;
}

/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}

   public static void main(String[] args) {