Java 上传读取Excel文件

最新推荐文章于 2024-06-29 02:51:39 发布
最新推荐文章于 2024-06-29 02:51:39 发布
阅读量1.1k 收藏
点赞数
分类专栏： Java进阶 文章标签： java servlet 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/x1043556915/article/details/127052504
版权

Web中導入Excel文件

ExtJs前端代碼:

//=========上傳Excel=============================

uploadPanel =  new Ext.form.FormPanel({

            fileUpload: true,

            id:'fileUploadForm',

            frame:true,

            labelAlign:'right',

            buttonAlign:'center',

            labelWidth: 60,

            items: [{

                xtype: 'textfield',

                fieldLabel: '文件',

                id: 'file',

                inputType: 'file'

            }],

           buttons: [{

              text:'提交',

              id:'tijiao',

              handler:function(){

                 var src=Ext.getCmp('file').getValue();

                 var filetype=src.split('.');

                 if(filetype[filetype.length-1].toLowerCase()!='xls'){

                    Ext.Msg.alert('提示','請選擇Excel文件!',function(){

                             uploadPanel.getForm().getEl().dom.reset();

                             Ext.getCmp('file').focus();

                       });

                    return;

                 }

                 if(src==''){

                    Ext.getCmp('file').focus();

                    return;

                 }

                 uploadPanel.getForm().submit({

                    method : 'post',

                    url:'paAction.do?actionType=uploadExcel',

                    waitTitle: '請稍後',

                    waitMsg: '正在導入 ...',

                    success : function(form, action){

                             if(action.result.success=='true'){

                                 Ext.Msg.alert('提示','導入成功!',function(){

                                           uploadPanel.getForm().getEl().dom.reset();

                                           win4.hide();

                                           store1.removeAll();

                                           store1.reload();

                                           win.show();

                                        });

                             }else{

                               Ext.Msg.alert('提示','導入失敗!');

                             }

                      },

                    failure: function(form, action){

                               Ext.Msg.alert('提示','導入失敗!');

                      }

                  });

             }

           },{

              text: '關閉',

              handler:function(){

                     win4.hide();

            }

        }]

    });

    var win4= new Ext.Window({

            title:'輸入/導入數據',

            width: 350,

            height:150,

            layout: 'fit',

            shadow:false,

            plain:true,

            bodyStyle:'padding:5px;',

            closeAction:'hide',

            items:uploadPanel

        });

    win4.on('hide',function(){

            uploadPanel.getForm().getEl().dom.reset();

    });

java後臺代碼:

  public ActionForward uploadExcel(ActionMapping actionMapping,       

                                      ActionForm actionForm,

                                      HttpServletRequest httpServletRequest,

                                      HttpServletResponse httpServletResponse) throws

         IOException, ServletException, SQLException, Exception {

       String user_id = httpServletRequest.getSession().getAttribute("user_id").

           toString();

       String ip = httpServletRequest.getRemoteHost();

       ArrayList plans = new ArrayList();

       String newFullNewPath = "";

       String returnvalue = "";

       boolean success = true;

       uploadForm fileForm = (uploadForm) actionForm;

       //System.out.println(fileForm);

       FormFile file = fileForm.getFile();

       String filePath = httpServletRequest.getSession().getServletContext().

           getRealPath("/");

       try {

         newFullNewPath = filePath + "\\Excel\\";

         File f = new File(newFullNewPath);

         if (!f.isDirectory()) {

           f.mkdirs();

         }

         newFullNewPath = newFullNewPath + file.getFileName().toString();

         InputStream is = file.getInputStream();

         FileOutputStream foutput = new FileOutputStream(new File(newFullNewPath), false);

         BufferedOutputStream bos = new BufferedOutputStream(foutput);

         int len = 0;

         byte[] buffer = new byte[8192];

         while ( (len = is.read(buffer, 0, 8192)) != -1) {

           bos.write(buffer, 0, len);

         }

         is.close();

         bos.close();

         foutput.close();

       }

       catch (FileNotFoundException e) {

         success = false;

         e.printStackTrace();

       }

       catch (IOException e) {

         success = false;

         e.printStackTrace();

       }

       plans = readExcel(newFullNewPath);

       success = insert(plans, user_id,ip);

       file.destroy();

       File ff = new File(newFullNewPath);

       ff.delete();

       returnvalue = "{success:'" + success + "',oper_id:'" + user_id + "'}";

       try {

         httpServletResponse.setContentType("text/html; charset=utf-8");

         httpServletResponse.getWriter().write(returnvalue);

       }

       catch (IOException ex) {

         ex.printStackTrace();

       }

       return actionMapping.findForward("");

}

