meidu2021/daju/target/classes/cn/com/lzt/common/util/ExportExcel.java

package cn.com.lzt.common.util;

import cn.com.lzt.common.excel.ExportExcelBean;
import cn.com.lzt.common.excel.ZipCompressorByAnt;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * 导出Excel公共方法
 *
 */
@SuppressWarnings("deprecation")
public class ExportExcel {

	private static final Logger log = Logger.getLogger(ExportExcel.class);
	// 显示的导出表的标题
	private String title;
	// 导出表的列名
	private Object[] rowName;
	// 导出的实体类对应的属性名称
	private Object[] attributeName;
	// 导出数据
	private List<Object> dataList = new ArrayList<Object>();
	// 导出excel表明
	private String excelName;
	// 构造方法，传入要导出的数据
	public ExportExcel(String title, Object[] rowName, String excelName,List<Object> dataList,Object[] attributeName) {
		this.dataList = dataList;
		this.rowName = rowName;
		this.excelName = excelName;
		this.title = title;
		this.attributeName = attributeName;
	}

	public ExportExcel(){
		
	}
	/*
	 * 导出数据
	 */
	public void export(HttpServletResponse response) throws Exception {
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
			// 产生表格标题行
			HSSFRow rowm = sheet.createRow(0);
			HSSFCell cellTiltle = rowm.createCell(0);
			// sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
			HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象
			HSSFCellStyle style = this.getStyle(workbook); // 单元格样式对象
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
					(rowName.length - 1)));
			cellTiltle.setCellStyle(columnTopStyle);
			cellTiltle.setCellValue(title);
			// 定义所需列数
			int columnNum = rowName.length;
			HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
			// 将列头设置到sheet的单元格中
			for (int n = 0; n < columnNum; n++) {
				HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(CellType.STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(rowName[n]!=null?rowName[n].toString():"");
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}
			
			// 将查询出的数据设置到sheet对应的单元格中
			if (dataList !=null && dataList.size() > 0){
				for (int i = 0; i < dataList.size(); i++) {
					Object object = dataList.get(i);// 遍历每个对象
					HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数
					Class<?> cla = object.getClass();
					if (cla.isAssignableFrom(java.util.HashMap.class)){
						/*if (rowName !=null && rowName.length > 0) {
							for (int n = 0; n < columnNum; n++) {
								HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
								cellRowName.setCellType(CellType.STRING); // 设置列头单元格的数据类型
								HSSFRichTextString text = new HSSFRichTextString(rowName[n]!=null?rowName[n].toString():"");
								cellRowName.setCellValue(text); // 设置列头单元格的值
								cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
							}
						}*/
						@SuppressWarnings("unchecked")
						Map<String,Object> map = (Map<String,Object>)object;
						Object[] keys = map.keySet().toArray();
						if(attributeName != null){
							 keys = attributeName;
						}
						//Object[] keys = attributeName;
						//log.info("........"+JSON.toJSON(keys));
						for (int j = 0; j < keys.length; j++) {
							HSSFCell cell = null; // 设置单元格的数据类型
							
							cell = row.createCell(j, CellType.STRING);
							if (map.get(keys[j]) !=null){
								cell.setCellValue(map.get(keys[j]).toString()); // 设置单元格的值
							}else{
								cell.setCellValue("");
							}
							cell.setCellStyle(style); // 设置单元格样式
						} 
					}else if (cla.isAssignableFrom(java.lang.Object[].class)){
						Object[] obj = (Object[])object;
						for (int j = 0; j < obj.length; j++) {
							HSSFCell cell = null; // 设置单元格的数据类型
							cell = row.createCell(j, CellType.STRING);
							if (obj[j] != null) {
								cell.setCellValue(obj[j].toString()); // 设置单元格的值
							}else{
								cell.setCellValue("");
							}
							cell.setCellStyle(style); // 设置单元格样式
						}
					}else{
						if (attributeName !=null && attributeName.length > 0){
							for (int j = 0; j < attributeName.length; j++) {
								String fdname = attributeName[j].toString();
								StringBuffer sb = new StringBuffer(fdname);
								if (!Character.isUpperCase(sb.charAt(1)))
									sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
								Method metd = cla.getMethod("get"+sb.toString(), null);
								Object value = metd.invoke(object, null);
								HSSFCell cell = null; // 设置单元格的数据类型
								cell = row.createCell(j, CellType.STRING);
								if (value !=null) {
									cell.setCellValue(value.toString()); // 设置单元格的值
								}else{
									cell.setCellValue("");
								}
								cell.setCellStyle(style); // 设置单元格样式
							}
						}else{
							Field[] fds = cla.getDeclaredFields();
							for (int j = 0; j < fds.length; j++) {
								Field field = fds[j];
								String fdname = field.getName();
								StringBuffer sb = new StringBuffer(fdname);
								if (!Character.isUpperCase(sb.charAt(1)))
									sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
								Method metd = cla.getMethod("get"+sb.toString(), null);
								Object value = metd.invoke(object, null);
								HSSFCell cell = null; // 设置单元格的数据类型
								cell = row.createCell(j, CellType.STRING);
								if (value !=null) {
									cell.setCellValue(value.toString()); // 设置单元格的值
								}else{
									cell.setCellValue("");
								}
								cell.setCellStyle(style); // 设置单元格样式
							}
						}
					}
				}
			}
			// 让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
				int columnWidth = sheet.getColumnWidth(colNum) / 256;
				for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
					HSSFRow currentRow;
					// 当前行未被使用过
					if (sheet.getRow(rowNum) == null) {
						currentRow = sheet.createRow(rowNum);
					} else {
						currentRow = sheet.getRow(rowNum);
					}
					if (currentRow.getCell(colNum) != null) {
						try {
							HSSFCell currentCell = currentRow.getCell(colNum);
							if (currentCell.getCellType() == CellType.STRING) {
								int length = 0;
								if (StringUtils.isNotEmpty(currentCell.getStringCellValue())){
									length = currentCell.getStringCellValue().getBytes().length;
								}
								if (columnWidth < length) {
									columnWidth = length;
								}
							}
						} catch (Exception e) {
							//undo
						}
					}
				}
				if (colNum == 0) {
					sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
				} else {
					sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
				}
			}
			if (workbook != null) {
				OutputStream out = response.getOutputStream();
				try {
//					String fileName = "Excel-"
//							+ String.valueOf(System.currentTimeMillis())
//									.substring(4, 13) + ".xls";
					String fileName = URLEncoder.encode(excelName + ".xls","utf-8");
					String headStr = "attachment; filename=\"" + fileName
							+ "\"";
					response.setContentType("application/vnd.ms-excel");
					response.setHeader("Content-Disposition", headStr);
					workbook.write(out);
				} catch (IOException e) {
					e.printStackTrace();
				}finally{
					if (out !=null){
						out.close();
						response.flushBuffer();
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
  public void toExcel(List<ExportExcelBean> eList,HttpServletRequest request,OutputStream out) throws IOException{
	 // List<String> fileNames = new ArrayList<String>();// 用于存放生成的文件名称s  
    //  File zip = new File(request.getRealPath("/files") + "/" + "fff" + ".zip");// 压缩文件  
      String dir = "excels";
      String basePath = request.getSession().getServletContext().getRealPath(""); 
      String excelsPath = basePath.substring(0,basePath.lastIndexOf(File.separator))+File.separator+dir;
      //log.info("获取的路径>>>>>"+excelsPath);
   
        File rDir = new File(excelsPath);
		if (!rDir.exists()){
			rDir.mkdirs();
		}
      for (int i = 0; i < eList.size(); i++) {
    	  HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
    	  String title2 = eList.get(i).getTableName()+"查询";
  		  HSSFSheet sheet = workbook.createSheet(title2); // 创建工作表
  		  
  		 String file =  excelsPath+ "/" 
  		 +eList.get(i).getCombName()+"-"
  		 +eList.get(i).getTableName()+"-"
  		 +eList.get(i).getExportTime()
  		 + ".xls";  
  		  
        // fileNames.add(file);  
           
         FileOutputStream o = new FileOutputStream(file);  
  		  
  		// 产生表格标题行
  		  HSSFRow rowm = sheet.createRow(0);
  		  HSSFCell cellTiltle = rowm.createCell(0);
  		  // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
  		  HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象
  		  HSSFCellStyle style = this.getStyle(workbook); // 单元格样式对象
  		  
  		  int rowLeng = eList.get(i).getRows().length;
  		  sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,(rowLeng - 1)));
  		  cellTiltle.setCellStyle(columnTopStyle);
  		  cellTiltle.setCellValue(title2);
  		  
  		// 定义所需列数
  		 int columnNum = rowLeng;
  		 // int columnNum = rowName.length;
  		  HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
  		// 将列头设置到sheet的单元格中
  		  for (int n = 0; n < columnNum; n++) {
	  			HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
	  			cellRowName.setCellType(CellType.STRING); // 设置列头单元格的数据类型
	  			HSSFRichTextString text = new HSSFRichTextString(eList.get(i).getRows()[n]!=null?eList.get(i).getRows()[n].toString():"");
	  			cellRowName.setCellValue(text); // 设置列头单元格的值
	  			cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
  		  }
  		  
  		List<Object> dataList2 = eList.get(i).getObjList();
  		if (dataList2!=null&&dataList2.size()>0) {
			for (int j = 0; j < dataList2.size(); j++) {
				Object object = dataList2.get(j);// 遍历每个对象
				HSSFRow row = sheet.createRow(j + 3);// 创建所需的行数
				Class<?> cla = object.getClass();
				if (cla.isAssignableFrom(java.util.HashMap.class)){
					@SuppressWarnings("unchecked")
					Map<String,Object> map = (Map<String,Object>)object;
					Object[] keys = eList.get(i).getAttrs();
				
					for (int k = 0; k < keys.length; k++) {
						HSSFCell cell = null; // 设置单元格的数据类型
						cell = row.createCell(k, CellType.STRING);
						if (map.get(keys[k]) !=null){
							cell.setCellValue(map.get(keys[k]).toString()); // 设置单元格的值
						}else{
							cell.setCellValue("");
						}
						cell.setCellStyle(style); // 设置单元格样式
					} 
				}
			}
		}
  	// 让列宽随着导出的列长自动适应
		for (int colNum = 0; colNum < columnNum; colNum++) {
			int columnWidth = sheet.getColumnWidth(colNum) / 128;
			for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
				HSSFRow currentRow;
				// 当前行未被使用过
				if (sheet.getRow(rowNum) == null) {
					currentRow = sheet.createRow(rowNum);
				} else {
					currentRow = sheet.getRow(rowNum);
				}
				if (currentRow.getCell(colNum) != null) {
					try {
						HSSFCell currentCell = currentRow.getCell(colNum);
						if (currentCell.getCellType() == CellType.STRING) {
							int length = 0;
							if (StringUtils.isNotEmpty(currentCell.getStringCellValue())){
								length = currentCell.getStringCellValue().getBytes().length;
							}
							if (columnWidth < length) {
								columnWidth = length;
							}
						}
					} catch (Exception e) {
						//undo
					}
				}
			}
			if (colNum == 0) {
				sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
			} else {
				sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
			}
		}
	
		if (workbook!=null) {
			workbook.write(o);  
			o.close();
		}
      }
      
      ZipCompressorByAnt zca = new ZipCompressorByAnt(rDir.getAbsolutePath()+".zip");  
	  int isZip = zca.compress(rDir.getAbsolutePath());
      if (isZip==0) {
    	  File file = new File(rDir.getAbsolutePath()+".zip");
    	  FileInputStream inStream = new FileInputStream(file);  
	        byte[] buf = new byte[4096];  
	        int readLength;  
	        while (((readLength = inStream.read(buf)) != -1)) {  
	            out.write(buf, 0, readLength);  
	        }  
	        inStream.close();  
	        out.close();
      }
      
      deleteDir(rDir);
      File file = new File(rDir.getAbsolutePath()+".zip");
      file.delete();
     
  }
	
  
  
	  private static boolean deleteDir(File dir) {
	      if (dir.isDirectory()) {
	          String[] children = dir.list();
	          //递归删除目录中的子目录下
	          for (int i=0; i<children.length; i++) {
	              boolean success = deleteDir(new File(dir, children[i]));
	              if (!success) {
	                  return false;
	              }
	          }
	      }
	      // 目录此时为空，可以删除
	      return dir.delete();
	  }
  
  
	/*
	 * 列头单元格样式
	 */
	public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
		// 设置字体
		HSSFFont font = workbook.createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 11);
		// 字体加粗
		font.setBold(true);
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(BorderStyle.THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(IndexedColors.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(BorderStyle.THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(IndexedColors.BLACK.index);
		// 设置右边框;
		style.setBorderRight(BorderStyle.THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(IndexedColors.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(BorderStyle.THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(IndexedColors.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setAlignment(HorizontalAlignment.CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		return style;
	}

	/*
	 * 列数据信息单元格样式
	 */
	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		// 设置字体
		HSSFFont font = workbook.createFont();
		// 设置字体大小
		// font.setFontHeightInPoints((short)10);
		// 字体加粗
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(BorderStyle.THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(IndexedColors.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(BorderStyle.THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(IndexedColors.BLACK.index);
		// 设置右边框;
		style.setBorderRight(BorderStyle.THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(IndexedColors.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(BorderStyle.THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(IndexedColors.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HorizontalAlignment.CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		return style;
	}
	
}