Order small program, order system, management background batch import excel dishes data

During the period when the ordering system went online, many students gave feedback on whether a batch import function of dishes could be added. Because I am busy at ordinary times, I have no time to add the function of batch import of dishes. Today is just free time, let's teach you to realize the function of batch import of dishes. Later, this function will be recorded as a video and put into the course of ordering system.

Portal: Ordering system, java background + ordering applet

Look at the renderings first

Choose excel dishes

Import data succeeded

The students who have seen my course before must know that I didn't import the category in batch before. Yes, this category is our new function today.

The implementation steps are simple:

  • 1. Click the import button to select excel
  • 2, the import is successfully transferred to the merchandise list page.

Let's explain the implementation steps in detail

1, Introduction of excel operation class library

We mainly use the two class libraries in the red box below

The class library is written in pom.xml. Don't forget to do ReImport

2, Add background web page imported into excel

Add dish category import page

Add product (DISH) import page

The above code, I will add to the ordering system, students who purchase ordering system courses, to refresh the previous SkyDrive link to get the latest code.

3, Write ExcelUtil tool class

It is very simple to post the complete code to you, that is, to define a method to import dishes and categories in the tool class.

Note: the corresponding import method is to analyze the data in excel, so your excel data must be consistent with mine, that is, what data is in the first column, which should correspond to mine. Otherwise, it will lead to wrong data storage.

package com.qcl.utils;

import com.qcl.dataobject.ProductCategory;
import com.qcl.dataobject.ProductInfo;
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.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import lombok.extern.slf4j.Slf4j;

/*
 * Operation excel
 * */
@Slf4j
public class ExcelUtils {

    /*
     * Batch import of dishes category
     * Requirement
     * 1,excel file that must end with. xlsx
     * 2,The table contents must be in the following order
     * 0: Category name, 1: type value
     *
     * */
    public static List<ProductCategory> excelToProductCategoryList(InputStream inputStream) {
        List<ProductCategory> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //Worksheet object
            Sheet sheet = workbook.getSheetAt(0);
            //Total number of banks
            int rowLength = sheet.getLastRowNum();
            System.out.println("How many lines are there in the total row" + rowLength);
            //Columns of sheet
            Row row = sheet.getRow(0);

            //Total column number
            int colLength = row.getLastCellNum();
            System.out.println("How many columns are there in total" + colLength);
            //Gets the specified cell
            Cell cell = row.getCell(0);
            for (int i = 1; i <= rowLength; i++) {
                ProductCategory goodInfo = new ProductCategory();
                row = sheet.getRow(i);
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        //Column: 0: category name, 1: type value
                        if (j == 0) {
                            goodInfo.setCategoryName(data);
                        } else if (j == 1) {
                            goodInfo.setCategoryType(Integer.parseInt(data));
                        }
                    }
                }
                list.add(goodInfo);
//                log.error("data per line = {}", menuInfo);
            }
        } catch (Exception e) {
            log.error("excel Error thrown by import={}", e);
        }
        return list;
    }

    /*
     * Batch import of dishes (commodities)
     * Requirement
     * 1,excel file that must end with. xlsx
     * 2,The table contents must be in the following order
     * 0 Commodity name, 1 unit price, 2 inventory, 3 category, 4 description, 5 picture links
     *
     * */
    public static List<ProductInfo> excelToProductInfoList(InputStream inputStream) {
        List<ProductInfo> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //Worksheet object
            Sheet sheet = workbook.getSheetAt(0);
            //Total number of banks
            int rowLength = sheet.getLastRowNum();
            //Columns of sheet
            Row row = sheet.getRow(0);
            //Total column number
            int colLength = row.getLastCellNum();
            //Gets the specified cell
            Cell cell = row.getCell(0);
            for (int i = 1; i <= rowLength; i++) {
                ProductInfo goodInfo = new ProductInfo();
                row = sheet.getRow(i);
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        //Column: 0 trade name, 1 unit price, 2 inventory, 3 category, 4 description, 5 picture link
                        if (j == 0) {
                            goodInfo.setProductId(KeyUtil.genUniqueKey());
                            goodInfo.setProductName(data);
                        } else if (j == 1) {
                            goodInfo.setProductPrice(new BigDecimal(data));
                        } else if (j == 2) {
                            goodInfo.setProductStock(Integer.parseInt(data));
                        } else if (j == 3) {
                            goodInfo.setCategoryType(Integer.parseInt(data));
                        } else if (j == 4) {
                            goodInfo.setProductDescription(data);
                        } else if (j == 5) {
                            goodInfo.setProductIcon(data);
                        }
                    }
                }
                list.add(goodInfo);
            }
        } catch (Exception e) {
            log.error("excel Error thrown by import={}", e);
        }
        return list;
    }
}

