Shulou(Shulou.com)06/03 Report--

Learn excl operation

Import java.io.File

Import java.io.FileInputStream

Import java.io.FileOutputStream

Import java.io.IOException

Import java.text.DecimalFormat

Import java.util.ArrayList

Import java.util.List

Import org.apache.poi.hssf.usermodel.HSSFWorkbook

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.xssf.usermodel.XSSFCell

Import org.apache.poi.xssf.usermodel.XSSFRow

Import org.apache.poi.xssf.usermodel.XSSFSheet

Import org.apache.poi.xssf.usermodel.XSSFWorkbook

/ * *

* this class mainly implements the operation of excel files with the suffix xlsx

, /

Public class ExcelUtil {

Private XSSFSheet ExcelWSheet; / / excl cell sheet page

Private XSSFWorkbook ExcelWBook; / / the whole excl object

Private XSSFCell Cell; / / column object

Private XSSFRow Row;// Row object

Private String filePath; / / File path

/ * * Construction method * /

/ / set the file path of the Excel to be operated and the sheet name in the Excel file

/ / when reading and writing excel, you need to call this method first to set the path of the excel file to be operated and the name of the sheet to be operated.

Public ExcelUtil (String Path, String SheetName) throws Exception {

FileInputStream ExcelFile

Try {

/ / instantiate the FileInputStream object of the excel file

ExcelFile = new FileInputStream (Path)

/ / instantiate the XSSFWorkbook object of the excel file

ExcelWBook = new XSSFWorkbook (ExcelFile); / / entire file

/ / instantiate the XSSFSheet object, specify the sheet name in the excel file, and then use it for row, column, and cell operations in sheet

ExcelWSheet = ExcelWBook.getSheet (SheetName)

} catch (Exception e) {

Throw (e)

}

This.filePath = Path

}

/ / read the function of the specified cell in the excel file. This function only supports excel files with the suffix xlsx

Public String getCellData (int RowNum, int ColNum) throws Exception {

Try {

/ / specify the row number and column number of the cell through the function parameters to get the specified cell object.

Cell = ExcelWSheet.getRow (RowNum) .getCell (ColNum)

/ / if the content of the cell is of string type, use the getStringCellValue method to get the contents of the cell

/ / if the contents of the cell are numeric, use the getNumericCellValue () method to get the contents of the cell

String CellData = ""

/ * * get the cell type * /

If (Cell.getCellType () = = XSSFCell.CELL_TYPE_STRING) {/ / if it is a string

CellData = Cell.getStringCellValue ()

} else if (Cell.getCellType () = = XSSFCell.CELL_TYPE_NUMERIC) {/ / if it is a number

DecimalFormat df = new DecimalFormat ("0")

CellData = df.format (Cell.getNumericCellValue ())

}

Return CellData

} catch (Exception e) {

E.printStackTrace ()

Return ""; / / if the exception returns null

}

}

/ * * write input data in the execution cell of the Excel file. This function only supports writing to files with the suffix xlsx.

* @ param result result

* @ param rowNum rows

Number of * @ param cellNum columns

* * /

Public void setCellData (int RowNum, int ColNum, String result) throws Exception {

Try {

/ / get the line object in the excel file

Row = ExcelWSheet.getRow (RowNum)

/ / return Null if the cell is empty

Cell = Row.getCell (ColNum, Row.RETURN_BLANK_AS_NULL)

If (Cell = = null) {

/ / create a cell when the cell object is null

/ / if the cell is empty, you cannot directly call the setCellValue method of the cell object to set the cell value

Cell = Row.createCell (ColNum)

/ / after creating a cell, you can call the setCellValue method of the cell object to set the cell value.

Cell.setCellValue (result)

} else {

/ / if there is content in the cell, you can directly call the setCellValue method of the cell object to set the cell value

Cell.setCellValue (result)

System.out.println ("execution complete")

}

/ / instantiate the file output stream object written to the excel file

FileOutputStream fileOut = new FileOutputStream (filePath)

/ / write the content to the excel file

ExcelWBook.write (fileOut)

/ / call the flush method to force the write file to be refreshed

FileOut.flush ()

/ / close the file output stream object

FileOut.close ()

} catch (Exception e) {

E.printStackTrace ()

Throw (e)

}

}

/ * * static method to get test data from excel file * /

Public static Object [] getTestData (String excelFilePath, String sheetName) throws IOException {

/ / combine the absolute path of the excel data file according to the data file path and file name passed in by the parameter

/ / declare a file file object

File file = new File (excelFilePath)

/ / create a FileInputStream object to read the excel file

FileInputStream inputStream = new FileInputStream (file)

/ / declare Workbook object

Workbook Workbook = null

/ / get the suffix name of the file name parameter to determine whether it is a xlsx file or a xls file

String fileExtensionName = excelFilePath.substring (excelFilePath.indexOf (".")

/ / determine the file type if it is xlsx, instantiate it using the XSSFWorkbook object 2007

/ / determine the file type if it is xls, instantiate it using the SSFWorkbook object 2003

If (fileExtensionName.equals (".xlsx")) {

Workbook = new XSSFWorkbook (inputStream)

} else if (fileExtensionName.equals (".xls")) {

Workbook = new HSSFWorkbook (inputStream)

}

/ / generate sheet object through sheetName parameter

Sheet Sheet = Workbook.getSheet (sheetName)

/ / get the number of rows of data in sheet1 in the excel data file, and get the last line number of the data by the getLastRowNum method

/ / the getFirstRowNum method gets the first row number of the data, and the number of rows of the data is calculated after subtraction.

/ / Note: the line number and column number of the excel file start with 0.

Int rowCount = Sheet.getLastRowNum ()-Sheet.getFirstRowNum ()

System.out.println (rowCount)

/ / create a list object named records to store the data read from the excel data file

/ /

List records = new ArrayList ()

/ / use 2 for loops to traverse all the data in the excel data file (except for the first row, which is the data column name)

/ / so I starts at 1, not 0; 0 represents the header

For (int I = 1; I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.