Java upload Excel file import data

/**
* Upload files into several steps
* 1-Get user uploaded files in Excel
* Read the contents of Excel file
* 3-data turned into entities
*/


Package com.csot.util;

Import com.csot.pm.file.model.FileEntity;
Import Com.google.common.reflect.TypeToken;
Import Com.google.gson.Gson;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import ORG.APACHE.POI.SS.FORMULA.FUNCTIONS.T;
Import org.apache.poi.ss.usermodel.*;
Import Org.apache.poi.xssf.usermodel.XSSFWorkbook;

Import Java.lang.reflect.Type;
Import java.util.ArrayList;
Import Java.util.HashMap;
Import java.util.List;
Import Java.util.Map;

/**
* Import Excel Tool class
* Created by Csot.qhluodajie on 2018/4/18.
*/
public class Improtexcelutil {

public static Workbook Checkexcel (Fileentity fileentity) throws exception{
Workbook Workbook = null;
Determine file type
if (Fileentity.getfiletype (). Equalsignorecase ("xls")) {
workbook = new Hssfworkbook (Fileentity.getinputstream ());
} else if (Fileentity.getfiletype (). Equalsignorecase ("xlsx")) {
workbook = new Xssfworkbook (Fileentity.getinputstream ());
Workbook = Workbookfactory.create (Fileentity.getinputstream ());
} else {
throw new RuntimeException ("The upload file format is incorrect!) ");
}
return workbook;
}

/**
* Data turned into list<map>
* @param sheet Stream
* @param cellname Object data columns, as with entity characters
* @param RowNum starting from the first few lines
* @param cellnum starting from the first few columns
* @return return list<map>
*/
public static list<map<string,object>> Exceltolistmap (Sheet sheet,int rownum,int cellnum,string [] Cellname) {
List<map<string,object>> listmap =new arraylist<> ();
for (int i = rowNum; I <= sheet.getlastrownum (); i++) {
Row row = Sheet.getrow (i);
map<string,object> map = new hashmap<> ();
for (int v=cellnum;v<row.getlastcellnum (); v++) {
Object obj = Row.getcell (v);
if (obj! = null) {
Row.getcell (v). Setcelltype (cell.cell_type_string);
Map.put (Cellname[v],row.getcell (v). Getstringcellvalue ());
}else {
Map.put (Cellname[v], "");
}

}
Listmap.add (map);
}
return listmap;
}
public static String Objfromjson (Object obj) {
Gson Gson = new Gson ();
return Gson.tojson (obj);
}
public static <T> T Fromjson (String json,class<t> type) {
Gson Gson = new Gson ();
Return Gson.fromjson (Json,type);
}
public static <T> List <T> Listfromjson (String json,type Type) {
Gson Gson = new Gson ();
Return Gson.fromjson (Json,type);
}

}
Business Layer

 @Override 
 public void saveimprotmaterial (Workbook Workbook) throws Exception {
//Import data columns, as with entity characters 
 Strin G[] Cellname = {"Materialname", "Factoryname", "Actualrate", "Targetrate", "Quartertargetrate", "Quartername"}; 
//sheetat position, and the first few rows of the column start 
 list<map<string, object>> listmap = Improtexcelutil.exceltolistmap (workboo K.getsheetat (0), 1,0, cellname); 
//Turn entity 
 list<pmmaterialrate> List = Improtexcelutil.listfromjson (Improtexcelutil.objfromjson (LISTMAP), New Typetoken<list<pmmaterialrate>> () {}.gettype ()); 
 for (pmmaterialrate pmmaterialrate:list) {
//todo save operation 
//... 
} 
} 
//Control layer 

@RequestMapping ("Saveallimprot")
@ResponseBody
Public Ajaxrtnjson Saveallimprot (string factoryname, String sheetat, HttpServletRequest request) {
    try {
        list<fileentity> list = fileutils.getfilesfromrequest (request);
        if (list = = NULL | | list.size () = = 0) {
            return new Ajaxrtnjson (False, "Save failed!") ");
        }
        Fileentity fileentity = list.get (0);
        Workbook Workbook = Improtexcelutil.checkexcel (fileentity);

　　　　  This.pmMaterialLightCrystalServiceImpl.saveImprot (workbook);//cu ketone acid

}catch (Exception ex) {
　　　　
　　
}

}

File Tool class

Extract the list of uploaded files from request

Package com.csot.util;

Import Java.io.BufferedInputStream;
Import Java.io.File;
Import Java.io.FileInputStream;
Import Java.io.FileOutputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import Java.io.OutputStream;
Import Java.io.OutputStreamWriter;
Import Java.io.PushbackInputStream;
Import Java.net.URLEncoder;
Import java.util.ArrayList;
Import java.util.List;
Import Java.util.Map;

