精华内容
下载资源
问答
  • Java导入复杂excel

    2021-03-16 13:22:33
    Java 导入复杂excel,多表格中有合并以及描述 接口返回类 package com.hs.report.utils; import com.hs.report.enums.ResultCode; import org.apache.commons.lang.StringUtils; import java.io.Serializable; ...

    Java 导入复杂excel,多表格中有合并以及描述

    在这里插入图片描述
    接口返回类

    package com.hs.report.utils;
    
    import com.hs.report.enums.ResultCode;
    import org.apache.commons.lang.StringUtils;
    
    import java.io.Serializable;
    import java.util.HashMap;
    import java.util.Map;
    
    public class Result <T> extends BaseDomain implements Serializable {
        private static final long serialVersionUID = -1117047807265427246L;
        private int status;
        private String msg;
        private T data;
    
        public Result() {
            this.status = ResultCode.SUCCESS.getStatus();
            this.msg = ResultCode.SUCCESS.getMsg();
        }
    
        public Result(T data) {
            this.setData(data);
            this.status = ResultCode.SUCCESS.getStatus();
            this.msg = ResultCode.SUCCESS.getMsg();
        }
    
        public Result(int status, String msg) {
            this.setStatus(status);
            this.setMsg(msg);
        }
    
        public Result(int status, String msg, T data) {
            this.setStatus(status);
            this.setMsg(msg);
            this.setData(data);
        }
    
        public Result<T> setError(int status, String msg) {
            this.setStatus(status);
            this.setMsg(msg);
            return this;
        }
    
        public Result<T> setError(ResultCode errorCode) {
            this.setStatus(errorCode.getStatus());
            this.setMsg(errorCode.getMsg());
            return this;
        }
    
        public boolean isSuccess() {
            return this.getStatus() == ResultCode.SUCCESS.getStatus();
        }
    
        public static  Result instance() {
            return new Result();
        }
    
        public static <T> Result instance(T data) {
            return new Result(data);
        }
    
        public static <T> Result instance(int status, String msg) {
            return new Result(status, msg);
        }
    
        public static <T> Result instance(int status, String msg, T data) {
            return new Result(status, msg, data);
        }
    
        public int getStatus() {
            return this.status;
        }
    
        public void setStatus(int status) {
            this.status = status;
        }
    
        public String getMsg() {
            return this.msg;
        }
    
        public void setMsg(String msg) {
            this.msg = msg;
        }
    
        public void setPeriodIdMsg(Integer periodId){
            this.msg = ResultCode.SUCCESS.getMsg() + "_" + periodId;
        }
    
        public T getData() {
            return this.data;
        }
    
        public void setData(T data) {
            this.data = data;
        }
    
        public Map<String,Object> toJsonMap(){
            Map<String,Object> map = new HashMap<>();
            map.put("data",this.data);
            map.put("msg",this.msg);
            map.put("status",this.status);
            return map;
        }
    
    }
    
    

    实体类

    package com.hs.report.entity.week;
    import lombok.Builder;
    import lombok.Data;
    import java.io.Serializable;
    @Data
    @Builder
    public class SnapshotReq<T> implements Serializable {
        private static final long serialVersionUID = 4403232919132430023L;
        private String type;
        private Integer offset;
    	/**
    	*json字符串
    	**/
        private T content;
    
        private Integer subNo;
    
        private Integer periodId;
    
        private String flowId;
    }
    
    
    

    Controller层

    package com.hs.report.controller.week;
    
    import com.hs.report.entity.week.SnapshotReq;
    import com.hs.report.service.week.ParseExcelForGSService;
    import com.hs.report.service.week.ParseExcelForOTCService;
    import com.hs.report.service.week.ParseExcelForZTService;
    import com.hs.report.utils.Result;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.InputStream;
    import java.util.List;
    
    @Slf4j
    @Api(tags = "导入Excel模板")
    @RestController
    @RequestMapping("/investmentReport/week")
    public class ImportWeekExcelController {
    
        @Autowired
        private ParseExcelForZTService parseExcelForZTService;
    
        @Autowired
        private ParseExcelForGSService parseExcelForGSService;
    
        @Autowired
        private ParseExcelForOTCService parseExcelForOTCService;
    
        @ApiOperation(value = "导入")
        @PostMapping("/import")
        public Result<List<SnapshotReq>> importExcel(@RequestParam("file") MultipartFile file, @RequestParam(value = "type") Integer type) throws Exception {
            log.info("开始解析Excel, type==={}", type);
    
            Result<List<SnapshotReq>> result = new Result<>();
            List<SnapshotReq> snapshotReqs = null;
    
            InputStream inputStream = null;
            Workbook wb = null;
            try{
                inputStream = file.getInputStream();
                String fileName = file.getOriginalFilename();
    
                String fileType = fileName.substring(fileName.lastIndexOf("."));
                if(".xls".equals(fileType)){
                    wb= new HSSFWorkbook(inputStream);
                }else if(".xlsx".equals(fileType)) {
                    wb = WorkbookFactory.create(inputStream);
                }
    
                switch(type){
                    //证投
                    case 1:
                        snapshotReqs = parseExcelForZTService.parseExcelForZT(wb);
                        break;
                    //固收
                    case 2:
                        snapshotReqs = parseExcelForGSService.parseExcelForGS(wb);
                        break;
                    //OTC
                    case 3:
                        snapshotReqs = parseExcelForOTCService.parseExcelForOTC(wb);
                        break;
                }
    
                inputStream.close();
                result.setData(snapshotReqs);
                log.info("结束解析Excel, type==={}", type);
            }catch (Exception ex){
                throw new Exception("导入Excel出现异常,异常信息:" + ex.getMessage());
            }
    
            return result;
        }
    
    
    }
    
    

    Service层

    package com.hs.report.service.impl.week;
    
    import com.hs.report.entity.week.BusinessTypeDO;
    import com.hs.report.entity.week.LossPayfDO;
    import com.hs.report.entity.week.SnapshotReq;
    import com.hs.report.entity.week.WeekLossPayfDO;
    import com.hs.report.service.week.ParseExcelForGSService;
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.stereotype.Service;
    
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.List;
    
    @Service
    public class ParseExcelForGSServiceImpl implements ParseExcelForGSService {
        @Override
        public List<SnapshotReq> parseExcelForGS(Workbook wb) throws InstantiationException, IllegalAccessException  {
            Sheet sheet = wb.getSheetAt(0);
            List<SnapshotReq> result = new ArrayList<>();
            int rowIndex = 0;
    
            //本周重点推进工作:描述
            int weekRowNum = parseTextValue(sheet, 1,rowIndex + 2, 0, result);
    
            //本周重点推进工作:描述
            int lastWeekRowNum = parseTextValue(sheet, 2, weekRowNum + 3, 0, result);
    
            //T0周损益对比:描述
            int weekLossRowNum = parseTextValue(sheet, 3, lastWeekRowNum + 3, 0, result);
    
            //第一个表格内容
            int oneTitleText = parseTableValue(sheet, 4, weekLossRowNum + 4, result, WeekLossPayfDO.class);
    
            //市场状况及交易部门损益
            int marketRowNum = parseTextValue(sheet, 5, weekLossRowNum + 3 + oneTitleText + 3, 0, result);
    
            //第二个表格内容
            int twoTitleText = parseTableValue(sheet, 7, marketRowNum + 4, result, LossPayfDO.class);
    
            //固定收益部损益分析
            int fixedRowNum = parseTextValue(sheet, 10, marketRowNum + 3 + twoTitleText + 4, 0, result);
    
            //第三个表格内容
            int threeTitleText = parseThreeTableValue(sheet, 11, fixedRowNum + 3, result);
    
            //固定收益部
            int dailyRowNum = parseTextValue(sheet, 12, fixedRowNum + 3 + threeTitleText + 3, 0, result);
    
            return result;
        }
    
        //解析文本框内容
        private int parseTextValue(Sheet sheet, int offset, int rowIndex, int cellIndex, List<SnapshotReq> result){
            SnapshotReq snapshotReq = null;
            Cell cell = sheet.getRow(rowIndex).getCell(cellIndex);
            snapshotReq = SnapshotReq.builder().offset(offset).content(cell.getStringCellValue()).build();
            result.add(snapshotReq);
            return rowIndex;
        }
    
        //解析第三表格部分 有合并列
        private <T> int parseThreeTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result) {
            Row row = null;
            Cell cell = null;
            SnapshotReq snapshotReq = null;
            String perPurpose = "";//上一个数据描述
            String purpose = "";//当前数据描述
            boolean flag = true;//用来记录是否是第一次循环
            //判断有几条数据
            int oneTitleText = 0;
            List<Object> dataList = new ArrayList<>();
            for(int i = rowIndex;i < sheet.getLastRowNum();i++){
                BusinessTypeDO businessTypeDO = new BusinessTypeDO();
                row = sheet.getRow(i);
                //判断此行的每一列是否都为空
                if(judgeWhetherNull(row)){
                    oneTitleText++;
                    break;
                }
    
                if(row != null){
                    for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                        cell = row.getCell(j);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        purpose = cell.getStringCellValue();
                        if(j == 0){
    
                            if(StringUtils.isNotEmpty(purpose) && !purpose.equals(perPurpose)){
                                flag = true;
                            }
    
                            if(flag){//如果是第一次循环,将perPurpose初始化,以便后面的第一次判断
                                perPurpose = purpose;
                                flag = false;
                            }
    
                            if(StringUtils.isEmpty(purpose) || purpose.equals(perPurpose)){
                                purpose = perPurpose;
                            }else{
                                purpose = cell.getStringCellValue();
                            }
                        }
    
                        getThreeTitleText(businessTypeDO, purpose, j);
                    }
                    dataList.add(businessTypeDO);
    
                }else{
                    break;
                }
                oneTitleText++;
            }
            snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
            result.add(snapshotReq);
    
            return oneTitleText - 1;
        }
    
    
        //解析表格内容
        private <T> int parseTableValue(Sheet sheet, int offset, int rowIndex, List<SnapshotReq> result, Class<T> tClass)throws IllegalAccessException, InstantiationException{
            Row row = null;
            Cell cell = null;
            SnapshotReq snapshotReq = null;
            //判断有几条数据
            int oneTitleText = 0;
            List<Object> dataList = new ArrayList<>();
            for(int i = rowIndex;i < sheet.getLastRowNum();i++){
                Object object = tClass.newInstance();
                row = sheet.getRow(i);
                //判断此行的每一列是否都为空
                if(judgeWhetherNull(row)){
                    if(i == rowIndex){
                        oneTitleText = 2;
                    }else{
                        oneTitleText++;
                    }
                    break;
                }
    
                if(row != null){
                    for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                        cell = row.getCell(j);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        generateData(object, cell.getStringCellValue(), j);
                    }
                    dataList.add(object);
    
                }else{
                    break;
                }
                oneTitleText++;
            }
            snapshotReq = SnapshotReq.builder().offset(offset).content(dataList).build();
            result.add(snapshotReq);
    
            return oneTitleText - 1;
        }
    
        private <T> void generateData(T object, String value, int cellIndex){
            if(object instanceof WeekLossPayfDO){
                getOneTitleText((WeekLossPayfDO) object, value, cellIndex);
            }else if(object instanceof LossPayfDO){
                getTwoTitleText((LossPayfDO) object, value, cellIndex);
            }
        }
    
        private void getThreeTitleText(BusinessTypeDO businessTypeDO, String value, int cellIndex) {
            switch(cellIndex){
                case 0:
                    businessTypeDO.setTitle(value);
                    break;
                case 2:
                    businessTypeDO.setSubtitle(getContentStr(value));
                    break;
                case 4:
                    businessTypeDO.setValue(getContentStr(value));
                    break;
            }
        }
    
        private String getContentStr(String content) {
            return StringUtils.isNotEmpty(content) ? content : "";
        }
    
        private boolean judgeWhetherNull(Row row) {
        	if(row == null){
                return true;
            }
            int count = 0;
            //单元格数量
            int rowCount = row.getLastCellNum() - row.getFirstCellNum();
            for (int c = 0; c < rowCount; c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
                    count += 1;
                }
            }
    
            if (count == rowCount) {
                return true;
            }
            return  false;
        }
    
        private void getTwoTitleText(LossPayfDO lossPayfDO, String value, int cellIndex) {
            Double data = null;
            if(cellIndex != 0){
                data = StringUtils.isNotEmpty(value) ? Double.valueOf(value) : 0.0;
            }
            switch (cellIndex){
                case 0:
                    lossPayfDO.setDepName(getContentStr(value));
                    break;
                case 1:
                    lossPayfDO.setRiskExpo(data);
                    break;
                case 2:
                    lossPayfDO.setGsck(data);
                    break;
                case 3:
                    lossPayfDO.setActlMval(data);
                    break;
                case 4:
                    lossPayfDO.setFutrNetPos(data);
                    break;
                case 5:
                    lossPayfDO.setWeekLossPayf(data);
                    break;
                case 6:
                    lossPayfDO.setMthLossPayf(data);
                    break;
                case 7:
                    lossPayfDO.setYearLossPayf(data);
                    break;
            }
        }
    
        private void getOneTitleText(WeekLossPayfDO weekLossPayfDO, String value, int cellIndex) {
            BigDecimal data = null;
            if(cellIndex != 0){
                data = StringUtils.isNotEmpty(value) ? new BigDecimal(value) : null;
            }
            switch (cellIndex){
                case 0:
                    weekLossPayfDO.setDeptName(getContentStr(value));
                    break;
                case 2:
                    weekLossPayfDO.setWeekLoss(data);
                    break;
                case 4:
                    weekLossPayfDO.setLastWeekLoss(data);
                    break;
                case 6:
                    weekLossPayfDO.setYearLoss(data);
                    break;
            }
        }
    }
    
    
    展开全文
  • 由于Java并没有表格对象,总要利用集合加实体类去实现(硬编码),如果碰到格式复杂的表格,解析难度大,工作量会成倍增加,代码不仅冗长、且很难通用。 比如要处理这么个场景:数据库表Logistics有3个字段:Shippers...

    一般常规办法:先使用POI或者HSSFWorkbook等第三方类库对其表格数据结构化,再用SQL语句写入数据库。由于Java并没有表格对象,总要利用集合加实体类去实现(硬编码),如果碰到格式复杂的表格,解析难度大,工作量会成倍增加,代码不仅冗长、且很难通用。

    比如要处理这么个场景:数据库表Logistics有3个字段:Shippers、Region、Quantity。解析如下Excel表格,并入库:

     

     

    入库后的效果:

    1579002648000e86b.png

    Java代码大概要写成这样子:

    ...

    File target = new File(filepath, filename);

    FileInputStream fi = new FileInputStream(target);

    HSSFWorkbook wb = new HSSFWorkbook(fi);

    HSSFSheet sheet = wb.getSheetAt(sheetnum);

    int rowNum = sheet.getLastRowNum() + 1;

    for (int i = startrow; i < rowNum; i++) {

            PageData varpd = new PageData();

            HSSFRow row = sheet.getRow(i);

            int cellNum = row.getLastCellNum();

            ...

            }

    ...

    List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 3, 0, 0);             

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

            pd.put("ET_ID", this.get32UUID());

            ...

            }      

    /*The operation to import the database*/

    mv.addObject("msg","success");  

    ...

    如果有了集算器,这样的问题则会简单很多,它是专业处理结构化数据的语言,能够轻松读取Excel数据,结构化成“序表”后导入数据库。以往需要编写数千行代码才能完成的Excel数据结构化入库工作,现在只需简单的几行就搞定了。比如上面的问题,集算器SPL仅3行:

     

    A

    1

    =file("/workspace/crosstab.xls").xlsimport@t(;1,2).rename(#1:Shippers)

    2

    =A1.pivot@r(Shippers;Region,Quantity)

    3

    =Mysql.update(A2,Logistics)

    其实还有很多类似的结构化问题不太方便,但有集算器SPL的辅助却很简单,感兴趣可以参考:复杂Excel表格导入导出的最简方法10行代码提取复杂Excel数据

    集算器还很容易嵌入到Java应用程序中,Java如何调用SPL脚本有使用和获得它的方法。

    关于集算器安装使用、获得免费授权和相关技术资料,可以参见如何使用集算器

    展开全文
  • String titleName="导入组织负责人"; String typeImpo="indexApic"; Integer addOkSize=0;//添加成功数据 Integer addErrorSize=0;//添加错数据 Integer updateOkSzie=0;//存在更新成功数据 Integer ...

    作者官方网站:http://www.wxl568.cn

    SysUser su = (SysUser) ContextUtil.getCurrentUser();
    ModelAndView mv = new ModelAndView("hthrmis/htimport/importConfig.jsp");
    UtilExcelFile uef=new UtilExcelFile();
    List<ImportOrgPic> picllist=uef.readExcelPic(excelPicFile,su,request);
    if(picllist.size()>5000){
    return mv.addObject("viewOkSizeCount",picllist.size());
    }
    List<ImportOrgPic> newNoPics = new ArrayList<ImportOrgPic>();
       List<ImportOrgPic> newPics = new ArrayList<ImportOrgPic>();
       List<Long> orgIdslist = new ArrayList<Long>();     
       boolean contain;
       for(int i = 0;i < picllist.size();i++){
         contain = orgIdslist.contains(picllist.get(i).getOrgId());   
         if(contain == false){//封装数据
         orgIdslist.add(picllist.get(i).getOrgId());  
                 ImportOrgPic newU = new ImportOrgPic();//
                 newU.setOrgId(picllist.get(i).getOrgId());
                 newU.setOrgCode(picllist.get(i).getOrgCode());
                 newU.setOrgName(picllist.get(i).getOrgName());
                 newU.setOrgWholeName(picllist.get(i).getOrgWholeName());
                 newU.setPrincipalCode(picllist.get(i).getPrincipalCode());
                 newU.setPrincipalName(picllist.get(i).getPrincipalName());
                 newU.setInvYear(picllist.get(i).getInvYear());
                 newU.setOperation(picllist.get(i).getOperation());
                 newPics.add(newU);
         }else{//     
         ImportOrgPic newU = new ImportOrgPic();//
                  newU.setOrgId(picllist.get(i).getOrgId());
                  newU.setOrgCode(picllist.get(i).getOrgCode());
                  newU.setOrgName(picllist.get(i).getOrgName());
                  newU.setOrgWholeName(picllist.get(i).getOrgWholeName());
                  newU.setPrincipalCode(picllist.get(i).getPrincipalCode());
                  newU.setPrincipalName(picllist.get(i).getPrincipalName());
                  newU.setInvYear(picllist.get(i).getInvYear());
                  newU.setOperation(picllist.get(i).getOperation());
                  newNoPics.add(newU);     
         }
       }//数据清洗
    List<ImportOrgPic> newNoOKPics = new ArrayList<ImportOrgPic>();
      for(int k = 0; k < newNoPics.size();k++ ){
         for(int p = 0; p < newPics.size();p++ ){
         if(String.valueOf(newPics.get(p).getOrgId()).equals(String.valueOf(newNoPics.get(k).getOrgId()))){
         ImportOrgPic newU = new ImportOrgPic();//id,newName,age
                 newU.setOrgId(newPics.get(p).getOrgId());
                 newU.setOrgName(newPics.get(p).getOrgName());
                 newU.setOrgCode(newPics.get(p).getOrgCode());
                 newU.setOrgWholeName(newPics.get(p).getOrgWholeName());
                 newU.setPrincipalCode(newPics.get(p).getPrincipalCode());
                 newU.setPrincipalName(newPics.get(p).getPrincipalName());
                 newU.setInvYear(newPics.get(p).getInvYear());
                 newU.setOperation(newPics.get(p).getOperation());
                  newNoOKPics.add(newU);
                  newPics.remove(p);
                  break;
         }
    }
       }   
      newNoOKPics.addAll(newNoPics);//数据清洗后
    OrgPiclList piclListOne=new OrgPiclList();
    piclListOne.setUserId(su.getUserId());
    piclListOne.setAccount(su.getAccount());
    piclListOne.setOrgPiclist(newPics);
    piclListOne.setNewNoOKPics(newNoOKPics);
    piclListOne.setCreateEmpId(su.getUserId());
    piclListOne.setCreateEmpCode(su.getAccount());
    piclListOne.setModifyEmpCode(su.getAccount());
    SystemInventoryDateView piclListOneDate=new SystemInventoryDateView();
    piclListOneDate.setUserId(su.getUserId());
    piclListOneDate.setAccount(su.getAccount());
    piclListOneDate.setCreateEmpId(su.getUserId());
    piclListOneDate.setCreateEmpCode(su.getAccount());
    piclListOneDate.setModifyEmpCode(su.getAccount());
    SystemInventoryDateView dateView = getIndexConfig(piclListOneDate);
    Date currentDate = new Date();
    String startDate="";
    String strEndDate="";
    String openSystemStatus="";
    if(dateView!=null){
    startDate=DateUtil.formatDate(dateView.getStartDate(), "yyyy-MM-dd");
    strEndDate=DateUtil.formatDate(dateView.getEndDate(), "yyyy-MM-dd");
    openSystemStatus=getSystemDate(currentDate, dateView.getStartDate(),dateView.getEndDate());
    }
    OrgPiclList picruturnlis=htimportServce.setSavePiclList(piclListOne);
    String titleName="导入组织负责人";
    String typeImpo="indexApic";
    Integer addOkSize=0;//添加成功数据
    Integer addErrorSize=0;//添加错数据
    Integer updateOkSzie=0;//存在更新成功数据
    Integer deleteOkSize=0;//删除
    if(picruturnlis!=null){
    if(picruturnlis.getAddErrorSize()!=null){
     addErrorSize=picruturnlis.getAddErrorSize();
    }
    if(picruturnlis.getAddOkSize()!=null){
    addOkSize=picruturnlis.getAddOkSize();
    }
    if(picruturnlis.getUpdateOkSzie()!=null){
    updateOkSzie=picruturnlis.getUpdateOkSzie();
    }
    if(picruturnlis.getDeleteOkSize()!=null){
    deleteOkSize=picruturnlis.getDeleteOkSize();
    }
    addOkSize=addOkSize+updateOkSzie;
    }
    String urlErrDow="hthrmis/htimport/htimportContro/exportExcelOrgPicErr.ht";
    return mv.addObject("urlErrDow", urlErrDow).addObject("titleName", titleName).addObject("typeImpo", typeImpo).addObject("deleteOkSize", deleteOkSize).addObject("addOkSize",addOkSize).addObject("updateOkSzie",updateOkSzie).addObject("addErrorSize",addErrorSize).addObject("openSystemStatus", openSystemStatus).addObject("typeImpo", typeImpo).addObject("dateView", dateView).addObject("startDate", startDate).addObject("strEndDate", strEndDate);

    展开全文
  • Java POI 导入EXCEL数据缓慢调优

    千次阅读 2019-01-07 11:35:10
    1.使用 SXSSFWorkbook workbook = new SXSSFWorkbook();// 创建一个Excel文件  将其赋上初始100条 SXSSFWorkbook workbook = new SXSSFWorkbook(100);...在数据循环导入时,尽量避免创建新对象(ne...
    1.使用 SXSSFWorkbook workbook = new SXSSFWorkbook();// 创建一个Excel文件
    

         将其赋上初始100条

      SXSSFWorkbook workbook = new SXSSFWorkbook(100);// 创建一个Excel文件

         如果超出100条则保存在磁盘中.

      2.在数据循环导入时,尽量避免创建新对象(new)

      3. String换成StringBuffer

      4.调整虚拟机内存

    展开全文
  • 最近接到一个需求,需要把一份37万的excel数据解析并导入数据库, 分析 表格格式相对复杂包含很多合并单元格, 不符合通过navicat直接导入的要求, 数据量比较大, 建议一次保存25条,否则会导致内存泄漏; 代码 1.引入依赖...
  • 内容包括:Java概述、Java基本语法、Java 执行控制流程、面向对象、访问控制权限、接口和抽象类、异常、内部类、集合、泛形、反射、枚举、I/O、关于 null 的几种处理方式、思维导图。 1、Java概述 2、Java基本语法 ...
  • Java数据导入Excel

    千次阅读 2014-07-18 15:04:05
    package com.bossy.base; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List;
  • Java数据导入导出excel

    2013-07-08 14:38:06
    Java数据导入导出excel  import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;import jxl....
  • java导入导出excel 01

    千次阅读 2016-05-19 10:52:41
    对Apache POI 3.9的简单封装,实现Excel的导出导入功能。使用Annotation定义导出导入字段。http://jeesite.com 优点: 简单易用,支持大数量导出,配置简单,代码量少。支持Excel 2003、2007、2010(xls、xlsx)...
  • JAVA 导入导出Excel

    2010-06-12 11:28:00
                                  Java解释Excel数据(jxl.jar包的使用) 关键字: java excel jxl.jarjxl....
  • java导入导出excel操作(jxl)  Java解释Excel数据(jxl.jar包的使用)  关键字: java excel jxl.jar  jxl.jar 包  下载地址:  http://www.andykhan.com/jexcelapi/  真实下载地址:...
  • 然后在网上找了找,找到一篇文章就试了试,因为我不止需要将Excel数据直接导入就行,还需要根据表格里的数据在进行一次查询,将更多数据重新存到数据库(晕,其实不复杂,就是多几行代码)。然后稍微改了一下,还是...
  • Java上传Excel写的Execl上传包含了很多逻辑和需求,如果业务不需要那么复杂的话可以省略一些判断,只用看readSheet、excData这两个方法,一个读数据,一个讲数据转换为我们需要的实体类 尬包 import jxl.Cell; import...
  • 导入1000行的数据,第一次用windows系统下的oracle数据库,每校验一条数据执行时间为0.003秒;第二次用linux系统下的oracle数据库,每校验一条数据执行时间为0.3秒。linux系统下的数据库数据量较多,但只查一次用时...
  • java进行excel数据导入数据库 (代码)

    千次阅读 热门讨论 2018-07-09 11:48:23
    博主自己写的微信支付,里面有源码有兴趣的可以看一下 ... excel导入导出网上参考了一些代码感觉写的都比较复杂,就自己看了一下poi的api...目前,比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和JavaExcel...
  • Java数据导入导出Excel

    2010-12-07 09:44:00
    import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util....
  • 目前,比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和Java Excel Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word...
  • Java数据导入导出Excel

    千次阅读 2008-12-10 19:48:00
    import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;import jxl.Workbook;import jxl.format.U
  • java导入excel模板中的数据到数据库

    千次阅读 2018-09-12 16:51:42
    //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作, for(Map, Object> modal:beExamList){ SatBeExam entity = new SatBeExam(); entity.setCommunity(...
  • 如何用java 将txt数据导入excel代码如下:import java.io.*;import jxl.*;import jxl.write.*;public class CreateXLS{public static void main(String args[]){try{//打开文件WritableWorkbook book= Workbook....
  • 本文是基于Apache poi类实现的批量导入读取Excel文件,所以要先引入Apache poi的依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,105
精华内容 2,842
关键字:

java导入复杂excel数据

java 订阅