poi导出报表数据到excel，工具类

package com.zhwg.core.dao.utile.excel;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhwg.core.tool.ZipUtil;

public class ExcelUtils {

private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private CellStyle cellStyle;
int writeLine = 1;

/**
 * 字号
 */
private static final int FONT_SIZE = 8;

/**
 * 导出到excel文件，默认的sheet名称
 */
private static final String DEFAULT_SHEET_NAME = "Sheet1";

/**
 * 列宽
 */
private static final int WIDTH_COL = 16;

public static void main(String[] args) {
	String[] title = { "第1列", "第2列", "第3列", "第4列", "第5列", "第6列", "第7列" };
	List<String[]> contents = new ArrayList<String[]>();
	for (int i = 0; i < 10; i ++) {
		contents.add(new String[] {"第" + i + "行第1列", "第" + i + "行第2列", "第" + i + "行第3列", "第" + i + "行第4列", "第" + i + "行第5列", "第" + i + "行第6列", "第" + i + "行第7列"});
	}
	new ExcelUtils().exportToExcel(title, contents);
}

/**
 * 
 * @description 将数据导出为excel文件
 *
 * @author chj
 * @date 2013-2-19
 * @time 上午11:38:38
 * 
 * @param titles 表头
 * @param contents 表内容
 * @return 导出的excel文件
 */
public File exportToExcel(String[] titles, List<String[]> contents) {
	WritableWorkbook workbook = null;
	File tempDir = new File("test");
	if (!tempDir.exists()){
		tempDir.mkdirs();
	}
	// 防止文件重名
	File file = new File("temp" + Calendar.getInstance().getTime().getTime() + ".xls");
    try {
        workbook = Workbook.createWorkbook(file);
        WritableSheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME, 0);
        initGridSize(sheet, titles.length, WIDTH_COL);
        initTitle(sheet, titles);
        if (contents != null && contents.size() > 0) {
        	for (int i = 0; i < contents.size(); i++) {
        		writeLineContent(sheet, contents.get(i));
        	}
        }
		workbook.write();
    } catch (Exception e) {
    	logger.error(e.getMessage(),e);
    } finally {
        closeWritableWorkbook(workbook);
    }
    return file;
}

/**
 * 
 * @description 将数据导出为excel文件
 *
 * @author chj
 * @date 2013-2-19
 * @time 下午01:31:20
 * 
 * @param titles 表头
 * @param contents 表内容
 * @return 导出的excel文件
 */
public File exportToExcel(List<String> titles, List<List<String>> contents) {
	WritableWorkbook workbook = null;
	// 防止文件重名
	File file = new File("temp" + Calendar.getInstance().getTime().getTime() + ".xls");
	try {
		workbook = Workbook.createWorkbook(file);
		WritableSheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME, 0);
		initGridSize(sheet, titles.size(), WIDTH_COL);
		String[] titleTemp = (String[]) titles.toArray();
		initTitle(sheet, titleTemp);
		if (contents != null && contents.size() > 0) {
			for (int i = 0; i < contents.size(); i++) {
				writeLineContent(sheet, contents.get(i));
			}
		}
		workbook.write();
	} catch (Exception e) {
		logger.error(e.getMessage(),e);
	} finally {
		closeWritableWorkbook(workbook);
	}
	return file;
}

/**
 * 把告警信息写入excel表
 * 
 * @param excel
 * @param trapList
 * @return
 */
public boolean exportToExcel(String userName, File excel) {
    WritableWorkbook workbook = null;
    try {
        workbook = Workbook.createWorkbook(excel);
        WritableSheet sheet = workbook.createSheet(DEFAULT_SHEET_NAME, 0);
        initGridSize(sheet);
        initTitle(sheet, new String[] { userName, "", ""});
        initTitle(sheet, new String[] { "第1列", "第2列", "第3列", "第4列", "第5列", "第6列", "第7列" });
        String[] values = new String[] { "1", "2", "3", "4", "5", "6", "7", };
        writeLineContent(sheet, values);
        workbook.write();
    } catch (Exception e) {
        return false;
    } finally {
        closeWritableWorkbook(workbook);
    }
    return true;
}

