Java POI Components-read, filter, write Excel__java

Next to the Java POI component-write Excel http://blog.csdn.net/chy555chy/article/details/52739711

Suppose there was such a demand--the leader singled out some of the employee's names (ready to be advanced), and needed only some of those employees ' information. You need to find out from the employee table some of the information they have with these employees.

The employee table is as follows

The advanced person is: "Chen Qi", "John", "Lin Eight", "Money Two", "Oriental Invincible" required fields to be sorted in the following order: "Name", "Employee Number", "department", "Age", "Entry Time" in addition, you need to generate indexes in the first column, automatic sorting

The results are as follows

The jar you need to use for this read-write Excel is

Poi-3.15.jar
Poi-ooxml-3.15.jar
Poi-ooxml-schemas-3.15.jar
Commons-collections4-4.1.jar
Xmlbeans-2.6.0.jar

Package com.example.pkg;
Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import Java.io.FileOutputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import Java.text.Format;
Import java.util.ArrayList;

Import Java.util.Date;
Import org.apache.poi.EncryptedDocumentException;
Import Org.apache.poi.hssf.extractor.ExcelExtractor;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
Import Org.apache.poi.ss.usermodel.BorderStyle;
Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.CellStyle;
Import Org.apache.poi.ss.usermodel.CellType;
Import Org.apache.poi.ss.usermodel.CellValue;
Import Org.apache.poi.ss.usermodel.CreationHelper;
Import Org.apache.poi.ss.usermodel.DataFormat;
Import Org.apache.poi.ss.usermodel.DataFormatter;
Import Org.apache.poi.ss.usermodel.DateUtil;
Import Org.apache.poi.ss.usermodel.FillPatternType;
Import Org.apache.poi.ss.usermodel.Font; ImporT Org.apache.poi.ss.usermodel.FormulaEvaluator;
Import org.apache.poi.ss.usermodel.IndexedColors;
Import org.apache.poi.ss.usermodel.RichTextString;
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 org.apache.poi.ss.util.CellRangeAddress;
Import Org.apache.poi.xssf.extractor.XSSFExcelExtractor;

