/**
* 描述: 数据导出功能
*
* @param response
* 设置表头,类型
* @param list
* 数据集合
* @param titles
* 标题
* @param values
* 需要输出的值,对应pojo对象
* @param excelName
* excel 的名称
*/
public static void keyValueExcel(HttpServletResponse response, @SuppressWarnings("rawtypes") List list,
String[] titles,String[] values, String excelName) {
WritableWorkbook workbook = null;
try {
// response.reset();
String filename = excelName + ".xls";// 设置下载时客户端Excel的名称
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
workbook = Workbook.createWorkbook(response.getOutputStream());
// 创建sheet
WritableSheet ws = workbook.createSheet(excelName, 0);
// 设置自动适应宽度
CellView cellView = new CellView();
cellView.setAutosize(true); // 设置自动大小
ws.setRowView(0, 600); // 设置行的高度
ws.setColumnView(0, cellView);// 根据内容自动设置列宽
// 设置字体;
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
// 设置背景颜色;
cellFormat1.setBackground(Colour.YELLOW);
// 设置边框;
cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
// 设置自动换行;
cellFormat1.setWrap(true);
// 设置文字居中对齐方式;
cellFormat1.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
// 要写的行,jxl操作excel时,第一行是从0开始,以此类推
int rowNum = 0;
if (titles != null) {
// 压入标题
for (int j = 0; j < titles.length; j++) {// 写一行
Label cell = new Label(j, rowNum, "" + titles[j], cellFormat1);
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
}
rowNum = 1;
}
// 写sheet
for (int i = 0; i < list.size(); i++, rowNum++) {
for (int j = 0; j < values.length; j++) {
Object obj = list.get(i);
String value = values[j];
// 首字母转为大写
value = value.substring(0, 1).toUpperCase() + value.substring(1, value.length());
Method m = obj.getClass().getMethod("get" + value);
if (null == m.invoke(obj)) {
Label cell = new Label(j, rowNum, "" + "");
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
} else {
Object obj_value = m.invoke(obj);
if (obj_value instanceof Date) {
// 日期格式化
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
obj_value = dateFormat.format(obj_value);
}
Label cell = new Label(j, rowNum, "" + obj_value);
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
}
}
}
} catch (Exception e) {
// logger.error("文件导出异常",e);
} finally {
try {
// 一定要关闭, 否则没有保存Excel
workbook.write();
workbook.close();
} catch (Exception e) {
// logger.error("文件导出异常",e);
}
}
}
* 描述: 数据导出功能
*
* @param response
* 设置表头,类型
* @param list
* 数据集合
* @param titles
* 标题
* @param values
* 需要输出的值,对应pojo对象
* @param excelName
* excel 的名称
*/
public static void keyValueExcel(HttpServletResponse response, @SuppressWarnings("rawtypes") List list,
String[] titles,String[] values, String excelName) {
WritableWorkbook workbook = null;
try {
// response.reset();
String filename = excelName + ".xls";// 设置下载时客户端Excel的名称
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
workbook = Workbook.createWorkbook(response.getOutputStream());
// 创建sheet
WritableSheet ws = workbook.createSheet(excelName, 0);
// 设置自动适应宽度
CellView cellView = new CellView();
cellView.setAutosize(true); // 设置自动大小
ws.setRowView(0, 600); // 设置行的高度
ws.setColumnView(0, cellView);// 根据内容自动设置列宽
// 设置字体;
WritableFont font1 = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
// 设置背景颜色;
cellFormat1.setBackground(Colour.YELLOW);
// 设置边框;
cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
// 设置自动换行;
cellFormat1.setWrap(true);
// 设置文字居中对齐方式;
cellFormat1.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
// 要写的行,jxl操作excel时,第一行是从0开始,以此类推
int rowNum = 0;
if (titles != null) {
// 压入标题
for (int j = 0; j < titles.length; j++) {// 写一行
Label cell = new Label(j, rowNum, "" + titles[j], cellFormat1);
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
}
rowNum = 1;
}
// 写sheet
for (int i = 0; i < list.size(); i++, rowNum++) {
for (int j = 0; j < values.length; j++) {
Object obj = list.get(i);
String value = values[j];
// 首字母转为大写
value = value.substring(0, 1).toUpperCase() + value.substring(1, value.length());
Method m = obj.getClass().getMethod("get" + value);
if (null == m.invoke(obj)) {
Label cell = new Label(j, rowNum, "" + "");
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
} else {
Object obj_value = m.invoke(obj);
if (obj_value instanceof Date) {
// 日期格式化
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
obj_value = dateFormat.format(obj_value);
}
Label cell = new Label(j, rowNum, "" + obj_value);
ws.setColumnView(j, cellView);// 根据内容自动设置列宽
ws.addCell(cell);
}
}
}
} catch (Exception e) {
// logger.error("文件导出异常",e);
} finally {
try {
// 一定要关闭, 否则没有保存Excel
workbook.write();
workbook.close();
} catch (Exception e) {
// logger.error("文件导出异常",e);
}
}
}