导出excel格式文件

最新推荐文章于 2020-08-28 08:19:53 发布

qq_36385601

最新推荐文章于 2020-08-28 08:19:53 发布

阅读量589

点赞数 1

分类专栏： JavaWeb 文章标签：导出excel文件

本文链接：https://blog.csdn.net/qq_36385601/article/details/84837543

版权

JavaWeb 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1-在controller中向exportExcelz方法中传入3个参数需要，导出的集合，导出的文件名，HttpServletResponse请求参数

2-导出excel文件的静态方法：对不同的需求有不同的写法，删除那些花里胡哨就可以了。

 public static void exportExcelz(List<BaseCustomer> list,String filename,HttpServletResponse res) throws IOException{
    	//处理filename乱码的问题
    	/*filename = new String(filename.getBytes("8859_1"), "utf8");*/
    	OutputStream output = res.getOutputStream();// 取得输出流
        res.reset();// 清空输出流
        res.setHeader("Content-Disposition", "attachment; filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")+".xls");
        res.setContentType("application/msexcel");// 定义输出类型
    	List<String> list1=new ArrayList<String>();
    	list1.add("Area");
    	list1.add("Sale country");
    	list1.add("Customer name");
    	list1.add("Customer code");
    	list1.add("language");

		// 创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();
		// 建立新的sheet对象（excel的表单）
		HSSFSheet sheet = wb.createSheet(filename);
		HSSFRow row0 = sheet.createRow(0);
		row0.setHeight((short) 500);
		
		//创建抬头标签
		HSSFCell  createCell0= row0.createCell(0);
		createCell0.setCellStyle(ExcelStyle.getStyle1(wb));//标题一行设置样式
		createCell0.setCellValue(filename);
		
		//合并抬头标签单元格
		CellRangeAddress regionAddress = new CellRangeAddress(0, 0, 0, list1.size()-1);
		sheet.addMergedRegion(regionAddress);
		
		ExcelStyle.setBorderStyle(HSSFCellStyle.BORDER_THIN, regionAddress, sheet, wb);//标题一行设置边框

		HSSFRow row1 = sheet.createRow(1);
		
		for(int i=0;i<list1.size();i++){
			HSSFCell createCell1 = row1.createCell(i);
			createCell1.setCellValue(list1.get(i));		
			sheet.setColumnWidth(i,5120);
			createCell1.setCellStyle(ExcelStyle.getStyle2(wb));//表头一行设置样式
		}
		for(int j=0;j<list.size();j++){
				HSSFRow row = sheet.createRow(j+2);
				for(int i=0;i<list1.size();i++){
					HSSFCell createCell2 = row.createCell(i);
					sheet.setColumnWidth(i,5120);
					createCell2.setCellStyle(ExcelStyle.getStyle3(wb));//正文单元格样式
				}
				if(list.get(j).getBelongContinent()!=null){
					row.getCell(0).setCellValue(list.get(j).getBelongContinent());
	    		}else{
	    			row.getCell(0).setCellValue("");
	    		}
				if(list.get(j).getBelongContury()!=null){
					row.getCell(1).setCellValue(list.get(j).getBelongContury());
	    		}else{
	    			row.getCell(1).setCellValue("");
	    		}
				if(list.get(j).getCustomerName()!=null){
					row.getCell(2).setCellValue(list.get(j).getCustomerName());
	    		}else{
	    			row.getCell(2).setCellValue("");
	    		}
				if(list.get(j).getCustomerCode()!=null){
					row.getCell(3).setCellValue(list.get(j).getCustomerCode());
	    		}else{
	    			row.getCell(3).setCellValue("");
	    		}
				if(list.get(j).getLanguage()!=null){
					row.getCell(4).setCellValue(list.get(j).getLanguage());
	    		}else{
	    			row.getCell(4).setCellValue("");
	    		}
				
			}
		
		wb.write(output);
		output.flush();
		output.close();
    }

3- 设置导出excel样式的工具类：如果对导出样式没有特殊要求可以不用写导出的样式，在上面exportExcelz方法中将设置样式的代码删除即可。

public class ExcelStyle {

	public static void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){
        RegionUtil.setBorderBottom(border, region, sheet, wb);//下边框
        RegionUtil.setBorderLeft(border, region, sheet, wb);     //左边框
        RegionUtil.setBorderRight(border, region, sheet, wb);    //右边框
        RegionUtil.setBorderTop(border, region, sheet, wb);      //上边框
        
    }
	
	public static HSSFFont getFont1(HSSFWorkbook wb){
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 15);
		font.setFontName("新宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		return font;
	}
	
	public static HSSFFont getFont2(HSSFWorkbook wb){
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setFontName("新宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		return font;
	}
	
	public static HSSFCellStyle getStyle1(HSSFWorkbook wb){
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(getFont1(wb)); // 调用字体样式对象
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		return style;
	}
	
	public static HSSFCellStyle getStyle2(HSSFWorkbook wb){
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(getFont2(wb)); // 调用字体样式对象
		style.setWrapText(true);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
		style.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
		style.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
		return style;
	}
	public static HSSFCellStyle getStyle3(HSSFWorkbook wb){
		HSSFCellStyle style = wb.createCellStyle();
		style.setWrapText(true);
		style.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
		style.setBorderLeft(CellStyle.BORDER_THIN);  // 左边边框
		style.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
		return style;
	}
	
}

4-js发送请求，代码框架easyUI。

function exp(){
	var pnlCenter = $("#cc").layout('panel', 'center');
	var title1 = pnlCenter.panel('options').title;
	//处理中文乱码
	var title= encodeURI(encodeURI(title1));
//导出条件获取
	var customerCode=$('#code').val();
	var customerName=$('#name').val();
	var belongContury=$('#country').val();
	var belongContinent=$('#area').val();
	var language=$('#language').val();
	location.href = 'exportExcel?title='+title+'&customerCode='+customerCode+'&customerName='+customerName+'&belongContury='+belongContury+'&belongContinent='
    +belongContinent+'&language='+language;
}

5-jsp表单查询导出，直接传的查询表单的信息

<form id="query_form" method="post">
			<table align="center" cellpadding="2">
				<tr><td>客户编码：</td><td><input  id="code" name="customerCode" type="text" autocomplete="off" style="width:150px"/></td></tr>
				<tr><td>客户姓名：</td><td><input id="name" name="customerName" type="text" autocomplete="off" style="width:150px" /></td></tr>
				<tr><td>所属国家：</td><td><input id="country" name="belongContury" type="text" autocomplete="off" style="width:150px" /></td></tr>
				<tr><td>所属区域：</td><td><input id="area" name="belongContinent" type="text" autocomplete="off" style="width:150px"  /></td></tr>
				<tr><td>使用语言：</td><td><input id="language" name="language" type="text" autocomplete="off" style="width:150px" /></td></tr>
			</table>
			<div id="ok" align="center" style="margin-top: 10px">
				<a href='javascript:void(0)' onclick="queryform()"
					class='easyui-linkbutton' iconCls='icon-search'>查询</a>
			</div>
		</form>