Java Excel 通用导出

最近因为数据迁移问题 ，需要用到excel 导入导出 。

就看了一下这方面的资料，感觉以后肯定还要用，就趁着有点时间就把先通用的导出搞出来。

通用的导入后期有时间也会发布出来，这就是这篇文章文章的由来，废话不多说，直接贴代码。

 

 

	@ResponseBody
	@RequestMapping(value = "/exprotExcel", method = { RequestMethod.POST })
	public void ExprotExcel(HttpServletResponse rep) {
		Criteria<SysResource> param = new Criteria<SysResource>();
		List<Map<String, Object>> list = sysResourceService.queryPage(param);
		
		String[] heads = {"资源名称=resourceName","资源编号=resourceId","资源类型=resourceTypeZh","资源路径=url","创建时间=createTime","创建人=createBy","父资源编号=parentResourceId","父资源名称=parentResourceName"	};
		int dateColumnIndex = 4;
		String fileName = "测试文件.xls";
		
		//调用通用导出工具
		ExportExcelUntil.exprotExcel(list,fileName,rep,heads,dateColumnIndex);
	}

上面是Controller 的调用 

 

 

 

package com.xiong.weixin.untils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
 * 
 * @author XiongYC
 * @date 2017年10月19日
 *
 */
public class ExportExcelUntil{
	
	private static final String UTF_8 = "UTF-8";
	private static final String ISO8859_1 = "ISO8859-1";
	private static final String YYYY_M_DDHHMMSS = "yyyy-M-dd HH:mm:ss";
	private static final String YYYYMDDHHMMSS = "yyyyMddHHmmss";
	
//	public static void main(InputStream fis) throws Exception {
//		outPrint(inputPrint());
//		inputPrint(fis);
//	}

//	private static List<Model> inputPrint(InputStream fis ) {
//		List<Model> modelList = null;
//		Model model = null;
//		try {
			InputStream fis = new FileInputStream("D:\\222.xls");
//			POIFSFileSystem fileSystem = new POIFSFileSystem(fis);
//			// 创建Excel工作薄
//			HSSFWorkbook wb = new HSSFWorkbook(fileSystem);
//			// 得到第一个工作頁
//			HSSFSheet sheet = wb.getSheetAt(0);
//			if (sheet != null){
//				modelList = new ArrayList<Model>();
//				//遍历多少行
//				for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
//					HSSFRow hssfRow = sheet.getRow(rowNum);
//					if (hssfRow == null){
//						continue;
//					}
//					model = new Model();
//					//遍历一行有多少列
//					for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
//						HSSFCell hssfCell = hssfRow.getCell(cellNum);
//						if (hssfCell == null){
//							continue;
//						}
//						if(cellNum == 0){
//							model.setDistributor(getVal(hssfCell));
//						}else if (cellNum == 1){
//							model.setStartCode(getVal(hssfCell));
//						}else{
//							model.setStopCode(getVal(hssfCell));
//						}
//					}
//					modelList.add(model);
//				}
//			}
//		} catch (Exception e) {
//		}
//		return modelList;
//		
//	}

//	private static String getVal(HSSFCell hssfCell) {
//		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//			return hssfCell.getStringCellValue();
//		} else {
//			return String.valueOf(hssfCell.getNumericCellValue());
//		}
//	}

//	private static void outPrint(List<Model> modelList) throws Exception {
//		Workbook wb = new HSSFWorkbook();
//		String str = "测试";
//		Sheet sheet;
//		Row row;
//		for (int i = 1; i < modelList.size(); i++) {
//			sheet =wb.createSheet(""+i);
//			
//			row = sheet.createRow(0);
//			row.createCell(0).setCellValue("经销商");
//			row.createCell(1).setCellValue("号码");
//			
//			String  distributor = modelList.get(i).getDistributor();
//			String  stopCode = modelList.get(i).getStopCode();
//			String  startCode = modelList.get(i).getStartCode();
//			int temp = Integer.valueOf(stopCode)- Integer.valueOf(startCode)+1;
//			
//			for (int j = 0; j <= temp; j++) {
//				row = sheet.createRow(j+1);
//				row.createCell(0).setCellValue(distributor);
//				row.createCell(1).setCellValue(Integer.valueOf(startCode)-1+j);
//			}
//		}
//		FileOutputStream fos = new FileOutputStream("D:\\测试.xls");
//		wb.write(fos);
//		fos.close();
//	}

