java的EXcel模板的下载、导入和导出

声明:本文章仅基于个人粗略整理总结,如有不足之处,欢迎指出。

基于ssm+layui开发,所需jar包

<!-- poi3.9:导出excel -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

1、下载Excel模板

前端:

<div class="layui-form-item"></div>
	<form action="/device/downloadExcel" method="get">

		<div class="layui-form-item">
			<label class="layui-form-label">下载模板:</label>
			<div class="layui-input-inline">
				<input class="layui-btn " type="submit" value="下载模板">
			</div>
	</form>
	<label class="layui-form-label"></label>
	<!--  -->
	<form class="layui-form" method="POST" enctype="multipart/form-data"
		id="form1" action="${ctx}/device/uploadExcel">
		<div class="layui-form-item">
			<label class="layui-form-label">上传文件:</label>
			<div class="layui-input-inline">
				<input id="upfile" type="file" name="upfile" calss="layui-btn">
			</div>
		</div>
		<div class="layui-form-item">
			<label class="layui-form-label">  </label>
			<div class="layui-input-inline">
				<input class="layui-btn " type="submit" value="批量导入Excel数据"
					onclick="return checkData()">
			</div>
		</div>
		<div class="layui-form-item">
			<label class="layui-form-label">注意事项:</label>
			<div class="layui-input-block">
				<span style="color: red"></br>1、请根据模板填写数据。</br>2、非必填列不要删除。</br>3、不要调整各个列的先后顺序。</span>
			</div>
		</div>
	</form>
</body>
<script type="text/javascript" src="${ctx }/layui/layui.js"></script>
<script language="javascript">
layui.config({
	base : "js/"
}).use(['form','layer','jquery','laydate'],function(){
	var form = layui.form,
		layer = parent.layer === undefined ? layui.layer : parent.layer,laydate = layui.laydate
		$ = layui.jquery;
	});
	//JS校验form表单信息  
	function checkData() {
		var fileDir = $("#upfile").val();
		var suffix = fileDir.substr(fileDir.lastIndexOf("."));
		if ("" == fileDir) {
			top.layer.msg("选择需要导入的Excel文件!", {
				icon : 5
			});
			return false;
		}
		if (".xls" != suffix && ".xlsx" != suffix) {
			top.layer.msg("文件格式有误!</br>选择Excel格式的文件导入!", {
				icon : 5
			});
			return false;
		}
		$("#form1").attr('disabled','disabled');
		var index = top.layer.msg('数据提交中,请稍候', {
			icon : 16,
			shade : 1
		});
		$('#form1').ajaxSubmit({
			url : ctx + '/device/uploadExcel',
			 dataType:'text',//服务器返回的数据类型 可选XML ,Json jsonp script html text等
			error:function(){
				$("#form1").removeAttr('disabled');
				 top.layer.msg("导入excel出错!", {
		 				icon : 5
		 			}); 
			},
			success:function(d){
				//alert(d);
	        	 top.layer.msg(d, {icon : 1
	 			});   
	             $("#upfile").val("");  
				$("#form1").removeAttr('disabled');
	         	//刷新父页面
			 	//parent.location.reload();
			} 
         });  //form1 
 
		return true;
	}
</script>

后端:

模板文件存放位置:

代码:

@RequestMapping(value="downloadExcel", method = RequestMethod.GET)//method = RequestMethod.GET将数据传递给前端
	public void downloadExcel(HttpServletResponse response,HttpServletRequest request)throws IOException {
		 //获取输入流,原始模板位置
		String  filePath = getClass().getResource("/resource/template/xx模板.xlsx" ).getPath();
        InputStream bis = new BufferedInputStream(new FileInputStream(new File(filePath)));
        //假如以中文名下载的话,设置下载文件名称
        String filename = "导入模板.xls";
        //转码,免得文件名中文乱码
        filename = URLEncoder.encode(filename,"UTF-8");
        //设置文件下载头
        response.addHeader("Content-Disposition", "attachment;filename=" + filename);
        //1.设置文件ContentType类型,这样设置,会自动判断下载文件类型
        response.setContentType("multipart/form-data");
        BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
        int len = 0;
        while((len = bis.read()) != -1){
            out.write(len);
            out.flush();
        }
        out.close();
	}

2、导入数据

controller

