Use POI to read and write to Excel

Actually, I feel like I just put the code on. The code almost does a gaze on each line. It should look simpler.

Code hosted on GitHub: Https://github.com/chsj1/ExcelUtils

Package Com.hjd.poiutils;import Java.io.file;import Java.io.fileinputstream;import java.io.fileoutputstream;import Java.io.ioexception;import Java.io.outputstream;import Java.math.bigdecimal;import Java.util.ArrayList;import Java.util.hashmap;import java.util.iterator;import java.util.list;import Java.util.map;import java.util.TreeMap; Import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.hssf.usermodel.hssfcellstyle;import Org.apache.poi.hssf.usermodel.hssffont;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.hssf.util.hssfcolor;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.cellstyle;import Org.apache.poi.ss.usermodel.font;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.xssf.usermodel.xssfworkbook;public class Excelutils {public static final String headerinfo = "Headinfo"; public static final String Datainfon = "Datainfo";/** * * @Title: Getweebwork * @Description: TODO(Gets the Workbook object (Workbook) based on the file name passed in) * @param filename * @return * @throws ioexception */public static Workbook getweebwork (String FileName) throws IOException {Workbook Workbook = null;if (null! = filename) {String FileType = filename.substring (Filena Me.lastindexof ("."), Filename.length ()); FileInputStream FileStream = new FileInputStream (new File), if (". xls". Equals (Filetype.trim (). toLowerCase () ) {workbook = new Hssfworkbook (fileStream);//Create an Excel 2003 Workbook object} else if (". xlsx". Equals (Filetype.trim ()). toLowerCase () ) {workbook = new Xssfworkbook (fileStream);//Create Excel 2007 Workbook object}}return workbook;}            /** * * @Title: Writeexcel * @Description: TODO (export Excel table) * @param pathname *: Export file path to Excel table * @param map * : Encapsulates data that needs to be exported (HeaderInfo encapsulates header information. Datainfon: Encapsulates the data information to be exported, which need to use TREEMAP *) such as: Map.put (excelutil.headerinfo,list<string> headlist); * Map.put (excelutil.datainfon,list<treemap<string,object>> * dataList); * @param WB * @thRows IOException */public static void Writeexcel (String pathname, map<string, object> Map,workbook WB) throws Ioexce ption {if (null! = Map && null! = pathname) {list<object> headlist = (list<object>) map.get (excelutils . HeaderInfo); list<treemap<string, object>> dataList = (list<treemap<string, object>>) Map.get ( Excelutils.datainfon); CellStyle style = Getcellstyle (WB); Sheet Sheet = Wb.createsheet ();//Create a form in the Document object ... The default is that the form name is Sheet0, sheet1....//Sheet Sheet = Wb.createsheet ("Hell poi");//When you create a love form, specify the name of the form/** * Set the first row of the Excel table is the header */row row = Sheet.createrow (0); for (int i = 0; i < headlist.size (); i++) {Cell Headcell = Row.createcell (i); Headcell.setcelltype ( cell.cell_type_string);//sets the type of data for this cell, whether it is a text type or a numeric type headcell.setcellstyle (style);//sets the header style Headcell.setcellvalue ( String.valueof (Headlist.get (i)));//Set the value for this cell}for (int i = 0; i < datalist.size (); i++) {Row RowData = Sheet.createrow ( i + 1);//Create Data row treemap<string, object> mapdata = DatAlist.get (i); Iterator it = Mapdata.keyset (). Iterator (); int j = 0;while (It.hasnext ()) {String strdata = string.valueof (ma Pdata.get (It.next ())); Cell Celldata = Rowdata.createcell (j);//Create a column in a row ... Celldata.setcelltype (cell.cell_type_string); Celldata.setcellvalue (strdata); j + +;}} File stream filename = new files (pathname), outputstream OS = new FileOutputStream (file); Os.flush (); wb.write (OS); Os.close ();}} /** * * @Title: Getcellstyle * @Description: TODO (SET header Style) * @param WB * @return */public static CellStyle Getcellstyle (Wo Rkbook WB) {CellStyle style = Wb.createcellstyle (); Font font = Wb.createfont (); Font.setfontname ("Arial"); Font.setfontheightinpoints ((short) 12);// Set the font size font.setboldweight (hssffont.boldweight_bold);//Bold Style.setfillforegroundcolor (HSSFColor.LIME.index);// Set Background color Style.setfillpattern (hssfcellstyle.solid_foreground); style.setalignment (Hssfcellstyle.solid_foreground); /Let the cell Center//Style.setwraptext (TRUE);//Set your own active newline style.setfont (font); return style;} /** * * @Title: Readerexceldemo * @DescrIption:todo (reading data from an Excel table) * @throws ioexception */public static void Readfromexceldemo () throws IOException {/** * Read exce L All data in the table */workbook Workbook = getweebwork ("e:/test.xlsx"); SYSTEM.OUT.PRINTLN ("Total table pages:" + workbook.getnumberofsheets ());//Get table pages Sheet sheet = workbook.getsheetat (0);//sheet Sheet = workbook.getsheetat (1); int rownum = Sheet.getlastrownum ();//Get Total rows for (int i = 0; I <= rownum; i++) {Row row = Sheet.getrow (i); Cell OrderNo = Row.getcell (2);//Gets the data in the specified cell//SYSTEM.OUT.PRINTLN (Orderno.getcelltype ());//This prints the cell's Typeshort Cellnum = Row.getlastcellnum (); Gets the total number of columns for the cell for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {cell Celldata = Row.getcell (j); System.out.print (celldata + "\ t");} System.out.println ();} /** * Reads the cell at the specified location *///Row row1 = Sheet.getrow (1);//Cell CELL1 = Row1.getcell (2);//System.out.print ("() Location cell value is:" +cell 1)///BigDecimal big = new//BigDecimal (Cell1.getnumericcellvalue ());//convert the data represented by scientific notation to string type//System.out.print ("\ t "+string.valueOf (big));}  public static void ReadFromExcelDemo1 () throws IOException {/** * read all data in Excel table * * Workbook:excel Document Object Sheet:excel form Cell lattice in row cell:excel in Row:excel * */workbook Workbook = getweebwork ("e:/test.xlsx"); SYSTEM.OUT.PRINTLN ("Total table pages:" + workbook.getnumberofsheets ()),//Get table Pages//Sheet Sheet =workbook.getsheetat (0); Sheet Sheet = Workbook.getsheetat (2);//Gets the second form int rownum = Sheet.getlastrownum ();//Gets the total number of rows for (int i = 0; I <= rownum; i++) {Row row = Sheet.getrow (i);//Gets the expression line I//cell OrderNo =//Row.getcell (2);//Gets the data in the specified cell (Gets the 2nd column in a row (where 2 refers to 0,1,2. In the third place)//System.out.println (Orderno.getcelltype ());//This print is the cell's type//short cellnum=row.getlastcellnum ();// Get the total number of columns in a cell (get how many cells (that is, how many columns) in a row)/** * Row.getfirstcellnum (): Gets the position of the first cell of the Row row.getlastcellnum (): * Gets the position of the last cell in the row */ for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {//Traverse all columns in a row cell Celldata = Row.getcell (j);//Get a row Column J returns the data for the Cell type System.out.print (celldata + "\ T")//}//print the specified column//cell celldata = Row.getcelL (4);//Gets the 4th column in the row (in the 5th position)//System.out.print ("\" "+ celldata+" \ ","); System.out.println ();} /** * Reads the cell at the specified location *///Row row1 = Sheet.getrow (1);//Cell CELL1 = Row1.getcell (2);//System.out.print ("() Location cell value is:" +cell 1)///BigDecimal big = new//BigDecimal (Cell1.getnumericcellvalue ());//convert the data represented by scientific notation to string type//System.out.print ("\ t "+string.valueof (big));} public static void Readfromexceldemo (String fileabsolutepath) throws IOException {/** * read all data in Excel table */workbook Workboo K = Getweebwork (Fileabsolutepath); SYSTEM.OUT.PRINTLN ("Total table pages:" + workbook.getnumberofsheets ());//Get table pages Sheet sheet = workbook.getsheetat (0);//sheet Sheet = workbook.getsheetat (1); int rownum = Sheet.getlastrownum ();//Get Total rows for (int i = 0; I <= rownum; i++) {Row row = Sheet.getrow (i); Cell OrderNo = Row.getcell (2);//Gets the data in the specified cell//SYSTEM.OUT.PRINTLN (Orderno.getcelltype ());//This prints the cell's Typeshort Cellnum = Row.getlastcellnum (); Gets the total number of columns for the cell for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {cell CelldatA = Row.getcell (j); System.out.print (celldata + "\ t");} System.out.println ();} /** * Reads the cell at the specified location *///Row row1 = Sheet.getrow (1);//Cell CELL1 = Row1.getcell (2);//System.out.print ("() Location cell value is:" +cell 1)///BigDecimal big = new//BigDecimal (Cell1.getnumericcellvalue ());//convert the data represented by scientific notation to string type//System.out.print ("\ t "+string.valueof (big));} public static void Main (string[] args) throws IOException {//ReadFromExcelDemo1 ();//string FilePath = "e:/test.xlsx";//r Eadfromexceldemo (FilePath);//read content from a specified Excel file//writetoexceldemo ();} public static void Writetoexceldemo () throws IOException {/** * HSSF:. xls XSSF:. xlsx so everyone will see Excel = HSSF+XSSF * * H in the official website SSF is a pure Java implementation of Poiproject for Excel 97 (-2007) file Operations XSSF is a pure Java implementation of Poiproject operations on Excel OOXML * (. xlsx) files * * Starting with the POI 3.8 version, provides a XSSF-based low memory footprint API----SXSSF * */workbook WB = new Xssfworkbook ();//Create a new Excel Document Object map<string, object> map = new Ha Shmap<string, object> (); List headlist = new ArrayList ();//table header data Headlist.add ("Order Time") Headlist.add ("at checkoutHeadlist.add ("Order Number"), Headlist.add ("Order Amount"), Headlist.add ("username");//Excel/** * TreeMap based on red-black tree */list DataList = new ArrayList ();//Data in table for (int i = 0; i < i++) {treemap<string, object> TreeMap = new TREEMAP&L T String, object> ();//The data here must be ordered data, so use TreeMap to encapsulate Treemap.put ("M1", "2013-10-" + i + 1); Treemap.put ("M2", "2013-11-" + i + 1); Treemap.put ("M3", "20124" + i + 1), Treemap.put ("M4", 23.5 + i + 1), Treemap.put ("M5", "Zhang San _" + i);d atalist.add (tree MAP);} /* Do not add the following paragraph, otherwise the following error: Cannot get a numeric value from a text * cell (Cannot get numeric type data from a text cell) * * Workaround: Http://blog.cs dn.net/ysughw/article/details/9288307 *///treemap<string,object> treeMap1 = new treemap<string, Object> ( );//Treemap1.put ("ASD", null);//Treemap1.put ("Pig Head", "Zhutou");//Datalist.add (TREEMAP1); Map.put ( Excelutils.headerinfo, headlist); Map.put (Excelutils.datainfon, dataList); Writeexcel ("e:/test1.xlsx", map, WB);// To write the contents of the map in the WB, generate e:/test1.xlsx this file ...}}

Use POI to read and write to Excel