Excel上传

最新推荐文章于 2025-03-03 11:38:36 发布

孙小伤

最新推荐文章于 2025-03-03 11:38:36 发布

阅读量267

点赞数

CC 4.0 BY-SA版权

分类专栏： JAVA

本文链接：https://blog.youkuaiyun.com/qq_33266657/article/details/79695130

JAVA 专栏收录该内容

1 篇文章

订阅专栏

自己在开发过程中用到了用到了excel的导入和导出功能。现在记录一下开发的过程。

因为代码是拼凑的，所有完善的不是很好。凑合着先用着，然后再优化吧

前台用的bootstrap。

modal层：

<div class="modal fade" id="importDialog" tabindex="-1" role="dialog"
		aria-labelledby="myModalLabel">
		<div class="modal-dialog">
			<div class="modal-content">
				<div class="modal-header">
					<button type="button" class="close" id="closebutton"
						data-dismiss="modal" aria-hidden="true">×</button>
					<h4 class="modal-title">电费-上传</h4>
				</div>
				<div class="modal-body">
					<form class="form-horizontal" role="form" id="electricityimport"
						action="import" method="post" enctype="multipart/form-data"
						target="hidden_frame">
						<div class="form-group">
							<label style="padding-top: 0px;" class="col-sm-3 control-label">选择数据</label>
							<div class="col-sm-9">
								<input type="file" name="file" id="file" />
							</div>
						</div>
						<div class="form-group">
							<label style="padding-top: 0px;" class="col-sm-3 control-label">说明</label>
							<lable class="col-sm-9 "> 只能导入Excel表格,包含.xls和xlsx类型 </lable>
						</div>
						<div class="text-center">
							<button class="btn btn-primary" type="button"
								id="importelectricity">导入</button>	   
							<button class="btn btn-default" type="reset" id="clear_form">清空</button>
						</div>
					</form>
				</div>
			</div>
		</div>
	</div>

button按钮：

<button id="uploadElectricityFees" class="btn  btn-sm"
	onclick="uploadElectricityFees();">
	<i class="glyphicon glyphicon-upload"></i> 电费上传
</button>

button按钮的onclick

function uploadElectricityFees() {
		$('#importDialog').modal();
	}

onclick事件：

因为使用的是简单地前后端分离，所以使用token来进行跨域，cost_type是类型（电费或者是空调费），如果不使用分离可以把token和cost_type去掉，只使用var url = path + "api/sys/cost/upLoadExcel";

 $("#electricityimport").off('click', '#importelectricity').on('click','#importelectricity',function() {
				var url = path + "api/sys/cost/upLoadExcel";
				url += "?token=" + securityContext.token;
				url += "&cost_type=" + "cost_electricity";
				$.ajaxFileUpload({
					url : url,
					secureuri : false, //
					fileElementId : $("#electricityimport #file").attr("id"),
					dataType : 'json',
					complete : function() {
					},
					success : function(data) {
						//结果为true
						if (data.result_code == "Y") {
							recordLog("O", "DMP", "上传成功",
									securityContext.username);
							result = true;
							$('#importDialog').modal("hide");
							queryElectricityFeesList();
							window.top.bootbox.dialog({
								title : "提示信息",
								message : "电费上传成功！",
								size : "middle",
								buttons : {
									cancelType : {
										label : "确定",
										className : "btn-" + bootstrapSkin,
										callback : function() {
											return true;
										}
									}
								}
							});
						} else if (data.result_code == "N") {
							recordLog("OF", "DMP", "电费上传失败",
									securityContext.username);
							result = false;
							window.top.bootbox.dialog({
								title : "错误信息",
								message : "电费上传失败！" + "<br><br>"
										+ data.result_info,
								size : "middle",
								buttons : {
									cancelType : {
										label : "确定",
										className : "btn-" + bootstrapSkin,
										callback : function() {
											return true;
										}
									}
								}
							});
						}
					}
				});
			});

后台代码：Controller

OperationResult 是自定有实体类，可以不使用，可以使用自己的类

	@RequestMapping(value = "/upLoadExcel", method = RequestMethod.POST)
	public @ResponseBody OperationResult doimport(HttpServletRequest request, @RequestParam("file") MultipartFile file)
			throws Exception {
		OperationResult operationResult = new OperationResult();
		try {
			String cost_type = request.getParameter("cost_type");
			costService.doImport(file, cost_type);
			operationResult.setResult_code("Y");
			operationResult.setResult_info("");
		} catch (Exception e) {
			e.printStackTrace();
			operationResult.setResult_code("N");
			operationResult.setResult_info(e.getMessage());
		}

		return operationResult;
	}