//讀取Excel文檔(excel2003)

 protected ArrayList readExcel(String filePath) {

     ArrayList pm_dn_plan_s = new ArrayList();

     ArrayList pm_dn_plandata = null;

     Workbook rwb = null;

     Sheet rs = null;

     try {

       InputStream is = new FileInputStream(filePath);

       rwb = Workbook.getWorkbook(is);

       //封裝明細數據

       rs = rwb.getSheet(0);

       for (int i = 1; i < rs.getRows(); i++) {

         pm_dn_plandata = new ArrayList();

         pm_dn_plandata.add(rs.getCell(0, i).getContents().trim());

         pm_dn_plandata.add(rs.getCell(1, i).getContents().trim());

         if(rs.getCell(2, i).getType()==CellType.NUMBER|| rs.getCell(2, i).getType()==CellType.NUMBER_FORMULA){

               DecimalFormat df = new DecimalFormat("###.0000");

               NumberCell nc=(NumberCell)rs.getCell(2, i);

               pm_dn_plandata.add(df.format(nc.getValue()));

           }else {

             pm_dn_plandata.add(rs.getCell(2, i).getContents().trim());

           }

          pm_dn_plandata.add(rs.getCell(3, i).getContents().trim());

          pm_dn_plandata.add(rs.getCell(4, i).getContents().trim());

          pm_dn_plandata.add(rs.getCell(5, i).getContents().trim());

         pm_dn_plan_s.add(pm_dn_plandata);

       }

       is.close();

       rwb.close();

     }

     catch (Exception e) {

       e.printStackTrace();

     }

     return pm_dn_plan_s;

   }

//插入臨時表

   public boolean insert(ArrayList Dn_tongzhi_s, String user_id,String ip) throws

         IOException, ServletException, SQLException, Exception {

       boolean returnvalue = true;

       Connection conn = null;

       Statement stmt = null;

       ResultSet rs = null;

       DataSource datasource = null;

       ServletContext context = null;

       context = servlet.getServletContext();

       datasource = (DataSource) context.getAttribute(Globals.DATA_SOURCE_KEY);

       conn = datasource.getConnection();

       conn.setAutoCommit(false);

       ArrayList evdata=null;

       //定義變量,用來判斷當為空字符串的時候,整形的賦值為0

       String evdata2="";

       //插入表頭

       stmt = conn.createStatement();

       String sqlString ="";

         try {

             //插入明細表

         stmt.addBatch("delete pa_data_temp where oper_id='"+user_id+"' and ip='"+ip+"'");

         for (int i = 0; i < Dn_tongzhi_s.size(); i++) {

           evdata = new ArrayList();

           evdata = (ArrayList) Dn_tongzhi_s.get(i);

           if (evdata.get(2).equals(""))

          {

            evdata2="0";

          }else{

            evdata2=evdata.get(2).toString();

          }

           sqlString ="insert into pa_data_temp(mat_id,mat_name,qty,deliver_by,used,notes,oper_id,ip) values('"+

                         evdata.get(0)+"','"+ evdata.get(1)+"','"+ evdata2+"','"+ evdata.get(3)+

                        "','"+ evdata.get(4)+"','"+ evdata.get(5)+"','"+ user_id+"','"+ ip+"')";

               //  System.out.println(sqlString);

                 stmt.addBatch(sqlString);

         }

         stmt.executeBatch();

        conn.commit();

         conn.setAutoCommit(true);

       }

       catch (SQLException e) {

         returnvalue = false;

         conn.rollback();

         System.err.println(e.getMessage());

         conn.setAutoCommit(true);

       }

       finally {

         if (rs != null) {

           try {

             rs.close();

           }

           catch (SQLException e) {

             rs = null;

           }

         }

         if (stmt != null) {

           try {

             stmt.close();

           }

           catch (SQLException e) {

             stmt = null;

           }

         }

         if (conn != null) {

           try {

             conn.close();

           }

           catch (SQLException e) {

             conn = null;

           }

         }

       }

       return returnvalue;

   }

