批量新增_(struts2文件上传、下载，jxl读取Excel等)

项目中的应用，有一个“批量新增”的按钮，点击时包括的处理：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">&nbsp;
	</td>
	<td>
	
		<div align="left" style="padding-top:2px; padding-bottom:2px; line-height:normal;display:inline;" class="button_a"  onclick="billItemFile.click()" >
			<a id="toInsert" href="#"><span>浏&nbsp;&nbsp;览</span></a>
		</div>
		<input type="file" id="billItemFile" onchange="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;
	}

评论

3 楼 clmgwan 2013-03-11  

clmgwan 写道

楼主你好，这个代码少一个BillFormatVo的pojo类，可否提供下？还有能否把上传的excel文件模版附带下？希望看到后与我联系  QQ:476603723    谢谢

Action类也有点儿不完整，如果是struts2的话，缺少execute()方法

2 楼 clmgwan 2013-03-11  

楼主你好，这个代码少一个BillFormatVo的pojo类，可否提供下？还有能否把上传的excel文件模版附带下？希望看到后与我联系  QQ:476603723    谢谢

1 楼 JavaEthan 2011-11-17  

大师 您好！您发的这个对我非常要帮助，但是我是个菜鸟，这里面还有我有几个不懂的地方，比如连接mysql以及一些xml的配置问题，希望您看到能与我联系 406036686 不胜感激