SSM导出Excel (POI)

我的实现是在项目的基础之上

话不多说,直接撸代码....

一.导出Excel表

1.添加所需jar (pom.xml)

<!-- 导出Excel所需依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.14-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14-beta1</version>
</dependency>

<dependency>
    <groupId>org.apache.httpcomponents</groupId>
    <artifactId>httpclient</artifactId>
    <version>4.5.2</version>
</dependency>

2.Excel工具类( ExcelUtil )

/* Excel工具类 */
public class ExcelUtil {
	/**
	 * 导出Excel表
	 * @param clazz 数据源model类型
	 * @param objs excel标题以及对应的model字段
	 * @param map 标题行数以及cell字体样式
	 * @param sheetName 工作簿名称
	 * @return
	 * @throws IntrospectionException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 */
	public static XSSFWorkbook createExcelFile(
			Class<?> clazz,
			List<Map<String,Object>> objs,
			Map<Integer,List<ExcelBean>> map,
			String sheetName) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException{
		//创建新的工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建工作表
		XSSFSheet sheet = workbook.createSheet(sheetName);
		//设置excel的字体样式以及标题与内容的创建
		createFont(workbook);//字体样式
		createTableHeader(sheet,map);//创建标题
		createTableRows(sheet,map,objs,clazz);//创建内容
		System.out.println(workbook);
		return workbook;
	}
	private static XSSFCellStyle fontStyle;
	private static XSSFCellStyle fontStyle2;
	private static void createFont(XSSFWorkbook workbook) {
		//表头
		fontStyle = workbook.createCellStyle();
		XSSFFont font1 = workbook.createFont();
		font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font1.setFontName("黑体");
		font1.setFontHeightInPoints((short) 12);//字体大小
		fontStyle.setFont(font1);
		fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);//下边框
		fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
		fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//右边框
		fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
		fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
		//内容
		fontStyle2 = workbook.createCellStyle();
		XSSFFont font2 = workbook.createFont();
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short)10);
		fontStyle2.setFont(font2);
		fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN);//下边框
		fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
		fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//右边框
		fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
		fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER);//居中
	}
	/**
	 * 根据ExcelMapping 生成列头(多行列头)
	 * @param sheet 工作簿
	 * @param map 每行每个单元格对应的列头信息
	 */
	private static void createTableHeader(
			XSSFSheet sheet, 
			Map<Integer, List<ExcelBean>> map) {
		int startIndex = 0;//cell起始位置
		int endIndex = 0;//cell终止位置
		for(Map.Entry<Integer,List<ExcelBean>> entry: map.entrySet()){
			XSSFRow row = sheet.createRow(entry.getKey()); //创建行
			List<ExcelBean> excels = entry.getValue();
			for(int x=0;x<excels.size();x++){
				//合并单元格
				if(excels.get(x).getCols()>1){
					if(x==0){
						endIndex += excels.get(x).getCols()-1;
						//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
						sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
						startIndex += excels.get(x).getCols();
					}else{
						endIndex += excels.get(x).getCols();
						sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
						startIndex += excels.get(x).getCols();
					}
					XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
					//设置内容
					cell.setCellValue(excels.get(x).getHeadTextName());
					if(excels.get(x).getCellStyle() != null){
						//设置格式
						cell.setCellStyle(excels.get(x).getCellStyle());
					}
					cell.setCellStyle(fontStyle);
				}else{
					XSSFCell cell = row.createCell(x);
					//设置内容
					cell.setCellValue(excels.get(x).getHeadTextName());
					if(excels.get(x).getCellStyle() != null){
						//设置格式
						cell.setCellStyle(excels.get(x).getCellStyle());
					}
					cell.setCellStyle(fontStyle);
				}
			}
		}
	}
	/**
	 * 为excel表中循环添加数据
	 * @param sheet
	 * @param map  字段名
	 * @param objs	查询的数据
	 * @param clazz 无用
	 */
	private static void createTableRows(
			XSSFSheet sheet, 
			Map<Integer,List<ExcelBean>> map, 
			List<Map<String,Object>> objs, 
			Class<?> clazz) 
			throws IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		int rowindex = map.size();
		int maxkey = 0;
		List<ExcelBean> ems = new ArrayList<>();
		for(Map.Entry<Integer,List<ExcelBean>> entry : map.entrySet()){
			if(entry.getKey() > maxkey){
				maxkey = entry.getKey();
			}
		}
		ems = map.get(maxkey);
		List<Integer> widths = new ArrayList<Integer>(ems.size());
		for(Map<String,Object> obj : objs){
			XSSFRow row = sheet.createRow(rowindex);
			for(int i=0;i<ems.size();i++){
				  ExcelBean em = (ExcelBean)ems.get(i);
				  String propertyName = em.getPropertyName();
				  Object value = obj.get(propertyName);
				  XSSFCell cell = row.createCell(i);
				  String cellValue = "";
				  if("valid".equals(propertyName)){
					  cellValue = value.equals(1)?"启用":"禁用";
				  }else if(value==null){
					  cellValue = "";
				  }else if(value instanceof Date){
					  cellValue = new SimpleDateFormat("yyyy-MM-dd").format(value);
				  }else{
					  cellValue = value.toString();
				  }
				  cell.setCellValue(cellValue);
				  cell.setCellType(XSSFCell.CELL_TYPE_STRING);
				  cell.setCellStyle(fontStyle2);
				  sheet.autoSizeColumn(i);
			}
			rowindex++;
		}
		
		//设置列宽
		for(int index=0;index<widths.size();index++){
			Integer width = widths.get(index);
			width = width<2500?2500:width+300;
			width = width>10000?10000+300:width+300;
			sheet.setColumnWidth(index, width);
		}
	}
}

