excel上传和下载

　需要注意的地方:

　　　　1.js构造表单并提交

　　　　2.js中文传参encodeURI(encodeURI("中文")),action接收并转换value = URLDecoder.decode(value,"utf-8");

　　　　3.excel2003和excel2007以上版本,java代码有所区别

　　　　4.excel边框,样式,字体,颜色等设置;excel单元格的合并

　　　　5.excel文件的下载

 

1.excel下载

　　1.1 js构造excel下载表单,设置查询参数

/* charset:UTF-8 */
$(document).ready(function() {
 
    //添加form,用于下载excel文件
    $("body").append(    '<div class="form_container" style="display:none;">'+
                            '<form id="download_excel_form" action="" method="post">'+
                                '<input type="text name="nessesary" value="">'+
                                '<input type="submit" name="submit" id="form_container_submit">'+
                            '</form>'+
                        '</div>'
    );
    
    
});

//重写方法:查询页面,根据查询条件,导出个人信息(完整信息)
function doExpExcel(){
    //查询条件16个
    //name,gender,birth,fhighestDegree全日制,nhighestAcademic非全日制最高学位,whetherPost是否在岗,
    //stationId单位,note是否审核,note1是否委派,currentPosition现任职务
    //accountingQualification会计专业技术资格,technicalPosition会计专业技术职务,administrativeLevel行政级别
    //appointmentTime现任职务时间,unitClassification单位分类,jobPosition现从事会计工作岗位
    var action = "/cwgl/cwgl/cwglpersonnel/CwglPersonnel.do?method=exportXlsx";
    //获取查询参数
    //单位名称
    action = getSelectedValue("stationId", action);
    //姓名
    action += "&name="+$("#name").val();
    //性别
    action = getSelectedValue("gender", action);
    //出生日期
    action += "&birth="+$("#birth").val();
    action = getSelectedValue("administrativeLevel", action);
    action = getSelectedValue("fhighestDegree", action);
    action = getSelectedValue("nhighestDegree", action);
    action = getSelectedValue("whetherPost", action);
    action = getSelectedValue("accountingQualification", action);
    action = getSelectedValue("technicalPosition", action);
    action += "&appointmentTime="+$("#appointmentTime").val();//现任职务时间
    action = getSelectedValue("note1", action);
    action = getSelectedValue("note", action);
    action = getSelectedValue("unitClassification", action);
    action = getSelectedValue("jobPosition", action);
    console.log(action);
    var form = $("#download_excel_form");
    form.attr("action",encodeURI(encodeURI(action)));
//    $("#download_excel_form").submit();
    $("#form_container_submit").click();
}

//下拉列表所选值
function getSelectedValue(id,action){
    var _txt = $("#"+id+"_dd_text").val();
    $("#"+id+" option").each(function(){
        if($(this).text()==_txt){
            action += "&"+id+"="+$(this).val();
        }
    });
    return action;
}

  1.2 action 获取查询参数,查询数据,poi生成excel,下载excel

