Ejemplo n.º 1
0
        internal Worksheet(ExcelXmlWorkbook parent)
        {
            if (parent == null)
            {
                throw new ArgumentNullException("parent");
            }

            ParentBook = parent;

            PrintOptions = new PrintOptions();

            PrintOptions.Layout      = PageLayout.None;
            PrintOptions.Orientation = PageOrientation.None;

            _Rows        = new List <Row>();
            _Columns     = new List <Column>();
            _MergedCells = new List <Range>();

            TabColor = -1;

            PrintOptions.FitHeight = 1;
            PrintOptions.FitWidth  = 1;
            PrintOptions.Scale     = 100;

            PrintOptions.ResetMargins();
        }
Ejemplo n.º 2
0
		internal Column(Worksheet parent)
		{
			if (parent == null)
				throw new ArgumentNullException("parent");

			ParentBook = parent.ParentBook;
		}
Ejemplo n.º 3
0
        internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
        {
            if (!reader.IsEmptyElement)
            {
                while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
                {
                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        if (reader.Name == "NamedRange")
                        {
                            Range  range = null;
                            string name  = "";

                            foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
                            {
                                if (xa.LocalName == "Name" && xa.HasValue)
                                {
                                    name = xa.Value;
                                }

                                if (xa.LocalName == "RefersTo" && xa.HasValue)
                                {
                                    range = new Range(xa.Value);
                                }
                            }

                            NamedRange nr = new NamedRange(range, name, ws);
                            book.NamedRanges.Add(nr);
                        }
                    }
                }
            }
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Imports a excel xml workbook into a ExcelXmlWorkbook instance
        /// </summary>
        /// <param name="importFile">File to import</param>
        /// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
        public static ExcelXmlWorkbook Import(string importFile)
        {
            if (!File.Exists(importFile))
            {
                return(null);
            }

            Stream stream;

            try
            {
                stream = new FileStream(importFile, FileMode.Open, FileAccess.Read);
            }
            catch (IOException)
            {
                return(null);
            }
            catch (SecurityException)
            {
                return(null);
            }
            catch (UnauthorizedAccessException)
            {
                return(null);
            }

            ExcelXmlWorkbook book = Import(stream);

            stream.Close();
            stream.Dispose();

            return(book);
        }
Ejemplo n.º 5
0
        internal Column(Worksheet parent)
        {
            if (parent == null)
            {
                throw new ArgumentNullException("parent");
            }

            ParentBook = parent.ParentBook;
        }
Ejemplo n.º 6
0
        internal void Import(XmlReader reader)
        {
            foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
            {
                if (xa.LocalName == "Name" && xa.HasValue)
                {
                    Name = xa.Value;
                }

                if (xa.LocalName == "StyleID" && xa.HasValue)
                {
                    Style = ParentBook.GetStyleByID(xa.Value);
                }
            }

            while (reader.Read() && !(reader.Name == "Worksheet" && reader.NodeType == XmlNodeType.EndElement))
            {
                if (reader.NodeType == XmlNodeType.Element)
                {
                    switch (reader.Name)
                    {
                    case "Names":
                    {
                        ExcelXmlWorkbook.ImportNamedRanges(reader, GetParentBook(), this);

                        break;
                    }

                    case "Table":
                    {
                        ImportTable(reader);

                        break;
                    }

                    case "WorksheetOptions":
                    {
                        ImportOptions(reader);

                        break;
                    }
                    }
                }
            }
        }
