JAVA excel模板导出

package com.nbeasy.cwa.xls;

import java.io.IOException;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 
 * excel模板导出基类
 *  
 */

public abstract class ExcelExp {

	protected XSSFWorkbook xssWb;
	
	protected XSSFSheet xssSheet;
	
	protected HSSFWorkbook hssWb;
	
	protected HSSFSheet hssSheet;
	
	/**
	 * 设置页脚 
	 */
	public abstract void createFooter();
	
	/**
	 * 
	 * 插入行 
	 * @param startRow
	 * @param rows
	 */
	public abstract void insertRows(int startRow, int rows);
	
	/**
	 * 
	 * 替换模板中变量 
	 * @param map
	 */
	public abstract void replaceExcelData(Map
 
   map);
	
	/**
	 * 下载excel 
	 * @param response
	 * @param filaName
	 * @throws IOException
	 */
	public abstract void downloadExcel(HttpServletResponse response, String filaName) throws IOException;

	
	public XSSFWorkbook getXssWb() {
		return xssWb;
	}

	
	public void setXssWb(XSSFWorkbook xssWb) {
		this.xssWb = xssWb;
	}

	
	public XSSFSheet getXssSheet() {
		return xssSheet;
	}

	
	public void setXssSheet(XSSFSheet xssSheet) {
		this.xssSheet = xssSheet;
	}

	
	public HSSFWorkbook getHssWb() {
		return hssWb;
	}

	
	public void setHssWb(HSSFWorkbook hssWb) {
		this.hssWb = hssWb;
	}

	
	public HSSFSheet getHssSheet() {
		return hssSheet;
	}

	
	public void setHssSheet(HSSFSheet hssSheet) {
		this.hssSheet = hssSheet;
	}
	
}
 

package com.nbeasy.cwa.xls;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Footer;


/**
 * 
 * excel导出类
 * 
处理.xls格式

 *  
 */

public class HssExcelExp extends ExcelExp{

	
	public HssExcelExp() {
		super();
	}
	/**
	 * 构造函数
	 * ExcelExp
	 * @param filePath 文件路径，如com/test/template/test.xls
	 * @param sheetNum 要操作的页签，0为第一个页签
	 * @throws IOException
	 */
	public HssExcelExp(String filePath, int sheetNum) throws IOException {
//		URL resource = this.getClass().getClassLoader().getResource(filePath);
//		InputStream is = new FileInputStream(resource.getFile());
		InputStream is = new FileInputStream(filePath);
		hssWb = new HSSFWorkbook(is);
		hssSheet = hssWb.getSheetAt(sheetNum);
	}
	
	@Override
	public void createFooter() {
		Footer footer = hssSheet.getFooter();
		footer.setRight("第" + HSSFFooter.page() + "页，共" + HSSFFooter.numPages() + "页");
	}

	@Override
	public void downloadExcel(HttpServletResponse response, String fileName) throws IOException {
		fileName = new String(fileName.getBytes(),"ISO-8859-1");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/octet-stream");
		ServletOutputStream out = response.getOutputStream();
		hssWb.write(out);
		out.flush();
		out.close();
	}

	@Override
	public void insertRows(int startRow, int rows) {
		hssSheet.shiftRows(startRow, hssSheet.getLastRowNum(), rows, true, false);
	}

	@Override
	public void replaceExcelData(Map
  
    map) { int rowNum = hssSheet.getLastRowNum(); for(int i = 0;i <= rowNum; i++){ HSSFRow row = hssSheet.getRow(i); if(row == null) continue; for(int j = 0;j < row.getPhysicalNumberOfCells();j++){ HSSFCell cell = row.getCell(j); if(cell == null) continue; String key = cell.getStringCellValue(); if(map.containsKey(key)){ cell.setCellValue(map.get(key)); } } } } }
  

package com.nbeasy.cwa.xls;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 
 * excel导出类
 * 
处理.xlsx格式

 *  
 */

public class XssExcelExp extends ExcelExp{
	
	public XssExcelExp() {
		super();
	}

	/**
	 * 构造函数
	 * ExcelExp
	 * @param filePath 文件路径，如com/test/template/test.xlsx
	 * @param sheetNum 要操作的页签，0为第一个页签
	 * @throws IOException
	 */
	public XssExcelExp(String filePath, int sheetNum) throws IOException {
		URL resource = this.getClass().getClassLoader().getResource(filePath);
		InputStream is = new FileInputStream(resource.getFile());
		xssWb = new XSSFWorkbook(is);
		xssSheet = xssWb.getSheetAt(sheetNum);
	}
	
	/**
	 * 设置页脚 
	 */
	public void createFooter(){
		Footer footer = xssSheet.getFooter();
		footer.setRight("第" + HSSFFooter.page() + "页，共" + HSSFFooter.numPages() + "页");
	}
	