4, Write corresponding interface

After the above tool class is encapsulated, we need to add the method of importing data in the corresponding Controller class next.

  • 1. Introduction of dishes

    One is to open the imported web page, the other is to realize the function of importing data. The complete code is posted to you

    /*
     * excel Import webpage
     * */
    @GetMapping("/excel")
    public ModelAndView excel(Map<String, Object> map) {
        return new ModelAndView("category/excel", map);
    }
    
    /*
     * Batch import the dishes in excel to the database
     * */
    @RequestMapping("/uploadExcel")
    @ResponseBody
    public ModelAndView uploadExcel(@RequestParam("file") MultipartFile file,
                                    Map<String, Object> map) {
        String name = file.getOriginalFilename();
        if (name.length() < 6 || !name.substring(name.length() - 5).equals(".xlsx")) {
            map.put("msg", "File format error");
            map.put("url", "/sell/seller/category/excel");
            return new ModelAndView("common/error", map);
        }
        List<ProductCategory> list;
        try {
            list = ExcelUtils.excelToProductCategoryList(file.getInputStream());
            log.info("excel Imported list={}", list);
            if (list == null || list.size() <= 0) {
                map.put("msg", "Import failed");
                map.put("url", "/sell/seller/category/excel");
                return new ModelAndView("common/error", map);
            }
            //excel data saved to database
            try {
                for (ProductCategory excel : list) {
                    if (excel != null) {
                        //If the category type value already exists, it will not be imported
                        List typeList = categoryService.findOneByType(excel.getCategoryType());
                        log.info("Query category type Does it exist? typeList={}", typeList);
                        if (typeList == null || typeList.size() < 1) {
                            System.out.println("Save successfully");
                            categoryService.save(excel);
                        }
                    }
                }
            } catch (Exception e) {
                log.error("Failed to save a row to database={}", e);
            }
        } catch (Exception e) {
            e.printStackTrace();
            map.put("msg", e.getMessage());
            map.put("url", "/sell/seller/category/excel");
            return new ModelAndView("common/error", map);
        }
        map.put("url", "/sell/seller/category/list");
        return new ModelAndView("common/success", map);
    }
  • 2. Import food data

    The code is posted for you

    /*
     * excel Import webpage
     * */
    @GetMapping("/excel")
    public ModelAndView excel(Map<String, Object> map) {
        return new ModelAndView("product/excel", map);
    }
    
    /*
     * Batch import the dishes (commodities) in excel to the database
     * */
    @RequestMapping("/uploadExcel")
    @ResponseBody
    public ModelAndView uploadExcel(@RequestParam("file") MultipartFile file,
                                    Map<String, Object> map) {
        String name = file.getOriginalFilename();
        if (name.length() < 6 || !name.substring(name.length() - 5).equals(".xlsx")) {
            map.put("msg", "File format error");
            map.put("url", "/sell/seller/product/excel");
            return new ModelAndView("common/error", map);
        }
        List<ProductInfo> list;
        try {
            list = ExcelUtils.excelToProductInfoList(file.getInputStream());
            log.info("excel Imported list={}", list);
            if (list == null || list.size() <= 0) {
                map.put("msg", "Import failed");
                map.put("url", "/sell/seller/product/excel");
                return new ModelAndView("common/error", map);
            }
            //excel data saved to database
            try {
                for (ProductInfo excel : list) {
                    if (excel != null) {
                        //If the category type value already exists, it will not be imported
                        productService.save(excel);
                    }
                }
            } catch (Exception e) {
                log.error("Failed to save a row to database={}", e);
            }
    
        } catch (Exception e) {
            e.printStackTrace();
            map.put("msg", e.getMessage());
            map.put("url", "/sell/seller/product/excel");
            return new ModelAndView("common/error", map);
        }
        map.put("url", "/sell/seller/product/list");
        return new ModelAndView("common/success", map);
    }

Here we have fully realized the function that the ordering system imports the dish data into the database in batches.

I will put the complete code into the ordering system. The students who buy my ordering system course, remember to refresh the previous online disk link to get the latest code

The demo video has also been recorded. You can watch it

https://edu.51cto.com/course/19004.html

The complete ordering system, including Java background and scanning code ordering applet, is shown below.

Tags: Javascript Excel Database Java Apache

Posted on Mon, 03 Feb 2020 03:17:20 -0800 by Spoiler