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;
}
}
}