OperationResult的代码：

import org.apache.ibatis.type.Alias;

@Alias("OperationResult")
public class OperationResult {

	private String result_code;
	private String result_info;

	public String getResult_code() {
		return result_code;
	}

	public void setResult_code(String result_code) {
		this.result_code = result_code;
	}

	public String getResult_info() {
		return result_info;
	}

	public void setResult_info(String result_info) {
		this.result_info = result_info;
	}

}

后台代码：Service

List<DmpCostUpload> costUploads = (List<DmpCostUpload>) ExcelReadUtil.readExcel(file, cost_type);这个是读取excel的内容然后产生实体类

public void doImport(MultipartFile file, String cost_type) throws Exception {
		List<DmpCostUpload> costUploads = (List<DmpCostUpload>) ExcelReadUtil.readExcel(file, cost_type);
		for (DmpCostUpload cost : costUploads) {
			if ("cost_airconditioner".equals(cost_type)) {
					costDao.insertDmpElectricityFees(cost);
			} else {
					costDao.insertDmpElectricityFees(cost);
			}
		}
	}

后台代码：readExcel()方法

	public static List<?> readExcel(MultipartFile file,String cost_type) throws Exception {
		String path = file.getOriginalFilename();
		if (StringUtils.isBlank(path)) {
			throw new Exception("文件路径不能为空");
		}
		if (path.endsWith(".xls")) {
			return readXls(file,cost_type);
		} else if (path.endsWith(".xlsx")) {
			return readXlsx(file,cost_type);
		} else {
			throw new Exception(path + "该文件不是excel文件");
		}
	}

@SuppressWarnings({ "rawtypes", "unchecked", "resource" })
	public static List<?> readXls(MultipartFile file,String cost_type) throws Exception {
		InputStream is;
		HSSFWorkbook hssfWorkbook;
		try {
			is = file.getInputStream();
			hssfWorkbook = new HSSFWorkbook(is);
		} catch (Exception fnfe) {
			fnfe.printStackTrace();
			throw new Exception("文件不符合规范或者文件上传失败");
		}
		List list = new ArrayList();
		// 读取表格
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 读取行
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null && hssfRow.getCell(0) != null
						&& StringUtils.isNotBlank(hssfRow.getCell(0).toString())) {
					DmpCostUpload costUpload = new DmpCostUpload();
					//年度
					HSSFCell dmp_cost_year = hssfRow.getCell(0);
					//月度
					HSSFCell dmp_cost_month = hssfRow.getCell(1);
					//片区
					HSSFCell dmp_region_name = hssfRow.getCell(2);
					//公寓
					HSSFCell dmp_apart_name = hssfRow.getCell(3);
					
					if("cost_electricity".equals(cost_type)){
						//楼层
						HSSFCell dmp_floor_name = hssfRow.getCell(4);
						//房间id
						HSSFCell dmp_room_id  = hssfRow.getCell(5);
						//房间号
						HSSFCell dmp_room_name = hssfRow.getCell(6);
						//上月度数
						HSSFCell dmp_last_month = hssfRow.getCell(7);
						//本月度数
						HSSFCell dmp_this_month = hssfRow.getCell(8);
						costUpload.setDmp_room_id(StringUtils.substringBefore(getValue(dmp_room_id), "."));
						costUpload.setDmp_room_name(StringUtils.substringBefore(getValue(dmp_room_name),"."));
						costUpload.setDmp_last_month(getValue(dmp_last_month));
						costUpload.setDmp_this_month(getValue(dmp_this_month));
						costUpload.setDmp_floor_name(getValue(dmp_floor_name));
					}else{
						//楼层id
						HSSFCell dmp_floor_id = hssfRow.getCell(4);
						//楼层名称
						HSSFCell dmp_floor_name  = hssfRow.getCell(5);
						//空调分摊费用
						HSSFCell dmp_air_cond = hssfRow.getCell(6);
						costUpload.setDmp_floor_id(StringUtils.substringBefore(getValue(dmp_floor_id), "."));
						costUpload.setDmp_floor_name(getValue(dmp_floor_name));
						costUpload.setDmp_air_cond(getValue(dmp_air_cond));
					}
					costUpload.setDmp_cost_year(StringUtils.substringBefore(getValue(dmp_cost_year), "."));
					costUpload.setDmp_cost_month(StringUtils.substringBefore(getValue(dmp_cost_month), "."));
					costUpload.setDmp_region_name(getValue(dmp_region_name));
					costUpload.setDmp_apart_name(getValue(dmp_apart_name));
					costUpload.setDmp_cost_type(cost_type);
					costUpload.setDmp_cost_state("N");
					list.add(costUpload);
				}
			}
		}
		return list;
	}