private void closeWritableWorkbook(WritableWorkbook rwb) {
    if (null != rwb) {
        try {
            rwb.close();
        } catch (WriteException e) {
            logger.warn("can not close the excel workbook", e);
        } catch (IOException e) {
            logger.warn("can not close the excel workbook", e);
        }
    }
}



/**
 * 初始化表格尺寸
 * 
 * @param sheet
 * @throws RowsExceededException
 */
private static void initGridSize(WritableSheet sheet) throws RowsExceededException {

    sheet.setColumnView(0, WIDTH_COL);
    sheet.setColumnView(1, WIDTH_COL);
    sheet.setColumnView(2, WIDTH_COL);
    sheet.setColumnView(3, WIDTH_COL);
    sheet.setColumnView(4, WIDTH_COL);
    sheet.setColumnView(5, WIDTH_COL);
    sheet.setColumnView(6, WIDTH_COL);
    sheet.setColumnView(7, WIDTH_COL);
    sheet.setColumnView(8, WIDTH_COL);
    sheet.setColumnView(9, WIDTH_COL);
    sheet.setColumnView(10, WIDTH_COL);
}

/**
 * 
 * @description
 *
 * @author chj
 * @date 2013-2-19
 * @time 上午11:06:42
 * 
 * @param sheet excel表
 * @param colSize 列数
 * @param width 列宽
 * @throws RowsExceededException
 */
private static void initGridSize(WritableSheet sheet, int colSize, int width) throws RowsExceededException {
	for (int i = 0; i < colSize; i++) {
		sheet.setColumnView(i, width);
	}
}

/**
 * 初始化标题栏
 * 
 * @param sheet
 * @throws WriteException
 * @throws RowsExceededException
 */
private void initTitle(WritableSheet sheet, String[] titles) throws WriteException {
    WritableCellFormat wcf = getCellTitleFormat();
    int i = 0;
    for (String title : titles) {
        sheet.addCell(new Label(i, writeLine, title, wcf));
        i++;
    }
    writeLine += 2;

}

/**
 * 获得标题栏格式
 * 
 * @return
 * @throws WriteException
 */
private WritableCellFormat getCellTitleFormat() throws WriteException {
    WritableCellFormat wcf = getBasicCellFormat();
    wcf.setBackground(Colour.GREY_25_PERCENT);
    return wcf;
}

/**
 * 初始化单元格基本格式
 * 
 * @param wcf_center
 * @throws WriteException
 */
private WritableCellFormat getBasicCellFormat() throws WriteException {
    WritableCellFormat wcf = new WritableCellFormat();
    WritableFont font = new WritableFont(WritableFont.ARIAL, FONT_SIZE);
    wcf.setFont(font);
    wcf.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
    wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
    wcf.setAlignment(Alignment.CENTRE);
    wcf.setWrap(true); // 是否换行
    return wcf;
}

/**
 * 向excel表格中写入一行内容
 * 
 * @param userList
 * @param sheet
 * @throws WriteException
 * @throws RowsExceededException
 */
private void writeLineContent(WritableSheet sheet, String[] values) throws WriteException {
    WritableCellFormat wcf = getBasicCellFormat();
    int i = 0;
    for (String value : values) {
        sheet.addCell(new Label(i, writeLine, value, wcf));
        i++;
    }
    if (values.length<=1) {
		sheet.mergeCells(0, writeLine, sheet.getColumns()-1, writeLine); // 合并单元格  
	}
    writeLine = writeLine + 1;
}

/**
 * 
 * @description 向excel表格中写入一行内容
 *
 * @author chj
 * @date 2013-2-19
 * @time 下午01:29:45
 * 
 * @param sheet
 * @param values
 * @throws WriteException
 */