@RequestMapping(value = "uploadExcel",method = { RequestMethod.GET , RequestMethod.POST} )
	@ResponseBody
	public ResponseEntity<String> ajaxUploadExcel(HttpServletRequest request, HttpServletResponse response) throws  IOException {	
		String commentHTML=null;
	    HttpHeaders responseHeaders = new HttpHeaders();
	    responseHeaders.setContentType(new MediaType("text","html",Charset.forName("UTF-8")));
		try {
			commentHTML = deviceService.importDeviceExcel(request, response);
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		if (StringUtils.isNotBlank(commentHTML)) {
			return new ResponseEntity<String>(commentHTML, responseHeaders, HttpStatus.OK);
		}else {
			return new ResponseEntity<String>("导入失败!", responseHeaders, HttpStatus.OK);
		}
	}

Service:

	private String no="0";//excel里的序号
  public String importDeviceExcel(HttpServletRequest request, HttpServletResponse response)throws Exception{
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		InputStream in = null;
		List<List<String>>  listob = null;
		MultipartFile file = multipartRequest.getFile("upfile");
		if (file.isEmpty()) {
			throw new Exception("文件不存在!");
		}
		in = file.getInputStream();
		listob = ImportExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
		
        in.close();
        
        int successCount=0;
        int failCount=0;
        //导入字段条件判断
        ArrayList<ArrayList<String>> fieldData =new ArrayList<ArrayList<String>>();//必填值为空的数据行
        Device device = new Device();//格式无误的数据行
        int lisize=listob.size();
        for (int i = 0; i < lisize; i++) {//这部分for循环可根据需求选择忽视
			List<String> lo = listob.get(i);
			if (!no.equals(lo.get(0))) {//no为EXCEL的序列号,本例中excel的第一行是各列的标题,不导入,从第二行开始
				no=lo.get(0);
				if (StringUtils.isNotBlank(lo.get(1))){//判断每行某个数据是否符合规范要求
					//符合要求,插入到数据库Device表中
					saveDeviceFromExcel(device,lo);
					lo=null;
					successCount+=1;
				}else{
					//需要另外生成excel的不规范行,构造excel的数据
					ArrayList<String> dataString=new ArrayList<String>();
					for(int j=0;j<lo.size();j++){
					    String a=String.valueOf(lo.get(j));
						dataString.add(a);
					}
					fieldData.add(dataString);
				}
			}
			
		}
		failCount=fieldData.size();
		String o=null;
		if (failCount>0) {
            //不符合规范的重新生成EXCEL表
			insExcel(fieldData,response);	
			o="成功导入"+successCount+"行,未成功导入"+failCount+"行,请在有误数据表内查看!";
		}else{
			//o="全部导入成功!";
		}

		no="0";
		return o;
    }
    
 public void insExcel( ArrayList<ArrayList<String>> fieldData,HttpServletResponse response) throws Exception{
    	if(fieldData!=null&&fieldData.size()>0){//如果存在不规范行,则重新生成表
			//使用ExcelFileGenerator完成导出
			LoadExcelUtil loadExcelUtil = new LoadExcelUtil(fieldData);
			OutputStream os = response.getOutputStream();
			//导出excel建议加上重置输出流,可以不加该代码,但是如果不加必须要保证输出流中不应该在存在其他数据,否则导出会有问题
			response.reset();
			//配置://文件名
			String fileName = "有误数据表("+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+").xls";
			//处理乱码
			fileName = new String(fileName.getBytes("gbk"),"iso-8859-1");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename="+fileName);
			response.setBufferSize(1024);
			//导出excel的操作
			loadExcelUtil.expordExcel(os);	
		}
    }

导入时读取EXCEL工具类

基本思路:

list集合的中的数据导入到excel表中的步骤如下:

 1、创建 WorkBook对象
 2、获取要导出的表格的总记录数
3、根据总记录和每个excels的所容纳的记录数,进行分页处理,得出分为几个sheetexcels
4
、得出sheet,进行每个sheet的循环处理
    4.1、通过workbook对象进行创建createSheet,HSSFSheet对象
   4.2、拥有了HSSFSheet对象,创建sheet.createRow行表头,单独的一行即数据说明
    4.3、创建表头行HSSFRow,开始导入表头行数据,开始对表头这一行进行数据循环填充
 4.4、接着开始循环每一行数据,对总记录进行循环,如果超过最大容量,那么最大值就是它,否则就是实际记录数
     4.5、开始进入循环,然后都要创建一个行对象HSSFRow,根据现在的页数取出第几条数据集合,进行循环
     4.5.1、也是通过HSSFRow,创建HSSFCell列对象
     4.5.2、通过cell对象设置相应的样式或者编码
     4.5.3、编码设置完,就可以进行每列数据的填充
 5、返回workBook对象

package com.irs.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @ClassName: ImportExcelUtil
 * @Description: excel数据导入数据库
 * @Author: 
 * @Version: V1.00 (版本号)
 * @CreateDate: 
 */
public class ImportExcelUtil {
	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	/**
	 * 描述:获取IO流中的数据,组装成List<List<Object>>对象
	 * 
	 * @param in,fileName
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> getBankListByExcel(InputStream in, String fileName) throws Exception {
		List<List<String>> list = null;

		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空!");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		list = new ArrayList<List<String>>();
		// 遍历Excel中所有的sheet  work.getNumberOfSheets()
		for (int i = 0; i < 1; i++) {
			sheet = work.getSheetAt(i);
			if (sheet != null) {
				// 遍历当前sheet中的所有行
				int totalRow = sheet.getPhysicalNumberOfRows();
				for (int j = 2; j < totalRow; j++) {
					row = sheet.getRow(j);
					if (row != null || row.getFirstCellNum() != j) {
						// 遍历所有的列
						List<String> li = new ArrayList<String>();
						int a=row.getLastCellNum();
						for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
							cell = row.getCell(y);
							String callCal = null;
							if (cell == null || cell.equals("") || cell + "" == "") {
								callCal = "";
							} else {
								callCal = getCellValue(cell) + "";
							}
							li.add(callCal);
						}
						list.add(li);
						li = null;
						//System.out.println(list.toString());
					}
				}
			  return list;
			}
		}
		return null;
	}

	/**
	 * 
	 * @Title: getWorkbook
	 * @Description: 根据文件后缀,自适应上传文件的版本
	 * @Author:
	 * @Version: V1.00 (版本号)
	 * @CreateDate: 
	 * @Parameters: @param inStr
	 * @Parameters: @param fileName
	 * @Parameters: @return
	 * @Parameters: @throws Exception
	 * @Return Workbook
	 * @Throws
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误!");
		}
		return wb;
	}

	/**
	 * 
	 * @Title: getCellValue
	 * @Description: 对表格中数值进行格式化
	 * @Author
	 * @Version: V1.00 (版本号)
	 * @CreateDate:
	 * @Parameters: @param cell
	 * @Parameters: @return
	 * @Return Object
	 * @Throws
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
}

3、导出数据

导出工具类

package com.irs.util;

import java.io.OutputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;


public class LoadExcelUtil {
	
	private final int SPLIT_COUNT = 100; //Excel每个工作簿的行数

	private ArrayList<String> fieldName = null; //excel标题数据集

	private ArrayList<ArrayList<String>> fieldData = null; //excel数据内容	

	private HSSFWorkbook workBook = null;

	/**
	 * 构造器
	 * @param fieldName 结果集的字段名
	 * @param data
	 */
	public LoadExcelUtil(ArrayList<ArrayList<String>> fieldData) {
		this.fieldData = fieldData;
	}

	/**
	 * 创建HSSFWorkbook对象
	 * @return HSSFWorkbook
	 */
	public HSSFWorkbook createWorkbook() {

		workBook = new HSSFWorkbook();//创建一个工作薄对象
		int rows = fieldData.size();//总的记录数
		int sheetNum = 0;           //指定sheet的页数

		if (rows % SPLIT_COUNT == 0) {
			sheetNum = rows / SPLIT_COUNT;
		} else {
			sheetNum = rows / SPLIT_COUNT + 1;
		}

		for (int i = 1; i <= sheetNum; i++) {//循环2个sheet的值
			HSSFSheet sheet = workBook.createSheet("Page " + i);//使用workbook对象创建sheet对象
			/**************对标题添加样式begin********************/
			HSSFRow headRow = sheet.createRow((short) 0); //创建行,0表示第一行(本例是excel的标题)
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//参数说明:1:开始行 2:结束行  3:开始列 4:结束列	
			headRow.createCell(0).setCellValue("序号");
			
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//参数说明:1:开始行 2:结束行  3:开始列 4:结束列	
			//设置样式-颜色
	    	HSSFCellStyle style = workBook.createCellStyle(); 
			style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
			 //水平居中
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        //垂直居中
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			HSSFFont font = workBook.createFont();//创建字体对象
			//字体加粗
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			//字体颜色变红
			font.setColor(HSSFColor.RED.index);
			//如果font中存在设置后的字体,并放置到cellStyle对象中,此时该单元格中就具有了样式字体
			style.setFont(font);
			headRow.createCell(1).setCellValue("名称");
			//headRow.createCell(1).setCellStyle(style);
			
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));//参数说明:1:开始行 2:结束行  3:开始列 4:结束列	
			headRow.createCell(2).setCellValue("所属馈线");
			//headRow.createCell(2).setCellStyle(style);
		
//创建绘图对象
        HSSFPatriarch p=sheet.createDrawingPatriarch();
        //创建单元格对象,批注插入到4行,1列,B5单元格
        HSSFCell cell=sheet.createRow(4).createCell(1);
        //插入单元格内容
         cell.setCellValue(new HSSFRichTextString("批注"));
        //获取批注对象
        //(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
        //前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
        HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
        //输入批注信息
        comment.setString(new HSSFRichTextString("插件批注成功!插件批注成功!"));
        //添加作者,选中B5单元格,看状态栏
        comment.setAuthor("toad");
        //将批注添加到单元格对象中
        cell.setCellComment(comment);

			//分页处理excel的数据,遍历所有的结果
			for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
				if (((i - 1) * SPLIT_COUNT + k) >= rows)//如果数据超出总的记录数的时候,就退出循环
					break;
				HSSFRow row = sheet.createRow((short) (k + 2));//创建1行
				//分页处理,获取每页的结果集,并将数据内容放入excel单元格
				ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);
				for (int n = 0; n < rowList.size(); n++) {//遍历某一行的结果
					HSSFCell cell = row.createCell( n);//使用行创建列对象
					if(rowList.get(n) != null){
						cell.setCellValue((String) rowList.get(n).toString());
					}else{
						cell.setCellValue("");
					}
				}
			}
		}
		return workBook;
	}

	public void expordExcel(OutputStream os) throws Exception {
		workBook = createWorkbook();
		workBook.write(os);//将excel中的数据写到输出流中,用于文件的输出
		os.close();
	}

}

