SSH--------------一键上传excel文件并解析文件内容(框架POI)

最新推荐文章于 2022-12-26 20:32:03 发布

保温杯有枸杞

最新推荐文章于 2022-12-26 20:32:03 发布

阅读量221

点赞数

分类专栏： SSH

本文链接：https://blog.csdn.net/qq_32659773/article/details/106016275

版权

SSH 专栏收录该内容

76 篇文章 0 订阅

订阅专栏

1、 jsp页面设置

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>区域设置</title>
    <!-- 导入jquery核心类库 -->
    <script type="text/javascript"
            src="${pageContext.request.contextPath }/js/jquery-1.8.3.js"></script>
    <script type="text/javascript"
            src="${pageContext.request.contextPath }/js/jquery.ocupload-1.1.2.js"></script>
    <!-- 导入easyui类库 -->
    <link rel="stylesheet" type="text/css"
          href="${pageContext.request.contextPath }/js/easyui/themes/default/easyui.css">
    <link rel="stylesheet" type="text/css"
          href="${pageContext.request.contextPath }/js/easyui/themes/icon.css">
    <link rel="stylesheet" type="text/css"
          href="${pageContext.request.contextPath }/js/easyui/ext/portal.css">
    <link rel="stylesheet" type="text/css"
          href="${pageContext.request.contextPath }/css/default.css">
    <script type="text/javascript"
            src="${pageContext.request.contextPath }/js/easyui/jquery.easyui.min.js"></script>
    <script type="text/javascript"
            src="${pageContext.request.contextPath }/js/easyui/ext/jquery.portal.js"></script>
    <script type="text/javascript"
            src="${pageContext.request.contextPath }/js/easyui/ext/jquery.cookie.js"></script>
    <script
            src="${pageContext.request.contextPath }/js/easyui/locale/easyui-lang-zh_CN.js"
            type="text/javascript"></script>
    <script type="text/javascript">
        function doAdd() {
            $('#addRegionWindow').window("open");
        }

        function doView() {
            alert("修改...");
        }

        function doDelete() {
            alert("删除...");
        }

        //工具栏
        var toolbar = [{
            id: 'button-edit',
            text: '修改',
            iconCls: 'icon-edit',
            handler: doView
        }, {
            id: 'button-add',
            text: '增加',
            iconCls: 'icon-add',
            handler: doAdd
        }, {
            id: 'button-delete',
            text: '删除',
            iconCls: 'icon-cancel',
            handler: doDelete
        }, {
            id: 'button-import',
            text: '导入',
            iconCls: 'icon-redo'

        }];
        // 定义列
        var columns = [[{
            field: 'id',
            checkbox: true,
        }, {
            field: 'province',
            title: '省',
            width: 120,
            align: 'center'
        }, {
            field: 'city',
            title: '市',
            width: 120,
            align: 'center'
        }, {
            field: 'district',
            title: '区',
            width: 120,
            align: 'center'
        }, {
            field: 'postcode',
            title: '邮编',
            width: 120,
            align: 'center'
        }, {
            field: 'shortcode',
            title: '简码',
            width: 120,
            align: 'center'
        }, {
            field: 'citycode',
            title: '城市编码',
            width: 200,
            align: 'center'
        }]];

        $(function () {
            // 先将body隐藏，再显示，不会出现页面刷新效果
            $("body").css({visibility: "visible"});

            // 收派标准数据表格
            $('#grid').datagrid({
                iconCls: 'icon-forward',
                fit: true,
                border: false,
                rownumbers: true,
                striped: true,
                pageSize:10,
                pageList: [10, 20, 30,40,50],
                pagination: true,
                toolbar: toolbar,
                url: "${pageContext.request.contextPath}/region_queryjson.action",
                idField: 'id',
                columns: columns,
                onDblClickRow: doDblClickRow
            });

            // 添加、修改区域窗口
            $('#addRegionWindow').window({
                title: '添加修改区域',
                width: 400,
                modal: true,
                shadow: true,
                closed: true,
                height: 400,
                resizable: false
            });
            $('#button-import').upload({
                action:'${pageContext.request.contextPath}/region_importexcel.action',
                name:'excelfile',
                onComplete:function (data) {             //上传完成刷新，显示页面
                    if(data=="success"){
                        $('#grid').datagrid('reload');
                    }

                }
            });


        });

        function doDblClickRow() {
            alert("双击表格数据...");
        }
    </script>
</head>
<body class="easyui-layout" style="visibility:hidden;">
<div region="center" border="false">
    <table id="grid"></table>
