java中execl导入与导出

package com.icos.utility.utils;

import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.CellView;
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;

public class ExcelUtil {
	

	 public static void export(String sheetname,String filename, String[] title,List list, HttpServletResponse response) throws Exception {
		try {
			OutputStream os = response.getOutputStream();
			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
			WritableSheet wsheet = wbook.createSheet(sheetname, 0); // 工作表名称
			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.NO_BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			//设置首标题格式 
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);
			titleFormat.setBackground(jxl.format.Colour.AQUA);
			titleFormat.setAlignment(Alignment.CENTRE);
			titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			for (int i = 0; i < title.length; i++) { 
				Label excelTitle = new Label(i, 0, title[i], titleFormat);
				wsheet.addCell(excelTitle);
			}  
			filename=URLEncoder.encode(filename,"GB2312"); 
			filename=URLDecoder.decode(filename, "ISO8859_1");
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=" + filename);
			for (int i = 0; i < list.size(); i++) {
				List slist = (List) list.get(i);
				for (int j = 0; j < slist.size(); j++) {
					if ("".equals(slist.get(j)) || "null".equals(slist.get(j))
							|| null == slist.get(j)) {
						Label content = new Label(j, i + 1, null);
						wsheet.addCell(content);
					} else {
						Label content = new Label(j, i + 1, slist.get(j)
								.toString());
						wsheet.addCell(content);
					}
				}
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("导出文件出错");
		}

	}
	 public static void exportMultiSheet(String sheetname,String filename, String[] title,List list, HttpServletResponse response) throws Exception {
		 final int MAXROWS = 50000;
		 final int MAX_COLUMN_LENGTH = 55;
		try {
			OutputStream os = response.getOutputStream();
			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
			if(list != null && list.size() != 0 ){
				int sheetNum = list.size() % MAXROWS == 0 ? list.size() / MAXROWS : list.size() / MAXROWS + 1;
				for (int num = 1; num < sheetNum + 1; num++) {
					List subList = list.subList((num - 1) * MAXROWS, num * MAXROWS > list.size() ? list.size() : num * MAXROWS);
					String sheetnameStr = sheetname;
					if (sheetNum > 1) {
						sheetnameStr = sheetname + "_" + num;
					}
					WritableSheet wsheet = wbook.createSheet(sheetnameStr, num - 1); // 工作表名称
					// 设置Excel字体
					WritableFont titlefont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
					// 设置首标题格式
					WritableCellFormat titleFormat = new WritableCellFormat(titlefont);
					titleFormat.setBackground(jxl.format.Colour.AQUA);
					titleFormat.setAlignment(Alignment.CENTRE);
					titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
					titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
					//给所有的列设置默认的列的宽度;  
					//wsheet.getSettings().setDefaultColumnWidth(15);  
					//初始化最大列宽数组
					int[] columnLength = new int[ title.length ]; 
					//插入title内容
					for (int i = 0; i < title.length; i++) {
						Label excelTitle = new Label(i, 0, title[i], titleFormat);
						wsheet.addCell(excelTitle);
						//有更大的字符串长度，则取更大的长度
						int length = title[i].getBytes().length + 2;//中文字符算两个字节
						columnLength[i] =  columnLength[i] < length ? length : columnLength[i] ;
						if( columnLength[i] > MAX_COLUMN_LENGTH ){ columnLength[i] = MAX_COLUMN_LENGTH; }
					}
					wsheet.setRowView(0, 500);//设置行高  
					// 设置正文格式
					WritableFont wfont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
					WritableCellFormat mainFormat = new WritableCellFormat(wfont);
					mainFormat.setAlignment(Alignment.CENTRE);
					mainFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
					mainFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);	
					//插入正文内容
					for (int i = 0; i < subList.size(); i++) {
						List slist = (List) subList.get(i);
						for (int j = 0; j < slist.size(); j++) {  
							if ("".equals(slist.get(j)) || "null".equals(slist.get(j)) || null == slist.get(j)) {
								Label content = new Label(j, i + 1, null, mainFormat);
								wsheet.addCell(content);
							} else {
								Label content = new Label(j, i + 1, slist.get(j).toString(), mainFormat );
								wsheet.addCell(content);
								//有更大的字符串长度，则取更大的长度
								int length = slist.get(j).toString().getBytes().length + 2;
								columnLength[j] =  columnLength[j] < length ? length : columnLength[j] ;
								if( columnLength[j] > MAX_COLUMN_LENGTH ){ columnLength[j] = MAX_COLUMN_LENGTH; }
							}
						}
						wsheet.setRowView(i+1, 500);//设置行高  
					}
					//设置列宽
					for(int i=0; i<columnLength.length; i++){
						wsheet.setColumnView(i, columnLength[i]);
					}
				}
			}else{//如果没有数据，则只把title写上
				WritableSheet wsheet = wbook.createSheet(sheetname, 0); // 工作表名称
				// 设置Excel字体
				WritableFont titlefont = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
				// 设置首标题格式
				WritableCellFormat titleFormat = new WritableCellFormat(titlefont);
				titleFormat.setBackground(jxl.format.Colour.AQUA);
				titleFormat.setAlignment(Alignment.CENTRE);
				titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); 
				//初始化最大列宽数组
				int[] columnLength = new int[ title.length ]; 
				//插入title内容
				for (int i = 0; i < title.length; i++) {
					Label excelTitle = new Label(i, 0, title[i], titleFormat);
					wsheet.addCell(excelTitle);
					int length = title[i].getBytes().length + 2;
					if( length > MAX_COLUMN_LENGTH ){ length = MAX_COLUMN_LENGTH; }
					wsheet.setColumnView(i, length );
				}
				wsheet.setRowView(0, 500);//设置行高  
			}
			
			filename=URLEncoder.encode(filename,"GB2312"); 
			filename=URLDecoder.decode(filename, "ISO8859_1");
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=" + filename);
			// 写入文件
			wbook.write(); 
			wbook.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("导出文件出错");
		}

	}
	

}