Upload Excel file into database using Apache POI a

2020-05-29 07:41发布

问题:

Couldn't find a good post on the internet to help me with this.

My requirement is to read each row from a spreadsheet, and generate a sql statement with values from the cells and do a batch upload when reading the spreadsheet is done.

I am using Apache POI, Spring framework and JDBC.

How should i generate the sqls from excel?

  1. Have a sql statement with args (?) and format with the cell contents?

or

  1. Prepare a sql by concatenating the cell contents?

What's the best way to do this??

回答1:

I was going to do the same thing some weeks ago, ended up with the following solution for the excel part of your question. This solution support both the new and the 97-2007 sheet format. I am using spring and POI. I dont think it is posible to answer the rest of your question without more information.

the jsp site where the user upload the file:

<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>import</title>
</head>
<body>
    <form:form modelAttribute="fileBean" method="post" enctype="multipart/form-data">
        <form:label for="fileData" path="fileData">Select file</form:label><br/><br/>
        <form:input path="fileData" type="file"/>
        <input type="submit" />
    </form:form>
</body>
</html>

the controler that will be triggered on submit

@Controller
@RequestMapping("/upload")
public class ExcelImporterController {

    @RequestMapping(method = RequestMethod.POST)
    public String upload(FileBean uploadItem, BindingResult result) {
        importService.import(uploadItem);

        return "import/importDone";
    }

}

interface..

public interface importService {

    public void import(FileBean fileBean);
}

implementation of the interface with the import method..

@Override
    public void import(FileBean fileBean) {

        ByteArrayInputStream bis = new ByteArrayInputStream(filedBean.getFileData().getBytes());
        Workbook workbook;
        try {
            if (fileBean.getFileData().getOriginalFilename().endsWith("xls")) {
                workbook = new HSSFWorkbook(bis);
            } else if (fileBean.getFileData().getOriginalFilename().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(bis);
            } else {
                throw new IllegalArgumentException("Received file does not have a standard excel extension.");
            }

            for (Row row : sheet) {
               if (row.getRowNum() == 0) {
                  Iterator<Cell> cellIterator = row.cellIterator();
                  while (cellIterator.hasNext()) {
                      Cell cell = cellIterator.next();
                      //go from cell to cell and do create sql based on the content
                  }
               }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

the configuration of the bean that will be used for the spring upload in the FileBean..

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="maxUploadSize" value="10000000"/>
</bean>

the file bean

public class FileBean {

  private CommonsMultipartFile fileData;

  public CommonsMultipartFile getFileData()
  {
    return fileData;
  }

  public void setFileData(CommonsMultipartFile fileData)
  {
    this.fileData = fileData;
  }

}



回答2:

Check these links

Apache Quick Guide

Read and Write Excel using POI

Read and Write Excel - Example 2