Ejemplo n.º 7
0
    private void Export_Excel()
    {
        try
        {
            string strStartDate = SLP_MDC_START_DATE.StartDate;
            string strEndDate = SLP_MDC_START_DATE.EndDate;
            string strRoot_S = SLP_ROOT_NO_S.Text;
            string strRoot_E = SLP_ROOT_NO_E.Text;
            string strPMA_S = SLP_PMA_S.Text;
            string strPMA_E = SLP_PMA_E.Text;
            string strCATEGORY_S = SLP_CATEGORY_S.Text;
            string strCATEGORY_E = SLP_CATEGORY_E.Text;

            COMModel.COM14_BCO BCO = new COMModel.COM14_BCO(ConnectionDB);
            // 讀取 Excel 資料    
            ParameterList.Clear();
            ParameterList.Add(strStartDate);
            ParameterList.Add(strEndDate);
            ParameterList.Add(strRoot_S);
            ParameterList.Add(strRoot_E);
            ParameterList.Add(strPMA_S);
            ParameterList.Add(strPMA_E);
            ParameterList.Add(strCATEGORY_S);
            ParameterList.Add(strCATEGORY_E);

            DataTable dt = BCO.QuerySaleRank(ParameterList);
            string strSheetName = "商品銷售排行榜";

            if (dt.Rows.Count == 0)
            {
                ErrorMsgLabel.Text = "查無資料";
                return;
            }

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];
            AddSheetData(ref sheet0, strSheetName, dt);

            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode(strSheetName + ".xls", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();


        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 8
0
    private void Export_Excel(ArrayList ParameterList)
    {
        try
        {

            BCO.VAM19_BCO BCO = new BCO.VAM19_BCO(ConntionDB);
            // 讀取 Excel 資料    

            DataTable dt;
            if (this.rblReportSource.SelectedValue == "1")
            {
                dt = BCO.QueryExportTrace(ParameterList);
            }
            else if (this.rblReportSource.SelectedValue == "2")
            {
                dt = BCO.QueryVamAgmTrace(ParameterList);
            }
            else
            {
                dt = BCO.QueryVamSKUTrace(ParameterList);
            }

            if (dt.Rows.Count == 0)
            {
                ErrorMsgLabel.Text = "查無資料";
                return;
            }
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];

            string s_Name;


            if (this.rblReportSource.SelectedValue == "1")
            {
                s_Name = "補收帳款匯出";
                AddSheetData(ref sheet0, s_Name, dt);
            }
            else if (this.rblReportSource.SelectedValue == "2")
            {
                s_Name = "各項扣款彙總表(補收)";
                AddSheetDataTrace(ref sheet0, s_Name, dt);
            }
            else
            {
                s_Name = "商品交易補收彙總表";
                AddSheetDataTrace_SKU(ref sheet0, s_Name, dt);
            }

           

            //設定輸出檔名
            string s_FileName =
            s_FileName = HttpUtility.UrlEncode("VAM191_" + s_Name + "_" + ParameterList[0].ToString() 
            + "_" + ParameterList[1].ToString() + ".XLS", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 9
0
		internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
		{
			if (!reader.IsEmptyElement)
			{
				while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
				{
					if (reader.NodeType == XmlNodeType.Element)
					{
						if (reader.Name == "NamedRange")
						{
							Range range = null;
							string name = "";

							foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
							{
								if (xa.LocalName == "Name" && xa.HasValue)
									name = xa.Value;

								if (xa.LocalName == "RefersTo" && xa.HasValue)
									range = new Range(xa.Value);
							}

							NamedRange nr = new NamedRange(range, name, ws);
							book.NamedRanges.Add(nr);
						}
					}
				}
			}
		}
Ejemplo n.º 10
0
    private void Export_Excel(DataTable dtTemp)
    {
        ExcelXmlWorkbook book = new ExcelXmlWorkbook();

        Worksheet sheet0 = book[0];
        AddSheetData(ref sheet0, "Sheet1", dtTemp);

        // 設定檔案路徑
        string filePath = Server.MapPath("..\\") + System.Configuration.ConfigurationManager.AppSettings["DownLoadPath"] + "CAA\\";
        Environment.CurrentDirectory = filePath + "\\";
        // 設定 excel 輸出檔名
        string excelFileName = "";
        excelFileName = DateTime.Today.Year.ToString("####") + DateTime.Today.Month.ToString("0#") + DateTime.Today.Day.ToString("0#")  + "_單品補貼報表.xls";
        System.IO.FileStream stream = new FileStream(filePath + "\\" + excelFileName, FileMode.Create);
        book.Export(stream);
        stream.Close();

        // 設定壓縮檔輸出檔名
        string zipFileName = filePath + "\\" + DateTime.Today.Year.ToString("####") + DateTime.Today.Month.ToString("0#") + DateTime.Today.Day.ToString("0#") + "_CAA35.zip";
        ZipFile zipFile = ZipFile.Create(zipFileName);
        // 產生壓縮檔
        zipFile.BeginUpdate();
        zipFile.Add(excelFileName);
        zipFile.CommitUpdate();
        zipFile.Close();

        // 傳到前端進行檔案下載
        Response.ClearContent();
        Response.ClearHeaders();
        Response.AddHeader("content-disposition", "attachment;filename=" + Path.GetFileName(zipFileName));
        Response.ContentType = "application/octet-stream";
        Response.WriteFile(zipFileName);
        Response.Flush();
        Response.Close();

    }
Ejemplo n.º 11
0
    /// <summary>
    /// 頁籤=[報表] 欄位[報表類型]=彙總,按下Button[匯出]鈕
    /// </summary>
    private void but_TAB3_Print_Total()
    {
        try
        {
            #region 檢查條件

            BCO.CAACommon CAAComm = new BCO.CAACommon();

            ArrayList arl_Check_Condition = Check_Condition("TAB 報表");

            #region 如果檢查有誤,則Return

            if (arl_Check_Condition[1].ToString() != string.Empty)
            {
                #region 錯誤訊息

                this.ErrorMsgLabel.Text = arl_Check_Condition[1].ToString();

                #endregion

                #region Focus欄位

                if (arl_Check_Condition[0].ToString() != string.Empty)
                {
                    string s_ScriptManager_Script = CAAComm.ToMakeUp_SetFocus_Script(arl_Check_Condition[0].ToString(), true);
                    ScriptManager.RegisterStartupScript(this.up_Msg, typeof(UpdatePanel), "CAA181", s_ScriptManager_Script, true);
                }

                #endregion

                return;
            }

            #endregion

            #endregion

            #region 傳入參數

            ParameterList.Clear();
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_B.Text, "string", false));//[結帳年月]起 20110520修改為起迄
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_E.Text, "string", false));//[結帳年月]迄
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_B.Text, "string", false));//[結帳年月-財會]起 20110520修改為起迄
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_E.Text, "string", false));//[結帳年月-財會]迄           
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_B.Text, "string", false));//[利潤中心]起
            ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_E.Text, "string", false));//[利潤中心]迄
            ParameterList.Add(CAAComm.GetValueSetParameter(Session["UID"].ToString(), "string", false));//V_LOG_UID

            #endregion

            #region 連接資料庫

            DataSet ds_Return = new DataSet();
            BCO.MaintainDiscRecord bco = new BCO.MaintainDiscRecord(ConntionDB);
            ds_Return = bco.QUERY_CAA18_REPORT_SUMMARY(ParameterList);

            #endregion

            #region 檢查回傳資料

            if (ds_Return.Tables["SUMMARY"].Rows.Count == 0)
            {
                this.ErrorMsgLabel.Text = "查無資料";
                return;
            }

            #endregion

            #region 組合匯出的Excel

            #region 設定變數

            ExcelXmlWorkbook exl_WorkSheet = new ExcelXmlWorkbook();
            Worksheet sheet = exl_WorkSheet[0];
            sheet.Name = "折讓入帳彙總表";

            #region 設定長和寬

            int i_Vertical = 0;//直的有幾個欄位(依據資料庫[利潤中心])
            int i_Horizontal = 0;//橫的有幾個欄位(依據資料庫[型式])

            int i_Vertical_Fix = 3;//直的固定欄位[型式、稅別、合計]
            int i_Horizontal_Fix = 4;//橫的固定欄位[標題抬頭、利潤中心抬頭、合計上方要空一行、合計]

            i_Vertical = ds_Return.Tables["PROFIT"].Rows.Count + i_Vertical_Fix;
            i_Horizontal = (ds_Return.Tables["DISC_TYPE"].Rows.Count * 3) + i_Horizontal_Fix;

            #endregion

            #endregion

            #region 設定格式

            #region 設定欄位寬度

            for (int i_Style = 0; i_Style < i_Vertical; i_Style++)
            { sheet.Columns(i_Style).Width = 100; }

            #endregion

            #region 設定合併儲存格

            Range rang_1 = new Range(sheet[0, 0], sheet[i_Vertical - 1, 0]);
            rang_1.Merge();

            #endregion

            #region 設定Style

            XmlStyle style_1 = new XmlStyle();
            style_1.Alignment.Vertical = VerticalAlignment.Center;
            style_1.Border.LineStyle = Borderline.Continuous;
            style_1.Border.Color = System.Drawing.Color.Black;
            style_1.Border.Sides = BorderSides.All;
            style_1.Border.Weight = 1;
            style_1.Font.Size = 10;
            style_1.Font.Name = "新細明體";

            XmlStyle style_2 = new XmlStyle();
            style_2.Alignment.Vertical = VerticalAlignment.Center;
            style_2.Alignment.Horizontal = HorizontalAlignment.Center;
            style_2.Border.LineStyle = Borderline.Continuous;
            style_2.Border.Color = System.Drawing.Color.Black;
            style_2.Border.Sides = BorderSides.All;
            style_2.Border.Weight = 1;
            style_2.Font.Size = 10;
            style_2.Font.Name = "新細明體";

            for (int x = 0; x < i_Vertical; x++)
            {
                for (int y = 0; y < i_Horizontal; y++)
                {
                    if (y != 0)
                    { sheet[x, y].Style = style_1; }
                }
            }

            #endregion

            #endregion

            #region 組合匯出資料

            for (int i = 0; i < i_Horizontal; i++)
            {
                switch (i)
                {
                    #region 橫的第一行

                    case 0:
                        sheet[0, 0].Value = this.slp_3_CLOSE_MONTH_ACCT_B.Text == string.Empty ? "折讓入帳彙總表" : this.slp_3_CLOSE_MONTH_ACCT_B.Text + "-" + this.slp_3_CLOSE_MONTH_ACCT_E.Text + "月折讓入帳彙總表";
                        sheet[0, 0].Style = style_2;
                        break;

                    #endregion

                    #region 橫的第二行

                    case 1:
                        sheet[0, 1].Value = string.Empty;
                        sheet[1, 1].Value = string.Empty;
                        for (int i_case_1 = 0; i_case_1 < ds_Return.Tables["PROFIT"].Rows.Count; i_case_1++)
                        { sheet[i_case_1 + 2, 1].Value = ds_Return.Tables["PROFIT"].Rows[i_case_1]["PROFIT_NAME"].ToString(); }
                        sheet[i_Vertical - 1, 1].Value = "合計";
                        break;

                    #endregion

                    #region 其它行

                    default:
                        switch (i_Horizontal - i)
                        {
                            #region 橫的倒數第二行(合計上面一行要空白)

                            case 2:
                                sheet[0, i].Value = string.Empty;
                                break;

                            #endregion

                            #region 橫的最後一行(合計那一行)

                            case 1:
                                sheet[0, i].Value = "合計";
                                for (int i_case_last = 0; i_case_last < ds_Return.Tables["PROFIT"].Rows.Count; i_case_last++)
                                { sheet[i_case_last + 2, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_case_last + 2, 0], sheet[i_case_last + 2, i_Horizontal - 2])); }
                                sheet[i_Vertical - 1, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_Vertical - 1, 0], sheet[i_Vertical - 1, i_Horizontal - 2]));
                                break;

                            #endregion

                            #region 橫的其他行

                            default:

                                #region 設定變數

                                int i_Get_Disc_Type = (i - 2) / 3;
                                int i_Get_Tax_Type = (i - 2) % 3;
                                string s_FilterExpression = string.Empty;//篩選條件
                                string s_DISC_SOURCE = string.Empty;//來源(1=系統、2=人工)
                                string s_DISC_TYPE = string.Empty;//型式
                                string s_TAX_TYPE = string.Empty;//稅別
                                string s_PROFIT_NO = string.Empty;//利潤中心

                                #endregion

                                #region 第1欄(型式)

                                sheet[0, i].Value = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString();

                                #endregion

                                #region 第2欄(稅別)

                                switch (i_Get_Tax_Type)
                                {
                                    case 0:
                                        sheet[1, i].Value = "應稅";
                                        break;
                                    case 1:
                                        sheet[1, i].Value = "稅額";
                                        break;
                                    case 2:
                                        sheet[1, i].Value = "免稅";
                                        break;
                                }

                                #endregion

                                #region 其他資料欄

                                for (int i_case_default = 0; i_case_default < ds_Return.Tables["PROFIT"].Rows.Count; i_case_default++)
                                {
                                    #region 設定[來源][型式][稅別][利潤中心]參數

                                    //[來源][型式]
                                    switch (i_Get_Disc_Type)
                                    {
                                        case 0://第1個一定是系統-一般
                                            s_DISC_SOURCE = "1";//來源(1=系統、2=人工)
                                            s_DISC_TYPE = "1";//型式=1=一般
                                            break;
                                        case 1://第2個一定是人工-一般
                                            s_DISC_SOURCE = "2";//來源(1=系統、2=人工)
                                            s_DISC_TYPE = "1";//型式=1=一般
                                            break;
                                        default:
                                            s_DISC_SOURCE = "2";//來源(1=系統、2=人工)
                                            s_DISC_TYPE = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString();
                                            break;
                                    }

                                    //[稅別]
                                    switch (i_Get_Tax_Type)
                                    {
                                        case 0:
                                            s_TAX_TYPE = "0";//稅別=0=應稅,稅別=1=免稅
                                            break;
                                        case 1:
                                            s_TAX_TYPE = "0";//稅別=0=應稅,稅別=1=免稅
                                            break;
                                        case 2:
                                            s_TAX_TYPE = "1";//稅別=0=應稅,稅別=1=免稅
                                            break;
                                    }

                                    //[利潤中心]
                                    s_PROFIT_NO = ds_Return.Tables["PROFIT"].Rows[i_case_default]["CODE"].ToString();

                                    #endregion

                                    #region 組合查詢條件,查詢資料

                                    s_FilterExpression = "DISC_SOURCE='" + s_DISC_SOURCE + "' and DISC_TYPE='" + s_DISC_TYPE + "' and TAX_TYPE='" + s_TAX_TYPE + "' and PROFIT_NO='" + s_PROFIT_NO + "'";
                                    DataRow[] dr = ds_Return.Tables["SUMMARY"].Select(s_FilterExpression);

                                    #endregion

                                    #region 將資料寫入欄位

                                    switch (dr.Length)
                                    {
                                        case 1:
                                            if (i_Get_Tax_Type != 1)
                                            { sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_UAMT"].ToString()); }
                                            else
                                            { sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_TAX"].ToString()); }
                                            break;
                                        default:
                                            sheet[i_case_default + 2, i].Value = 0;
                                            break;
                                    }

                                    #endregion
                                }

                                #endregion

                                #region 合計欄(Excel右邊的[合計]欄)

                                sheet[i_Vertical - 1, i].Value = FormulaHelper.Formula("SUM", new Range(sheet[0, i], sheet[i_Vertical - 2, i]));

                                #endregion

                                break;

                            #endregion
                        }
                        break;

                    #endregion
                }
            }

            #endregion

            #region 匯出Excel

            string s_FileName = HttpUtility.UrlEncode("折讓入帳彙總表.XLS", System.Text.Encoding.UTF8);//設定輸出檔名
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            exl_WorkSheet.Export(stream);
            if (stream == null ||
                stream.Length == 0)
            { return; }
            stream.Flush();
            stream.Position = 0;
            System.IO.StreamReader sr = new System.IO.StreamReader(stream);
            string producedExcel = sr.ReadToEnd();
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();

            #endregion

            #endregion
        }
        catch (Exception ex)
        {
            WaringLogProcess(ex.Message);
            this.ErrorMsgLabel.Text = ex.Message;
        }
    }
        /// <summary>
        /// Converts a dataset to a work book
        /// </summary>
        /// <param name="source">The source dataset to convert to a work book</param>
        /// <returns>Returns the <see cref="Yogesh.ExcelXml.ExcelXmlWorkbook"/>
        /// for the dataset.</returns>
        /// <remarks>All the tables are converted into sheets with sheet names as table + table number,
        /// eg. "Table0" "Table1" etc. Supported types which can be successfully
        /// converted to cells are the same as described in <see cref="Yogesh.ExcelXml.Cell"/>
        /// except <see cref="Yogesh.ExcelXml.Cell"/> and
        /// <see cref="Yogesh.ExcelXml.Formula"/></remarks>
        public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
        {
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
            {
                Worksheet sheet = book[tableNumber];
                sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);

                int columnCount = source.Tables[tableNumber].Columns.Count;
                for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
                {
                    sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;

                    sheet[columnNumber, 0].Font.Bold = true;
                }

                int rowNumber = 0;
                foreach (DataRow row in source.Tables[tableNumber].Rows)
                {
                    rowNumber++;

                    for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
                    {
                        string rowType = row[columnNumber].GetType().FullName;

                        switch (rowType)
                        {
                        case "System.DateTime":
                        {
                            sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
                            break;
                        }

                        case "System.Boolean":
                        {
                            sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
                            break;
                        }

                        case "System.SByte":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                        case "System.UInt16":
                        case "System.UInt32":
                        case "System.UInt64":
                        case "System.Single":
                        case "System.Double":
                        case "System.Decimal":
                        {
                            sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber],
                                                                                     CultureInfo.InvariantCulture);
                            break;
                        }

                        case "System.DBNull":
                            break;

                        // case "System.String": <-- default covers this...
                        default:
                        {
                            sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
                            break;
                        }
                        }
                    }
                }
            }

            return(book);
        }
