How to transfer the excel file to the local database
This article mainly explains "how to get excel files to the local database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to get excel files to the local database".
Package com.pingan.wiseapm.web
Import java.io.File
Import java.io.FileInputStream
Import java.io.FileNotFoundException
Import java.io.FileOutputStream
Import java.io.IOException
Import java.lang.reflect.Method
Import org.apache.poi.hssf.usermodel.HSSFSheet
Import org.apache.poi.hssf.usermodel.HSSFWorkbook
Import org.apache.poi.ss.usermodel.Cell
Import org.apache.poi.ss.usermodel.Row
Public class ExcelManage {
Private HSSFWorkbook workbook = null
Public boolean fileExist (String filePath) {
Boolean flag = false
File file = new File (filePath)
Flag = file.exists ()
Return flag
}
Public boolean sheetExist (String filePath, String sheetName) {
Boolean flag = false
File file = new File (filePath)
If (file.exists ()) {
Try {
Workbook = new HSSFWorkbook (new FileInputStream (file))
/ / add Worksheet (the xls file generated without adding sheet will report an error when it is opened)
HSSFSheet sheet = workbook.getSheet (sheetName)
If (sheet! = null)
Flag = true
} catch (Exception e) {
E.printStackTrace ()
}
} else {/ / File does not exist
Flag = false
}
Return flag
}
Public void createSheet (String filePath, String sheetName, String titleRow []) throws FileNotFoundException, IOException {
FileOutputStream out = null
File excel = new File (filePath); / / read the file
FileInputStream in = new FileInputStream (excel); / / convert to stream
Workbook = new HSSFWorkbook (in); / / load the working directory of excel
Workbook.createSheet (sheetName); / / add a new sheet
/ / add header
Row row = workbook.getSheet (sheetName) .createRow (0); / / create the first line
Try {
For (int I = 0; I < titleRow.length; iTunes +) {
Cell cell = row.createCell (I)
Cell.setCellValue (titleRows [I])
}
Out = new FileOutputStream (filePath)
Workbook.write (out)
} catch (Exception e) {
E.printStackTrace ()
} finally {
Try {
Out.close ()
} catch (IOException e) {
E.printStackTrace ()
}
}
}
/ * *
* create a new excel.
*
* @ param filePath
* path of excel
* @ param sheetName
* Table index to be created
* @ param titleRow
* the first line of excel is the table header
, /
Public void createExcel (String filePath, String sheetName, String titleRow []) {
/ / create a workbook
Workbook = new HSSFWorkbook ()
/ / add Worksheet (the xls file generated without adding sheet will report an error when it is opened)
Workbook.createSheet (sheetName)
/ / create a new file
FileOutputStream out = null
Try {
/ / add header
Row row = workbook.getSheet (sheetName) .createRow (0); / / create the first line
For (int I = 0; I < titleRow.length; iTunes +) {
Cell cell = row.createCell (I)
Cell.setCellValue (titleRows [I])
}
Out = new FileOutputStream (filePath)
Workbook.write (out)
} catch (Exception e) {
E.printStackTrace ()
} finally {
Try {
Out.close ()
} catch (IOException e) {
E.printStackTrace ()
}
}
}
/ * *
* write to excel.
*
* @ param filePath
* File path
* @ param sheetName
* Table index
* @ param object
, /
Public void writeToExcel (String filePath, String sheetName, Object object, String titleRow []) {
/ / create a workbook
File file = new File (filePath)
Try {
Workbook = new HSSFWorkbook (new FileInputStream (file))
} catch (FileNotFoundException e) {
E.printStackTrace ()
} catch (IOException e) {
E.printStackTrace ()
}
FileOutputStream out = null
HSSFSheet sheet = workbook.getSheet (sheetName)
/ / get the total number of rows of the table
Int rowCount = sheet.getLastRowNum () + 1; / / need to add one
Try {
Row row = sheet.createRow (rowCount); / / the latest line to be added
/ / obtain the field of object through reflection, and insert it corresponding to the header
/ / get the class object of this object
Class
