1、poi依赖jar包(poi-3.14jar、poi-ooxml-3.14jar、poi-ooxml-shcemas-3.14.jar)
2、web端后台控制层主要是设置流出流,响应头部和内容,具体代码如下(Controller)
/**
* <导出execl>
* <功能详细描述>
* @param excelBean
* @param request
* @param response
* @throws Exception [参数说明]
*
* @see [类、类#方法、类#成员]
*/
protected void exportExcelReport(ExportExcelBean excelBean,HttpServletRequest request,HttpServletResponse response)throws Exception
{
exportExcelReport(excelBean.getSheetName(), excelBean.getList(),excelBean.getColumnNameList(), excelBean.getColumnNameMap(),request,response);
}
/**
* 导出Excel
* <功能详细描述>
* @param sheetName 工作表名称
* @param rows 工作表格
* @param columnNames 标题key,value
* @param request HttpServletRequest
* @param response HttpServletResponse
* @throws Exception [参数说明]
*
* @see [类、类#方法、类#成员]
*/
public static void exportExcel( String sheetName, List<Map<String, Object>> rows,Map<String, String> columnNames,
HttpServletRequest request,HttpServletResponse response) throws Exception
{
long startTime = System.currentTimeMillis();
HSSFWorkbook workbook = createSXSSFWorkbook( sheetName, rows, columnNames );
String downloadFileName = getDownloadFileName(sheetName,request);
downloadFileName += ".xls";
// response.setHeader("Content-disposition","attachment;filename=" + downloadFileName);
// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=gbk");
// ServletOutputStream sos = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+downloadFileName);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
long end = System.currentTimeMillis();
logger.info( "export excel used "+(end-startTime)/1000 );
}
/**
* 导出Excel
* <功能详细描述>
* @param excelReport
* @param request
* @param response
* @throws Exception [参数说明]
*
* @see [类、类#方法、类#成员]
*/
public static void exportExcel( ExcelReport excelReport,HttpServletRequest request,HttpServletResponse response) throws Exception
{
exportExcel(excelReport.getSheetName(),excelReport.getRows(),excelReport.getColumnChineseNames(),request,response);
}
/**
* 获取下载文件名 Mozilla/5.0 (Windows NT 6.3; WOW64;Trident/7.0; rv:11.0) like Gecko IE11 MSIE IE 10以前 Mozilla/5.0
* (Windows NT 6.2; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0
* @param sheetName
* @param request
* @return [参数说明]
*
* @see [类、类#方法、类#成员]
*/
protected static String getDownloadFileName( String sheetName, HttpServletRequest request )
{
String downloadName = null;
try
{
String agent = request.getHeader( "User-Agent" );
boolean isIE = (agent != null && agent.indexOf( "MSIE" ) != -1);
// IE
if ( isIE || agent.indexOf( "Trident/7.0; rv:1" ) != -1 )
{
downloadName = URLEncoder.encode( sheetName, "utf-8" );
downloadName = downloadName.replace( "+", " " );
}
else
{
downloadName = new String( sheetName.getBytes( "utf-8" ), "iso-8859-1" );
}
}
catch ( UnsupportedEncodingException e )
{
}
return downloadName;
}
/**
* 创建导出超大表格工作表
* <功能详细描述>
* @param sheetName
* @param rows
* @param columnNames
* @return
* @throws Exception [参数说明]
*
* @see [类、类#方法、类#成员]
*/
public static HSSFWorkbook createSXSSFWorkbook( String sheetName, List<Map<String, Object>> rows,Map<String, String> columnNames) throws Exception
{
//默认缓存大小为100,当数据表格特别大将缓存大小设置50较好
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = workbook.createSheet();
//设置第一个工作表名称
workbook.setSheetName( 0, sheetName );
//创建行索引(用于记录当前行数)
int rowIndex = 0;
//创建工作表行记录
HSSFRow row = sheet.createRow( rowIndex++ );
HSSFCell cell;
/**
* Set title style for this sheet.
*/
CellStyle titleStyle = workbook.createCellStyle();
//设置标题水平居中
titleStyle.setAlignment( XSSFCellStyle.ALIGN_CENTER );
//设置标题垂直居中
titleStyle.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
//创建工作表格标题字体
Font titleFont = workbook.createFont();
//设置字体为黑体
titleFont.setFontName( "黑体" );
//设置标题字体为14号字体
titleFont.setFontHeightInPoints( (short)14 );
//设置标题样式字体
titleStyle.setFont( titleFont );
cell = row.createCell( 0 );
cell.setCellType( SXSSFCell.CELL_TYPE_STRING );
Set<String> columnNameList = columnNames.keySet();
CellRangeAddress range = new CellRangeAddress( 0, 0, 0, columnNameList.size() - 1 );
sheet.addMergedRegion( range );
cell.setCellValue( sheetName );
cell.setCellStyle( titleStyle );
row = sheet.createRow( rowIndex++ );
//标题
int index = 0;
for ( String title : columnNameList )
{
cell = row.createCell( index++ );
cell.setCellType( SXSSFCell.CELL_TYPE_STRING );
cell.setCellValue( columnNames.get( title ) );
}
Object recordValue;
for ( int i = 0; i < rows.size(); i++ )
{
row = sheet.createRow( i + rowIndex );
Map<String, Object> record = rows.get( i );
index=0;
for ( String title : columnNameList )
{
cell = row.createCell( index++ );
recordValue = record.get( title );
if ( recordValue == null )
{
continue;
}
if ( recordValue instanceof Number )
{
cell.setCellType( Cell.CELL_TYPE_NUMERIC );
cell.setCellValue( ((Number)recordValue).doubleValue() );
}
else
{
cell.setCellType( Cell.CELL_TYPE_STRING );
if ( recordValue instanceof java.util.Date )
{
cell.setCellValue( DateUtil.defautlFormatDate( (java.util.Date)recordValue ) );
}
else
{
cell.setCellValue( recordValue.toString() );
}
}
}
}
return workbook;
}