Import Org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Poiexcelclass {/** * filter and Reorganize tables (automatically generate indexes), filter only the first sheet * * @params originfilename source file name * @par
    AMS targetfilename Target filename * @params columns only the columns (excluding indexes) that have occurred in the original table are extracted * @params filteritems filter criteria, only the rows that appear. private static void filter (String originfilename, String TargetFileName, string[] columns, string filtercolumn, string[] F
        Ilteritems) {InputStream INP = null;
        Workbook workbook = null; arraylist<integer> columnindexlist = new Arraylist<iNteger> (columns.length);
        int filtercolumnindex =-1;
        Workbook workbook_w = null;
        FileOutputStream fos = null;
            try {inp = new FileInputStream (originfilename);
            Workbook = Workbookfactory.create (INP);
            Formulaevaluator evaluator = Workbook.getcreationhelper (). Createformulaevaluator ();
            Sheet Sheet = workbook.getsheetat (0);
            Row row = Sheet.getrow (0);
            If no rows are found, returns if (row = = null) return;
                    The first line must be title for (Cell Cell:row) {if (cell.getcelltypeenum () = = celltype.string) {
                        for (string str:columns) {String value = Cell.getstringcellvalue ();
                            if (str.equals (value)) {int index = Cell.getcolumnindex ();
                            Columnindexlist.add (index); if (Filtercolumnindex = = 1 && fIltercolumn.equals (value)) {Filtercolumnindex = index;
                        } break; //If the field to be filtered is not found one, return if (Columnindexlist.s ize () = = 0 | |
            Filtercolumnindex = = 1) return;
            if (Targetfilename.endswith (SUFFIX_XSSF)) {workbook_w = new Xssfworkbook ();
            else {workbook_w = new Hssfworkbook ();
            } Sheet Sheet_w = Workbook_w.createsheet ("Sheet1");
            int rowIndex = 1;
            Add header row Row_w = Sheet_w.createrow (0);
            Row_w.createcell (0). Setcellvalue ("index"); for (int i = 0; i < columnindexlist.size (); i++) {String value = Row.getcell (Columnindexlist.get (i)). g
                Etstringcellvalue ();
            Row_w.createcell (i+1). Setcellvalue (value);
           } for (Row R:sheet) {if (r.getrownum () = 0) continue;
                Cell cell = R.getcell (Filtercolumnindex);
                     if (cell.getcelltypeenum () = = celltype.string) {STRING value = Cell.getstringcellvalue (); for (String str:filteritems) {if (Str.equals (value)) {/
                             /The line satisfies the filter condition Row_w = Sheet_w.createrow (RowIndex);
                             Row_w.createcell (0). Setcellformula ("ROW ()-1"); 
                                 for (int i = 0; i < columnindexlist.size (); i++) {int index = columnindexlist.get (i);
                                 Cell C = R.getcell (index);
                                 Cell c_w = Row_w.createcell (i+1, C.getcelltype ()); Inherits cell formatting from the original cell, including content formatting, borders, colors, fonts. Note: You must first create, you can not directly set the format of other cells CellStyle cs = Workbook_w.createcelLstyle ();
                                 Cs.clonestylefrom (C.getcellstyle ());
                                 C_w.setcellstyle (CS); The following is the wrong method, Error:this Style does not belong to the supplied workbook Stlyes Source.
                                 Are you trying to assign a style from one workbook to the cell of a differnt workbook?
                                 C_w.setcellstyle (C.getcellstyle ());
                                Switch (C.getcelltypeenum ()) {case _none:break;
                                Case Blank:break;
                                    Case BOOLEAN:c_w.setCellValue (C.getbooleancellvalue ());
                                Break
                                    Case ERROR:c_w.setCellErrorValue (C.geterrorcellvalue ());
               Break                 Case FORMULA://The only problem is that the formula is based on the cell calculation, the new table may be the corresponding cells to store different data, resulting in different results
                                    C_w.setcellformula (C.getcellformula ());
                                    Cellvalue Cellvalue = Evaluator.evaluate (c);
                                    C_w.setcelltype (Cellvalue.getcelltype ());
                                        Switch (Cellvalue.getcelltypeenum ()) {case BOOLEAN:
                                        C_w.setcellvalue (Cellvalue.getbooleanvalue ());
                                    Break
                                        Case ERROR:c_w.setCellErrorValue (Cellvalue.geterrorvalue ());
                                    Break
                                            Case Numeric:if (dateutil.iscelldateformatted (c)) { C_w.setcellvalue (C.getdatecellvalUE ());
                                        else {C_w.setcellvalue (C.getnumericcellvalue ());
                                    } break;
                                        Case STRING:c_w.setCellValue (C.getrichstringcellvalue ());
                                    Break
                                    Default:break;
                                } break;
                                        Case Numeric:if (dateutil.iscelldateformatted (c)) {
                                    C_w.setcellvalue (C.getdatecellvalue ());
                                    else {C_w.setcellvalue (C.getnumericcellvalue ());
    } break;                            Case STRING:c_w.setCellValue (C.getrichstringcellvalue ());
                                Break
                             }} rowindex++;
                         Break
            FOS = new FileOutputStream (TargetFileName)}}}
        Workbook_w.write (FOS);
        catch (FileNotFoundException e) {e.printstacktrace ();
        catch (Encrypteddocumentexception e) {e.printstacktrace ();
        catch (Invalidformatexception e) {e.printstacktrace ();
        catch (IOException e) {e.printstacktrace ();
                finally {if (INP!= null) {try {inp.close ();
                catch (IOException e) {e.printstacktrace ();
        }    } if (workbook!= null) {try {workbook.close ();
                catch (IOException e) {e.printstacktrace ();
                } if (Workbook_w!= null) {try {workbook_w.close ();
                catch (IOException e) {e.printstacktrace ();
                } if (fos!= null) {try {fos.close ();
                catch (IOException e) {e.printstacktrace (); }}} public static void Main (string[] args) {filter (Excel_path, "filter.xlsx", NE
    W string[] {"Name", "Employee Number", "department", "Age", "Entry Time"}, "name", new string[] {"Chen Qi", "John", "Lin Eight", "Money Two", "Oriental Unbeaten"}); }

Although the

can basically accomplish this task, there is an imperfect place here, which is the formula cell.
The formula cell is processed in such a way that the value is calculated and its value is filled in the corresponding unit.
The better thing to do is to map the fields in the formula to the new fields.