//插入正式表

   public ActionForward insert_zhengshi(ActionMapping actionMapping,

                                      ActionForm actionForm,

                                      HttpServletRequest httpServletRequest,

                                      HttpServletResponse httpServletResponse) throws

          IOException, ServletException, SQLException {

        HashMap maplist = new HashMap();

        String returnvalue = "";

        boolean success = true;

        String sqlString ="";

        String ip = httpServletRequest.getRemoteHost();

        String user_id = httpServletRequest.getSession().getAttribute("user_id").

         toString();

         String json1 = java.net.URLDecoder.decode(httpServletRequest.getParameter("json"), "utf-8").replace('"', ' ').trim();

         json1 = java.net.URLDecoder.decode(json1, "utf-8").replace('"', ' ').trim();

        //System.out.println(json1);       

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        DataSource datasource = null;

        ServletContext context = null;

        context = servlet.getServletContext();

        datasource = (DataSource) context.getAttribute(Globals.DATA_SOURCE_KEY);

        conn = datasource.getConnection();

        stmt = conn.createStatement();

        conn.setAutoCommit(false);

        try {

          ArrayList list = json.jsonstrToarraylist(json1);

          String mat_name="";

          String used="";

          String notes="";

          for (int i = 0; i < list.size(); i++) {

            maplist = (HashMap) list.get(i);

            mat_name=json.toTeshuStr(maplist.get("pmat_name").toString().replaceAll("'", "").trim());

            used=json.toTeshuStr(maplist.get("used").toString().replaceAll("'", "").trim());

            notes=json.toTeshuStr(maplist.get("notes").toString().replaceAll("'", "").trim());

                sqlString = "update pa_data_temp set mat_id=" +maplist.get("pmat_id")+",mat_name='"+mat_name+"',deliver_by="+maplist.get("deliver_by")+",qty="+maplist.get("qty")+" ,pa_no="+maplist.get("pa_no")+

                            ",used='"+used+"',notes='"+notes+"' where pa_detail_id="+ maplist.get("detail_id");

               // System.out.println(sqlString);

                stmt.executeUpdate(sqlString);

          }

          sqlString="insert into pa_data(pa_no,mat_id,mat_name,qty,deliver_by,used,notes)"+

                    " select ltrim(pa_no) as pa_no, ltrim(mat_id) as mat_id, ltrim(mat_name) as mat_name,qty,deliver_by, ltrim(used) as used, ltrim(notes) as notes "+

                    "from pa_data_temp where  oper_id='"+user_id+"'and ip='"+ip+"'";

          System.out.println(sqlString);

            stmt.addBatch(sqlString);

            stmt.executeBatch();

            returnvalue = "保存成功！";

conn.commit();

            conn.setAutoCommit(true);

        }

        catch (SQLException e) {

          conn.rollback();

          conn.setAutoCommit(true);

          System.err.println(e.getMessage());

          success = false;

          returnvalue = "保存失敗！";

        }

        finally {

          if (rs != null) {

            try {

              rs.close();

            }

            catch (SQLException e) {

              rs = null;

            }

          }

          if (stmt != null) {

            try {

              stmt.close();

            }

            catch (SQLException e) {

              stmt = null;

            }

          }

          if (conn != null) {

            try {

              conn.close();

            }

            catch (SQLException e) {

              conn = null;

            }

          }

        }

        returnvalue = "{success:'" + success + "',msg:'" + returnvalue + "'}";

        try {

          httpServletResponse.setContentType("text/json; charset=utf-8");

          httpServletResponse.getWriter().write(returnvalue);

        }

        catch (IOException ex) {

          ex.printStackTrace();

        }

        return actionMapping.findForward("");

  }

