Java Common Tool Class encapsulation--encapsulates the operation of Excel __java

Encapsulates operations on Excel, including local read-write Excel and output excel in streams

Import Java.io.File;
Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import Java.io.FileOutputStream;
Import java.io.IOException;
Import Java.io.OutputStream;
Import Java.text.DecimalFormat;
Import java.util.LinkedList;
 
Import java.util.List;
 
Import Javax.servlet.http.HttpServletResponse;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import org.apache.poi.hssf.usermodel.HSSFRichTextString;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
Import Org.apache.poi.ss.usermodel.Cell;
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; /** * Encapsulates operations on Excel, including local read-write Excel and output excel in streaming, and supports Office 2007. <br/> * dependent on Poi-3.9-20121203.jar,poi-ooxml-3.9-20121203.jar,poi-ooxml-schemas-3.9-* 20121203.jar,dom4j-1.6.1.jar<br/> * Parametric constructor parameter is the full path of Excel <br/>
 
    * * @author Jiangshuai * @date April 24, 2017 * * public class Excelutil {//Excel file path private String path = "";
    When you write to Excel, the column widths are automatically extended to fit the content.
 
    Private Boolean autocolumnwidth = false;            /** * Parameterless constructor default/public Excelutil () {}/** * Parameter constructor * * @param path *
    Excel path */public Excelutil (String path) {this.path = path;
     /** * Reads the values of all cells on a workbook.
     * * @param sheetorder * Workbook number, starting from 0.
     * @return List<object[]> The values of all cells.
     * @throws ioexception * Load Excel file IO exception.
     * @throws FileNotFoundException * Excel file did not find an exception. * @throws invalidformatexception * @author Jiangshuai * @date April 24, 2017/public LIST&LT;OBJECT[]&G T
            Read (int sheetorder) throws FileNotFoundException,IOException, invalidformatexception {fileinputstream fis = new FileInputStream (path);
        Workbook workbook = workbookfactory.create (FIS);
        if (FIS!= null) {fis.close ();
        } Sheet Sheet = Workbook.getsheetat (Sheetorder);
        Used to record Excel values list<object[]> valuelist = new linkedlist<object[]> ();
        Iterate through each row and column.
            for (row Row:sheet) {//each row object[] rowobject = null; for (cell cell:row) {//Cell.getcelltype is the type switch (Cell.getcelltype ()) that obtains the value stored in the cell. {Case Cell.cell_type_boolean://Get BOOLEAN object Method Rowobject = Colle
                    Ctionutil.addobjecttoarray (Rowobject, Cell.getbooleancellvalue ());
                Break Case Cell.cell_type_numeric://First See if the date format if (dateutil.iscelldateformatted (Cell))
  {                      Read date format rowobject = Collectionutil.addobjecttoarray (Rowobject,
                    Cell.getdatecellvalue ());
                        else {DecimalFormat df = new DecimalFormat ();
                                The value of the cell, replaced by String value = Df.format (Cell.getnumericcellvalue ())
                        . replace (",", ""); Read number Rowobject = Collectionutil.addobjecttoarray (Rowobject, Val
                    UE);
                } break; Case Cell.cell_type_formula://Read Formula Rowobject = Collectionutil.addobjecttoarray (r
                    Owobject, Cell.getcellformula ());
                Break Case cell.cell_type_string://Read STRING Rowobject = Collectionutil.addobjecttoarra Y (RowobJect, cell. Getrichstringcellvalue (). toString ());
                Break
            }//Add this line to the list.
        Valuelist.add (Rowobject);
    return valuelist;
     /** * Reads the value of a cell on a workbook.
     * * @param sheetorder * Workbook number, starting from 0.
     * @param colum * Number of columns starting from 1 * @param row * Number of rows starting from 1 * @return cell value.
     * @throws Exception * Load Excel exception. * @author Jiangshuai * @date April 24, 2017 * * Public String read (int sheetorder, int colum, int row) throws E
        xception {FileInputStream fis = new FileInputStream (path);
        Workbook workbook = workbookfactory.create (FIS);
        if (FIS!= null) {fis.close ();
        } Sheet Sheet = Workbook.getsheetat (Sheetorder);
        Row rows = Sheet.getrow (row-1);
        Cell cell = Rows.getcell (colum-1); String content = Cell.getstrIngcellvalue ();
    return content;
     /** * Writes values in the specified workbook, row, and column.
     * * @param sheetorder * Workbook serial number, based on 0. * @param colum * column based on 1 * @param row * Line based on 1 * @param content * will
     The content to be written.
     * @throws Exception * Save exception after writing. * @author Jiangshuai * @date April 24, 2017/public void write (int sheetorder, int colum, int row, String Co
        Ntent) throws Exception {FileInputStream fis = new FileInputStream (path);
        Workbook workbook = workbookfactory.create (FIS);
        if (FIS!= null) {fis.close ();
        } Sheet Sheet = Workbook.getsheetat (Sheetorder);
        Row rows = Sheet.createrow (row-1);
        Cell cell = Rows.createcell (colum-1);
        Cell.setcellvalue (content);
        FileOutputStream fileout = new FileOutputStream (path);
        Workbook.write (fileout);
 
    Fileout.close (); }
 
    /**
     *Gets the ordinal number of the last record in a workspace, equivalent to how many rows of data are in this workbook.
     * * @param sheetorder * Work area serial number * @return int serial number.
     * @throws IOException * Load Excel exceptions with Excel paths. * @throws invalidformatexception * @author Jiangshuai * @date April 24, 2017/public int Getsheetlastro Wnum (int sheetorder) throws IOException, invalidformatexception {fileinputstream fis = new fileinputs
        Tream (path);
        Workbook workbook = workbookfactory.create (FIS);
        if (FIS!= null) {fis.close ();
        } Sheet Sheet = Workbook.getsheetat (Sheetorder);
    return Sheet.getlastrownum (); /** * generates an Excel with content on disk, Path property * * @param sheetname * Exported sheet name * @param f Ieldname * Column array Group * @param data * @throws IOException * @author jiangsh UAI * @date April 24, 2017/public void Makeexcel (String sheetname, string[] FieldName, list<object[]> data) throws IOException {//Generate work thin in memory hssfworkbook workbook = Makewor
        Kbook (SheetName, fieldName, data);
        Intercepting folder path String FilePath = path.substring (0, Path.lastindexof ("\"));
        If the path does not exist, create the path File File = new file (FilePath);
        System.out.println (path+ "-----------" +file.exists ());
        if (!file.exists ()) file.mkdirs ();
        FileOutputStream fileout = new FileOutputStream (path);
        Workbook.write (fileout);
    Fileout.close ();
     /** * Exports excel in the output stream. * * @param excelname * Exported Excel names include extension * @param sheetname * Exported sheet name * @ PARAM fieldName * Column array Group * @param data * @param response * r 
    Esponse * @throws IOException * Conversion stream when IO error * @author Jiangshuai * @date April 24, 2017 * * public void Makestreamexcel (String exCelname, String sheetname, string[] fieldName, list<object[]> data, HttpServletResponse resp
        Onse) throws IOException {outputstream os = null; Response.reset (); Empty output stream OS = Response.getoutputstream (); Get output stream Response.setheader ("content-disposition", "attachment; Filename= "+ New String (Excelname.getbytes ()," iso-8859-1 ")); Set Output file header Response.setcontenttype ("Application/msexcel");
        Define output type//generate work in memory thin hssfworkbook workbook = Makeworkbook (SheetName, fieldName, data);
        Os.flush ();
    Workbook.write (OS);
     /** * Generates the workbook object to memory according to the criteria.
     * * @param sheetname * Sheet Object name * @param fieldName * first column column name * @param data * Data * @return Hssfworkbook * @author Jiangshuai * @date April 24, 2017/private HSSF Workbook Makeworkbook (String sheetname, string[] FieldName, List<objeCt[]> data) {//used to record the maximum column width, automatically adjust column width.
 
        Integer collength[] = new Integer[fieldname.length];
        Produce work Thin object Hssfworkbook workbook = new Hssfworkbook ();
        Generate Worksheet Object Hssfsheet sheet = Workbook.createsheet ();
        For the worksheet to support Chinese, set the character set to Utf_16 workbook.setsheetname (0, SheetName);
        Produces a row hssfrow row = sheet.createrow (0);
        Generating cell hssfcell cells; Write the names for each field for (int i = 0; i < fieldname.length i++) {//Cells cell with the first row of each field name = row
            . Createcell ((short) i);
            Sets the contents of the cell to be a string type Cell.setcelltype (hssfcell.cell_type_string);
            To be able to enter Chinese in the cell, set the character set to Utf_16//cell.setencoding (HSSFCELL.ENCODING_UTF_16);
            Assign value to cell contents Cell.setcellvalue (New Hssfrichtextstring (fieldname[i));
        Initialize column width collength[i] = fieldname[i].getbytes (). length; }//Temporary cell content String tempcellcontent ="";
            Write each record, each record corresponds to one row in the Excel table for (int i = 0; i < data.size (); i++) {object[] tmp = Data.get (i);
            Generates row = Sheet.createrow (i + 1);
                for (int j = 0; J < Tmp.length J + +) {cell = Row.createcell (short) j);
                Sets the cell character type STRING cell.setcelltype (hssfcell.cell_type_string); Tempcellcontent = (Tmp[j] = = null)?
                "": tmp[j].tostring ();
 
                Cell.setcellvalue (New hssfrichtextstring (tempcellcontent));
                If the column width is automatically adjusted.
                        if (autocolumnwidth) {if (J >= collength.length) {////) The number of header columns is less than the number of data columns. 
                    Collength = Collectionutil.addobjecttoarray (Collength, Tempcellcontent.getbytes (). length);
                        else {//If the width of this content is greater than the previous maximum, follow this setting width.
 if (Collength[j] < Tempcellcontent.getbytes (). Length) {                           COLLENGTH[J] = tempcellcontent.getbytes (). length;
        The column width is automatically adjusted in the}}}//.
            if (autocolumnwidth) {//Adjusts the maximum width corresponding to this column of text.
            for (int i = 0; i < fieldname.length i++) {sheet.setcolumnwidth (i, collength[i] * 2 * 256);
    } return workbook;
     /** * Feature: Sets whether the column width is automatically extended to fit content when writing to Excel, and defaults to false.
    * * @author Jiangshuai * @date April 24, 2017 * @param autocolumnwidth * True or FALSE * * *
    public void Setautocolumnwidth (Boolean autocolumnwidth) {this.autocolumnwidth = Autocolumnwidth; }
}