Ejemplo n.º 13
0
		/// <summary>
		/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
		/// </summary>
		/// <param name="stream">Stream to import</param>
		/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
		public static ExcelXmlWorkbook Import(Stream stream)
		{
			XmlReaderSettings settings = new XmlReaderSettings();
			settings.CloseInput = false;
			settings.IgnoreComments = true;
			settings.IgnoreProcessingInstructions = true;
			settings.IgnoreWhitespace = true;

			if (!stream.CanRead)
				return null;

			XmlReader reader = XmlReader.Create(stream, settings);

			ExcelXmlWorkbook book = new ExcelXmlWorkbook();
			book.Styles.Clear();

			int workSheet = 0;

			while (reader.Read())
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						// Document Properties
						case "DocumentProperties":
							{
								if (!reader.IsEmptyElement)
									book.Properties.Import(reader);
								break;
							}

						case "Styles":
							{
								if (!reader.IsEmptyElement)
									book.ImportStyles(reader);

								break;
							}

						case "Names":
							{
								ImportNamedRanges(reader, book, null);

								break;
							}

						case "Worksheet":
							{
								if (!reader.IsEmptyElement)
									book[workSheet++].Import(reader);

								break;
							}
					}
				}
			}

			book.ResolveNamedRangeReferences();

			book.ResolveCellReferences();

			reader.Close();

			stream.Close();
			stream.Dispose();

			return book;
		}
