java


/**
*service层代码
*
/
/**
* 2016年8月31日 80001092方法说明:导出盘点差异
* @param bean
* void
*/

@SuppressWarnings("unchecked")
public void exportStockDiffInfo(BeanBase bean) {
Map<String,Object[]> maps = this.getMapData();
Map<String, Object> map = new HashMap<String, Object>();
List<String[]> excel = new ArrayList<String[]>();
final String[] cellsTitle = this.getCellsTitle(maps);
final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
header.put(cellsTitle.length, "盘点差异");

String[] cells = null;
int length = maps.size();
UmUser currentUser = UserUtil.getCurrentUser();
String inv_type = null;
String empCode = currentUser.getEmpCode();
bean.getDatas().get(0).put("empCode", empCode);
// 权限关系代码
inv_type = bean.getDatas().get(0).get("LEVEL_CODE");
bean.getDatas().get(0).put("INV_TYPE", inv_type);

// 权限组织代码
getOrgCodes(bean);
String orgCode = bean.getDatas().get(0).get("ORG_CODES");
String[] orgCodes = orgCode.replaceAll("'", "").split(",");
List<String> codeList = Arrays.asList(orgCodes);
List<Map<String, Object>> list = invoiceMaintainDao.queryCheckDiffCountList(bean
.getDatas().get(0), codeList, 0, Integer.MAX_VALUE);
for (Map<String, Object> data : list) {
cells = new String[length];
int i = 0;
for(Map.Entry<String, Object[]> entry: maps.entrySet()) {
if(entry.getValue()!=null ) {
Object[] o = entry.getValue();
Map<String,String> objMap = (Map<String,String>)o[1];
String cellValue = null;
if(objMap !=null){
cellValue = objMap.get(String.valueOf(data.get(entry.getKey())));
}else{
cellValue = String.valueOf(data.get(entry.getKey()));
}
cells[i++] = "null".equals(cellValue) ? "" : cellValue ;
}

}
excel.add(cells);
}
map.put("orgCode", "sheet1");//sheet名 必填
map.put("data", excel);
map.put("header", header); //统计头部
map.put("cellsTitle", cellsTitle);
map.put("fileName", "盘点差异_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
bean.getExpData().put("excelData", map);
}

private Map<String, Object[]> getMapData() {
Map<String,Object[]> map = new LinkedHashMap<String,Object[]>();
map.put("STOCK_TIME", new Object[]{"盘点时间", null});//
map.put("ORG_CODE", new Object[]{"组织编码", null});
map.put("ORG_NAME", new Object[]{"组织名称", null});
map.put("FACE_AMT", new Object[]{"发票面额", null});
map.put("SYS_COUNT", new Object[]{"系统库存(张)", null});
map.put("REAL_COUNT", new Object[]{"实物盘存(张)", null});
map.put("DIFF_COUNT", new Object[]{"差异张数", null});
map.put("DIFF_AMT", new Object[]{"差异金额", null});
return map;
}

private String[] getCellsTitle(Map<String,Object[]> maps){
String[] titles = new String[maps.size()];
int i=0;
for(Map.Entry<String, Object[]> entry: maps.entrySet()) {
if(entry.getValue()!=null ) {
Object[] o = entry.getValue();
titles[i] = (String) o[0];
}
i++;
}
return titles;
}

/**
*controler 层
*/
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);
}
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();
}
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值