//讀取exec文檔(execl2007,2003)

  protected ArrayList readExcel(String excelPath) throws InvalidFormatException, FileNotFoundException, IOException

{

  Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)));

  Sheet sheet = workbook.getSheetAt(0);

  int startRowNum = sheet.getFirstRowNum();

  int endRowNum = sheet.getLastRowNum();

  ArrayList pm_dn_plan_s = new ArrayList();

  ArrayList pm_dn_plandata = null;

  boolean check = true;

  //第一行標題不要

  for(int rowNum = startRowNum+1;rowNum<=endRowNum;rowNum++){

    Row row = sheet.getRow(rowNum);

    if (row == null)continue;

    int startCellNum = row.getFirstCellNum();

    int endCellNum = row.getLastCellNum();

    if(endCellNum<2){

      check = false;

      break;

    }else{

      //只讀第一第二列

      pm_dn_plandata = new ArrayList();

      for (int cellNum = startCellNum; cellNum < endCellNum; cellNum++) {

        //pm_dn_plandata = new ArrayList();

        Cell cell = row.getCell(cellNum);

        if(cellNum==0){

          if(cell.getCellType()!=Cell.CELL_TYPE_STRING){

            check = false;

            break;

          }

          pm_dn_plandata.add(cell.getStringCellValue());

        }

        if(cellNum==1){

          if(cell.getCellType()!=Cell.CELL_TYPE_NUMERIC){

            check = false;

            break;

          }

          pm_dn_plandata.add(cell.getNumericCellValue());

        }

      }

      pm_dn_plan_s.add(pm_dn_plandata);

    }

    //只讀第一第二列

    //if(endCellNum)

    //System.out.println();

  }

  if(!check){

    pm_dn_plan_s.clear();

  }

  return pm_dn_plan_s;

}

