java execl 导出_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

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("导出文件出错");

}

}

}