Java导出Excel表格

Java导出Excel，有两个步骤：一、生成Excel；二、下载Excel。

所需jar包：http://download.youkuaiyun.com/detail/mijinghjb/7522507

 代码示例：

/**
 * 生成Excel
 * @author hujianbin
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.POIXMLProperties;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.http.MediaType;

import cn.com.screendata.common.util.FileUtil;


public class ExcelTest {
	/**
	 * 生成Excel
	 * @param dataList
	 * @param titleList
	 * @param dir
	 * @param sheetName
	 * @return fileName 
	 */
	public static String exportExcelFile(List<String[]> dataList,List<String> titleList,String dir,String sheetName){
		String fileName=null;
		FileOutputStream fos=null;
		try {
			fileName=UUID.randomUUID().toString()+".xlsx";
			Workbook wb=new SXSSFWorkbook(2000);
			POIXMLProperties props=((SXSSFWorkbook)wb).getXSSFWorkbook().getProperties();
			POIXMLProperties.CoreProperties coreProps=props.getCoreProperties();
			coreProps.setCreator("");
			Sheet sheet=wb.createSheet(sheetName);
			Row row=sheet.createRow(0);
			CellStyle titleStyle=wb.createCellStyle();
			Font titleFont=wb.createFont();
			titleFont.setFontName("黑体");
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);
			titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
			titleStyle.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());
			titleStyle.setFillPattern(CellStyle.BIG_SPOTS);
			for(int i=0;i<titleList.size();i++){//写标题
				Cell cell=row.createCell(i);
				cell.setCellStyle(titleStyle);
				cell.setCellValue(titleList.get(i));
			}
			for(int i=0;i<dataList.size();i++){//写数据
				row=sheet.createRow(i+1);
				String[] obj=dataList.get(i);
				for (int j = 0; j < obj.length; j++) {
					String object = obj[j];
					Cell cell=row.createCell(j);
					cell.setCellValue(object);
				}
			}
			for(int i=0;i<titleList.size();i++){
				sheet.autoSizeColumn(i);
			}
			fos=new FileOutputStream(dir+fileName);
			wb.write(fos);
		} catch (Exception e) {
			fileName=null;
			e.printStackTrace();
		} finally{
			try {
				if(fos!=null){
					fos.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return fileName;
	}
	
	/**
	 * 下载Excel
	 * @param downfile
	 * @param request
	 * @param response
	 * @param filename
	 * @param isDownload
	 */
	public static void writeFile(File downfile, HttpServletRequest request, HttpServletResponse response, String filename,boolean isDownload) {

		if (downfile != null && downfile.exists()) {
			if (downfile.getName().toLowerCase().endsWith(".gif")) {
				response.setContentType(MediaType.IMAGE_GIF_VALUE);
			} else if (downfile.getName().toLowerCase().endsWith(".jpg")) {
				response.setContentType(MediaType.IMAGE_JPEG_VALUE);
			} else if (filename.toLowerCase().endsWith(".jpg")) {
				response.setContentType(MediaType.IMAGE_JPEG_VALUE);
			} else if (downfile.getName().toLowerCase().endsWith(".jpeg")) {
				response.setContentType(MediaType.IMAGE_JPEG_VALUE);
			} else if (downfile.getName().toLowerCase().endsWith(".png")) {
				response.setContentType(MediaType.IMAGE_PNG_VALUE);
			} else if (downfile.getName().toLowerCase().endsWith(".flv")) {
				response.setContentType("video/flv");
			} else if (downfile.getName().toLowerCase().endsWith(".mp4")) {
				response.setContentType("video/mp4");
			} else if (downfile.getName().toLowerCase().endsWith(".mov")) {
				response.setContentType("video/mov");
			} else if (downfile.getName().toLowerCase().endsWith(".3gp")) {
				response.setContentType("video/3gp");
			} else if (downfile.getName().toLowerCase().endsWith(".ogg")) {
				response.setContentType("video/ogg");
			} else {
				response.setContentType("application/download");
				response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
			}
			
			response.setHeader("Content-Length", "" + downfile.length());
			response.setHeader("Content-Disposition", "inline; filename=\"" + filename + "\"");

			response.setHeader("Accept-Ranges", "bytes");

			if(isDownload){
				response.setContentType("application/download");
				response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
			}
			long start = 0;
			long end = 0;
			end = downfile.length();
			
			response.setHeader("Content-Length", new Long(end - start).toString());


			FileInputStream fin = null;

			try {
				byte buff[] = new byte[1024];
				fin = new FileInputStream(downfile);
				int rsize = 0;
				int totalrsize = (int) (end - start);
				fin.skip(start);
				while (totalrsize > 0 && (rsize = fin.read(buff, 0, Math.min(1024, totalrsize))) > 0) {
					response.getOutputStream().write(buff, 0, rsize);
					totalrsize -= rsize;
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (fin != null) {
					try {
						fin.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	public static void main(String[] args) {
		//Excel表内容信息
		List<String[]> dataList = new ArrayList<String[]>();
		String[] dataContent1 = new String[6];
		dataContent1[0] = "谢逊";
		dataContent1[1] = "金毛狮王";
		dataContent1[2] = "无";
		dataContent1[3] = "狮吼功/七伤拳";
		dataContent1[4] = "5000";
		dataContent1[5] = "-1000";
		dataList.add(dataContent1);
		
		String[] dataContent2 = new String[6];
		dataContent2[0] = "慕容复";
		dataContent2[1] = "北慕容";
		dataContent2[2] = "燕王室之后";
		dataContent2[3] = "斗转星移";
		dataContent2[4] = "3000";
		dataContent2[5] = "-500";
		dataList.add(dataContent2);
		
		String[] dataContent3 = new String[6];
		dataContent3[0] = "乔峰";
		dataContent3[1] = "南乔峰";
		dataContent3[2] = "丐帮";
		dataContent3[3] = "降龙十八掌";
		dataContent3[4] = "10000";
		dataContent3[5] = "5000";
		dataList.add(dataContent3);
		
		
		//Excel表头信息
		List<String> titleList = new ArrayList<String>();
		titleList.add("姓名");
		titleList.add("外号");
		titleList.add("门派");
		titleList.add("擅长武功");
		titleList.add("江湖声望");
		titleList.add("侠义值");
		
		//生成路径及Excel sheet名
		String dir = "F:/data/excel/";
		String sheetName = "武林大会名单";
		//生成Excel
		String fileName = FileUtil.exportExcelFile(dataList, titleList, dir, sheetName);
		//下载Excel
		String filePath = "F:/dats/download/"+fileName;
		File downfile = new File(filePath);
		//以下调用得在Web容器环境下生效
		//FileUtil.writeFile(downfile, request, response, fileName, true);
	}
}