Import Javax.servlet.http.HttpServletRequest;
Import Javax.servlet.http.HttpServletResponse;

Import Org.slf4j.Logger;
Import Org.slf4j.LoggerFactory;
Import Org.springframework.web.multipart.MultipartFile;
Import Org.springframework.web.multipart.MultipartHttpServletRequest;

Import com.csot.pm.file.model.FileEntity;

public class FileUtils {
private static final Logger Logger = Loggerfactory.getlogger (Fileutils.class);

/**
* Extract List of uploaded files from request
*
* @param request HttpServletRequest
*/
public static list<fileentity> Getfilesfromrequest (HttpServletRequest request) {
list<fileentity> files = new arraylist<fileentity> ();

Multiparthttpservletrequest multipartrequest = (multiparthttpservletrequest) request;
map<string, multipartfile> filemap = Multipartrequest.getfilemap ();
try {
For (map.entry<string, multipartfile> entity:fileMap.entrySet ()) {
InputStream InputStream = Entity.getvalue (). getInputStream ();
if (! ( Inputstream.marksupported ())) {
InputStream = new Pushbackinputstream (InputStream, 8);
}

String fileName = Entity.getvalue (). Getoriginalfilename ();
String prefix =
Filename.lastindexof (".") >= 1? Filename.substring (Filename.lastindexof (".") + 1)
: null;
fileentity file = new fileentity ();
File.setinputstream (InputStream);
File.setfiletype (prefix);
File.setfilename (FileName);
Files.add (file);
}
} catch (IOException e) {
E.printstacktrace ();
}
return files;
}

/**
* Download File
*
* @param response
* Absolute full path required for filepath of @param entity
*/
public static void Setdownloadresponse (HttpServletResponse response, fileentity entity)
Throws IOException {
if (entity = = NULL | | response = = NULL) {
Response.senderror (404, "File not found!");
Return
}

File F = new file (Entity.getfilepath ());
if (!f.exists ()) {
Response.senderror (404, "File not found!");
Return
}

Bufferedinputstream br = new Bufferedinputstream (new FileInputStream (f));
byte[] buf = new byte[1024];
int len = 0;

Response.reset (); Very important
/*
* if (isOnLine) {//online open mode url u = new URL ("file:///" + FilePath);
* Response.setcontenttype (U.openconnection (). getContentType ());
* Response.setheader ("content-disposition", "inline"; Filename= "+ f.getname ()); //
* File name should be encoded as UTF-8} else {//Pure download method
*/
Response.setcontenttype ("Application/x-msdownload");
Response.setheader (
"Content-disposition",
"Attachment; Filename= "
+ Urlencoder.encode (Entity.getfilename (), "Utf-8"). Replace ("+", "%20"));
// }
OutputStream out = Response.getoutputstream ();
while (len = Br.read (buf)) > 0) {
Out.write (buf, 0, Len);
}
Br.close ();
Out.close ();
}


/**
* Save File
*
* @param path
* @param content
*/
public static void Write (string path, string content) {
try {
OutputStreamWriter out = new OutputStreamWriter (new FileOutputStream (path), "UTF-8");
Out.write ("\ n" +content);
Out.write (content);
Out.flush ();
Out.close ();
} catch (IOException e) {
E.printstacktrace ();
}
}

/**
* Delete Files
*
* @param path
*/
public static void Delete (String path) {
File File = new file (path);
if (file.exists ()) {
File.delete ();
}
Logger.info ("Delete file:" + path);
}

/**
* Delete all files under a path
*
* @param path
*/
public static void Deleteallfiles (String path) {
File Dirfile = new file (path);
if (!dirfile.exists () | |!dirfile.isdirectory ()) {
Return
}
file[] files = dirfile.listfiles ();
for (int i = 0; i < files.length; i++) {
if (Files[i].isfile ()) {
Delete (Files[i].getabsolutepath ());
}
}
}

/**
* Copy Files
*/
public static void CopyFile (String frompath, String destpath) {
File Oldfile = new file (Frompath);
File NewFile = new file (destpath);
if (!oldfile.exists ()) {
throw new RuntimeException ("file does not exist!");
}
if (newfile.exists ()) {
Newfile.delete ();
} else if (!newfile.getparentfile (). exists ()) {
Newfile.getparentfile (). Mkdirs ();
}

int bytesum = 0;
int byteread = 0;
When a try {//file exists
InputStream instream = new FileInputStream (Frompath); Read in the original file
FileOutputStream fs = new FileOutputStream (destpath);
byte[] buffer = new byte[1024];
while ((Byteread = instream.read (buffer))! =-1) {
Bytesum + = Byteread; Number of bytes File size
Fs.write (buffer, 0, byteread);
}
Fs.close ();
Instream.close ();
} catch (Exception e) {
throw new RuntimeException ("Error copying single file operation!");
}
}

}

Java upload Excel file import data