这里只使用Servlet,先用apache fileupload包将EXCEL文件流上传到服务器,然后服务器端读取EXCEL
JSP页面:
<form action="ImportExcelToDatabase?action=1" enctype="multipart/form-data" method="post">
<input type="file" name="excel"><br>
<input type="submit" value="导入">
</form>
?
Servlet类:
package readexcel;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.commons.fileupload.*;
import java.util.*;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ImportExcelToDatabase extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
??? throws ServletException, IOException {
?? this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
??? throws ServletException, IOException {
?? String strAction = request.getParameter("action");
?? int action = 0;
?? if (strAction != null && !strAction.equals("")) {
??? action = Integer.parseInt(strAction);
?? }
?? switch (action) {
?? case 1:
??? this.importExcelToDatabase(request, response);
??? break;
?? }
}
?
public void importExcelToDatabase(HttpServletRequest request,
??? HttpServletResponse response) throws ServletException, IOException {
?? response.setContentType("text/html;charset=gbk");
?? PrintWriter out = response.getWriter();
?? if (!DiskFileUpload.isMultipartContent(request)) {
??? out.println("只能处理multipart/form-data类型的数据!");
??? return;
?? }
?? DiskFileUpload fu = new DiskFileUpload();
?? // 最多上传200M数据
?? fu.setSizeMax(1024 * 1024 * 200);
?? // 超过1M的字段数据采用临时文件缓存
?? fu.setSizeThreshold(1024 * 1024);
?? // 设置上传的普通字段的名称和文件字段的文件名所采用的字符集编码
?? fu.setHeaderEncoding("gbk");
?? // 得到所有表单字段对象的集合
?? List fileItems = null;
?? try {
??? fileItems = fu.parseRequest(request);
?? } catch (FileUploadException e) {
??? out.println("解析数据时出现如下问题：");
??? e.printStackTrace(out);
??? return;
?? }
?? // 处理每个表单字段
?? Iterator i = fileItems.iterator();
?? while (i.hasNext()) {
??? FileItem fi = (FileItem) i.next();
??? if (fi.isFormField()) {
???? String content = fi.getString("GBK");
???? String fieldName = fi.getFieldName();
???? request.setAttribute(fieldName, content);
??? } else {
???? try {
????? String pathSrc = fi.getName();
????? /*
????? * 如果用户没有在FORM表单的文件字段中选择任何文件，那么忽略对该字段项的处理
????? */
????? if (pathSrc.trim().equals("")) {
?????? continue;
????? }
????? InputStream is=fi.getInputStream();//得到文件流
????? Workbook wb = Workbook.getWorkbook(is);
????? Sheet sheet = wb.getSheet(0);
????? Vector<DataVo> v=new Vector<DataVo>();
????? for(int m=0;m<100000;m++){
?????? //开始读取EXCEL了
?????? Cell c0=sheet.getCell(0,m+4);
?????? String strc0=c0.getContents();
?????? Cell c2=sheet.getCell(2,m+4);
?????? String strc2=c2.getContents();
?????? Cell c3=sheet.getCell(3,m+4);
?????? String strc3=c3.getContents();
?????? if(strc3==""){
??????? break;
?????? }
?????? DataVo vo=new DataVo();
?????? vo.setType(strc0);
?????? vo.setName(strc2);
?????? vo.setTon(Double.parseDouble(strc3));
?????? v.addElement(vo);
????? }
????? List<DataVo> list=v;
????? for(int j=0;j<list.size();j++){
?????? DataVo vo=(DataVo)list.get(j);
?????? System.out.println(vo.getName());
????? }
???? } catch (Exception e) {
????? out.println("存储文件时出现如下问题：");
????? e.printStackTrace(out);
????? return;
???? } finally { // 总是立即删除保存表单字段内容的临时文件
????? fi.delete();
???? }
??? }
?? }
}
}