	/**
	 * 导出excel
	 * @param list
	 * @param fileName
	 * @param rep
	 * @param heads
	 * @param dateColumnIndex
	 */
	public static void exprotExcel(List<Map<String, Object>> list,
			String fileName, HttpServletResponse rep, String[] heads,
			int dateColumnIndex) {
		
		fileName = new SimpleDateFormat(YYYYMDDHHMMSS).format(new Date())+fileName;
		
		Workbook wb = new HSSFWorkbook();
		Row row;
		Cell cell;
		if(list.size()>-1){
			Sheet sheet= wb.createSheet();
		    CellStyle  cellStyle = wb.createCellStyle();
			//创建标题行
			row = sheet.createRow(0);
			for (int i = 0; i < heads.length; i++) {
				cell = row.createCell(i);
				
				//暂时解决字段自适应列宽度中文不友好问题
				cell.setCellValue("         "+heads[i].split("=")[0]+"         ");
				cell.setCellStyle(cellStyle(wb,cellStyle ));
			}
			
			//写入数据
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i+1);
				for (int j = 0; j < heads.length; j++) {
					cell = row.createCell(j);
					
					String param = null;
					
					for (int k = 0; k < heads.length; k++) {
						
						if(j == k){
							if(k== dateColumnIndex){
								param = new SimpleDateFormat(YYYY_M_DDHHMMSS).format(list.get(i).get(heads[j].split("=")[1]));
							}else{
								param = String.valueOf(list.get(i).get(heads[j].split("=")[1]));
							}
						}

					}
					cell.setCellValue(param);
					
					cell.setCellStyle(cellStyle(wb,cellStyle ));
				}
			}
			
			//自适应列宽度
			for (int i = 0; i < heads.length; i++) {
//				if (i == 0) {
//					sheet.setColumnWidth(i, heads[i].split("=")[0].getBytes().length*2*256);
//				} else {
					sheet.autoSizeColumn(i,true);
//				}
			}

		}
		
		//输出到浏览器
		out(wb,rep,fileName);
	}
	
	
	/**
	 * 单元格样式
	 * @param wb
	 * @return
	 */
	private static CellStyle cellStyle(Workbook wb,CellStyle cellStyle) {
		// 居中 
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		return cellStyle;
	}
	
	/**
	 * 输出到浏览器
	 * @param wb
	 * @param rep
	 * @param fileName
	 */
	private static void out(Workbook wb, HttpServletResponse rep,
			String fileName) {
		OutputStream os = null ;
	    try {
			rep.setHeader("Content-Disposition", "attachment;filename="  + new String(fileName.getBytes(UTF_8),ISO8859_1));
			rep.setContentType("application/vnd.ms-excel;charset=utf-8");
			os = rep.getOutputStream();
			wb.write(os);
			os.flush();
//			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				if(os!=null){
						os.close();
				 }
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
}

//class Model implements Serializable{
//	/**
//	 * 
//	 */
//	private static final long serialVersionUID = -7003708043162396435L;
//	private String distributor;
//	private String startCode;
//	private String stopCode;
//	public String getDistributor() {
//		return distributor;
//	}
//	public void setDistributor(String distributor) {
//		this.distributor = distributor;
//	}
//	public String getStartCode() {
//		return startCode;
//	}
//	public void setStartCode(String startCode) {
//		this.startCode = startCode;
//	}
//	public String getStopCode() {
//		return stopCode;
//	}
//	public void setStopCode(String stopCode) {
//		this.stopCode = stopCode;
//	}
//	
//}

这是导出代码。注解的是导入导出 一开始自己随便写的case。如果需要 也可以看一下。