3.Excel实体类

/* Excel实体类 */
public class ExcelBean {
	private String headTextName; //列头（标题）名  
    private String propertyName; //对应字段名  
    private Integer cols; //合并单元格数  
    private XSSFCellStyle cellStyle;
    
	public ExcelBean() {}
	
	public ExcelBean(String headTextName, String propertyName, Integer cols) {
		super();
		this.headTextName = headTextName;
		this.propertyName = propertyName;
		this.cols = cols;
	}

	public String getHeadTextName() {
		return headTextName;
	}
	public void setHeadTextName(String headTextName) {
		this.headTextName = headTextName;
	}
	public String getPropertyName() {
		return propertyName;
	}
	public void setPropertyName(String propertyName) {
		this.propertyName = propertyName;
	}
	public Integer getCols() {
		return cols;
	}
	public void setCols(Integer cols) {
		this.cols = cols;
	}
	public XSSFCellStyle getCellStyle() {
		return cellStyle;
	}
	public void setCellStyle(XSSFCellStyle cellStyle) {
		this.cellStyle = cellStyle;
	}
	@Override
	public String toString() {
		return "ExcelBean [headTextName=" + headTextName + ", propertyName=" + propertyName + ", cols=" + cols
				+ ", cellStyle=" + cellStyle + "]";
	}
}

4.service层

public interface SysExcelInfo {
	XSSFWorkbook exportExcelInfo() throws Exception;
} //注意类型(XSSFWorkbook )

5.Dao层

public interface SysExcelDao {
	List<Map<String,Object>> findUserObject();
}

5.mapper文件SQL语句

<!-- 给予接口方式定义映射文件的规则:
 		1.映射文件命名空间与接口类全名(权限定名)相同
 		2.映射文件中的元素与接口方法对应(元素id和方法名相同)
  -->
<mapper namespace="com.jt.sys.dao.SysExcelDao">
	<select id="findUserObject" resultType="map">
		select u.*,d.name deptName
		from sys_users u
		left join sys_depts d
		on u.deptId=d.id
	</select>
</mapper>

6.实现类

@Service
public class SysExportExcelInfo implements SysExcelInfo{
	@Autowired
	private SysExcelDao sysExcelDao;
	@Override
	public XSSFWorkbook exportExcelInfo() throws Exception{
		//根据条件查询数据
		List<Map<String,Object>> list = sysExcelDao.findUserObject();
		//System.out.println(list);
		List<ExcelBean> excel = new ArrayList<>();
		Map<Integer,List<ExcelBean>> map = new LinkedHashMap<>();
		//设置标题栏
		excel.add(new ExcelBean("序号","id",0));
		excel.add(new ExcelBean("用户名","username",0));
		excel.add(new ExcelBean("部门", "deptName", 0));
		excel.add(new ExcelBean("邮箱","email",0));
		excel.add(new ExcelBean("电话","mobile",0));
		excel.add(new ExcelBean("状态","valid", 0));
		excel.add(new ExcelBean("创建时间","createdTime",0));
		excel.add(new ExcelBean("修改时间","modifiedTime",0));
		map.put(0,excel);
		String sheetName = "用户信息表";
		//调用ExcelUtil方法
		XSSFWorkbook xssfWorkbook = ExcelUtil.createExcelFile(SysUser.class, list, map, sheetName);
		System.out.println(xssfWorkbook);
		return xssfWorkbook;
	}
}

7.controller层

@Controller
@RequestMapping("/")
public class ExportExcel {
	@Autowired
	private SysExcelInfo sysExcelInfo;
	@RequestMapping("export")
	@ResponseBody
	public void export(HttpServletRequest request,HttpServletResponse response) throws Exception{
	        response.reset(); //清除buffer缓存  
	        //Map<String,Object> map=new HashMap<String,Object>();  
	        // 指定下载的文件名  
	        response.setContentType("application/vnd.ms-excel;charset=UTF-8");  
	        response.setHeader("Content-Disposition","attachment;filename="+new String("用户表.xlsx".getBytes(),"iso-8859-1"));
	        //导出Excel对象  
	        XSSFWorkbook workbook = sysExcelInfo.exportExcelInfo();
	        OutputStream output;  
	        try {  
	            output = response.getOutputStream();  
	            BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);  
	            bufferedOutput.flush();  
	            workbook.write(bufferedOutput);  
	            bufferedOutput.close();  
	        } catch (IOException e) {  
	            e.printStackTrace();  
	        }
	}
}

8.还是用HTML(JSP页面)

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>POI导出Excel表</title>
	</head>
	<body>
		<div class="input-group-btn" style="text-align: center;">
			<button type="button" class="btn btn-import btn-derive">导出信息</button>
			<button type="button" class="btn btn-export btn-pdf">导出Pdf</button>
		</div>
		<script type="text/javascript">
			$(document).ready(function(){
				$(".input-group-btn")
				.on("click",".btn-derive",doImprotObject) //导出Excel
				.on("click",".btn-pdf",doImportPdf); //导出PDF
		});
			
		function doImportPdf(){
			location.href="pdf/exportPdf.do";
		}

		function doImprotObject(){
			location.href="http://localhost:8080/CGB-JT-SYS-V1.01.01/export.do";
		}
		</script>
	</body>
</html>

然后就没了,看效果

PDF不想写了,下载源码看吧...

源码资源

个人用...