Excel导入导出-前后端(JQuery+Servlet)

<body>
	<input id="excel" type="file">
	<input id="import" type="button" value="导入">
</body>
<script type="text/javascript">
	$(function() {
		$("#import").click(function() {
			var filePath = $("#excel").val();
			alert(filePath);
			$.ajax({
				type : 'post',
				url : '${pageContext.request.contextPath}/TankImportServlet',
				data : {
					"file" : filePath ,
			},
				dataType : 'json',
				async : true,
				success : function(data) {
					alert("导入结束，未成功导入的编号："+data.result);
				}

			});

		});
	});
</script>

后端

servlet层
导入文件需要在servlet层先将客户端的文件上传到服务器的制定路径，然后utils层通过服务器的路径导入excel，最后可以删除临时文件
util类
使用Workbook excel = WorkbookFactory.create(fileIn);可以自动识别2007以上或以下的版本，不用再自己判断2003的.xls用HSSFWorkbook还是2007及以上的.xlsx用XSSFWorkbook了。

//excel处理工具类
public class ExcelUtils {

	// 导入excel到油箱产品表  filePath:文件路径
	public int importTankProduct(String filePath) {

		int flag = 0;//记录未成功插入的数量
		try {

			InputStream fileIn = new FileInputStream(filePath);
			Workbook excel = WorkbookFactory.create(fileIn);

			System.out.println("获取excel成功！");

			//获取第一个sheet表
			Sheet sh0 = excel.getSheetAt(0);
			//创建单元格式 颜色：red
			XSSFCellStyle cellStyle=(XSSFCellStyle) excel.createCellStyle();
			XSSFFont cellFont=(XSSFFont) excel.createFont();
			cellFont.setColor(new XSSFColor(Color.red));
			cellStyle.setFont(cellFont);
			//新建油箱产品javaBean
			TankProductBean tankProduct = new TankProductBean();

			// //遍历每一行，将数据一行一行插入数据库
			for (Row row : sh0) {
				if (row.getRowNum() < 1) {
					continue;
				}else if (new TankManageDao().repeatProductId(row.getCell(0).getStringCellValue())) {//检查产品id是否重复
					flag++;
					Cell cell = row.createCell(5);
					cell.setCellValue("产品编号重复!");
					cell.setCellStyle(cellStyle);//将错误信息颜色改为红色
					continue;
				}
				tankProduct.setProductId(row.getCell(0).getStringCellValue());
				tankProduct.setProductName(row.getCell(1).getStringCellValue());
				tankProduct.setRelationId(row.getCell(2).getStringCellValue());
				//插入数据库
				boolean result = new TankManageDao().addProduct(tankProduct);
				//判断插入是否成功
				if (!result) {
					flag++;
						Cell cell = row.createCell(5);
						cell.setCellValue("请检查数据类型");
						cell.setCellStyle(cellStyle);//将错误信息颜色改为红色

				}
				System.out.println("导入结束！");

			}
			if (flag > 0) {
				OutputStream fileOut = new FileOutputStream(filePath);
				excel.write(fileOut);
				fileOut.close();
			}
			fileIn.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("找不到指定路径下的excel文件！");
		}
		return flag;

	}
}

3.导出实现

前端

不能用ajax，只能直接跳转

<script>

	/*导出按钮*/
	$("#export").click(function() {
		exportProcuctPlan();
	});
	/*导出功能*/
	function exportProcuctPlan() {
	$(location).attr(
					'href',
					'${pageContext.request.contextPath}/TankPlanExportServlet?beginDate='
							+ $("#beginDate").val() + '&endDate='
							+ $("#endDate").val() + '&dayNight='
							+ $("#dayNight").val() + '&productLine='
							+ $("#productLine").val() + '&productId='
							+ $("#productId").val());
		}
</script>

后端

servlet层

public class TankPlanExportServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		TankPlanSearchBean planSearch=new TankPlanSearchBean();
		// 获取前台传入的搜索参数
		planSearch.setBeginDate(request.getParameter("beginDate"));
		planSearch.setEndDate(request.getParameter("endDate"));
		planSearch.setProductId(request.getParameter("productId"));
		planSearch.setProductLineId(request.getParameter("productLine"));
		planSearch.setDayNight(request.getParameter("dayNight"));
		
		String filename = "文件名称.xlsx";
		filename = URLEncoder.encode(filename, "UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename=" + filename);
		Workbook excel=new ExcelUtils().exportTankPlan(planSearch);
		OutputStream output=response.getOutputStream();
		excel.write(output);
		   output.close();
	}

}

util类

//导出生产计划表到excel
		public Workbook exportTankPlan(TankPlanSearchBean planSearch) {
			XSSFWorkbook excel=new XSSFWorkbook();
			XSSFSheet sheet1=excel.createSheet();
			XSSFCellStyle cellStyle = (XSSFCellStyle) excel.createCellStyle();
			XSSFFont cellFont = (XSSFFont) excel.createFont();
			XSSFRow row0=sheet1.createRow(0);
			XSSFCell cell0=row0.createCell(0);
			cellFont.setFontHeightInPoints((short)15);
			cellStyle.setFont(cellFont);
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			String  date=formatter.format(new Date());
			cell0.setCellValue("文件名称"+"("+date+")");
			cell0.setCellStyle(cellStyle);
			sheet1.addMergedRegion(new CellRangeAddress(0,0,0,6));//合并单元格0行-0行，0列-0列
			XSSFRow row1=sheet1.createRow(1);
			row1.createCell(0).setCellValue("计划编号");
			row1.createCell(1).setCellValue("产品编号");
			row1.createCell(2).setCellValue("生产日期");
			row1.createCell(3).setCellValue("生产线");
			row1.createCell(4).setCellValue("白夜班");
			row1.createCell(5).setCellValue("计划数量");
			row1.createCell(6).setCellValue("实际数量");
			
			ArrayList<TankPlanBean> productPlanList=new TankPlanDao().getExportPlanList(planSearch);
			for(int i=0;i<productPlanList.size();i++) {
				XSSFRow row=sheet1.createRow(i+2);
				row.createCell(0).setCellValue(productPlanList.get(i).getPlanId());
				row.createCell(1).setCellValue(productPlanList.get(i).getProductId());
				row.createCell(2).setCellValue(productPlanList.get(i).getProductDate().toString());
				row.createCell(3).setCellValue(productPlanList.get(i).getProductLineId());
				row.createCell(4).setCellValue(productPlanList.get(i).getDayNight());
				row.createCell(5).setCellValue(productPlanList.get(i).getNumPlan());
				row.createCell(6).setCellValue(productPlanList.get(i).getNumReal());
				
			}
			
			return excel;
			
		}