Poi导入excel

桃扇骨 2022-04-08 19:42 461阅读 0赞

1.jsp页面

  1. <form action="$\{pageContext.request.contextPath\}/admin/uploadexcel" method="post" enctype="multipart/form-data" id="form2" οnsubmit="return check();">  
  2.     <table style="table-layout: fixed;" class="tablelist tablelistc">  
  3.         <tr>  
  4.             <td colspan="3" style="text-align: center;">  
  5.                 <!-- <input id="upfile" type="file" name="upfile"> -->  
  6.                 <input id="upfile" type="file" name="file">  
  7.             </td>  
  8.         </tr>  
  9.         <tr>  
  10.             <td colspan="3" style="text-align: center;">  
  11.                  <a href="downhouseexcel?excelName=houseowner">下载Excel导入模版</a><br>  
  12.             </td>  
  13.         </tr>  
  14.         <tr>  
  15.             <td colspan="3">  
  16.                 <input type="button" name="btn" id="btn" class="share-button-style" value="导入Excel" />  
  17.             </td>  
  18.         </tr>  
  19.     </table>  
  20.    </form>

2.js

  1. //ajax 方式上传文件操作    
  2.     $(document).ready(function()\{    
  3.        $('\#btn').click(function()\{   
  4.            showMask();  
  5.            $('\#btn').attr('disabled',"true");  
  6.            var $this = $(this).val('导入中...');  
  7.            if(checkData())\{    
  8.                $('\#form2').ajaxSubmit(\{      
  9.                    url:'ajaxUploadHouseOwnerExcel',    
  10.                    dataType: 'text',    
  11.                    success: resutlMsg,    
  12.                    error: errorMsg    
  13.                \});     
  14.                function resutlMsg(msg)\{    
  15.                    alert(msg);  
  16.                    hideMask();  
  17.                    $('\#btn').removeAttr("disabled");  
  18.                    $("\#upfile").val("");    
  19.                    $this.val('导入Excel');  
  20.                    close\_window('importhouseownerdata');  
  21.                    $(".bodyid").mask("正在加载中...");                
  22.                     $("\#formid").submit();   
  23.                \}    
  24.                function errorMsg()\{     
  25.                    alert("服务正忙！");   
  26.                    hideMask();  
  27.                    $('\#btn').removeAttr("disabled");  
  28.                    $this.val('导入Excel');  
  29.                \}    
  30.            \}    
  31.        \});    
  32.     \});    
  34.     //JS校验form表单信息    
  35.     function checkData()\{    
  36.        var fileDir = $("\#upfile").val();    
  37.        var suffix = fileDir.substr(fileDir.lastIndexOf("."));    
  38.        if("" == fileDir)\{    
  39.            alert("选择需要导入的Excel文件！");  
  40.            hideMask();  
  41.            $('\#btn').removeAttr("disabled");  
  42.            $('\#btn').val('导入Excel');  
  43.            return false;    
  44.        \}    
  45.        if(".xls" != suffix && ".xlsx" != suffix )\{    
  46.            alert("选择Excel格式的文件导入！");  
  47.            hideMask();  
  48.            $('\#btn').removeAttr("disabled");  
  49.            $('\#btn').val('导入Excel');  
  50.            return false;    
  51.        \}    
  52.        return true;    
  53.     \}

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(); 

  1.     MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  
  2.     InputStream in=null;  
  3.     MultipartFile file = multipartRequest.getFile("file");  
  4.      if(file.isEmpty())\{    
  5.             throw new Exception("文件不存在！");  
  6.         \}  
  7.      in = file.getInputStream();  
  8.      Workbook work = new ImportExcelUtil().getWorkbook(in,file.getOriginalFilename());  
  10.     List<TblHouseOwner> houseownerList = new ArrayList<TblHouseOwner>();  
  11.     TblHouseOwner houseOwner = null;  
  12.     int count = 0;// 业主导入数量  
  13.     // 得到一个工作表  
  14.     Sheet sheet = work.getSheetAt(0);  
  15.     // 获得表头  
  16.     //Row rowHead = sheet.getRow(0);  
  17.     // 获得数据的总行数  
  18.     int totalRowNum = sheet.getLastRowNum();  
  19.     // 获得所有数据  
  20.     for (int i = 1; i <= totalRowNum; i++) \{  
  21.         // 获得第i行对象  
  22.         Row row = sheet.getRow(i);  
  23.         if(row.getPhysicalNumberOfCells()!=9)//每一行得到列数  
  24.             continue;  
  25.         HSSFDataFormatter dataFormatter = new HSSFDataFormatter();//设置读取小数为整数  
  26.         // 获得获得第i行第0列的 String类型对象  
  27.         Cell cell = row.getCell((short) 0);  
  28.         if(cell.toString().trim().equals("")||cell==null)continue;  
  29.         houseOwner = new  TblHouseOwner();  
  30.         houseOwner.setOwnerid(WebUtil.getRandUserId());  
  31.         houseOwner.setOwnername(cell.toString());  
  32.         cell = row.getCell((short) 1);  
  33.         if(!isNumeric(cell)||cell==null)continue;  
  34.         houseOwner.setOwnerphone(cell.toString());  
  35.         cell = row.getCell((short) 2);  
  36.         if(!isNumeric(cell)||cell==null)continue;  
  37.         houseOwner.setIdcard(cell.toString());  
  38.         cell = row.getCell((short) 3);  
  39.         if(!isNumeric(cell)||cell==null)continue;  
  40.         houseOwner.setVid(cell.toString());  
  41.         cell = row.getCell((short) 4);  
  42.         if(!isNumeric(cell)||cell==null)continue;  
  43.         houseOwner.setBuildcode(cell.toString());  
  44.         cell = row.getCell((short) 5);  
  45.         if(!isNumeric(cell))continue;  
  46.         houseOwner.setUnitcode(cell.toString());  
  47.         cell = row.getCell((short) 6);  
  48.         if(!isNumeric(cell))continue;  
  49.         houseOwner.setHousecode(cell.toString());  
  50.         cell = row.getCell((short) 7);  
  51.         if(!isNumeric(cell))continue;  
  52.         houseOwner.setHouseareas(cell.toString());  
  53.         cell = row.getCell((short) 8);  
  54.         if(!isNumeric(cell))continue;  
  55.         houseOwner.setUnitprice(cell.toString());  
  56.         houseOwner.setMonthpayfee(WebUtil.monthPayfee(houseOwner.getHouseareas(), houseOwner.getUnitprice()));  
  57.         houseOwner.setYearpayfee(WebUtil.totalPayfee(houseOwner.getHouseareas(), houseOwner.getUnitprice()));  
  58.         houseOwner.setHousebilldate(WebUtil.getCurrentMonthOneNum());// 获取当月一号  
  59.         houseownerList.add(houseOwner);  
  60.     \}  
  61.     if (houseownerList != null && houseownerList.size() > 0) \{  
  62.         if (houseownerList.size() > 3000) \{  
  63.             out.print("很抱歉，您本次导入的业主人数是" + houseownerList.size()+ "人，系统目前最大支持导入3000条数据！");  
  64.             out.flush();  
  65.             out.close();  
  66.             //inputStream.close();  
  67.             return;  
  68.         \}  
  69.         // 批量插入数据库  
  70.         baseDao.insertBatchDate(houseownerList);  
  71.         count=houseownerList.size();  
  72.     \}else \{  
  73.         out.flush();  
  74.         out.close();  
  75.         //inputStream.close();  
  76.         return;  
  77.     \}  
  79.     houseownerList = null;  
  81.     out.print("导入成功！本次共导入业主" + count + "人");  
  82.     out.flush();  
  83.     out.close();  
  84. \}

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 

  1. /\*\*   
  2.  \* 描述：获取IO流中的数据，组装成List<List<Object>>对象   
  3.  \* @param in,fileName   
  4.  \* @throws IOException    
  5.  \*//\*    
  6. public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception\{    
  7.     List<List<Object>> list = null;    
  9.     POIFSFileSystem fs = new POIFSFileSystem(in);  
  10.     HSSFWorkbook work = new HSSFWorkbook(fs);  
  11.     HSSFSheet sheet= null;   
  12.     HSSFRow row=null;  
  13.     Cell cell = null;    
  14.     //户主 电话    身份证 有无房屋    房屋缴费类型    房屋面积(m²)    房屋单价：m²/元    有无车位    车位费    车位缴费类型    单元(栋)
  16.     list = new ArrayList<List<Object>>();   
  17.     //遍历Excel中所有的sheet   
  18.     for (int i = 0; i < work.getNumberOfSheets(); i++) \{    
  19.         sheet = work.getSheetAt(i);    
  20.         if(sheet==null)\{continue;\}    
  21.         //遍历当前sheet中的所有行 从第6行开始遍历  
  22.         for (int j = 6; j <=sheet.getLastRowNum(); j++)\{    
  23.             row = sheet.getRow(j);    
  24.             //遍历所有的列  row.getFirstCellNum()  
  25.             List<Object> li = new ArrayList<Object>();   
  26.             for (int y =0 ; y < row.getLastCellNum(); y++) \{    
  27.                 if(y>=15) break;  
  28.                 cell = row.getCell(y);  
  29.                 if(cell!=null)  
  30.                 \{  
  31.                     cell.setCellType(Cell.CELL\_TYPE\_STRING);//设置列值类型  
  32.                     if(StringUtils.isEmpty(cell.getStringCellValue().trim().replaceAll(" ", "")))  
  33.                     \{  
  34.                         cell.setCellValue("0");  
  35.                     \}else  
  36.                     \{  
  37.                         cell.setCellValue(cell.getStringCellValue());  
  38.                     \}  
  39.                     li.add(this.getCellValue(cell));  
  40.                 \}  
  41.                 cell=null;  
  42.             \}  
  44.             if(li.size()>=15)  
  45.             \{  
  46.                 list.add(li);  
  47.             \}  
  48.         \}    
  49.     \}    
  50.     //work.close();    
  51.     return list;    
  52. \} \*/  
  56. /\*\*   
  57.  \* 描述：根据文件后缀，自适应上传文件的版本    
  58.  \* @param inStr,fileName   
  59.  \* @return   
  60.  \* @throws Exception   
  61.  \*/    
  62. public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception\{    
  63.     Workbook wb = null;    
  64.     String fileType = fileName.substring(fileName.lastIndexOf("."));    
  65.     if(excel2003L.equals(fileType))\{    
  66.         wb = new HSSFWorkbook(inStr);  //2003-    
  67.     \}else if(excel2007U.equals(fileType))\{    
  68.         wb = new XSSFWorkbook(inStr);  //2007+    
  69.     \}else\{    
  70.         throw new Exception("解析的文件格式有误！");    
  71.     \}    
  72.     return wb;    
  73. \}   
  75. /\*\*   
  76.  \* 描述：对表格中数值进行格式化   
  77.  \* @param cell   
  78.  \* @return   
  79.  \*/    
  80. public  Object getCellValue(Cell cell)\{    
  81.     Object value = null;    
  82.     DecimalFormat df = new DecimalFormat("0");  //格式化number String字符    
  83.     SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化    
  84.     DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字    
  86.     switch (cell.getCellType()) \{    
  87.     case Cell.CELL\_TYPE\_STRING:    
  88.         value = cell.getRichStringCellValue().getString();    
  89.         break;    
  90.     case Cell.CELL\_TYPE\_NUMERIC:    
  91.         if("General".equals(cell.getCellStyle().getDataFormatString()))\{    
  92.             value = df.format(cell.getNumericCellValue());    
  93.         \}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString()))\{    
  94.             value = sdf.format(cell.getDateCellValue());    
  95.         \}else\{    
  96.             value = df2.format(cell.getNumericCellValue());    
  97.         \}    
  98.         break;    
  99.     case Cell.CELL\_TYPE\_BOOLEAN:    
  100.         value = cell.getBooleanCellValue();    
  101.         break;    
  102.     case Cell.CELL\_TYPE\_BLANK:    
  103.         value = "";    
  104.         break;    
  105.     default:    
  106.         break;    
  107.     \}    
  108.     return value;    
  109. \}   
  111. public static void main(String\[\] args) \{
  113. \}

}

发表评论

表情：
评论列表 （有 0 条评论，461人围观）

还没有评论，来说两句吧...

相关阅读

    相关 poi批量导入excel

    1.先下载poi依赖的包，复制张贴代码一定没有错误 2.直接上代码，如果导入有错误，可能是别人给你发的excel模版有错误，可以自己创建一个excel测试       

    左手的ㄟ右手左手的ㄟ右手/ 0 赞/ 486 阅读