//导出数据为Xlsx文件
    public void exportXlsx(ActionMapping mapping, ActionForm form, HttpServletRequest request,
            HttpServletResponse response) throws UnsupportedEncodingException{
        
        ////79个字段
        String[] head = new String[]{
                "从业资格档案号码","姓名","民族","身份证号","性别","出生日期","政治面貌","全日制最高学历","全日制最高学历毕业学校","全日制最高学历毕业时间",
                "全日制最高学历所学专业","全日制最高学位","非全日制最高学历","非全日制学历毕业学校","非全日制学历毕业时间","非全日制学历所学专业","非全日制最高学位","非全日制最高学位毕业学校","非全日制最高学位毕业时间","非全日制最高学位所学专业",
                "单位代码","单位名称","具体下属单位","工作单位经济类型","单位分类","工作单位电话","单位地址","单位邮政编码","会计行政职务","行政级别",
                "行政级别任职命令号","现任职务","参加工作时间","从事会计工作时间","现从事会计工作岗位","珠算等级","珠算证号","珠算证取得时间","电算级别","电算证号",
                "电算证取得时间","继续教育成绩","本年学时","继续教育开始时间","首次从业资格证发证机关","会计从业资格证取得方式","资格证最初取得时间","发证单位","从业资格证发证日期","注册时间",
                "是否在岗","会计专业技术资格","会计专业技术资格取得方式","会计专业技术资格取得时间","会计专业技术资格证号或批文号","会计专业技术职务","会计专业技术职务聘任时间","会计专业技术职务任职命令号","非会计专业技术资格级别","非会计专业技术资格类型",
                "非会计专业技术资格取得时间","非会计专业技术资格证书号或批文号","非会计专业技术资格取得方式","注册会计师","资产评估师","注册税务师","其他资格","IC卡号","诚信记录档案","有效证件名称",
                "有效证件号","籍贯","出生地","家庭住址","联系电话","电子邮箱","外语掌握情况","是否审核","是否委派"};
        
        //查询条件16个
        //name,gender,birth,fhighestDegree全日制,nhighestAcademic非全日制最高学位,whetherPost是否在岗,
        //stationId单位,note是否审核,note1是否委派,currentPosition现任职务
        //accountingQualification会计专业技术资格,technicalPosition会计专业技术职务,administrativeLevel行政级别
        //appointmentTime现任职务时间,unitClassification单位分类,jobPosition现从事会计工作岗位
        LinkedList<String> names = new LinkedList<String>();
        LinkedList<String> values = new LinkedList<String>();
        
        @SuppressWarnings("unchecked")
        Enumeration<String> nameEnum = request.getParameterNames();
        while (nameEnum.hasMoreElements()) {
            String name = (String) nameEnum.nextElement();
            String value = request.getParameter(name);
            if (value!=null&&!"".equals(value.trim())&&!"exportXlsx".equals(value)&&!"提交查询".equals(value)) {
                Pattern pattern = Pattern.compile("([A-Z])");
                Matcher matcher = pattern.matcher(name);
                while(matcher.find()){
                    if(matcher.groupCount()>0){
                        name = matcher.replaceAll("_"+matcher.group(1).toLowerCase());
                    }
                }
                names.add(name);
                value = URLDecoder.decode(value,"utf-8");
                values.add(value);
            }
        }
        CwglPersonnelManager manager = ((CwglPersonnelManager) getEntityManager());
        List<Object[]> datas = manager.getAllData(names,values);
    //1.将数据转换为excel
        short validColNum = 79;
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        
        //row:表名
        XSSFRow titlerow = sheet.createRow(0);
        XSSFCell titlecell = titlerow.createCell(0,XSSFCell.CELL_TYPE_STRING);
        titlecell.setCellValue("人员信息");//表名,表标题
            //标题样式
        XSSFCellStyle style = getNewCenterStyle(workbook,Color.gray,"title");
        CellRangeAddress region = new CellRangeAddress(0,(short)0,0,validColNum-1);//设置合并的行列
        
        sheet.addMergedRegion(region);//将单元格合并
        setRegionStyle(sheet,region,style);//设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
//        setRegionBorder(XSSFCellStyle.BORDER_DASHED, region, sheet, workbook);
            //表头样式
        setAllRangeStyle(sheet,style);
        style = getNewCenterStyle(workbook, Color.LIGHT_GRAY, "header");
        //row:表头,
        XSSFRow headrow = sheet.createRow(1);
        for (int i = 0; i < head.length; i++) {
            XSSFCell headcell = headrow.createCell(i,XSSFCell.CELL_TYPE_STRING);
            headcell.setCellValue(head[i]);
            headcell.setCellStyle(style);
        }
            //内容数据样式
        style = getNewCenterStyle(workbook, new Color(192, 192, 192),"body");;
        //row:内容
        for (int i = 0; i < datas.size(); i++) {
            XSSFRow row = sheet.createRow(i+2);
            Object[] rowdata =  datas.get(i);//.values().toArray();
            for (int j = 0; j < validColNum; j++) {
                XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
                cell.setCellStyle(style);
            }
        }
        
        try  
        {   
            //保存excel到磁盘
            @SuppressWarnings("deprecation")
            String directory = request.getRealPath("/");
            File file = new File(directory+"\\cwgl\\module\\cwglpersonnel\\人员信息.xlsx");
            System.out.println(file.getAbsolutePath());
            FileOutputStream fout = new FileOutputStream(file);
            workbook.write(fout);  
            fout.close();  
            
            //下载excel
            downLoadFile(request,response, file.getAbsolutePath(), "xlsx");
        }catch (Exception e)  {  
            e.printStackTrace();  
        }  
    }
    
    
    /**
     * 获取excel边框,样式
     * @param workBook    
     * @param bgColor    背景色
     * @param type    类型:title标题,Header表头,body内容
     * @return
     */
    
    private static XSSFCellStyle getNewCenterStyle(XSSFWorkbook workBook,Color bgColor,String type){
          XSSFCellStyle style = workBook.createCellStyle();;
          style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
          style.setWrapText(true);
          style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
          style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
          style.setBorderRight(XSSFCellStyle.BORDER_THIN);
          style.setBorderTop(XSSFCellStyle.BORDER_THIN);
          style.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
          //字体
          XSSFFont font = workBook.createFont();   
          if ("title".equals(type)) {
              font.setFontName("黑体");
              font.setColor(new XSSFColor(Color.green));//字体颜色
              font.setFontHeight(60);
              font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示    
              font.setFontHeightInPoints((short) 22); 
          }else if ("header".equals(type)) {
              font.setFontName("宋体");
              font.setColor(new XSSFColor(Color.red));
              font.setFontHeightInPoints((short) 12); 
              //设置单元格边框颜色
              XSSFColor borderColor = new XSSFColor(Color.red);
              style.setTopBorderColor(borderColor);
              style.setBottomBorderColor(borderColor);
              style.setLeftBorderColor(borderColor);
              style.setRightBorderColor(borderColor);
              //设置单元格背景色
              style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
              style.setFillBackgroundColor(new XSSFColor(bgColor));// 设置背景色,好像不起作用
              style.setFillForegroundColor(new XSSFColor(bgColor));// 设置前景色
          }else if ("body".equals(type)) {
              font.setFontName("仿宋_GB2312");    
              font.setFontHeightInPoints((short) 9);   //字体大小
          }
          style.setFont(font);
      return style;
     }
    
    /**
     * 设置合并区域样式
     * 设置excel边框,样式
     * @param sheet    sheet
     * @param range    合并区域
     * @param cs    样式
     */
    private void setRegionStyle(XSSFSheet sheet, CellRangeAddress range , XSSFCellStyle cs) {
        for (int i = range.getFirstRow(); i <= range.getLastRow(); i ++) {
            XSSFRow row = sheet.getRow(i);
            if(range.getFirstColumn()!=range.getLastColumn()){
                for (int j = range.getFirstColumn(); j <= range.getLastColumn(); j++) {
                    XSSFCell cell = row.getCell((short)j);
                    if( cell==null){  
                        cell=row.createCell(j);  
                        cell.setCellValue("");  
                    } 
                    cell.setCellStyle(cs);
                }
            }
        }
    }
    
    /**
     * 一次性设置所有合并区域
     * @param sheet        工作薄
     * @param style        样式
     */
    private void setAllRangeStyle(XSSFSheet sheet , XSSFCellStyle style){
        int num = sheet.getNumMergedRegions();
        for (int i = 0; i < num; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            setRegionStyle(sheet, range, style);
        }
    }
    
    /**
     * excel 下载
     * @param response            response
     * @param fileFullName        文件名包括路径
     * @param fileType            文件类型:pdf,xls或xlsx,doc或docx
     * @return
     * @throws Exception
     */
    public static boolean downLoadFile(HttpServletRequest request,HttpServletResponse response, String fileFullName, String fileType)
            throws Exception {
            File file = new File(fileFullName);  //根据文件路径获得File文件
            //设置文件类型(这样设置就不止是下Excel文件了，一举多得)
            if("pdf".equals(fileType)){
               response.setContentType("application/pdf;charset=GBK");
            }else if("xls".equals(fileType)||"xlsx".equals(fileType)){
               response.setContentType("application/msexcel;charset=GBK");
            }else if("doc".equals(fileType)||"docx".equals(fileType)){
               response.setContentType("application/msword;charset=GBK");
            }

            //文件名
            String fileName = fileFullName.substring(fileFullName.lastIndexOf("\\"));
            String userAgent = request.getHeader("User-Agent");
            //针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE")||userAgent.contains("Trident")) {
              fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
              //非IE浏览器的处理：
              fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
            }
            response.setHeader("Content-Disposition", "attachment;filename=\""+ fileName + "\"");
            response.setContentLength((int) file.length());
            BufferedOutputStream output = null;
            BufferedInputStream input = null;
            try {
              output = new BufferedOutputStream(response.getOutputStream());
              InputStream fis = new BufferedInputStream(new FileInputStream(file));  
              byte[] buffer = new byte[fis.available()];  
              fis.read(buffer);  
              fis.close();
              output.write(buffer);
              output.flush();   //不可少
              output.close();
              response.flushBuffer();//不可少
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
               //关闭流，不可少
               if (input != null)
                    input.close();
               if (output != null)
                    output.close();
            }

           return false;
        }
    /**
     * 利用poi自带工具RegionUtil来设置边框
     * @param border
     * @param region
     * @param sheet
     * @param wb
     */
    private static void setRegionBorder(int border, CellRangeAddress region, Sheet sheet,Workbook 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);  
      
    }
}

 

