需求: 现在有这样一个文件需要处理

 

 

编写工具类:

  • 导入poi包
      
        
            org.apache.poi
            poi-excelant
            3.14
        
  • 工具类 
package xyz.anix.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelReaderUtil {
	
	

    /**
     * 从sheet3读取excel文件
     * @param file
     * @throws IOException
     */
    public static List> myreadExcel(MultipartFile file) {
        //String fileType = path.substring(path.lastIndexOf(".") + 1);
        // return a list contains many list
        List> lists = new ArrayList>();
        //读取excel文件
        InputStream is = null;
        Workbook wb=null;
        try {
        	 is = file.getInputStream();
            //获取工作薄
             wb= new HSSFWorkbook(is);
           
            //读取第3个工作页sheet
            Sheet sheet = wb.getSheetAt(2);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList list = new ArrayList();
                for (Cell cell : row) {
                    //根据不同类型转化成字符串
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    list.add(cell.getStringCellValue());
                }
                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) is.close();
                if (wb != null) wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }
    
    
    /**
     * 根据fileType不同读取excel文件
     *
     * @param path
     * @param path
     * @throws IOException
     */
    public static List> readExcel(String path) {
        String fileType = path.substring(path.lastIndexOf(".") + 1);
        // return a list contains many list
        List> lists = new ArrayList>();
        //读取excel文件
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }

            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList list = new ArrayList();
                for (Cell cell : row) {
                    //根据不同类型转化成字符串
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    list.add(cell.getStringCellValue());
                }
                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }


    /**
     * 创建Excel.xls
     * @param lists 需要写入xls的数据
     * @param titles 列标题
     * @param name  文件名
     * @return
     * @throws IOException
     */
    public static Workbook creatExcel(List> lists, String[] titles, String name) throws IOException {
        System.out.println(lists);
        //创建新的工作薄
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet(页),并命名
        Sheet sheet = wb.createSheet(name);
        // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
        for(int i=0;i> lists = readExcel(path);
        for (List list : lists) {
            for (String strs : list) {
                System.out.println(strs);
            }
        }
    }
    
}

 

使用:

上述工具类中将每行放到一个list中,然后每行的每列放入到一个list中,这里再根据自己需求去对表中数据进行处理

    @RequestMapping(value="/uploadfile")
    @ResponseBody
    public ResultEntity uploadfile(MultipartFile file){
        ResultEntity res=new ResultEntity();
        Subject currentUser = SecurityUtils.getSubject();

        DxcsBase base=new DxcsBase();
        //DxcsData data=new DxcsData();

        if (file!=null) {
            List> lists = DxcsExcelReaderUtil.myreadExcel(file);
            if (lists != null && lists.size()>0) {

                base.setCpxh(lists.get(1).get(2));
                base.setCpph(lists.get(1).get(10));

                base.setJyyq(lists.get(2).get(2));
                base.setZjr(lists.get(2).get(10));

                base.setLspl(lists.get(3).get(2)+lists.get(3).get(7));
                base.setQpl(lists.get(3).get(10)+lists.get(3).get(15));

                base.setLsbz(lists.get(6).get(1));
                base.setQbz(lists.get(6).get(2)+lists.get(6).get(3));

                base.setRdcbz(lists.get(6).get(4)+lists.get(6).get(5));
                base.setSrfbz(lists.get(6).get(6)+lists.get(6).get(7));

                base.setGcsx(lists.get(7).get(1));
                base.setGcxx(lists.get(8).get(1));

                base.setCssl(lists.get(35).get(2));
                base.setHgs(lists.get(35).get(8));
                base.setBhgs(lists.get(35).get(13));

                base.setPpjg(lists.get(36).get(2));
                base.setPpr(lists.get(36).get(8));
                base.setRq(lists.get(36).get(12));

                base.setRemark(lists.get(34).get(0));

                base.setCreateTime(TimeHelper.getCurrentTime());
                base.setCreateUser((String)currentUser.getPrincipal());
                base.setStatus("1");

                //System.out.println(base);
                baseservce.savedxcs(base);

                List> dataLists = new ArrayList>();

                for (int i = 9; i < 34; i++) {
                    //System.out.println(lists.get(i));
                    dataLists.add(lists.get(i).subList(0, 7));
                }
                for (int i = 9; i < 34; i++) {
                    dataLists.add(lists.get(i).subList(8, 15));
                }
                // System.out.println(dataLists);

                String datath = lists.get(35).get(2);

                //[1, 99.9566359, 29.7510063, , 1.58575026, , 64645]
                for (int i = 0; i < Integer.parseInt(datath); i++) {
                    DxcsData data=new DxcsData();

                    data.setXh(dataLists.get(i).get(0));
                    data.setLs(dataLists.get(i).get(1));
                    data.setQ(dataLists.get(i).get(2));
                    data.setRdc(dataLists.get(i).get(4));
                    data.setSrf(dataLists.get(i).get(6));

                    data.setCreateTime(TimeHelper.getCurrentTime());
                    data.setCreateUser((String)currentUser.getPrincipal());
                    data.setStatus("1");

                    // System.out.println(data);
                    data.setDxcsBase(base);

                    dataservce.savedxcs(data);

                }
            }

            res.setSuccess(SUCCESS);
            res.setMessage(OPERATE_SUCCESS);
            return res;

        }else {
            res.setSuccess(ERROR);
            res.setMessage(OPERATE_ERROR+"请检查您的文件格式是否有误!");
            return res;
        }


    }    

 效果:

 

参考链接 https://www.cnblogs.com/SimonHu1993/p/8202391.html

 

最后修改于 2019-02-22 15:58:15
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