private void writeLineContent(WritableSheet sheet, List<String> values) throws WriteException {
	WritableCellFormat wcf = getBasicCellFormat();
	int i = 0;
	for (String value : values) {
		sheet.addCell(new Label(i, writeLine, value, wcf));
		i++;
	}
	writeLine = writeLine + 1;
}

/**
 * 获取文件路径
 * 
 * @param folderName
 * @return
 */
public static String getFolderPath(String folderName) {
	File classFile = new File(getClassPath());
	String tempFileStr = classFile.getParentFile().getParent()
			+ File.separator + folderName;
	File tempFile = new File(tempFileStr);
	if (!tempFile.exists() || !tempFile.isDirectory()) {
		if (tempFile.mkdirs()) {
			return tempFileStr;
		} else {
			return "";
		}
	}
	return tempFileStr;
}

/**
 * 获取系统文件classpath路径
 * 
 * @return
 */
public static String getClassPath() {
	return ExcelUtils.class.getClassLoader().getResource("").getFile()
			.replaceAll("%20", " ");
}
/**
 * 向一个单元格写入值//复制sheet(0)
 * 
 * @param sheetNum
 *            sheet的编号
 * @param rowNum
 *            行号
 * @param columnNum
 *            列号
 * @param value
 *            写入的值
 * @throws Exception
 */
public void writeToExcel(HSSFWorkbook workbook ,int sheetNum, int rowNum, int columnNum,
		Object value) throws Exception {
	try {
		HSSFSheet sheet = workbook.getSheetAt(sheetNum);
		HSSFRow row = sheet.getRow(rowNum);
		if (null == row) {
			row = sheet.createRow(rowNum);
		}
		HSSFCell cell = row.getCell(columnNum);
		if (cell == null) {
			cell = row.createCell(columnNum);
		}
		cell.setCellValue(convertString(value));
	} catch (Exception e) {
		logger.error(e.getMessage(),e);
	} finally {
	}
}

public void writeToExcel(XSSFWorkbook workbook ,int sheetNum, int rowNum, int columnNum, Object value) throws Exception {
	try {
		XSSFSheet sheet = workbook.getSheetAt(sheetNum);
		XSSFRow row = sheet.getRow(rowNum);
		if (null == row) {
			row = sheet.createRow(rowNum);
		}
		XSSFCell cell = row.getCell(columnNum);
		if (cell == null) {
			cell = row.createCell(columnNum);
		}
		cell.setCellValue(convertString(value));
	} catch (Exception e) {
		logger.error(e.getMessage(),e);
	} finally {
	}
}

public void createPicture(int dx1,int dy1,int dx2,int dy2,short col1,int row1,short col2,int row2,
		HSSFPatriarch patriarch,String path,HSSFWorkbook wb) throws Exception {
	//anchor主要用于设置图片的属性
    /*关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
    dx1：起始单元格的x偏移量，例如255表示直线起始位置距A1单元格左侧的距离；
    dy1：起始单元格的y偏移量，例如125表示直线起始位置距A1单元格上侧的距离；
    dx2：终止单元格的x偏移量，例如1023表示直线起始位置距C3单元格左侧的距离；
    dy2：终止单元格的y偏移量，例如150表示直线起始位置距C3单元格上侧的距离；
    col1：起始单元格列序号，从0开始计算；
    row1：起始单元格行序号，从0开始计算，例如col1=0,row1=0就表示起始单元格为A1；
    col2：终止单元格列序号，从0开始计算；
    row2：终止单元格行序号，从0开始计算，例如col2=2,row2=2就表示起始单元格为C3；*/
    HSSFClientAnchor anchor = new HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2);
	//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray    
	ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();  
	File file = new File(path);
	if(file.isFile() && !file.isDirectory()){
		BufferedImage input = ImageIO.read(file);
		ImageIO.write(input, "png", byteArrayOut);
		//插入图片    
		patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
	}
}

/**
 * 向一个单元格写入值//复制sheet(0)
 * 
 * @param sheetNum
 *            sheet的编号
 * @param rowNum
 *            行号
 * @param columnNum
 *            列号
 * @param value
 *            写入的值
 * @throws Exception
 */