　　　　1.3 manager 数据接口

/**
	 * 查询所有字段,根据参数
	 * @param names		names条件字段
	 * @param values	values条件值
	 * @return
	 */
	public List<Object[]> getAllData(LinkedList<String> names,LinkedList<String> values){
		StringBuffer sb = new StringBuffer();
		//79个字段
		sb.append(" select "+
					"t.file_num,t.name,t.nation,t.id_num,t.gender,t.birth,t.political_outlook,t.fhighest_degree,t.fgraduation_school,t.fgraduation_time,"+
					"t.fschool_major,t.fhighest_academic,t.nhighest_degree,t.ngraduation_school,t.ngraduation_time,t.nschool_major,t.nhighest_academic,t.nzgraduation_school,t.nzgraduation_time,t.nzschool_major,"+
					"t.station_id,t.station_name,t.subordinate_unit,t.economic_type,t.unit_classification,t.telephone,t.address,t.post,t.administrative,t.administrative_level,"+
					"t.command_number,t.current_position,t.work_time,t.accounting_time,t.job_position,t.abacus_level,t.abacus_no,t.abacus_time,t.power_level,t.power_num,"+
					"t.power_time,t.continuing_education,t.hours_year,t.continuing_time,t.issuing_authority,t.get_way,t.get_time,t.issuing_unit,t.issuing_time,t.registration_time,"+
					"t.whether_post,t.accounting_qualification,t.qualification_way,t.qualification_time,t.qualification_num,t.technical_position,t.appointment_time,t.post_number,t.nqualification_level,t.nqualification_type,"+
					"t.nqualification_gettime,t.npermit_no,t.nqualification_way,t.registered,t.assessment_division,t.tax_division,t.other_qualifications,t.ic,t.integrity_record,t.certificate_name,"+
					"t.certificate_num,t.place_origin,t.place_birth,t.home_address,t.personal_phone,t.electronic_mail,t.foreign_language,t.note,t.note1 "+
					"from CWGL_PERSONNEL t "+
					"where 1=1 "
			);
		for (int i = 0; i < names.size(); i++) {
			String name = names.get(i);
			if ("birth".equals(name)||"appointmentTime".equals(name)) {
				sb.append(" and to_char(to_date("+name+",'yyyy-MM-dd'),'yyyy-MM') = ").append("'"+ values.get(i) +"'");
			}else{
				sb.append(" and "+name+" = '").append(values.get(i)).append("'");
			}
		}
		String wheresql = sb.toString();
		Session session = getExtDao().openSession();
		Query query = session.createSQLQuery(wheresql);
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.list();
		return list;
	}

 

 

2.excel上传和下载完整代码　　

　　先上图:看着效果挺不错^_^

　　

import java.awt.Color;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletResponse;

import org.apache.catalina.tribes.util.Arrays;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import cn.com.jtv.kaanalysis.module.dzdynamicexcel.entity.DzDynamicExcel;
import cn.com.jtv.kaanalysis.module.dzdynamicexcel.manager.DzDynamicExcelManager;
import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.entity.DzDynamicExcelSampleinfo;
import cn.com.jtv.kaanalysis.module.dzdynamicexcelsampleinfo.manager.DzDynamicExcelSampleinfoManager;
import cn.com.jtv.kaanalysis.module.dzdynamicrule.entity.DzDynamicRule;
import cn.com.jtv.kaanalysis.module.dzdynamicrule.manager.DzDynamicRuleManager;
import cn.com.jtv.mf.core.utils.UtilDate;
import cn.com.jtv.mf.core.utils.json.UtilJson;
import cn.com.jtv.mf.core.web.LoginUserHolder;
import cn.com.jtv.mf.core.web.mvc.BaseEntityJsonAction;
/**
 * 动态表格简单信息管理控制器.
 * <p>
 * 
 * @version 2016-08-25 
 * @author wanghj 
 */
