package com.sf.novatar.tpl.util;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
/**
* 利用开源组件POI3.0.2动态导出EXCEL文档
* 转载时请保留以下信息，注明出处！
* @author leno
* @version v1.0
* @param <T> 应用泛型，代表任意一个符合javabean风格的类
* 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
* byte[]表jpg格式的图片数据
*/
public class ExportExcel<T> {
public void exportExcel(Collection<T> dataset, OutputStream out , Map<String , String[]> mapper) throws IOException {
exportExcel("EXCEL导出", mapper, dataset, out, "yyyy-MM-dd");
}
@SuppressWarnings("unchecked")
public void exportExcel(String title, Map<String , String[]> mapper ,
Collection<T> dataset, OutputStream out, String pattern) throws IOException {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("leno");
//产生表格标题行
HSSFRow row = sheet.createRow(0);
String[] headers = mapper.get("headers");
String[] properties = mapper.get("properties");
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
//利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
//Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < properties.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
/*Field field = fields[i];
String fieldName = field.getName();*/
String fieldName = properties[i];
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});
Object value = getMethod.invoke(t, new Object[] {}) ;
if ( value == null ) {
value = "";
}
cell.setCellValue(value.toString());
//判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(
bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else{
//其它数据类型都当作字符串简单处理
textValue = value.toString();
}
//如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if(textValue!=null){
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if(matcher.matches()){
//是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}else{
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
out.close();
}
}
public static Map<String, String[]> parseXml( InputStream is ) throws DocumentException {
SAXReader reader = new SAXReader();
Document document = reader.read(is);
Element root = document.getRootElement();
@SuppressWarnings("unchecked")
List<Element> columns = root.elements("column");
String[] headers = new String[columns.size()];
String[] properties = new String[columns.size()];
for ( int i = 0; i < columns.size() ; i++ ) {
headers[i] = columns.get(i).attribute("header").getValue();
properties[i] = columns.get(i).attribute("property").getValue();
}
Map<String, String[]> map = new HashMap<String , String[]>();
map.put("headers", headers);
map.put("properties", properties);
return map;
}
public static Map<String, String[]> parseXml2( InputStream is ) throws DocumentException {
SAXReader reader = new SAXReader();
Document document = reader.read(is);
Element root = document.getRootElement();
@SuppressWarnings("unchecked")
List<Element> columns = root.elements("column");
String[] types = new String[columns.size()];
String[] properties = new String[columns.size()];
for ( int i = 0; i < columns.size() ; i++ ) {
types[i] = columns.get(i).attribute("type").getValue();
properties[i] = columns.get(i).attribute("property").getValue();
}
Map<String, String[]> map = new HashMap<String , String[]>();
map.put("types", types);
map.put("properties", properties);
return map;
}
}
/导出方法2(same as 方法1)
public void exportQueryResult(BeanBase bean) throws DocumentException, ParseException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
Map<String, String> paramsmap = bean.getDatas().get(0);
String exportType = paramsmap.get("exportType");
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("excel/excelexport-" + exportType +".xml");
Map<String , String[]> mapper = ExportExcel.parseXml(inputStream);
String[] headers = mapper.get("headers");
String[] properties = mapper.get("properties");
Map<String, Object> map = new HashMap<String, Object>();
List<String[]> excel = new ArrayList<String[]>();
final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
header.put(headers.length, "RollPlan");
int length = headers.length;
String planDate = paramsmap.get("planDate");
String reqDate = paramsmap.get("reqDate");
String materialCode = paramsmap.get("materialCode");
//String maturialDesc = paramsmap.get("maturialDesc");
String supplierCode = paramsmap.get("supplierCode");
//String supplierDesc = paramsmap.get("supplierDesc");
String status = paramsmap.get("status");
String sentStatus = paramsmap.get("sentStatus");
PurRollPlanExample example = new PurRollPlanExample();
PurRollPlanExample.Criteria cr = example.createCriteria();
//########### 数据权限控制 START ###################
AccessBean access = SessionUtil.getAttribute(SspConstants.ACCESS_KEY);
if(access==null){
throw new ServiceException("用户未授权");
}
//全网权限不控制
if(!SspConstants.DATA_ADMIN.equals(access.getAdminUser())){
if(SspConstants.DATA_SUP_ACCESS.equals(access.getAccessType())){ //判定是否是供应商权限
cr.andSupplierCodeIn(access.getValueList());
}/* else if (SspConstants.DATA_PUR_ACCESS.equals(access.getAccessType())){ //判定是否是采购权限
String createPerson = UserUtil.getCurrentUser().getUsername();
cr.andCreatePersonEqualTo(createPerson);
}*/
}
//########### 数据权限控制 END ###################
if (StringUtils.isNotEmpty(materialCode)) {
cr.andMaterialCodeEqualTo(materialCode);
}
/*if (StringUtils.isNotEmpty(maturialDesc)) {
cr.andMaturialDescEqualTo(maturialDesc);
}*/
if (StringUtils.isNotEmpty(supplierCode)) {
cr.andSupplierCodeEqualTo(supplierCode);
}
if (StringUtils.isNotEmpty(status)) {
cr.andStatusEqualTo(status);
}
if (StringUtils.isNotEmpty(sentStatus)) {
cr.andSentStatusEqualTo(sentStatus);
}
cr.andDelFlagEqualTo("1");
if (StringUtils.isNotEmpty(planDate)) {
cr.andPlanDateEqualTo(DateUtils.parseDate(planDate, "yyyy-MM"));
}
if (StringUtils.isNotEmpty(reqDate)) {
try {
cr.andReqDateEqualTo(new SimpleDateFormat("yyyy-MM-dd")
.parse(reqDate));
} catch (ParseException e) {
logger.error("滚动计划根据需求日期查询异常！");
}
}
List<PurRollPlan> list = null;
try{
list = purRollPlanDao.selectByExample(example);
}catch(Exception e){
//logger.error(e.getMessage(), e);
}
Class tCls = PurRollPlan.class;
//导出id
StringBuffer sbExp = new StringBuffer();
if(CollectionUtils.isNotEmpty(list)) {
for (PurRollPlan purDeliveryPlan : list) {
sbExp.append(purDeliveryPlan.getId());
String[] line = new String[length];
String fieldName = "";
for ( int i= 0 ; i < length ;i++) {
fieldName = properties[i];
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(purDeliveryPlan, new Object[] {});
String cellstr = "";
if ( value == null ) {
cellstr = "";
} else if (value instanceof Date) {
if("planDate".equals(fieldName)) {
cellstr = DateUtil.date2Str((Date)value, DateUtil.SHORT_DATE_FORMAT );
cellstr = cellstr.substring(0, 7);
}
if("reqDate".equals(fieldName) || "promiseDate".equals(fieldName)) {
cellstr = DateUtil.date2Str((Date)value, DateUtil.SHORT_DATE_FORMAT );
}
if("createTime".equals(fieldName) || "updateTime".equals(fieldName)) {
cellstr = DateUtil.date2Str((Date)value, DateUtil.NORMAL_DATE_FORMAT );
}
} else {
cellstr = value.toString();
if("status".equals(fieldName)) {
if("1".equals(cellstr)) {
cellstr = "已确认";
}
if("0".equals(cellstr)) {
cellstr = "未确认";
}
}
}
line[i] = cellstr;
}
excel.add(line) ;
}
}
map.put("orgCode","滚动计划");
map.put("data", excel);
map.put("header", header); //统计头部
//map.put("titles", titles); //头部标题
map.put("cellsTitle", headers);
map.put("fileName", "RollPlan_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
bean.getExpData().put("excelData", map);
/
public void suppDeliveryViewExport(BeanBase bean) throws DocumentException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
String folder = AsnStockServiceImpl.class.getClassLoader().getResource("/excel").getPath();
logger.info("folder : " + folder);
Map<String, String> paramsmap = bean.getDatas().get(0);
String exportType = paramsmap.get("exportType");
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("excel/excelexport-" + exportType +".xml");
logger.info("folder2 : " + this.getClass().getClassLoader().getResource("excel/excelexport-" + exportType +".xml").getPath());
String xmlPath = folder + File.separator + "excelexport-" + exportType +".xml";
logger.info("xmlPath " + xmlPath);
Map<String , String[]> mapper = ExportExcel.parseXml(inputStream);
String[] headers = mapper.get("headers");
String[] properties = mapper.get("properties");
Map<String, Object> map = new HashMap<String, Object>();
List<String[]> excel = new ArrayList<String[]>();
final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
header.put(headers.length, "SuppDeliveryView");
int length = headers.length;
String suppName = "%" + paramsmap.get("suppName") + "%";
String material_name = "%" + paramsmap.get("material_name") + "%";
String wHouse_name = "%" + paramsmap.get("wHouse_name") + "%";
paramsmap.put("suppName", suppName);
paramsmap.put("material_name", material_name);
paramsmap.put("wHouse_name", wHouse_name);
//########### 数据权限控制 START ###################
AccessBean access = SessionUtil.getAttribute(SspConstants.ACCESS_KEY);
if(access==null){
throw new ServiceException("用户未授权");
}
//全网权限不控制
if(!SspConstants.DATA_ADMIN.equals(access.getAdminUser())){
if(SspConstants.DATA_SUP_ACCESS.equals(access.getAccessType())){ //判定是否是供应商权限
//cr.andSupplierCodeIn(access.getValueList());
paramsmap.put("supAccessSql", access.getAccessSql());
} else if (SspConstants.DATA_INV_ACCESS.equals(access.getAccessType())){ //判定是否是仓管权限
//cr.andRecWarehouseCodeIn(access.getValueList());
paramsmap.put("invAccessSql", access.getAccessSql());
} else if (SspConstants.DATA_PUR_ACCESS.equals(access.getAccessType())){ //判定是否是采购权限
//cr.andRecWarehouseCodeIn(access.getValueList());
paramsmap.put("purAccessSql", access.getAccessSql());
}
}
//########### 数据权限控制 END ###################
List<Map<String, Object>> list = suppDeliveryViewDao.queryDeliveryViewList2(paramsmap);
StringBuffer expId = new StringBuffer();
for(Map<String, Object> m : list) {
expId.append(m.get("material_code"));
String[] line = new String[length];
String fieldName = "";
//int j = m.keySet().size();
for ( int i=0; i<length; i++) {
fieldName = properties[i];
Object value = m.get(fieldName);
String cellstr = "";
if(value != null) {
cellstr = value.toString();
if("delivery_status".equals(fieldName)) { //字段显示调整
if("1".equals(cellstr)) {
cellstr = "已完成收货";
}
if("0".equals(cellstr)) {
cellstr = "未完成收货";
}
}
if("isCreatedAsn".equals(fieldName)) {
if("1".equals(cellstr)) {
cellstr = "已创建";
}
if("0".equals(cellstr)) {
cellstr = "未创建";
}
}
if("audit_status".equals(fieldName)) {
if("1".equals(cellstr)) {
cellstr = "已审核";
}
if("0".equals(cellstr)) {
cellstr = "未审核";
}
}
if("receivedStatus".equals(fieldName)) {
if(Double.parseDouble(cellstr) <= 0) {
cellstr = "已完成收货";
} else {
cellstr = "未完成收货";
}
}
if("suppPromiseDelayTime".equals(fieldName)) {
if(Double.parseDouble(cellstr) <= 0) {
cellstr = "";
} else {
cellstr = "承诺交货已延迟" + cellstr + "天";
}
}
if("asnCreateDelay".equals(fieldName)) {
if(Double.parseDouble(cellstr) > -3) {
cellstr = "创建ASN已延迟" + (Double.parseDouble(cellstr) + 3) + "天";
} else {
cellstr = "";
}
}
if("deliveryDelay".equals(fieldName)) {
if("0".equals(m.get("isCreatedAsn"))) {
cellstr = "";
}
}
if("currDate".equals(fieldName)) {
if("0".equals(m.get("isCreatedAsn"))) {
cellstr = "";
} else {
cellstr = Math.abs(Double.parseDouble(cellstr)) + "";
}
}
} else {
cellstr = "";
}
line[i] = cellstr;
}
excel.add(line) ;
}
map.put("orgCode","供应商交货可视化报表");
map.put("data", excel);
map.put("header", header); //统计头部
map.put("cellsTitle", headers);
map.put("fileName", "SuppDeliveryView_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
bean.getExpData().put("excelData", map);
xml文件格式///
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE bean [
<!ELEMENT bean (column+)>
<!ATTLIST bean class CDATA #REQUIRED>
<!ATTLIST bean type CDATA #REQUIRED>
<!ELEMENT column EMPTY>
<!ATTLIST column property CDATA #REQUIRED>
<!ATTLIST column header CDATA #REQUIRED>
]>
<bean class="AsnStock" type="AsnStock">
<column property="mAsnno" header="Asn单号" />
<column property="dPurOrderNo" header="采购订单号" />
<column property="line_item" header="行项目" />
<column property="mSuppCode" header="供应商编码" />
<column property="suppName" header="供应商编码名称" />
<column property="material_code" header="物料编码" />
<column property="material_name" header="物料名称" />
<column property="unit" header="单位" />
<column property="reserve_amount" header="预约交货数量" />
<column property="received_amount" header="实际收货数量" />
<column property="diff" header="差异" />
<column property="mRecWhouseCode" header="收货仓库代码" />
<column property="mRecWhouseName" header="收货仓库名称" />
<column property="quantity_time" header="订单需求日期" />
<column property="mReserveDelivTime" header="预约交货日期" />
<column property="warehouse_received_time" header="仓库实际收货日期" />
<column property="mAuditStatus" header="仓库审核状态" />
<column property="mAsnCreator" header="创建人" />
<column property="mAsnCreateTime" header="创建时间" />
<column property="mAsnUpdatePerson" header="修改人" />
<column property="mAsnUpdateTime" header="修改时间" />
</bean>
///controler层///
/**
* 业务处理方法 导出：<br>
*
* @param bean
* @return
*/
@SuppressWarnings("unchecked")
@RequestMapping("/export")
public ModelAndView export(BeanBase bean,HttpServletRequest request) {
Map<String, Object> map = null;
try {
bean.setContent(bean.getContent() + getCreateId());
String processorName = bean.getNeedcontrolprocess();
if (StringUtils.isEmpty(processorName)) {
bean = service.service(bean);
} else {
bean.getExpData().put("request", request);
process(bean);
bean.getExpData().put("request", null);
}
map = (Map<String, Object>) bean.getExpData().get("excelData");
} catch (ServiceException e) {
logger.info(e.getMessage(), e);
bean.setStatus(BusinessStatus.FAIL);
bean.setProcessMsg(e.getMessage());
}
return new ModelAndView(new ViewExcel(), map);
}
///
2：
public void exportTaxFavInfo(BeanBase bean){
Map<String, Object> map = new HashMap<String, Object>();
List<String[]> excel = new ArrayList<String[]>();
final String[] cellsTitle =
new String[] { "项目","税局批复日期","纳税主体编码","纳税主体名称",
"公司代码","公司名称","减免原因","减免方式","减免期间-开始","减免期间-结束",
"减免税率","减免金额","备案金额","备案号","是否有税收承诺",
"承诺税种","承诺金额","承诺时间","备注",
"创建人","创建时间","修改人","修改时间"};
final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
header.put(cellsTitle.length, "税务优惠信息");
String[] cells = null;
int length = cellsTitle.length;
int row = 1;
List<Map<String, Object>> list = null;
try{
list = taxFavInfoDao.queryTaxFavInfo(bean.getDatas().get(0), 0, Integer.MAX_VALUE);
}catch(Exception e){
logger.info(e.getMessage(), e);
}
for (Map<String, Object> data : list) {
cells = new String[length];
int i = 0;
cells[i++] = String.valueOf(row); // 序号
cells[i++] = String.valueOf(data.get("PROJECT"));
cells[i++] = String.valueOf(data.get("REVIEW_DATE"));
cells[i++] = String.valueOf(data.get("YWLXMC"));
cells[i++] = String.valueOf(data.get("TAX_PAY_NAME"));
cells[i++] = String.valueOf(data.get("CORP_CODE"));
cells[i++] = String.valueOf(data.get("CORP_NAME"));
cells[i++] = String.valueOf(data.get("BREAKS_CAUSE"));
cells[i++] = String.valueOf(data.get("BREAKS_TYPE"));
cells[i++] = String.valueOf(data.get("BREAKS_BEGIN_TIME"));
cells[i++] = String.valueOf(data.get("BREAKS_END_TIME"));
cells[i++] = String.valueOf(data.get("BREAKS_RATE"));
cells[i++] = String.valueOf(data.get("BREAKS_AMT"));
cells[i++] = String.valueOf(data.get("RECORD_AMT"));
cells[i++] = String.valueOf(data.get("RECORD_NUMBER"));
cells[i++] = String.valueOf(data.get("IS_PROMISE"));
cells[i++] = String.valueOf(data.get("PROMISE_BREED"));
cells[i++] = String.valueOf(data.get("PROMISE_AMT"));
cells[i++] = String.valueOf(data.get("PROMISE_TIME"));
cells[i++] = String.valueOf(data.get("REMARKS"));
cells[i++] = String.valueOf(data.get("CREATE_ID"));
cells[i++] = String.valueOf(data.get("CREATE_TIME"));
cells[i++] = String.valueOf(data.get("UPDATE_ID"));
cells[i++] = String.valueOf(data.get("UPDATE_TIME"));
excel.add(cells);
row++;
}
map.put("orgCode", "税务优惠信息");
map.put("data", excel);
map.put("header", header); //统计头部
//map.put("titles", titles); //头部标题
map.put("cellsTitle", cellsTitle);
map.put("fileName", "税务优惠信息" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
bean.getExpData().put("excelData", map);
}
/**
*controler层，根据不同service会调用不同业务
*
*/
public ModelAndView export(BeanBase bean,HttpServletRequest request) {
Map<String, Object> map = null;
try {
bean.setContent(bean.getContent() + getCreateId());
String processorName = bean.getNeedcontrolprocess();
if (StringUtils.isEmpty(processorName)) {
bean = service.service(bean);
} else {
process(bean);
}
map = (Map<String, Object>) bean.getExpData().get("excelData");
} catch (Throwable e) {
logger.info(e.getMessage(), e);
bean.setStatus(BusinessStatus.FAIL);
bean.setProcessMsg(e.getMessage());
}
return new ModelAndView(new ViewExcel(), map);
}
/**
*ViewExcel方法
**/
package com.sf.novatar.tpl.util;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;
@SuppressWarnings("deprecation")
public class ViewExcel extends AbstractExcelView {
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// sheet存在导出文件的机构代码，导入时需要使用此数据项
String orgCode = (String) model.get("orgCode");
HSSFSheet sheet = workbook.createSheet(orgCode);
sheet.setDefaultColumnWidth(12);
String[] cellsTitle = (String[]) model.get("cellsTitle");
HSSFCell cell = null;
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框
int row = 0; //开始行
int column = 0; //开始列
int columns = 0; //合并列数
//报表头部
Map<Integer, String> header = (Map<Integer, String>) model.get("header");
if (header != null) {
for(Map.Entry<Integer, String> entry: header.entrySet()) {
columns = entry.getKey().intValue(); //合并单元格列数
String cotent = entry.getValue(); //单元格内容
cell = getCell(sheet, row, 0);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle); //设置单元格样式
setText(cell, cotent); //设置第row行0列单元格
for (int i = 1; i < columns; i++) { //从第2列开始
cell = getCell(sheet, row, i);
cell.setCellStyle(cellStyle); //设置单元格样式
setText(cell, ""); //设置第row行1列到column列单元格
}
sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1)); //合并单元格
}
row = 1; //余下的从第1行开始
}
columns = 0;
String[] columnTitles = null;
String columnTitle = "";
//报表合并标题
Map<Integer, String[]> titles = (Map<Integer, String[]>) model.get("titles");
if (titles != null) {
for (String content : cellsTitle) {
columnTitles = titles.get(column);
if (columnTitles != null) {
columns = column + Integer.valueOf(columnTitles[0]) - 1;
columnTitle = columnTitles[1];
}
if (columnTitles != null || (column <= columns && column != 0)) {
cell = getCell(sheet, row, column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, columnTitle);
sheet.addMergedRegion(new CellRangeAddress(row, row, column, columns));
cell = getCell(sheet, row + 1, column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, content);
} else {
cell = getCell(sheet, row, column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中
cell.setCellStyle(cellStyle);
setText(cell, content);
cell = getCell(sheet, row + 1, column);
cell.setCellStyle(cellStyle);
setText(cell, "");
sheet.addMergedRegion(new CellRangeAddress(row, row + 1, column, column));
}
column += 1;
}
row += 2;
} else {
for (String content : cellsTitle) {
cell = getCell(sheet, row, column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, content);
column += 1;
}
row += 1;
}
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框
ArrayList<String[]> excelData = (ArrayList<String[]>) model.get("data");
if (excelData != null) {
// 记录行
for (String[] contents : excelData) {
column = 0;
for (String content : contents) {
cell = getCell(sheet, row, column);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, content);
column += 1;
}
row += 1;
}
}
Map<String[], List<String[]>> rowData = (Map<String[], List<String[]>>) model.get("rowData");
if (rowData != null && rowData.size() > 0) {
for(Map.Entry<String[], List<String[]>> entry: rowData.entrySet()) {
String[] keys = entry.getKey();
List<String[]> valueList = entry.getValue();
int size = valueList.size();
for (int i = 0; i < keys.length; i++) {
cell = getCell(sheet, row, i);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中
cell.setCellStyle(cellStyle);
setText(cell, keys[i]);
if (valueList.size() > 1) {
for (int j = 1; j < valueList.size(); j++) {
cell = getCell(sheet, row + j, i);
cell.setCellStyle(cellStyle);
setText(cell, "");
}
sheet.addMergedRegion(new CellRangeAddress(row, row + valueList.size() - 1, i, i));
}
}
for (int i = 0; i < valueList.size(); i++) {
String[] values = valueList.get(i);
for (int j = 0; j < values.length; j++) {
cell = getCell(sheet, row + i, keys.length + j);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
//setText(cell, values[i]);
setText(cell, values[j]);
}
}
row += size;
}
}
columns = 0;
//报表底部
Map<Integer, List<String>> footer = (Map<Integer, List<String>>) model.get("footer");
if (footer != null) {
for(Map.Entry<Integer, List<String>> entry: footer.entrySet()) {
columns = entry.getKey().intValue(); //合并单元格列数
List<String> totals = entry.getValue();
cell = getCell(sheet, row, 0);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, "合计：");
for (int k = 1; k < columns; k++) {
cell = getCell(sheet, row, k);
cell.setCellStyle(cellStyle);
setText(cell, "");
}
sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1));
for (String total : totals) {
cell = getCell(sheet, row, columns++);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
setText(cell, total);
}
for (int i = columns; i < cellsTitle.length; i++) {
cell = getCell(sheet, row, i);
cell.setCellStyle(cellStyle);
setText(cell, "");
}
}
}
String filename = model.get("fileName") + ".xls";// 设置下载时客户端Excel的名称
filename = ExcelNameEncode.encodeFilename(filename, request);// 处理中文文件名
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
// response.setContentType("application/msexcel;charset=GB2312");
response.setHeader("Content-disposition", "attachment;filename="
+ filename);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
}
导出excel文件
最新推荐文章于 2024-09-20 19:22:55 发布
扫一扫
97
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
