当前位置：网站首页>Upload and download Excel files from the browser to the database

Upload and download Excel files from the browser to the database

2022-07-22 03:53:00 【The carnival of the lonely】

Excel Data format in ：
Insert picture description here
jsp page ：

<body>
<h2> Upload user information </h2>
<form method="post" action="<%= request.getContextPath() %>/user/excel/upload"
      enctype="multipart/form-data">
    <input type="file" name="file1">
    <input type="file" name="file2">
    <input type="text" name="username">
    <input type="submit" name="" value=" Upload ">
</form>

<a href="<%= request.getContextPath() %>/user/excel/output"> download </a>
</body>

maven The specific process of the project ：

1、 Needed jar package ：

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.1.1</version>
    </dependency>

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

    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.4</version>
</dependency>

2、 stay spring.xml Configure the upload file parser ： help Spring Handle uploaded files , After processing spring The processing object will also be encapsulated as MultipartFile object

 <!-- Uploaded file parser -->
    <bean id="multipartResolver"
          class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8"></property>
        <property name="maxInMemorySize" value="10485760"></property>
    </bean>

3、 Upload Excel file ：

First , Take from form The data uploaded by the form is encapsulated as an object ： One text、 Two file file

Encapsulated classes ：

public class FormItems {
    private String username;
    private MultipartFile file1;
    private MultipartFile file2;
    ...
    setter、getter A little 
}

Controller Layer code ：
utilize Spring Characteristics of , Automatically encapsulate form data into custom objects （ The data name in the form corresponds to the attribute name of the user-defined object ）

  // Upload Excel file 
 @RequestMapping("/excel/upload")
 @ResponseBody
public Result doImportExcel(FormItems items) {
        InputStream inputStream=null;
        InputStream inputStream2=null;
        // Just output the uploaded user name , There is no other operation ;
        System.out.println(" user name ："+items.getUsername());
        try {
        
            // Get to turn the file into an output stream 
            inputStream = items.getFile1().getInputStream();
            inputStream2 = items.getFile2().getInputStream();
            
            // hold Excel User data in is encapsulated in list
            List<User> list = OperateUploadFile.saveUploadFile(inputStream);
            List<User> list2 = OperateUploadFile.saveUploadFile(inputStream2);
            
            // hold list Inside user Put it all in the database 
            int save = userService.save(list);
            int save2 = userService.save(list2);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (inputStream2 != null) {
                try {
                    inputStream2.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
  }

In the code above ： hold Excel User data in is encapsulated in list The process encapsulates a method ：

public static List<User> saveUploadFile(InputStream inputStream) throws IOException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        // Get sheet The last row of data in ： Used for cyclic output subscript 
        int lastRowNum = sheet.getLastRowNum();
        System.out.println("lastRowNum = " + lastRowNum);

        // hold Excel All data is encapsulated in list In the assembly 
        List<User> list=new ArrayList<>();
        for (int i = 1; i <=lastRowNum ; i++) {
            Row row = sheet.getRow(i);
            User user = new User();
            user.setUsername(row.getCell(1).getStringCellValue());
            user.setPassword(row.getCell(2).getStringCellValue());
            list.add(user);
        }
        return  list;
    }

4、 download Excel file ：
Get everything in the database user After the data , Encapsulate into Workbook Object can ; Then put the workbook Object output .

Controller Layer code ：

@RequestMapping("/excel/output")
@ResponseBody
public void exportExcel(HttpServletResponse response) {
        // Get all of the database User data 
        List<User> allUser = userService.findAllUser();

        // add to Excel Title row of 
        String[] title = {"ID", "USERNAME", "PASSWORD"};
        // create a new workbook
        Workbook wb = new HSSFWorkbook();
        // create a new sheetL
        Sheet s = wb.createSheet();
        // declare a row object reference
        Row r = s.createRow(0);
        // declare a cell object reference
        for (int i = 0; i < title.length; i++) {
            r.createCell(i).setCellValue(title[i]);
        }

        // add to Excel data row 
        for (int i = 0; i < allUser.size(); i++) {
            User user = allUser.get(i);
            Row row = s.createRow(i+1);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUsername());
            row.createCell(2).setCellValue(user.getPassword());

        }

        //  Generate file name 
        String filename = UUID.randomUUID().toString() + ".xls";
        System.out.println("filename = " + filename);
        //  Handling Downloads 
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=" + filename);
        // Get the output object 
        // response.getWriter();
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
}

At the end of the article, please attach apache Of poi Official documents ：
http://poi.apache.org/components/spreadsheet/

原网站

版权声明
本文为[The carnival of the lonely]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/203/202207211116493282.html

当前位置：网站首页>Upload and download Excel files from the browser to the database

Upload and download Excel files from the browser to the database

边栏推荐

猜你喜欢

随机推荐