用jxl插件导入excel2003版本以下的表格内容并导入数据库

来源：互联网发布：网络建设之歌编辑：程序博客网时间：2024/06/11 03:45

技术框架：spring、springmvc、mybatis

依赖（注：poi用于读写excel2007版本）：

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.10</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
    <scope>compile</scope>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
    <scope>compile</scope>
</dependency>

excel表格：

商品编码材料名称规格型号品牌名称单位含税价不含税价厂商限定价出厂价包装规格供应商供应商编码140300101101NC清底漆3kg 鑫甲桶70.00 无上海聚贸易有限公司D1140300102101NC清底漆(易打磨）11kg 鑫甲桶205.00 无上海聚贸易有限公司D1140300201101NC白底漆3kg 鑫甲桶70.00 无上海聚贸易有限公司D1140300202101NC白底漆（易打磨）11kg 鑫甲桶205.00 无上海聚贸易有限公司D1140300301101NC黑底漆11kg 鑫甲桶255.00 无上海聚贸易有限公司D1140300401101PU白底漆11kg 鑫甲桶195.00 无上海聚贸易有限公司D1140300501101PU封固底漆11kg 鑫甲桶195.00 无上海聚贸易有限公司D1140300601101NC稀释剂2kg 鑫甲桶43.00 无上海聚贸易有限公司D1140300602101NC稀释剂10L 鑫甲桶145.00 无上海聚贸易有限公司D1140300701101PU稀释剂2kg 鑫甲桶45.00 无上海聚贸易有限公司D1140300702101PU稀释剂10L 鑫甲桶157.00 无上海聚贸易有限公司D1140300801102家丽安底漆18L 多乐士桶258.00 无上海聚贸易有限公司D1140300901102无添加通用底漆18L 多乐士桶430.00 无上海聚贸易有限公司D1140301001103墙面卫士底漆15L 立邦桶285.00 无上海聚贸易有限公司D1

代码部分：

packagecom.jixi.pojo;public class PickpriceMaster{    private String itemcode;    private String itemname;    private String standard;    private Strin gmodel;    private String brandname;    private String chargeunit;    private Double inprice;    private Double excludingtax;    private Double limitprice;    private Double factoryprice;    private String packingsize;    private String supplier;    private String suppliercode;    public PickpriceMaster() {}    public PickpriceMaster(String itemcode,String itemname,String standard,String model,String brandname,String chargeunit,Double inprice,Double excludingtax,Double limitprice,Double factoryprice,String packingsize,String supplier,String suppliercode) {       this.itemcode= itemcode;       this.itemname= itemname;       this.standard= standard;       this.model= model;       this.brandname= brandname;       this.chargeunit= chargeunit;       this.inprice= inprice;       this.excludingtax= excludingtax;       this.limitprice= limitprice;       this.factoryprice= factoryprice;       this.packingsize= packingsize;       this.supplier= supplier;       this.suppliercode= suppliercode;     }     @Override     public String toString() {       return"PickpriceMaster{"+            "itemcode='"+itemcode+'\''+            ", itemname='" +itemname+'\''+            ", standard='" +standard+'\''+            ", model='" +model+'\''+            ", brandname='" +brandname+'\''+            ", chargeunit='" +chargeunit+'\''+            ", inprice=" +inprice+            ", excludingtax=" +excludingtax+            ", limitprice=" +limitprice+            ", factoryprice=" +factoryprice+            ", packingsize='" +packingsize+'\''+            ", supplier='" +supplier+'\''+            ", suppliercode='" +suppliercode+'\''+            '}';    }    public String getItemcode() {        returnitemcode;    }    public void setItemcode(String itemcode) {        this.itemcode= itemcode ==null?null: itemcode.trim();    }    public String getItemname() {        returnitemname;    }    public void setItemname(String itemname) {        this.itemname= itemname ==null?null: itemname.trim();    }        public String getStandard() {        returnstandard;    }    public void setStandard(String standard) {        this.standard= standard ==null?null: standard.trim();    }    public String getModel() {        returnmodel;    }    public void setModel(String model) {        this.model= model ==null?null: model.trim();    }    public String getBrandname() {        returnbrandname;    }    public void setBrandname(String brandname) {        this.brandname= brandname ==null?null: brandname.trim();    }    public String getChargeunit() {        returnchargeunit;    }    public void setChargeunit(String chargeunit) {        this.chargeunit= chargeunit ==null?null: chargeunit.trim();    }    public Double getInprice() {        returninprice;    }    public void setInprice(Double inprice) {        this.inprice= inprice;    }    public Double getExcludingtax() {        returnexcludingtax;    }    public void setExcludingtax(Double excludingtax) {       this.excludingtax= excludingtax;    }    public Double getLimitprice() {       returnlimitprice;    }    public void setLimitprice(Double limitprice) {        this.limitprice= limitprice;    }    public Double getFactoryprice() {        returnfactoryprice;    }    public void setFactoryprice(Double factoryprice) {        this.factoryprice= factoryprice;    }    public String getPackingsize() {        returnpackingsize;    }    public void setPackingsize(String packingsize) {        this.packingsize= packingsize ==null?null: packingsize.trim();    }    public String getSupplier() {      returnsupplier;    }    public void setSupplier(String supplier) {      this.supplier= supplier ==null?null: supplier.trim();    }    public String getSuppliercode() {      returnsuppliercode;    }    public void setSuppliercode(String suppliercode) {       this.suppliercode= suppliercode ==null?null: suppliercode.trim();    }}

packagecom.jixi.service.impl;importcom.jixi.mapper.PickpriceMasterMapper;importcom.jixi.pojo.PickpriceMaster;importcom.jixi.service.IPickPriceMasterService;importjxl.Sheet;importjxl.Workbook;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;/** * Created by zhm on 2017/8/23. */@Servicepublic class PickPriceMasterService implements IPickPriceMasterService {    @Autowired    private PickpriceMasterMapper pickpriceMasterMapper;    /**     *判断itemCode是否存在     *@paramitemCode     *@return     */    public boolean isExist(String itemCode) {        try{            PickpriceMaster pickpriceMaster =pickpriceMasterMapper.selectByPrimaryKey(itemCode);            System.out.println(pickpriceMaster);            if(pickpriceMaster==null) {                return false;            }        }catch(Exception e) {            e.printStackTrace();        }        return true;    }    /**     *判断itemCode是否为空     * */    public boolean isNull(String itemCode){        if(itemCode!=null&&!"".equals(itemCode)){            return true;        }        return false;    }    /**     *查询指定目录中电子表格中所有的数据，导入数据库     *     *@paramin文件输入流     *@paramsheetNum表格中第几个sheet从0开始     *@return     */    @Override    public List getAllByExcel(InputStream in, intsheetNum) {        List list =newArrayList();        try{            Workbook rwb = Workbook.getWorkbook(in);            Sheet rs = rwb.getSheet(sheetNum);//或者rwb.getSheet(0)            intclos = rs.getColumns();//得到所有的列            introws = rs.getRows();//得到所有的行            System.out.println("clos:"+ clos +" rows:"+ rows);            for(inti =1;i < rows -1;i++) {                for(intj =0;j < clos;j++) {                    //第一个是列数，第二个是行数                    String itemcode = rs.getCell(j++,i).getContents();//默认最左边编号也算一列 所以这里得j++                    String itemname = rs.getCell(j++,i).getContents();                    String standard = rs.getCell(j++,i).getContents();                    String model = rs.getCell(j++,i).getContents();                    String brandname = rs.getCell(j++,i).getContents();                    String chargeunit = rs.getCell(j++,i).getContents();                    String inprice = rs.getCell(j++,i).getContents();                    String excludingtax = rs.getCell(j++,i).getContents();                    String limitprice = rs.getCell(j++,i).getContents();                    String factoryprice = rs.getCell(j++,i).getContents();                    String packingsize = rs.getCell(j++,i).getContents();                    String supplier = rs.getCell(j++,i).getContents();                    String suppliercode = rs.getCell(j++,i).getContents();                    list.add(newPickpriceMaster(itemcode,itemname,standard,model,brandname,chargeunit,Double.valueOf("".equals(inprice) ? "0.00": inprice),Double.valueOf("".equals(excludingtax) ? "0.00": excludingtax),Double.valueOf("".equals(limitprice) ? "0.00": limitprice),Double.valueOf("".equals(factoryprice) ? "0.00": factoryprice),packingsize,supplier,suppliercode));                }            }        }catch(Exception e) {            e.printStackTrace();        }        //将得到的数据插入数据库中        for(PickpriceMaster p : list) {            String itemCode = p.getItemcode();            if(isNull(itemCode)) {                if(!isExist(itemCode)) {                    //不存在就添加                    System.out.println("添加");                    pickpriceMasterMapper.insertSelective(p);                }else{                    //存在就更新                    System.out.println("更新");                    pickpriceMasterMapper.updateByPrimaryKeySelective(p);                }            }        }        returnlist;    }}

@Controller@RequestMapping("/pickPriceMaster")public class PickPriceMasterController {    @Autowired    private IPickPriceMasterService pickPriceMasterService;        @RequestMapping(value="/importExcel",method= RequestMethod.GET)    publicStringreadExcelData(){        return"pickPriceMaster-importExcel";    }        /**     *将excel数据导入数据库     * */    @RequestMapping(value="/importExcel",method= RequestMethod.POST)    @ResponseBody    public String readExcelData(MultipartHttpServletRequest request, intsheetNum)throwsIOException {        //得到上传的文件        MultipartFile fileFile = request.getFile("file");        //转换成输入流        InputStream in = fileFile.getInputStream();        pickPriceMasterService.getAllByExcel(in,sheetNum);        return"successful!";    }}

<%@pagecontentType="text/html;charset=UTF-8" language="java" %>    Title    请选则文件
    您要导入的sheet：

过程中出现的bug：

出现的bugNO1:
空指针异常：原因先实例化本类再调用方法
改进：直接调用即可，本类的方法调用无需实例化
eg： if(isNull(itemCode)) {
                if(!isExist(itemCode)) {
                    //不存在就添加
                    System.out.println("添加");
                    pickpriceMasterMapper.insertSelective(p);
                } else {
                    //存在就更新
                    System.out.println("更新");
                    pickpriceMasterMapper.updateByPrimaryKeySelective(p);
                }
出现的bugNO2:
io流异常：原因没有加try...catch
改进：任何输入输出流都别忘了抛异常
eg： public boolean isExist(String itemCode) {
        try {
            PickpriceMaster pickpriceMaster = pickpriceMasterMapper.selectByPrimaryKey(itemCode);
            System.out.println(pickpriceMaster);
            if (pickpriceMaster==null) {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }
出现的bugNO2:
空指针异常：原因判断itemCode（主键）是否存在时只判断单个属性是否存在，没查找到数据
改进：直接判断整个类是否为空
eg：
public boolean isExist(String itemCode) {
        try {
            PickpriceMaster pickpriceMaster = pickpriceMasterMapper.selectByPrimaryKey(itemCode);
            System.out.println(pickpriceMaster);
            if (pickpriceMaster==null) {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }
出错前if里写的是：pickpriceMaster.getItemname==null

阅读全文

0 0