Java import excel Data demo

Ideas:
1. JSP pages include browse files and submit files
2. The server reads the excel file
3. Save the excel file content to the database and display it on the page.

Required packages: commons-fileupload.jar, commons-io.jar, jxl. jar.

I. JSP page
<%
If (request. getParameter ("flag ")! = Null & request. getParameter ("flag"). equals ("true") {%>
<Script type = "text/javascript">
Alert ("Chinese workbook imported successfully! ");

Self. location. replace ("$ {ctx}/ABROAD/PAPER/telegraphcodelist. jsp ");
</Script>
<%} Else if (request. getParameter ("flag ")! = Null & request. getParameter ("flag"). equals ("false") {%>
<Script type = "text/javascript">
Alert ("An error occurred while importing the Chinese workbook! ");
</Script>
<% }%>
<Form name = "attachmentForm" action = "$ {ctx}/ABROAD/PAPER/telegraphcodelist. jsp "target =" _ self "method =" post "enctype =" multipart/form-data ">
<Input type = "file" size = "50" name = "importExcel" id = "importExcel" onkeydown = 'Return false';>
<Input class = "sumb3" type = "button" value = "import Chinese electronic code table" onClick = "newAttachment ();" id = "buttonSave"/>
</Form>
<Script>

// -- Import the Chinese workbook
Function newAttachment ()
{
Var filename = document. getElementById ('importexcel '). value;
If (filename = '')
{
Alert ('select the file to upload '); return false;
}
Var fileextend = filename. substring (filename. lastIndexOf ('.') + 1). toLowerCase ();
Var moreTemple = "xls ";

If (fileextend. indexOf (moreTemple) <0)
{
Alert ('select an excel file! ');
Return false;
}

Document. attachmentForm. action = "$ {ctx}/telegraphcodeImport ";
Document. attachmentForm. submit ();

}

</Script>

 

Ii. Servlet
Package com. baosight. spes. abroad. base. commmon;

Import java. io. File;
Import java. io. IOException;
Import java. text. SimpleDateFormat;
Import java. util. ArrayList;
Import java. util. Date;
Import java. util. HashMap;
Import java. util. Iterator;
Import java. util. List;
Import java. util. Map;
Import java. util. regex. Matcher;
Import java. util. regex. Pattern;

Import javax. servlet. RequestDispatcher;
Import javax. servlet. ServletException;
Import javax. servlet. http. HttpServlet;
Import javax. servlet. http. HttpServletRequest;
Import javax. servlet. http. HttpServletResponse;

Import jxl. Cell;
Import jxl. Sheet;
Import jxl. Workbook;
Import jxl. read. biff. BiffException;

Import org. apache. commons. fileupload. FileItem;
Import org. apache. commons. fileupload. FileUpload;
Import org. apache. commons. fileupload. FileUploadException;
Import org. apache. commons. fileupload. disk. DiskFileItemFactory;
Import org. apache. commons. fileupload. servlet. ServletFileUpload;
Import org. apache. commons. logging. Log;
Import org. apache. commons. logging. LogFactory;

Import com. baosight. efmpx. system. util. SessionUtil;
Import com. baosight. spes. abroad. base. domain. Telegraphcode;
Import com. baosight. spes. abroad. group. agent. GROUPAgentFactory;
Import com. baosight. spes. abroad. util. Constants;

 

Public class FileImportForTelegraphcodeServlet extends HttpServlet {

/**
* SerialVersionUID
*/
Private static final long serialVersionUID = 1L;

Private static Log logger = LogFactory
. GetLog (FileImportForTelegraphcodeServlet. class );

Protected void doPost (HttpServletRequest req, HttpServletResponse resp)
Throws ServletException, IOException {
// TODO Auto-generated method stub
Service (req, resp );
}

 

/**
* Service
*
* @ Param req HttpServletRequest
* @ Param resp HttpServletResponse
* @ Throws ServletException
* @ Throws IOException
*/
Protected void service (HttpServletRequest req,
HttpServletResponse resp) throws ServletException, IOException {
Req. setCharacterEncoding ("UTF-8 ");
Resp. setCharacterEncoding ("UTF-8 ");
Resp. setContentType ("text/html; charset = UTF-8 ");
RequestDispatcher dispatcher = null;
Boolean flag = false;

Try {
List list = parseData (req, resp );
ImportToDB (req, list );
Flag = true;
} Catch (Exception e ){
Flag = false;
// E. printStackTrace ();
Logger. error (e. getMessage ());
}
Dispatcher = req. getRequestDispatcher ("ABROAD/PAPER/telegraphcodelist. jsp? Flag = "+ flag );
Dispatcher. forward (req, resp );
}

 

/** Convert the EXCEL file and place it in retList.
* Depending on the excel data and data format, the implementation of the following code is slightly different
* @ Param request
* @ Return retList
* @ Throws FileUploadException
* @ Throws FileUploadException
* @ Throws IOException
* @ Throws IOException
* @ Throws BiffException
* @ Throws BiffException
*/
Private List parseData (HttpServletRequest req, HttpServletResponse resp) throws FileUploadException, IOException, BiffException {
Req. setCharacterEncoding ("UTF-8 ");
Resp. setCharacterEncoding ("UTF-8 ");
Resp. setContentType ("text/html; charset = UTF-8 ");
List resultList = new ArrayList ();
// Default path
String uploadTo = "D :\\";
// Supported file types
String [] errorType = {". xls "};
// Format the date
SimpleDateFormat format = new SimpleDateFormat ("yyyyMMddHHmmssSSS ");
// Buffer area
// File tempPathFile;
If (FileUpload. isMultipartContent (req )){
DiskFileItemFactory factory = new DiskFileItemFactory ();
// Set the buffer size, which is 4 kb
Factory. setSizeThreshold (4096 );
// Set the buffer directory
// Factory. setRepository (tempPathFile );
// Set the maximum file size, which is 4 MB
ServletFileUpload upload = new ServletFileUpload (factory );
Upload. setSizeMax (4*1024*1024 );
// Start reading upload information
// Start reading upload information
List fileItems = new ArrayList ();
Try {
FileItems = upload. parseRequest (req );
} Catch (FileUploadException e1 ){
// E1.printStackTrace ();
Logger. error (e1.getMessage ());
}
// Process each uploaded file in sequence
Iterator iter = fileItems. iterator ();
// System. out. println ("fileItems size is" + fileItems. size ());
Logger.info ("The fileItems size is" + fileItems. size ());
// Regular Expression matching, filtering the path to get the file name
String regExp = ". + \\\\ (. +) $ ";
Pattern p = Pattern. compile (regExp );
While (iter. hasNext ()){
FileItem item = (FileItem) iter. next ();
// Ignore all other forms that are not in the file Field
// System. out. println ("processing" + item. getFieldName ());
Logger.info ("processing" + item. getFieldName ());
If (! Item. isFormField ()){
String name = item. getName ();
Long size = item. getSize ();
If (name = null | name. equals ("") & size = 0)
Continue;
Matcher m = p. matcher (name );
Boolean result = m. find ();
If (result ){
Boolean flag = false;
For (int temp = 0; temp <errorType. length; temp ++ ){
If (m. group (1). endsWith (errorType [temp]) {
Flag = true;
}
}
If (! Flag ){
// System. out. println ("unsupported file types uploaded ");
Logger. error ("unsupported file types uploaded ");
Throw new IOException (name + ": wrong type ");
}
Try {
String fileName = uploadTo + format. format (new Date () + m. group (1). substring (m. group (1). indexOf ("."));
Item. write (new File (fileName ));
// Open the file
Workbook book = Workbook. getWorkbook (new File (fileName ));
// Obtain the first sheet
Sheet sheet = book. getSheet (0 );
// Obtain the number of rows
Int rows = sheet. getRows ();
Map <String, Object> param = new HashMap <String, Object> ();
Param. put ("aliveFlag", Constants. REC_STATUS_USE );
// Find all the data in the database for the Chinese workbook
List <Telegraphcode> listdata = GROUPAgentFactory. getTelegraphcodeManager (). queryList (param );
Map mapdata = new HashMap ();
If (listdata! = Null & listdata. size ()> 0 ){
For (Telegraphcode telegraphcode: listdata ){
Mapdata. put (telegraphcode. getTelegraphcode (), telegraphcode. getTelegraph ());
}
}

For (int I = 1; I <rows; I ++ ){
Cell [] cell = sheet. getRow (I );
For (int j = 0; j <cell. length; j ++ ){
Map <String, Object> dataMap = new HashMap <String, Object> ();
String str = sheet. getCell (j, I). getContents ();
// GetCell (column, row)
// System. out. println ("****** Cell data =" + sheet. getCell (j, I). getContents ());
Logger.info ("****** Cell data =" + sheet. getCell (j, I). getContents ());
If (str! = Null &&! Str. Repeated signorecase ("")){
Str = str. trim ();
String telegraph = str. substring (0, 1 );
String telegraphcode = str. substring (1, str. length ());
// If the database already exists, continue
If (mapdata. containsKey (telegraphcode) continue;
DataMap. put ("telegraph", telegraph );
DataMap. put ("telegraphcode", telegraphcode );
ResultList. add (dataMap );
};
}
}

} Catch (Exception e ){
// E. printStackTrace ();
Logger. error (e. getMessage ());
}

}

}

}

}

Return resultList;
}

 

/** Import data to the database
* @ Param request
* @ Param retList
*/
Private void importToDB (HttpServletRequest request, List dataList) throws Exception {

Try {
String userLabel = SessionUtil. getCurrentUserLabel (request );
If (dataList! = Null & dataList. size ()> 0 ){
For (int I = 0; I <dataList. size (); I ++ ){
// System. out. println ("****** dataList. get (I) =" + dataList. get (I ));
Logger.info ("******* dataList. get (I) =" + dataList. get (I ));
HashMap row = (HashMap) dataList. get (I );
Telegraphcode telegraphcode = new Telegraphcode ();
Telegraphcode. setTelegraph (row. get ("telegraph"). toString ());
Telegraphcode. setTelegraphcode (row. get ("telegraphcode"). toString ());
Telegraphcode. setAliveFlag (Constants. REC_STATUS_USE );
Telegraphcode. setCreateUserLabel (userLabel );
Telegraphcode. setUpdateUserLabel (userLabel );
GROUPAgentFactory. getTelegraphcodeManager (). insert (telegraphcode );
}
}

} Catch (Exception e ){
// TODO: handle exception
// E. printStackTrace ();
Logger. error (e. getMessage ());
Throw new Exception (e );
}
}

}

3. Configure web. xml
<! -- Import Chinese workbook data -->
<Servlet>
<Servlet-name> fileImportForTelegraphcodeServlet </servlet-name>
<Servlet-class> com. baosight. spes. abroad. base. commmon. FileImportForTelegraphcodeServlet </servlet-class>
</Servlet>
<Servlet-mapping>
<Servlet-name> fileImportForTelegraphcodeServlet </servlet-name>
<Url-pattern>/telegraphcodeImport </url-pattern>
</Servlet-mapping>