public class DzDynamicExcelSampleinfoAction extends
        BaseEntityJsonAction<DzDynamicExcelSampleinfo> {
    
    //DzDynamicExcel
    private DzDynamicExcelManager dEntityManager;

    public DzDynamicExcelManager getdEntityManager() {
        return dEntityManager;
    }
    public void setdEntityManager(DzDynamicExcelManager dEntityManager) {
        this.dEntityManager = dEntityManager;
    }
    
    //DzDynamicRule
    private DzDynamicRuleManager rEntityManager;
    
    public DzDynamicRuleManager getrEntityManager() {
        return rEntityManager;
    }
    public void setrEntityManager(DzDynamicRuleManager rEntityManager) {
        this.rEntityManager = rEntityManager;
    }
    
    //表头规则排序字段
    Integer sortNum = 1;
    
    
    
    
    
    /**
     * 导入Excel 2003.
     */
    public String importXls() throws Exception {
        request.setCharacterEncoding("UTF-8");
        Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
        // 获得excel文件
        // 解析器解析request的上下文
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                request.getSession().getServletContext());
        // 先判断request中是否包涵multipart类型的数据，
        if (multipartResolver.isMultipart(request)) {
            // 再将request中的数据转化成multipart类型的数据
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
            Iterator<String> iter = multiRequest.getFileNames();
            while (iter.hasNext()) {
                MultipartFile file = multiRequest.getFile(iter.next());
                if (file != null) {
                    maps = parseAndSaveExcel2003(file);
                    request.setAttribute("fileName",file.getOriginalFilename());
                    request.setAttribute("maps",maps);
                }
            }
        }
        return "success";
    }

    //转换excel并保存其中数据到数据库
    public Map<String, Map<String, String>> parseAndSaveExcel2003(MultipartFile file) {
        
        Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
        DzDynamicExcelSampleinfo sampleInfo = null;
        try {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            // 新建WorkBook
            HSSFWorkbook wb = new HSSFWorkbook(pois);
            // 获取Sheet（工作薄）总个数
            int sheetNumber = wb.getNumberOfSheets();
            
            for (int i = 0; i < sheetNumber; i++) {
                
                // 获取Sheet（工作薄）
                HSSFSheet sheet = wb.getSheetAt(i);
                // 开始行数
                int firstRow = sheet.getFirstRowNum();
                // 结束行数
                int lastRow = sheet.getLastRowNum();
                // 判断该Sheet（工作薄)是否为空
                boolean isEmpty = false;
                if (firstRow == lastRow) {
                    isEmpty = true;
                }

                if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库
                    Map<String, String> map = new HashMap<String, String>();
                    int totalRownum = lastRow;    //总行数
                    int importRownum = 0;    //成功导入行数
//                    int failedRownum = 0;    //失败行数
                    ArrayList<Integer> errorList = new ArrayList<Integer>();
                    // 获得表头固定数据
                    String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
                            //创建时间,一张表中,表头所包含的每一行的创建时间是相同的
                    String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
                    String table_id = UUID.randomUUID().toString();
                    String create_person = LoginUserHolder.getLoginUserId();//创建人id
                    String table_name = "";                //每张表的第一行内容为表名
                    String isheader = "1";
                    String logic_del = "0";
                    String id = "";                        //UUID,每条数据的id,主键
                    
                    //获取数据
                    List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
                    DzDynamicExcel entity = null;
                    boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
                    Integer seriesNumber = 0;    //需要,用于排序
                    List<String[]> tempExcels = new ArrayList<String[]>();
                    List<String[]> rulesData = new ArrayList<String[]>();
                    
                    for (int j = firstRow; j <= lastRow; j++) {
                        
                        // 获取一行
                        HSSFRow row = sheet.getRow(j);
                        // 开始列数
                        int firstCell = row.getFirstCellNum();
                        // 结束列数
                        int lastCell = row.getLastCellNum();
                        // 判断该行是否为空
                        String[] value = new String[lastCell];
                        if (firstCell != lastCell) {
                            for (int k = firstCell; k < lastCell; k++) {
                                // 获取一个单元格
                                HSSFCell cell = row.getCell(k);
                                value[k] = strFromCell(cell);
                            }
                            //是否是表头终止行
                            String checkStr = value[0]+value[1];
                            if("01".equals(checkStr)||"null1".equals(checkStr)) {
                                willExist= true;
                            }else{
                                tempExcels.add(value);
                                id = UUID.randomUUID().toString();
                                if(j==firstRow){//第一行是标题
                                    table_name = strFromArr(value);
                                    //删除数据库中相同的表
                                    List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
                                    getEntityManager().delete(sampleinfos);
                                    int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
                                    if(0!=count){
                                        break;//删除失败
                                    }
                                }else{
                                    rulesData.add(value);//表头规则数据
                                    entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
                                    if(entity!=null){//导入成功
                                        list.add(entity);
                                        importRownum++;
                                    }/*else {//导入失败
                                        errorList.add(j+1);
                                        failedRownum++;
                                    }*/
                                }
                            }
                            
                        }
                        if(willExist) break;
                    }
                    
                    //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
                        //一个工作薄一条记录一张表,保存多个工作薄
                    sampleInfo = new DzDynamicExcelSampleinfo(    table_id,
                                                                dwmcid,
                                                                table_id, 
                                                                table_name,
                                                                create_time,
                                                                create_person,
                                                                String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)),    //有效列数
                                                                importRownum+"");                    //有效行数
                    ((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo);
                    
                    //保存DzDynamicExcel
                    getdEntityManager().save(list);
                    //添加表头合并规则
                    int index = rulesData.size()-1;//2:例如0,1两行数据,
                    if (index>=0) {
                        List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
                        //表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
                        for (int l =index-1 ; l >=0 ; l--) {
                            String[] row = (String[]) rulesData.get(l);
                            String[] last_row = null;//上一行数据
                            
                            last_row = (String[]) rulesData.get(l+1);
                            
                            List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
                            if(subRules!=null){
                                rules.addAll(subRules);
                            }
                        }
                        if(rules!=null){
                            System.out.println(UtilJson.toJson(rules));
                            ((DzDynamicRuleManager)getrEntityManager()).save(rules);
                        }
                        int successRownum = importRownum==0?0:(importRownum+2);
                        map.put("totalRownum", (totalRownum+1)+"");
                        map.put("importRownum", successRownum+"");
                        map.put("failedRownum", (totalRownum+1-successRownum)+"");
                        map.put("errorList", Arrays.toString(errorList.toArray()));
                        
                        maps.put("sheet"+i, map);
                    }else{
                        System.out.println("导入失败");;
                    }
                    
                    

                    
                }//--if
                
            }

            
        } catch (IOException e) {
            e.printStackTrace();
        }
        return maps;
    }
    
    //将cell中不同类型的值全部转换为String类型
    private String strFromCell(HSSFCell cell) {
        String str = "";
        if (cell != null) {
            // 获取单元格，值的类型
            int cellType = cell.getCellType();

            if (cellType == 0) {
                Object cellNumber = cell
                        .getNumericCellValue();
                str = new DecimalFormat("#.##")
                        .format(cellNumber);
            } else if (cellType == 1) {
                str = cell.getStringCellValue() + "";
                // }else if(cellType == 2){
            } else if (cellType == 4) {
                str = (cell.getBooleanCellValue()) + "";
            } else {
                str = "";
            }
        }
        return str;
    }
    //将cell中不同类型的值全部转换为String类型
    private String strFromXssfCell(XSSFCell cell) {
        String str = "";
        if (cell != null) {
            // 获取单元格，值的类型
            int cellType = cell.getCellType();

            if (cellType == 0) {
                Object cellNumber = cell
                        .getNumericCellValue();
                str = new DecimalFormat("#.##")
                        .format(cellNumber);
            } else if (cellType == 1) {
                str = cell.getStringCellValue() + "";
                // }else if(cellType == 2){
            } else if (cellType == 4) {
                str = (cell.getBooleanCellValue()) + "";
            } else {
                str = "";
            }
        }
        return str;
    }

    
    
    /**
     * 导入Excel 2007及以上版本.
     */
    public String importXlsx() throws Exception {
        Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
        // 获得前台上传的文件
        Map<String, List<MultipartFile>> files = getUploadFiles();
        MultipartFile file = files.get("fileselect").get(0);
        // 格式校验
        String name = file.getOriginalFilename();
        if (!name.endsWith(".xlsx")) {
            throw new RuntimeException("目前仅支持 xlsx 格式的文件");
        }
        // 导入数据
        InputStream is = file.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        try {
            maps = parseAndSaveExcel2007(workbook);
            request.setAttribute("fileName",file.getOriginalFilename());
            request.setAttribute("maps",maps);
        } finally {
            try {
                workbook.close();
            } catch (Exception ex) {
            }
        }
        return "success";
    }
    
    private Map<String, Map<String, String>> parseAndSaveExcel2007(XSSFWorkbook workbook) {
        
        Map<String, Map<String, String>> maps = new HashMap<String, Map<String,String>>();
        DzDynamicExcelSampleinfo sampleInfo = null;
        
        int sheetNum = workbook.getNumberOfSheets();
        
        //遍历sheet
        for (int x = 0; x < sheetNum; x++) {
        
            XSSFSheet sheet = workbook.getSheetAt(x);
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            if(firstRow != lastRow) {
            
                Map<String, String> map = new HashMap<String, String>();
                int totalRownum = lastRow;    //总行数
                int importRownum = 0;    //成功导入行数
        //        int failedRownum = 0;    //失败行数
                ArrayList<Integer> errorList = new ArrayList<Integer>();
                // 获得表头固定数据
                String dwmcid = LoginUserHolder.getLoginStationId();//登录这所在单位id,
                        //创建时间,一张表中,表头所包含的每一行的创建时间是相同的
                String create_time = UtilDate.dateToString(Calendar.getInstance().getTime(),"yyy-MM-dd HH:mm:ss");
                String table_id = UUID.randomUUID().toString();
                String create_person = LoginUserHolder.getLoginUserId();//创建人id
                String table_name = "";                //每张表的第一行内容为表名
                String isheader = "1";
                String logic_del = "0";
                String id = "";                        //UUID,每条数据的id,主键
                
                //获取数据
                List<DzDynamicExcel> list = new ArrayList<DzDynamicExcel>();
                DzDynamicExcel entity = null;
                boolean willExist = false;//如果解析到某一行是0,1,2,3...则说明表头到此为止.不用再解析了
                Integer seriesNumber = 0;    //需要,用于排序
                List<String[]> tempExcels = new ArrayList<String[]>();
                List<String[]> rulesData = new ArrayList<String[]>();
                // 遍历行
                for (int i = 0; i < lastRow; i++) {
                    XSSFRow row = sheet.getRow(i);
                    int firstCellNum = row.getFirstCellNum();
                    int lastCellNum = row.getLastCellNum();
                    if (firstCellNum == lastCellNum) break;
                    
                    String[] value = new String[lastCellNum];
                    for (int j = firstCellNum; j <lastCellNum; j++) {//遍历列
                        XSSFCell cell = row.getCell(j);
                        value[j] = strFromXssfCell(cell);
                    }
                    //是否是表头终止行
                    String checkStr = value[0]+value[1];
                    if("01".equals(checkStr)||"null1".equals(checkStr)) {
                        willExist= true;
                    }else{
                        tempExcels.add(value);
                        id = UUID.randomUUID().toString();
                        if(i==firstRow){//第一行是标题
                            table_name = strFromArr(value);
                            //删除数据库中相同的表
                            List<DzDynamicExcelSampleinfo> sampleinfos = ((DzDynamicExcelSampleinfoManager)getEntityManager()).deleteByTableName(table_name);
                            getEntityManager().delete(sampleinfos);
                            int count = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(table_name);
                            if(0!=count){
                                break;//删除失败
                            }
                        }else{
                            rulesData.add(value);//表头规则数据
                            entity = new DzDynamicExcel(dwmcid, create_time, table_id, table_name, isheader, logic_del,id,value,String.valueOf(seriesNumber++));
                            if(entity!=null){//导入成功
                                list.add(entity);
                                importRownum++;
                            }/*else {//导入失败
                                errorList.add(j+1);
                                failedRownum++;
                            }*/
                        }
                    }
                    if(willExist) break;
                }//遍历行结束
                    
                    
                //先保存DzDynamicExcelSampleinfo,后保存DzDynamicExcel(因为数据库中有级联)
                    //一个工作薄一条记录一张表,保存多个工作薄
                sampleInfo = new DzDynamicExcelSampleinfo(    table_id,
                                                            dwmcid,
                                                            table_id, 
                                                            table_name,
                                                            create_time,
                                                            create_person,
                                                            String.valueOf(tempExcels.size()==0?0:getValicColumnSum(tempExcels)),    //有效列数
                                                            importRownum+"");                    //有效行数
                ((DzDynamicExcelSampleinfoManager)getEntityManager()).save(sampleInfo);
                
                //保存DzDynamicExcel
                getdEntityManager().save(list);
                //添加表头合并规则
                int index = rulesData.size()-1;//2:0,1例如两行数据,
                if (index>=0) {
                    List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
                    //表头规则倒序解析,并从倒数第二行开始(如果存在的话),并且每一个解析成功的对象都有一个排序字段(按照解析的次序)
                    for (int l =index-1 ; l >=0 ; l--) {
                        String[] row = (String[]) rulesData.get(l);
                        String[] last_row = null;//上一行数据
                        
                        last_row = (String[]) rulesData.get(l+1);
                        
                        List<DzDynamicRule> subRules = getMergeRuleToEntities(create_time, table_id,row,last_row);
                        if(subRules!=null){
                            rules.addAll(subRules);
                        }
                    }
                    if(rules!=null){
                        System.out.println(UtilJson.toJson(rules));
                        ((DzDynamicRuleManager)getrEntityManager()).save(rules);
                    }
                    int successRownum = importRownum==0?0:(importRownum+2);
                    map.put("totalRownum", (totalRownum+1)+"");
                    map.put("importRownum", successRownum+"");
                    map.put("failedRownum", (totalRownum+1-successRownum)+"");
                    map.put("errorList", Arrays.toString(errorList.toArray()));
                    
                    maps.put("sheet"+x, map);
                }else{
                    System.out.println("导入失败");;
                }
            }//--if(firstRow != lastRow) {
            
        }//--for sheet end
        
        return maps;
    }
    
    
    // 将数组转换为字符串
    public static String strFromArr(String[] arr) {
        String strs = "";
        for (String str : arr) {
            strs += (str == null ? "" : str);
        }
        return strs;
    }
    
    /**
     * 处理多余的列,多余的null列不需要
     * @param data     待处理的List
     * @return           处理过的List
     */
    public Integer getValicColumnSum(List<String[]> data){
        int len = data.size();
        int maxIndex = 0;
        if(data!=null){
            for (int j = 0; j < len; j++) {//遍历每行数据
                Object[] obj = data.get(j);
                int maxLen = obj.length<100?obj.length:100;
                for (int i = 0; i < maxLen; i++) {        //这一行从后面第一个不为null的值的位置,
                    int currIndex = obj.length-i-1;
                    if(obj[currIndex]!=null&&!"".equals(obj[currIndex])){
                        if(maxIndex<currIndex){
                            maxIndex = currIndex;
                        }
                        int rowCol = data.get(j).length-1;
                        if(maxIndex<rowCol){
                            maxIndex= rowCol;
                        }
                        break;
                    }
                }
            }
            
        }
        return maxIndex+1;
    }
    
    /**
     * 异步检查xlsx文件中的表是否已经存在
     * @throws Exception 
     */
    public void xlsxExist() throws Exception{
        // 获得前台上传的文件
        Map<String, List<MultipartFile>> files = getUploadFiles();
        MultipartFile file = files.get("fileselect").get(0);
        
        // 导入数据
        InputStream is = file.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        Map<String, String> map = new HashMap<String, String>();
        try {
            map = checkXlsx(workbook);
            String json = UtilJson.toJson(map);
            PrintWriter out = response.getWriter();
            out.print(json);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            } catch (Exception ex) {
            }
        }
    }

    private Map<String, String> checkXlsx(XSSFWorkbook workbook) {
        Map<String, String> map = new HashMap<String,String>();
        int sheetNum = workbook.getNumberOfSheets();
        //遍历sheet
        for (int x = 0; x < sheetNum; x++) {
            
            XSSFSheet sheet = workbook.getSheetAt(x);
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            if(firstRow == lastRow) break;
                
            XSSFRow row = sheet.getRow(0);
            
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            if (firstCellNum == lastCellNum) break;
            String[] value = new String[lastCellNum];
            if (firstCellNum != lastCellNum) {
                for (int k = firstCellNum; k < lastCellNum; k++) {
                    // 获取一个单元格
                    XSSFCell cell = row.getCell(k);
                    String str = "";
                    if (cell != null) {
                        // 获取单元格，值的类型
                        int cellType = cell.getCellType();

                        if (cellType == 0) {
                            Object cellNumber = cell
                                    .getNumericCellValue();
                            str = new DecimalFormat("#.##")
                                    .format(cellNumber);
                        } else if (cellType == 1) {
                            str = cell.getStringCellValue() + "";
                            // }else if(cellType == 2){
                        } else if (cellType == 4) {
                            str = (cell.getBooleanCellValue()) + "";
                        } else {
                            str = "";
                        }
                    }
                    value[k] = str;
                }
                
            }
            String tableName = strFromArr(value);
            Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
            map.put(tableName,String.valueOf(num));
        }
        return map;
    }
    
    /**
     * 异步检查Xls文件中的表是否已经给存在
     * 传给前台的是json格式的map字符串,{表名:已经存在次数}
     */
    public void xlsExist() {

        try {
            request.setCharacterEncoding("UTF-8");
            response.setCharacterEncoding("utf-8");
            Map<String, String> maps = new HashMap<String, String>();
            // 获得excel文件
            // 解析器解析request的上下文
            CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                    request.getSession().getServletContext());
            // 先判断request中是否包涵multipart类型的数据，
            if (multipartResolver.isMultipart(request)) {
                // 再将request中的数据转化成multipart类型的数据
                MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
                Iterator<String> iter = multiRequest.getFileNames();
                while (iter.hasNext()) {
                    MultipartFile file = multiRequest.getFile(iter.next());
                    if (file != null) {
                        maps = checkXls(file);
                    }
                }
            }
            String json = UtilJson.toJson(maps);
            PrintWriter out = response.getWriter();
            out.print(json);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private Map<String,String> checkXls(MultipartFile file) {
        Map<String, String> map = new HashMap<String,String>();
        try {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            // 新建WorkBook
            HSSFWorkbook wb = new HSSFWorkbook(pois);
            // 获取Sheet（工作薄）总个数
//            int sheetNumber = wb.getNumberOfSheets();

            // 获取Sheet（工作薄）
            HSSFSheet sheet = wb.getSheetAt(0);
            // 开始行数
            int firstRow = sheet.getFirstRowNum();
            // 结束行数
            int lastRow = sheet.getLastRowNum();
            // 判断该Sheet（工作薄)是否为空
            boolean isEmpty = false;
            if (firstRow == lastRow) {
                isEmpty = true;
            }

            if (!isEmpty) {// 工作薄不为空,获取其中数据,并保存到数据库

                    
        // 获取一行
                HSSFRow row = sheet.getRow(firstRow);
                // 开始列数
                int firstCell = row.getFirstCellNum();
                // 结束列数
                int lastCell = row.getLastCellNum();
                // 判断该行是否为空
                String[] value = new String[lastCell];
                if (firstCell != lastCell) {
                    for (int k = firstCell; k < lastCell; k++) {
                        // 获取一个单元格
                        HSSFCell cell = row.getCell(k);
                        value[k] = strFromCell(cell);
                    }
                    
                }
                String tableName = strFromArr(value);
                Integer num = ((DzDynamicExcelSampleinfoManager)getEntityManager()).getCountByTableName(tableName);
                map.put(tableName,String.valueOf(num));
            }
            
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }
    
    
    //根据表头的一行数据,得到合并规则
    public List<DzDynamicRule> getMergeRuleToEntities(String createTime, String tableId,String[] row,String[] last_row){
        List<DzDynamicRule> rules = new ArrayList<DzDynamicRule>();
        for (int i = 0; i < row.length; i++) {
            String groupName = row[i];
            if(!"".equals(groupName)){//空白单元格不处理
                int field_count = 1;
                for (int j = i+1; j < row.length; j++) {//判断当前单元格,后面的空白单元格数
                    if("".equals(row[j])){
                        field_count++;
                    }else{
                        break;
                    }
                }
                //完成一个单元格的判定了,并且这个单元格,是需要合并的
                if(field_count>1){
                    String[] group = new String[]{groupName,"col_"+(i+1),field_count+""};
                    DzDynamicRule rule = new DzDynamicRule(createTime,  UUID.randomUUID().toString(), tableId, sortNum++, group);
                    rules.add(rule);
                    i = i+field_count-1;
                }else if(field_count==1&&last_row.length!=0&&i<last_row.length){//如果是一个单元格,需要判断上一行同列单元格是否为空?
                    if (!"".equals(last_row[i])) {//上行同列单元格为空,记录规则
                        String[] group = new String[]{groupName,"col_"+(i+1),"1"};
                        DzDynamicRule rule = new DzDynamicRule(createTime,  UUID.randomUUID().toString(), tableId, sortNum++, group);
                        rules.add(rule);
                        i = i+field_count-1;
                    }
                }
                field_count = 1;
            }
        }
        return rules;
    }
    
    //导出数据为Xlsx文件
    public void exportXlsx(){

    //1.将数据转换为excel
        String tableId = request.getParameter("tableId");
        System.out.println(tableId);
        //获取表头,获取表头规则,获取数据
            //表信息
        List<Object[]> list = ((DzDynamicExcelSampleinfoManager) getEntityManager()).getSampleInfo(tableId);
        Object[] sampleInfo = list.get(0);//表名,有效列数,有效行数
            //获取表头,数据
        List<Object[]> datas = ((DzDynamicExcelManager)getdEntityManager()).getData(tableId);
            //获取表头规则
        List<Object[]> rules = ((DzDynamicRuleManager)getrEntityManager()).getRules(tableId);
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        
        //row:表名
        XSSFRow titlerow = sheet.createRow(0);
        XSSFCell titlecell = titlerow.createCell(0,XSSFCell.CELL_TYPE_STRING);
        titlecell.setCellValue(String.valueOf(sampleInfo[0]));//表名,表标题
            //边框,样式
        XSSFCellStyle style = getNewCenterStyle(workbook,Color.white,"title");
        CellRangeAddress region = new CellRangeAddress(0,(short)0,0,Short.valueOf(sampleInfo[1]+"")-1);//设置合并的行列
        setRegionStyle(sheet,region,style);        //设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
        sheet.addMergedRegion(region);            //将单元格合并
        
        //有效列数
        int validColumn = Integer.valueOf(sampleInfo[1]+"");
        int validRow = Integer.valueOf(sampleInfo[2]+"");
        //row:表头,内容
        for (int i = 0; i < datas.size(); i++) {
            XSSFRow row = sheet.createRow(i+1);
            Object[] rowdata = datas.get(i);
            //表头
            if (i+1<=validRow) {
                style = getNewCenterStyle(workbook,Color.lightGray,"header");
                for (int j = 0; j < validColumn; j++) {
                    XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
                    if(j>rowdata.length-1){
                        cell.setCellValue("");
                        continue;
                    }
                    cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
                    cell.setCellStyle(style);
                }
            //内容
            }else{
                style = getNewCenterStyle(workbook,new Color(214, 214, 214),"body");
                for (int j = 0; j < validColumn; j++) {
                    XSSFCell cell = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
                    if(j>rowdata.length-1){
                        cell.setCellValue("");
                        continue;
                    }
                    cell.setCellValue(rowdata[j]==null?"":String.valueOf(rowdata[j]));
                    cell.setCellStyle(style);
                }
            }
            
        }
        //合并表头
        sheet = mergeAllRange(sheet, validRow, validColumn);
        //设置表头样式
        style = getNewCenterStyle(workbook,Color.lightGray,"header");
        setAllRangeStyle(sheet, style);
        try  
        {   
            //保存excel到磁盘
            @SuppressWarnings("deprecation")
            String directory = request.getRealPath("/");
            File file = new File(directory+"\\kaanalysis\\module\\dzdynamicexcelsampleinfo\\dzdynamic_export_excels\\"+sampleInfo[0]+".xlsx");
            System.out.println(file.getAbsolutePath());
            FileOutputStream fout = new FileOutputStream(file);
            workbook.write(fout);  
            fout.close();  
            
            //下载excel
            downLoadFile(response, file.getAbsolutePath(), "xlsx");
        }catch (Exception e)  {  
            e.printStackTrace();  
        }  
    }
    
    /**
     * 合并表头,并添加表头样式
     * @param sheet
     * @param validrow
     * @param validcol
     * @return
     */
    private static XSSFSheet mergeAllRange(XSSFSheet sheet,int validrow,int validcol){
        XSSFCellStyle style = getNewCenterStyle(sheet.getWorkbook(),Color.lightGray,"header");
        int countrow = 1;
        int countcol = 1;
        for (int row = 1; row <validrow; row++) {//eg:3,则1,2行需要合并,3行为最后一行表头,不需要合并
            XSSFRow rowdata = sheet.getRow(row);
            if (rowdata==null) {
                continue;
            }
            for (int col = 0; col < validcol; col++) {
                //当前单元格非空,
                if (rowdata.getCell(col)!=null&&!"".equals(rowdata.getCell(col).getStringCellValue())) {
                    //计算空白列
                    for (int i = col+1; i < validcol; i++) {
                        if (rowdata.getCell(i)!=null&&!"".equals(rowdata.getCell(i).getStringCellValue())) {
                            break;
                        }
                        countcol++;
                    }
                    //计算空白行
                    for (int i = row+1; i <= validrow; i++) {
                        if(sheet.getRow(i).getCell(col)!=null&&!"".equals(sheet.getRow(i).getCell(col).getStringCellValue())){
                            break;
                        }
                        
                        countrow++;
                    }
                }
                if (countcol!=1||countrow!=1) {
                    CellRangeAddress range = new CellRangeAddress(row,(short)(row+countrow-1),col,(short)(col+countcol-1));//设置合并的行列
                    setRegionStyle(sheet,range,style);//设置合并单元格的风格（加边框）setRegionStyle是一个我写的方法
                    sheet.addMergedRegion(range);//将单元格合并
                    //重新计数
                    countcol = 1;
                    countrow = 1;
                }
            }
        }
        return sheet;
    }
    
    /**
     * 获取excel边框,样式
     * @param workBook    
     * @param bgColor    背景色
     * @param type    类型:title标题,Header表头,body内容
     * @return
     */
    
    private static XSSFCellStyle getNewCenterStyle(XSSFWorkbook workBook,Color bgColor,String type){
          XSSFCellStyle style = workBook.createCellStyle();;
          style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
          style.setWrapText(true);
          style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
          style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
          style.setBorderRight(XSSFCellStyle.BORDER_THIN);
          style.setBorderTop(XSSFCellStyle.BORDER_THIN);
          style.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
          //字体
          XSSFFont font = workBook.createFont();   
          if ("title".equals(type)) {
              font.setFontName("黑体");
              font.setColor(new XSSFColor(Color.green));//字体颜色
              font.setFontHeight(60);
              font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示    
              font.setFontHeightInPoints((short) 22); 
          }else if ("header".equals(type)) {
              font.setFontName("宋体");
              font.setColor(new XSSFColor(Color.red));
              font.setFontHeightInPoints((short) 12); 
              //设置单元格边框颜色
              XSSFColor borderColor = new XSSFColor(Color.red);
              style.setTopBorderColor(borderColor);
              style.setBottomBorderColor(borderColor);
              style.setLeftBorderColor(borderColor);
              style.setRightBorderColor(borderColor);
              //设置单元格背景色
              style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
              style.setFillBackgroundColor(new XSSFColor(bgColor));// 设置背景色,好像不起作用
              style.setFillForegroundColor(new XSSFColor(bgColor));// 设置前景色
          }else if ("body".equals(type)) {
              font.setFontName("仿宋_GB2312");    
              font.setFontHeightInPoints((short) 10);   //字体大小
          }
          style.setFont(font);
      return style;
     }
    
    /**
     * 设置合并区域样式
     * 设置excel边框,样式
     * @param sheet    sheet
     * @param range    合并区域
     * @param cs    样式
     */
    private static void setRegionStyle(XSSFSheet sheet, CellRangeAddress range , XSSFCellStyle cs) {
        for (int i = range.getFirstRow(); i <= range.getLastRow(); i ++) {
            XSSFRow row = sheet.getRow(i);
            if(range.getFirstColumn()!=range.getLastColumn()){
                for (int j = range.getFirstColumn(); j <= range.getLastColumn(); j++) {
                    XSSFCell cell = row.getCell((short)j);
                    if( cell==null){  
                        cell=row.createCell(j);  
                        cell.setCellValue("");  
                    } 
                    cell.setCellStyle(cs);
                }
            }
        }
    }
    
    /**
     * 一次性设置所有合并区域,但不包括表题,其实只是表头
     * @param sheet        工作薄
     * @param style        样式
     */
    private void setAllRangeStyle(XSSFSheet sheet , XSSFCellStyle style){
        int num = sheet.getNumMergedRegions();
        for (int i = 1; i < num; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            setRegionStyle(sheet, range, style);
        }
    }
    
    //excel 下载
    public static boolean downLoadFile(HttpServletResponse response, String fileFullName, String fileType)
            throws Exception {
            File file = new File(fileFullName);  //根据文件路径获得File文件
            //设置文件类型(这样设置就不止是下Excel文件了，一举多得)
            if("pdf".equals(fileType)){
               response.setContentType("application/pdf;charset=GBK");
            }else if("xls".equals(fileType)||"xlsx".equals(fileType)){
               response.setContentType("application/msexcel;charset=GBK");
            }else if("doc".equals(fileType)){
               response.setContentType("application/msword;charset=GBK");
            }

            //文件名
            response.setHeader("Content-Disposition", "attachment;filename=\""
                + new String(fileFullName.substring(fileFullName.lastIndexOf("\\")).getBytes(), "ISO8859-1") + "\"");
            response.setContentLength((int) file.length());
            BufferedOutputStream output = null;
            BufferedInputStream input = null;
            try {
              output = new BufferedOutputStream(response.getOutputStream());
              InputStream fis = new BufferedInputStream(new FileInputStream(file));  
              byte[] buffer = new byte[fis.available()];  
              fis.read(buffer);  
              fis.close();
              output.write(buffer);
              output.flush();   //不可少
              output.close();
              response.flushBuffer();//不可少
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
               //关闭流，不可少
               if (input != null)
                    input.close();
               if (output != null)
                    output.close();
            }

           return false;
        }
  
    
}