Read Excel file Write database Mybatis, POI, JXL

Nonsense not much to say, directly on the code structure diagram

The Lib package used

Students entity class

Package Com.test.model;public class Students {private int id;private String username;private int age;private int SALARY;PU Blic int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetUserName () {return username;} public void Setusername (String username) {this.username = username;} public int getage () {return age;} public void Setage (int.) {this.age = age;} public int getsalary () {return salary;} public void setsalary (int salary) {this.salary = salary;} @Overridepublic String toString () {return "Students [id=" + ID + ", username=" + Username + ", age=" + age+ ", salary=" + Salary + "]";} public Students (int ID, String username, int age, int salary) {//super (); this.id = Id;this.username = Username;this.age = Age;this.salary = salary;} Public Students () {//super ();}}

Mybatis configuration file

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration><environments default= "Development" ><environment id= "development" >< TransactionManager type= "JDBC"/><!--configuration database connection information--><datasource type= "pooled" ><property name= " Driver "value=" Com.mysql.jdbc.Driver "/><property name=" url "value=" Jdbc:mysql://localhost:3306/testmysql "/ ><property name= "username" value= "root"/><property name= "password" value= "1234"/></datasource ></environment></environments><!--configuration file for SQL statements--><mappers><mapper resource= "com/ Test/reader/sqlconfig/students.xml "/></mappers></configuration>

Students.xml SQL statements

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en"        "HTTP://MYBATIS.ORG/DTD/MYBATIS-3-MAPPER.DTD" > <mapper namespace= "Com.test.readerExcel.TestReaderExcelToDB" ><select id= "getstudent" resulttype= "map" >select * from students order by Id</select><select id= "Getstudentbyid" resulttype= "map" parametertype= "map ">select * from students where id = #{userid}</select><delete id=" Deletestuentbyid "parametertype=" Map "> Delete from students where id = #{userid}</delete><insert id= "insertstudent" parametertype= "map" >insert into students (username,age,salary) VALUES (#{username},#{age},#{salary}) </insert></mapper>

Sqlsessionutil

Package Com.test.util;import Java.io.inputstream;import Org.apache.ibatis.session.sqlsession;import Org.apache.ibatis.session.sqlsessionfactory;import Org.apache.ibatis.session.sqlsessionfactorybuilder;public Class Sqlsessionutil {public sqlsession getsqlsession () {String configfile = "mybatis_config.xml";//Use ClassLoader The MyBatis configuration file is loaded, the MyBatis configuration file is configured with the associated mapping file InputStream InputStream = SqlSessionUtil.class.getClassLoader (). getResourceAsStream (configfile);//Build sqlsession factory sqlsessionfactory sqlsessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream); Sqlsession sqlsession = Sqlsessionfactory.opensession (); return sqlsession;}}

Read the tool class for Excel

This read Excel2007 code a bit of a problem, do not know is the method problem, or My code problem, always the last few lines, yesterday looked for a long time did not find .... Really spit blood ....

Readerexcelutils

Package Com.excelutils;import Java.io.file;import Java.io.fileinputstream;import java.text.numberformat;import Java.util.arraylist;import java.util.hashmap;import java.util.linkedhashmap;import java.util.Map;import JXL. Sheet;import JXL. Workbook;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.dateutil;import Org.apache.poi.xssf.usermodel.xssfcell;import Org.apache.poi.xssf.usermodel.xssfrow;import Org.apache.poi.xssf.usermodel.xssfsheet;import org.apache.poi.xssf.usermodel.xssfworkbook;/** * Read Excel * @author Cyx * @since 2016-6-4 morning 1:03:23 */public class Readerexcelutils {/** * input Excel file, parse to return ArrayList * * @param file * Input Exce L File * * @return Arraylist<map&gt, where Map takes the contents of the first row as the key value */@SuppressWarnings ({"Unchecked", "rawtypes"}) public static Arra ylist<map> readerexcel (file file) {/* * Workbook: Workbook is the entire Excel document * Sheet: Worksheet * Cell: A cell * row: A row of */if (check Excel2007 (file) {return importToExcel2007 (file);} Initialize return values and fields an array group arraylist<map> arr = newArraylist<map> (); String[] title; Workbook Workbook = null;try{//Read Excel file Workbook = workbook.getworkbook (file);//Total sheet number int sheetnumber = Workbook.getnumberofsheets (); SYSTEM.OUT.PRINTLN ("Total number of sheet:" +sheetnumber); for (int i = 0; i < Sheetnumber; i++) {Sheet Sheet = Workbook.getsheet (i);//Current page total record number of rows and columns int rowCount = Sheet.getrows ();//Gets the number of rows int columecount = Sheet.getco Lumns ();//Gets the number of columns System.out.println ("Total number of records:" +rowcount); SYSTEM.OUT.PRINTLN ("Total number of columns:" +columecount);//First action field name, so the number of rows is greater than one to execute if (RowCount > 1 && columecount >0) {//Take the first column Field name title = new String[columecount];for (int j = 0; J < Columecount; J + +) {Title[j] = Sheet.getcell (j,0). getcontents (). t Rim ();} Take the current page all values into the list for (int h = 1; h < RowCount; h++) {//Line number Linkedhashmap DataMap = new Linkedhashmap (); for (int k = 0; K & Lt Columecount; k++) {//Number of columns Datamap.put (Title[k], Sheet.getcell (k,h). getcontents ()),//getcontents () Gets the value of the cell}arr.add (DATAMAP);}}} catch (Exception e) {e.printstacktrace ();} Finally{if (Workbook! = null) {WorkbooK.close (); workbook = null;}} return arr;} /** * Enter 2007 or more Excel files, parse and return ArrayList (there is a bug, temporarily not, reserved) * @param file * @return */@SuppressWarnings ("Rawtypes") public Static arraylist<map> importToExcel2007 (file file) {arraylist<map> arr = new arraylist<map> (); String[] title;//initialization fileinputstream readFile = null; Xssfworkbook workbook = null; Xssfrow row = null; Xssfsheet sheet = null; Xssfcell cell = null;try{//Read file ReadFile = new FileInputStream (file); workbook = new Xssfworkbook (readFile);//Document page int Numofsheets = Workbook.getnumberofsheets (); System.out.println ("Document pages:" +numofsheets); for (int i = 0; i < numofsheets; i++) {//Gets the current sheet (worksheet) sheet = Workbook.getsheetat (i);//Gets the number of rows of the current page int sheetrows = Sheet.getlastrownum (); System.out.println ("Current page total number of rows:" +sheetrows);//If the current page row number is greater than 0, first action field name if (Sheetrows > 0) {row = Sheet.getrow (0);//Current page First line int cells = Row.getlastcellnum ();//first row of cells title = new String[cells];for (int j = 0; J < cells; J + +) {//column blank, enter an empty string if (Row.getcell (j) = = null) {TITLE[J]= ""; continue;} Cell = Row.getcell (j); switch (Cell.getcelltype ()) {case Cell.cell_type_numeric:{integer num = new Integer ((int) Cell.getnumericcellvalue ()); Title[j] = string.valueof (num); break;} Case Cell.cell_type_string:{title[j] = Cell.getrichstringcellvalue (). toString (); DEFAULT:TITLE[J] = "";}} Branch resolution for (int j = 0; J < Sheetrows; J + +) {///If it is a blank line, continue to the next if (Sheet.getrow (j) = = null) {continue;} Place each row of data into map row = Sheet.getrow (j); Arr.add (Getcellmap (Row,cells,title));}}} catch (Exception e) {e.printstacktrace ();} Finally{try {readfile.close ();} catch (Exception e) {e.printstacktrace ()}} return arr;} /** * Determines whether Excel 2007 or more is based on the file name extension * @param file * @return */private static boolean checkExcel2007 (file file) {String Extendna me = File.getname (). substring (File.getname (). LastIndexOf (".")); if (Extendname.equals (". xlsx")) {return true;} return false;} /** * Based on incoming Excel row data, get map data * @param row * @param cells * @param title * @return */@SuppressWarnings ({"Unchecked", "Rawty PES "}) private static Map GetCEllmap (xssfrow row, int cells, string[] title) {//Initialize map data = new HashMap (); Xssfcell cell = null;//columns for (int i = 0; i < cells; i++) {//Column null, enter an empty string if (Row.getcell (i) = = null) {Data.put (Title[i], "" ); continue;} Cell = Row.getcell (i); switch (Cell.getcelltype ()) {Case Cell.cell_type_numeric:{if (dateutil.iscelldateformatted ( Cell) {data.put (Title[i], cell.getdatecellvalue ());} Else{numberformat NF = numberformat.getinstance () nf.setgroupingused (false);d Ata.put (Title[i], Nf.format ( Cell.getnumericcellvalue ()));} break;} Case Cell.cell_type_string:{data.put (Title[i], cell.getrichstringcellvalue ()); Case Cell.cell_type_boolean:{data.put (Title[i], cell.getbooleancellvalue ()); Default:data.put (Title[i], "");}} return data;}}

Some of the Java bean methods are very useful .... See more Project Code ...

Package Com.beanutils;import java.util.map;/** * JavaBean Tool method * @author Cyx * @since 2016-6-4 morning 12:16:23 */public class M ybeanutils {/** * Convert the Get property in JavaBean to map *  * <pre> * properties that meet the copy criteria must meet the following: * 1. Properties with Get method in Source JavaBean * </pre> ; *  * @param bean *            Source Object JavaBean * @return converted map * @throws Exception */public static map describe (object bean) throw S Exception{return org.apache.commons.beanutils.BeanUtils.describe (bean);}}

Test the Main method

Package Com.test.readerexcel;import Java.io.file;import Java.util.date;import java.util.iterator;import Java.util.list;import Java.util.map;import Java.util.set;import Org.apache.ibatis.session.sqlsession;import Com.beanutils.mybeanutils;import Com.excelutils.readerexcelutils;import Com.test.model.students;import Com.test.util.sqlsessionutil;public class Testreaderexceltodb {private static readerexcelutils Reu = new Readerexcelutils ();p rivate static sqlsessionutil su = new Sqlsessionutil ();p rivate static sqlsession session = Su.getsqls Ession ();p rivate static String className = TestReaderExcelToDB.class.getName () + "."; public static void Main (string[] args) throws Exception {inserttodatabase ();} /** * Read the Excel file and save the data to the database * * @param data * @throws Exception * */@SuppressWarnings ({"Rawtypes", "Static-ac Cess "}) public static void Inserttodatabase () throws Exception {Date date = new Date (); Long time = Date.gettime (); String excelfilename = "D://export Excel by Mybatis.xls"; File File = new file (excelfilename); List<map> Datalistmap = Reu. Readerexcel (file); Iterator it = Datalistmap.iterator (); Students Students = new Students (); Mybeanutils mu = new mybeanutils (), try {while (It.hasnext ()) {Map Onemap = (map) it.next (); Students.setusername (onemap.ge T ("name"). ToString ()); Students.setage (Integer.parseint (Onemap.get ("Age"). ToString ()); Students.setsalary ( Integer.parseint (Onemap.get ("wages"). ToString ())); Map Beanmap = mu.describe (students); int result = 0;try {result = Session.insert (ClassName + "insertstudent", Beanmap); if (r Esult < 1) {System.out.println ("Insert database Error");} Session.commit ();} catch (Exception e) {e.printstacktrace (); Session.rollback ();}} SYSTEM.OUT.PRINTLN ("Insert Database All");D ate date2 = new Date (); Long time2 = Date2.gettime (); long longs = time2-time; System.out.println (longs);} catch (Exception e) {e.printstacktrace ();}} /** * Get the key value in Map * * @param dataMap * @return */public static string[] Getmapkey (Map dataMap) {string[] Keyarr = new STR Ing[datamap.size ()];if (dAtamap.size () < 0 | | DataMap = = null) {SYSTEM.OUT.PRINTLN ("map is empty"); return null;} String keystr = ""; Set set = Datamap.keyset (); for (Object Name:set) {keystr + = name + ",";} Keystr = keystr.substring (0, Keystr.length ()-1), Keyarr = Keystr.split (","); return Keyarr;}}

Excel file Contents

After execution, the database has ....

Before testing on the company computer, 1500 data of Excel spent 75 seconds or so ...

At night home with your own computer, only 2, 3 seconds .... Company computer is really slag ....

The hands of a cheap try 5,000 article. Just five seconds ....

Ah, really ...

Read Excel file Write database Mybatis, POI, JXL