Ejemplo n.º 14
0
    private void Export_Excel(DataTable dt1, DataTable dt2,DataTable dt3)
    {
        try
        {
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];
            Worksheet sheet1 = book[1];
            Worksheet sheet2 = book[2];
            AddSheetData1(ref sheet0, "商品期別檔", dt1);
            AddSheetData2(ref sheet1, "期別帳務檔", dt2);
            AddSheetData3(ref sheet2, "期別帳務店群門市", dt3);
            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode("商品期別異動表_" + System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + ".XLS", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 15
0
    private void Export_Excel()
    {
        ErrorMsgLabel.Text = "";
        //try
        //{
        ExcelXmlWorkbook book = new ExcelXmlWorkbook();

        Worksheet sheet0 = book[0];//頁籤1
        Worksheet sheet1 = book[1];//頁籤2
        DataTable dtTab = AddSheetData0(ref sheet0, "Table");
        if (dtTab.Rows.Count > 0)
        {
            AddSheetData1(ref sheet1, "Columns", dtTab);

            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode("系統檔案使用清單_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string strExcelData = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(strExcelData);
            Response.Flush();
            Response.End();
        }
        else
        {
            ErrorMsgLabel.Text = "查無資料";
        }

        //}
        //catch (Exception ex)
        //{
        //    this.ErrorMsgLabel.Text = ex.ToString();
        //}
        //finally { }
    }
Ejemplo n.º 16
0
    private void Export_Excel()
    {
        try
        {

            ImportStoreOrderItem BCO = new ImportStoreOrderItem(ConntionDB);
            // 讀取 Excel 資料    
            ParameterList.Clear();
            ParameterList.Add(Session["UID"].ToString());
            DataTable dt = BCO.QueryTemp2(ParameterList);

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];//即有品項       
            Worksheet sheet1 = book[1];//新增品項       
            Worksheet sheet2 = book[2];//異動品項       
            AddSheetData(ref sheet0, "即有品項", dt.Select("CHECK_FLAG='3'"));
            AddSheetData(ref sheet1, "新增品項", dt.Select("CHECK_FLAG='1'"));
            AddSheetData(ref sheet2, "異動品項", dt.Select("CHECK_FLAG='2'"));
            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode(txtExportFileName.Text.Trim(), System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();


        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 17
0
    private void Export_Excel(DataTable dtTemp)
    {
        ExcelXmlWorkbook book = new ExcelXmlWorkbook();

        Worksheet sheet0 = book[0];
        Worksheet sheet1 = book[1];
        Worksheet sheet2 = book[2];
        Worksheet sheet3 = book[3];
        AddSheetData(ref sheet0, "進貨", DT_Filter(dtTemp, "RTYPE='進貨'"));
        AddSheetData(ref sheet1, "退貨", DT_Filter(dtTemp, "RTYPE='退貨'"));
        AddSheetData(ref sheet2, "補帳", DT_Filter(dtTemp, "RTYPE='補帳'"));
        AddSheetData(ref sheet3, "調撥", DT_Filter(dtTemp, "RTYPE='調撥'"));
        //設定輸出檔名
        string s_FileName = "";
        s_FileName = HttpUtility.UrlEncode("銷售明細報表_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xls", System.Text.Encoding.UTF8);

        //File download
        Response.Charset = "big5";
        Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
        Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        book.Export(stream);
        string producedExcel = GetStringFromMemoryStream(stream);
        stream.Close();
        Response.Write(producedExcel);
        Response.Flush();
        Response.End();
    }
Ejemplo n.º 18
0
        private static ExcelXmlWorkbook OrdersArrayToWorkbook(IEnumerable<Order> orders)
        {
            var book = new ExcelXmlWorkbook { Properties = { Author = "AdvantShop.Net", LastAuthor = "AdvantShop.Net" }, DefaultStyle = new XmlStyle { Font = { Name = "Calibri", Size = 11 } } };

            Worksheet sheet = book[0];

            sheet.Name = "Orders";

            sheet.PrintOptions.Orientation = PageOrientation.Landscape;
            sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

            //XLS columns definition

            sheet.Columns(0).Width = 50;
            sheet.Columns(1).Width = 80;
            sheet.Columns(2).Width = 100;
            sheet.Columns(3).Width = 130;
            sheet.Columns(4).Width = 100;
            sheet.Columns(5).Width = 100;
            sheet.Columns(6).Width = 150;
            sheet.Columns(7).Width = 80;
            sheet.Columns(8).Width = 80;
            sheet.Columns(9).Width = 80;
            sheet.Columns(10).Width = 80;
            sheet.Columns(11).Width = 300;
            sheet.Columns(12).Width = 300;
            sheet.Columns(13).Width = 300;
            sheet.Columns(14).Width = 300;
            sheet.Columns(15).Width = 300;

            sheet[0, 0].Value = "OrderID";
            sheet[1, 0].Value = "Status";
            sheet[2, 0].Value = "OrderDate";
            sheet[3, 0].Value = "FIO";
            sheet[4, 0].Value = "Customer Email";
            sheet[5, 0].Value = "Customer Phone";
            sheet[6, 0].Value = "OrderedItems";
            sheet[7, 0].Value = "Total";
            sheet[8, 0].Value = "Tax";
            sheet[9, 0].Value = "Cost";
            sheet[10, 0].Value = "Profit";
            sheet[11, 0].Value = "Payment";
            sheet[12, 0].Value = "Shipping";
            sheet[13, 0].Value = "Shipping Address";
            sheet[14, 0].Value = "Customer Comment";
            sheet[15, 0].Value = "Admin Comment";

            var i = 1;
            foreach (Order order in orders)
            {
                if (!CommonStatistic.IsRun) return book;

                CommonStatistic.RowPosition++;
                //Order to XLS row

                sheet[0, i].Value = order.OrderID;
                sheet[1, i].Value = order.OrderStatus != null ? order.OrderStatus.StatusName : "Неизвестный";
                sheet[2, i].Value = order.OrderDate;
                if (order.OrderCustomer != null)
                {
                    sheet[3, i].Value = order.OrderCustomer.LastName + " " + order.OrderCustomer.FirstName;
                    sheet[4, i].Value = order.OrderCustomer.Email ?? string.Empty;
                    sheet[5, i].Value = order.OrderCustomer.MobilePhone ?? string.Empty;
                }
                else
                {
                    sheet[3, i].Value = "Неизвестный";
                    sheet[4, i].Value = string.Empty;
                    sheet[5, i].Value = string.Empty;
                }

                if (order.OrderCurrency != null)
                {
                    sheet[6, i].Value = RenderOrderedItems(order.OrderItems) ?? string.Empty;
                    sheet[7, i].Value = CatalogService.GetStringPrice(order.Sum, order.OrderCurrency);
                    sheet[8, i].Value = CatalogService.GetStringPrice(order.TaxCost, order.OrderCurrency);
                    float totalCost = order.OrderItems.Sum(oi => oi.SupplyPrice * oi.Amount);
                    sheet[9, i].Value = CatalogService.GetStringPrice(totalCost, order.OrderCurrency);
                    sheet[10, i].Value = CatalogService.GetStringPrice(order.Sum - order.ShippingCost - order.TaxCost - totalCost, order.OrderCurrency);
                    sheet[11, i].Value = order.PaymentMethodName;
                    sheet[12, i].Value = order.ArchivedShippingName + " - " + CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency);
                    sheet[13, i].Value = order.ShippingContact != null ? order.ShippingContact.Country + ", " + order.ShippingContact.City + ", " + order.ShippingContact.Address : string.Empty;
                    sheet[14, i].Value = order.CustomerComment ?? string.Empty;
                    sheet[15, i].Value = order.AdminOrderComment ?? string.Empty;
                }

                i++;
            }

            return book;
        }
Ejemplo n.º 19
0
        private void buttonSelectFileDirectory_Click(object sender, EventArgs e)
        {
            try
            {
                FolderBrowserDialog folderDialog = new FolderBrowserDialog();
                folderDialog.Description = "Select Destination Folder for Inventory file";
                folderDialog.ShowDialog();
                workBook = new ExcelXmlWorkbook();
                workBook.Properties.Author = Environment.UserName;
                writer = File.OpenWrite(folderDialog.SelectedPath + "\\" + textBoxFileName.Text + ".xml");
                sheetCount = 0;

                //make this thing and all associated stuff go away
                buttonSelectFileDirectory.Enabled = false;
                buttonSelectFileDirectory.Visible = false;
                textBoxFileName.Enabled = false;
                textBoxFileName.Visible = false;

                //next phase appear
                buttonSelectInventoryDirectory.Enabled = true;
                buttonSelectInventoryDirectory.Visible = true;

            }
            catch(ArgumentNullException ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.ParamName);
            }
            catch(IOException ex)
            {
                System.Windows.Forms.MessageBox.Show("Could not make file-" + ex.Message);
            }
            catch(Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Asking for directory failed" + ex.Message);
            }
        }
Ejemplo n.º 20
0
    private void Export_Excel()
    {
        try
        {
            string strStartDate = SLP_MDC_START_DATE.StartDate;
            string strEndDate = SLP_MDC_START_DATE.EndDate;
            string strRoot_S = SLP_ROOT_NO_S.Text;
            string strRoot_E = SLP_ROOT_NO_E.Text;

            COMModel.COM10_BCO BCO = new COMModel.COM10_BCO(ConnectionDB);
            // 讀取 Excel 資料    
            ParameterList.Clear();
            ParameterList.Add(strStartDate);
            ParameterList.Add(strEndDate);
            ParameterList.Add(strRoot_S);
            ParameterList.Add(strRoot_E);

            //1:各所縣市營業額查詢
            //2:營業所及通路門市營業員業績查詢
            //3:各所通路單店商品業績查詢
            DataTable dt = new DataTable();
            string strSheetName = string.Empty;
            switch (ddlType.SelectedValue)
            {
                case "1":
                    dt = BCO.QueryByZOArea(ParameterList);
                    strSheetName = "各所縣市營業額查詢";
                    break;
                case "2":
                    dt = BCO.QueryByZOChanStore(ParameterList);
                    strSheetName = "COM10_業績查詢";
                    break;
                case "3":
                    dt = BCO.QueryBookByZOChanStore(ParameterList);
                    strSheetName = "各所通路單店商品業績查詢";
                    break;
            }

            if (dt.Rows.Count == 0)
            {
                ErrorMsgLabel.Text = "查無資料";
                return;
            }

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];

            switch (ddlType.SelectedValue)
            {
                case "1":
                    AddSheetData1(ref sheet0, strSheetName, dt);
                    break;
                case "2":
                    AddSheetData2(ref sheet0, strSheetName, dt);
                    break;
                case "3":
                    AddSheetData3(ref sheet0, strSheetName, dt);
                    break;
            }

            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode(strSheetName + ".xls", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();


        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 21
0
    private void Export_Excel(DataTable dt)
    {
        ErrorMsgLabel.Text = "";

        ExcelXmlWorkbook book = new ExcelXmlWorkbook();

        Worksheet sheet0 = book[0];//頁籤1        
        AddSheetData0(ref sheet0, "Sheet1", dt);

        //設定輸出檔名
        string s_FileName = "";
        if (rblREPORT.SelectedValue == "1")
            s_FileName = HttpUtility.UrlEncode("儲區庫存匯總表_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", System.Text.Encoding.UTF8);
        else
            s_FileName = HttpUtility.UrlEncode("儲位庫存明細表_" + DateTime.Now.ToString("yyyyMMdd") + ".xls", System.Text.Encoding.UTF8);

        //File download
        Response.Charset = "big5";
        Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
        Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        book.Export(stream);
        string strExcelData = GetStringFromMemoryStream(stream);
        stream.Close();
        Response.Write(strExcelData);
        Response.Flush();
        Response.End();
    }
Ejemplo n.º 22
0
    protected void Btn_Export_Click(object sender, EventArgs e)
    {
        try
        {
            #region 匯出條件變數

            string strItem = this.SLP_SKU1.Text;
            string strPeriod = this.SLP_ItemPeriod1.Text;
            string strSLP_StoreChain_S = SLP_StoreChain_S.Text;
            string strSLP_StoreChain_E = SLP_StoreChain_E.Text;
            string strSLP_CodeFile97_S = SLP_CodeFile97_S.Text;
            string strSLP_CodeFile97_E = SLP_CodeFile97_E.Text;
            string strSLP_CodeFile91_S = SLP_CodeFile91_S.Text;
            string strSLP_CodeFile91_E = SLP_CodeFile91_E.Text;
            string strSLP_CodeFile18_S = SLP_CodeFile18_S.Text;
            string strSLP_CodeFile18_E = SLP_CodeFile18_E.Text;
            string strSLP_CodeFile3_S = SLP_CodeFile3_S.Text;
            string strSLP_CodeFile3_E = SLP_CodeFile3_E.Text;

            #endregion

            #region check是否存在OnPack贈品主檔資料

            DataTable dtGift = new DataTable();

            ParameterList.Clear();
            ParameterList.Add(GetValueSetParameter(strItem, "string"));
            ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
            ParameterList.Add(GetValueSetParameter("-1", "int"));

            BCO.MaintainGift bco = new MaintainGift(ConnectionDB);

            dtGift = bco.QueryGift(ParameterList);

            if (dtGift == null || (dtGift != null && dtGift.Rows.Count <= 0))
            {
                ErrorMsgLabel.Text = "無贈品主檔資料";
                return;
            }

            #endregion

            #region check是否存在通路門市贈品資料

            DataTable dtStoreGift = new DataTable();

            ParameterList.Clear();
            ParameterList.Add(GetValueSetParameter(strItem, "string"));
            ParameterList.Add(GetValueSetParameter(strPeriod, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_S, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_StoreChain_E, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_S, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile97_E, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_S, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile91_E, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_S, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile18_E, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_S, "string"));
            ParameterList.Add(GetValueSetParameter(strSLP_CodeFile3_E, "string"));
            ParameterList.Add(SLP_SHELVE_CM3.Text); //專屬架

            BCO.MaintainStoreGift bcoStoreGift = new MaintainStoreGift(ConnectionDB);

            dtStoreGift = bcoStoreGift.ExportStoreGift(ParameterList);

            if (dtStoreGift == null || (dtStoreGift != null && dtStoreGift.Rows.Count <= 0))
            {
                ErrorMsgLabel.Text = "無通路門市贈品資料";
                return;
            }

            #endregion

            #region 匯出通路門市贈品資料

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet = book[0];
            Worksheet sheet1 = book[1];

            //設定Worksheet各column 寬度
            sheet.Columns(3).Width = 70;
            sheet.Columns(5).Width = 110;
            sheet.Columns(6).Width = 45;
            sheet.Columns(7).Width = 60;
            sheet.Columns(8).Width = 120;
            sheet1.Columns(0).Width = 30;
            sheet1.Columns(2).Width = 110;
            sheet1.Columns(3).Width = 45;
            sheet1.Columns(4).Width = 60;
            sheet1.Columns(5).Width = 120;
            //sheet1.Columns(6).Hidden = true; //品號品名怕被修改到,所以產生兩個隱藏欄位
            //sheet1.Columns(7).Hidden = true;


            int iRow = 0;
            //贈品主檔
            sheet1.Name = "贈品主檔";
            sheet1.PrintOptions.Orientation = PageOrientation.Landscape;
            sheet1.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
            iRow = 0;
            sheet1[0, iRow].Value = "序號";
            sheet1[1, iRow].Value = "商品品號";
            sheet1[2, iRow].Value = "商品名稱";
            sheet1[3, iRow].Value = "期別";
            sheet1[4, iRow].Value = "贈品名稱";
            sheet1[5, iRow].Value = "虛擬品號";
            //sheet1[6, iRow].Value = "贈品名稱";
            //sheet1[7, iRow].Value = "虛擬品號";
            iRow++;

            DataRow[] drs = dtGift.Select("1=1", "VIRTUAL_NAME");
            foreach (DataRow dr in drs)
            {
                if (dr["id"].ToString() != "0")
                {
                    sheet1[0, iRow].Value = iRow.ToString();
                    sheet1[1, iRow].Value = dr["ITEM"].ToString();
                    sheet1[2, iRow].Value = dr["ITEM_NAME"].ToString();
                    sheet1[3, iRow].Value = dr["PERIOD"].ToString();
                    sheet1[4, iRow].Value = dr["VIRTUAL_NAME"].ToString();
                    sheet1[5, iRow].Value = dr["VIRTUAL_CODE"].ToString();
                    //sheet1[6, iRow].Value = dr["VIRTUAL_NAME"].ToString();
                    //sheet1[7, iRow].Value = dr["VIRTUAL_CODE"].ToString();
                    iRow++;
                }
            }

            //多通路門市贈品設定
            iRow = 0;
            sheet.Name = "多通路門市贈品設定";
            sheet.PrintOptions.Orientation = PageOrientation.Landscape;
            sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

            sheet[0, iRow].Value = "通路代號";
            sheet[1, iRow].Value = "通路名稱";
            sheet[2, iRow].Value = "門市代號";
            sheet[3, iRow].Value = "門市名稱";
            sheet[4, iRow].Value = "商品品號";
            sheet[5, iRow].Value = "商品名稱";
            sheet[6, iRow].Value = "期別";
            sheet[7, iRow].Value = "虛擬品號";
            sheet[8, iRow].Value = "贈品名稱";

            iRow++;
            foreach (DataRow dr in dtStoreGift.Rows)
            {
                sheet[0, iRow].Value = dr["CHAN_NO"].ToString();
                sheet[1, iRow].Value = dr["CHAN_NAME"].ToString();
                sheet[2, iRow].Value = dr["STORE"].ToString();
                sheet[3, iRow].Value = dr["STORE_NAME"].ToString();
                sheet[4, iRow].Value = dr["ITEM"].ToString();
                sheet[5, iRow].Value = dr["ITEM_NAME"].ToString();
                sheet[6, iRow].Value = dr["PERIOD"].ToString();
                Formula f = new Formula().Add("VLOOKUP").StartGroup();
                f.Add(new Range(sheet[8, iRow])).Operator(',');
                //f.Add(new Range(sheet1[5, 1], sheet1[5, dtGift.Rows.Count])).Operator(',');
                //f.Add(new Range(sheet1[4, 1], sheet1[4, dtGift.Rows.Count])).EndGroup();
                f.Add(new Range(sheet1[5, 1], sheet1[4, dtGift.Rows.Count])).Operator(',');
                f.Add("2").Operator(',');
                f.Add("false").EndGroup();
                sheet[7, iRow].Value = f;
                //sheet[7, x].Value = dr["VIRTUAL_CODE"].ToString();
                sheet[8, iRow].Value = dr["VIRTUAL_NAME"].ToString();
                iRow++;
            }

            //設定輸出檔名
            string s_FileName = "";
            s_FileName = HttpUtility.UrlEncode(string.Format("MKT14_通路贈品門市設定_{0}_{1}.xls", strItem, strPeriod), System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();

            #endregion
        }
        catch (Exception ex)
        { this.ErrorMsgLabel.Text = ex.ToString(); }
        finally { }

    }
Ejemplo n.º 23
0
    private void ExportUnusual_Excel()
    {
        try
        {
            ParameterList.Clear();
            ParameterList.Add(Session["UID"].ToString());
            ParameterList.Add("2");
                                                                        
            BCO.ProcessVAMMonthClose bco = new BCO.ProcessVAMMonthClose(ConntionDB);
            DataTable dt_Return = bco.QueryTmp(ParameterList);
            if (dt_Return.Rows.Count == 0)
            {
                this.ErrorMsgLabel.Text = "查無資料";
                return;
            }
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];

            AddSheetData(ref sheet0, "Sheet1", dt_Return);

            //設定輸出檔名
            string s_FileName =
            s_FileName = HttpUtility.UrlEncode("VAM03_供應商合約補收異常報表.XLS", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 24
0
        public void GenerateOutput_Backup(string outputRootDir)
        {
            #region Input validation
            if (string.IsNullOrWhiteSpace(outputRootDir))
                throw new ArgumentNullException(nameof(outputRootDir));

            if (!Directory.Exists(outputRootDir))
                throw new ArgumentException($"Unable to find directory {outputRootDir}.");
            #endregion

            #region Create output DIR and file
            OutputDir = Path.Combine(outputRootDir, Ids.EXCEL_DIR);
            if (!Directory.Exists(Directory.CreateDirectory(OutputDir).FullName))
                throw new OutputComponentException($"Unable to create output directory {OutputDir}.");

            OutputFile = Path.Combine(outputRootDir, Ids.EXCEL_DIR, Ids.EXCEL_FILE);
            //			File.Create(OutputFile);
            //			if (!File.Exists(OutputFile))
            //				throw new OutputComponentException($"Unable to create output file {OutputFile}.");
            #endregion

              #region Define row 'n' columns values...
            int columns = 13;

              int firstHeaderRow  = 0;
              int secondHeaderRow = firstHeaderRow + 1;
              int timestampRow    = secondHeaderRow + 1;
              int tableHeaderRow  = 0;
              int row             = tableHeaderRow + 1;
              int freezeRow       = tableHeaderRow + 1;
              #endregion

              #region Create the Excel xml workbook...
              // Create a instance...
              ExcelXmlWorkbook book = new ExcelXmlWorkbook();

              // Many such properties exist. Details can be found in the documentation
              // The author of the document
              book.Properties.Author = "Code Analyzer";

              // This returns the first worksheet.
              // Note that we have not declared a instance of a new worksheet
              // All the dirty work is done by the library.
              Worksheet dashboardSheet = book[0];
              Worksheet matchesSheet   = book[1];

            // Name is the name of the sheet. If not set, the default name
            // style is "sheet" + sheet number, like sheet1, sheet2
            dashboardSheet.Name = "Dashboard";
              matchesSheet.Name   = "Matches";

            // More on this in documentation
            matchesSheet.FreezeTopRows = freezeRow;

              // and this too...
              dashboardSheet.PrintOptions.Orientation = PageOrientation.Landscape;
            dashboardSheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
              #endregion

              #region Creating the header...
              // Text for the header...
              dashboardSheet[0, firstHeaderRow].Value  = "Octopus | Code Analyzer";
            dashboardSheet[0, secondHeaderRow].Value = "The Octopus Code Analyzer - the simple way to keep track of your code base";

              // Setting the background color for the header...
              new Range(dashboardSheet[0, firstHeaderRow],  dashboardSheet[30, firstHeaderRow]).Interior.Color    = Color.FromArgb(22, 54, 92);
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Interior.Color   = Color.FromArgb(22, 54, 92);
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Sides     = BorderSides.Bottom;
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.LineStyle = Borderline.Double;
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Color     = Color.FromArgb(255, 255, 255);
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[30, secondHeaderRow]).Border.Weight    = 1;

              // Setting the foreground color for the header text...
              new Range(dashboardSheet[0, firstHeaderRow],  dashboardSheet[8, firstHeaderRow]).Font.Color  = Color.FromArgb(255, 255, 255);
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Color = Color.FromArgb(255, 255, 255);

              // Setting the font for the header text...
              new Range(dashboardSheet[0, firstHeaderRow],  dashboardSheet[8, firstHeaderRow]).Font.Size  = 18;
              new Range(dashboardSheet[0, firstHeaderRow],  dashboardSheet[8, firstHeaderRow]).Font.Bold  = true;
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Size = 8;
              new Range(dashboardSheet[0, secondHeaderRow], dashboardSheet[8, secondHeaderRow]).Font.Bold = true;

              dashboardSheet[0, timestampRow].Value     = "Exported on: " + DateTime.Now.ToShortDateString();
              dashboardSheet[0, timestampRow].Font.Bold = true;
            dashboardSheet[0, timestampRow].Font.Size = 8;
              #endregion

              #region Create the table header for all the data...
              // Setting the width of the columns...
              matchesSheet.Columns(0).Width  = 100;
              matchesSheet.Columns(1).Width  = 100;
              matchesSheet.Columns(2).Width  = 100;
              matchesSheet.Columns(3).Width  = 100;
              matchesSheet.Columns(4).Width  = 100;
            matchesSheet.Columns(5).Width  = 100;
            matchesSheet.Columns(6).Width  = 100;
            matchesSheet.Columns(7).Width  = 100;
            matchesSheet.Columns(8).Width  = 100;
            matchesSheet.Columns(9).Width  = 100;
            matchesSheet.Columns(10).Width = 100;
            matchesSheet.Columns(11).Width = 100;
            matchesSheet.Columns(12).Width = 100;
            matchesSheet.Columns(13).Width = 900;

              // Inserting headers and setting them to bold...
              matchesSheet[0,  tableHeaderRow].Value = "BatchId Id";
              matchesSheet[1,  tableHeaderRow].Value = "Timestamp";
              matchesSheet[2,  tableHeaderRow].Value = "Match Id";
              matchesSheet[3,  tableHeaderRow].Value = "Severity";
              matchesSheet[4,  tableHeaderRow].Value = "Project";
              matchesSheet[5,  tableHeaderRow].Value = "Language";
              matchesSheet[6,  tableHeaderRow].Value = "Category";
              matchesSheet[7,  tableHeaderRow].Value = "Category description";
              matchesSheet[8,  tableHeaderRow].Value = "Rule";
            matchesSheet[9,  tableHeaderRow].Value = "Rule description";
            matchesSheet[10, tableHeaderRow].Value = "Rule Expression";
            matchesSheet[11, tableHeaderRow].Value = "File name";
            matchesSheet[12, tableHeaderRow].Value = "Match on line";
            matchesSheet[13, tableHeaderRow].Value = "Code extract";

            new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Font.Bold = true;
              new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).AutoFilter();
              new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Sides     = BorderSides.Bottom;
              new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.LineStyle = Borderline.Continuous;
              new Range(matchesSheet[0, tableHeaderRow], matchesSheet[columns, tableHeaderRow]).Border.Weight    = 2;
            #endregion

            #region Insert data...
            // Insert data...
            IMatchProxy matchProxy = ProxyHome.Instance.RetrieveMatchProxy(OutputKeyKeeper.Instance.AccessKey);

            foreach (IMatch match in matchProxy.Matches())
            {
                if ((row % 2) == 0)
              new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(220, 230, 241);
            else
              new Range(matchesSheet[0, row], matchesSheet[columns, row]).Interior.Color = Color.FromArgb(197, 217, 241);

                matchesSheet[0,  row].Value = match.Batch.Id;
                matchesSheet[1,  row].Value = $"{match.Batch.TimeStamp.ToShortDateString()} - {match.Batch.TimeStamp.ToShortTimeString()}";
                matchesSheet[2,  row].Value = match.Id;
                matchesSheet[3,  row].Value = $"{RuleSeverityMapper.Int2RuleSeverity((int)match.Severity)}";
                matchesSheet[4,  row].Value = match.ProjectDefinitionRef.Name;
                matchesSheet[5,  row].Value = match.LanguageDeclarationRef.Name;
                matchesSheet[6,  row].Value = match.CategoryDeclarationRef.Name;
                matchesSheet[7,  row].Value = match.CategoryDeclarationRef.Description;
            matchesSheet[8,  row].Value = match.RuleDeclarationRef.Name;
            matchesSheet[9,  row].Value = match.RuleDeclarationRef.Description;
            matchesSheet[10, row].Value = match.RuleDeclarationRef.Expression;
                matchesSheet[11, row].Value = match.Filename;
                matchesSheet[12, row].Value = match.LineNumber;
                matchesSheet[13, row].Value = match.CodeExtract;
                row++;
              }
              #endregion

              #region Export the Excel xml workbook...
              book.Export(OutputFile);
              #endregion
        }
Ejemplo n.º 25
0
		static void Main(string[] args)
		{
			// Create a instance...
			ExcelXmlWorkbook book = new ExcelXmlWorkbook();
			 
			// Many such properties exist. Details can be found in the documentation
			// The author of the document
			book.Properties.Author = "Yogesh Jagota";
			 
			// This returns the first worksheet.
			// Note that we have not declared a instance of a new worksheet
			// All the dirty work is done by the library.
			Worksheet sheet = book[0];
			 
			// Name is the name of the sheet. If not set, the default name
			// style is "sheet" + sheet number, like sheet1, sheet2
			sheet.Name = "AgewiseOutstanding";
			 
			// More on this in documentation
			sheet.FreezeTopRows = 3;
			 
			// and this too...
			sheet.PrintOptions.Orientation = PageOrientation.Landscape;
			sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);
			 
			// This is the actual code which sets out the cell values
			// Note again, that we don't declare any instance at all.
			// All the work is done by the library.
			// Index operator takes first value as column and second as row.
			sheet[0, 0].Value = "Outstanding as on " +
				 DateTime.Now;
			sheet[0, 0].Font.Bold = true;

			sheet.Columns(0).Width = 180;
			sheet.Columns(1).Width = 72;
			sheet.Columns(2).Width = 90;
			sheet.Columns(3).Width = 51;
			sheet.Columns(4).Width = 51;
			sheet.Columns(5).Width = 51;
			sheet.Columns(6).Width = 51;
			sheet.Columns(7).Width = 51;
			sheet.Columns(8).Width = 51;
			sheet.Columns(9).Width = 72;

			sheet[0, 2].Value = "Name of Party";
			sheet[1, 2].Value = "RSM";
			sheet[2, 2].Value = "ASM";
			sheet[3, 2].Value = "0-30";
			sheet[4, 2].Value = "31-60";
			sheet[5, 2].Value = "61-90";
			sheet[6, 2].Value = "91+";
			sheet[7, 2].Value = "Total";
			new Range(sheet[0, 2], sheet[7, 2]).Font.Bold = true;

			sheet[0, 3].Value = "M/s Stupid Paymaster";
			sheet[1, 3].Value = "Mr. Nonsense";
			sheet[2, 3].Value = "Mr. More Nonsense";
			sheet[3, 3].Value = 0;
			sheet[4, 3].Value = 5000;
			sheet[5, 3].Value = 45000;
			sheet[6, 3].Value = 0;
			sheet[7, 3].Value = FormulaHelper.Formula("Sum", new Range(sheet[3, 3], sheet[6, 3]));
			 
			sheet[0, 4].Value = "M/s Good Paymaster";
			sheet[1, 4].Value = "Mr. Good RSM";
			sheet[2, 4].Value = "Mr. Good ASM";
			sheet[3, 4].Value = 32000;
			sheet[4, 4].Value = 0;
			sheet[5, 4].Value = 0;
			sheet[6, 4].Value = 0;
			sheet[7, 4].Value = FormulaHelper.Formula("Sum", new Range(sheet[3, 4], sheet[6, 4]));
			 
			// no extension is added if not present
			string outputFile = "Outstanding File.xml";
			 
			book.Export(outputFile);
		}
Ejemplo n.º 26
0
        /// <summary>
        /// Imports a excel xml workbook into a ExcelXmlWorkbook instance
        /// </summary>
        /// <param name="stream">Stream to import</param>
        /// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
        public static ExcelXmlWorkbook Import(Stream stream)
        {
            XmlReaderSettings settings = new XmlReaderSettings();

            settings.CloseInput     = false;
            settings.IgnoreComments = true;
            settings.IgnoreProcessingInstructions = true;
            settings.IgnoreWhitespace             = true;

            if (!stream.CanRead)
            {
                return(null);
            }

            XmlReader reader = XmlReader.Create(stream, settings);

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            book.Styles.Clear();

            int workSheet = 0;

            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element)
                {
                    switch (reader.Name)
                    {
                    // Document Properties
                    case "DocumentProperties":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book.Properties.Import(reader);
                        }
                        break;
                    }

                    case "Styles":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book.ImportStyles(reader);
                        }

                        break;
                    }

                    case "Names":
                    {
                        ImportNamedRanges(reader, book, null);

                        break;
                    }

                    case "Worksheet":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book[workSheet++].Import(reader);
                        }

                        break;
                    }
                    }
                }
            }

            book.ResolveNamedRangeReferences();

            book.ResolveCellReferences();

            reader.Close();

            stream.Close();
            stream.Dispose();

            return(book);
        }
