java poi 生成excel模板

一、手动生产excel模板（最笨的一种实现方式，不推荐使用）

	//创建excel表格
		HSSFWorkbook wb = new HSSFWorkbook();  //创建工作簿
		HSSFSheet sheet = wb.createSheet(title); //创建表单
		sheet.setDefaultColumnWidth((short) 15); //设置表单列宽
		HSSFDataFormat format = wb.createDataFormat(); //创建数据格式
		HSSFCellStyle style = getHSSFCellStyle(wb);
		
		//表单创建行和单元格
		for(int rowSum = 0;rowSum < 1000;rowSum++){
			HSSFRow row = sheet.createRow(rowSum);
			for(int c = 0;c<256;c++){
				HSSFCell cell = row.createCell(c);
			}	
		}
		
		//设置自定义excel模板
		HSSFRow row = sheet.getRow(0);  
		String[] showTitle = headers;
		int indexTitle = 0;//excel列下标
		for (short i = 0; i < showTitle.length; i++) {
			HSSFCell cell = row.getCell(indexTitle);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(showTitle[i]);
			cell.setCellValue(text);
	
			//根据字段信息设置生产日期的excel格式
			if(showTitle[i].equals(cfgMaps.get("factoryDate"))){
				for(int r = 1;r < sheet.getLastRowNum();r++){
					HSSFRow row1 = sheet.getRow(r);
					HSSFCell cell1 = row1.getCell(Integer.parseInt(elementIndex.get(cfgMaps.get("factoryDate"))+""));  
					HSSFCellStyle dateStyle = wb.createCellStyle();
					dateStyle.setDataFormat(format.getFormat(pattern));  
//					dateStyle.setDataFormat(format.getFormat("@"));
					cell1.setCellStyle(dateStyle); 
				}
			}
			
			//根据字段信息设置价格的excel格式
			if(showTitle[i].equals(cfgMaps.get("unitPrice"))){
				for(int r = 1;r < sheet.getLastRowNum();r++){
					HSSFRow row2 = sheet.getRow(r);
					HSSFCell cell2 = row2.getCell(Integer.parseInt(elementIndex.get(cfgMaps.get("unitPrice"))+""));
					cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
					HSSFCellStyle numStyle = wb.createCellStyle();  
					numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
			        cell2.setCellStyle(numStyle);
			        cell2.setCellValue("");
			        
				}
			}
			
			//根据字段信息设置数量的excel格式
			if(showTitle[i].equals(cfgMaps.get("leftNum"))){
				for(int r = 1;r < sheet.getLastRowNum();r++){
					HSSFRow row3 = sheet.getRow(r);
					HSSFCell cell3 = row3.getCell(Integer.parseInt(elementIndex.get(cfgMaps.get("leftNum"))+""));
					
					HSSFCellStyle styleNum = wb.createCellStyle();
					cell3.setCellType(Cell.CELL_TYPE_NUMERIC); 
					styleNum.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
					cell3.setCellStyle(styleNum);
					cell3.setCellValue("");
		
				}
			}
			
			indexTitle++;
		}
      
		try {
			wb.write(out);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
	
	/**
	 * 设置单元格基础样式
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.WHITE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
		return style;
	}
}

二、通过模板的形式生产excel模板文件

public static void setExcelData(String pathName,String cfgType){
//		Integer in = 0;
		//获得excel模板配置信息
		List<MmsCfgExcel> lists = getItemList(cfgType);
		
		Map<String, String> cfgMaps = new HashMap<String, String>();
		for(MmsCfgExcel mmsCfgExcel:lists){
			cfgMaps.put(mmsCfgExcel.getPropertyName(),mmsCfgExcel.getPropertyLabel());
		}
		
		//加载excel模板文件，进行标题设置
		FileInputStream fs = null;
		POIFSFileSystem ps = null;
		HSSFWorkbook wb = null;
		try {
			fs = new FileInputStream(pathName);
			ps = new POIFSFileSystem(fs);
			wb = new HSSFWorkbook(ps);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		HSSFSheet sheet = wb.getSheetAt(0); // 获取到工作表，因为一个excel可能有多个工作表
		HSSFRow row = sheet.getRow(1); // 获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
		Iterator<Cell> iter = row.cellIterator();
		for (MmsCfgExcel m : lists) {
			Cell cell = iter.next();
			HSSFRichTextString text = new HSSFRichTextString(m.getPropertyLabel());
			cell.setCellValue(text);
		}
		
		if(Integer.parseInt(cfgType) == 1){
			String[] textlist = getDropdownMfDictData(); //获得需要设置在下拉列表里的数据内容
			setDropdownList(lists, cfgMaps, sheet, textlist); //设置单元格物资状态下拉列表内容
		}
		
		try {
			fs.close();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		FileOutputStream out2 = null;
		try {
			out2 = new FileOutputStream(pathName);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				wb.write(out2);
				wb.close();
				out2.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				}
			}
		}