上传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);
}
}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>
1225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
