POI reads Excel files, imports Excel data into the database

Source: Internet
Author: User
Tags time and seconds

/**
* 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.