using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Windows.Media.Media3D;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace 練習
{
    public partial class About : Page
    {


        protected void btnDownload_Click(object sender, EventArgs e)
        {

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelPackage excelPackage = new ExcelPackage();
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");



            worksheet.Cells["A1"].Value = "XX資訊股份有限公司";
            worksheet.Cells["A1"].Style.Font.Size = 16;
            worksheet.Cells["A1:H1"].Merge = true;
            worksheet.Cells["A1:H1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:H1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;


            worksheet.Cells["A2"].Value = "客戶寄出統計表";
            worksheet.Cells["A2"].Style.Font.Size = 18;
            worksheet.Cells["A2:H2"].Merge = true;
            worksheet.Cells["A2:H2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A2:H2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center; 


            worksheet.Cells["A3"].Value = "客戶編號：";
            worksheet.Cells["B3"].Value = "B-0004 ~ B-0013";
            worksheet.Cells["F3"].Value = "頁次：";
            worksheet.Cells["G3"].Value = 1;
            worksheet.Cells["A3:H3"].Style.Font.Size = 12;
            worksheet.Cells["A3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;


            worksheet.Cells["A4"].Value = "日期範圍：";
            worksheet.Cells["B4"].Value = "2001/01/06 ~ 2006/03/02";
            worksheet.Cells["F4"].Value = "列印日期：";
            worksheet.Cells["G4"].Value = "2010/10/28";
            worksheet.Cells["A4:H4"].Style.Font.Size = 12;
            worksheet.Cells["A4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;


            worksheet.Cells["A5"].Value = "客戶簡稱：XX科技(-002)";
            worksheet.Cells["A5:H5"].Style.Font.Size = 12;
            worksheet.Cells["A5:H5"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A5:H5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A5:H5"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A5:H5"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A5:H5"].Merge = true;
            worksheet.Cells["A5:H5"].Style.Fill.PatternType = ExcelFillStyle.Solid; 
            worksheet.Cells["A5:H5"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);


            string[] headers = { "產品代碼", "品名", "單位", "訂購數量", "已取消量", "已出數量", "未出數量", "預出日期" };
            int headerRow = 6;
            for (int i = 0; i < headers.Length; i++)
            {
                worksheet.Cells[headerRow, i + 1].Value = headers[i];
                worksheet.Cells[headerRow, i + 1].Style.Font.Size = 12;
                worksheet.Cells[headerRow, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                worksheet.Cells[headerRow, i + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                worksheet.Cells[headerRow, i + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                worksheet.Cells[headerRow, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                worksheet.Cells[headerRow, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells[headerRow, i + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                worksheet.Cells[headerRow, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; 
                worksheet.Cells[headerRow, i + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
            }


            worksheet.Cells["A7"].Value = "單據日期：2001/02/22";
            worksheet.Cells["B7"].Value = "單號：900222002";
            worksheet.Cells["C7"].Value = "客戶訂購單";
            worksheet.Cells["D7"].Value = "業務員：XXX(6007)";
            worksheet.Cells["A7:H7"].Style.Font.Size = 12;
            worksheet.Cells["A7:H7"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A7:H7"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A7"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["H7"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            

            string[,] dataRows = {
                                 { "CPA1-CASE-0001", "", "", "", "", "", "", "" },
                                 { "CPA1-CASE-0002", "", "", "", "", "", "", "" },
                                 { "CPA1-CASE-0003", "", "", "", "", "", "", "" }
                                 };
            int dataStartRow = 8;
            for (int i = 0; i < dataRows.GetLength(0); i++)
            {
                for (int j = 0; j < dataRows.GetLength(1); j++)
                {
                    worksheet.Cells[dataStartRow + i, j + 1].Value = dataRows[i, j];
                    worksheet.Cells[dataStartRow + i, j + 1].Style.Font.Size = 12;
                    worksheet.Cells[dataStartRow + i, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells[dataStartRow + i, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells[dataStartRow + i, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells[dataStartRow + i, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                }
            }


            worksheet.Cells["B11"].Value = "小計：";
            worksheet.Cells["D11"].Formula = "=SUM(D8:D10)";
            worksheet.Cells["E11"].Formula = "=SUM(E8:E10)";
            worksheet.Cells["F11"].Formula = "=SUM(F8:F10)";
            worksheet.Cells["G11"].Formula = "=SUM(G8:G10)";
            worksheet.Cells["B11:G11"].Style.Font.Size = 12;
            worksheet.Cells["B11:C11"].Merge = true;
            worksheet.Cells["B12:G12"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["B11:H11"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["B11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["B11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;



            worksheet.Cells.AutoFitColumns(6, 26);//自適應寬度設定


            

            // 將 Excel 檔案儲存到記憶體流中
            MemoryStream stream = new MemoryStream();
            excelPackage.SaveAs(stream);

            // 將記憶體流的位置設定為起始位置
            stream.Position = 0;

            // 設定回應標頭，指定下載的檔案名稱和檔案類型
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=excel_file.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            // 將 Excel 檔案資料寫入回應串流
            Response.BinaryWrite(stream.ToArray());
            Response.End();

        }
    }
}