springMvc下导出Excel功能
2017-11-17 11:48 sihao560 阅读(5972) 评论(0) 编辑 收藏 举报1、ExcleUtils.java代码：
public class ExcelUtils {
//excel默认宽度；
private static int width = 256*14;
//默认字体
private static String excelfont = "微软雅黑";
/**
*
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字 当前sheet数目只为1
* @param headers 导出的表格的表头
* @param ds_titles 导出的数据 map.get(key) 对应的 key
* @param ds_format 导出数据的样式
* 1:String left;
* 2:String center
* 3:String right
* 4 int right
* 5:float ###,###.## right
* 6:number: #.00% 百分比 right
* @param widths 表格的列宽度 默认为 256*14
* @param data 数据集 List<Map>
* @param response
* @throws IOException
*/
public static void export(String excelName, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data ,HttpServletRequest request, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
session.setAttribute("state", null);
if(widths==null){
widths = new int[ds_titles.length];
for(int i=0;i<ds_titles.length;i++){
widths[i]=width;
}
}
if(ds_format==null){
ds_format = new int[ds_titles.length];
for(int i=0;i<ds_titles.length;i++){
ds_format[i]=1;
}
}
//设置文件名
String fileName = "";
if(StringUtils.isNotEmpty(excelName)){
fileName = excelName;
}
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?sheetName:"excel");
//创建表头，如果没有跳过
int headerrow = 0;
if(headers!=null){
HSSFRow row = sheet.createRow(headerrow);
//表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 11);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth((short)i,(short)widths[i]);
HSSFCell cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
headerrow++;
}
//表格主体 解析list
if(data != null){
List styleList = new ArrayList();
for (int i = 0; i <ds_titles.length; i++) { //列数
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
if(ds_format[i]==1){
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}else if(ds_format[i]==2){
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else if(ds_format[i]==3){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
}else if(ds_format[i]==4){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
}else if(ds_format[i]==5){
//float类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
}else if(ds_format[i]==6){
//百分比类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
styleList.add(style);
}
for (int i = 0; i < data.size() ; i++) { //行数
HSSFRow row = sheet.createRow(headerrow);
Map map = data.get(i);
for (int j = 0; j <ds_titles.length; j++) { //列数
HSSFCell cell = row.createCell(j);
Object o = map.get(ds_titles[j]);
if(o==null||"".equals(o)){
cell.setCellValue("");
}else if(ds_format[j]==4){
//int
cell.setCellValue((Long.valueOf((map.get(ds_titles[j]))+"")).longValue());
}else if(ds_format[j]==5|| ds_format[j]==6){
//float
cell.setCellValue((Double.valueOf((map.get(ds_titles[j]))+"")).doubleValue());
}else {
cell.setCellValue(map.get(ds_titles[j])+"");
}
cell.setCellStyle((HSSFCellStyle)styleList.get(j));
}
headerrow++;
}
}
fileName=fileName+".xls";
String filename = "";
try{
filename =encodeChineseDownloadFileName(request,fileName);
}catch(Exception e){
e.printStackTrace();
}
// final String userAgent = request.getHeader("USER-AGENT");
// if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器
// filename = URLEncoder.encode(fileName,"UTF8");
// }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器
// filename = new String(fileName.getBytes(), "ISO8859-1");
// }else{
// filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器
// }
response.setHeader("Content-disposition", filename);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filename);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
session.setAttribute("state", "open");
}
/**
* 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
* @throws UnsupportedEncodingException
*/
public static String encodeChineseDownloadFileName(
HttpServletRequest request, String pFileName) throws Exception {
String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent){
if (-1 != agent.indexOf("Firefox")) {//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";
}else if (-1 != agent.indexOf("Chrome")) {//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
}
2、前端调用代码：
$("#exportExcel").click(function(){
var url = "exprotProductList";
$('#listForm').attr('action', url);
$('#listForm').submit();
$('#listForm').attr('action', "list");
});
3、controller中代码（springMVC）：
/**
* 测试下载
*/
@GetMapping("/exprotProductList")
public void exportExcel(Product.Type type,Long countryId, Long productCategoryId, Long brandId, Long promotionId, Long productTagId, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, ModelMap model,HttpSession session,HttpServletRequest request,HttpServletResponse response){
String[] excelHeader = { "ID","国家", "商品编号", "名称","分类","创建日期"};
String[] ds_titles = { "id","country", "sn", "name","productCategory","createdDate"};
int[] ds_format = { 1,1, 1, 1,1,1};
try{
Country country = null;
if (countryId != null) {
country = countryService.find(countryId);
}
ProductCategory productCategory = productCategoryService.find(productCategoryId);
Brand brand = brandService.find(brandId);
Promotion promotion = promotionService.find(promotionId);
ProductTag productTag = productTagService.find(productTagId);
List<Product> products = productService.findList(type, productCategory, country, brand, promotion, productTag, isMarketable, isList, isTop, isOutOfStock, isStockAlert);
List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
if(CollectionUtils.isNotEmpty(products)){
for(Product product : products){
Map<String,Object> map = new HashMap<String,Object>();
map.put("id", product.getId());
map.put("country", product.getProductCategory().getCountry().getName());
map.put("sn", product.getSn());
map.put("name", product.getName());
map.put("productCategory", product.getProductCategory().getName());
map.put("createdDate", product.getCreatedDate());
data.add(map);
}
}
ExcelUtils.export("商品表", "商品", excelHeader, ds_titles, ds_format, null, data, request, response);
}catch(Exception e){
e.printStackTrace();
}
}
4、servicer、servicerimpl、dao、daoimpl中的代码省略
