昨天在博客中写了方式一，今天在测试程序的时间发现方式一代码量大有部分bug，当时数据量大的时间出现导出excle的名字变成了方法名，后缀也不见了，鉴于项目比较急，同事说之前写过一个通用的可以用下，作为小白我当然愿意，于是开始了抄袭之路。废话少说，看代码...
前台不在说,直接请求就可以,
控制层:1,获得要到导出的数据，我这里是list集合，然后把list存放到LinkedHashMap泛型中的List对象中，这里可以了解下LinkedHashMap的的作用，记得在把从存放时的字段顺序设置好，这个顺序将会影响你在excle中导出时字段的的顺序，
2,创建一个headers数组存放。excle中用到的列名，记得和上面把数据存放到LinkedHashMap泛型中的List顺序一样，
3,创建widhts数组，设置上了列的宽度，
4,创建工作薄HSSFWorkbook对象，
做完以上工作可以开始调用excle转换的方法了
excelUtil.exportXLS(list, headers,wb,widhts,title) ;//调用excle导出方法,参数分别为，data集合，列名，工作薄对象，列宽，工作薄title。
---------------------------------------------------------------导出中主要方法----------------------------------------------------------------------------------------------
//读取数据库并导出报表
public <T> void exportXLS(List<LinkedHashMap<String, Object>> list,String[] headers,HSSFWorkbook workbook,int [] widths,String title) {
// 2.创建一个 worksheet
HSSFSheet worksheet = workbook.createSheet("sheet");
// 3.定义起始行和列
int startRowIndex = 0;
int startColIndex = 0;
// 4.创建title,data,headers
ExcelUtil.buildReport(worksheet, startRowIndex, startColIndex,headers,widths,title);
// 5.填充数据
ExcelFill.fillReport(worksheet, startRowIndex, startColIndex,
list);
/* // 6.设置reponse参数
String fileName = "敏感信息表.xls";
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
}
---------------------------------------------------------------------------------创建title,data,headers--------------------------------------------------------------------
public static void buildReport(HSSFSheet worksheet,int startRowIndex,
int startColIndex,String[] headers,int [] widths,String title){
for(int i=0;i< widths.length ;i++){
int width = widths[i];
worksheet.setColumnWidth(i, width);
}
buildTitle(worksheet,startRowIndex,startColIndex,headers.length,title );
buildHeaders(worksheet,startRowIndex,startColIndex,headers);
}
private static void buildTitle(HSSFSheet worksheet, int startRowIndex,
int startColIndex,int rowlength,String title) {
//报表标题字体
Font fontTitle = worksheet.getWorkbook().createFont();
fontTitle.setBoldweight((short)Font.BOLDWEIGHT_BOLD);
fontTitle.setFontHeight((short)280);
//标题单元格格式
HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setWrapText(true);
cellStyleTitle.setFont(fontTitle);
HSSFRow rowTitle = worksheet.createRow((short)startRowIndex);
rowTitle.setHeight((short)500);
HSSFCell cellTitle = rowTitle.createCell(startColIndex);
cellTitle.setCellValue(title);
cellTitle.setCellStyle(cellStyleTitle);
worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, rowlength-1));//标题合并列
}
private static void buildHeaders(HSSFSheet worksheet, int startRowIndex,
int startColIndex,String[] headers ) {
// Header字体
Font font = worksheet.getWorkbook().createFont();
font.setBoldweight((short)Font.BOLDWEIGHT_BOLD);
// 单元格样式
HSSFCellStyle headerCellStyle = worksheet.getWorkbook()
.createCellStyle();
headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headerCellStyle.setWrapText(true);
headerCellStyle.setFont(font);
headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
headerCellStyle.setBorderRight(CellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(CellStyle.BORDER_THIN);
//产生表格标题行
// 创建字段标题
HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 1);
rowHeader.setHeight((short) 500);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = rowHeader.createCell(i);
cell.setCellStyle(headerCellStyle);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
}
----------------------------------------------------------------------------数据填充---------------------------------------------------------------------------------------
public static void fillReport(HSSFSheet worksheet,int startRowIndex,
int startColIndex, List<LinkedHashMap<String, Object>> list){
startRowIndex += 2;
HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
bodyCellStyle.setWrapText(false); //是否自动换行.
bodyCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
bodyCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
bodyCellStyle.setBorderRight(CellStyle.BORDER_THIN);
bodyCellStyle.setBorderTop(CellStyle.BORDER_THIN);
for (int i = 0 ; i < list.size() ; i++ ) {
LinkedHashMap<String, Object> map = list.get(i);
Row row=worksheet.createRow(startRowIndex+i);
int j = 0 ;
for(Map.Entry<String, Object> entry: map.entrySet()) {
String cellValue= (String)entry.getValue();
Cell cell=row.createCell(j);
if("null".equals(cellValue)){
cell.setCellValue("");
}else if("退出".equals(cellValue)){
bodyCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
bodyCellStyle.setFillForegroundColor((short) 24);
cell.setCellValue(cellValue);
cell.setCellStyle(bodyCellStyle);
}else{
cell.setCellValue(cellValue);
}
j++ ;
}
}
}
到此完成，
-------------------------控制层------------------------
List<LoginLogVo> listTbsUserModel=null;
List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
try {
//查询敏感信息数据
listTbsUserModel= loginLogService.findLoanStatisticsList(userNumber,userName,status,loginDate,ip);
} catch (Exception e) {
logger.error("查询数据失败"+e);
e.printStackTrace();
}
if(listTbsUserModel !=null && listTbsUserModel.size() > 0 ){
for(int i = 0 ; i < listTbsUserModel.size() ; i++ ){
LoginLogVo vo = listTbsUserModel.get(i);
LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>() ;
map.put("loginDate",vo.getLoginDate());
map.put("usernumber",vo.getUserNumber());
map.put("username", vo.getUserName());
String statusStr = String.valueOf(vo.getStatus());
String newLoanState = statusStr == null ? "0":statusStr ;
String state = Long.parseLong(newLoanState) > 0 ? "退出":"登录";
map.put("status", state);
map.put("Ip", vo.getIp());
list.add(map);
}
}
ExcelUtil excelUtil = new ExcelUtil();
String [] headers = { "时间 ", "账号","姓名", "状态", "IP" };
HSSFWorkbook wb = null;
wb=new HSSFWorkbook();
String title = "登录退出表";
int [] widhts = {4000,3000,5000,4000,5000};
excelUtil.exportXLS(list, headers,wb,widhts,title) ;
OutputStream out=null;
try {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename="+"\""+ new String(("登录退出信息列表.xls").getBytes("GB2312"),"iso8859-1")+ "\"");
out = response.getOutputStream();
wb.write(out);
response.addHeader("Content-Length", "");
} catch (UnsupportedEncodingException e1) {
logger.error(e1);
e1.printStackTrace();
} catch (IOException e) {
logger.error(e);
e.printStackTrace();
}finally{
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
