项目中的应用,有一个“批量新增”的按钮,点击时包括的处理:Excel文档上传,读取Excel数据等。
Excel上传部分,用Struts2 文件上传实现,关键代码如下:
package com.eshore.itsm.common;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
/**
* UploadUtil 文件上传工具类
*
* @author Musoon
*
*/
public class UploadUtil{
private static final long serialVersionUID = 572146812454l;
private static final int BUFFER_SIZE = 16 * 1024;
public void upload(File src, File des){
InputStream in = null;
OutputStream out = null;
FileInputStream fis = null;
FileOutputStream fos = null;
try {
fis = new FileInputStream(src);
fos = new FileOutputStream(des);
in = new BufferedInputStream(fis, BUFFER_SIZE);
out = new BufferedOutputStream(fos, BUFFER_SIZE);
byte[] buffer = new byte[BUFFER_SIZE];
while (in.read(buffer) > 0) {
out.write(buffer);
}
out.flush();
} catch (Exception e) {
System.out.println("上传文件失败!");
e.printStackTrace();
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
System.out.println("关闭InputStream失败!");
e.printStackTrace();
}
}
if (null != out) {
try {
out.close();
} catch (IOException e) {
System.out.println("关闭OutputStream失败!");
e.printStackTrace();
}
}
}
}
public String getFilePath(File src, File dest) {
upload(src, dest);
return dest.getAbsolutePath();
}
}
读取Excel的工具类:
/**
*
*/
package com.eshore.itsm.common;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
/**
* ExcelUtil 读取Excel工具类
*
* @author Musoon
*
*/
public class ExcelUtil {
/**
* 读取单个sheet的Excel,
* @param path
* @return list的第一个值是map的key值
*/
public static List<Map<String, String>> readExcel(String path) {
File file = null;
Workbook book = null;
Sheet[] sheets = null;
Sheet sheet = null;
Map<String, String> map = null;
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
Map<String, String> keyMap = new HashMap<String, String>();
try {
file = new File(path);
book = Workbook.getWorkbook(file);
//获取到所有工作表
sheets = book.getSheets();
if (null != sheets && 0 != sheets.length) {
sheet = sheets[0];
System.out.println("工作表对象的名字:" + sheet.getName() + "/n"
+ "工作表对象的行数:" + sheet.getRows() + "/n"
+ "工作表对象的列数:" + sheet.getColumns());
//第一行即所有map的键值
for (int i = 0; i < sheet.getColumns(); i++) {
String mapKey = sheet.getCell(i, 0).getContents();
keyMap.put(String.valueOf(i), handleKey(mapKey));
}
mapList.add(keyMap);
//行循环,一行就一个map
for (int j = 1; j < sheet.getRows(); j++) {
map = new HashMap<String, String>();
//列循环
for (int k = 0; k < sheet.getColumns(); k++) {
//标题栏
String key = sheet.getCell(k, 0).getContents();
Cell value = sheet.getCell(k, j);
System.out.println("坐标为 (" + k + ", " + j + ") 的值为 == > " + value.getContents());
map.put(handleKey(key), value.getContents());
}
mapList.add(map);
}
}
book.close();
} catch (Exception e) {
System.out.println("读取Excel失败!");
e.printStackTrace();
}
return mapList;
}
/**
* 处理key,形如"XX(XX)",变为"XX"
* @param key
* @return
*/
public static String handleKey(String key) {
String str = "";
if (null != key && 0 != key.trim().length()) {
int i = 0;
//英文括号
if (-1 != key.indexOf("(")) {
i = key.indexOf("(");
str = key.substring(0, i);
//中文括号
} else if (-1 != key.indexOf("(")) {
i = key.indexOf("(");
str = key.substring(0, i);
} else {
str = key;
}
}
return str.trim().toString();
}
}
上传接口jsp页面:
<div style="float:none;padding-top:5px;line-height:normal;" class="button_b">
<a href="#" id="downLoadTemplate"><span>模板下载</span></a>
</div>
<form action="" method ="POST" enctype ="multipart/form-data" id="billItemExcelUploadForm">
<input type="hidden" id="billParentId" name="billParentId" value="<%=request.getParameter("billParentId")%>"/>
<table>
<tr>
<td>
<input type="text" id="txt" name="txt" readonly="readonly">
</td>
<td>
<div align="left" style="padding-top:2px; padding-bottom:2px; line-height:normal;display:inline;" class="button_a" οnclick="billItemFile.click()" >
<a id="toInsert" href="#"><span>浏 览</span></a>
</div>
<input type="file" id="billItemFile" οnchange="txt.value=this.value" name="billItemFile" class="file" hidefocus>
</td>
<td><div align="left" style="padding-top:2px; padding-bottom:2px; line-height:normal;display:inline;" class="button_a" >
<a id="billItemFileSubmit" href="#" ><span>批量导入</span></a>
</div></td>
</tr>
</table>
</form>
action部分:
private File billFormatFile;
private String contentType;
private String fileName;
public String getFileName() {
return fileName;
}
public void setBillFormatFileContentType(String contentType) {
this.contentType = contentType;
System.out.println("上传类型为==>" + contentType);
}
public void setBillFormatFileFileName(String fileName) {
this.fileName = fileName;
System.out.println("上传的文件名为==>" + fileName);
}
public void setBillFormatFile(File billFormatFile) {
this.billFormatFile = billFormatFile;
}
/**
* 批量新增
* @return NONE
*/
public String saveBillFormatBatch() {
//记录读取Excel得到的所有数据
List<Map<String, String>> list = null;
//记录Excel中第一行之后的每一行数据
Map<String, String> m = null;
//记录Excel中第一行数据,即行头作为每一个字段的key
Map<String, String> keyMap = null;
//记录新增出错的所有数据
List<BillFormatVo> errorList = new ArrayList<BillFormatVo>();
//出错的具体数据
BillFormatVo billFormatVo = null;
//批量新增成功的记录条数
int successCount = 0;
//批量新增失败的记录条数
int failCount = 0;
//记录批量新增失败的原因
StringBuffer sb = null;
//把批量新增的Excel上传到服务器
File file = new File(ServletActionContext.getServletContext().getRealPath("/common/excelModule") + "/" + fileName);
UploadUtil uu = new UploadUtil();
//读取上传路径
String path = uu.getFilePath(billFormatFile, file);
try {
//根据上传路径读取Excel数据
list = ExcelUtil.readExcel(path);
//省略对list数据的相关操作
} catch (Exception e) {
e.printStackTrace();
}
return NONE;
}