@SuppressWarnings({ "rawtypes", "unchecked", "resource" })
	public static List<?> readXlsx(MultipartFile file,String cost_type) throws Exception {
		InputStream is;
		XSSFWorkbook xssfWorkbook;
		try {
			is = file.getInputStream();
			xssfWorkbook = new XSSFWorkbook(is);
		} catch (Exception fnfe) {
			fnfe.printStackTrace();
			throw new Exception("没有找到需要处理的文件，或者文件内容为空，处理失败");
		}
		List list = new ArrayList();
		// 读取表格
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			// 读取行
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null && xssfRow.getCell(0) != null
						&& StringUtils.isNotBlank(xssfRow.getCell(0).toString())) {
					DmpCostUpload costUpload = new DmpCostUpload();
					//年度
					XSSFCell dmp_cost_year = xssfRow.getCell(0);
					//月度
					XSSFCell dmp_cost_month = xssfRow.getCell(1);
					//片区
					XSSFCell dmp_region_name = xssfRow.getCell(2);
					//公寓
					XSSFCell dmp_apart_name = xssfRow.getCell(3);
					if("cost_electricity".equals(cost_type)){
						//楼层
						XSSFCell dmp_floor_name = xssfRow.getCell(4);
						//房间id
						XSSFCell dmp_room_id  = xssfRow.getCell(5);
						//房间号
						XSSFCell dmp_room_name = xssfRow.getCell(6);
						//上月度数
						XSSFCell dmp_last_month = xssfRow.getCell(7);
						//本月度数
						XSSFCell dmp_this_month = xssfRow.getCell(8);
						costUpload.setDmp_room_id(StringUtils.substringBefore(getValue(dmp_room_id), "."));
						costUpload.setDmp_room_name(StringUtils.substringBefore(getValue(dmp_room_name),"."));
						costUpload.setDmp_last_month(getValue(dmp_last_month));
						costUpload.setDmp_this_month(getValue(dmp_this_month));
						costUpload.setDmp_floor_name(getValue(dmp_floor_name));
					}else{
						//楼层id
						XSSFCell dmp_floor_id = xssfRow.getCell(4);
						//楼层名称
						XSSFCell dmp_floor_name  = xssfRow.getCell(5);
						//空调分摊费用
						XSSFCell dmp_air_cond = xssfRow.getCell(6);
						costUpload.setDmp_floor_id(StringUtils.substringBefore(getValue(dmp_floor_id), "."));
						costUpload.setDmp_floor_name(getValue(dmp_floor_name));
						costUpload.setDmp_air_cond(getValue(dmp_air_cond));
					}
					costUpload.setDmp_cost_year(StringUtils.substringBefore(getValue(dmp_cost_year), "."));
					costUpload.setDmp_cost_month(StringUtils.substringBefore(getValue(dmp_cost_month), "."));
					costUpload.setDmp_region_name(getValue(dmp_region_name));
					costUpload.setDmp_apart_name(getValue(dmp_apart_name));
					costUpload.setDmp_cost_type(cost_type);
					costUpload.setDmp_cost_state("N");
					list.add(costUpload);
				}
			}
		}
		return list;
	}

@SuppressWarnings("deprecation")
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell == null) {
			return null;
		}
		switch (hssfCell.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return String.valueOf(hssfCell.getBooleanCellValue());
		case Cell.CELL_TYPE_NUMERIC:
			return String.valueOf(hssfCell.getNumericCellValue());
		default:
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

@SuppressWarnings("deprecation")
	private static String getValue(XSSFCell xssfRow) throws Exception {
		if (xssfRow == null) {
			return null;
		}
		switch (xssfRow.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return String.valueOf(xssfRow.getBooleanCellValue());
		case Cell.CELL_TYPE_NUMERIC:
			return String.valueOf(xssfRow.getNumericCellValue());
		default:
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

然后就是对应的具体的业务操作。

引入的：

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

poi-3.9.jar
poi-examples-3.17.jar
poi-excelant-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
poi-scratchpad-3.17.jar