Ben_1043556915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
JAVA 读取Excel 上传数据
weixin_42984489的博客
04-09 727
导入Excel 文件（https://www.cnblogs.com/smart-hwt/p/8243420.html） 1。js事件 function loadExcel(){ index = openDialogNoBtn(GetBasePath() + "/ipAdmin/loadnewview.do","导入Excel", "380px", "200px", null,null); ...
java上传并读取excel工具类
01-17
java上传并读取excel工具类 涉及到MultiFile转FIle 以及多个sheet页
Java上传excel，读取excel，xlsx和xls两种类型。读入数据库
夏笙的博客
04-17 3693
这个小工具是去年在公司做东西的时候学会的，不是本人 自己写的 这是工具类 package com.ruowei.Utils; import com.ruowei.exception.BusinessException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cel...
java multipartfile读取文件
最新发布
weixin_42500789的博客
06-29 224
使用Java MultipartFile读取文件 在Java中，我们经常需要处理文件上传和下载的操作。而Spring框架提供了MultipartFile接口来处理文件上传，通过这个接口我们可以方便地读取上传的文件内容。 MultipartFile概述 MultipartFile接口是Spring框架中用于处理文件上传的接口...
java读取上传excel文件和txt文件中的数据.doc
01-13
java读取上传excel文件和txt文件中的数据.doc
java上传excel_java使用poi上传并解析Excel文档
weixin_28723109的博客
02-19 287
public void importFile(MultipartFile uploadFile) throws IOException {String fileName = uploadFile.getOriginalFilename();String fileType = "";if (fileName.lastIndexOf(".") != -1&& fileName.last...
java解析Excel文件并把数据存入数据库和导出数据为excel文件SpringBoot代码示例
05-08
1、java解析读取excel文件中的数据，并写入数据库。 2、java读取数据库数据，并导出为excel文件。 3、README.md中有详细的操作步骤示例。 使用说明： 1. 先使用postman导入：other/excel相关.postman_collection....
java上传excel并且解析入库
11-16
在Java开发中，上传并解析Excel文件是一种常见的需求，特别是在数据处理、报表导入或系统集成等场景。本篇文章将深入探讨如何实现这个功能，主要涉及的技术包括文件上传、Excel解析以及数据库操作。 首先，我们需要...
向hdfs上传Excel文件.doc
03-06
下面详细介绍如何利用Python中的`pandas`库来读取Excel文件，并将其转换为CSV格式，再通过`hdfs`库将CSV文件上传到HDFS中。 ##### 步骤一：将Excel文件转换为CSV格式 首先，我们需要安装必要的Python库，如`pandas...
Java解析Excel文件并把数据存入数据库
08-30
本篇文章主要介绍了Java解析Excel文件并把数据存入数据库的方法，涵盖了上传Excel文件、解析Excel文件、将数据存入数据库等多个方面的知识点。 一、上传Excel文件 在上传Excel文件时，需要在web.xml文件中配置...
java解析上传的excel_Java读取导入Excel文件解析
weixin_34889287的博客
02-13 662
在编程中经常需要使用到表格(报表)的处理主要以Excel表格为主。下面给出用java读取excel表格方法：1.添加jar文件java导入导出Excel文件要引入jxl.jar包，最关键的是这套API是纯Java的，并不依赖Windows系统，即使运行在Linux下，它同样能够正确的处理Excel文件。下载地址:http://www.andykhan.com/jexcelapi/2.jxl对Exc...
java上传文件，基于内存用poi读取excel内容
05-09
java上传文件，基于内存用poi读取excel内容. Excel内容解析完成后，可根据实际需求做相应处理
Excel上传并解析java对象
01-08
Excel上传并解析java对象Excel上传并解析java对象Excel上传并解析java对象
java文件上传和上传excel后并解析出来的小工具
03-28
NULL 博文链接：https://little-bill.iteye.com/blog/1268491
java获取前端传入的Excel路径
weixin_47119456的博客
02-09 589
java获取前端传入的Excel路径
java读取前端上传的Excel文件内容
Gareth的博客
04-10 533
对象，然后分别获取该行的第1、2、3个单元格（索引从0开始）的值。这里假设第一个单元格包含整数值，后两个单元格包含字符串值。2、从工作簿中获取第一个工作表（索引为0）。4、从第二行开始（索引为1，因为第一行通常是标题行）遍历到最后一行。，用于存储从Excel中读取的数据。在Service类的方法中处理、读取表格内容。是Apache POI库中的一个类，用于处理。数组，代表Excel中的一行数据。获取上传文件的输入流，并创建一个。对象，用于读取Excel文件。格式的Excel文件。6、将读取到的数据（
JavaWeb实现导入Excel数据&导出Excel数据(Apache POI)
weixin_72083767的博客
05-17 1432
JavaWeb实现导入Excel数据&导出Excel数据(Apache POI)
java上传excel文件并读取表格信息
a939839307的博客
12-14 9312
public void readExcel()throws Exception{         HttpServletRequest request = ServletActionContext.getRequest();         HttpServletResponse response= Struts2Utils.getResponse();         //获取要保存文件夹
Java 读取excel文件
m0_37346206的博客
10-07 3088
8、通过双重 for循环遍历List,内层List为列，外层List为行，如下图将第一行数据单独拿出来，作为判断字段，如果实体类等@Excel注解属性和Excel表格第一行名字相同，对实体类进行set赋值。4、ExcelUtils工具类对数据进行处理，因为表格有行和列，所以处理完数据使用二维集合List对数据进行存储。5、已经获取到了Excel表格中所有的数据，包含所有的行和列，都在List中，这一步所有的数据获取完了。6、有了数据，要将数据存储到数据库。
java实现上传excel文件
07-08
要在Java中实现上传Excel文件，你可以使用一些常见的框架和库来简化该过程。下面是一个基本的示例代码，使用Spring Boot框架和Apache POI库来实现上传Excel文件： 1. 首先，确保在pom.xml文件中添加以下依赖： ```...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值