Application framework of Excel data dump database

Introduction: How to transfer the data in Excel to the relational database, has always been a topic of concern, this paper proposes a solution, that is, using the Apache POI to a certain Excel file to the database table mapping rules to parse the data, and then use JDBC or hibernate/j Technology such as PA to dump data into the database.

Introduction

With the continuous upgrading of enterprise IT systems, many enterprises have a strong need to upgrade legacy systems. and the integration of information resources in the legacy system is one of the most critical step, which can be used to data mining and other innovative work. This article involves such an application case, the enterprise's data information is the Excel file as its attachment way, the dispersed storage in the enterprise different server, the customer request the data information inside these files to dump into the database.

Readers may also encounter other scenarios that require the parsing of data from Excel files into the database.

This article provides a relatively efficient and intelligent solution for parsing Excel files and depositing them in a database.

Introduction to Apache POI (POI-HSSF and POI-XSSF)

POI is a subproject of the Apache Fund organization, POI (Poor obfuscation implementation) aims to provide a set of Java APIs to enable M in the Microsoft OLE 2 compound Document format Icrosoft Office files are easy to manipulate. HSSF (horrible spreadsheet format) is a pure Java implementation of the POI project Excel file Format (97-2007), through which developers can read, write, and modify Excel files using pure Java code. XSSF is a pure Java implementation of the Excel 2007 OOXML (. xlsx) file format.

This article mainly involves applying HSSF and XSSF to read data in Excel files.

Map metadata definition for Excel files to tables

How do you map the data in an Excel table to data in a relational database table? A two-dimensional table in a relational database is a structured data store, and an Sheet page of an Excel file may contain multiple pieces of information that can be mapped to a database table structure. This block of information can be very simple or complex. In this article, we define several mapping rules for Excel files to database tables for the actual situation of information blocks in Excel. These rules are the metadata we use to parse. Without these metadata, we are far from intelligent and efficient parsing.

Mapping rule One: Single cell mapping

A cell in an Excel table corresponds to a field (field) of the next table (a schema) in a relational database. such as: A1-> name

Mapping rule two: cell combination mappings

Multiple cells in an Excel table correspond to a field (field) in the next table (table) in a schema in a relational database. The combination is a string connection, such as C1,C2, F3-> address. You can define the delimiter for the string in the mapping rule, for example, in the example above, with a comma ",".