maven中Excel 表格导入导出（使用POI ）加拼音项目一

最新推荐文章于 2023-03-27 20:53:53 发布

蚂蚁工兵

最新推荐文章于 2023-03-27 20:53:53 发布

阅读量163

点赞数

本文链接：https://blog.csdn.net/chengxuhaha/article/details/103611703

版权

//excel保存到数据库 (导入)
toolbar工具栏 { id : 'button-import', text : '导入', iconCls : 'icon-redo' }
执行方法

	//绑定一键上传按钮
			$("#button-import").upload({
				action:"${pageContext.request.contextPath}/bcRegion/import.action",
				name:"excelFile"
			});

后台：

@Controller
@RequestMapping("bcRegion")
//区域设置
public class BcRegionController {
   @Autowired
    private BcRegionServiceI bcRegionServiceI;

    @RequestMapping(value = "import.action",method = RequestMethod.POST)
    @RequiresRoles(value = {"admin"})
    public String importExcel(MultipartFile excelFile) throws IOException, PinyinException {

        ArrayList<BcRegion> bcRegions = new ArrayList<>();

        try {
            HSSFWorkbook workbook = new HSSFWorkbook(excelFile.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for(int i=1;i<=lastRowNum;i++){
                HSSFRow row = sheet.getRow(i);
                BcRegion bcRegion = new BcRegion();
                bcRegion.setId(UUID32Gen.gen32UUIDString());
                short lastCellNum = row.getLastCellNum();
                for(int j=0;j<lastCellNum;j++){

                    switch (j){
                        case 1:
                            bcRegion.setProvince(row.getCell(1).getStringCellValue()); //省
                            break;

                        case 2:
                            bcRegion.setCity(row.getCell(2).getStringCellValue()); //市
                            break;

                        case 3:
                            bcRegion.setDistrict(row.getCell(3).getStringCellValue()); //省
                            break;

                        case 4:
                            bcRegion.setPostcode(row.getCell(4).getStringCellValue()); //邮编
                            break;

                    }


                }


                //设置简码和城市简码
                String province = bcRegion.getProvince();
                String city = bcRegion.getCity();
                String district = bcRegion.getDistrict();

                province=province.substring(0,province.length()-1);
                city=city.substring(0,city.length()-1);
                district=district.substring(0,district.length()-1);


                bcRegion.setCitycode(PinyinHelper.convertToPinyinString(city,"", PinyinFormat.WITHOUT_TONE));//城市简码 taiyuan
                bcRegion.setShortcode(PinyinHelper.getShortPinyin(province+city+district));//简码 sxtyqx
                bcRegions.add(bcRegion);



            }

            //excel保存到数据库
            this.bcRegionServiceI.saveAll(bcRegions);


        }catch (Exception e){

            e.printStackTrace();

        }

        return "base/region";
    }

依赖jarb包：


        <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </dependency>


        <dependency>
            <groupId>com.github.stuxuhai</groupId>
            <artifactId>jpinyin</artifactId>
        </dependency>

要在父工程设置jar包版本问题
代码：

 <properties>
 <apache.poi.version>4.0.1</apache.poi.version>
        <jpinyin.version>1.1.7</jpinyin.version>
    </properties>



<dependencies>
        <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${apache.poi.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${apache.poi.version}</version>
            </dependency>

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>${apache.poi.version}</version>
            </dependency>

            <dependency>
                <groupId>com.github.stuxuhai</groupId>
                <artifactId>jpinyin</artifactId>
                <version>${jpinyin.version}</version>
            </dependency>

        </dependencies>