dianwangAdm/src/main/java/com/minpay/dianwang/util/ReportExcelUtil.java

package com.minpay.dianwang.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSectPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STShd;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;

import com.minpay.common.util.CommonUtil;

import net.sf.json.JSONArray;
 
/**
 * 基于POI的javaEE导出Excel工具类
 * @author zhumq
 * @see POI
 */
public class ReportExcelUtil {
	 /**
	  * @param response 请求
	  * @param fileName 文件名 如："财务报表"
	  * @param excelHeader excel表头数组，存放"姓名#name"格式字符串，"姓名"为excel标题行， "name"为对象字段名
	  * @param dataList 数据集合，需与表头数组中的字段名一致，并且符合javaBean规范
	  * @return 返回一个HSSFWorkbook
	  * @throws Exception
	  */
	 public static <T> HSSFWorkbook export(HttpServletResponse response, String fileName,
			 List<List<String>> dataList, String reportDesc) throws Exception {
		  // 设置请求
		  response.setContentType("application/application/vnd.ms-excel");
		  response.setHeader("Content-disposition",
		    "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
		  // 创建一个Workbook，对应一个Excel文件
		  HSSFWorkbook wb = new HSSFWorkbook();
		  // 在Workbook中添加一个sheet,对应Excel文件中的sheet
		  HSSFSheet sheet = wb.createSheet(fileName);
		  // 遍历集合数据，产生数据行
		  int index = 0;
		  if (!CommonUtil.isEmpty(reportDesc)) {
			  HSSFRow row = sheet.createRow(index);
			  row.setHeight((short)1000);
			  HSSFCell dataCell = row.createCell(0);
			  dataCell.setCellValue(reportDesc);
			  CellRangeAddress region = new CellRangeAddress(0, 0, 0, dataList.get(0).size() - 1);
			  sheet.addMergedRegion(region);
			  index ++;
		  }
		  for (Object dataChild : dataList) {
			  List<Object> dataChildList = (List<Object>)dataChild;
			  
			  HSSFRow row = sheet.createRow(index);
			  for (int i = 0; i < dataChildList.size(); i ++) {
				  String data = CommonUtil.objToString(dataChildList.get(i));
				  HSSFCell dataCell = row.createCell(i);
				  dataCell.setCellValue(data);
			  }
			  index ++;
		  }
//		  sheet.setColumnWidth(fieldArray.length, 20 * 256);
		  return wb;
	 }
	 
	 public static <T> HSSFWorkbook export(HttpServletResponse response, String fileName, String[] excelHeader,
			   Collection<T> dataList) throws Exception {
			  // 设置请求
			  response.setContentType("application/application/vnd.ms-excel");
			  response.setHeader("Content-disposition",
			    "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
			  // 创建一个Workbook，对应一个Excel文件
			  HSSFWorkbook wb = new HSSFWorkbook();
			  // 设置标题样式
			  HSSFCellStyle titleStyle = wb.createCellStyle();
			  // 设置字体样式
			  Font titleFont = wb.createFont();
			  titleFont.setFontHeightInPoints((short) 12); // 字体高度
			  titleFont.setFontName("黑体"); // 字体样式
			  titleStyle.setFont(titleFont);
			  // 在Workbook中添加一个sheet,对应Excel文件中的sheet
			  HSSFSheet sheet = wb.createSheet(fileName);
			  // 标题数组
			  String[] titleArray = new String[excelHeader.length];
			  // 字段名数组
			  String[] fieldArray = new String[excelHeader.length];
			  for (int i = 0; i < excelHeader.length; i++) {
				  String[] tempArray = excelHeader[i].split("#");// 临时数组 分割#
				  titleArray[i] = tempArray[0];
				  fieldArray[i] = tempArray[1];
			  }
			  // 在sheet中添加标题行
			  HSSFRow row = sheet.createRow((int) 0);// 行数从0开始
			  HSSFCell sequenceCell = row.createCell(0);// cell列 从0开始 第一列添加序号
			  sequenceCell.setCellValue("序号");
			  sequenceCell.setCellStyle(titleStyle);
			  sheet.autoSizeColumn(0);// 自动设置宽度
			  // 为标题行赋值
			  for (int i = 0; i < titleArray.length; i++) {
				  HSSFCell titleCell = row.createCell(i + 1);// 0号位被序号占用，所以需+1
				  titleCell.setCellValue(titleArray[i]);
				  titleCell.setCellStyle(titleStyle);
				  sheet.autoSizeColumn(i + 1);// 0号位被序号占用，所以需+1
				  sheet.setColumnWidth(i+1,titleArray[i].getBytes().length*256+200);
			  }
			  // 数据样式 因为标题和数据样式不同 需要分开设置 不然会覆盖
			  HSSFCellStyle dataStyle = wb.createCellStyle();
			  // 设置数据字体
			  Font dataFont = wb.createFont();
			  dataFont.setFontHeightInPoints((short) 10); // 字体高度
			  dataFont.setFontName("宋体"); // 字体
			  dataStyle.setFont(dataFont);
			  // 遍历集合数据，产生数据行
			  Iterator<T> it = dataList.iterator();
			  int index = 0;
			  while (it.hasNext()) {
				  index++;
				  row = sheet.createRow(index);
				  // 为序号赋值
				  HSSFCell sequenceCellValue = row.createCell(0);// 序号值永远是第0列
				  sequenceCellValue.setCellValue(index);
				  sequenceCellValue.setCellStyle(dataStyle);
//					  sheet.autoSizeColumn(0);
				  T t = (T) it.next();
				  Map<String, Object> map = (Map<String, Object> )t;
				  HSSFCell dataCell = null;
				  for (int i = 0; i < fieldArray.length; i++) {
					  dataCell = row.createCell(i + 1);
					  dataCell.setCellStyle(dataStyle);
					  String fieldName = fieldArray[i];
					  String value = CommonUtil.objToString(map.get(fieldName));
					  if (CommonUtil.isEmpty(value)) {
						  dataCell.setCellValue("");					// 为当前列赋值
					  } else {
						  dataCell.setCellValue(CommonUtil.objToString(value));// 为当前列赋值
					  }
					  
				  }
			  }
			  sheet.setColumnWidth(fieldArray.length, 20 * 256);
			  return wb;
		 }
	 
	 public static void write2Docx(HttpServletResponse response, String title, String content, String path, String fileName)throws Exception{
	        XWPFDocument document= new XWPFDocument();
	        //Write the Document in file system
	        File saveFilePath = new File(path);
	        if (!saveFilePath.exists()) {
				saveFilePath.mkdirs();
			}
			
			FileOutputStream out = null;

			try {
				out = new FileOutputStream(new File(path + "\\" + fileName));
				 //添加标题
		        XWPFParagraph titleParagraph = document.createParagraph();
		        //设置段落居中
		        titleParagraph.setAlignment(ParagraphAlignment.CENTER);

		        XWPFRun titleParagraphRun = titleParagraph.createRun();

		        titleParagraphRun.setText(title);
		        titleParagraphRun.setColor("000000");
		        titleParagraphRun.setFontSize(20);

		        String[] paragraphs = content.split(System.getProperty("line.separator"));
		        for (int i = 0; i < paragraphs.length; i ++) {
			        //段落
			        XWPFParagraph firstParagraph = document.createParagraph();
			        XWPFRun run = firstParagraph.createRun();
			        run.setText(paragraphs[i]);
			        run.setColor("696969");
			        run.setFontSize(16);
		        }

		        document.write(out);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				out.close();
			}
			OutputStream toClient = null;
			InputStream fis = null;
			try {
				fis = new BufferedInputStream(new FileInputStream(path + "\\" + fileName));
				byte[] buffer = new byte[fis.available()];
				fis.read(buffer);
				   
				// 清空response
				response.reset();
				// 设置response的Header
				response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
				toClient = new BufferedOutputStream(response.getOutputStream());
				response.setContentType("application/octet-stream");
				toClient.write(buffer);
				toClient.flush();
			} catch (Exception e) {
				e.printStackTrace(); 
			} finally {
				fis.close();
				toClient.close();
			}
			
			// 操作成功后删除本地文件
			new File(path + "\\" + fileName).delete();
	    }
}