Java-poi Import Export Excel (including JSON) __java

Recently done a few very interesting features, girlfriend companies need to collect information on the Beijing Internet, the first reaction is the crawler, but not very good, so think of the conversion to the JSON format with a regular resolution to Excel. The right thing to do is to make the data in the database into the export and send the mail of the timed task, so here is a detailed study of the operation of some of the Excel content.

Now the mainstream operation of Excel file open source tools are many, using more is the Apache POI and Jexcelapi. Here we use Apache POI.

Here is an example of the official Apache, please refer to the next http://poi.apache.org/spreadsheet/quick-guide.html , Poi introduction

Apache POI is the 100% Open Source Library provided by the Apache Software Foundation. Most small and medium-size application development relies heavily on Apache POI (hssf+ XSSF). It supports all the basic functions of Excel Library; However, presentation and text extraction are its main features.

second, the required jar bag

Here we are using the MAVEN project, giving the Pom file as a reference:

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactid>poi</artifactid >
            <version>3.15</version>
        </dependency>

        <dependency>
            <groupid >org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15 </version>
        </dependency>

third, the use of examples of the general idea is to call the API, new work thin, new sheet, get to operate the row number of rows, cell cells to manipulate the contents.

It should be noted that the HSSF in Apache POI (hssf+ XSSF) corresponds to the previous version of 2004, and XSSF is an Excel file that corresponds to a 2004 later version, which means that you need to use xlsx for the end of the. xssf file.

Row,cell coordinates start at 0 instead of starting at 1.

Here we give a few demo and comments for reference: (1) Create a new Excel table to do the operation

  /**1, create work Thin * * Xssfworkbook xs = new Xssfworkbook (pkg);

        /**2, add a sheet to the XS, corresponding to the sheet in Excel file/xssfsheet sheet = xs.createsheet ("Sheet1");

        /**3, add table header No. 0 row in sheet, note that the old version poi the number of rows in Excel is limited short*///xssfrow row=sheet.createrow (0);
        /**4, create a cell, and set the value table header to center the header * * Xssfcellstyle style = Xs.createcellstyle (); Style.setalignment (xssfcellstyle.align_center);////Create a center format/**5, write-by-line data *//Xssfcell Cell=row.createcel
L (0);

        Cell.setcellvalue ();
        int a = 0; Traversal list get data for (int i = 0; i < valuelist.size (); i++) {Excelinformation Excelinfo = valuelist.ge

            T (i);
            Xssfrow row = Sheet.createrow (a);

            Row.setrowstyle (style);
            /** Merge Cell * * Sheet.addmergedregion (new cellrangeaddress (A, a, 0, 5));

            Sheet.addmergedregion (New Cellrangeaddress (A, a, 6, 10)); Row.createcell (0). Setcellvalue (Exceltitlemap.get (Excelinfo.getseRvicetype ()));

            Row.createcell (1). Setcellvalue ("error hint TOP5");
            Xssfrow Row1 = Sheet.createrow (A + 1);
            Row1.setrowstyle (style);
            Row1.setrowstyle (style);
            Row1.createcell (0). Setcellvalue ("");
            Row1.createcell (1). Setcellvalue ("");
            Row1.createcell (2). Setcellvalue ("");
            Row1.createcell (3). Setcellvalue ("");
            Row1.createcell (4). Setcellvalue ("");

            Row1.createcell (5). Setcellvalue ("");
            Xssfrow row2 = Sheet.createrow (A + 2);
            Row2.setrowstyle (style);
            Row2.createcell (0). Setcellvalue (Excelinfo.getenterpage ());
            Row2.createcell (1). Setcellvalue (Excelinfo.getfailurenumber ());
            Row2.createcell (2). Setcellvalue (Excelinfo.getauthorizedsuccess ());
            Row2.createcell (3). Setcellvalue (Excelinfo.getauthorizationrate ());
            Row2.createcell (4). Setcellvalue (Excelinfo.getcrawlsuccess ()); Row2.createcell (5). SetcellvaluE (Excelinfo.getcrawlrate ());

        A = a + 4;


 }

(2) write to Local:

  Try
        {
            FileOutputStream fout = new FileOutputStream ("File path");
            Workbook.write (fout);
            Fout.close ();
        }
        catch (Exception e)
        {
            e.printstacktrace ();
        }

(3) Read the project path Excel and modify

Xssfworkbook workbook = null;
        try {
            InputStream excelfileinputstream = ExcelUtil.class.getResourceAsStream ("file path");
            workbook = new Xssfworkbook (excelfileinputstream);
        } catch (IOException e) {
            e.printstacktrace ();
        }

(4) Return in the form of a stream

Bytearrayoutputstream Bytearrayoutputstream = new Bytearrayoutputstream ();
        try {
            workbook.write (bytearrayoutputstream);
        } catch (IOException e) {
            e.printstacktrace ();
        }

(5) JSON as data passed in to export Excel

/** * Created by Tanyunlong on 2017/4/10.
    /public class Excelutil {@SuppressWarnings ("unchecked")//create Excel file Functions//src for file path to save, JSON for saved JSON data public static Jsonobject Createexcel (String src, jsonobject json) {jsonobject result = new Jsonobject ();//For feedback
            The function calls the result try {//new file filename = new files (SRC);


            File.createnewfile (); OutputStream outputstream = new FileOutputStream (file);//create work thin writableworkbook Writableworkbook = workbook.c
            Reateworkbook (OutputStream); Writablesheet sheet = writableworkbook.createsheet ("First sheet", 0);//Create a new page Jsonarray Jsonarray = Json.get Jsonarray ("Data");//Get data corresponding to the Jsonarray label label; Cell object int column = 0; Count the number of columns//Add the first line of information to the page.
            such as: name, age, Gender Jsonobject-jsonarray.getjsonobject (0); Iterator<string> iterator = First.keys (); Get the key set for the first item WHILe (Iterator.hasnext ()) {//Traverse key collection String key = (string) iterator.next ();//Get Key label = new Label (column++, 0, key); The first argument is the column that contains the cell, the second argument is the row of the cell, and the third parameter is the value Sheet.addcell (label);
                Add cell to Page}//Traverse Jsonarray for (int i = 0; i < jsonarray.size (); i++) { Jsonobject item = jsonarray.getjsonobject (i); Gets the array of each item iterator = Item.keys (); Get key Collection column = 0;//from No. 0 column while (Iterator.hasnext ()) {String K ey = Iterator.next (); Get key String value = item.getstring (key); Get the key corresponding to the value label = new Label (column++, (i + 1), value); The first argument is the column that contains the cell, the second argument is the row of the cell, and the third parameter is the value Sheet.addcell (label); Add cell to Page}} writableworkbook.write (); Add to File Writableworkbook.close ();
 Close file, release resource} catch (Exception e) {           Result.put ("Result", "failed"); The result of calling the function is returned to Result.put ("Reason", e.getmessage ());
        Returns the return result for the reason that the function failed to be invoked;
        } result.put ("Result", "successed");
    return result; }

}

Attach several reference APIs

POI API Chinese Description

POI official description and examples