java 导入excel案列

思路：

1、JSP 页面  包括浏览文件和提交文件

2、服务器对excel文件进行读出

3、将excel文件内容解析保存至数据库中，然后显示到页面上

必要的包：commons-fileupload.jar 、 commons-io.jar、jxl.jar。

一：JSP 页面

<%
if (request.getParameter("flag") != null && request.getParameter("flag").equals("true")){%>
<script type="text/javascript">
alert("中文电码表导入成功！");
      
            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("中文电码表导入失败！");
</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" οnkeydοwn='return false'; >
  <input class="sumb3" type="button" value="导入中文电码表" onClick="newAttachment();" id="buttonSave"/> 
</form>
<script> 


// --导入中文电码表
function newAttachment()
{
var filename = document.getElementById('importExcel').value;
if(filename=='')
{
alert('请选择要上传的文件');return false;
}
    var fileextend = filename.substring(filename.lastIndexOf('.')+1).toLowerCase();
var moreTemple = "xls";

if(fileextend.indexOf(moreTemple)<0)
{
alert('请选择excel文件！');
return false;
}

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

}

</script>

二：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);
}



/** 转化EXCEL文件并存放到retList中 
         *根据excel数据和数据格式的不同，以下代码的实现方式也会略有不同
* @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();
 //默认路径
 String uploadTo = "D:\\";
 // 支持的文件类型
 String[] errorType = { ".xls" };
 //格式化日期
 SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");
          //缓冲区域
//  File tempPathFile;
 if (FileUpload.isMultipartContent(req)) {
   DiskFileItemFactory factory = new DiskFileItemFactory();
            //设置缓冲区大小，这里是4kb
factory.setSizeThreshold(4096);
// 设置缓冲区目录
   // factory.setRepository(tempPathFile);
//设置最大文件尺寸，这里是4MB
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setSizeMax(4*1024*1024); 
            // 开始读取上传信息
//开始读取上传信息
List fileItems = new ArrayList();
try {
fileItems = upload.parseRequest(req);
   } catch (FileUploadException e1) {
// e1.printStackTrace();
    logger.error(e1.getMessage());
}
   //依次处理每个上传的文件
   Iterator iter = fileItems.iterator();
//System.out.println("fileItems的大小是" + fileItems.size());
   logger.info("fileItems的大小是" + fileItems.size());
//正则匹配，过滤路径取文件名
String regExp = ".+\\\\(.+)$";
Pattern p = Pattern.compile(regExp);
while (iter.hasNext()){
FileItem item = (FileItem) iter.next();
                //忽略其他不是文件域的所有表单信息
//System.out.println("正在处理" + item.getFieldName());
logger.info("正在处理" + 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("上传了不支持的文件类型");
logger.error("上传了不支持的文件类型");
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));
//打开文件
Workbook book = Workbook.getWorkbook(new File(fileName)) ;
//取得第一个sheet
Sheet sheet = book.getSheet(0);
//取得行数
int rows = sheet.getRows();
Map<String, Object> param=new HashMap<String, Object>();
param.put("aliveFlag",Constants.REC_STATUS_USE);
//查出数据库中所有的中文电码表数据
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(列，行)
//System.out.println("******Cell data=="+sheet.getCell(j, i).getContents());
   logger.info("******Cell data=="+sheet.getCell(j, i).getContents());
   if(str!=null&&!str.equalsIgnoreCase("")){
    str=str.trim();
       String telegraph=str.substring(0,1);
   String telegraphcode=str.substring(1,str.length());
   // 如果数据库中已存在，则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;  
}



/** 导入数据进库
* @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);
}
}

}

三：配置web.xml

       <!--  导入中文电码表数据-->
     <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>