package com.sitech.dss.ssjf.service.impl;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.MapUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.springframework.stereotype.Service;
import com.sitech.dss.ssjf.service.ExportUniteService;
/**
* 统一导出功能实现类
* @author cfl
* 2014-7-31
*/
@Service("export_service")
public class ExportUniteServiceImpl implements ExportUniteService {
private Logger logger = Logger.getLogger(ExportUniteServiceImpl.class);
/**
* EXCEL单行表头导出功能实现
* @author cfl
*/
@Override
public void exportExcelBySingleHeader(List<Map<String, Object>> dateList,
Map<String,String> headMap,String filename, String sheetName,Map<String, Object> styleMap)
{
try
{
/**---------------------------------------输出控制---------------------------------------------------------**/
HttpServletResponse response = ServletActionContext.getResponse();
OutputStream os = response.getOutputStream();// 取得输出流
filename = filename+".xlsx";
filename = new String(filename.getBytes("GBK"), "iso8859-1");
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename="+filename);// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
/**---------------------------------------创建webbook---------------------------------------------------------**/
// 第一步，创建一个webbook，对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRow row = sheet.createRow((int) 0);
sheet.setDefaultColumnWidth((short) 15);
/**---------------------------------------表头样式设置---------------------------------------------------------**/
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//设置背景颜色
setBackColor(style,MapUtils.getShortValue(styleMap, "headerBackColor"));
//设置边框颜色
setBorder(style,CellStyle.BORDER_THIN,MapUtils.getShortValue(styleMap, "borderColor"));
//设置字体
Font font=wb.createFont();
setFont(font,style,(short)MapUtils.getIntValue(styleMap, "headerFontSize"),MapUtils.getShortValue(styleMap, "headerFontColor") , MapUtils.getString(styleMap, "font"));
/**----------------------------------------表内容样式设置--------------------------------------------------------**/
CellStyle style2 = wb.createCellStyle();
//设置边框颜色
setBorder(style2,CellStyle.BORDER_THIN,MapUtils.getShortValue(styleMap, "borderColor"));
//设置字体
Font font2=wb.createFont();
setFont(font2,style2,(short)MapUtils.getIntValue(styleMap, "bodyFontSize"),MapUtils.getShortValue(styleMap, "bodyFontColor") , MapUtils.getString(styleMap, "font"));
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
/**----------------------------------------表头信息组装--------------------------------------------------------**/
XSSFCell cell = row.createCell((short) 0);
int cellIndex = 0;
for(Map.Entry<String, String> entry:headMap.entrySet()){
cell = row.createCell((short) cellIndex);
cell.setCellValue(entry.getValue());
cell.setCellStyle(style);
cellIndex++;
}
/**-----------------------------------------表内容信息组装-------------------------------------------------------**/
for (int i = 0; i < dateList.size(); i++)
{
row = sheet.createRow((int) i + 1);
Map map = dateList.get(i);
int rowCellIndex = 0;
for(Map.Entry<String, String> entry:headMap.entrySet()){
cell = row.createCell((short) rowCellIndex);
cell.setCellValue(MapUtils.getString(map, entry.getKey()));
cell.setCellStyle(style2);
rowCellIndex++;
}
}
/**-----------------------------------------写入输出流，关闭输出流-------------------------------------------------------**/
wb.write(os);
os.flush();
os.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
/**
* 设置背景颜色
* @author cfl
* @param style style对象
* @param color ：IndexedColors.SKY_BLUE.getIndex()
* @return
*/
public static CellStyle setBackColor(CellStyle style,short color){
//设置前端颜色
style.setFillForegroundColor(color);
//设置填充模式
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/**
* 设置单元格边框(四个方向的颜色一样)
* @author cfl
* @param style style对象
* @param borderStyle 边框类型 ：dished-虚线 thick-加粗 double-双重 dotted-有点的 CellStyle.BORDER_THICK
* @param borderColor 颜色 IndexedColors.BLUE.getIndex()
* @return
*/
public static CellStyle setBorder(CellStyle style,short borderStyle,short borderColor){
//设置底部格式（样式+颜色）
style.setBorderBottom(borderStyle);
style.setBottomBorderColor(borderColor);
//设置左边格式
style.setBorderLeft(borderStyle);
style.setLeftBorderColor(borderColor);
//设置右边格式
style.setBorderRight(borderStyle);
style.setRightBorderColor(borderColor);
//设置顶部格式
style.setBorderTop(borderStyle);
style.setTopBorderColor(borderColor);
return style;
}
/**
*
* 设置字体（简单的需求实现，如果复杂的字体，需要自己去实现）尽量重用
* @author cfl
* @param style style对象
* @param fontSize 字体大小 shot(24)
* @param color 字体颜色 IndexedColors.BLACK.getIndex()
* @param fontName 字体名称 "Courier New"
* @param
*/
public static CellStyle setFont(Font font, CellStyle style,short fontSize,short color,String fontName){
font.setFontHeightInPoints(fontSize);
font.setFontName(fontName);
//font.setItalic(true);// 斜体
//font.setStrikeout(true);//加干扰线
font.setColor(color);//设置颜色
// Fonts are set into a style so create a new one to use.
style.setFont(font);
return style;
}
}
代码如上所示
