Using Java for Excel reading and writing

1: Add a dependency jar package that handles Excel

        <!--Introducing POI, Parsing workbook views-        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>${poi.version}</version>        </dependency >        <dependency>            <groupId>org.apache.poi</groupId>            <artifactid>poi-ooxml </artifactId>            <version>3.14</version>        </dependency>        <!-- Work with Excel and the above features are the same--        <dependency>            <groupId>net.sourceforge.jexcelapi</groupId>            <artifactId>jxl</artifactId>            <version>2.6.  10</version>        </dependency>  

2: Class that writes content to Excel

Writeexcel.java

 PackageCom.li.controller;ImportJava.io.File;ImportJava.io.FileInputStream;ImportJava.io.FileOutputStream;Importjava.io.IOException;ImportJava.io.OutputStream;Importjava.util.ArrayList;ImportJava.util.HashMap;Importjava.util.List;ImportJava.util.Map;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Sheet;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook; Public classWriteexcel {Private Static FinalString Excel_xls = "XLS"; Private Static FinalString excel_xlsx = "XLSX";  Public Static voidMain (string[] args) {Map<string, string> datamap=NewHashmap<string, string>(); Datamap.put ("Bankname", "Bankname"); Datamap.put ("Addr", "Addr"); Datamap.put ("Phone", "phone"); List<Map> list=NewArraylist<map>();        List.add (DATAMAP); Writeexcel (list,3, "D:/writeexcel.xlsx"); }     Public Static voidWriteexcel (list<map> dataList,intcloumncount,string Finalxlsxpath) {OutputStream out=NULL; Try {            //get total number of columns            intColumnnumcount =Cloumncount; //reading an Excel documentFile Finalxlsxfile =NewFile (Finalxlsxpath); Workbook Workbook=Getworkbok (Finalxlsxfile); //sheet corresponds to a work pageSheet Sheet = workbook.getsheetat (0); /*** Delete the original data except the attribute column*/            intRowNumber = Sheet.getlastrownum ();//the first line counts from 0.SYSTEM.OUT.PRINTLN ("Total rows of raw data, except attribute columns:" +rowNumber);  for(inti = 1; I <= RowNumber; i++) {row row=Sheet.getrow (i);            Sheet.removerow (row); }            //Create a file output stream, output a spreadsheet: This has to be, or anything you do on the sheet won't work.out =NewFileOutputStream (Finalxlsxpath);            Workbook.write (out); /*** Write new data into Excel*/             for(intj = 0; J < Datalist.size (); J + +) {                //Create a line: Start with the second row, skip the attribute columnRow row = Sheet.createrow (j + 1); //get each record that you want to insertMap DataMap =Datalist.get (j); String name= Datamap.get ("Bankname"). toString (); String Address= Datamap.get ("Addr"). toString (); String Phone= Datamap.get ("Phone"). toString ();  for(intk = 0; K <= Columnnumcount; k++) {                //loop within a lineCell first = Row.createcell (0);                        First.setcellvalue (name); Cell Second= Row.createcell (1);                        Second.setcellvalue (address); Cell Third= Row.createcell (2);                Third.setcellvalue (phone); }            }            //Create a file output stream and prepare the spreadsheet for output: This must be there, or anything you do on sheet will not work.out =NewFileOutputStream (Finalxlsxpath);        Workbook.write (out); } Catch(Exception e) {e.printstacktrace (); } finally{            Try {                if(Out! =NULL) {Out.flush ();                Out.close (); }            } Catch(IOException e) {e.printstacktrace (); }} System.out.println ("Data export succeeded"); }    /*** To determine the version of Excel, get workbook *@paramIn *@paramfilename *@return     * @throwsIOException*/     Public StaticWorkbook getworkbok (file file)throwsioexception{Workbook WB=NULL; FileInputStream in=Newfileinputstream (file); if(File.getname (). EndsWith (Excel_xls)) {//excel&nbsp;2003WB =NewHssfworkbook (in); }Else if(File.getname (). EndsWith (EXCEL_XLSX)) {//Excel 2007/2010WB =NewXssfworkbook (in); }        returnWB; }}

3: Read data from Excel and write to list

 PackageCom.li.controller;ImportJava.io.File;ImportJava.io.FileInputStream;Importjava.io.FileNotFoundException;Importjava.io.IOException;ImportJava.io.InputStream;Importjava.util.ArrayList;Importjava.util.List;ImportJXL. Sheet;ImportJXL. Workbook;Importjxl.read.biff.BiffException; Public classReadexcel { Public Static voidMain (string[] args) {Readexcel obj=NewReadexcel (); //Create an Excel path for me here: E:/ZHANHJ/STUDYSRC/JXLFile File =NewFile ("D:/readexcel.xls"); List excellist=obj.readexcel (file); System.out.println ("The data in the list prints out");  for(inti = 0; I < excellist.size (); i++) {List List=(List) excellist.get (i);  for(intj = 0; J < List.size (); J + +) {System.out.print (List.get (j));        } System.out.println (); }    }    //to read the Excel method Readexcel, the method's entry parameter is a file object     PublicList readexcel (file file) {Try {            //Create an input stream, read ExcelInputStream is =NewFileInputStream (File.getabsolutepath ()); //Workbook class provided by JXLWorkbook WB =Workbook.getworkbook (IS); //number of page signs in Excel            intSheet_size =wb.getnumberofsheets ();  for(intindex = 0; Index < sheet_size; index++) {List<List> outerlist=NewArraylist<list>(); //Create a Sheet object per page signSheet Sheet =Wb.getsheet (index); //sheet.getrows () returns the total number of rows for the page                 for(inti = 0; I < sheet.getrows (); i++) {List innerlist=NewArrayList (); //Sheet.getcolumns () returns the total number of columns for the page                     for(intj = 0; J < Sheet.getcolumns (); J + +) {String cellinfo=Sheet.getcell (J, i). getcontents (); if(Cellinfo.isempty ()) {Continue;                        } innerlist.add (Cellinfo);                    System.out.print (Cellinfo);                    } outerlist.add (I, innerlist);                System.out.println (); }                returnouterlist; }        } Catch(FileNotFoundException e) {e.printstacktrace (); } Catch(biffexception e) {e.printstacktrace (); } Catch(IOException e) {e.printstacktrace (); }        return NULL; }}

4: Create Readexcel.xls (with content) and writeexcel.xlsx below the D drive

Using Java for Excel reading and writing