package com.icos.util;
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("导出文件出错");
}
}
}