public  void writeToExcelStyle(HSSFWorkbook workbook ,int sheetNum, int rowNum, int columnNum,
		Object value,HSSFCellStyle style) throws Exception {
	try {
		HSSFSheet sheet = workbook.getSheetAt(sheetNum);
		HSSFRow row = sheet.getRow(rowNum);
		if (null == row) {
			row = sheet.createRow(rowNum);
		}
		HSSFCell cell = row.getCell(columnNum);
		if (cell == null) {
			cell = row.createCell(columnNum);
		}
		cell.setCellValue(convertString(value));
		cell.setCellStyle(style);
	} catch (Exception e) {
		logger.error(e.getMessage(),e);
	} finally {
	}
}
/**
 * 向一个单元格写入值//复制sheet(0)
 * 
 * @param sheetNum
 *            sheet的编号
 * @param rowNum
 *            行号
 * @param columnNum
 *            列号
 * @param value
 *            写入的值
 * @throws Exception
 */
public  void writeToExcelStyle(XSSFWorkbook workbook ,int sheetNum, int rowNum, int columnNum,
		Object value,short alignStyle) throws Exception {
	try {
		XSSFSheet sheet = workbook.getSheetAt(sheetNum);
		XSSFRow row = sheet.getRow(rowNum);
		if (null == row) {
			row = sheet.createRow(rowNum);
		}
		XSSFCell cell = row.getCell(columnNum);
		if (cell == null) {
			cell = row.createCell(columnNum);
		}
		cell.setCellValue(convertString(value));
		
		setCellStyle(workbook, alignStyle, CellStyle.VERTICAL_CENTER);
		cell.setCellStyle(cellStyle);
	} catch (Exception e) {
		logger.error(e.getMessage(),e);
	} finally {
	}
}

public String convertString(Object value) {
	if (value == null) {
		return "";
	} else {
		return value.toString();
	}
}
/**
 * 设置单元格
 * @param workbook
 * @param alignStyle //水平对其方式
 * @param verticalAlignment//垂直对其方式
 */
public void setCellStyle(XSSFWorkbook workbook,short alignStyle,short verticalAlignment){
	cellStyle = workbook.createCellStyle();
	cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
	cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//下边框
	cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
	cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
	cellStyle.setAlignment(alignStyle);//水平对其方式
	cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对其方式
	cellStyle.setWrapText(true);//自适应高度
}

/**
 * 设置单元格
 * @param workbook
 * @param alignStyle //水平对其方式
 * @param verticalAlignment//垂直对其方式
 */
public void setCellStyle(HSSFWorkbook workbook,short alignStyle,short verticalAlignment){
	cellStyle = workbook.createCellStyle();
	cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
	cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//下边框
	cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
	cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
	cellStyle.setAlignment(alignStyle);//水平对其方式
	cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对其方式
	cellStyle.setWrapText(true);//自适应高度
}

/**设置合并单元格
 * *
 */
public void setRegion(HSSFWorkbook workBook,int sheetId,int rowFrom, short colFrom, int rowTo, short colTo){
	try{
		HSSFSheet sheet = workBook.getSheetAt(sheetId);
		sheet.addMergedRegion(new CellRangeAddress(rowFrom, rowTo, colFrom, colTo));//参数：起始行号，终止行号， 起始列号，终止列号
	}catch (Exception e) {
		logger.error(e.getMessage(),e);
	}
}
public void setRegion(XSSFWorkbook workBook,int sheetId,int rowFrom, short colFrom, int rowTo, short colTo){
	try{
		XSSFSheet sheet = workBook.getSheetAt(sheetId);
		CellRangeAddress address = new CellRangeAddress(rowFrom, rowTo, colFrom, colTo);
		sheet.addMergedRegion(address);//参数：起始行号，终止行号， 起始列号，终止列号
		RegionUtil.setBorderBottom(1, address, sheet);
		RegionUtil.setBorderLeft(1, address, sheet);
		RegionUtil.setBorderRight(1, address, sheet);
		RegionUtil.setBorderTop(1, address, sheet);
		
	}catch (Exception e) {
		logger.error(e.getMessage(),e);
	}
}