</div>
<div class="easyui-window" title="区域添加修改" id="addRegionWindow" collapsible="false" minimizable="false"
     maximizable="false" style="top:20px;left:200px">
    <div region="north" style="height:31px;overflow:hidden;" split="false" border="false">
        <div class="datagrid-toolbar">
            <a id="save" icon="icon-save" href="#" class="easyui-linkbutton" plain="true">保存</a>
        </div>
    </div>

    <div region="center" style="overflow:auto;padding:5px;" border="false">
        <form>
            <table class="table-edit" width="80%" align="center">
                <tr class="title">
                    <td colspan="2">区域信息</td>
                </tr>
                <tr>
                    <td>省</td>
                    <td><input type="text" name="province" class="easyui-validatebox" required="true"/></td>
                </tr>
                <tr>
                    <td>市</td>
                    <td><input type="text" name="city" class="easyui-validatebox" required="true"/></td>
                </tr>
                <tr>
                    <td>区</td>
                    <td><input type="text" name="district" class="easyui-validatebox" required="true"/></td>
                </tr>
                <tr>
                    <td>邮编</td>
                    <td><input type="text" name="postcode" class="easyui-validatebox" required="true"/></td>
                </tr>
                <tr>
                    <td>简码</td>
                    <td><input type="text" name="shortcode" class="easyui-validatebox" required="true"/></td>
                </tr>
                <tr>
                    <td>城市编码</td>
                    <td><input type="text" name="citycode" class="easyui-validatebox" required="true"/></td>
                </tr>
            </table>
        </form>
    </div>
</div>
</body>
</html>

2、先导入jar包，解压后，只需要导入poi-3.9-20121203.jar一个jar包即可

3、action

package com.dong.bos.web.action;

import com.dong.bos.model.PageBean;
import com.dong.bos.model.Region;
import com.dong.bos.service.IRegionService;
import com.dong.bos.utils.PinYin4jUtils;
import com.dong.bos.web.action.base.BaseAction;
import net.sf.json.JSON;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.struts2.ServletActionContext;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.beans.factory.annotation.Autowired;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class RegionAction extends BaseAction<Region> {
    private File excelfile;
    @Autowired
    private IRegionService iRegionService;

    public void setExcelfile(File excelfile) {
        this.excelfile = excelfile;
    }

    public String importexcel() throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(excelfile));
        Sheet sheet = hssfWorkbook.getSheetAt(0);
        List<Region> regions = new ArrayList<Region>();
        for (Row row : sheet) {
            String city = row.getCell(2).getStringCellValue();
            String citytemp = StringUtils.substring(city, 0, city.length() - 1);
            String citycode = StringUtils.join(PinYin4jUtils.stringToPinyin(citytemp), "");
            String distinct = row.getCell(3).getStringCellValue();

            String distincttemp = StringUtils.substring(distinct, 0, distinct.length() - 1);
            String[] citystrs = PinYin4jUtils.getHeadByString(citytemp);
            String[] distinctstr = PinYin4jUtils.getHeadByString(distincttemp);
            String shortcode= StringUtils.join(citystrs,"")+StringUtils.join(distinctstr,"");
            Region region = new Region(row.getCell(0).getStringCellValue(), row.getCell(1).getStringCellValue(), row.getCell(2).getStringCellValue(), row.getCell(3).getStringCellValue(), row.getCell(4).getStringCellValue(), shortcode, citycode);

            regions.add(region);
        }
        regions.remove(0);
        iRegionService.saveRegion(regions);
        ReponseStr("success");
        return NONE;
    }


    public void queryjson() throws IOException {

        PageBeanSet();
        iRegionService.pageQuery(pb);

        pagequery(new String[]{"currentPage", "pageSize", "detachedCriteria"});


    }

    @Override
    public String save() {
        return null;
    }

    @Override
    public String update() {
        return null;
    }

    @Override
    public String delete() throws IOException {
        return null;
    }

    @Override
    public String list() {
        return null;
    }
}

4、dao实现方法

public void saveInsert(List<Region> regions) {
    hibernateTemplate.saveOrUpdateAll(regions);    //saveOrUpdateAll填集合，模板实现插入语句

}

5、jsp显示分页（具体可以看 SSH------------实现员工分页文章）

在action中添加方法，返回json

public void queryjson() throws IOException {
    PageBean<Region> regionPageBean = new PageBean<Region>();
    regionPageBean.setPageSize(rows);
    regionPageBean.setCurrentPage(page);
    DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Region.class);
    regionPageBean.setDetachedCriteria(detachedCriteria);
    iRegionService.pageQuery(regionPageBean);
    JsonConfig jsonConfig=new JsonConfig();
    jsonConfig.setExcludes(new String[]{"currentPage","pageSize","detachedCriteria"});
     JSONObject  jsonObject=JSONObject.fromObject(regionPageBean,jsonConfig);

    HttpServletResponse httpServletResponse= ServletActionContext.getResponse();
    httpServletResponse.setHeader("content-type","text/json;charset=utf-8");
    httpServletResponse.getWriter().write(jsonObject.toString());


}

在dao包中添加方法

public void pageQuery(PageBean<T> regionPageBean) {
    DetachedCriteria detachedCriteria= regionPageBean.getDetachedCriteria();
    detachedCriteria.setProjection(Projections.rowCount());
    List<Long> longList=hibernateTemplate.findByCriteria(detachedCriteria);
    regionPageBean.setTotal(longList.get(0).intValue());

    detachedCriteria.setProjection(null);
    int start=(regionPageBean.getCurrentPage()-1)*regionPageBean.getPageSize();
    int length=regionPageBean.getPageSize();
    List<T> regionList=hibernateTemplate.findByCriteria(detachedCriteria,start,length);
    regionPageBean.setRows(regionList);
}

保温杯有枸杞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SSH--------------一键上传excel文件并解析文件内容(框架POI)

1、 jsp页面设置<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head> &
复制链接

扫一扫