Servlet+poi读写excel

poi导入excel数据

依赖如下：

private void importData(HttpServletRequest req, HttpServletResponse resp){
	    String iYear    = "";//假设前面传了两个参数，第一个参数是年
		String iQuarter = "";//第二个参数是季度
		List<FileItem> 	items;
		try {
			items = importFile(req, resp);
			for (FileItem item : items) {
				String name 	= "";
				String value 	= "";
				if (item.isFormField()) {
					name = item.getFieldName();
					value = item.getString("utf-8");
					if (name.equals("importYear"))  //获取参数一
						iYear = value;
					if (name.equals("importQuarter")) //获取参数二
						iQuarter = value;
				}
			}
			for (FileItem item : items) {
				if (!item.isFormField()) {
					InputStream in = item.getInputStream();
					//读取原始数据
					List<Object> objectList = readXls( in,iYear,iQuarter );
					in.close();
					//对原始数据做处理
					
				}
			}
		} catch (ServletException e) {e.printStackTrace();} 
		catch (IOException e) {e.printStackTrace();}
	}
	
	public List<Object> readXls(InputStream path,String pYear,String pQuarter)
			throws IOException {
		List<Object> ObjectList = new ArrayList<Object>();
		Object 		iObject 	= null;
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(path);
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					if ( numSheet == 0 ) { //获取第一个sheet信息 
						System.out.println(getCellValue(hssfRow.getCell(0)) +","+getCellValue(hssfRow.getCell(1)) +","+getCellValue(hssfRow.getCell(3)) +","+getCellValue(hssfRow.getCell(4)));
						//一般我会将信息存到一个Object里面
					}else if ( numSheet == 1 ) { //获取第二个sheet信息 
						//将信息存到一个Object里面
					}else{
					}
					//对象放入集合
					ObjectList.add(iObject);
				}
			}
		}
		return ObjectList;
	}
	
	private String getCellValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC: {
				short format = cell.getCellStyle().getDataFormat();
				if (format == 14 || format == 31 || format == 57
						|| format == 58) { // excel中的时间格式
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = DateUtil.getJavaDate(value);
					cellvalue = sdf.format(date);
				}
				// 判断当前的cell是否为日期类型
				else if (HSSFDateUtil.isCellDateFormatted(cell)) { 
				  	// 经测试发现HSSFDateUtil.isCellDateFormatted(cell)只识别2022/02/02这种格式。
					// 如果是日期类型，获取该Cell的Date值 （对2022-02-02格式识别不出是日期格式）
					Date date = cell.getDateCellValue();
					DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = formater.format(date);
				} else { // 纯数字
					// 获取当前Cell的数值
					cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为string
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getStringCellValue().replaceAll("'", "''");
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				cellvalue = null;
				break;
			// 默认的Cell值
			default: {
				cellvalue = " ";
			}
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	public static List<FileItem> importFile(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String iRst = "";
		List<FileItem> items = null;
		// 1、创建一个DiskFileItemFactory工厂
		DiskFileItemFactory factory = new DiskFileItemFactory();
		// 2、创建一个文件上传解析器
		ServletFileUpload upload = new ServletFileUpload(factory);
		// 解决上传文件名的中文乱码
		upload.setHeaderEncoding("UTF-8");
		factory.setSizeThreshold(1024 * 500);// 设置内存的临界值为500K
		upload.setSizeMax(1024 * 1024 * 500);// 设置上传的文件总的大小不能超过500M

		try {
			// 1. 得到 FileItem 的集合 items
			items = upload.parseRequest(request);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return items;
	}

POI导出excel

private void exportData(HttpServletResponse resp){
		String iFileName = "XXX模板";
		try {
			OutputStream out = resp.getOutputStream();
			resp.reset();
			resp.setHeader(
					"content-disposition",
					"attachment;filename="
							+ java.net.URLEncoder.encode(iFileName,
									"UTF-8") + ".xls");
			resp.setContentType("application/msexcel");
			List<List<String>> data = new ArrayList<List<String>>();
			
			String[] header0 = { "姓名"   , "角色"   , "权重"  ,"说明" ,"排序"};
			String[] header1 = { "部门类型", "部门名称", "领导","说明" ,"部门排序"};
			String[] header2 = { "部门名称", "人员名称", "职位", "说明", "特殊处理" , "人员排序"};
			ExportExcelUtils eeu = new ExportExcelUtils();
			HSSFWorkbook workbook = new HSSFWorkbook();
			eeu.exportExcel(workbook, 0, "Sheet_1名称", header0, data, out);
			eeu.exportExcel(workbook, 1, "Sheet_2名称", header1, data, out);
			eeu.exportExcel(workbook, 2, "Sheet_3名称", header2, data, out);
			// 原理就是将所有的数据一起写入，然后再关闭输入流。
			workbook.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

ExportExcelUtils.exportExcel方法内容如下：

public void exportExcel(HSSFWorkbook workbook, int sheetNum,
			String sheetTitle, String[] headers, List<List<String>> result,
			OutputStream out) throws Exception {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum, sheetTitle);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
 
		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);
 
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
		
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
		}
		// 遍历集合数据，产生数据行
		if (result != null) {
			int index = 1;
			for (List<String> m : result) {
				row = sheet.createRow(index);
				int cellIndex = 0;
				for (String str : m) {
					HSSFCell cell = row.createCell((short) cellIndex);
					cell.setCellValue(str.toString());
					cellIndex++;
				}
				index++;
			}
		}
	}