package com.lizi.admin.controller.platform.excel;

import java.util.List;
import java.util.Map;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {
//创建HSSFWorkbook工作薄对象
public static HSSFWorkbook export_text(List<Map<String,Object>> list,String data){
try {
//创建工作薄对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建标题行样式
HSSFCellStyle headStyle = headStyle(wb);
//创建内容行样式
HSSFCellStyle contentStyle = contentStyle(wb);

//创建表
HSSFSheet sheet_1 = wb.createSheet(data+"对账信息");
//设置表的默认列宽
sheet_1.setDefaultColumnWidth(30);

//创建标题行
HSSFRow headRow = sheet_1.createRow(0);
HSSFCell head_cell_1 = headRow.createCell(0); //创建标题行第一列
head_cell_1.setCellValue("对账时间"); //第一列内容
head_cell_1.setCellStyle(headStyle); //将标题行样式添加

HSSFCell head_cell_2 = headRow.createCell(1);
head_cell_2.setCellValue("商家总流水");
head_cell_2.setCellStyle(headStyle);

HSSFCell head_cell_3 = headRow.createCell(2);
head_cell_3.setCellValue("商家总流水笔数");
head_cell_3.setCellStyle(headStyle);

HSSFCell head_cell_4 = headRow.createCell(3);
head_cell_4.setCellValue("实际收单总流水");
head_cell_4.setCellStyle(headStyle);

HSSFCell head_cell_5 = headRow.createCell(4);
head_cell_5.setCellValue("实际收单总流水笔数");
head_cell_5.setCellStyle(headStyle);

HSSFCell head_cell_6 = headRow.createCell(5);
head_cell_6.setCellValue("差异金额");
head_cell_6.setCellStyle(headStyle);

HSSFCell head_cell_7 = headRow.createCell(6);
head_cell_7.setCellValue("差异笔数");
head_cell_7.setCellStyle(headStyle);

HSSFCell head_cell_8 = headRow.createCell(7);
head_cell_8.setCellValue("调账金额");
head_cell_8.setCellStyle(headStyle);

HSSFCell head_cell_9 = headRow.createCell(8);
head_cell_9.setCellValue("调账笔数");
head_cell_9.setCellStyle(headStyle);

HSSFCell head_cell_10 = headRow.createCell(9);
head_cell_10.setCellValue("对账单状态");
head_cell_10.setCellStyle(headStyle);

//为内容行添加数据和样式
for (int i = 1; i <= list.size(); i++) {
HSSFRow contentRow = sheet_1.createRow(i);
Map<String, Object> map=list.get(i-1);
String accountsTime=map.get("accountsTime").toString();
String sysMoney=map.get("sysMoney").toString();
String sysNum=map.get("sysNum").toString();
String passagewayMoney=map.get("passagewayMoney").toString();
String passagewayNum=map.get("passagewayNum").toString();
String differenceMoney=map.get("differenceMoney").toString();
String differenceNumber=map.get("differenceNumber").toString();
String adjustmentMoney=map.get("adjustmentMoney").toString();
String adjustmentNumber=map.get("adjustmentNumber").toString();
String state="";
if(map.get("state")!=null){
if(map.get("state").toString().equals("0")){
state="正常";
}
if(map.get("state").toString().equals("1")){
state="未对账";
}
if(map.get("state").toString().equals("2")){
state="已对账";
}
}
HSSFCell content_cell_1 = contentRow.createCell(0);
content_cell_1.setCellValue(accountsTime);
content_cell_1.setCellStyle(contentStyle);

HSSFCell content_cell_2 = contentRow.createCell(1);
content_cell_2.setCellValue(sysMoney);
content_cell_2.setCellStyle(contentStyle);

HSSFCell content_cell_3 = contentRow.createCell(2);
content_cell_3.setCellValue(sysNum);
content_cell_3.setCellStyle(contentStyle);

HSSFCell content_cell_4 = contentRow.createCell(3);
content_cell_4.setCellValue(passagewayMoney);
content_cell_4.setCellStyle(contentStyle);

HSSFCell content_cell_5 = contentRow.createCell(4);
content_cell_5.setCellValue(passagewayNum);
content_cell_5.setCellStyle(contentStyle);

HSSFCell content_cell_6 = contentRow.createCell(5);
content_cell_6.setCellValue(differenceMoney);
content_cell_6.setCellStyle(contentStyle);

HSSFCell content_cell_7 = contentRow.createCell(6);
content_cell_7.setCellValue(differenceNumber);
content_cell_7.setCellStyle(contentStyle);

HSSFCell content_cell_8 = contentRow.createCell(7);
content_cell_8.setCellValue(adjustmentMoney);
content_cell_8.setCellStyle(contentStyle);

HSSFCell content_cell_9 = contentRow.createCell(8);
content_cell_9.setCellValue(adjustmentNumber);
content_cell_9.setCellStyle(contentStyle);

HSSFCell content_cell_10 = contentRow.createCell(9);
content_cell_10.setCellValue(state);
content_cell_10.setCellStyle(contentStyle);

}
return wb;
} catch (Exception e) {
e.getStackTrace();
}

return null;
}

//创建HSSFWorkbook工作薄对象
public static HSSFWorkbook export_day(List<Map<String,Object>> list,String data){
try {
//创建工作薄对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建标题行样式
HSSFCellStyle headStyle = headStyle(wb);
//创建内容行样式
HSSFCellStyle contentStyle = contentStyle(wb);

//创建表
HSSFSheet sheet_1 = wb.createSheet(data+"对账信息");
//设置表的默认列宽
sheet_1.setDefaultColumnWidth(30);

//创建标题行
HSSFRow headRow = sheet_1.createRow(0);
HSSFCell head_cell_1 = headRow.createCell(0); //创建标题行第一列
head_cell_1.setCellValue("对账时间"); //第一列内容
head_cell_1.setCellStyle(headStyle); //将标题行样式添加

HSSFCell head_cell_2 = headRow.createCell(1);
head_cell_2.setCellValue("商户名称");
head_cell_2.setCellStyle(headStyle);

HSSFCell head_cell_3 = headRow.createCell(2);
head_cell_3.setCellValue("系统订单号");
head_cell_3.setCellStyle(headStyle);

HSSFCell head_cell_4 = headRow.createCell(3);
head_cell_4.setCellValue("系统订单金额");
head_cell_4.setCellStyle(headStyle);

HSSFCell head_cell_5 = headRow.createCell(4);
head_cell_5.setCellValue("通道订单号");
head_cell_5.setCellStyle(headStyle);

HSSFCell head_cell_6 = headRow.createCell(5);
head_cell_6.setCellValue("通道订单金额");
head_cell_6.setCellStyle(headStyle);

HSSFCell head_cell_7 = headRow.createCell(6);
head_cell_7.setCellValue("订单状态");
head_cell_7.setCellStyle(headStyle);

HSSFCell head_cell_8 = headRow.createCell(7);
head_cell_8.setCellValue("订单类型");
head_cell_8.setCellStyle(headStyle);

HSSFCell head_cell_9 = headRow.createCell(8);
head_cell_9.setCellValue("交易时间");
head_cell_9.setCellStyle(headStyle);

//为内容行添加数据和样式
for (int i = 1; i <= list.size(); i++) {
HSSFRow contentRow = sheet_1.createRow(i);
Map<String, Object> map=list.get(i-1);
String accountsTime=map.get("accountsTime").toString();
String shopName=map.get("shopName").toString();

String sysNo="";
if(map.get("sysNo")!=null){
sysNo=map.get("sysNo").toString();
}
String sysMoney="";
if(map.get("sysMoney")!=null){
sysMoney=map.get("sysMoney").toString();
}
String passagewayNo="";
if(map.get("passagewayNo")!=null){
passagewayNo=map.get("passagewayNo").toString();
}
String passagewayMoney="";
if(map.get("passagewayMoney")!=null){
passagewayMoney=map.get("passagewayMoney").toString();
}
String orderType="";
if(map.get("orderType")!=null){
int type=Integer.valueOf(map.get("orderType").toString()).intValue();
switch (type) {//0正常1订单缺失2通道缺失3金额不正确
case 0:
orderType="正常";
break;
case 1:
orderType="订单缺失";
break;
case 2:
orderType="通道缺失";
break;
default:
orderType="金额不正确";
break;
}
}
String orderState="";
if(map.get("orderState")!=null){
int state=Integer.valueOf(map.get("orderState").toString()).intValue();
switch (state) {//0正常1未对账2已对帐3已调账
case 0:
orderState="正常";
break;
case 1:
orderState="未对账";
break;
case 2:
orderState="已对帐";
break;
default:
orderState="已调账";
break;
}
}

String transactionTime=map.get("transactionTime").toString();
HSSFCell content_cell_1 = contentRow.createCell(0);
content_cell_1.setCellValue(accountsTime);
content_cell_1.setCellStyle(contentStyle);

HSSFCell content_cell_2 = contentRow.createCell(1);
content_cell_2.setCellValue(shopName);
content_cell_2.setCellStyle(contentStyle);

HSSFCell content_cell_3 = contentRow.createCell(2);
content_cell_3.setCellValue(sysNo);
content_cell_3.setCellStyle(contentStyle);

HSSFCell content_cell_4 = contentRow.createCell(3);
content_cell_4.setCellValue(sysMoney);
content_cell_4.setCellStyle(contentStyle);

HSSFCell content_cell_5 = contentRow.createCell(4);
content_cell_5.setCellValue(passagewayNo);
content_cell_5.setCellStyle(contentStyle);

HSSFCell content_cell_6 = contentRow.createCell(5);
content_cell_6.setCellValue(passagewayMoney);
content_cell_6.setCellStyle(contentStyle);

HSSFCell content_cell_7 = contentRow.createCell(6);
content_cell_7.setCellValue(orderType);
content_cell_7.setCellStyle(contentStyle);

HSSFCell content_cell_8 = contentRow.createCell(7);
content_cell_8.setCellValue(orderState);
content_cell_8.setCellStyle(contentStyle);

HSSFCell content_cell_9 = contentRow.createCell(8);
content_cell_9.setCellValue(transactionTime);
content_cell_9.setCellStyle(contentStyle);

}
return wb;
} catch (Exception e) {
e.getStackTrace();
}

return null;
}
/**
* 创建标题行样式
* @param wb
* @return
*/
public static HSSFCellStyle headStyle(HSSFWorkbook wb){
HSSFCellStyle headStyle = wb.createCellStyle(); //创建样式对象
HSSFFont headFont = wb.createFont(); //创建字体
headFont.setFontName("微软雅黑");
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFFont.COLOR_RED);

headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setFont(headFont);
return headStyle;
}