Ejemplo n.º 27
0
    private void Export_Excel(DataTable dtTemp)
    {
        ExcelXmlWorkbook book = new ExcelXmlWorkbook();

        Worksheet sheet0 = book[0];
        AddSheetData(ref sheet0, "Sheet1", dtTemp);

        //設定輸出檔名
        string s_FileName = "";
        s_FileName = HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + "_CAA18帳務報表.xls", System.Text.Encoding.UTF8);

        //File download
        Response.Charset = "big5";
        Response.ContentType = "application/x-excel;charset='utf-8'";//"application/vnd.ms-excel";
        //Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        book.Export(stream);
        string producedExcel = GetStringFromMemoryStream(stream);
        stream.Dispose();
        Response.Write(producedExcel);
        Response.Flush();
        Response.End();
    }
Ejemplo n.º 28
0
		/// <summary>
		/// Converts a dataset to a work book
		/// </summary>
		/// <param name="source">The source dataset to convert to a work book</param>
		/// <returns>Returns the <see cref="Yogesh.ExcelXml.ExcelXmlWorkbook"/>
		/// for the dataset.</returns>
		/// <remarks>All the tables are converted into sheets with sheet names as table + table number, 
		/// eg. "Table0" "Table1" etc. Supported types which can be successfully
		/// converted to cells are the same as described in <see cref="Yogesh.ExcelXml.Cell"/>
		/// except <see cref="Yogesh.ExcelXml.Cell"/> and 
		/// <see cref="Yogesh.ExcelXml.Formula"/></remarks>
		public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
		{
			ExcelXmlWorkbook book = new ExcelXmlWorkbook();

			for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
			{
				Worksheet sheet = book[tableNumber];
				sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);

				int columnCount = source.Tables[tableNumber].Columns.Count;
				for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
				{
					sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;

					sheet[columnNumber, 0].Font.Bold = true;
				}

				int rowNumber = 0;
				foreach (DataRow row in source.Tables[tableNumber].Rows)
				{
					rowNumber++;

					for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
					{
						string rowType = row[columnNumber].GetType().FullName;

						switch (rowType)
						{
							case "System.DateTime":
								{
									sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
									break;
								}
							case "System.Boolean":
								{
									sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
									break;
								}
							case "System.SByte":
							case "System.Int16":
							case "System.Int32":
							case "System.Int64":
							case "System.Byte":
							case "System.UInt16":
							case "System.UInt32":
							case "System.UInt64":
							case "System.Single":
							case "System.Double":
							case "System.Decimal":
								{
									sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber],
										CultureInfo.InvariantCulture);
									break;
								}
							case "System.DBNull":
								break;
							// case "System.String": <-- default covers this...
							default:
								{
									sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
									break;
								}
						}
					}
				}
			}

			return book;
		}
