Spring boot2.0 入门(十二)-xlsx表格下载与上传（导入）

xlsx工具包

用JavaPOI导出Excel时，需要考虑Excel版本及数据量的问题。针对不同的Excel版本，要采用不同的工具类，否则出现错误信息。
**HSSFWorkbook:**操作Excel2003以前（包括2003）的版本，扩展名是.xls,行最多65536。
**XSSFWorkbook:**操作Excel2007的版本，扩展名是.xlsx，行最多104万行
**SXSSFWorkbook:**从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。
pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

一.xlsx表格下载

controller

@GetMapping("/importTemplate")
void getImportTemplate(HttpServletResponse response)
{
	ExcelUtil.setResponseProperties("模板", response);
	OutputStream os;
	try
	{
		os = response.getOutputStream();
		ExcelUtil.getImportTemplate(os);
	}
	catch (Exception e)
	{
		logger.error(e.toString());
	}
}

在响应头中设置编码和ContentType，并添加内容

public class ExcelUtil
{
	    public static void setResponseProperties(String fileName, HttpServletResponse response)
    {
     // 设置文件后缀
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fn = fileName + sdf.format(new Date()) + ".xlsx";
        // 读取字符编码
        String utf = "UTF-8";
        // 设置响应
        response.setContentType("application/ms-txt.numberformat:@");
        response.setCharacterEncoding(utf);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        try
        {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
        }
        catch (UnsupportedEncodingException e)
        {
           logger.error(e.toString());
        }
    }
    public static void getImportTemplate(OutputStream os) throws IOException
	{
	
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();
		XSSFRow headerRow = sheet.createRow(0);
		XSSFRow sampleRow = sheet.createRow(1);
		
		Map<String, String> importTemplate = new LinkedHashMap<String, String>(){{
			put("姓名", "例如:张三");
	    	put("年龄", "例如:35");
		}};
		
		for(String key:importTemplate.keySet())
		{
			headerRow.createCell(headerRow.getLastCellNum()==-1?0:headerRow.getLastCellNum()).setCellValue(key);
			sampleRow.createCell(sampleRow.getLastCellNum()==-1?0:sampleRow.getLastCellNum()).setCellValue(importTemplate.get(key));
		}
		workbook.write(os);
		os.flush();
		workbook.close();
	}
}

二.上传(导入)xlsx表格

前台文件上传插件使用的bootstrap的插件bootstrap-fileinput

<input id="importTest" type="file" class="file-loading" name="fileTest">

导入插件JS

function initImportFile()
{
	//初始化文件上传参数
	$("#importTest").fileinput({
		language: 'zh', //设置语言
		uploadUrl: '/importFile', //上传的地址
		showClose: true,
		showUpload: true, //是否上传文件
		showRemove: false,
		showCancel:false,
		showZoom: false,
		dropZoneEnabled: false,//是否显示拖拽区域
		showPreview:false,//是否显示预览窗口
		maxFileSize:50*1024,//50M
		//minFileCount: 1,//不设置则只能上传一个
		//maxFileCount: 1, 
		layoutTemplates: {progress:''},//取消进度条
		allowedFileExtensions: ['xlsx'],
		allowedPreviewTypes : ['image','video', 'audio', 'flash'],
		elErrorContainer: "#importError",
		/* previewSettings: {
		html: {display: "none"},
		other: {display: "none"},
		}, */
		//previewFileIcon: "<i class='glyphicon glyphicon-king'></i>", 
		validateInitialCount: true,
		//msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}！"
		}).on('filepreupload', function(event, data, previewId, index) {
			$("input[class='file-caption-name']").val("");
		}).on("fileuploaded",function(event, data){
			var response = data.response;
			
			$("input[class='file-caption-name']").val("");
			$('#importAlertMsg').html(response.msg);
			$('#importAlert').modal('show');
		});
}

controller

@PostMapping("/importFile") 
@ResponseBody
public JSONObject importFile(@RequestParam(value = "fileTest") MultipartFile fileTest,
		HttpServletRequest req)
{
	JSONObject result = new JSONObject();
	try
	{
		result = importServiceimpl.import(fileTest);
	}
	catch (Exception e) 
	{
		result.put("msg", "导入失败,文件数据异常");
		result.put("result", false);
		logger.error(e.toString());
	}
	return result;
}

service

@Service
public class importServiceimpl
{
	@Override
	@Transactional(rollbackFor = Exception.class)
	public JSONObject Import(MultipartFile file)
	{
		long FILESIZE_LIMIT = 20971520L;
		JSONObject result = new JSONObject();
    	result.put("msg", "导入成功");
        result.put("result", true);
    	if(file == null || file.getSize()==0)
    	{
    		result.put("msg", "导入失败,空文件");
            result.put("result", false);
            return result;
    	}
    	
    	long filesize = assetfile.getSize();
    	if(filesize>FILESIZE_LIMIT)
    	{
    		result.put("msg", "导入失败,文件过大");
            result.put("result", false);
            return result;
    	}
    	
    	List<String> fileType = Arrays.asList("xlsx");
        // 获取文件名，带后缀
        String originalFilename = file.getOriginalFilename();
        // 获取文件的后缀格式
        String fileSuffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1).toLowerCase();
        
        if(fileType.contains(fileSuffix))
        {
        	InputStream in=null;
        	try 
        	{
        		in = assetfile.getInputStream();
        		Workbook book = book = new XSSFWorkbook(in);
            	//处理xlsx
        		//.......
        		
        	}
        	catch (Exception e)
        	{
        		result.put("msg", "导入失败,文件解析错误");
                result.put("result", false);
                logger.error(e.toString());
				return result;
			}
        }
        else
        {
        	result.put("msg", "导入失败,文件格式错误");
            result.put("result", false);
            return result;
        }
        
        return result;
	}
}