上传jar包 common-fileupload1.2.jar common-io.1.3.2.jar
java1.5可能要打jar包补丁xercesImpl.jar excel读取的补丁包。
POI jar包,我用的是3.7版本。
excel2007大数据量读取代码如下(转载+部分修改):XxlsAbstract.java
import java.io.InputStream; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.openxml4j.opc.OPCPackage; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; /** * XSSF and SAX (Event API) */ public abstract class XxlsAbstract extends DefaultHandler { protected SharedStringsTable sst; protected String lastContents; protected boolean nextIsString; protected int sheetIndex = -1; protected List<String> rowlist = new ArrayList<String>(); protected int curRow = 0; protected int curCol = 0; protected List<String> cellLabel = new ArrayList<String>(); //excel记录行操作方法,以行索引和行元素列表为参数,对一行元素进行操作,元素为String类型 // public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ; //excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型 public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException; //只遍历一个sheet,其中sheetId为要遍历的sheet索引,从1开始,1-3 public Map<String,String[]> processOneSheet(String filename,int sheetId) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader reader = new XSSFReader(pkg); SharedStringsTable sst = reader.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // rId2 found by processing the Workbook // 根据 rId# 或 rSheet# 查找sheet InputStream sheet2 = reader.getSheet("rId"+sheetId); sheetIndex++; InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close(); return null; } /** * 遍历 excel 文件 */ public void process(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator<InputStream> sheets = r.getSheetsData(); while (sheets.hasNext()) { curRow = 0; sheetIndex++; InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); } } public XMLReader fetchSheetParser(SharedStringsTable sst)throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); this.sst = sst; parser.setContentHandler(this); return parser; } @Override public void startElement(String uri, String localName, String name,Attributes attributes) throws SAXException { // c => 单元格 if (name.equals("c")) { // 如果下一个元素是 SST 的索引,则将nextIsString标记为true String cellType = attributes.getValue("t"); if (cellType != null && cellType.equals("s")) { nextIsString = true; } else { nextIsString = false; } } // 置空 lastContents = ""; } @Override public void endElement(String uri, String localName, String name)throws SAXException { // 根据SST的索引值的到单元格的真正要存储的字符串 // 这时characters()方法可能会被调用多次 if (nextIsString) { try { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); } catch (Exception e) { } } if(name.equals("c")){//源代码是没有这一段的,也就是当cell里面没有内容是,直接跳过,而我要的效果是如果cell没有内容存入“”进list。 if(cellLabel.size() == 0){ rowlist.add(curCol, " "); curCol++; } if(!"v".equals(cellLabel.get(cellLabel.size() - 1))){ rowlist.add(curCol, " "); curCol++; } } cellLabel.add(name); // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引 // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符 if (name.equals("v")){ String value = lastContents.trim(); value = value.equals("")?" ":value; rowlist.add(curCol, value); curCol++; }else { //如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法 if (name.equals("row")) { // System.out.print(curRow + 1 + "-------"); try { optRows(sheetIndex,curRow,rowlist); } catch (SQLException e) { e.printStackTrace(); } rowlist.clear(); curRow++; curCol = 0; } } } @Override public void characters(char[] ch, int start, int length)throws SAXException { //得到单元格内容的值 lastContents += new String(ch, start, length); } }
如何调用上面该方面,如何修改逻辑。XxlsPrint.java
import java.io.InputStream; import java.sql.SQLException; import java.util.List; import java.util.Map; import java.util.TreeMap; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import excelReadUtil.XxlsAbstract; public class XxlsPrint extends XxlsAbstract { private Map<String,String[]> dataMap = new TreeMap<String,String[]>(); private boolean flag = false; private int startCol = 0; @Override public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException { for (int i = 0; i < rowlist.size(); i++) { System.out.print("'" + rowlist.get(i) + "',"); } System.out.println(); } //只遍历一个sheet,其中sheetId为要遍历的sheet索引,从1开始,1-3 @Override public Map<String,String[]> processOneSheet(String filename,int sheetId) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader reader = new XSSFReader(pkg); SharedStringsTable sst = reader.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // rId2 found by processing the Workbook // 根据 rId# 或 rSheet# 查找sheet InputStream sheet2 = reader.getSheet("rId"+sheetId); sheetIndex++; InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close(); return dataMap; } @Override public void endElement(String uri, String localName, String name)throws SAXException {//可以在这里修改你们的逻辑,下面只是我的一个示例。 // 根据SST的索引值的到单元格的真正要存储的字符串 // 这时characters()方法可能会被调用多次 if (nextIsString) { try { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); } catch (Exception e) { } } if(name.equals("c")){ if(cellLabel.size() == 0){ rowlist.add(curCol, ""); curCol++; } if(!"v".equals(cellLabel.get(cellLabel.size() - 1))){ rowlist.add(curCol, " "); curCol++; } } cellLabel.add(name); // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引 // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符 if (name.equals("v")){ String value = lastContents.trim(); value = value.equals("")?" ":value; rowlist.add(curCol, value); curCol++; }else { //如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法 if (name.equals("row")) { System.out.print(curRow + 1 + "-------"); try { optRows(sheetIndex,curRow,rowlist); } catch (SQLException e) { e.printStackTrace(); } if(flag == true&&rowlist.size() >= (startCol+12)){ // System.out.print(">>>"); String[] data = new String[9]; data[0] = rowlist.get(startCol); //地区 data[1] = rowlist.get(startCol+1);//终端客户 data[2] = rowlist.get(startCol+3);//直接客户 data[3] = rowlist.get(startCol+4);//产品线 data[4] = rowlist.get(startCol+5);//工厂 data[5] = rowlist.get(startCol+6);//项目名称 data[6] = rowlist.get(startCol+9);//负责人 data[7] = rowlist.get(startCol+10);//描述 data[8] = rowlist.get(startCol+12);//2011JAN if(!dataMap.containsKey(data[5])){ dataMap.put(data[5], data); } else{ dataMap.get(data[5])[8] = String.valueOf( Double.parseDouble( dataMap.get(data[5])[8]==null||"".equals(dataMap.get(data[5])[8].trim())?"0.0":dataMap.get(data[5])[8]) + Double.parseDouble(data[8]==null||"".equals(data[8].trim())?"0.0":data[8])); } } for(int i = 0;i<rowlist.size();i++){ if(flag == false){ if("地区".equals(rowlist.get(i))){ startCol = i; flag = true; break; } } } rowlist.clear(); curRow++; curCol = 0; } } } public static void main(String[] args) throws Exception { XxlsPrint howto = new XxlsPrint(); howto.processOneSheet("F:\\数据报表.xlsx",7); // howto.processAllSheets("F:/new.xlsx"); } }
接着excel的大数据已经告一个段落了。接着是excel怎么上传的问题。直接上代码。
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="java.util.*" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>测试</title> <script> function onSubmit(){ //简单验证数据 var file = document.getElementById("file").value; if(file.indexOf(".xls") == -1 && file.indexOf(".xlsx")== -1){ return false; } //简单判断是否完整 //拼接请求的字符串 var url = "logic.jsp"; url+="?file=" + file; url=encodeURI(url); url=encodeURI(url); var form = document.mainform.action=url; return true; } </script> </head> <body> <form name="mainform" id="mainform" method="POST" onSubmit="onSubmit();" enctype="multipart/form-data"> <table border="0" cellspacing="3" cellpadding="3"> <tr> <td align="right" valign="top" nowrap> <label for="file"> <font class="requiredfield">*</font><font class="wizardlabel">文件:</font> </label> </td> <td align="left" valign="top"> <input type="file" name="file" id="file" size="27"/><br/> </td> <td valign="middle"> <font class=wizardbuttonfont> <input type="submit" id="PJL_wizard_ok" name="okButton" value="确定"/> </font> </td> </tr> </table> </form> <% Map<String,String[]> map = (Map<String,String[]>)session.getAttribute("map"); if(map!=null){ %> <table width="990" border="1" align="center" cellpadding="0" cellspacing="0"> <tr> <th>地区</th> <th>终端客户</th> <th>直接客户</th> <th>产品线</th> <th>工厂</th> <th>项目名称</th> <th>负责人</th> <th>描述</th> <th>2011JAN</th> </tr> <% Iterator it = map.entrySet().iterator(); while (it.hasNext()) { Map.Entry entry = (Map.Entry) it.next(); Object value = entry.getValue(); %> <tr > <%for(String o : (String[])value){%> <td NOWRAP><%=o %></td> <%}%> </tr> <%}%> </table> <%}%> </body> </html>
处理页面,也可以放入servlet中,如果用其他的架构,放入相应的业务处理中去
logic.jsp
<%@ page contentType="text/html; charset=UTF-8" language="java" errorPage="" %> <%@ page import="java.lang.*" %> <%@ page import="testExcelRead.XxlsPrint" %> <%@ page import="java.net.URLDecoder"%> <%@ page import="java.util.*,java.text.*"%> <%@ page import="java.io.*" %> <%@ page import="org.apache.commons.fileupload.FileItem"%> <%@ page import="org.apache.commons.fileupload.FileItemFactory"%> <%@ page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%> <%@ page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%> <html> <head> <title>报表统计</title> </head> <body> <% System.out.println(URLDecoder.decode(request.getParameter("file"),"UTF-8")); String sourceFile = ""; try { request.setCharacterEncoding("UTF-8"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } // 文件上傳部分 boolean isMultipart = ServletFileUpload.isMultipartContent(request); if(isMultipart){ try { FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); // 得到所有的表单域,它们目前都被当作FileItem List<FileItem> fileItems = upload.parseRequest(request); Iterator<FileItem> iter = fileItems.iterator(); out.println(fileItems.size() + "----文件控件个数<br />"); // 依次处理每个表单域 while (iter.hasNext()) { FileItem item = (FileItem) iter.next(); if(item.isFormField()){ // 如果item是正常的表单域 String name = item.getFieldName(); String value = item.getString(); } else{ // 如果item是文件上传表单域 // 获得文件名及路径 String filePath = item.getName(); System.out.println(); System.out.println(filePath);//绝对路径 out.println(filePath+"----文件路径<br />"); String fileName = filePath.substring(filePath.lastIndexOf(File.separator)+1); out.println(fileName+"----文件名称<br />"); if (fileName != null){ // 如果文件存在则上传 String temp = System.getProperties().getProperty("java.io.tmpdir"); File fileOnServer = new File(temp + "测试文档" + filePath.substring(filePath.lastIndexOf("."))); item.write(fileOnServer); out.println("文件:"+fileOnServer.getName() + " 上传成功<br />"); System.out.println("文件:"+fileOnServer.getName() + "上传成功"); sourceFile = fileOnServer.getAbsolutePath(); } } } } catch (Exception e) { out.println("have Exception<br />"); e.printStackTrace(); } }else{ out.println("the enctype must be multipart/form-data<br />"); } System.out.println(sourceFile); out.println(sourceFile+"----sourceFile<br />"); // Map<String,String[]> map = new XxlsPrint().processOneSheet(sourceFile,1);//第几个sheet。 // session.setAttribute("map",map); // response.sendRedirect("index.jsp"); %> </body> </html>