Java Read and write Excel (POI, XLS and xlsx two formats supported)

This should be an all-in-one example, and more complex functions can be expanded on this basis. This example is based on the Apache POI Class Library, and the associated jar package is not listed. This kind of library is very general, the Internet is very easy to find.

1. Write Excel that does not contain cell merges

/** * Excel export to output stream * who is responsible for closing the output stream * @param os output stream * @param excelextname Excel file extension, XLS and xlsx, without dot * @param data * @throws IOException */public static void Writeexcel (OutputStream os, String excelextname, map<string, list<list<string >>> data) throws Ioexception{workbook WB = null;try {if ("xls". Equals (Excelextname)) {wb = new Hssfworkbook ();} E LSE if ("XLSX". Equals (Excelextname)) {wb = new Xssfworkbook ();} else {throw new Exception ("The current file is not an Excel file");} For (String SheetName:data.keySet ()) {Sheet Sheet = Wb.createsheet (sheetname); List<list<string>> rowlist = Data.get (SheetName); for (int i = 0; i < rowlist.size (); i++) {LIST&LT;STRING&G T Celllist = Rowlist.get (i); Row row = Sheet.createrow (i); for (int j = 0; J < Celllist.size (); j + +) {cell cell = Row.createcell (j); Cell.setcellvalue (Celllist.get (j));}} Wb.write (OS);} catch (Exception e) {e.printstacktrace ();} finally {if (WB! = null) {Wb.close ();}}}

2. Write Excel with cell merge

Auxiliary VO

Class Exceldata{private String value;//cell value private int colSpan = 1;//cell spans several columns private int rowSpan = 1;//cell spans several rows private Boolea n aligncenter;//whether the cell is centered, default is not centered, and if it is selected, the public boolean isaligncenter () {return aligncenter is centered horizontally and vertically;} public void Setaligncenter (Boolean aligncenter) {this.aligncenter = AlignCenter;} Public String GetValue () {return value;} public void SetValue (String value) {this.value = value;} public int Getcolspan () {return colSpan;} public void Setcolspan (int colSpan) {this.colspan = ColSpan;} public int Getrowspan () {return rowSpan;} public void Setrowspan (int rowSpan) {this.rowspan = RowSpan;}}

Logic for writing Excel files

/** * Excel export to output stream * who is responsible for shutting down the output stream * @param os output stream * @param excelextname Excel file extension, XLS and xlsx support, no dot * @param data Excel, MA The key in P is the name of the tab, and the list of value corresponds to the data in the tab page. A child list in a list is a row in a tab, and the object in the sub list is the data for a cell, including whether it is centered, spans several rows, and how many values are stored * @throws ioexception */public static void Testwrite ( OutputStream OS, String excelextname, map<string, list<list<exceldata>>> data) throws ioexception{ Workbook wb = null;  CellStyle CellStyle = Null;boolean isxls;try {if ("xls". Equals (Excelextname)) {wb = new Hssfworkbook (); isxls = true;} else if ("xlsx". Equals (Excelextname)) {wb = new Xssfworkbook (); isxls = false;} else {throw new Exception ("The current file is not an Excel file");} CellStyle = Wb.createcellstyle (); if (Isxls) {cellstyle.setalignment (hssfcellstyle.align_center); Cellstyle.setverticalalignment (Hssfcellstyle.vertical_center);} else {cellstyle.setalignment (xssfcellstyle.align_center); Cellstyle.setverticalalignment (XSSFCellStyle.VERTICAL_ CENTER);} For (String SheetName:data.keySet ()) {Sheet Sheet = Wb.createsheet (SheetName); List<list<exceldata>> rowlist = Data.get (sheetname);//i represents the first line starting from 0 for (int i = 0; i < rowlist.size (); i++) {list<exceldata> celllist = Rowlist.get (i); Row row = Sheet.createrow (i); int J = 0;//j represents the first few columns starting from 0 for (Exceldata exceldata:celllist) {if (exceldata! = null) {if (exc Eldata.getcolspan () > 1 | | Exceldata.getrowspan () > 1) {cellrangeaddress CRA = new Cellrangeaddress (i, i + Exceldata.getrowspan ()-1, J, J + exce Ldata.getcolspan ()-1); sheet.addmergedregion (CRA);} Cell cell = Row.createcell (j); Cell.setcellvalue (Exceldata.getvalue ()); if (Exceldata.isaligncenter ()) { Cell.setcellstyle (CellStyle);} j = j + Exceldata.getcolspan ();} else {j + +;}}}} Wb.write (OS);} catch (Exception e) {e.printstacktrace ();} finally {if (WB! = null) {Wb.close ();}}}

Test code

public static void Main (string[] args) throws IOException {map<string, list<list<exceldata>>> data = NE W linkedhashmap<> (); list<list<exceldata>> Sheet1 = new arraylist<> ();//First page list<exceldata> List1 = new ArrayList <> ();//First line Exceldata Exceldata = new Exceldata ();//First cell exceldata.setcolspan (6); Exceldata.setrowspan (1); Exceldata.setvalue ("xxx"); Exceldata.setaligncenter (true); List1.add (Exceldata); list<exceldata> list2 = new arraylist<> ();//second row exceldata = new Exceldata ();//First cell Exceldata.setcolspan (1 ); Exceldata.setrowspan (1); Exceldata.setvalue ("a"); List2.add (exceldata); exceldata = new Exceldata ();// The second cell exceldata.setcolspan (1); Exceldata.setrowspan (1); Exceldata.setvalue ("B"); List2.add (exceldata); excelData = New Exceldata ();///Third cell exceldata.setcolspan (2); Exceldata.setrowspan (4); Exceldata.setvalue ("C"); Exceldata.setaligncenter (True); List2.add (exceldata); exceldata = new Exceldata ();//Fourth Cell exceldata.setcolspan (2); Exceldata.setRowSpan (2); Exceldata.setvalue ("D"); Exceldata.setaligncenter (true); List2.add (Exceldata); list<exceldata> list3 = new arraylist<> ();//third row exceldata = new Exceldata ();//First cell Exceldata.setcolspan (1 ); Exceldata.setrowspan (1); Exceldata.setvalue ("E"); List3.add (exceldata); exceldata = new Exceldata ();// The second cell exceldata.setcolspan (1); Exceldata.setrowspan (1); Exceldata.setvalue ("F"); List3.add (Exceldata); List3.add ( NULL);//Third cell list3.add (NULL),//Fourth cell list3.add (NULL),//Fifth cell list3.add (null);//Sixth cell list<exceldata> List4 = new arraylist<> ();//Fourth line exceldata = new Exceldata ();//First cell exceldata.setcolspan (1); Exceldata.setrowspan (1); Exceldata.setvalue ("I"); List4.add (exceldata); exceldata = new Exceldata ();// The second cell exceldata.setcolspan (1); Exceldata.setrowspan (1); Exceldata.setvalue ("J"); List4.add (Exceldata); List4.add ( NULL);//Third cell list4.add (NULL),//Fourth cell exceldata = new Exceldata ();//Fifth cell exceldata.setrowspan (1); Exceldata.setcolspan (1); Exceldata.setvalue ("G"); List4.add (exceldata); ExceldATA = new Exceldata ();//Sixth cell exceldata.setrowspan (1); Exceldata.setcolspan (1); Exceldata.setvalue ("H"); List4.add ( Exceldata); list<exceldata> list5 = new arraylist<> ();//Line Fifth Exceldata = new Exceldata ();//First cell Exceldata.setcolspan (1 ); Exceldata.setrowspan (1); Exceldata.setvalue ("K"); List5.add (exceldata); exceldata = new Exceldata ();// The second cell exceldata.setcolspan (1); Exceldata.setrowspan (1); Exceldata.setvalue ("L"); List5.add (Exceldata); List5.add ( NULL);//Third cell list5.add (NULL),//Fourth cell exceldata = new Exceldata ();//Fifth cell exceldata.setrowspan (1); Exceldata.setcolspan (1); Exceldata.setvalue ("M"); List5.add (exceldata); exceldata = new Exceldata ();// The sixth cell Exceldata.setrowspan (1); Exceldata.setcolspan (1); Exceldata.setvalue ("n"); List5.add (Exceldata); Sheet1.add ( List1); Sheet1.add (List2); Sheet1.add (LIST3); Sheet1.add (LIST4) sheet1.add (LIST5);d ata.put ("Table 1", Sheet1); testwrite (New FileOutputStream ("D:/temp/my.xlsx"), "xlsx", Data);}}

3, read Excel, the return value of this method with a little business logic, for Excel without cell merging, and the first row is the case of title. In the returned result, wrapping each cell outside the first row into a map,key is the value of the first row of the cell, which is the title, and the values are the cells. Logic is a bit around, just look at the code.

/** * Applicable expire line is the header row of Excel, for example * Name Age gender Height * Zhang 325 men 175 * Li 422 female 160 * Each row constitutes a Map,key value is a column header, value is a column value. Cells without values whose value value is null * Returns the outermost list of results for an Excel file, the second-level list corresponds to a sheet page, the third-level map corresponds to a row in the sheet page * @throws Exception */public Static list<list<map<string, string>>> Readexcelwithtitle (String filepath) throws Exception{string FileType = filepath.substring (Filepath.lastindexof (".") + 1, filepath.length ()); InputStream is = null;    Workbook wb = null;try {is = new FileInputStream (filepath);    if (filetype.equals ("xls")) {wb = new Hssfworkbook (IS);    } else if (Filetype.equals ("xlsx")) {wb = new Xssfworkbook (IS);    } else {throw new Exception ("Read not Excel file"); } list<list<map<string, string>>> result = new arraylist<list<map<string,string>>&    gt; ();//corresponds to excel file int sheetsize = Wb.getnumberofsheets ();    for (int i = 0; i < sheetsize; i++) {//Traverse Sheet page Sheet Sheet = Wb.getsheetat (i); List<map<string, string>> sheetlist= new arraylist<map<string, string>> ();//corresponds to sheet page list<string> titles = new ARRAYLIST&LT;STRING&G    t; ();//Place all headings int rowsize = Sheet.getlastrownum () + 1;    for (int j = 0; J < Rowsize; J + +) {//traverse rows Row row = Sheet.getrow (j);    if (row = = null) {//skip blank line continue;}  int cellsize = Row.getlastcellnum ();//The number of cells in the row, that is, how many columns if (j = = 0) {///The first row is the header row for (int k = 0; k < cellsize; k++)    {Cell cell = Row.getcell (k);    Titles.add (Cell.tostring ());  }} else {//other rows are data rows map<string, string> rowmap = new hashmap<string, string> ();//corresponds to a data row for (int k = 0; K < Titles.size ();    k++) {Cell cell = Row.getcell (k);    String key = Titles.get (k);    String value = null;    if (cell! = NULL) {value = Cell.tostring ();}    Rowmap.put (key, value);    } sheetlist.add (Rowmap);    }} result.add (Sheetlist); } return result; catch (FileNotFoundException e) {throw e;} finally {if (WB! = null) {Wb.close ();} if (IS! = NULL) {is.close ();}}} 

4. Read Excel, suitable for cases where there are no merged cells and no header row

/** * for Excel without header row, for example * Zhang San 25 years old 175cm * John Doe 22 year old female 160cm * each row constitutes a Map,key value is a column header, value is a column value. Cells without values whose value value is null * Returns the outermost list of results for an Excel file, the second-level list corresponds to a sheet page, the third-level map corresponds to a row in the sheet page * @throws Exception */public Static list<list<list<string>>> Readexcelwithouttitle (String filepath) throws Exception{string FileType = filepath.substring (Filepath.lastindexof (".") + 1, filepath.length ()); InputStream is = null;    Workbook wb = null;try {is = new FileInputStream (filepath);    if (filetype.equals ("xls")) {wb = new Hssfworkbook (IS);    } else if (Filetype.equals ("xlsx")) {wb = new Xssfworkbook (IS);    } else {throw new Exception ("Read not Excel file"); } list<list<list<string>>> result = new arraylist<list<list<string>>> ();//corresponds to EXC    El file int sheetsize = Wb.getnumberofsheets ();    for (int i = 0; i < sheetsize; i++) {//Traverse Sheet page Sheet Sheet = Wb.getsheetat (i); list<list<string>> sheetlist = new Arraylist<list<striNg>> ();//corresponds to sheet page int rowsize = Sheet.getlastrownum () + 1;    for (int j = 0; J < Rowsize; J + +) {//traverse rows Row row = Sheet.getrow (j);    if (row = = null) {//skip blank line continue;} int cellsize = Row.getlastcellnum ();//The number of cells in a row, that is, how many columns list<string> rowlist = new arraylist<string> ();    Should be a data row for (int k = 0; k < cellsize; k++) {Cell cell = Row.getcell (k);    String value = null;    if (cell! = NULL) {value = Cell.tostring ();}    Rowlist.add (value);    } sheetlist.add (Rowlist);    } result.add (Sheetlist); } return result; catch (FileNotFoundException e) {throw e;} finally {if (WB! = null) {Wb.close ();} if (is = null) {Is.close ();}}}

Java Read and write Excel (POI, XLS and xlsx two formats supported)