/**
* 创建内容行样式
* @param wb
* @return
*/
public static HSSFCellStyle contentStyle(HSSFWorkbook wb){
HSSFCellStyle contentStyle = wb.createCellStyle();
HSSFFont contentFont = wb.createFont();
contentFont.setFontName("微软雅黑");
contentFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
contentFont.setColor(HSSFFont.COLOR_NORMAL);

contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setFont(contentFont);
return contentStyle;
}
}

/**
* 导出年月的对账单
* @param model
* @param request
* @param response
* @param session
* @param payType
* @param date
* @param type
* @return
*/
@ResponseBody
@RequestMapping(value = "http://www.mamicode.com/FINANCIALCENTER/exportByYM")
public ModelAndView exportByYM(Model model, HttpServletRequest request,HttpServletResponse response, HttpSession session,
@RequestParam(value = "http://www.mamicode.com/payType", required = true) String payType,
@RequestParam(value = "http://www.mamicode.com/date", required = true) String date,
@RequestParam(value = "http://www.mamicode.com/type", required = true) String type) {
try {
if(StringUtils.isNotBlank(payType)){
if(payType.equals("1")){
payType="7";
}
if(payType.equals("2")){
payType="6";
}
if(payType.equals("3")){
payType="4";
}
}
Criteria criteria = new Criteria();
criteria.getCondition().put("state", "");
criteria.getCondition().put("type", payType);
SessionUser user = getSessionUser(session);
TUSER tUser = this.tUSERService.queryById(user.getUserId());
Integer agentId=0;
if(tUser.getTYPE().equals(SystemConfig.USER_TYPE_AGENT_T)){
agentId=tUser.getAGENTID();
}
criteria.getCondition().put("agentId",agentId);

List<Map<String,Object>> maps=null;
if(type.equals("1")){//年
criteria.getCondition().put("year", date);
maps=this.adjustService.getOrderByYearAndType(criteria);
}
if(type.equals("2")){//月
criteria.getCondition().put("date", date);
maps=this.adjustService.getOrderByMonthAndType(criteria);
}
if(type.equals("3")){//日
criteria.getCondition().put("day", date);
maps=this.adjustService.getOrderByDayAndType(criteria);
}
if(maps!=null&&maps.size()>0){
//调用方法创建HSSFWorkbook工作簿对象
HSSFWorkbook wb = null;
if(type.equals("3")){//日
wb = ExcelUtil.export_day(maps,date);
}else{
wb = ExcelUtil.export_text(maps,date);
}
try {
//定义导出文件的名称，看不懂的同学可以先行了解一下文件下载
Random r = new Random();
int n2 = r.nextInt(1000);
String name=date+"_"+n2+".xls";
String fileName = new String(name.getBytes("UTF-8"),"ISO-8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename="+fileName);
OutputStream os = response.getOutputStream();
//将工作薄写入到输出流中
wb.write(os);
os.close();
} catch (Exception e) {
e.getStackTrace();
}
}else{
model.addAttribute(ErrorMsg.KEY_CODE, ErrorMsg.CALL_FAIL);
model.addAttribute(ErrorMsg.KEY_MESSAGE, date+"该日期没有交易数据");
return new ModelAndView(new MappingJacksonJsonView());
}
} catch (Exception e) {
e.printStackTrace();
model.addAttribute(ErrorMsg.KEY_CODE, ErrorMsg.CALL_FAIL);
model.addAttribute(ErrorMsg.KEY_MESSAGE, "查詢失败！");
}
return new ModelAndView(new MappingJacksonJsonView());
}

/**
* 导出excel
*/
exports.exportExcel = function(me){
var year = $cmt_year_details.param.year;
var payType = $cmt_year_details.payType;
var type = 1;

confirmDialog("导出提示", "确定导出Excel么？", {
okAction : function() {
$("#"+$cmt_year_details.page_id).loading(‘show‘);
location.href=http://www.mamicode.com/$cmt_year_details.config.exportByYM+‘&date=‘+year+‘&payType=‘+payType+‘&type=‘+type;
$("#"+$cmt_year_details.page_id).loading(‘hide‘);
}
});
}

java 数据导入到exc ,并下载