Ejemplo n.º 29
0
    private void Export_Excel(ArrayList ParameterList)
    {
        try
        {
            DataTable Dt = new DataTable();

            Dt = this.searchData();

            if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
            {
                ErrorMsgLabel.Text = "查無資料";
            }
            else
            {

                ExcelXmlWorkbook book = new ExcelXmlWorkbook();

                Worksheet sheet0 = book[0];

                AddSheetData(ref sheet0, "不備庫存檔匯出資料", Dt);

                //設定輸出檔名
                string s_FileName =
                s_FileName = HttpUtility.UrlEncode("IVM381不備庫存檔_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".XLS", System.Text.Encoding.UTF8);

                //File download
                Response.Charset = "big5";
                Response.ContentType = "application/x-excel;charset='utf-8'";
                Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
                System.IO.MemoryStream stream = new System.IO.MemoryStream();
                book.Export(stream);
                string producedExcel = GetStringFromMemoryStream(stream);
                stream.Close();
                Response.Write(producedExcel);
                Response.Flush();
                Response.End();

                stream.Dispose();
                Response.Clear();

            }
        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 30
0
        internal string RangeReference(Cell cell)
        {
            if (CellFrom == null)
            {
                return(UnresolvedRangeReference);
            }

            if (CellFrom.ParentRow == null)
            {
                return("#N/A");
            }

            if (CellTo != null && CellTo.ParentRow == null)
            {
                return("#N/A");
            }

            if (cell == null)
            {
                throw new ArgumentNullException("cell");
            }

            string range;

            if (Absolute)
            {
                range = AbsoluteReference();
            }
            else
            {
                if (CellTo != null)
                {
                    range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]:R[{2}]C[{3}]",
                                          CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex,
                                          CellFrom.CellIndex - cell.CellIndex,
                                          CellTo.ParentRow.RowIndex - cell.ParentRow.RowIndex,
                                          CellTo.CellIndex - cell.CellIndex);
                }
                else
                {
                    range = String.Format(CultureInfo.InvariantCulture, "R[{0}]C[{1}]",
                                          CellFrom.ParentRow.RowIndex - cell.ParentRow.RowIndex,
                                          CellFrom.CellIndex - cell.CellIndex);
                }
            }

            string sheetReference = "";

            if (CellFrom.ParentRow.ParentSheet != cell.ParentRow.ParentSheet)
            {
                sheetReference = CellFrom.ParentRow.ParentSheet.Name;
                ExcelXmlWorkbook workBook = CellFrom.GetParentBook();

                if (workBook != cell.GetParentBook())
                {
                    throw new ArgumentException("External workbook references are not supported");
                }
            }

            if (!sheetReference.IsNullOrEmpty())
            {
                range = "'" + sheetReference + "'!" + range;
            }

            return(range);
        }
Ejemplo n.º 31
0
    private void Export_Excel()
    {
        try
        {
            this.ErrorMsgLabel.Text = "";
            ParameterList.Clear();
            ParameterList.Add(SLP_VENDOR.Text.Trim());
            BCO.VAM21_BCO bco = new BCO.VAM21_BCO(ConntionDB);
            DataTable dt = bco.QueryData(ParameterList);

            if (dt.Rows.Count == 0)
            {
                this.ErrorMsgLabel.Text = "查無資料";
                return;
            }


            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];

            AddSheetData(ref sheet0, "Sheet1", dt);

            //設定輸出檔名
            string s_FileName =
            s_FileName = HttpUtility.UrlEncode("折讓單自動排除廠商.XLS", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            this.ErrorMsgLabel.Text = ex.ToString();
        }
        finally { }
    }
Ejemplo n.º 32
0
    protected void Btn_ExportSurvey_Click(object sender, EventArgs e)
    {
        try
        {
            ErrorMsgLabel.Text = "";

            #region 資料檢查

            if (SLP_CodeFile1.Text == "" || SLP_CodeFile2.Text == "")
            {
                ErrorMsgLabel.Text = "營業所不可選全部";
                return;
            }
            
            if (string.Compare(SLP_CodeFile1.Text, SLP_CodeFile2.Text) != 0)
            {
                ErrorMsgLabel.Text = "只能選一個營業所";
                return;
            }
            
            if (dSurveyDate.Text == "")
            {
                ErrorMsgLabel.Text = "市調日期不可空白";
                return;
            }

            #endregion

            DataTable dt = new DataTable();

            #region 查詢市調情報資料

            //取得查詢參數: 營業所,市調日期
            ParameterList.Clear();
            ParameterList.Add(GetValueSetParameter(SLP_CodeFile1.Text, "string")); //營業所
            ParameterList.Add(GetValueSetParameter(dSurveyDate.Text, "date")); //市調日期
            ParameterList.Add(GetValueSetParameter(txtInvestigator.Text.Trim(), "string")); //市調人員  2010/09/24 modified
            ParameterList.Add(GetValueSetParameter("9999", "int"));
            //ParameterList.Add(GetValueSetParameter((SLP_CodeFile1.Text != "") ? "" : SLP_CodeFile2.Text, "string")); //營業所2
            ParameterList.Add(GetValueSetParameter(SLP_CodeFile2.Text, "string")); //營業所(迄)
            BCO.MaintainMarketSurveyRecords bco = new MaintainMarketSurveyRecords(ConnectionDB);
            dt = bco.QuerySurveyRec(ParameterList);

            #endregion

            #region 將上市日期轉為無時間的格式
            DataTable Dt = dt.Clone();
            if (dt.Rows.Count > 0)
            {
                Dt.Columns["PUBLISH_DATE"].DataType = System.Type.GetType("System.String");
                foreach (DataRow dr in dt.Rows)
                {
                    DataRow DR = Dt.NewRow();
                    for (int i = 0; i < dt.Columns.Count; i++)
                        DR[i] = dr[i];

                    if (DR["PUBLISH_DATE"] != System.DBNull.Value)
                        DR["PUBLISH_DATE"] = DateTime.Parse(DR["PUBLISH_DATE"].ToString()).ToShortDateString();
                    Dt.Rows.Add(DR);
                }
                dt.Dispose();
                dt = null;
            }
            #endregion

            if (Dt == null || (Dt != null && Dt.Rows.Count <= 0))
            {
                //若不存在市調情報資料,無法匯出市調表
                ErrorMsgLabel.Text = "查無資料";
            }
            else
            {
                //若已存在市調情報資料,匯出市調表
                #region 匯出市調表

                //設定輸出檔名
                string s_FileName = "";
                s_FileName = HttpUtility.UrlEncode("市調表.xls", System.Text.Encoding.UTF8);

                #region 設定Excel Worksheet

                //Excel Worksheet initilize
                ExcelXmlWorkbook book = new ExcelXmlWorkbook();
                Worksheet sheet = book[0];
                sheet.Name = "市調表";

                //設定Worksheet橫印
                sheet.PrintOptions.Orientation = PageOrientation.Landscape;
                sheet.PrintOptions.SetMargins(0.5, 0.7, 0.5, 0.6);

                //設定Worksheet各column 寬度
                sheet.Columns(0).Width = 55;
                sheet.Columns(1).Width = 130;
                sheet.Columns(2).Width = 43;
                sheet.Columns(3).Width = 58;
                sheet.Columns(4).Width = 30;

                sheet.Columns(5).Width = 42;
                sheet.Columns(6).Width = 30;
                sheet.Columns(7).Width = 30;
                sheet.Columns(8).Width = 30;
                sheet.Columns(9).Width = 30;

                sheet.Columns(10).Width = 42;
                sheet.Columns(11).Width = 30;
                sheet.Columns(12).Width = 30;
                sheet.Columns(13).Width = 30;
                sheet.Columns(14).Width = 30;

                sheet.Columns(15).Width = 42;
                sheet.Columns(16).Width = 30;
                sheet.Columns(17).Width = 30;
                sheet.Columns(18).Width = 30;
                sheet.Columns(19).Width = 30;

                //設定外框線
                sheet.Border.Sides = BorderSides.All;
                sheet.Border.LineStyle = Borderline.Continuous;

                #endregion

                int iRow = 0; //Worksheet 列數

                string strSTORE = "";
                ArrayList alTITLE = new ArrayList();//營業所,市調人員ArrayList
                ArrayList alSTORE = new ArrayList();//店號,店名ArrayList
                int iStoreCount = 0; //總店數

                //營業所,市調人員ArrayList
                foreach (DataRow dr in Dt.Rows)
                {
                    alTITLE.Add(new string[] { dr["EXPLAIN"].ToString(), dr["INVESTIGATOR"].ToString() });
                    break; //(營業所,市調人員)都相同, 取第一筆即可
                }

                int iRowStore = 3;//每行店數
                int iStoreCol = 5;//每個店有幾個欄位

                foreach (string[] ss in alTITLE)
                {
                    //取得店號,店名,市調人員ArrayList
                    foreach (DataRow dr in Dt.Select("ITEM='" + Dt.Rows[0]["ITEM"].ToString() + "'"))
                    {
                        alSTORE.Add(new string[] { dr["STORE"].ToString(), dr["STORE_NAME"].ToString(), dr["INVESTIGATOR"].ToString() });//20121204 顏筱盈
                    }

                    //總店數
                    iStoreCount = alSTORE.Count;

                    for (int iStoreArea = 0; iStoreArea < Convert.ToInt32(Math.Ceiling(alSTORE.Count / (decimal)iRowStore)); iStoreArea++)
                    {
                        #region 設定Header列資訊

                        ArrayList alQTY = new ArrayList();
                        sheet[0, iRow].Value = ss[0]; //營業所
                        //sheet[1, iRow].Value = ss[1]; //市調人員
                        sheet[1, iRow].Value = ""; //20121204 顏筱盈
                        sheet[2, iRow].Value = "期別";
                        sheet[3, iRow].Value = "上市日期";
                        sheet[4, iRow].Value = "市調";
                        new Range(sheet[0, iRow + 1], sheet[3, iRow + 1]).Merge();
                        sheet[4, iRow + 1].Value = "次數";

                        for (int iStoreNo = (iRowStore * iStoreArea); iStoreNo < (iRowStore * iStoreArea + iRowStore); iStoreNo++)
                        {
                            if (iStoreNo >= alSTORE.Count) break;
                            string[] ss1 = (string[])alSTORE[iStoreNo];
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow].Value = ss1[0];//店號
                            //sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1];//店名
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow].Value = ss1[1] + " ( " +ss1[2] + " )";//店名,市調人員 20121204 顏筱盈
                            new Range(sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow], sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow]).Merge();

                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5, iRow + 1].Value = "發書";
                            alQTY.Add((iStoreNo - iStoreArea * iRowStore) * iStoreCol + 5);
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 6, iRow + 1].Value = "補書";
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 7, iRow + 1].Value = "退書";
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 8, iRow + 1].Value = "庫存";
                            sheet[(iStoreNo - iStoreArea * iRowStore) * iStoreCol + 9, iRow + 1].Value = "備註";
                        }

                        #endregion

                        iRow = iRow + 2;

                        int iItemCount = 0; //商品項數

                        //取得商品相關資料                        
                        foreach (DataRow dr in Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea])[0] + "'", "ITEM,ROUTE,STEP"))
                        {
                            sheet[0, iRow].Value = dr["ITEM"].ToString();           //品號
                            sheet[1, iRow].Value = dr["ITEM_NAME"].ToString();      //品名
                            sheet[2, iRow].Value = dr["PERIOD"].ToString();         //期別
                            sheet[3, iRow].Value = dr["PUBLISH_DATE"].ToString();   //上市日期
                            sheet[4, iRow].Value = dr["SURVEY_TIME"].ToString();    //市調次數

                            int iStore = 0;
                            foreach (object o in alQTY)
                            {
                                DataRow[] drQty = Dt.Select("STORE='" + ((string[])alSTORE[iRowStore * iStoreArea + iStore])[0] + "'" + " AND ITEM='" + dr["ITEM"].ToString() + "'" + "  AND PERIOD='" + dr["PERIOD"].ToString() + "'", "ITEM,ROUTE,STEP");
                                sheet[Convert.ToInt32(o), iRow].Value = drQty[0]["INCOME_QTY"].ToString(); //發書
                                sheet[Convert.ToInt32(o) + 1, iRow].Value = drQty[0]["REPLINISHMENT_QTY"].ToString(); //補書
                                sheet[Convert.ToInt32(o) + 2, iRow].Value = drQty[0]["RETURN_QTY"].ToString(); //退書
                                iStore++;
                            }
                            iRow++;
                            iItemCount++;
                        }
                        iRow++;

                        //商品總項數設定為20,不足則填入空列數
                        if (iItemCount != 0 & iItemCount < 20)
                        {
                            for (int iAdd = iItemCount; iAdd < 20; iAdd++)
                            {
                                iRow++;
                            }
                        }
                    }
                }

                #region 報表最後一頁特別處理

                double dStoreCal = (double)(iStoreCount / (decimal)iRowStore);

                int iNewBeginRow = Convert.ToInt32(Math.Ceiling(dStoreCal)) * 23;

                //報表最後一頁,最後一列,最後一格 給一空白, 這樣最後一頁印出才會完整
                if (Math.Ceiling(dStoreCal) % 2 == 1)
                {
                    sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1 + 23].Value = " ";

                }
                else
                {
                    sheet[4 + iRowStore * iStoreCol, iNewBeginRow - 1].Value = " ";
                }

                #endregion

                //設定報表 縮放比例
                int iPageCount = Convert.ToInt32(Math.Ceiling(dStoreCal / 2.0));
                sheet.PrintOptions.SetFitToPage(1, iPageCount); //Sets excel's fit to page property (int width,int height)

                //File download
                Response.Charset = "big5";
                Response.ContentType = "application/vnd.ms-excel;charset='utf-8'";
                Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名  

                System.IO.MemoryStream stream = new System.IO.MemoryStream();
                book.Export(stream);
                string producedExcel = GetStringFromMemoryStream(stream);
                stream.Close();
                Response.Write(producedExcel);
                Response.Flush();
                Response.End();

                #endregion
            }
        }
        catch (Exception ex)
        {
            ErrorMsgLabel.Text = ex.Message;
        }
    }
