poi导出Excel之模板导出

模板导出
实现流程1.自定义excel模板，放到Springboot的resource路径下，程序按照excel的路径将Excel读取成workbook流，
2.给单元格赋值，我们知道模板长什么样子，从第几行开始遍历赋值数据，或者指定某个单元格赋值数据都可以通过x，y轴的坐标来定位。
3.赋值完成后，我们将带有数据的workbook保存到本地；
4.输出到浏览器下载，或者只留在本地文件留着给Email做附件都可以，看自己的业务需求

封装的工具类

import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
/*
* poi 将数据导入指定模板
* Qauthor T011921
* */
@Component
public class ExcelDataWriteTemplate {
	static final Logger logger = LoggerFactory.getLogger(ExcelDataWriteTemplate.class);
	private Workbook workbook;
	private Sheet sheet;
	private Cell cell;
	private Row row;
	private CellStyle cellStyle;
	/**
	** @param filePath 模板的路径
	   @return
	 */
	 public void createWorkbook(Stping filePath) {
	  try{
		 	 ClassPathResource classPathResource = new CLassPathResource(filePath);
		  	 workbook = WorkbookFactory.create(classPathResource.getInputStream());
		     cellStyle = workbook.createCellStyle();
		     cellStyle.setAlignment(HorizontalAlignment.CENTER);
		   	 cellStyle.setBorderBottom(BorderStyle.THIN);
		   	 cellStyle.setBorderLeft(BorderStyLe.THIN);
		     cellStyLe.setBorderRight(BorderStyLe.THIN);
		   	 cellStyle.setBorderTop(BorderStyle.THIN);
	   }catch(Exception e){
		   	Logger.error("createWorkbook-->",filePath)
		   	e.printStackTrace();
		}
	}
		/**
		 @param SheetName 提供sheet页的名称,操作该sheet页
		 */
	public void setCustomSheet(String SheetName){ 
		try{
			sheet = workbook.getSheet(SheetName);
		}catch (Exception e){
			logger.error("setcustomSheet-->", SheetName);
			e.printstackTrace();
		}
	}
	/*根据excel坐标,向指定坐标位置赋值
	* @param x ,从0开始
	* @param y从0开始
	* @param value 存入的值
	*/
	public void setCellValue(int x,int y, String value){
		 try{
		 	row = sheet.getRow(y);
		 	if(row == null){
		 		row = sheet.createRow(y);
		 	}
		 	cell = row.getCell(x);
		 	if(cell == null){
		 		cell = row.createCell(x);
		 	}
		 	cell.setCellValue(value);
		 }catch (Exception e){
		 	logger.error("setCellValue-->""{x:" + x + ",y:" + y +  ",value:" + value + "}");
		 e.printStackTrace();
		 }
	}
	/*
	*根据excel坐标,从指定行循环依次遍历数据
	* @param y告诉程序从exceL的第几行开始插入数据；从0开始
	* @param x告诉程序从第几列开始依次插入数据,从0开始
	* @param fieldName 与数据集合的key字段对应,该数组的顺序需要与ExceL模板的列顺序对应
	* @param dataList 数据集合
	* */
	public void setCelLValue( int x, int y, List<Map<String, Object>> dataList, String[] fieldName){
		 int cache_X =x;
		 for (int i=0; i<dataList.size();i++){
		 	 row = sheet.getRow(y);
		 	 if(row == null){
		 	 	row = sheet.createRow(y);
		 	 }
		 	 y++;
		 	 for(int j =0; j<fieldName.length;j++){
		 	 	 cell = row.getCell(x);
		 	 	 if(cell == null){
		 	 	 	cell = row.createCell(x);
		 	 	 	cell.setCellStyle(cellStyle);
		 	 	 }
		 	 	 if (dataList.get(i).containsKey(fieldName[j].toString())) {
		 	 	 	 cell.setCellValue(dataList.get(i).get(fieldName[j]).toString());
		 	 	 }
		 	 	 x++;
		 	 }
		 	 x = cache_X;
		  }
		}
		/**
		*将workbook文件流输出到本地
		*/
		public void write(String tempFile) {
			FileOutputStream fos = nulL;
			try{
				fos = new FileOutputStream(tempFile);
				workbook.write(fos);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}catch (IOException e) {
				e.printStackTrace()
			} finally {
				if(fos != null) {
					 try{
					 	 fos.close();
					 } catch (IOException e) {
					 	e.printStackTrace();
					 }
				}	
				if (workbook!=null) {
					try {
						 workbook.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
}		

调用上面工具类

public String writeExcelFile(List<Map<String, Object>> list1, List<Map<String, Object>> List2, String tempPath, boolean flag){ 
	ExcelDataWriteTemplate workbook = new ExcelDataWriteTemplate();
	workbook.createWorkbook(tempPath);
	if(flag){
		 workbook.setCustomSheet("sheet0");
		 workbook.setCellValue(2, 1, "填报机构：xxx");
		 workbook.setCellValue(5, 1, "联系电话：xxx");
		 workbook.setcellValue(0, 3,list1,EmailConstant.fieldNames);
	   //写入到临时文件
	   String filePath= SystemProperties.DOWLOAD_PATH + File.separator + System.currentTimeMillis() +".xLs";
	   workbook.write(filePath);
	   return filePath;
}