SSM的 excel导出（多sheet对应不同数据库表数据查询）

一键导出多sheet的excel

记录 基于项目需求，实现一键导出多数据库表中的数据到一个excel中，用多sheet存放不同表数据。
代码中导出的excel文件存放9个sheet

前端JSP页面

<a class="btm3" href="javascript:expdata1()">省份报表导出</a>

前端JS页面

var _controller = contextPath + '/plugins/Tsfpowerratio/lowcostinfo';
var _export2 = _controller + '/export2.ilf';
function expdata1() {
   
	var conditions = getSearchConditions();
					var download_file = $("#download_file");
					if (download_file.length == 0) {
   
						var i = '<iframe id="download_file" name="download_file"></iframe>';
						$("body").append(i);
						download_file = $("#download_file");
					}
					 
					var fileName = "省份报表.xls";
					var filetype = "xlsx";
					var url = _export2 + "?filename=" + fileName + "&filetype=" + filetype
							+ "&conditions=" + conditions;
					
					download_file.attr("src", encodeURI(url));
					download_file.css("display", "none");

};

Controller层

@RequestMapping(value = "/export2.ilf")
	@ResponseBody
	public void export2(HttpServletRequest request, HttpServletResponse response) throws Exception {
   
		String realName = request.getParameter("filename");
		String filetype = request.getParameter("filetype");
		String conditions = request.getParameter("conditions");
		Map<String, String> c = DataTableCondition.getCondition(conditions);
		String filePath = lowcostinfoService.exportByCondition3(filetype, c);
		FileOperateUtil.download(request, response, filePath, realName);
	}

Service层

public String exportByCondition3(String filetype, Map<String, String> c);

ServiceImpl层

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.jws.WebService;

import org.apache.ibatis.session.RowBounds;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Override
	public String exportByCondition3(String filetype, Map<String, String> c) {
   
		SXSSFWorkbook wb = new SXSSFWorkbook();  
		String filePath = "";
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        Sheet sheet = null;
        sheet = wb.createSheet("转供电比例");  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short 
        Row row = null;
        row = sheet.createRow((int) 0); 
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        CellStyle style = null;
        style = wb.createCellStyle(); 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        Cell cell = null;
        cell = row.createCell(0); 
        cell.setCellValue("地市");  
        cell.setCellStyle(style);  
        cell = row.createCell(1);
        cell.setCellValue("自有总站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(2);
        cell.setCellValue("自有转供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(3);
        cell.setCellValue("自有转供电占比");  
        cell.setCellStyle(style); 
        cell = row.createCell(4);
        cell.setCellValue("自有转供电平均单价");  
        cell.setCellStyle(style);  
        cell = row.createCell(5);
        cell.setCellValue("自有直供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(6);
        cell.setCellValue("自有直供电占比");  
        cell.setCellStyle(style);  
        cell = row.createCell(7);
        cell.setCellValue("自有直供电平均单价");  
        cell.setCellStyle(style);  
        cell = row.createCell(8);
        cell.setCellValue("铁塔总站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(9);
        cell.setCellValue("铁塔转供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(10);
        cell.setCellValue("铁塔转供电占比");  
        cell.setCellStyle(style);  
        cell = row.createCell(11);
        cell.setCellValue("铁塔转供电平均单价");  
        cell.setCellStyle(style);  
        cell = row.createCell(12);
        cell.setCellValue("铁塔直供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(13);
        cell.setCellValue("铁塔直供电占比");  
        cell.setCellStyle(style);  
        cell = row.createCell(14);
        cell.setCellValue("铁塔直供电平均单价");  
        cell.setCellStyle(style);  
        cell = row.createCell(15);
        cell.setCellValue("小计总站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(16);
        cell.setCellValue("小计转供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(17);
        cell.setCellValue("小计转供电占比");  
        cell.setCellStyle(style);  
        cell = row.createCell(18);
        cell.setCellValue("小计转供电平均单价");  
        cell.setCellStyle(style);  
        cell = row.createCell(19);
        cell.setCellValue("小计直供电站房数");  
        cell.setCellStyle(style);  
        cell = row.createCell(20);
        cell.setCellValue("小计直供电占比");  
        cell.setCellStyle(style);  
        cell = row.createCell(21);
        cell.setCellValue("小计直供电平均单价");  
        cell.setCellStyle(style);  
        
       
        //创建sheet2
        Sheet sheet2 = null;
        sheet2 = wb.createSheet("自动计提率明细");  
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short 
        Row row2 = null;
        row2 = sheet2.createRow((int) 0); 
        // 创建单元格，并设置值表头 设置表头居中  
        CellStyle style2 = null;
        style2 = wb.createCellStyle(); 
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        Cell cell2 = null;
        cell2 = row2.createCell(0); 
        cell2.setCellValue("地市");  
        cell2.setCellStyle(style);  
        cell2 = row2.createCell(1);
        cell2.setCellValue("自动计提率");  
        cell2.setCellStyle(style);
        
       //创建sheet3
        Sheet sheet3 = null;
        sheet3 = wb.createSheet("计提支付进度");  
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short 
        Row row3 = null;
        row3 = sheet3.createRow((int) 0); 
        // 创建单元格，并设置值表头 设置表头居中  
        CellStyle style3 = null;
        style3 =