Ejemplo n.º 33
0
    private void Export_Excel(ArrayList ParameterList)
    {
        try
        {

            CAAModel.CAA32_BCO BCO = new CAAModel.CAA32_BCO(ConntionDB);
            // 讀取 Excel 資料    

            DataTable dt = BCO.QueryExportFile(ParameterList);
            if (dt.Rows.Count == 0)
            {
                lblError.Text = "查無資料";
                return;
            }
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            Worksheet sheet0 = book[0];

            AddSheetData(ref sheet0, "前期餘額匯出", dt);

            //設定輸出檔名
            string s_FileName =
            s_FileName = HttpUtility.UrlEncode("CAA321_" + ParameterList[0].ToString() + ParameterList[1].ToString() + ".XLS", System.Text.Encoding.UTF8);

            //File download
            Response.Charset = "big5";
            Response.ContentType = "application/x-excel;charset='utf-8'";
            Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名
            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            book.Export(stream);
            string producedExcel = GetStringFromMemoryStream(stream);
            stream.Close();
            Response.Write(producedExcel);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            this.lblError.Text = ex.ToString();
        }
        finally { }
    }
        public static bool exportToExcel_old(DataGridView dgv, string fileName)
        {
            ExcelXmlWorkbook libro = new ExcelXmlWorkbook();

            libro.Properties.Author = "Trust International S.A."; // Tomar el nombre de la empresa de Propertie.
            
            Worksheet hoja = libro[0];
            hoja.Name = "Exportacion";
            //hoja.FreezeTopRows = 3;
            //hoja.DisplayFormat = DisplayFormatType.None;
            hoja.PrintOptions.Orientation = PageOrientation.Landscape;
            hoja.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

            XmlStyle estiloCeldaNombreColumnas = new XmlStyle();
            estiloCeldaNombreColumnas.Font.Bold = true;
            estiloCeldaNombreColumnas.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
            estiloCeldaNombreColumnas.Border.LineStyle = Borderline.Continuous;
            estiloCeldaNombreColumnas.Border.Weight = 2;
            estiloCeldaNombreColumnas.Border.Sides = BorderSides.All;



            // Primero cargo todas las columnas permitiendo hacerle algun formato diferente.
            foreach (DataGridViewColumn col in dgv.Columns)
            {
                hoja[col.Index, 0].Value = col.Name;
                hoja[col.Index, 0].Style = estiloCeldaNombreColumnas;
            }

            XmlStyle estiloCeldaNormal = new XmlStyle();
            estiloCeldaNormal.Font.Bold = false;
            estiloCeldaNormal.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
            estiloCeldaNormal.Border.LineStyle = Borderline.Continuous;
            estiloCeldaNormal.Border.Weight = 1;
            estiloCeldaNormal.Border.Sides = BorderSides.All;
            //estiloCeldaNormal.DisplayFormat = DisplayFormatType.Text;
            
            int fila = 0;
            int columna = 0;
            double[] maxWithPerColumn;
            TimeSpan h;
            maxWithPerColumn = new double[dgv.Columns.Count];
            DisplayFormatType cellType;
            while (fila < dgv.Rows.Count)
            {
                columna = 0;
                while (columna < dgv.Columns.Count)
                {
                    
                    if (dgv[columna, fila].ValueType == typeof(DateTime))
                    {
                        cellType = DisplayFormatType.GeneralDate;
                        if (dgv[columna,fila].Value.ToString() != "")
                            hoja[columna, fila+1].Value = ((DateTime)dgv[columna,fila].Value).ToShortDateString();
                    }
                    else if (dgv[columna, fila].ValueType == typeof(TimeSpan))
                    {
                        cellType = DisplayFormatType.Time;
                        h = (TimeSpan)dgv[columna, fila].Value;
                        if (dgv[columna, fila].Value.ToString() != "")
                            hoja[columna, fila + 1].Value = System.Math.Abs(System.Math.Truncate(h.TotalHours)).ToString()  + ":" + System.Math.Abs(h.Minutes).ToString();          
                                //((TimeSpan)dgv[columna, fila].Value).ToString();
                    }
                    else if (dgv[columna, fila].ValueType == typeof(Boolean))
                    {
                        cellType = DisplayFormatType.Custom;
                        if (dgv[columna, fila].Value.ToString() != "")
                            hoja[columna, fila+1].Value = ((Boolean)dgv[columna, fila].Value);
                    }
                    else if (dgv[columna, fila].ValueType == typeof(int) || dgv[columna, fila].ValueType == typeof(uint) || dgv[columna, fila].ValueType == typeof(byte) || dgv[columna, fila].ValueType == typeof(sbyte))
                    {
                        cellType = DisplayFormatType.Standard;
                        if (dgv[columna, fila].Value.ToString() != "")
                            hoja[columna, fila + 1].Value = dgv[columna, fila].Value;
                    }
                    else
                    {
                        // String
                        String str = dgv[columna, fila].Value.ToString();//.Replace("\n",Environment.NewLine);
                        cellType = DisplayFormatType.Text;
                        hoja[columna, fila + 1].Value = str;
                        
                    }
                    
                    if (dgv[columna, fila].Value.ToString().Length > maxWithPerColumn[columna])
                    {
                        maxWithPerColumn[columna] = (double)dgv[columna, fila].Value.ToString().Length;
                    }
                    //hoja[columna, fila].ContentType = ContentType.Formula;
                    estiloCeldaNormal.DisplayFormat = cellType;
                    hoja[columna, fila + 1].Style = estiloCeldaNormal;
                    columna++;

                }
                fila++;
            }
            columna = 0;
            while (columna < dgv.Columns.Count)
            {
                hoja.Columns(columna).Width = dgv.Columns[columna].Width;
                columna++;
            }
            
            // no extension is added if not present

            Stream sw = new FileStream(fileName,FileMode.Create);
            bool retval;
            retval = libro.Export(sw);
            sw.Close();
            return retval;
        }