public class JxlExcelExport{
private Logger logger = Logger.getLogger(this.getClass());
private int[] createHeader(WritableSheet sheet,String[] attributes)
throws RowsExceededException, WriteException {
int[] columnWidth = new int[attributes.length];
WritableCellFormat cellFormat = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false));
cellFormat.setAlignment(Alignment.CENTRE);
Label label;
for(int i = 0; i < attributes.length; i++) {
label = new Label(i, 0, attributes[i], cellFormat);
sheet.addCell(label);
// Set column width
columnWidth[i] = (int) (attributes[i].length() * 1.5);
}
return columnWidth;
}
private int[] createBody(WritableSheet sheet,
List list,
String[] attributes,
Class[] types,
int[] columnWidth,
int currRow)
throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
Iterator it = list.iterator();
Object vo;
Object obj;
Label label;
Number number;
DateTime dateCell;
int rowIndex = currRow;
DateFormat customDateFormat = new DateFormat("MM/dd/yyyy HH:mm");
while(it.hasNext()) {
vo = it.next();
for(int i = 0; i < attributes.length; i++) {
obj = BeanUtilsBean.getInstance().getProperty(vo, attributes[i]);
obj = (obj == null)? "" : obj;
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
if(types[i].equals(Date.class)) {
if(obj instanceof Date) {
// Format date
dateCell = new DateTime(i, rowIndex, (Date)obj, new WritableCellFormat(customDateFormat));
sheet.addCell(dateCell);
columnWidth[i] = checkMaxWidth(columnWidth[i], dateCell.getBytes().length);
} else {
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
columnWidth[i] = checkMaxWidth(columnWidth[i], obj.toString().length());
}
} else if(types[i].equals(Integer.class)) {
// Set number cell
number = new Number(i, rowIndex, new Double((String)obj).doubleValue());
sheet.addCell(number);
columnWidth[i] = checkMaxWidth(columnWidth[i], obj.toString().length());
} else {
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
columnWidth[i] = checkMaxWidth(columnWidth[i], StringUtils.trim(obj.toString()).length());
}
}
rowIndex ++;
}
return columnWidth;
}
private void autoWidthColumns(WritableSheet sheet, int[] columnWidth) {
for(int i = 0; i < columnWidth.length; i++) {
sheet.setColumnView(i, columnWidth[i]);
}
}
private int checkMaxWidth(int orignWidth, int comparedWdith) {
// Column width will be different for font family
return (int) (orignWidth > comparedWdith * 1.2? orignWidth : comparedWdith * 1.2);
}
public void export(HttpServletRequest request,
HttpServletResponse response,
User user,
String[] propertyNames,
Class[] types,
List list,
String fileName){
WritableWorkbook workbook = null;
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="+ fileName +".xls");
workbook = Workbook.createWorkbook(response.getOutputStream());
WritableSheet sheet = workbook.createSheet(fileName, 0);
int[] columnWidth;
columnWidth = createHeader(sheet,propertyNames);
int currRow = 1;
autoWidthColumns(sheet, columnWidth);
workbook.write();
} catch (Exception e) {
logger.error("JXLExcelExporter error, ", e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e2) {
}
}
}
}
}
// Action
userList = userService.findUser(user);
String[] attributes = new String[] { "userID", "userName","sex", "age", "address", "phoneNumber",};
Class[] types = new Class[] {String.class, String.class, String.class,String.class, String.class, String.class};
JxlExcelExport jxlExcelExport = new JxlExcelExport();
jxlExcelExport.export(request, response,attributes,types, userList, "user");
private Logger logger = Logger.getLogger(this.getClass());
private int[] createHeader(WritableSheet sheet,String[] attributes)
throws RowsExceededException, WriteException {
int[] columnWidth = new int[attributes.length];
WritableCellFormat cellFormat = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false));
cellFormat.setAlignment(Alignment.CENTRE);
Label label;
for(int i = 0; i < attributes.length; i++) {
label = new Label(i, 0, attributes[i], cellFormat);
sheet.addCell(label);
// Set column width
columnWidth[i] = (int) (attributes[i].length() * 1.5);
}
return columnWidth;
}
private int[] createBody(WritableSheet sheet,
List list,
String[] attributes,
Class[] types,
int[] columnWidth,
int currRow)
throws RowsExceededException, WriteException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
Iterator it = list.iterator();
Object vo;
Object obj;
Label label;
Number number;
DateTime dateCell;
int rowIndex = currRow;
DateFormat customDateFormat = new DateFormat("MM/dd/yyyy HH:mm");
while(it.hasNext()) {
vo = it.next();
for(int i = 0; i < attributes.length; i++) {
obj = BeanUtilsBean.getInstance().getProperty(vo, attributes[i]);
obj = (obj == null)? "" : obj;
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
if(types[i].equals(Date.class)) {
if(obj instanceof Date) {
// Format date
dateCell = new DateTime(i, rowIndex, (Date)obj, new WritableCellFormat(customDateFormat));
sheet.addCell(dateCell);
columnWidth[i] = checkMaxWidth(columnWidth[i], dateCell.getBytes().length);
} else {
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
columnWidth[i] = checkMaxWidth(columnWidth[i], obj.toString().length());
}
} else if(types[i].equals(Integer.class)) {
// Set number cell
number = new Number(i, rowIndex, new Double((String)obj).doubleValue());
sheet.addCell(number);
columnWidth[i] = checkMaxWidth(columnWidth[i], obj.toString().length());
} else {
label = new Label(i, rowIndex, obj.toString());
sheet.addCell(label);
columnWidth[i] = checkMaxWidth(columnWidth[i], StringUtils.trim(obj.toString()).length());
}
}
rowIndex ++;
}
return columnWidth;
}
private void autoWidthColumns(WritableSheet sheet, int[] columnWidth) {
for(int i = 0; i < columnWidth.length; i++) {
sheet.setColumnView(i, columnWidth[i]);
}
}
private int checkMaxWidth(int orignWidth, int comparedWdith) {
// Column width will be different for font family
return (int) (orignWidth > comparedWdith * 1.2? orignWidth : comparedWdith * 1.2);
}
public void export(HttpServletRequest request,
HttpServletResponse response,
User user,
String[] propertyNames,
Class[] types,
List list,
String fileName){
WritableWorkbook workbook = null;
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename="+ fileName +".xls");
workbook = Workbook.createWorkbook(response.getOutputStream());
WritableSheet sheet = workbook.createSheet(fileName, 0);
int[] columnWidth;
columnWidth = createHeader(sheet,propertyNames);
int currRow = 1;
autoWidthColumns(sheet, columnWidth);
workbook.write();
} catch (Exception e) {
logger.error("JXLExcelExporter error, ", e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e2) {
}
}
}
}
}
// Action
userList = userService.findUser(user);
String[] attributes = new String[] { "userID", "userName","sex", "age", "address", "phoneNumber",};
Class[] types = new Class[] {String.class, String.class, String.class,String.class, String.class, String.class};
JxlExcelExport jxlExcelExport = new JxlExcelExport();
jxlExcelExport.export(request, response,attributes,types, userList, "user");