package com.senlany.service.impl.k3;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.senlany.comm.ConnUtil;
import com.senlany.mapper.k3.FinancialReportMapper;
/**
* @author Amos E-mail : zhangxiaopeng@wlspjt.com
* @version 创建时间:2015年5月28日 下午2:36:37
* @description
* 因程序未按照标准java程序结构创建
* 现将程序逻辑放在service中
*/
@Service
public class FinancialReportService {
private static Log log = LogFactory.getLog(FinancialReportService.class);
@Autowired
private FinancialReportMapper financialReportMapper;
/**
* @param map
* @return
*/
public List getList(Map map){
return financialReportMapper.getList(map);
}
/**
* 导出财务报表
* @param begindate
* @param enddate
*/
public void exportExcel(HttpServletRequest request, HttpServletResponse response){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
conn = ConnUtil.getConn();
WritableWorkbook workbook = null;
WritableSheet sheet = null;
Label label = null;
Label label0 = null;
try {
/**
* 此程序块必须放在此处执行
* 因此处调用函数中有批量更新操作
*/
conn.setAutoCommit(false);
st = (Statement) conn.createStatement();
st.addBatch("dbo.WL_F_MAIN");
st.executeBatch();
conn.commit();
//查询全局临时表
CallableStatement cs = conn.prepareCall("SELECT * FROM ##financial_report_table");
rs = cs.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 创建Excel表
try {
/**
* 将工作薄输出至前台文件输出流
*/
String contentType = "application/vnd.ms-excel";
response.reset();
response.setContentType(contentType);
response.setHeader("Content-Disposition", "attachment; filename=\"" + new String("休闲营销财务报表.xls".getBytes("gb2312"), "ISO8859-1") + "\"");
OutputStream out = response.getOutputStream();
workbook = Workbook.createWorkbook(out);
// 创建Excel表中的sheet
sheet = workbook.createSheet("First Sheet", 0);
// 向Excel中添加数据
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
String colName = null;
int row = 2;
int income_count = 0;//收入计数
int inc_cat_xxd_ct = 0;//收入_休闲豆计数
int inc_cat_rsb_ct = 0;//收入_肉松饼计数
int inc_cat_dxm_ct = 0;//收入_点心面计数
int gift_count = 0;//搭赠计数
int gif_cat_xxd_ct = 0;//搭赠_休闲豆计数
int gif_cat_rsb_ct = 0;//搭赠_肉松饼计数
int gif_cat_dxm_ct = 0;//搭赠_点心面计数
int fee_count = 0;//运费计数
// 添加标题
for (int i = 0; i < columnCount; i++) {
colName = rsmd.getColumnName(i + 1);
if("FCustID".equals(colName)){
colName = "客户编号";
label = new Label(i, 0, colName);
}else if("BIGAREA".equals(colName)){
colName = "大区";
label = new Label(i, 0, colName);
}else if("AREAMANAGER".equals(colName)){
colName = "大区经理";
label = new Label(i, 0, colName);
}else if("PROVINCE".equals(colName)){
colName = "省区";
label = new Label(i, 0, colName);
}else if("PROVINCEMANAGER".equals(colName)){
colName = "省区经理";
label = new Label(i,0, colName);
}else if("CLIENT".equals(colName)){
colName = "客户";
label = new Label(i,0, colName);
}else if("SALESMAN".equals(colName)){
colName = "业务员";
label = new Label(i,0, colName);
} else if("DATE".equals(colName)){
colName = "日期";
label = new Label(i, 0, colName);
}else{
if(colName.contains("QUANTITY")){
label = new Label(i, row+1, "数量");
sheet.addCell(label);
}else if(colName.contains("PRICE")){
label = new Label(i, row+1, "单价");
sheet.addCell(label);
}else if(colName.contains("AMOUNT")){
label = new Label(i, row+1, "金额");
sheet.addCell(label);
}
label = new Label(i, row, colName);
}
if(colName.endsWith("0")){
income_count++;
if(colName.startsWith("xxd")){
inc_cat_xxd_ct ++;
}else if(colName.startsWith("rsb")){
inc_cat_rsb_ct ++;
}else{
inc_cat_dxm_ct ++;
}
}else if(colName.endsWith("1")){
gift_count++;
if(colName.startsWith("xxd")){
gif_cat_xxd_ct ++;
}else if(colName.startsWith("rsb")){
gif_cat_rsb_ct ++;
}else{
gif_cat_dxm_ct ++;
}
}else if(colName.endsWith("3")){
fee_count++;
}
log.debug("标题:"+i+"---"+row +"---"+ colName);
sheet.addCell(label);
}
/**
* 单元格合并各参数含义
* mergeCells(startColNum, startRowNum, endColNum, endRowNum);
*/
sheet.mergeCells(0, 0, 0, 3);
sheet.mergeCells(1, 0, 0, 3);
sheet.mergeCells(2, 0, 0, 3);
sheet.mergeCells(3, 0, 0, 3);
sheet.mergeCells(4, 0, 0, 3);
sheet.mergeCells(5 ,0, 0, 3);
sheet.mergeCells(6, 0, 0, 3);
sheet.mergeCells(7, 0, 0, 3);
sheet.mergeCells(8, 0,7+income_count, 0);
label = new Label(8, 0, "销售收入");
sheet.addCell(label);
sheet.mergeCells(8, 1,7+inc_cat_xxd_ct,1);
label = new Label(8,1, "休闲豆");
sheet.addCell(label);
sheet.mergeCells(8+inc_cat_xxd_ct, 1,7+inc_cat_xxd_ct+inc_cat_rsb_ct,1);
label = new Label(8+inc_cat_xxd_ct, 1, "肉松饼");
sheet.addCell(label);
sheet.mergeCells(8+inc_cat_xxd_ct+inc_cat_rsb_ct, 1,7+inc_cat_xxd_ct+inc_cat_rsb_ct+inc_cat_dxm_ct,1);
label = new Label(8+inc_cat_xxd_ct+inc_cat_rsb_ct, 1, "点心面");
sheet.addCell(label);
sheet.mergeCells(8+income_count, 0,7+income_count+gift_count,0);
label = new Label(8+income_count, 0, "搭赠");
sheet.addCell(label);
sheet.mergeCells(8+income_count, 1,7+income_count+gif_cat_xxd_ct, 1);
label = new Label(8+income_count, 1, "休闲豆");
sheet.addCell(label);
sheet.mergeCells(8+income_count+gif_cat_xxd_ct, 1,7+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct,1);
label = new Label(8+income_count+gif_cat_xxd_ct, 1, "肉松饼");
sheet.addCell(label);
sheet.mergeCells(8+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct, 1,7+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct+gif_cat_dxm_ct,1);
label = new Label(8+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct, 1, "点心面");
sheet.addCell(label);
sheet.mergeCells(8+income_count+gift_count, 0,7+income_count+gift_count+fee_count,0);
label = new Label(8+income_count+gift_count, 0, "运费");
sheet.addCell(label);
row += 2;
// row ++;
log.debug("写入标题成功");
while (rs.next()) {
for (int i = 0; i < columnCount; i++) {
label = new Label(i, row, rs.getString(i + 1));
log.debug("行:"+i+"---"+row +"---"+ rs.getString(i+1));
sheet.addCell(label);
}
row++;
}
log.debug("写入内容成功");
// 关闭文件
workbook.write();
workbook.close();
log.info("数据成功写入Excel");
} catch (SQLException e) {
log.debug(e.getMessage());
} catch (RowsExceededException e) {
log.debug(e.getMessage());
} catch (WriteException e) {
log.debug(e.getMessage());
} catch (IOException e) {
log.debug(e.getMessage());
} finally {
try {
workbook.close();
//删除全局临时表
st = (Statement) conn.createStatement();
st.addBatch("if object_id('tempdb..##financial_report_table') is not null DROP TABLE ##financial_report_table;");
st.executeBatch();
conn.commit();
//释放数据库相关链接
ConnUtil.free(null, st, conn);
} catch (Exception e) {
log.debug(e.getMessage());
}
}
}
}
分享到:
2015-05-30 15:54
浏览 414
评论