POI reads Excel files, imports Excel data into the database

/**
* Read the string displayed by the Excel cell (return what is displayed)
* Create Person: Minlorry
* Date Created: 2015-07-31
* Update Date: 2015-07-31
* Update Note: Processing of numeric cells is handled rigidly, and date cells are not processed.
* @param cell Excel cells
* @return string displayed by string cell
*/
public static String GetStringValue (cell cell) {
String stringvalue = "";//return value
if (cell = = null) {
Return "";
}

Read Excel cell format, convert to DecimalFormat, and adjust before use
String fmt= "0";//decimalformat format
CellStyle CellStyle = Cell.getcellstyle ();
if (cellstyle!=null) {
This string is the format string for the custom cell format [general, numeric, currency, custom)
See: Org.apache.poi.ss.usermodel.builtinformats|org.apache.poi.ss.usermodel.dataformatter
Instructions for having those basic strings.
String dataformatstring = cellstyle.getdataformatstring ();
if (dataformatstring!=null &&! "". Equals (dataformatstring)) {
fmt= Dataformatstring.replaceall ("[_ ()]", ""). Trim ();
}else{
fmt= "0";
}
}
DecimalFormat DF;

Switch (Cell.getcelltype ()) {
Case cell.cell_type_string:
StringValue = Cell.getstringcellvalue ();
StringValue = Cell.getrichstringcellvalue (). getString ();
Break
Case Cell.cell_type_numeric:
if (dateutil.iscelldateformatted (cell)) {
Date
Method 1: The data format for this is time and seconds: 2011-10-12 0:00:00
StringValue = Cell.getdatecellvalue (). toLocaleString ();

Method 2: The data format for this is no time and seconds: 2011-10-12
Date date = Cell.getdatecellvalue ();
SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd");
StringValue = Sdf.format (date);
}else{
Numerical
// }
if (Fmt.equals ("General")) {
Fmt= "#,# #0.00";//numeric cell and regular format, as if it were a formula
}else if (Fmt.equals ("0")) {
fmt= "#,# #0";//Integer cell
}

DF = new DecimalFormat (FMT);
StringValue = Df.format (Cell.getnumericcellvalue ()). Trim ();
if (Stringvalue.endswith (". 00")) {
Stringvalue=stringvalue.substring (0, Stringvalue.lastindexof ("."));
}
Break
Case Cell.cell_type_formula:
try {
if (Fmt.endswith ("0%")) {
Fmt= "#,# #0.0%";
}else if (Fmt.endswith ("0.00")) {
Fmt= "#,# #0.00";
}else{
fmt= "#,# #0";
}
DF = new DecimalFormat (FMT);
StringValue = Df.format (Cell.getnumericcellvalue ());
}catch (Exception e) {
try{
StringValue = string.valueof (Cell.getrichstringcellvalue ());
}catch (Exception E1) {
Stringvalue= "formula calculation error";
}
}
Break
Case Cell.cell_type_boolean:
StringValue = string.valueof (Cell.getbooleancellvalue ());
Break
Case Cell.cell_type_blank:
StringValue = "";
Break
Case CELL.CELL_TYPE_ERROR:
StringValue = "";
Default
Return "";
}
if ("". Equals (stringvalue) | | StringValue = = null) {
Return "";
}
return stringvalue;
}

/**
* Save uploaded Excel file to database
* @author Minlorry
* Files uploaded @param the file page
* @param user currently logged in
*/
@Transactional (readOnly = False)
Public String savefromexcel (User user,multipartfile file) {
Workbook Workbook = null;//workbook
Sheet Sheet = null;//Worksheet
Row row = null;//rows in a worksheet
/** read into workbook start*/
try{
Switch (Excelutil.getpostfix (File.getoriginalfilename ()). toLowerCase ()) {
Case "xlsx": WorkBook = new Xssfworkbook (File.getinputstream ()); break;//office2007 and later
Case "xls": WorkBook = new Hssfworkbook (File.getinputstream ()); break;//office2003 and Previous
Default
Return "An unsupported file format was opened when importing enterprise information from Excel!" ";
}
}catch (Exception e) {
There may be an exception to such factors as encrypted files or locks
Return "When importing enterprise information from Excel, there was an error in parsing the file!" ";
}
/** read into workbook end*/
List<company> companylist =new ArrayList ();
try{
Company Company=null;
Traverse worksheets in a workbook
for (int sheetnum= 0; sheetnum < workbook.getnumberofsheets (); sheetnum++) {
Sheet = Workbook.getsheetat (sheetnum);
if (sheet = = null) {continue;}
if (Sheet.getphysicalnumberofrows () <2) {continue;}
Traversing table rows (second row)
for (int rowNum = 1; rowNum <= sheet.getlastrownum (); rownum++) {
row = Sheet.getrow (RowNum);
if (row==null) {continue;}

Company = new company ();
Company.setcode (Excelutil.getstringvalue (Row.getcell (1)));
Company.setname (Excelutil.getstringvalue (Row.getcell (2)));
Company.setattention (Excelutil.getstringvalue (Row.getcell (3)));
Company.setphone (Excelutil.getstringvalue (Row.getcell (4)));

Date date = new Date ();
Company.setcreatetime (date);
Company.setupdatetime (date);
Company.setcreator (user);
Company.setupdator (user);

Company.setcompanyclass (null);//Uncategorized
Companylist.add (company);
}
}
}catch (Exception e) {
Return "Error reading data record when importing enterprise information from Excel! ";
}
try{
Add (companylist);
}catch (Exception e) {
Return "When importing enterprise information from Excel, there was an error while saving the data!" ";
}
Return "Import enterprise information from Excel successfully!" ";
}

POI reads Excel files, imports Excel data into the database