using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using Excel;
using System.Reflection;
using whims.model;

public partial class ExcelReport : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        string save_path = "", tick = "";
        ExcelOperate excelOperate = new ExcelOperate();
        string temp_path = Server.MapPath("~/xls_files");//生成的文件存放路径
        if (!Directory.Exists(temp_path))
        {
            Directory.CreateDirectory(temp_path);
        }

        try
        {
            DataView dv = Cache["ReportByNone"] as DataView;    //***
            //建立一个Excel.Application的新进程
            Excel.Application app = new Excel.Application();
            if (app == null)
            {
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Workbooks workbooks = app.Workbooks;
            _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Sheets sheets = workbook.Worksheets;
            _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
            if (worksheet == null)
            {
                return;
            }
            excelOperate.Merge(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 10]);//横向合并
            excelOperate.WriteRange(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 10], "询价单");
            excelOperate.SetBold(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]); //黑体
            excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中

            excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 18);//字体大小
            excelOperate.SetRowHeight(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 35);//行高

            //公司
            worksheet.Cells[2, 1] = "买  方：";
            excelOperate.Merge(worksheet, worksheet.Cells[2, 2], worksheet.Cells[2, 3]);//横向合并
            excelOperate.WriteRange(worksheet, worksheet.Cells[2, 2], worksheet.Cells[2, 3], "陕西渭河重化工集团有限责任公司");
            //联系人
            Userinfo userinfo = Session["UserID"] as Userinfo;
            worksheet.Cells[3, 1] = "联系人：";
            excelOperate.Merge(worksheet, worksheet.Cells[3, 2], worksheet.Cells[3, 3]);//横向合并
            excelOperate.WriteRange(worksheet, worksheet.Cells[3, 2], worksheet.Cells[3, 3], userinfo.USERNAME);
            //电话
            worksheet.Cells[4, 1] = "电  话：";
            excelOperate.Merge(worksheet, worksheet.Cells[4, 2], worksheet.Cells[4, 3]);//横向合并
            excelOperate.WriteRange(worksheet, worksheet.Cells[4, 2], worksheet.Cells[4, 3], userinfo.TEL);

            //传真
            worksheet.Cells[4, 4] = "传真：";
            worksheet.Cells[4, 5] = userinfo.UCZ;

            worksheet.Cells[4, 9] = "打印日期：";
            worksheet.Cells[4, 10] = DateTime.Now.ToShortDateString();

            worksheet.Cells[5, 1] = "物资编码";
            worksheet.Cells[5, 2] = "物资名称";
            worksheet.Cells[5, 3] = "规格型号";
            worksheet.Cells[5, 4] = "材质";
            worksheet.Cells[5, 5] = "单位";
            worksheet.Cells[5, 6] = "要求日期";
            worksheet.Cells[5, 7] = "数量";
            worksheet.Cells[5, 8] = "单价";
            worksheet.Cells[5, 9] = "总金额";
            worksheet.Cells[5, 10] = "承诺日期";
            worksheet.Cells[5, 11] = "备注";

            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[5, 11]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
            excelOperate.SetBold(worksheet, worksheet.Cells[5, 1], worksheet.Cells[5, 11]); //黑体
            excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[5, 1], worksheet.Cells[5, 11]);//居中
            excelOperate.SetFontSize(worksheet, worksheet.Cells[5, 1], worksheet.Cells[5, 11], 12);//字体大小
            excelOperate.SetRowHeight(worksheet, worksheet.Cells[5, 1], worksheet.Cells[5, 11], 24);//行高

            int rowNum = dv.Count;
            for (int i = 0; i < rowNum; i++)
            {
                worksheet.Cells[6 + i, 1] = "'" + dv[i].Row[0].ToString();  //0物资编码
                worksheet.Cells[6 + i, 2] = dv[i].Row[1].ToString();       //1物资名称
                worksheet.Cells[6 + i, 3] = dv[i].Row[2].ToString();   //  2规格型号 3物资材质 4计量单位 5系统码 6数量 7交货日期
                worksheet.Cells[6 + i, 4] = dv[i].Row[3].ToString();    
                worksheet.Cells[6 + i, 5] = dv[i].Row[4].ToString();   
                worksheet.Cells[6 + i, 6] = Convert.ToDateTime(dv[i].Row[7].ToString()).ToShortDateString();   
                //y
                worksheet.Cells[6 + i, 7] = dv[i].Row[6].ToString();     //
            }
            excelOperate.SetColumnWidth(worksheet, "A", 12);
            excelOperate.SetColumnWidth(worksheet, "B", 14);
            excelOperate.SetColumnWidth(worksheet, "C", 20);
            excelOperate.SetColumnWidth(worksheet, "D",6);
            excelOperate.SetColumnWidth(worksheet, "E", 5);
            excelOperate.SetColumnWidth(worksheet, "F", 9);  //交货日期
            //Y
            excelOperate.SetColumnWidth(worksheet, "G", 5);  //数量
            excelOperate.SetColumnWidth(worksheet, "H", 5);
            excelOperate.SetColumnWidth(worksheet, "I", 15);
            excelOperate.SetColumnWidth(worksheet, "J", 9);

            worksheet.Name = "询价单工作区";
            tick = DateTime.Now.Ticks.ToString();
            save_path = temp_path + "\\" + tick + ".xls";
            workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
            Response.Write(save_path);
        }
        catch
        {
            Response.Write("Error");
        }
        finally
        {
            Response.End();
        }

    }


}