<%@ page language="java" pageEncoding="gb2312"%>
<%@ page
	import="com.zhuozhengsoft.pageoffice.*,com.zhuozhengsoft.pageoffice.excelwriter.*,javax.servlet.*,javax.servlet.http.*,java.sql.*,java.text.SimpleDateFormat,java.util.Date,java.util.regex.Pattern,java.util.regex.Matcher,java.util.Date,java.text.NumberFormat,java.util.Locale,java.awt.*"%>
<%@ taglib uri="http://java.pageoffice.cn" prefix="po"%>

<%
	Object userName = session.getAttribute("UserName");
	if (userName == null || userName.toString().length() <= 0) {
		response.sendRedirect("login.jsp?tz=true");
	}

	Workbook wb = new Workbook();
	Sheet sheet = wb.openSheet("查询表");
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//载入驱动程序类别
	String strUrl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="
			+ this.getServletContext().getRealPath("demodata/")
			+ "\\demo.mdb";
	Connection conn = DriverManager.getConnection(strUrl);
	Statement stmt = conn.createStatement();
	String sql = "SELECT OrderNum,OrderDate,CustName,SalesName,Amount from OrderMaster order by ID desc";
	ResultSet rs = stmt.executeQuery(sql);
	int rowCount = 0;//记录行数
	String salesName = "";
	String date = "";
	String orderNum = "";
	String custName = "";
	String amount = "";
	double totalMoney = 0.00;
	NumberFormat formater = NumberFormat
			.getCurrencyInstance(Locale.CHINA);//货币类型
	while (rs.next()) {
		orderNum = rs.getString("OrderNum");
		date = rs.getString("OrderDate");
		custName = rs.getString("CustName");
		salesName = rs.getString("SalesName");
		amount = rs.getString("Amount");

		sheet.openCell("B" + (5 + rowCount)).setValue(orderNum);
		if (date != null && date.length() > 0) {
			sheet.openCell("C" + (5 + rowCount)).setValue(
					new SimpleDateFormat("yyyy/MM/dd")
							.format(new SimpleDateFormat("yyyy-MM-dd")
									.parse(date)));
		}
		sheet.openCell("D" + (5 + rowCount)).setValue(custName);
		sheet.openCell("E" + (5 + rowCount)).setValue(salesName);
		sheet.openCell("F" + (5 + rowCount)).setValue(
				formater.format(Double.parseDouble(amount)));
		totalMoney += Double.parseDouble(amount);

		if (rowCount % 2 == 0) {
			//设置背景色
			sheet.openTable(
					"B" + (5 + rowCount) + ":F" + (5 + rowCount))
					.setBackColor(new Color(253, 233, 217));
		}
		rowCount++;
	}

	//设置前景色
	sheet.openTable("B5:F" + (rowCount + 4)).setForeColor(
			new Color(148, 138, 84));

	//水平方向对齐方式
	sheet.openTable("B5:F" + (rowCount + 4)).setHorizontalAlignment(
			XlHAlign.xlHAlignLeft);
	sheet.openTable("C5:C" + (rowCount + 4)).setHorizontalAlignment(
			XlHAlign.xlHAlignCenter);
	sheet.openTable("E5:E" + (rowCount + 4)).setHorizontalAlignment(
			XlHAlign.xlHAlignCenter);
	sheet.openTable("F5:F" + (rowCount + 4)).setHorizontalAlignment(
			XlHAlign.xlHAlignRight);
	//竖直方向对齐方式
	sheet.openTable("B5:F" + (rowCount + 4)).setVerticalAlignment(
			XlVAlign.xlVAlignCenter);

	//合计：

	//合并单元格
	sheet.openTable("B" + (rowCount + 5) + ":F" + (rowCount + 5))
			.merge();
	//行高
	sheet.openTable("B5:F" + (rowCount + 6)).setRowHeight(18);
	sheet.openTable("B" + (rowCount + 5) + ":F" + (rowCount + 6))
			.setHorizontalAlignment(XlHAlign.xlHAlignLeft);
	sheet.openTable("B" + (rowCount + 5) + ":F" + (rowCount + 6))
			.setVerticalAlignment(XlVAlign.xlVAlignCenter);

	sheet.openCell("B" + (rowCount + 6)).setValue("合计");
	sheet.openTable("C" + (rowCount + 6) + ":E" + (rowCount + 6))
			.merge();

	sheet.openCell("F" + (rowCount + 6)).setValue(
			formater.format(totalMoney));
	sheet.openTable("F" + (rowCount + 6) + ":F" + (rowCount + 6))
			.setHorizontalAlignment(XlHAlign.xlHAlignRight);
	sheet.openTable("B" + (rowCount + 6) + ":F" + (rowCount + 6))
			.setVerticalAlignment(XlVAlign.xlVAlignCenter);

	//设置字体：大小、名称
	sheet.openTable("B5:F" + (rowCount + 6)).getFont().setSize(9);
	sheet.openTable("B5:F" + (rowCount + 6)).getFont().setName("宋体");

	//设置Table的边框样式：样式、宽度、颜色(多种边框样式重叠时，需创建Table对象才可实现样式的叠加覆盖)
	Table table = sheet.openTable("B" + (rowCount + 6) + ":F"
			+ (rowCount + 6));
	table.getBorder().setBorderType(XlBorderType.xlTopEdge);
	table.getBorder().setWeight(XlBorderWeight.xlThin);
	table.getBorder().setLineColor(new Color(148, 138, 84));

	table.close();
	rs.close();
	stmt.close();
	conn.close();
	
	//禁用右键、双击
	wb.setDisableSheetDoubleClick(true);
	wb.setDisableSheetRightClick(true);
	
	PageOfficeCtrl poCtrl1 = new PageOfficeCtrl(request);
	poCtrl1.setServerPage("poserver.do"); //此行必须
	String fileName = "OrderQuery.xls";
	poCtrl1.setWriter(wb);

	// 创建自定义菜单栏
	poCtrl1.addCustomToolButton("页面设置", "ShowPageSetupDlg()", 5);
	poCtrl1.addCustomToolButton("打印", "ShowPrintDlg()", 6);
	poCtrl1.addCustomToolButton("打印预览", "PrintPreView()", 7);
	poCtrl1.addCustomToolButton("另存到本机", "SaveAs()", 1);
	poCtrl1.addCustomToolButton("全屏切换", "SetFullScreen()", 4);

	poCtrl1.setMenubar(false);//隐藏菜单栏
	poCtrl1.setOfficeToolbars(false);//隐藏工具栏

	poCtrl1.webOpen("doc/" + fileName, OpenModeType.xlsReadOnly, "");
	poCtrl1.setTagId("PageOfficeCtrl1"); //此行必须
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
		<link rel="stylesheet" href="css/style.css" type="text/css"></link>
		<!--[if lte IE 6]>
