Java read Excel gets data written to another Excel

Pom.xml

<?xml version= "1.0" encoding= "UTF-8"? ><project xmlns= "http://maven.apache.org/POM/4.0.0" xmlns:xsi= "http: Www.w3.org/2001/XMLSchema-instance "xsi:schemalocation=" http://maven.apache.org/POM/4.0.0/http Maven.apache.org/xsd/maven-4.0.0.xsd "> <modelVersion>4.0.0</modelVersion> <groupId> Com.nihaorz</groupid> <artifactId>excel_hezhan</artifactId> <version>1.0-snapshot</            version> <dependencies> <dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version>1.0.6</version> </dependency > <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifact id>jxl</artifactid> <version>2.6.12</version> </dependency> </dependenc Ies></project>

　　

Excelreader.java

Package Com.nihaorz;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.DataFormatter; Import Org.apache.poi.ss.usermodel.dateutil;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.ss.usermodel.workbookfactory;import Java.io.file;import Java.io.fileinputstream;import Java.io.ioexception;import java.util.arraylist;import java.util.hashmap;import Java.util.List;import java.util.Map    ;p Ublic class Excelreader {private String filePath;    Private String SheetName;    Private Workbook Workbook;    Private Sheet Sheet;    Private list<string> columnheaderlist;    Private list<list<string>> ListData;    Private list<map<string,string>> MapData;    Private Boolean flag;        Public Excelreader (String FilePath, String sheetname) {This.filepath = FilePath;        This.sheetname = SheetName;        This.flag = false;    This.load ();  }  Public map<string, String> Getalldata () {map<string, string> Map = new HashMap ();        String prefix = "Left (\" ";        String suffix = "\", 19) ";            for (int i = 0; i < listdata.size (); i++) {list<string> List = Listdata.get (i);            List<string> List1 = new ArrayList ();                for (int j = 0; J < List.size (); j + +) {String str = list.get (j); if (str.startswith (prefix) && str.endswith (suffix)) {str = str.substring (Prefix.length (), str.la                Stindexof (suffix));            } list1.add (str);        } map.put (List1.get (0), List.get (1));    } return map;        } private void Load () {FileInputStream instream = null;            try {instream = new FileInputStream (new File (FilePath));            WorkBook = Workbookfactory.create (instream);        Sheet = Workbook.getsheet (sheetname);  } catch (Exception e) {          E.printstacktrace ();                }finally{try {if (instream!=null) {instream.close ();            }} catch (IOException e) {e.printstacktrace ();        }}} private String Getcellvalue (cell cell) {String cellvalue = "";        Dataformatter formatter = new Dataformatter ();                    if (cell! = NULL) {switch (Cell.getcelltype ()) {case Cell.cell_type_numeric:                    if (dateutil.iscelldateformatted (cell)) {Cellvalue = Formatter.formatcellvalue (cell);                        } else {Double value = Cell.getnumericcellvalue ();                        int intvalue = (int) value; Cellvalue = Value-intvalue = = 0?                    String.valueof (intvalue): string.valueof (value);                } break; Case Cell.CELL_TYPE_STRING:cellValue = CELl.getstringcellvalue ();                Break                    Case Cell.CELL_TYPE_BOOLEAN:cellValue = string.valueof (Cell.getbooleancellvalue ());                Break                    Case Cell.CELL_TYPE_FORMULA:cellValue = string.valueof (Cell.getcellformula ());                Break                    Case Cell.CELL_TYPE_BLANK:cellValue = "";                Break                    Case Cell.CELL_TYPE_ERROR:cellValue = "";                Break                    Default:cellvalue = cell.tostring (). Trim ();            Break    }} return Cellvalue.trim ();        } private void Getsheetdata () {listData = new arraylist<list<string>> ();        MapData = new arraylist<map<string, string>> ();        Columnheaderlist = new arraylist<string> ();        int numofrows = Sheet.getlastrownum () + 1; for (int i = 0; I &Lt Numofrows;            i++) {Row row = Sheet.getrow (i);            map<string, string> map = new hashmap<string, string> ();            list<string> list = new arraylist<string> (); if (row! = null) {for (int j = 0; J < Row.getlastcellnum (); j + +) {Cell cell = ROW.G                    Etcell (j);                    if (i = = 0) {Columnheaderlist.add (Getcellvalue (cell));                    } else{Map.put (Columnheaderlist.get (j), This.getcellvalue (cell));                } list.add (This.getcellvalue (cell));            }} if (i > 0) {mapdata.add (map);        } listdata.add (list);    } flag = true;        Public String getcelldata (int row, int col) {if (row<=0 | | col<=0) {return null;        } if (!flag) {this.getsheetdata ();       } if (Listdata.size () >=row && listdata.get (row-1). Size () >=col) {return listdata.get (row-1). Get (col-        1);        }else{return null;        }} public String getcelldata (int row, string headername) {if (row<=0) {return null;        } if (!flag) {this.getsheetdata (); } if (Mapdata.size () >=row && mapdata.get (row-1). ContainsKey (Headername)) {return Mapdata.get (r        ow-1). Get (Headername);        }else{return null; }    }}

　　

Exceltest.java

Package Com.nihaorz;import JXL. Cell;import JXL. Sheet;import JXL. Workbook;import Jxl.write.label;import Jxl.write.writablecellformat;import Jxl.write.writablefont;import Jxl.write.writablesheet;import Jxl.write.writableworkbook;import Java.io.file;import Java.util.Map;public class exceltest {public static void main (string[] args) throws Exception {Excelreader eh = new Excelreader ("C:\\user        S\\nihaorz\\desktop\\ station. xlsx "," Sheet1 ");        Eh.getcelldata (a);        map<string, string> map = Eh.getalldata ();        String FilePath = "c:\\users\\nihaorz\\desktop\\ pending modification. xls";        Excel obtains files Workbook Workbook = workbook.getworkbook (new file (FilePath));        Open a copy of a file, and specify that the data is written back to the original file Writableworkbook book = workbook.createworkbook (new file (FilePath), Workbook);        Sheet Sheet = book.getsheet (0);        Writablesheet wsheet = book.getsheet (0);        int colunms = Sheet.getcolumns (); for (int i = 0; i < sheet.getrows (); i++) {StrinG number = Sheet.getcell (4, I). getcontents (). Trim ();                if (Map.containskey (number)) {Cell cell = Wsheet.getcell (i);                String address = cell.getcontents (). Trim (); if (address = = NULL | |                    ". Equals (address)) {Label label = new label (COLUNMS, I, Map.get (number), Getdatacellformat ());                Wsheet.addcell (label);        }}} book.write ();    Book.close (); } public static Writablecellformat Getdatacellformat () {writablefont wf = new Writablefont (Writablefont.times,        Ten, Writablefont.bold, false);        Writablecellformat WCF = new Writablecellformat (WF);    return WCF; }}

　　

Java read Excel gets data written to another Excel