/**
 * 设置单元格样式
 */
public void setRowStyle(HSSFWorkbook workBook,int sheetId,int rowFrom, short colFrom, int rowTo, short colTo){
	try{
		setCellStyle(workBook, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
		
		HSSFSheet sheet = workBook.getSheetAt(sheetId);
		for(int i=0;rowFrom+i<=rowTo;i++ ){
			HSSFRow hssfRow=sheet.getRow(rowFrom+i);
			if(hssfRow!=null){
				
			}else{
				hssfRow=sheet.createRow(colFrom+i);
			}
			for(int j=0; colFrom+j<=colTo; j++ ){
				HSSFCell hssfCell=hssfRow.getCell(colFrom+j);
				if(hssfCell!=null){
					hssfCell.setCellStyle(cellStyle);
				}else{

// hssfRow.createCell(colFrom);
// hssfCell.setCellStyle(cellStyle);
}
}

		}
	}catch (Exception e) {
		logger.error(e.getMessage(),e);
	}
}
public void setRowStyle(XSSFWorkbook workBook,int sheetId,int rowFrom, int colFrom, int rowTo, int colTo){
	try{
		setCellStyle(workBook, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);
		XSSFSheet sheet = workBook.getSheetAt(sheetId);
		for(int i=0;rowFrom+i<=rowTo;i++ ){
			XSSFRow hssfRow = sheet.getRow(rowFrom+i);
			if(hssfRow==null){
				hssfRow = sheet.createRow(colFrom+i);
			}
			for(int j=0; colFrom+j<=colTo; j++ ){
				XSSFCell hssfCell = hssfRow.getCell(colFrom+j);
				if(hssfCell!=null){
					hssfCell.setCellStyle(cellStyle);
				}else{
					hssfRow.createCell(colFrom+j);
					hssfCell = hssfRow.getCell(colFrom+j);
					hssfCell.setCellStyle(cellStyle);
				}
			}
		}
	}catch (Exception e) {
		logger.error(e.getMessage(),e);
	}
}
/**
 * 写入Excel文件并关闭
 * @throws UnsupportedEncodingException 
 */

public void writeAndClose(HSSFWorkbook workBook,HttpServletResponse response, String filename) throws UnsupportedEncodingException {
	// 创建文件输出流，准备输出电子表格
	response.setContentType("text/html;charset=utf-8");
	response.setCharacterEncoding("utf-8");
	response.setContentType("octets/stream");
	response.addHeader("Content-Disposition",
			"attachment;filename="+new String(filename.getBytes("GB2312"), "ISO_8859_1")+"");
	if (workBook != null) {
		try {
			OutputStream out = response.getOutputStream();
			workBook.write(out);
			if (out != null) {
				out.close();
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} 
	}
}

public void writeAndClose(XSSFWorkbook workBook,HttpServletResponse response, String filename) throws UnsupportedEncodingException {
	// 创建文件输出流，准备输出电子表格
	response.setContentType("text/html;charset=utf-8");
	response.setCharacterEncoding("utf-8");
	response.setContentType("octets/stream");
	response.addHeader("Content-Disposition",
			"attachment;filename="+new String(filename.getBytes("GB2312"), "ISO_8859_1")+"");
	if (workBook != null) {
		try {
			OutputStream out = response.getOutputStream();
			workBook.write(out);
			if (out != null) {
				out.close();
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} 
	}
}

  /** 
 * <b>功能：</b>列头样式<br> 
 * <b>作者：</b>yixq<br> 
 * <b>@param wb 
 * <b>@return</b> 
 */  
public static HSSFCellStyle initColumnHeadStyle(HSSFWorkbook wb) {  
    HSSFCellStyle columnHeadStyle = wb.createCellStyle(); 
    HSSFFont columnHeadFont = wb.createFont();  
    columnHeadFont.setFontName("宋体");  
    columnHeadFont.setFontHeightInPoints((short) 10);  
    columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
    columnHeadStyle.setFont(columnHeadFont);  
    columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
    columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中  
    columnHeadStyle.setLocked(true);  
    columnHeadStyle.setWrapText(true);  
    columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色  
    columnHeadStyle.setBorderLeft((short) 1);// 边框的大小  
    columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色  
    columnHeadStyle.setBorderRight((short) 1);// 边框的大小  
    columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体  
    columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色  
    // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）  
    columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);  
    return columnHeadStyle;  
}

public void exportExcel(XSSFWorkbook workBook,HttpServletResponse response, String fileName, 
		String path,Map<String, String> map,String flag) throws Exception {
	String nFilePath = path  + fileName;//"D:/testZip/test11.xlsx"
	int position = fileName.lastIndexOf(".");
	String zipPath = path + fileName.substring(0,position);//"D:/testZip/test11"
	String zipName =  zipPath + ".zip";//"D:/testZip/test11.zip"
	
	//excel导出到path路径下
	saveExcel(workBook, fileName, path);
	//强制修改后缀名为zip
	ZipUtil.changeFileName(nFilePath,zipName);
	//解压
	ZipUtil.zipToFile(zipName, zipPath);
	//删除压缩文件
	File file = new File(zipName);
	if (file.isFile()) { // 判断是否为文件
		file.delete();
	}
	//替换文件
	if("3-5".equals(flag) || "5-4".equals(flag)){
		if(StringUtils.isNotBlank(map.get("cbrbzr"))){
			ZipUtil.copyFile(map.get("cbrbzr"),zipPath+"/xl/media/image1.png");//承包人业务部门部长
		}
		if(StringUtils.isNotBlank(map.get("jlrshr"))){
			ZipUtil.copyFile(map.get("jlrshr"),zipPath+"/xl/media/image2.png");//专业监理工程师
		}
		if(StringUtils.isNotBlank(map.get("zxrshr"))){
			ZipUtil.copyFile(map.get("zxrshr"),zipPath+"/xl/media/image3.png");//造价咨询工点职员
		}
	}else if("3-3".equals(flag) || "3-4".equals(flag)){
		if(StringUtils.isNotBlank(map.get("cbrxmjl"))){
			ZipUtil.copyFile(map.get("cbrxmjl"),zipPath+"/xl/media/image1.png");//承包人项目经理
		}
		if(StringUtils.isNotBlank(map.get("jlrzgcs"))){
			ZipUtil.copyFile(map.get("jlrzgcs"),zipPath+"/xl/media/image2.png");//总监理工程师
		}
		if(StringUtils.isNotBlank(map.get("zxrfzr"))){
			ZipUtil.copyFile(map.get("zxrfzr"),zipPath+"/xl/media/image3.png");//造价咨询工点项目经理
		}
	}else if("2-4".equals(flag)){
		if(StringUtils.isNotBlank(map.get("bdxmb"))){
			ZipUtil.copyFile(map.get("bdxmb"),zipPath+"/xl/media/image1.png");//标段项目部
		}
		if(StringUtils.isNotBlank(map.get("jldw"))){
			ZipUtil.copyFile(map.get("jldw"),zipPath+"/xl/media/image2.png");//监理单位
		}
		if(StringUtils.isNotBlank(map.get("zxdwr"))){
			ZipUtil.copyFile(map.get("zxdw"),zipPath+"/xl/media/image3.png");//咨询单位
		}
	}
    //压缩
	ZipUtil zip = new ZipUtil(zipName);
	zip.compress(zipPath+"/_rels",zipPath+"/docProps",zipPath+"/xl",zipPath+"/[Content_Types].xml");
	//还原名称
	ZipUtil.changeFileName(zipName, nFilePath);
	
	//导出
	//writeAndClose(workBook, response, fileName);
	//exportExcel(response, fileName, nFilePath);
}

public void exportExcel(String nFilePath,Map<String, String> map,String flag) throws Exception {
	//nFilePath   "D:/testZip/test11.xlsx"
	int position = nFilePath.lastIndexOf(".");
	String zipPath = nFilePath.substring(0,position);//"D:/testZip/test11"
	String zipName =  zipPath + ".zip";//"D:/testZip/test11.zip"
	
	//强制修改后缀名为zip
	ZipUtil.changeFileName(nFilePath,zipName);
	//解压
	ZipUtil.zipToFile(zipName, zipPath);
	//删除压缩文件
	File file = new File(zipName);
	if (file.isFile()) { // 判断是否为文件
		file.delete();
	}
	//替换文件
	if("3-2".equals(flag)){
		if(StringUtils.isNotBlank(map.get("cbrxmjl"))){
			ZipUtil.copyFile(map.get("cbrxmjl"),zipPath+"/xl/media/image1.png");//承包人项目经理
		}
		if(StringUtils.isNotBlank(map.get("jlrzgcs"))){
			ZipUtil.copyFile(map.get("jlrzgcs"),zipPath+"/xl/media/image2.png");//总监理工程师
		}
		if(StringUtils.isNotBlank(map.get("zxrfzr"))){
			ZipUtil.copyFile(map.get("zxrfzr"),zipPath+"/xl/media/image3.png");//造价咨询工点项目经理
		}
	}
    //压缩
	ZipUtil zip = new ZipUtil(zipName);
	zip.compress(zipPath+"/_rels",zipPath+"/docProps",zipPath+"/xl",zipPath+"/[Content_Types].xml");
	//还原名称
	ZipUtil.changeFileName(zipName, nFilePath);
	
	//导出
	//writeAndClose(workBook, response, fileName);
	//exportExcel(response, fileName, nFilePath);
}
/**
 * 2007导出
 * @param response
 * @param filename
 * @param path
 * @throws Exception
 */
public void exportExcel(HttpServletResponse response, String filename,String path)  {
	// 创建文件输出流，准备输出电子表格
	try {
		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="+new String(filename.getBytes("GB2312"), "ISO_8859_1")+"");
		InputStream fileInStream = new FileInputStream(path);
		XSSFWorkbook workBook = new XSSFWorkbook(fileInStream);
		if (workBook != null) {
			OutputStream out = response.getOutputStream();
			workBook.write(out);
			if (out != null) {
				out.close();
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
		logger.error(e.getMessage(),e);
	} 
}

/**
 * 本地2003导出 
 * @param response
 * @param filename ***test.xls
 * @param path D:/testZip/***test.xls
 * @throws Exception
 */
public void exportExcel2003(HttpServletResponse response, String filename,String path)  {
	// 创建文件输出流，准备输出电子表格
	try {
		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="+new String(filename.getBytes("GB2312"), "ISO_8859_1")+"");
		InputStream fileInStream = new FileInputStream(path);
		HSSFWorkbook workBook = new HSSFWorkbook(fileInStream);
		if (workBook != null) {
			OutputStream out = response.getOutputStream();
			workBook.write(out);
			if (out != null) {
				out.close();
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
		logger.error(e.getMessage(),e);
	} 
}

public void saveExcel(XSSFWorkbook workBook, String fileName, String path ) {
	if (workBook != null) {
		try {
			createDir(path);
			OutputStream out = new FileOutputStream(path+fileName);
			workBook.write(out);
			if (out != null) {
				out.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
}

public boolean createDir(String destDirName){
	File dir = new File(destDirName);
	if (dir.exists()) {// 判断目录是否存在
		System.out.println("创建目录失败，目标目录已存在！");
		return false;
	}
	if (!destDirName.endsWith(File.separator)) {// 结尾是否以"/"结束
		destDirName = destDirName + File.separator;
	}
	if (dir.mkdirs()) {// 创建目标目录
		System.out.println("创建目录成功！" + destDirName);
		return true;
	} else {
		System.out.println("创建目录失败！");
		return false;
	}
}

}