另外:layui2.4之后,可根据调用toolbarDemo头工具栏事件,自动实现简单的数据导出

https://www.layui.com/demo/table/toolbar.html

其他补充:

Poi导出版本优缺点:

版本

Excel后缀

优点

缺点

HSSFWorkbook

(03之前)

.xls

不会内存溢出

最多只能导出 65535行

XSSFWorkbook

(03-07之间)

.xlsx

容易内存溢出

最多可以导出104w条数据

SXSSFWorkbook-POI3.8(07之后)

.xlsx

既然一部分数据持久化到了硬盘中,且不能被查看和访问那么就会导致

1、在同一时间点我们只能访问一定数量内存中存储的数据;

2、不再支持sheet.clone()

3、不再支持对公式的求值,在硬盘中的数据没法读取到内存中进行计

4、在使用模板方式下载数据的时候,不能改动表头

  • 一般不会出现内存溢出(用了硬盘换取内存空间,也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来,而内存中存的都是最新的数据),并且支持大型Excel文件的创建(存储百万条数据绰绰有余)。

使用情况

1、当导入导出的数据不超过7w的情况下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;

2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook;

3、当数据量查过7w,并且我们需要操做Excel中的表头,样式,公式等,这时候我们可以使用 XSSFWorkbook 配合进行分批查询,分批写入Excel的方式来做;

百万数据导入导出

思路:利用阿里的POI封装工具EasyExcel

  1. 首先在查询数据库层面,需要分批进行查询(比如每次查询20w)
  2. 每查询一次结束,就使用EasyExcel工具将这些数据写入一次;
  3. 当一个Sheet写满了100w条数据,开始将查询的数据写入到另一个Sheet中;
  4. 如此循环直到数据全部导出到Excel完毕。

ps:我们需要计算Sheet个数,以及循环写入次数。特别是最后一个Sheet的写入次数

因为你不知道最后一个Sheet会写入多少数据,可能是100w,也可能是25w因为我们这里的500w只是模拟数据,有可能导出的数据比500w多也可能少

ps:我们需要计算写入次数,因为我们使用的分页查询,所以需要注意写入的次数。

其实查询数据库多少次就是写入多少次

实例:https://mp.weixin.qq.com/s/WXPDhqaiFk0PsuT-QVIrtw​​​​​​​