POI跨行导出excell表格实现

Dear 丶 2021-10-09 10:28 366阅读 0赞

# POI跨行导出excell表格实现 #

### 需求：需要导出父子级关系的元素，父级需要跨行显示，子级需要单独显示。 ###

### 在此之前可以看一下简单的Excel表单导出实现：[https://mp.csdn.net/postedit/86619130][https_mp.csdn.net_postedit_86619130] ###

### 由于数据库中的数据存的是子级存父级的ID形式，所以需要将数据包装，自己将装在父级下的list中，本案例将会包装成五级子级形式。 ###

### 第一步：首先写sql ，sql中要计算出父级需要跨的行数。（思路：首先自身内连接，计算出每一级需要跨的行数，在与主表相连将跨行数关联上去） ###

<select id="findList" resultType="OcScope">
    		SELECT 
    			a.* ,b.label as typeName,c.scope_name as parentName,
    			case when a.level =1  then '评估维度'
    			 when a.level =2  then '评估类别'
    			  when a.level =3  then '评估项目'
    			   when a.level =4  then '评估内容'
    			    when a.level =5  then '问题'
    			else '未知类别' end as levelName
    		FROM oc_scope a  left join oc_dict b on a.type=b.value and b.type="evaluation_dimension"
    		  left join oc_scope c on a.parent_id=c.id
    		WHERE a.status = #{DEL_FLAG_NORMAL}  
    		<if test="scopeName != null and scopeName != ''">
    			AND a.scope_name LIKE 
    					<if test="dbName == 'oracle'">'%'||#{scopeName}||'%'</if>
    					<if test="dbName == 'mssql'">'%'+#{scopeName}+'%'</if>
    					<if test="dbName == 'mysql'">CONCAT('%', #{scopeName}, '%')</if>
    		</if>
    		<if test="type != null and type != ''">
    			AND a.type = #{type}
    		</if>
    		<if test="level != null and level != ''">
    			AND a.level = #{level}
    		</if>
    		<if test="remark != null and remark != ''">
    			AND a.remark = #{remark}
    		</if>
    		<if test="parentId != null and parentId != ''">
    			AND a.parent_id = #{parentId}
    		</if>
    	
    		<if test="scopeName != null and scopeName != ''">
    			AND a.scope_name LIKE CONCAT(CONCAT('%',#{scopeName},'%'))
    		</if>
    			ORDER BY a.type,a.level, a.sort
    	</select>

### 第二步：编写实体类（OcScope）： ###

private String type;
    private String level;
    private OcScope parent;
    private String parentId;
    private String parentName;
    private String grandFatherName;
    private String module;	
    private String typeName;
    private String scopeName;
    private String p1ScopeName;
    private String p2ScopeName;
    private String p3ScopeName;
    private String p4ScopeName;
    private Integer count1;
    private Integer count2;
    private Integer count3;
    private Integer count4;
    private String activityCatalogue;
    private String sow;
    private String activityCataloguePosition;
    private String sowPosition;
    private String remark;
    private String sort;
    private Double weights;
    private Double p1Weights;
    private Double p2Weights;
    private Double p3Weights;
    private Double p4Weights;
    private Double remainWeights;
    private String levelName;
    private String showStatus;
             .......

### 第三步：编写mapper层： ###

public List<OcScope> findCountList(OcScope ocScope);

### 第四步：编写server层： ###

一.需要先包装数据,将子级包装到list中  
1.写一个实体类(ExcelDataBo)包装数据：

public class ExcelDataBo  { 
       private String id;
       private String name;
       private String pid;
       private Double  weights;
       private Integer count;
       private String level;
       private List<ExcelDataBo> subList;
              ...........
        }

2.包装数据

public List<ExcelDataBo> getExcelDataList() { 
    		List<ExcelDataBo> boList = Lists.newArrayList();
    	//从数据库中查询出level=1的数据
    		OcScope ocScope = new OcScope();
    		ocScope.setLevel("1");
    		List<OcScope> list1 = dao.findList(ocScope);
    	//从数据库中查询出level=2的数据
    		ocScope.setLevel("2");
    		List<OcScope> list2 = dao.findList(ocScope);
    	//从数据库中查询出level=3的数据
    		ocScope.setLevel("3");
    		List<OcScope> list3 = dao.findList(ocScope);
    	//从数据库中查询出level=4的数据
    		ocScope.setLevel("4");
    		List<OcScope> list4 = dao.findList(ocScope);
    	//从数据库中查询出level=5的数据
    		ocScope.setLevel("5");
    		List<OcScope> list5 = dao.findList(ocScope);
    		
    		int count1=0,count2=0,count3=0,count4=0; 
    		for (OcScope oc : list1) {  // level 1
    			if (oc.getLevel().equals("1")) { 
    				ExcelDataBo bo = new ExcelDataBo();
    				bo.setId(oc.getId());
    				bo.setPid(oc.getParentId());
    				bo.setName(oc.getScopeName());
    				bo.setWeights(oc.getWeights());
    				count1=0;  //首先赋值为0，count1：用于记录该项需要跨的行数
    				
    				List<ExcelDataBo> boList2 = Lists.newArrayList();
    				for (OcScope oc2 : list2) {  // level 2
    					if (oc2.getParentId().equals(bo.getId())) { 
    						ExcelDataBo bo2 = new ExcelDataBo();
    						bo2.setId(oc2.getId());
    						bo2.setPid(oc2.getParentId());
    						bo2.setName(oc2.getScopeName());
    						bo2.setWeights(oc2.getWeights());
    						count2=0;  //首先赋值为0，count2：用于记录该项需要跨的行数
    						List<ExcelDataBo> boList3 = Lists.newArrayList();
    						for (OcScope oc3 : list3) {  // level 3
    							if (oc3.getParentId().equals(bo2.getId())) { 
    								ExcelDataBo bo3 = new ExcelDataBo();
    								bo3.setId(oc3.getId());
    								bo3.setPid(oc3.getParentId());
    								bo3.setName(oc3.getScopeName());
    								bo3.setWeights(oc3.getWeights());
    								count3=0;  //首先赋值为0，count3：用于记录该项需要跨的行数
    								List<ExcelDataBo> boList4 = Lists.newArrayList();
    								for (OcScope oc4 : list4) {  // level 4
    									if (oc4.getParentId().equals(bo3.getId())) { 
    										ExcelDataBo bo4 = new ExcelDataBo();
    										bo4.setId(oc4.getId());
    										bo4.setPid(oc4.getParentId());
    										bo4.setName(oc4.getScopeName());
    										bo4.setWeights(oc4.getWeights());
    										count4=0; //首先赋值为0，count4：用于记录该项需要跨的行数
    										List<ExcelDataBo> boList5 = Lists.newArrayList();
    										for (OcScope oc5 : list5) {   // level 5
    											if (oc5.getParentId().equals(bo4.getId())) { 
    												ExcelDataBo bo5 = new ExcelDataBo();
    												bo5.setId(oc5.getId());
    												bo5.setPid(oc5.getParentId());
    												bo5.setName(oc5.getScopeName());
    												bo5.setWeights(oc5.getWeights());
    								                  bo5.setCount(1); //直接赋值1，这是最底层数据不存跨行的问题
    												boList5.add(bo5);
    												count4++;
    											}
    										}
    										if(count4==0)  //有可能其下没有list子级，但是本身需要跨一行
    										count4=1;
    										bo4.setCount(count4); 
    										bo4.setSubList(boList5); 
    										boList4.add(bo4);
    										count3 = count3+count4;
    									}
    								} 
    								if(count3==0)//有可能其下没有list子级，但是本身需要跨一行
    								count3=1;
    								bo3.setCount(count3);
    								bo3.setSubList(boList4);
    								boList3.add(bo3);
    								count2 = count2+count3;
    							}
    						}
    						if(count2==0) //有可能其下没有list子级，但是本身需要跨一行
    						count2=1;
    						bo2.setCount(count2);
    						bo2.setSubList(boList3);
    						boList2.add(bo2);
    						count1 = count1 + count2;
    					}
    				}
    				if(count1==0) //有可能其下没有list子级，但是本身需要跨一行
    				count1=1;
    				bo.setCount(count1);
    				bo.setSubList(boList2);
    				boList.add(bo);
    			}
    
    		}
    		return boList;
    	}

二.将数据写在excel中去

1.编写工具类（ExcelUtils)，注：该类可直接复制我的就行

import java.io.BufferedInputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.FileWriter;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.Enumeration;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    import java.util.zip.CRC32;
    import java.util.zip.CheckedOutputStream;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipFile;
    import java.util.zip.ZipOutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    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.ss.usermodel.Workbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.dom4j.Document;
    import org.dom4j.Element;
    import org.dom4j.io.OutputFormat;
    import org.dom4j.io.SAXReader;
    import org.dom4j.io.XMLWriter;
    
    import com.google.common.collect.Maps;
    import com.thinkgem.jeesite.common.utils.IdGen;
    
    public class ExcelUtils { 
       /** * 根据路径获取Workbook * @param filePath excel文件路径 * @return * @throws Exception */
       public static Workbook getWorkbook(String filePath) throws Exception{ 
       	int version = 0;
       	if (filePath.endsWith(".xls")) { 
       		version = 2003;
       	}else if (filePath.endsWith(".xlsx")) { 
       		version = 2007;
       	}else { 
       		throw new Exception("Incorrect file format,Only allowed '.xls,.xlsx' extension");
       	}
       	Workbook workbook = null;
       	switch (version) { 
       		case 2003:
       			POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(new FileInputStream(filePath)));
       			workbook = new HSSFWorkbook(fs);
       			break;
       
       		case 2007:
       			workbook = new XSSFWorkbook(new BufferedInputStream(new FileInputStream(filePath)));
       			break;
       	}
       	return workbook;
       }
       
       /** @SuppressWarnings("unchecked") public static void main(String[] args) throws Exception { long start = System.currentTimeMillis(); Workbook wb = ExcelUtils.getWorkbook("D:/temp/002.xlsx"); XSSFSheet sheet = (XSSFSheet) wb.getSheet("Security"); int last = sheet.getLastRowNum(); System.out.println("rownum:"+last); System.out.println("phy rownum:"+sheet.getPhysicalNumberOfRows()); System.out.printf("删除共花费%s毫秒\n",System.currentTimeMillis() - start); System.out.println(sheet.getRow(1).getCell(0).getStringCellValue().equals("Level 4\n(Product)")); Field nameField = XSSFSheet.class.getDeclaredField("_rows"); Field modifiersField = Field.class.getDeclaredField("modifiers"); modifiersField.setAccessible(true); modifiersField.setInt(nameField, nameField.getModifiers() & ~Modifier.FINAL);//把 final从修饰集中除掉 nameField.setAccessible(true); //允许访问 private Object object = nameField.get(sheet); SortedMap<Integer, XSSFRow> rowMap = (SortedMap<Integer, XSSFRow>) object; Integer index = new Integer(4); int idx = rowMap.headMap(index).size(); Field worksheetField = XSSFSheet.class.getDeclaredField("worksheet"); worksheetField.setAccessible(true); nameField.setAccessible(true); //允许访问 private CTWorksheet worksheet = (CTWorksheet) worksheetField.get(sheet); worksheet.getSheetData().removeRow(idx); rowMap.remove(index); System.out.println(((SortedMap<Integer, XSSFRow>)object).keySet()); List<XSSFRow> list = new ArrayList<XSSFRow>(); for (int i=0;i<last;i++) { if (i==5) { continue; } list.add(sheet.getRow(i)); } int sheetIndex = wb.getSheetIndex(sheet); String sheetname = sheet.getSheetName(); //wb.removeSheetAt(sheetIndex); Sheet createSheet = wb.createSheet(sheetname+"2"); wb.setSheetOrder(sheetname+"2", sheetIndex); for (int i=1;i<list.size()+1;i++) { Row row = sheet.getRow(i); XSSFRow createRow = (XSSFRow) createSheet.createRow(i); createRow.copyRowFrom(row, new CellCopyPolicy()); } last = sheet.getLastRowNum(); System.out.println("rownum:"+last); start = System.currentTimeMillis(); wb.write(new FileOutputStream("D:/temp/007-test.xlsx")); System.out.printf("写共花费%s毫秒",System.currentTimeMillis() - start); wb.close(); } * @throws Exception */
       
       public static void testZipFiles(String filePath) throws Exception{ 
       	zipFiles("D:/temp/test/99b6d26cbea34e3890b9a16b5b2a6676","D:/temp/test/test.xlsx");
       }
       
       public static void main(String[] args) throws Exception { 
       	String unzipExcel = unzipExcel("D:/temp/test/0010.xlsx","D:/temp/test/");
       	System.out.println(unzipExcel);
       }
       
       @SuppressWarnings("unchecked")
       public static void editXml(String filePath) throws Exception{ 
       	long start = System.currentTimeMillis();
       	SAXReader reader = new SAXReader();
       	Document document = reader.read(new File(filePath));
       	Element rootElement = document.getRootElement();
       	Iterator<Element> elements = rootElement.element("sheetData").elementIterator("row");
       	int index = 0;
       	while (elements.hasNext()) { 
       		Element row = elements.next();
       		index++;
       		String rownum = index+"";
       		if (index>2) { 
       			//获取第一列
       			Element col = row.element("c");
       			if (col.element("v")==null) { 
       				row.getParent().remove(row);
       				index--;
       				continue;
       			}
       			
       			row.addAttribute("r", rownum);
       			
       			List<Element> cols = row.elements("c");
       			for (Element element : cols) { 
       				String colnum = element.attributeValue("r");
       				//数字匹配
       				Matcher matcher = Pattern.compile("\\d+").matcher(colnum);
       				matcher.find();
       				String colno = matcher.group();
       				colnum = colnum.replace(colno, "");
       				element.addAttribute("r", colnum + rownum);
       			}
       		}
       	}
       	
       	/** List<Element> elements = rootElement.element("sheetData").elements("row"); for (int i = 0; i < elements.size(); i++) { Element row = elements.get(i); int index = i+1; String rownum = index+""; if (!row.attributeValue("r").equals(rownum)) { row.addAttribute("r", index+""); } List<Element> cols = row.elements("c"); for (Element element : cols) { String colnum = element.attributeValue("r"); //数字匹配 Matcher matcher = Pattern.compile("\\d+").matcher(colnum); matcher.find(); String colno = matcher.group(); colnum = colnum.replace(colno, ""); element.addAttribute("r", colnum + rownum); } } */
       	OutputFormat outputFormat = OutputFormat.createPrettyPrint();
       	outputFormat.setEncoding("UTF-8");    // 指定XML编码 
       	outputFormat.setNewlines(false); //设置是否换行
           outputFormat.setIndent(false); //设置是否缩进
       	XMLWriter writer = new XMLWriter(new FileWriter("D:/temp/0010/xl/worksheets/test.xml"), outputFormat);
           writer.write(document);
           writer.close();
           document.clone();
           System.out.println(String.format("共耗时%s ms", System.currentTimeMillis()-start));
       }
       
       @SuppressWarnings("unchecked")
       public static void removeFromXml(String xmlFilePath,Map<Integer, Object> indexs) throws Exception{ 
       	long start = System.currentTimeMillis();
       	SAXReader reader = new SAXReader();
       	File xmlFile = new File(xmlFilePath);
       	Document document = reader.read(xmlFile);
       	Element rootElement = document.getRootElement();
       	Iterator<Element> elements = rootElement.element("sheetData").elementIterator("row");
       	int index = 0;
       	int i = 0;
       	for (;elements.hasNext();i++) { 
       		Element row = elements.next();
       		index++;
       		String rownum = index+"";
       		if (i>=2) { 
       			if (indexs.containsKey(i)) { 
       				row.getParent().remove(row);
       				index--;
       				continue;
       			}
       			
       			row.addAttribute("r", rownum);
       			
       			List<Element> cols = row.elements("c");
       			for (Element element : cols) { 
       				String colnum = element.attributeValue("r");
       				//数字匹配
       				Matcher matcher = Pattern.compile("\\d+").matcher(colnum);
       				matcher.find();
       				String colno = matcher.group();
       				colnum = colnum.replace(colno, "");
       				element.addAttribute("r", colnum + rownum);
       			}
       		}
       	}
       	
       	OutputFormat outputFormat = OutputFormat.createPrettyPrint();
       	outputFormat.setEncoding("UTF-8");    // 指定XML编码 
       	outputFormat.setNewlines(false); //设置是否换行
           outputFormat.setIndent(false); //设置是否缩进
           
           //创建临时文件
           String tempFileName = IdGen.uuid();
           File tempFile = new File(xmlFilePath.replace(xmlFile.getName(), tempFileName+".xml"));
           
       	XMLWriter writer = new XMLWriter(new FileOutputStream(tempFile), outputFormat);
           writer.write(document);
           writer.close();
           //删除原来xml
           try { 
           	xmlFile.delete();
       	} catch (Exception e) { 
       		throw new RuntimeException(e);
       	}
           //重命名修改后的xml
           tempFile.renameTo(new File(xmlFilePath));
           System.out.println(String.format("共耗时%s ms", System.currentTimeMillis()-start));
       }
       
       /** * 解压excel文件到指定临时目录,并返回解压后的临时目录 * @param excelFilePath * @param tempPath * @return * @throws Exception */
       public static String unzipExcel (String excelFilePath,String tempPath) throws Exception{ 
       	String tempDir = tempPath;
       	String dirName = IdGen.uuid();
       	ZipFile zipFile = null;
       	try { 
       		zipFile = new ZipFile(new File(excelFilePath));
       		if (tempPath.endsWith("/")||tempPath.endsWith(File.separator)) { 
       			tempDir = tempDir + dirName + File.separator;
       		}else { 
       			tempDir = tempDir + File.separator + dirName + File.separator;
       		}
       		unZipFiles(zipFile, tempDir);
       	} catch (Exception e) { 
       		throw e;
       	}finally{ 
       		if (zipFile!=null) { 
       			zipFile.close();
       		}
       	}
       	return tempDir.replaceAll("\\\\", "/");
       }
       
       @SuppressWarnings("rawtypes")
       private static void unZipFiles(ZipFile zipFile,String descDir)throws IOException{ 
       	for(Enumeration entries = zipFile.entries();entries.hasMoreElements();){ 
       		ZipEntry entry = (ZipEntry)entries.nextElement();
       		String zipEntryName = entry.getName();
       		InputStream in = zipFile.getInputStream(entry);
       		String outPath = (descDir+zipEntryName).replaceAll("\\\\", "/");;
       		//判断路径是否存在,不存在则创建文件路径
       		File file = new File(outPath.substring(0, outPath.lastIndexOf('/')));
       		if(!file.exists()){ 
       			file.mkdirs();
       		}
       		//判断文件全路径是否为文件夹,如果是上面已经上传,不需要解压
       		if(new File(outPath).isDirectory()){ 
       			continue;
       		}
       		//输出文件路径信息
       		System.out.println(outPath);
       		
       		OutputStream out = new FileOutputStream(outPath);
       		byte[] buf1 = new byte[1024];
       		int len;
       		while((len=in.read(buf1))>0){ 
       			out.write(buf1,0,len);
       		}
       		in.close();
       		out.close();
       		}
       	System.out.println("******************unzip scuccess********************");
       }
       
    // public static void unZipFiles(File zipFile,String descDir)throws IOException{ 
    // File pathFile = new File(descDir);
    // if(!pathFile.exists()){ 
    // pathFile.mkdirs();
    // }
    // unZipFiles(zipFile, descDir);
    // }
       
       public static void zipFiles(String inputFileName,String outPutFileName) throws IOException {     
           File file = new File(inputFileName);    
           if (!file.exists()){   
               throw new RuntimeException(inputFileName + "not exists！");    
           }  
           String rootPath = inputFileName;
           
           if (inputFileName.endsWith("/")) { 
           	rootPath = inputFileName.substring(0,inputFileName.lastIndexOf("/"));
       	}
           FileOutputStream fileOutputStream = null;
           CheckedOutputStream cos = null;
           ZipOutputStream out = null;
           try {     
               fileOutputStream = new FileOutputStream(outPutFileName);    
               cos = new CheckedOutputStream(fileOutputStream,new CRC32());    
               out = new ZipOutputStream(cos);    
               zipByType(file, out, "",rootPath);    
    
           } catch (Exception e) {    
               e.printStackTrace();  
               throw new RuntimeException(e);    
           }finally { 
           	if (out!=null) { 
           		out.close();   
           	}
       		if (fileOutputStream!=null) { 
       			fileOutputStream.close();
       		}
       	}    
       } 
       
        /** * 判断是目录还是文件，根据类型（文件/文件夹）执行不同的压缩方法 */  
       private static void zipByType(File file, ZipOutputStream out, String basedir,String rootDir) {     
           /* 判断是目录还是文件 */    
           if (file.isDirectory()) {     
              zipDirectory(file, out, "",rootDir);    
           } else {     
               zipFile(file, out, basedir,rootDir);    
           }    
       }    
       
       /** * 压缩一个目录 */  
       private static void zipDirectory(File dir, ZipOutputStream out, String basedir,String rootDir) {     
           if (!dir.exists()){   
                return;    
           }  
           File[] files = dir.listFiles();    
           for (int i = 0; i < files.length; i++) {     
               /* 递归 */    
               zipByType(files[i], out, basedir + dir.getName() + "/",rootDir);    
           }    
       }    
       
       /** * 压缩一个文件 */  
       private static void zipFile(File file, ZipOutputStream out, String basedir, String rootDir) {     
           if (!file.exists()) {     
               return;    
           }    
           try {     
               BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
               //获取文件相对于压缩文件夹根目录的子路径
       		String subPath = file.getAbsolutePath();
       		int index = subPath.indexOf(file.getAbsolutePath());
       		if (index != -1) 
       		{ 
       			subPath = subPath.substring(rootDir.length() + File.separator.length());
       		}
               ZipEntry entry = new ZipEntry(subPath);    
               out.putNextEntry(entry);    
               int count;    
               byte data[] = new byte[4096];    
               while ((count = bis.read(data, 0, 4096)) != -1) {     
                   out.write(data, 0, count);    
               }    
               bis.close();    
           } catch (Exception e) {     
               throw new RuntimeException(e);    
           }    
       }
       
       /** * 删除Excel中需要保留Sheet之外的其他Sheet,返回Workbook对象 * @param filePath Excel文件路径 * @param sheetName 需要保留的Sheet名称 * @return 返回只包含保留Sheet的Workbook对象 * @throws Exception */
       public static Workbook getSingleSheetWorkbook(String filePath,String sheetName) throws Exception { 
       	Workbook workbook = getWorkbook(filePath);
       	int numberOfSheets = workbook.getNumberOfSheets();
       	String[] sheetNames = new String[numberOfSheets];
       	for (int i = 0; i < numberOfSheets; i++) { 
       		sheetNames[i] = workbook.getSheetName(i);
       	}
       	for (String name : sheetNames) { 
       		if (!sheetName.equalsIgnoreCase(name)) { 
       			workbook.removeSheetAt(workbook.getSheetIndex(name));
       		}
       	}
       	return workbook;
       }
       
       /** * 删除Excel中需要保留Sheet之外的其他Sheet,返回Workbook对象 * @param workbook Workbook对象 * @param sheets 需要保留的Sheet名称数组 * @return 返回只包含保留Sheet的Workbook对象 * @throws Exception */
       public static Workbook getSheetWorkbook(Workbook workbook,String[] sheets) throws Exception { 
       	int numberOfSheets = workbook.getNumberOfSheets();
       	String[] sheetNames = new String[numberOfSheets];
       	for (int i = 0; i < numberOfSheets; i++) { 
       		sheetNames[i] = workbook.getSheetName(i);
       	}
       	Map<String, Object> sheetMap = Maps.newHashMap();
       	for (String sheet : sheets) { 
       		sheetMap.put(sheet.toLowerCase(), null);
       	}
       	for (String name : sheetNames) { 
       		if (!sheetMap.containsKey(name.toLowerCase())) { 
       			workbook.removeSheetAt(workbook.getSheetIndex(name));
       		}
       	}
       	return workbook;
       }
       
       /** * 复制单元格样式 * @param wb 新建单元格所在Workbook * @param scrCell 源单元格,需要复制的单元格 * @param destCell 目标单元格,新建单元格 */
       public static void copyCellStyle(Workbook wb,Cell scrCell, Cell destCell) {   
           CellStyle newStyle = wb.createCellStyle();  
           copyStyle(scrCell.getCellStyle(), newStyle);  
           destCell.setCellStyle(newStyle);  
       }  
     
       // 单元格样式复制函数 
       private static void copyStyle(CellStyle oldStyle, CellStyle newStyle) {   
       	  newStyle.setAlignment(oldStyle.getAlignment());  
             //边框和边框颜色 
             newStyle.setBorderBottom(oldStyle.getBorderBottom());  
             newStyle.setBorderLeft(oldStyle.getBorderLeft());  
             newStyle.setBorderRight(oldStyle.getBorderRight());  
             newStyle.setBorderTop(oldStyle.getBorderTop());  
             newStyle.setTopBorderColor(oldStyle.getTopBorderColor());  
             newStyle.setBottomBorderColor(oldStyle.getBottomBorderColor());  
             newStyle.setRightBorderColor(oldStyle.getRightBorderColor());  
             newStyle.setLeftBorderColor(oldStyle.getLeftBorderColor());  
               
             //背景和前景 
             newStyle.setFillBackgroundColor(oldStyle.getFillBackgroundColor());  
             newStyle.setFillForegroundColor(oldStyle.getFillForegroundColor());  
               
             newStyle.setDataFormat(oldStyle.getDataFormat());  
             newStyle.setFillPattern(oldStyle.getFillPattern());  
    // newStyle.setFont(oldStyle.getFont(null)); 
             newStyle.setHidden(oldStyle.getHidden());  
             newStyle.setIndention(oldStyle.getIndention());//首行缩进 
             newStyle.setLocked(oldStyle.getLocked());  
             newStyle.setRotation(oldStyle.getRotation());//旋转 
             newStyle.setVerticalAlignment(oldStyle.getVerticalAlignment());  
             newStyle.setWrapText(oldStyle.getWrapText());    
       } 
       
       public static void copyRowStyle(Workbook wb,Row scrRow,Row destRow){ 
       	for (int i=0;i<destRow.getLastCellNum();i++) { 
       		Cell cell = scrRow.getCell(i);
       		if (cell!=null) { 
       			CellStyle newStyle = wb.createCellStyle();
              		newStyle.cloneStyleFrom(scrRow.getCell(i).getCellStyle());
              	    destRow.getCell(i).setCellStyle(newStyle);
       		}
       	}
       }
       
       /** * 判断指定的单元格是否是合并单元格,如果是则返回合并单元格index,不是则返回-1 * @param sheet * @param row 行下标 * @param column 列下标 * @return */    
       public static int isMergedRegion(Sheet sheet,int row ,int column) {     
           int sheetMergeCount = sheet.getNumMergedRegions();    
           for (int i = 0; i < sheetMergeCount; i++) {     
               CellRangeAddress range = sheet.getMergedRegion(i);    
               int firstColumn = range.getFirstColumn();    
               int lastColumn = range.getLastColumn();    
               int firstRow = range.getFirstRow();    
               int lastRow = range.getLastRow();    
               if(row >= firstRow && row <= lastRow){     
                   if(column >= firstColumn && column <= lastColumn){     
                       return i;    
                   }    
               }    
           }    
           return -1;    
       }
       
       /** * 获取合并单元格的值 * @param sheet * @param row * @param column * @return */    
       public static String getMergedRegionValue(Sheet sheet ,int row , int column){  
           int sheetMergeCount = sheet.getNumMergedRegions();    
               
           for(int i = 0 ; i < sheetMergeCount ; i++){     
               CellRangeAddress ca = sheet.getMergedRegion(i);    
               int firstColumn = ca.getFirstColumn();    
               int lastColumn = ca.getLastColumn();    
               int firstRow = ca.getFirstRow();    
               int lastRow = ca.getLastRow();    
               if(row >= firstRow && row <= lastRow){     
                   if(column >= firstColumn && column <= lastColumn){     
                       Row fRow = sheet.getRow(firstRow);    
                       Cell fCell = fRow.getCell(firstColumn);    
                       return getCellValue(fCell) ;    
                   }    
               }    
           }    
               
           return null ;    
       }  
       
      	/** * 获取单元格的值 * @param cell * @return */
      	public static String getCellValue(Cell cell) { 
      		if (cell == null)
      			return "";
      		if (cell.getCellType() == Cell.CELL_TYPE_STRING) { 
      			return cell.getStringCellValue();
      		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { 
      			return String.valueOf(cell.getBooleanCellValue());
      		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { 
      			return cell.getCellFormula();
      		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { 
      			return String.valueOf(cell.getNumericCellValue());
      		}
      		return "";
      	}
      	
       
       /** * 合并单元格 * @param sheet * @param firstRow 开始行 * @param lastRow 结束行 * @param firstCol 开始列 * @param lastCol 结束列 */    
       public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {     
           sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));    
       }   
       
    }

2.将数据写到excel中去

//注入地址，防止硬编码问题
    @Value("${excel.temp.dir}")
    	private String tempDir;
    
    @SuppressWarnings("unchecked")
    	public void exportEvaluationTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception { 
    		Workbook workbook = null;
    		File destFile = null;
    		try { 
    			//保证模板文件放在项目中，或者自己的c/d盘中，（本例是放在项目中的） 获取最新Excel模板
    			 String classpath = exportEvaluationTemplate.class.getClassLoader().getResource("").getPath(); //获取当类的位置
    		       String templatePath = classpath + "/templates/"; //类所在的文件夹中新建一个templates包用来存储文件的包
    		       
    		 // templatePath：文件的地址
    			File srcFile = new File(templatePath+"Evaluation_Template.xlsx"); //拿到文件
    			String tempName = "Evaluation_Template_" + DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx";
    			destFile = new File(tempDir + "/" + tempName);
    			FileUtils.copyFile(srcFile, destFile);
    			workbook = ExcelUtils.getWorkbook(destFile.getPath());
    			Sheet sheet = workbook.getSheetAt(0);
    			// 得到需要写入的数据
    			List<ExcelDataBo> rowList = getExcelDataList();
    			// 统计信息从第2行，第一列开始写
    			int rowIndex = 1; // 行
    			int cellIndex = 0;  //列
    			for (ExcelDataBo bo : rowList) { 
    				String cellName = bo.getName();
    				Double cellWeights = bo.getWeights();
    				int rowMgrCnt = bo.getCount(); // 合并行数
    				cellIndex = 0;
    				//将数据写入表格
    				sheet.getRow(rowIndex).getCell(cellIndex).setCellValue(cellName);
    				//合并所需要的行或者列
    				if(rowMgrCnt > 1)  //如果rowMgrCnt<1,就没有必要合并单元格了
    				ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, cellIndex, cellIndex);
    				cellIndex++;  
    				sheet.getRow(rowIndex).getCell(cellIndex).setCellValue(cellWeights);
    				if(rowMgrCnt > 1)
    				ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, cellIndex, cellIndex);
    					List<ExcelDataBo> boList2 = bo.getSubList();
    					if(boList2!=null && boList2.size()>0 ){ 
    					for (ExcelDataBo bo2 : boList2) { 
    						rowMgrCnt = bo2.getCount(); // 合并行
    						
    						int c1=cellIndex;
    						c1++;
    						sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getName());
    						if(rowMgrCnt > 1)
    						ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c1, c1);
    						c1++;
    // sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getWeights());
    						if(bo2.getWeights()!=null && !bo2.getWeights().equals("") )
    						{ 
    							sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getWeights());
    						}else{ 
    							sheet.getRow(rowIndex).getCell(c1).setCellValue(0);
    						}
    						if(rowMgrCnt > 1)
    						ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c1, c1);
    							List<ExcelDataBo> boList3 = bo2.getSubList();
    							if(boList3!=null && boList3.size()>0 ){ 
    							for (ExcelDataBo bo3 : boList3) { 
    								rowMgrCnt = bo3.getCount(); // 合并行
    								int c2=c1;
    								c2++;
    								sheet.getRow(rowIndex).getCell(c2).setCellValue(bo3.getName());
    								if(rowMgrCnt > 1)
    								ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c2, c2);
    								c2++;
    								if(bo3.getWeights()!=null && !bo3.getWeights().equals("") )
    								{ 
    									sheet.getRow(rowIndex).getCell(c2).setCellValue(bo3.getWeights());
    								}else{ 
    									sheet.getRow(rowIndex).getCell(c2).setCellValue(0);
    								}
    								if(rowMgrCnt > 1)
    								ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c2, c2);
    							 
    									List<ExcelDataBo> boList4 = bo3.getSubList();
    									if(boList4!=null && boList4.size()>0 ){ 
    									for (ExcelDataBo bo4 : boList4) { 
    										rowMgrCnt = bo4.getCount(); // 合并行
    										
    										int c3=c2;
    										c3++;
    										sheet.getRow(rowIndex).getCell(c3).setCellValue(bo4.getName());
    										if(rowMgrCnt > 1)
    										ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c3, c3);
    										c3++;
    										if(bo4.getWeights()!=null && !bo4.getWeights().equals("") )
    										{ 
    											sheet.getRow(rowIndex).getCell(c3).setCellValue(bo4.getWeights());
    										}else{ 
    											sheet.getRow(rowIndex).getCell(c3).setCellValue(0);
    										}
    										if(rowMgrCnt > 1)
    										ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c3, c3);
    										
    										List<ExcelDataBo> boList5 = bo4.getSubList();
    										if(boList5!=null && boList5.size()>0 ){ 
    											for (ExcelDataBo bo5 : boList5) { 
    												rowMgrCnt = 1; // 合并行
    												int c4=c3;
    												c4++;
    												sheet.getRow(rowIndex).getCell(c4).setCellValue(bo5.getName());
    												if(rowMgrCnt > 1)
    												ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c4, c4);
    												c4++;
    												if(bo5.getWeights()!=null && !bo5.getWeights().equals("") )
    												{ 
    													sheet.getRow(rowIndex).getCell(c4).setCellValue(bo5.getWeights());
    												}else{ 
    													sheet.getRow(rowIndex).getCell(c4).setCellValue(0);
    												}
    												
    												if(rowMgrCnt > 1)
    												ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c4, c4);
    												//将行数怎家
    												rowIndex=rowIndex+rowMgrCnt;
    											}
    									}else{   //进入else就是等于说是rowMgrCnt=0，输出表格需要换行开始下一行的数据输出。
    										rowIndex = rowIndex+1;
    									}
    								
    								}
    									
    							}else{ 
    								rowIndex = rowIndex+1;   
    							}
    						}
    						}else{ 
    							rowIndex = rowIndex+1;
    						}
    					}
    					
    				}else{ 
    					rowIndex = rowIndex+rowMgrCnt;
    				}
    				 
    			}
    			OutputStream wos = null;
    			try { 
    				wos = new FileOutputStream(destFile);
    				workbook.write(wos);
    			} catch (Exception e) { 
    				throw e;
    			} finally { 
    				if (wos != null) { 
    					wos.close();
    				}
    			}
    			FileUtils.downFile(destFile, request, response);
    		} catch (Exception e) { 
    			throw e;
    		} finally { 
    			if (workbook != null) { 
    				workbook.close();
    			}
    			if (destFile != null) { 
    				try { 
    					destFile.delete();
    				} catch (Exception e) { 
    					throw new RuntimeException(e);
    				}
    			}
    		}
    	}

### 第五步：编写controller层： ###

/** * 表单导出方法 * @param request * @param response * @throws Exception */
    	@RequestMapping(value = "export")
        public void exportEvaluationTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception { 
    		
    		ocScopeService.exportEvaluationTemplate(request,response);
    		
    	}

[https_mp.csdn.net_postedit_86619130]: https://mp.csdn.net/postedit/86619130