	/**
	 * 
	 * 插入行 
	 * @param startRow
	 * @param rows
	 */
	public void insertRows(int startRow, int rows){
		xssSheet.shiftRows(startRow, xssSheet.getLastRowNum(), rows, true, false);
	}
	
	/**
	 * 
	 * 替换模板中变量
	 * @param map
	 */
	public void replaceExcelData(Map
  
    map){ int rowNum = xssSheet.getLastRowNum(); for(int i = 0;i <= rowNum; i++){ XSSFRow row = xssSheet.getRow(i); if(row == null) continue; for(int j = 0;j < row.getPhysicalNumberOfCells();j++){ XSSFCell cell = row.getCell(j); if(cell == null) continue; String key = cell.getStringCellValue(); if(map.containsKey(key)){ cell.setCellValue(map.get(key)); } } } } /** * 下载excel * @param response * @param filaName * @throws IOException */ public void downloadExcel(HttpServletResponse response, String filaName) throws IOException{ String encodeFileName = URLEncoder.encode(filaName,"UTF-8"); response.addHeader("Content-Disposition","attachment;filename=" +encodeFileName); ServletOutputStream out = response.getOutputStream(); xssWb.write(out); out.flush(); out.close(); } }
  

public void ex_ap_form(HttpServletResponse response, 
			HttpServletRequest request) throws Exception
	{
		String type = request.getParameter("fields");
		String BorrowID = request.getParameter("header");	
        String templateName = "";
		String fileName = "";
		if (type == null) 
		{
			throw new Exception(str);
		}
		else if ("0".equals(type)) // 表示 调阅
		{
			templateName = "read.xls";
			fileName = "调阅申请.xls";
		}
		else if ("1".equals(type)) //表示 借阅
		{
			templateName = "borrow.xls";
			fileName = "借阅申请.xls";
		}
		
		String sql = "SELECT DATE_FORMAT(DATE(b.ApplyTime),'%Y年%m月%d日') date,b.BorrowID no,w.YJ_all_name wid," +
				"b.YJ_JB_Name operator,b.YJ_JB_Tel phone,b.BorrowerCompany WidName,ProveFileId certificateNumber," +
				"BorrowerName person,BorrowerIdentityCode ID_number,BorrowReason reason," +
				"CONCAT(DATE_FORMAT(DATE(b.ApplyTime),'%Y.%m.%d'),'-',DATE_FORMAT(DATE(b.ReturnTime),'%Y.%m.%d')) borrowDeadline," +
				"DATE_FORMAT(DATE(b.ReturnTime),'%Y.%m.%d') backTime FROM t_archives_borrow b " +
				"LEFT JOIN yj_wid w ON b.YJ_JB_Wid = w.YJ_wid WHERE b.BorrowID = '" + BorrowID + "' GROUP BY b.BorrowID";
		database_class data = new database_class();
		List
 
  > list = data.RunSelectFun(sql);
		//读取模板文件
		String filePath = request.getSession().getServletContext().getRealPath("template" 
				+ File.separator + templateName);	
		ExcelExp hee = new HssExcelExp(filePath, 0);
		Map
  
    result = new HashMap
   
    (); for (Entry
    
      entry : list.get(0).entrySet()) { result.put(entry.getKey(), StringUtils.replaceNull(entry.getValue())); } hee.replaceExcelData(result); //更改模版内容 String a_sql = "SELECT t.InstitutionName t0,t.ArchivesYear t1,t.ArchivesName t2,t.VolumeID t3,t.yj_save_dpid t4,t.FilesNo t5 " + "FROM t_archives t LEFT JOIN t_archives_borrow b ON t.ArchivesID = b.ArchivesID WHERE b.BorrowID = '" + BorrowID + "'"; List
     
      > a_list = data.RunSelectFun(a_sql); //向表格中插入行并写入数据 for (int i = 0; i < a_list.size(); i++) { Map
      
        map = a_list.get(i); if (i > 1) //判断原表格是否填满 hee.insertRows(9 + i, 1); //插入行 HSSFRow row_d = hee.getHssSheet().createRow(9 + i); if (i > 1) //判断原表格是否填满 hee.getHssSheet().addMergedRegion(new CellRangeAddress(9,9 + i,0,0)); //合并单元格 HSSFRow row_m = hee.getHssSheet().getRow(8); //获得第9行 row_d.createCell(0).setCellStyle(row_m.getCell(0).getCellStyle()); //对所合并的单元格继承上一个单元格的样式 //写入前一行行高 row_d.setHeightInPoints(row_m.getHeightInPoints()); for (int j = 0; j < map.size(); j++) { HSSFCell cell = row_d.createCell(j + 1); //创建单元格 cell.setCellValue(StringUtils.replaceNull(map.get("t" + j))); //写入值 HSSFCellStyle cellStyle = row_m.getCell(j).getCellStyle(); //得到前一行对应列单元格样式 cell.setCellStyle(cellStyle); //写入前一行对应列单元格样式 } } hee.downloadExcel(response, fileName); //下载 }