<script type="text/javascript" src="js/belatedPNG.js"></script>
<script type="text/javascript">
  DD_belatedPNG.fix('.png_bg,.png_bg a:hover,img,li');
</script>
<![endif]-->
		<title>查询表</title>
	</head>
	<body>
		<!--header-->
		<div class="zz-headBox clearfix">
			<div class="zz-head mc">
				<!--logo-->
				<div class="logo fl">
					<a href="home.html"> <img src="images/logo.png" alt="" />
					</a>
				</div>
				<!--logo end-->
				<ul class="head-rightUl fr">
                <li><a target="_blank" href="http://www.zhuozhengsoft.com">卓正网站</a></li>
                <li><a target="_blank" href="http://www.zhuozhengsoft.com/poask/index.asp">客户问吧</a></li>
                <li><a target="_blank" href="http://www.zhuozhengsoft.com/contact-us.html">联系我们</a></li>
				</ul>
			</div>
		</div>
		<!--header end-->
		<!--a title-->
		<div class=" topTitle">
			<ul>
				<li class="pd-left">
					销售订单管理系统示例
				</li>
				<li>
					<font>当前用户：</font><%=userName%></li>
				<li>
					<font>当前系统日期：</font><%=new SimpleDateFormat("yyyy/MM/dd").format(new Date())%></li>
				<li>
					<font>当前模块：</font>查询表
				</li>
			</ul>
		</div>
		<!--content-->
		<div class="zz-content mc clearfix pd-28">
			<!--left-->
			<div class="zz-contentLeft fl">
				<ul class="left-ul">
					<h2 class="fs-12">
						用户功能区
					</h2>
					<li>
						<a href="OrderList.jsp">订单列表</a>
					</li>
					<li>
						<a href="NewOrder.jsp">新建订单</a>
					</li>
					<li>
						<a href="OrderStat.jsp">统计图表</a>
					</li>
					<li style="background: #d0eaf7; display: block;">
						<a href="OrderStat2.jsp">查询表</a>
					</li>
					<li class="bo-n">
						<a href="logout.jsp">退出系统</a>
					</li>
				</ul>
			</div>
			<div class="zz-contentRight fl">
				<!--表格内容-->
				<%--<table class="zz-talbe">
            <thead>
                <tr>
                    <th class="text-cent" style=" width:15%">订单编号</th>
                    <th class="text-cent" style=" width:11%">日期</th>
                    <th class="text-cent" >客户名称</th>
                    <th class="text-cent" style=" width:9%">业务员</th>
                    <th class="text-cent" style=" width:12%">订单金额</th>
                    <th class="text-cent" style=" width:26%">操作</th>
                </tr>
            </thead>
            <tbody>
                <asp:Literal ID="Literal1" runat="server"></asp:Literal>
            </tbody>
        </table>--%>
				<div style="width: 860px; height: 750px;">
					<!-- *********************pageoffice组件的使用 **************************-->
					<script language="javascript">
	//另存到本机
	function SaveAs() {
		document.getElementById("PageOfficeCtrl1").ShowDialog(2);
	}
	//页面设置
	function ShowPageSetupDlg() {
		document.getElementById("PageOfficeCtrl1").ShowDialog(5);
	}
	//打印
	function ShowPrintDlg() {
		document.getElementById("PageOfficeCtrl1").ShowDialog(4);
	}
	//打印预览
	function PrintPreView() {
		document.getElementById("PageOfficeCtrl1").PrintPreview();
	}
	//全屏
	function SetFullScreen() {
		document.getElementById("PageOfficeCtrl1").FullScreen = !document
				.getElementById("PageOfficeCtrl1").FullScreen;
	}
</script>
					<po:PageOfficeCtrl id="PageOfficeCtrl1" />
					<!-- *********************pageoffice组件的使用 **************************-->
				</div>
			</div>
			<!--内容区-->
		</div>
		<!--content end-->
		<!--footer-->
		<div class="login-footer clearfix">
			Copyright 2013 北京卓正志远软件有限公司
		</div>
		<!--footer end-->
	</body>
</html>
