Exemplo n.º 1
0
        public static Workbook ToExcel <T>(this IEnumerable <T> src) where T : new()
        {
            var myWb = new Workbook();

            myWb.AddStyles();
            var myWs = myWb.Worksheets.Add("one");

            var headerRow = myWs.Table.Rows.Add();
            var cols      = MereUtils.CacheCheck <T>().SelectMereColumns.ToList();

            foreach (var field in cols)
            {
                var myCol = new WorksheetColumn(150);
                myWs.Table.Columns.Add(myCol);
                headerRow.Cells.Add(field.DisplayName, DataType.String, "HeaderStyle");
            }

            foreach (var rec in src)
            {
                var newRow = myWs.Table.Rows.Add();
                newRow.AutoFitHeight = false;
                foreach (var field in cols)
                {
                    var v   = field.GetBase(rec);
                    var val = v == null ? "" : v.ToString();
                    newRow.Cells.Add(val, DataType.String, (myWs.Table.Rows.IndexOf(newRow) % 2) == 0 ? "s31" : "s31Odd");
                }
            }

            return(myWb);
        }
Exemplo n.º 2
0
        private void GenerateShopListWorksheet(WorksheetCollection sheets)
        {
            Worksheet sheet = sheets.Add("ShopList");

            sheet.Names.Add(new WorksheetNamedRange("Print_Area", "=Sheet1!R1C1:R24C6", false));

            //TODO:Add RTL
            //sheet.Table.Options.
            sheet.Table.DefaultRowHeight   = 14.25F;
            sheet.Table.DefaultColumnWidth = 54F;
            //sheet.Table.ExpandedColumnCount = 6;
            //sheet.Table.ExpandedRowCount = 24;
            sheet.Table.FullColumns = 1;
            sheet.Table.FullRows    = 1;
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            column0.Index = 5;
            column0.Width = 177;

            GenerateHeaders(sheet);

            GenerateDataRows(sheet);
            // -----------------------------------------------
            GenerateFooterRow(sheet);
            // -----------------------------------------------
            //  Options
            // -----------------------------------------------
            sheet.Options.Selected                     = true;
            sheet.Options.ProtectObjects               = false;
            sheet.Options.ProtectScenarios             = false;
            sheet.Options.PageSetup.Header.Margin      = 0.3F;
            sheet.Options.PageSetup.Footer.Margin      = 0.3F;
            sheet.Options.PageSetup.PageMargins.Bottom = 0.75F;
            sheet.Options.PageSetup.PageMargins.Left   = 0.7F;
            sheet.Options.PageSetup.PageMargins.Right  = 0.7F;
            sheet.Options.PageSetup.PageMargins.Top    = 0.75F;
            sheet.Options.Print.PaperSizeIndex         = 9;
            sheet.Options.Print.HorizontalResolution   = 1200;
            sheet.Options.Print.VerticalResolution     = 1200;
            sheet.Options.Print.ValidPrinterInfo       = true;
        }
Exemplo n.º 3
0
        private void GenerarEncabezadoDatosEnHoja(Worksheet sheet)
        {
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            column0.Width   = 75;
            column0.StyleID = "s62";
            WorksheetColumn column1 = sheet.Table.Columns.Add();

            column1.Width   = 75;
            column1.StyleID = "s62";
            WorksheetColumn column2 = sheet.Table.Columns.Add();

            column2.Width   = 75;
            column2.StyleID = "s62";
            WorksheetColumn column3 = sheet.Table.Columns.Add();

            column3.Width   = 75;
            column3.StyleID = "s62";
            WorksheetColumn column4 = sheet.Table.Columns.Add();

            column4.Width   = 75;
            column4.StyleID = "s62";

            WorksheetRow Row0 = sheet.Table.Rows.Add();

            Row0.AutoFitHeight = false;
            WorksheetRow Row1 = sheet.Table.Rows.Add();

            Row1.Height        = 27;
            Row1.AutoFitHeight = false;
            Row1.Cells.Add("FARMACIA AGRICOLA >> TIERRA SANA >>", DataType.String, "s65");

            WorksheetRow Row2 = sheet.Table.Rows.Add();

            Row2.AutoFitHeight = true;
            Row2.Cells.Add("SUCURSAL", DataType.String, "s63");
            WorksheetCell cell;

            cell           = Row2.Cells.Add();
            cell.Data.Type = DataType.String;
            cell.Data.Text = "SUCURSAL NUMERO N° 1";

            string       date = Convert.ToString(DateTime.Now);
            WorksheetRow Row3 = sheet.Table.Rows.Add();

            Row3.AutoFitHeight = true;
            Row3.Cells.Add("FECHA", DataType.String, "s63");
            cell           = Row3.Cells.Add();
            cell.Data.Type = DataType.String;
            cell.Data.Text = date.ToString();

            WorksheetRow Row4 = sheet.Table.Rows.Add();

            Row4.AutoFitHeight = true;
            Row4.Cells.Add("MONEDA", DataType.String, "s63");
            cell           = Row4.Cells.Add();
            cell.Data.Type = DataType.String;
            cell.Data.Text = "SOLES";
            WorksheetRow Row8 = sheet.Table.Rows.Add();

            Row8.AutoFitHeight = true;
            Row8.Height        = 20;
            Row8.Cells.Add("N°", DataType.String, "s84");
            Row8.Cells.Add("CODIGO", DataType.String, "s84");
            Row8.Cells.Add("NOMBRE PRODUCTO", DataType.String, "s84");
            Row8.Cells.Add("PRECIO COMPRA", DataType.String, "s84");
            Row8.Cells.Add("PRECIO PRODUCTO", DataType.String, "s84");
            Row8.Cells.Add("STOCK", DataType.String, "s84");
            Row8.Cells.Add("STOCK PROMEDIO", DataType.String, "s84");
            Row8.Cells.Add("STOCK MINIMO", DataType.String, "s84");
            Row8.Cells.Add("CATEGORIA", DataType.String, "s84");
            Row8.Cells.Add("UNIDAD", DataType.String, "s84");
            Row8.Cells.Add("ALMACEN", DataType.String, "s84");



            //WorksheetRow Row10 = sheet.Table.Rows.Add();
            //Row10.AutoFitHeight = true;
            //Row10.Cells.Add("MONEDA s87", DataType.String, "s87");
            //cell = Row10.Cells.Add();
            //cell.Data.Type = DataType.String;
            //cell.Data.Text = "SOLES s87";

            //WorksheetRow Row11 = sheet.Table.Rows.Add();
            //Row11.AutoFitHeight = true;
            //Row11.Cells.Add("MONEDA s88", DataType.String, "s88");
            //cell = Row11.Cells.Add();
            //cell.Data.Type = DataType.String;
            //cell.Data.Text = "SOLES s88";

            //WorksheetRow Row12 = sheet.Table.Rows.Add();
            //Row12.AutoFitHeight = true;
            //Row12.Cells.Add("MONEDA s89", DataType.String, "s89");
            //cell = Row12.Cells.Add();
            //cell.Data.Type = DataType.String;
            //cell.Data.Text = "2";

            //WorksheetRow Row9 = sheet.Table.Rows.Add();
            //Row9.AutoFitHeight = true;
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
            //cell = Row9.Cells.Add();
            //cell.StyleID = "s85";
        }
        private string GenerateWorksheet(WorksheetCollection sheets, DateTime dttungay, DateTime dtdenngay, DataTable dtnv, ProgressBar progressBar)
        {
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            tblhoadonbanBL ctrhd = new tblhoadonbanBL();
            Worksheet      sheet = sheets.Add("TKDoanhThu");

            sheet.Table.DefaultRowHeight    = 15F;
            sheet.Table.ExpandedColumnCount = 10;
            if (dtnv.Rows.Count > 1)
            {
                try
                {
                    DataTable dttemp = ctrhd.TKDoanhThu(dttungay.ToString(), dtdenngay.AddDays(1).ToString());
                    sheet.Table.ExpandedRowCount = 8 + dttemp.Rows.Count;
                }
                catch (Exception ex)
                {
                    return(ex.Message);
                }
            }
            if (dtnv.Rows.Count == 1)
            {
                try
                {
                    DataTable dttemp = ctrhd.TKDoanhThubyIDNV(dttungay.ToString(), dtdenngay.AddDays(1).ToString(), dtnv.Rows[0]["taikhoan"].ToString().Trim());
                    sheet.Table.ExpandedRowCount = 8 + dttemp.Rows.Count;
                }
                catch (Exception ex)
                {
                    return(ex.Message);
                }
            }
            sheet.Table.FullColumns = 1;
            sheet.Table.FullRows    = 1;
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            column0.Width   = 24;
            column0.StyleID = "s62";
            WorksheetColumn column1 = sheet.Table.Columns.Add();

            column1.Width   = 65;
            column1.StyleID = "s62";
            WorksheetColumn column2 = sheet.Table.Columns.Add();

            column2.Width   = 78;
            column2.StyleID = "s62";
            WorksheetColumn column3 = sheet.Table.Columns.Add();

            column3.Width   = 93;
            column3.StyleID = "s62";
            sheet.Table.Columns.Add(132);
            sheet.Table.Columns.Add(95);
            sheet.Table.Columns.Add(76);
            sheet.Table.Columns.Add(75);
            sheet.Table.Columns.Add(80);
            sheet.Table.Columns.Add(144);
            // -----------------------------------------------
            #region Add header
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row0 = sheet.Table.Rows.Add();
            Row0.Height        = 23;
            Row0.AutoFitHeight = false;
            WorksheetCell cell;
            cell             = Row0.Cells.Add();
            cell.StyleID     = "s90";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "THỐNG KÊ DOANH THU";
            cell.MergeAcross = 8;
            // -----------------------------------------------
            WorksheetRow Row1 = sheet.Table.Rows.Add();
            Row1.AutoFitHeight = false;
            cell             = Row1.Cells.Add();
            cell.StyleID     = "s91";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "(Từ ngày " + dttungay.ToString("dd/MM/yyyy") + " đến ngày " + dtdenngay.ToString("dd/MM/yyyy") + ")";
            cell.MergeAcross = 8;
            // -----------------------------------------------
            WorksheetRow Row2 = sheet.Table.Rows.Add();
            Row2.AutoFitHeight = false;
            cell             = Row2.Cells.Add();
            cell.StyleID     = "s96";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Đơn vị tính: VNÐ";
            cell.MergeAcross = 8;
            // -----------------------------------------------
            WorksheetRow Row3 = sheet.Table.Rows.Add();
            Row3.AutoFitHeight = false;
            Row3.Cells.Add("Stt", DataType.String, "s64");
            Row3.Cells.Add("Nhân viên", DataType.String, "s64");
            Row3.Cells.Add("Doanh thu", DataType.String, "s64");
            Row3.Cells.Add("Ngày xuất HĐ", DataType.String, "s64");
            Row3.Cells.Add("Tên khách hàng", DataType.String, "s64");
            Row3.Cells.Add("Tiền vốn/HĐ", DataType.String, "s64");
            Row3.Cells.Add("Doanh thu/HĐ", DataType.String, "s64");
            Row3.Cells.Add("Lợi nhuận/HĐ", DataType.String, "s64");
            Row3.Cells.Add("Chiết khấu/HĐ", DataType.String, "s64");

            #endregion
            // -----------------------------------------------
            #region Add Content
            int rowcount = 0;
            for (int i = 0; i < dtnv.Rows.Count; i++)
            {
                Application.DoEvents();
                progressBar.PerformStep();
                Application.DoEvents();
                string sidnv = "";
                try { sidnv = dtnv.Rows[i]["taikhoan"].ToString().Trim(); }
                catch { }
                string shoten = "";
                try { shoten = dtnv.Rows[i]["hoten"].ToString().Trim(); }
                catch { shoten = "-/-"; }
                DataTable dt = new DataTable();
                Application.DoEvents();
                progressBar.PerformStep();
                Application.DoEvents();
                dt = ctrhd.TKDoanhThubyIDNV(dttungay.ToString(), dtdenngay.AddDays(1).ToString(), sidnv);
                if (i == 0)
                {
                    rowcount = dt.Rows.Count;
                }
                if (i > 0)
                {
                    if (rowcount > 0)
                    {
                        rowcount = rowcount + dt.Rows.Count;
                    }
                    if (rowcount == 0)
                    {
                        rowcount = dt.Rows.Count;
                    }
                }
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    WorksheetRow row = sheet.Table.Rows.Add();
                    if (j == 0)
                    {
                        row.Cells.Add((j + 1).ToString().Trim(), DataType.Number, "s65");
                        cell           = row.Cells.Add();
                        cell.StyleID   = "s92";
                        cell.Data.Type = DataType.String;
                        cell.Data.Text = shoten;
                        cell.MergeDown = dt.Rows.Count - 1;
                        cell           = row.Cells.Add();
                        cell.StyleID   = "m66637128";
                        cell.Data.Type = DataType.Number;
                        cell.MergeDown = dt.Rows.Count - 1;
                        if (i == 0)
                        {
                            cell.Formula = "=SUM(RC[4]:R[" + (dt.Rows.Count - 1).ToString().Trim() + "]C[4])";
                        }
                        if (i == 1)
                        {
                            if (dt.Rows.Count > 1)
                            {
                                cell.Formula = "=SUM(RC[4]:R[" + (rowcount - 1 - i).ToString().Trim() + "]C[4])";
                            }
                            else
                            {
                                cell.Formula = "=SUM(RC[4]:R[0]C[4])";
                            }
                        }
                        if (i > 1)
                        {
                            if (dt.Rows.Count > 1)
                            {
                                cell.Formula = "=SUM(RC[4]:R[" + (rowcount - 1 - i).ToString().Trim() + "]C[4])";
                            }
                            else
                            {
                                cell.Formula = "=SUM(RC[4]:R[0]C[4])";
                            }
                        }
                        DateTime dttemp = Convert.ToDateTime(dt.Rows[j]["ngaytao"].ToString().Trim());
                        row.Cells.Add(dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim(), DataType.String, "s67");
                        row.Cells.Add(dt.Rows[j]["tenkh"].ToString().Trim(), DataType.String, "s67");
                        row.Cells.Add(dt.Rows[j]["gianhap"].ToString().Trim(), DataType.Number, "s66");
                        row.Cells.Add(dt.Rows[j]["tongtien"].ToString().Trim(), DataType.Number, "s66");
                        cell           = row.Cells.Add();
                        cell.StyleID   = "s66";
                        cell.Data.Type = DataType.Number;
                        cell.Formula   = "=RC[-1]-RC[-2]-RC[1]";
                        row.Cells.Add(dt.Rows[j]["chietkhau"].ToString().Trim(), DataType.Number, "s66");
                    }
                    else
                    {
                        row.Cells.Add((j + 1).ToString().Trim(), DataType.Number, "s65");
                        cell           = row.Cells.Add();
                        cell.StyleID   = "s67";
                        cell.Data.Type = DataType.String;
                        DateTime dttemp = Convert.ToDateTime(dt.Rows[j]["ngaytao"].ToString().Trim());
                        cell.Data.Text = dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim();
                        cell.Index     = 4;
                        row.Cells.Add(dt.Rows[j]["tenkh"].ToString().Trim(), DataType.String, "s67");
                        row.Cells.Add(dt.Rows[j]["gianhap"].ToString().Trim(), DataType.Number, "s66");
                        row.Cells.Add(dt.Rows[j]["tongtien"].ToString().Trim(), DataType.Number, "s66");
                        cell           = row.Cells.Add();
                        cell.StyleID   = "s66";
                        cell.Data.Type = DataType.Number;
                        cell.Formula   = "=RC[-1]-RC[-2]-RC[1]";
                        row.Cells.Add(dt.Rows[j]["chietkhau"].ToString().Trim(), DataType.Number, "s66");
                    }
                }
            }
            #endregion
            // -----------------------------------------------
            #region Add Tính toán Sum
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row7 = sheet.Table.Rows.Add();
            Row7.AutoFitHeight = false;
            cell             = Row7.Cells.Add();
            cell.StyleID     = "m66637088";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng doanh thu";
            cell.MergeAcross = 7;
            cell             = Row7.Cells.Add();
            cell.StyleID     = "s68";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + rowcount.ToString().Trim() + "]C[-2]:R[-1]C[-2])";
            // -----------------------------------------------
            WorksheetRow Row8 = sheet.Table.Rows.Add();
            Row8.AutoFitHeight = false;
            cell             = Row8.Cells.Add();
            cell.StyleID     = "m66637108";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng lợi nhuận";
            cell.MergeAcross = 7;
            cell             = Row8.Cells.Add();
            cell.StyleID     = "s69";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + (rowcount + 1).ToString().Trim() + "]C[-1]:R[-2]C[-1])";
            #endregion
            // -----------------------------------------------
            //  Options
            // -----------------------------------------------
            sheet.Options.Selected                     = true;
            sheet.Options.ProtectObjects               = false;
            sheet.Options.ProtectScenarios             = false;
            sheet.Options.PageSetup.Header.Margin      = 0.3F;
            sheet.Options.PageSetup.Footer.Margin      = 0.3F;
            sheet.Options.PageSetup.PageMargins.Bottom = 0.75F;
            sheet.Options.PageSetup.PageMargins.Left   = 0.7F;
            sheet.Options.PageSetup.PageMargins.Right  = 0.7F;
            sheet.Options.PageSetup.PageMargins.Top    = 0.75F;
            sheet.Options.Print.HorizontalResolution   = 300;
            sheet.Options.Print.VerticalResolution     = 0;
            sheet.Options.Print.ValidPrinterInfo       = true;
            return("");
        }
Exemplo n.º 5
0
        public void CreateExcelReportForObj()
        {
            dcObj = new PartDataContext();
            Warehouse w = string.IsNullOrEmpty(WarehouseId) ? null : dcObj.ActiveWarehouses.SingleOrDefault(wh => wh.WarehouseId == long.Parse(WarehouseId));
            Dealer d = dcObj.Dealers.SingleOrDefault(dl => dl.DealerCode == DealerCode);
            if (d == null) return;

            string path = Path.Combine(HttpRuntime.AppDomainAppPath, PartMonthlyReport.GetMonthlyReportObjFilePath(w == null ? null : w.Code, DealerCode, Month, Year));

            List<MonthlyReportObject> data = null;
            //for (int i = 0; i < 40; i++)
            {
                data = GetMonthlyReportObjects(w, d, Month, Year);
            }

            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets.Add(string.Format("{0}.{1}", DealerCode, w == null ? "All" : w.Code));

            #region Setting - styles

            // Some optional properties of the Document
            book.ExcelWorkbook.ActiveSheetIndex = 1;
            book.Properties.Author = "ThangLong";
            book.Properties.Title = "Stock monthly report by components";
            book.Properties.Created = DateTime.Now;

            // Add some styles to the Workbook
            WorksheetStyle style = book.Styles.Add("HeaderStyle");
            //style.Font.FontName = "Tahoma";
            //style.Font.Size = 13;
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous).Weight = 1;
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous).Weight = 1;
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous).Weight = 1;
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous).Weight = 1;
            style.Font.Bold = true;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Alignment.Vertical = StyleVerticalAlignment.Center;
            style.Font.Color = "White";
            style.Interior.Color = "#555555";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            style = book.Styles.Add("Group");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Vertical = StyleVerticalAlignment.Center;
            style.Interior.Color = "#CCCCCC";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            style = book.Styles.Add("GroupCenter");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Vertical = StyleVerticalAlignment.Center;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Interior.Color = "#CCCCCC";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            style = book.Styles.Add("DetailOdd");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Vertical = StyleVerticalAlignment.Center;

            style = book.Styles.Add("DetailEven");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Vertical = StyleVerticalAlignment.Center;
            style.Interior.Color = "#f1f5fa";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            style = book.Styles.Add("DetailOddCenter");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Alignment.Vertical = StyleVerticalAlignment.Center;

            style = book.Styles.Add("DetailEvenCenter");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Alignment.Vertical = StyleVerticalAlignment.Center;
            style.Interior.Color = "#f1f5fa";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            // Create the Default Style to use for everyone
            style = book.Styles.Add("Default");
            style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous);
            style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous);
            style.Alignment.Vertical = StyleVerticalAlignment.Center;

            #endregion

            #region column settings

            WorksheetColumn col;
            col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // plType
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // plCode
            col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // plName
            col = new WorksheetColumn(20); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // No
            col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // pType
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // pCode
            col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pEname
            col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pVname

            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // Begin
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // In
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // InAmount
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // Out
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // OutAmount
            col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true;  // Balance

            #endregion

            #region header rows

            WorksheetCell cell;
            WorksheetRow row = sheet.Table.Rows.Add();
            cell = row.Cells.Add("Report for", DataType.String, "HeaderStyle"); cell.MergeAcross = 2;
            cell = row.Cells.Add("Part", DataType.String, "HeaderStyle"); cell.MergeAcross = 4;
            cell = row.Cells.Add("Begin", DataType.String, "HeaderStyle"); cell.MergeDown = 1;
            cell = row.Cells.Add("In", DataType.String, "HeaderStyle"); cell.MergeAcross = 1;
            cell = row.Cells.Add("Out", DataType.String, "HeaderStyle"); cell.MergeAcross = 1;
            cell = row.Cells.Add("Balance", DataType.String, "HeaderStyle"); cell.MergeDown = 1;

            row = sheet.Table.Rows.Add();
            cell = row.Cells.Add("Type", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Code", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Name", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("No", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Type", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Code", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("English name", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Vietnamese name", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle"); cell.Index = 10;
            cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle");
            cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle");

            #endregion

            #region bind data

            string pOddStyle = w == null ? "Group" : "DetailOdd",
                   pEvenStyle = w == null ? "Group" : "DetailOdd",
                   pOddCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter",
                   EvenCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter",

                   dOddStyle = "DetailOdd", dEvenStyle = "DetailEven",
                   dOddCenterStyle = "DetailOddCenter", dEvenCenterStyle = "DetailEvenCenter";

            int pIndex = 0;
            data.ForEach(part =>
            {
                pIndex++;
                bool odd = pIndex % 2 == 1;
                string pStyle = odd ? pOddStyle : pEvenStyle;
                string pcStyle = odd ? pOddCenterStyle : EvenCenterStyle;
                // group line
                row = sheet.Table.Rows.Add();
                cell = row.Cells.Add(part.PlaceType, DataType.String, pcStyle);
                cell = row.Cells.Add(part.PlaceCode, DataType.String, pStyle);
                cell = row.Cells.Add(part.PlaceName, DataType.String, pStyle);

                cell = row.Cells.Add("", DataType.String, pcStyle);
                cell = row.Cells.Add("", DataType.String, pcStyle);
                cell = row.Cells.Add("", DataType.String, pStyle);
                cell = row.Cells.Add("", DataType.String, pStyle);
                cell = row.Cells.Add("", DataType.String, pStyle);

                cell = row.Cells.Add(part.BeginQuantity.ToString(), DataType.Number, pStyle); //cell.Index = 9;
                cell = row.Cells.Add(part.InQuantity.ToString(), DataType.Number, pStyle);
                cell = row.Cells.Add(part.InAmount.ToString(), DataType.Number, pStyle);
                cell = row.Cells.Add(part.OutQuantity.ToString(), DataType.Number, pStyle);
                cell = row.Cells.Add(part.OutAmount.ToString(), DataType.Number, pStyle);
                cell = row.Cells.Add(part.Balance.ToString(), DataType.Number, pStyle);
                // detail lines
                int index = 0;
                AddMonthlyReportObjDetailRows(ref index, sheet, part.PartList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle);
                AddMonthlyReportObjDetailRows(ref index, sheet, part.AccList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle);
            });

            #endregion

            dcObj.Dispose(); dcObj = null;
            // Save the file
            book.Save(path);
        }
Exemplo n.º 6
0
        private void GenerateWorksheetSheet1(WorksheetCollection sheets, Order order)
        {
            Culture.InitializeCulture();
            Worksheet sheet = sheets.Add(Resource.Admin_ViewOrder_ItemNum + order.OrderID);

            sheet.Table.DefaultRowHeight    = 15F;
            sheet.Table.ExpandedColumnCount = 5;
            List <TaxValue> taxedItems = TaxServices.GetOrderTaxes(order.OrderID);

            sheet.Table.ExpandedRowCount = 40 + order.OrderItems.Count * 2 + taxedItems.Count;
            sheet.Table.FullColumns      = 1;
            sheet.Table.FullRows         = 1;
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            //column0.Width = 186; Commented By Evgeni to enlarge column width
            column0.Width = 250;
            column0.Span  = 1;
            WorksheetColumn column1 = sheet.Table.Columns.Add();

            column1.Index = 3;
            column1.Width = 156;
            sheet.Table.Columns.Add(120);
            sheet.Table.Columns.Add(89);
            // Order ID-----------------------------------------------
            WorksheetRow Row0 = sheet.Table.Rows.Add();

            Row0.Height = 22;
            WorksheetCell cell;

            cell             = Row0.Cells.Add();
            cell.StyleID     = "s132";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_ItemNum + order.OrderID;
            cell.MergeAcross = 4;
            // Status -----------------------------------------------
            WorksheetRow Row1 = sheet.Table.Rows.Add();

            cell             = Row1.Cells.Add();
            cell.StyleID     = "s133";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "(" + order.OrderStatus.StatusName + ")";
            cell.MergeAcross = 4;
            //  -----------------------------------------------
            WorksheetRow Row2 = sheet.Table.Rows.Add();

            cell             = Row2.Cells.Add();
            cell.StyleID     = "s134";
            cell.MergeAcross = 4;
            // Date -----------------------------------------------
            WorksheetRow Row3 = sheet.Table.Rows.Add();

            cell             = Row3.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_Date;
            cell             = Row3.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Culture.ConvertDate(order.OrderDate);
            cell.MergeAcross = 3;
            // NUmber to status check -----------------------------------------------
            WorksheetRow Row4 = sheet.Table.Rows.Add();

            cell             = Row4.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_Number;
            cell             = Row4.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.Number;
            cell.MergeAcross = 3;
            // Status comment -----------------------------------------------
            WorksheetRow Row5 = sheet.Table.Rows.Add();

            cell             = Row5.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_StatusComment;
            cell             = Row5.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.StatusComment;
            cell.MergeAcross = 3;
            // -----------------------------------------------
            WorksheetRow Row6 = sheet.Table.Rows.Add();

            cell             = Row6.Cells.Add();
            cell.StyleID     = "s136";
            cell.MergeAcross = 4;
            // Headers -----------------------------------------------
            WorksheetRow Row7 = sheet.Table.Rows.Add();

            Row7.Cells.Add(Resource.Admin_ViewOrder_Billing, DataType.String, "s70");
            Row7.Cells.Add(Resource.Admin_ViewOrder_Shipping, DataType.String, "s70");
            Row7.Cells.Add(Resource.Admin_ViewOrder_ShippingMethod, DataType.String, "s70");
            // Names -----------------------------------------------
            WorksheetRow Row8 = sheet.Table.Rows.Add();

            Row8.Cells.Add("     " + Resource.Admin_ViewOrder_Name + order.BillingContact.Name, DataType.String, "s70");

            Row8.Cells.Add("     " + Resource.Admin_ViewOrder_Name + order.ShippingContact.Name, DataType.String, "s70");
            var shippingMethodName = order.ArchivedShippingName;

            if (order.OrderPickPoint != null)
            {
                shippingMethodName += order.OrderPickPoint.PickPointAddress.Replace("<br/>", " ");
            }

            Row8.Cells.Add("     " + shippingMethodName, DataType.String, "s70");
            // Countries -----------------------------------------------
            WorksheetRow Row9 = sheet.Table.Rows.Add();

            Row9.Cells.Add("     " + Resource.Admin_ViewOrder_Country + order.BillingContact.Country, DataType.String,
                           "s70");
            Row9.Cells.Add("     " + Resource.Admin_ViewOrder_Country + order.ShippingContact.Country, DataType.String,
                           "s70");
            Row9.Cells.Add(Resource.Admin_ViewOrder_PaymentType, DataType.String, "s70");
            // Cities -----------------------------------------------
            WorksheetRow Row10 = sheet.Table.Rows.Add();

            Row10.Cells.Add("     " + Resource.Admin_ViewOrder_City + order.BillingContact.City, DataType.String, "s70");
            Row10.Cells.Add("     " + Resource.Admin_ViewOrder_City + order.ShippingContact.City, DataType.String, "s70");
            Row10.Cells.Add("     " + order.PaymentMethodName, DataType.String, "s70");
            // Zones -----------------------------------------------
            WorksheetRow Row11 = sheet.Table.Rows.Add();

            Row11.Cells.Add("     " + Resource.Admin_ViewOrder_Zone + order.BillingContact.Zone, DataType.String, "s70");
            Row11.Cells.Add("     " + Resource.Admin_ViewOrder_Zone + order.ShippingContact.Zone, DataType.String, "s70");
            cell         = Row11.Cells.Add();
            cell.StyleID = "s70";
            // Zips -----------------------------------------------
            WorksheetRow Row12 = sheet.Table.Rows.Add();

            Row12.Cells.Add("     " + Resource.Admin_ViewOrder_Zip + order.BillingContact.Zip, DataType.String, "s70");
            Row12.Cells.Add("     " + Resource.Admin_ViewOrder_Zip + order.ShippingContact.Zip, DataType.String, "s70");
            cell         = Row12.Cells.Add();
            cell.StyleID = "s70";
            // Adresses -----------------------------------------------
            WorksheetRow Row13 = sheet.Table.Rows.Add();

            Row13.Cells.Add("     " + Resource.Admin_ViewOrder_Address + order.BillingContact.Address, DataType.String,
                            "s70");
            Row13.Cells.Add("     " + Resource.Admin_ViewOrder_Address + order.ShippingContact.Address, DataType.String,
                            "s70");
            cell         = Row13.Cells.Add();
            cell.StyleID = "s70";

            // Added by Evgeni
            //Tel
            WorksheetRow Row14 = sheet.Table.Rows.Add();

            Row14.Cells.Add("     " + Resource.Client_OrderConfirmation_MobilePhone + " :" + order.OrderCustomer.MobilePhone, DataType.String,
                            "s70");
            Row14.Cells.Add("     " + Resource.Client_OrderConfirmation_MobilePhone + " :" + order.OrderCustomer.MobilePhone, DataType.String,
                            "s70");
            cell         = Row14.Cells.Add();
            cell.StyleID = "s70";

            //
            // -----------------------------------------------
            WorksheetRow Row17 = sheet.Table.Rows.Add();

            cell             = Row17.Cells.Add();
            cell.StyleID     = "s134";
            cell.MergeAcross = 4;
            // Orders -----------------------------------------------
            WorksheetRow Row18 = sheet.Table.Rows.Add();

            cell             = Row18.Cells.Add();
            cell.StyleID     = "s136";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_OrderItem;
            cell.MergeAcross = 4;
            // -----------------------------------------------
            WorksheetRow Row19 = sheet.Table.Rows.Add();

            Row19.Height     = 15;
            cell             = Row19.Cells.Add();
            cell.StyleID     = "s137";
            cell.MergeAcross = 4;
            // Order items table header -----------------------------------------------
            WorksheetRow Row20 = sheet.Table.Rows.Add();

            Row20.Height = 16;
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemName, DataType.String, "s89");
            Row20.Cells.Add(Resource.Admin_ViewOrder_CustomOptions, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_Price, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemAmount, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemCost, DataType.String, "s91");
            // Order items -----------------------------------------------
            foreach (OrderItem item in order.OrderItems)
            {
                WorksheetRow Row = sheet.Table.Rows.Add();
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494176";
                cell.Data.Type = DataType.String;
                //Changed by Evgeni to isert ArtNo in order print
                cell.Data.Text = item.Name + " [" + item.ArtNo + "]";
                //
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494196";
                var html = new StringBuilder();
                foreach (EvaluatedCustomOptions ev in item.SelectedOptions)
                {
                    html.Append(string.Format("- {0}: {1} \n", ev.CustomOptionTitle, ev.OptionTitle));
                }

                //Changed by Evgeni to isert ArtNo in order print
                cell.Data.Text = " " + item.ArtNo + " " + html.ToString();
                //
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494216";
                cell.Data.Type = DataType.String;

                bool     ciscodebefore;
                string   cpriceformat = null;
                string   csign        = null;
                Currency cur          = CurrencyService.Currency(order.OrderCurrency.CurrencyCode);
                ciscodebefore = cur.IsCodeBefore;
                cpriceformat  = cur.PriceFormat;
                csign         = cur.Symbol;

                cell.Data.Text = CatalogService.GetStringPrice(item.Price, order.OrderCurrency.CurrencyValue,
                                                               order.OrderCurrency.CurrencyCode);
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494236";
                cell.Data.Type = DataType.Number;
                cell.Data.Text = item.Amount.ToString();
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494256";
                cell.Data.Type = DataType.String;

                cell.Data.Text = CatalogService.GetStringPrice(item.Price * item.Amount,
                                                               order.OrderCurrency.CurrencyValue,
                                                               order.OrderCurrency.CurrencyCode);
                cell.MergeDown = 1;
                // -----------------------------------------------
                WorksheetRow RowSep = sheet.Table.Rows.Add();
                RowSep.Height = 15;
            }
            // -----------------------------------------------
            WorksheetRow Row27 = sheet.Table.Rows.Add();

            Row27.Height   = 15;
            cell           = Row27.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row27.Cells.Add();
            cell.StyleID   = "s70";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_ItemCost2;
            cell.Index     = 4;
            decimal orderPrice = ((order.Sum - order.ShippingCost) * 100) / (100 - order.OrderDiscount);

            Row27.Cells.Add(
                CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue,
                                              order.OrderCurrency.CurrencyCode), DataType.String, "s70");
            // -----------------------------------------------
            WorksheetRow Row28 = sheet.Table.Rows.Add();

            cell           = Row28.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row28.Cells.Add();
            cell.StyleID   = "s70";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount;
            cell.Index     = 4;


            decimal discount = orderPrice * (order.OrderDiscount / 100);

            Row28.Cells.Add(
                "-" +
                CatalogService.GetStringPrice(discount, order.OrderCurrency.CurrencyValue,
                                              order.OrderCurrency.CurrencyCode), DataType.String, "s70");
            // -----------------------------------------------
            WorksheetRow Row29 = sheet.Table.Rows.Add();

            cell           = Row29.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row29.Cells.Add();
            cell.StyleID   = "s70";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_ShippingPrice;
            cell.Index     = 4;

            Row29.Cells.Add(
                "+" +
                CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency.CurrencyValue,
                                              order.OrderCurrency.CurrencyCode), DataType.String, "s70");
            // -----------------------------------------------

            if (taxedItems.Count > 0)
            {
                foreach (TaxValue tax in taxedItems)
                {
                    WorksheetRow Row = sheet.Table.Rows.Add();
                    cell           = Row.Cells.Add();
                    cell.StyleID   = "s70";
                    cell           = Row.Cells.Add();
                    cell.StyleID   = "s70";
                    cell.Data.Type = DataType.String;
                    cell.Data.Text = (tax.TaxShowInPrice ? Resource.Core_TaxServices_Include_Tax + " " : "") +
                                     tax.TaxName + ":";
                    cell.Index = 4;
                    Row.Cells.Add(
                        (tax.TaxShowInPrice ? "" : "+") +
                        CatalogService.GetStringPrice(tax.TaxSum, order.OrderCurrency.CurrencyValue,
                                                      order.OrderCurrency.CurrencyCode), DataType.String, "s70");
                }
            }
            else
            {
                WorksheetRow Row30a = sheet.Table.Rows.Add();
                cell           = Row30a.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row30a.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_Taxes;
                cell.Index     = 4;

                Row30a.Cells.Add(
                    "+" +
                    CatalogService.GetStringPrice(0, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode),
                    DataType.String, "s70");
            }
            // -----------------------------------------------
            WorksheetRow Row30 = sheet.Table.Rows.Add();

            cell           = Row30.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row30.Cells.Add();
            cell.StyleID   = "s135";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_TotalPrice;
            cell.Index     = 4;

            cell =
                Row30.Cells.Add(
                    CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue,
                                                  order.OrderCurrency.CurrencyCode), DataType.String, "s135");

            // -----------------------------------------------
            WorksheetRow Row31 = sheet.Table.Rows.Add();

            cell             = Row31.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Client_PrintOrder_YourComment;
            cell.MergeAcross = 4;
            // -----------------------------------------------
            WorksheetRow Row32 = sheet.Table.Rows.Add();

            cell             = Row32.Cells.Add();
            cell.StyleID     = "s141";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.CustomerComment;
            cell.MergeAcross = 4;
            cell.MergeDown   = 1;
            // -----------------------------------------------
            //  Options
            // -----------------------------------------------
            sheet.Options.Selected                     = true;
            sheet.Options.ProtectObjects               = false;
            sheet.Options.ProtectScenarios             = false;
            sheet.Options.PageSetup.Header.Margin      = 0.3F;
            sheet.Options.PageSetup.Footer.Margin      = 0.3F;
            sheet.Options.PageSetup.PageMargins.Bottom = 0.75F;
            sheet.Options.PageSetup.PageMargins.Left   = 0.7F;
            sheet.Options.PageSetup.PageMargins.Right  = 0.7F;
            sheet.Options.PageSetup.PageMargins.Top    = 0.75F;
            sheet.Options.Print.PaperSizeIndex         = 9;
            sheet.Options.Print.VerticalResolution     = 0;
            sheet.Options.Print.ValidPrinterInfo       = true;
        }
        /// //////////////////////////////////////////////
        public CResultAErreur Export(DataSet ds, IDestinationExport destination)
        {
            CResultAErreur result = CResultAErreur.True;

            if (!(destination is CDestinationExportFile))
            {
                result.EmpileErreur(I.T("An Excel export can be done only in a file|171"));
                return(result);
            }
            string strNomFichier = ((CDestinationExportFile)destination).FileName;

            try
            {
                ds.EnforceConstraints = false;

                if (File.Exists(strNomFichier))
                {
                    File.Delete(strNomFichier);
                }


                Workbook book = new Workbook();

                WorksheetStyle style = book.Styles.Add("SC2I_DATE");
                style.NumberFormat = I.T("Short DATE|170");

                //Nom de table->true : pour éviter 2 fois le même nom de table
                Hashtable tableTablesCrees   = new Hashtable();
                Hashtable tableColumnsToHide = new Hashtable();
                Regex     colKeyAuto         = new Regex("^Key_");
                if (m_bMasquerIdsAuto)
                {
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataColumn col in table.PrimaryKey)
                        {
                            if (col.AutoIncrement || colKeyAuto.IsMatch(col.ColumnName))
                            {
                                tableColumnsToHide[col] = true;
                            }
                        }
                    }
                    foreach (DataRelation rel in ds.Relations)
                    {
                        if (rel.ParentColumns.Length == 1 &&
                            tableColumnsToHide.Contains(rel.ParentColumns[0]) &&
                            rel.ChildColumns.Length == 1)
                        {
                            tableColumnsToHide[rel.ChildColumns[0]] = true;
                        }
                    }
                }
                foreach (DataTable table in ds.Tables)
                {
                    Worksheet sheet = book.Worksheets.Add(table.TableName);

                    //Création des colonnes
                    WorksheetRow row = sheet.Table.Rows.Add( );

                    DataType[] typeCol = new DataType[table.Columns.Count];

                    int nCol = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        if (!tableColumnsToHide.Contains(col))
                        {
                            WorksheetColumn colSheet = sheet.Table.Columns.Add( );
                            DataType        tp       = GetExcelType(col.DataType);
                            typeCol[nCol] = tp;
                            nCol++;
                            row.Cells.Add(ConvertName(col.ColumnName));
                            if (tp == DataType.DateTime)
                            {
                                colSheet.StyleID = "SC2I_DATE";
                            }
                        }
                    }

                    if (!m_bOnlyStructure)
                    {
                        //Insertion des données
                        foreach (DataRow rowNet in table.Rows)
                        {
                            row  = sheet.Table.Rows.Add();
                            nCol = 0;
                            foreach (DataColumn col in table.Columns)
                            {
                                if (!tableColumnsToHide.Contains(col))
                                {
                                    object val = rowNet[col];
                                    if (val == DBNull.Value)
                                    {
                                        row.Cells.Add();
                                    }
                                    else
                                    {
                                        string strVal = "";
                                        if (typeCol[nCol] == DataType.DateTime)
                                        {
                                            if (val is CDateTimeEx)
                                            {
                                                val = ((CDateTimeEx)val).DateTimeValue;
                                            }
                                            strVal = ((DateTime)val).ToString("yyyy-MM-ddTHH:mm:ss");
                                        }
                                        else
                                        {
                                            strVal = val.ToString();
                                        }
                                        row.Cells.Add(strVal, typeCol[nCol], null);
                                    }
                                    nCol++;
                                }
                            }
                        }
                    }
                }
                book.Save(strNomFichier);
            }
            catch (Exception e)
            {
                result.EmpileErreur(new CErreurException(e));
                return(result);
            }
            finally
            {
                ds.EnforceConstraints = true;
            }
            return(result);
        }
Exemplo n.º 8
0
        //public static Workbook GenerateExcel(List<ExcelColumn> columns, DataSet data, bool generateHeader)
        //{
        //  Workbook book = new Workbook();
        //  Worksheet sheet = book.Worksheets.Add("Assortment");

        //  WorksheetRow row;

        //  GenerateStyles(book.Styles);

        //  foreach (var name in columns)
        //  {
        //    WorksheetColumn column = new WorksheetColumn();
        //    column.AutoFitWidth = true;

        //    sheet.Table.Columns.Add(column);
        //  }

        //  int rowIndex = GenerateHeaders(sheet, generateHeader, columns);

        //  Dictionary<int, Dictionary<string, string>> excelRows = new Dictionary<int, Dictionary<string, string>>();

        //  foreach (var productRow in data.Tables[0].AsEnumerable())
        //  {
        //    rowIndex++;
        //    var product = productRow;
        //    Dictionary<string, string> excelvalues = new Dictionary<string, string>();

        //    foreach (var att in data.Tables[0].AsEnumerable())
        //    {
        //      if (excelvalues.ContainsKey(att.Field<string>))
        //        excelvalues[att.Name.LocalName] = att.Value;
        //      else
        //        excelvalues.Add(att.Name.LocalName, att.Value);
        //    }

        //    foreach (XElement el in product.Elements())
        //    {
        //      if (columns.Any(x => x.ValueFieldName == el.Name.LocalName))
        //      {
        //        if (excelvalues.ContainsKey(el.Name.LocalName))
        //          excelvalues[el.Name.LocalName] = el.Value;
        //        else
        //          excelvalues.Add(el.Name.LocalName, el.Value);
        //      }

        //      foreach (XAttribute att in el.Attributes())
        //      {
        //        if (columns.Any(x => x.ValueFieldName == att.Name.LocalName))
        //        {
        //          if (excelvalues.ContainsKey(att.Name.LocalName))
        //            excelvalues[att.Name.LocalName] = att.Value;
        //          else
        //            excelvalues.Add(att.Name.LocalName, att.Value);

        //        }
        //      }
        //    }
        //    excelRows.Add(rowIndex, excelvalues);
        //  }

        //  foreach (var val in excelRows.Keys)
        //  {
        //    row = sheet.Table.Rows.Add();

        //    foreach (var key in columns)
        //    {
        //      row.Cells.Add(excelRows[val][key.ColumnName]);
        //    }
        //  }

        //  return book;
        //}

        public static Workbook GenerateExcel(int connectorID, List <ExcelColumn> columns, bool generateHeader)
        {
            AssortmentServiceSoapClient client = new AssortmentServiceSoapClient();



            var       assortmentSource = new XDocument(client.GetAssortment(1, null, true));
            Workbook  book             = new Workbook();
            Worksheet sheet            = book.Worksheets.Add("Assortment");

            WorksheetRow row;

            GenerateStyles(book.Styles);

            foreach (var name in columns)
            {
                WorksheetColumn column = new WorksheetColumn();
                column.AutoFitWidth = true;

                sheet.Table.Columns.Add(column);
            }

            int rowIndex = GenerateHeaders(sheet, generateHeader, columns);

            Dictionary <int, Dictionary <string, string> > excelRows = new Dictionary <int, Dictionary <string, string> >();

            foreach (var productRow in assortmentSource.Root.Elements("Product"))
            {
                rowIndex++;
                var product = productRow;
                Dictionary <string, string> excelvalues = new Dictionary <string, string>();

                foreach (XAttribute att in product.Attributes())
                {
                    if (columns.Any(x => x.ValueFieldName == att.Name.LocalName))
                    {
                        if (excelvalues.ContainsKey(att.Name.LocalName))
                        {
                            excelvalues[att.Name.LocalName] = att.Value;
                        }
                        else
                        {
                            excelvalues.Add(att.Name.LocalName, att.Value);
                        }
                    }
                }

                foreach (XElement el in product.Elements())
                {
                    if (columns.Any(x => x.ValueFieldName == el.Name.LocalName))
                    {
                        if (excelvalues.ContainsKey(el.Name.LocalName))
                        {
                            excelvalues[el.Name.LocalName] = el.Value;
                        }
                        else
                        {
                            excelvalues.Add(el.Name.LocalName, el.Value);
                        }
                    }

                    foreach (XAttribute att in el.Attributes())
                    {
                        if (columns.Any(x => x.ValueFieldName == att.Name.LocalName))
                        {
                            if (excelvalues.ContainsKey(att.Name.LocalName))
                            {
                                excelvalues[att.Name.LocalName] = att.Value;
                            }
                            else
                            {
                                excelvalues.Add(att.Name.LocalName, att.Value);
                            }
                        }
                    }
                }
                excelRows.Add(rowIndex, excelvalues);
            }

            foreach (var val in excelRows.Keys)
            {
                row = sheet.Table.Rows.Add();

                foreach (var key in columns)
                {
                    row.Cells.Add(excelRows[val][key.ColumnName]);
                }
            }

            return(book);
        }
Exemplo n.º 9
0
        private void GenerateWorksheetSheet1(WorksheetCollection sheets, Order order)
        {
            Culture.InitializeCulture();
            Worksheet sheet = sheets.Add(Resource.Admin_ViewOrder_ItemNum + order.OrderID);

            sheet.Table.DefaultRowHeight    = 15F;
            sheet.Table.ExpandedColumnCount = 5;
            List <OrderTax> taxedItems = TaxServices.GetOrderTaxes(order.OrderID);

            sheet.Table.ExpandedRowCount = 42 + order.OrderItems.Count * 2 + taxedItems.Count;
            sheet.Table.FullColumns      = 1;
            sheet.Table.FullRows         = 1;
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            column0.Width = 186;
            column0.Span  = 1;
            WorksheetColumn column1 = sheet.Table.Columns.Add();

            column1.Index = 3;
            column1.Width = 156;
            sheet.Table.Columns.Add(120);
            sheet.Table.Columns.Add(89);
            // Order ID-----------------------------------------------
            WorksheetRow Row0 = sheet.Table.Rows.Add();

            Row0.Height = 22;
            WorksheetCell cell;

            cell             = Row0.Cells.Add();
            cell.StyleID     = "s132";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_ItemNum + order.OrderID;
            cell.MergeAcross = 4;
            // Status -----------------------------------------------
            WorksheetRow Row1 = sheet.Table.Rows.Add();

            cell             = Row1.Cells.Add();
            cell.StyleID     = "s133";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "(" + order.OrderStatus.StatusName + ")";
            cell.MergeAcross = 4;
            //  -----------------------------------------------
            WorksheetRow Row2 = sheet.Table.Rows.Add();

            cell             = Row2.Cells.Add();
            cell.StyleID     = "s134";
            cell.MergeAcross = 4;
            // Date -----------------------------------------------
            WorksheetRow Row3 = sheet.Table.Rows.Add();

            cell             = Row3.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_Date;
            cell             = Row3.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Culture.ConvertDate(order.OrderDate);
            cell.MergeAcross = 3;
            // NUmber to status check -----------------------------------------------
            WorksheetRow Row4 = sheet.Table.Rows.Add();

            cell             = Row4.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_Number;
            cell             = Row4.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.Number;
            cell.MergeAcross = 3;
            // Status comment -----------------------------------------------
            WorksheetRow Row5 = sheet.Table.Rows.Add();

            cell             = Row5.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_StatusComment;
            cell             = Row5.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.StatusComment;
            cell.MergeAcross = 3;


            WorksheetRow RowEmail = sheet.Table.Rows.Add();

            cell             = RowEmail.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewCustomer_Email;
            cell             = RowEmail.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.OrderCustomer.Email;
            cell.MergeAcross = 3;

            WorksheetRow RowPhone = sheet.Table.Rows.Add();

            cell             = RowPhone.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_CommonSettings_Phone;
            cell             = RowPhone.Cells.Add();
            cell.StyleID     = "s70";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.OrderCustomer.MobilePhone;
            cell.MergeAcross = 3;

            // -----------------------------------------------
            WorksheetRow Row6 = sheet.Table.Rows.Add();

            cell             = Row6.Cells.Add();
            cell.StyleID     = "s136";
            cell.MergeAcross = 4;
            // Headers -----------------------------------------------
            WorksheetRow Row7 = sheet.Table.Rows.Add();

            Row7.Cells.Add(Resource.Admin_ViewOrder_Billing, DataType.String, "s70");
            Row7.Cells.Add(Resource.Admin_ViewOrder_Shipping, DataType.String, "s70");
            Row7.Cells.Add(Resource.Admin_ViewOrder_ShippingMethod, DataType.String, "s70");
            // Names -----------------------------------------------
            WorksheetRow Row8 = sheet.Table.Rows.Add();

            Row8.Cells.Add("���� " + Resource.Admin_ViewOrder_Name + order.BillingContact.Name, DataType.String, "s70");

            Row8.Cells.Add("���� " + Resource.Admin_ViewOrder_Name + order.ShippingContact.Name, DataType.String, "s70");
            var shippingMethodName = order.ArchivedShippingName;

            if (order.OrderPickPoint != null)
            {
                shippingMethodName += order.OrderPickPoint.PickPointAddress.Replace("<br/>", " ");
            }

            Row8.Cells.Add("���� " + shippingMethodName, DataType.String, "s70");
            // Countries -----------------------------------------------
            WorksheetRow Row9 = sheet.Table.Rows.Add();

            Row9.Cells.Add("�����" + Resource.Admin_ViewOrder_Country + order.BillingContact.Country, DataType.String,
                           "s70");
            Row9.Cells.Add("�����" + Resource.Admin_ViewOrder_Country + order.ShippingContact.Country, DataType.String,
                           "s70");
            Row9.Cells.Add(Resource.Admin_ViewOrder_PaymentType, DataType.String, "s70");
            // Cities -----------------------------------------------
            WorksheetRow Row10 = sheet.Table.Rows.Add();

            Row10.Cells.Add("�����" + Resource.Admin_ViewOrder_City + order.BillingContact.City, DataType.String, "s70");
            Row10.Cells.Add("�����" + Resource.Admin_ViewOrder_City + order.ShippingContact.City, DataType.String, "s70");
            Row10.Cells.Add("���� " + order.PaymentMethodName, DataType.String, "s70");
            // Zones -----------------------------------------------
            WorksheetRow Row11 = sheet.Table.Rows.Add();

            Row11.Cells.Add("�����" + Resource.Admin_ViewOrder_Zone + order.BillingContact.Zone, DataType.String, "s70");
            Row11.Cells.Add("�����" + Resource.Admin_ViewOrder_Zone + order.ShippingContact.Zone, DataType.String, "s70");
            cell         = Row11.Cells.Add();
            cell.StyleID = "s70";
            // Zips -----------------------------------------------
            WorksheetRow Row12 = sheet.Table.Rows.Add();

            Row12.Cells.Add("�����" + Resource.Admin_ViewOrder_Zip + order.BillingContact.Zip, DataType.String, "s70");
            Row12.Cells.Add("�����" + Resource.Admin_ViewOrder_Zip + order.ShippingContact.Zip, DataType.String, "s70");
            cell         = Row12.Cells.Add();
            cell.StyleID = "s70";
            // Adresses -----------------------------------------------
            WorksheetRow Row13 = sheet.Table.Rows.Add();

            Row13.Cells.Add("�����" + Resource.Admin_ViewOrder_Address + order.BillingContact.Address, DataType.String,
                            "s70");
            Row13.Cells.Add("�����" + Resource.Admin_ViewOrder_Address + order.ShippingContact.Address, DataType.String,
                            "s70");
            cell         = Row13.Cells.Add();
            cell.StyleID = "s70";
            // -----------------------------------------------
            WorksheetRow Row17 = sheet.Table.Rows.Add();

            cell             = Row17.Cells.Add();
            cell.StyleID     = "s134";
            cell.MergeAcross = 4;
            // Orders -----------------------------------------------
            WorksheetRow Row18 = sheet.Table.Rows.Add();

            cell             = Row18.Cells.Add();
            cell.StyleID     = "s136";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Admin_ViewOrder_OrderItem;
            cell.MergeAcross = 4;
            // -----------------------------------------------
            WorksheetRow Row19 = sheet.Table.Rows.Add();

            Row19.Height     = 15;
            cell             = Row19.Cells.Add();
            cell.StyleID     = "s137";
            cell.MergeAcross = 4;
            // Order items table header -----------------------------------------------
            WorksheetRow Row20 = sheet.Table.Rows.Add();

            Row20.Height = 16;
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemName, DataType.String, "s89");
            Row20.Cells.Add(Resource.Admin_ViewOrder_CustomOptions, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_Price, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemAmount, DataType.String, "s90");
            Row20.Cells.Add(Resource.Admin_ViewOrder_ItemCost, DataType.String, "s91");
            // Order items -----------------------------------------------
            foreach (OrderItem item in order.OrderItems)
            {
                WorksheetRow Row = sheet.Table.Rows.Add();
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494176";
                cell.Data.Type = DataType.String;
                cell.Data.Text = item.ArtNo + ", " + item.Name;
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494196";
                var html = new StringBuilder();

                if (!string.IsNullOrEmpty(item.Color))
                {
                    html.Append(Configuration.SettingsCatalog.ColorsHeader + ": " + item.Color + " \n");
                }

                if (!string.IsNullOrEmpty(item.Size))
                {
                    html.Append(Configuration.SettingsCatalog.SizesHeader + ": " + item.Size + " \n");
                }

                foreach (EvaluatedCustomOptions ev in item.SelectedOptions)
                {
                    html.Append(string.Format("- {0}: {1} \n", ev.CustomOptionTitle, ev.OptionTitle));
                }

                cell.Data.Text = html.ToString();
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494216";
                cell.Data.Type = DataType.String;

                cell.Data.Text = CatalogService.GetStringPrice(item.Price, order.OrderCurrency.CurrencyValue,
                                                               order.OrderCurrency.CurrencyCode);
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494236";
                cell.Data.Type = DataType.String;
                cell.Data.Text = item.Amount.ToString();
                cell.MergeDown = 1;
                cell           = Row.Cells.Add();
                cell.StyleID   = "m51494256";
                cell.Data.Type = DataType.String;

                cell.Data.Text = CatalogService.GetStringPrice(item.Price * item.Amount,
                                                               order.OrderCurrency.CurrencyValue,
                                                               order.OrderCurrency.CurrencyCode);
                cell.MergeDown = 1;
                // -----------------------------------------------
                WorksheetRow RowSep = sheet.Table.Rows.Add();
                RowSep.Height = 15;
            }
            // -----------------------------------------------
            WorksheetRow Row27 = sheet.Table.Rows.Add();

            Row27.Height   = 15;
            cell           = Row27.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row27.Cells.Add();
            cell.StyleID   = "s70";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_ItemCost2;
            cell.Index     = 4;

            var   ordCurrency  = order.OrderCurrency;
            float productPrice = order.OrderItems.Sum(item => item.Amount * item.Price);

            Row27.Cells.Add(
                CatalogService.GetStringPrice(productPrice, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode),
                DataType.String, "s70");


            //-----------------------------------------------

            if (order.Coupon != null)
            {
                WorksheetRow Row28 = sheet.Table.Rows.Add();
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_Coupon;
                cell.Index     = 4;

                var productsWithCoupon = order.OrderItems.Where(item => item.IsCouponApplied).Sum(item => item.Price * item.Amount);

                switch (order.Coupon.Type)
                {
                case CouponType.Fixed:
                    Row28.Cells.Add(String.Format("-{0} ({1})",
                                                  CatalogService.GetStringPrice(order.Coupon.Value, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode),
                                                  order.Coupon.Code), DataType.String, "s70");
                    break;

                case CouponType.Percent:
                    Row28.Cells.Add(String.Format("-{0} ({1}%) ({2})",
                                                  CatalogService.GetStringPrice(productsWithCoupon * order.Coupon.Value / 100, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode),
                                                  CatalogService.FormatPriceInvariant(order.Coupon.Value),
                                                  order.Coupon.Code), DataType.String, "s70");
                    break;
                }
            }

            // -----------------------------------------------
            float totalDiscount = order.OrderDiscount;

            if (totalDiscount > 0)
            {
                WorksheetRow Row28 = sheet.Table.Rows.Add();
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount;
                cell.Index     = 4;

                Row28.Cells.Add(
                    "-" +
                    CatalogService.GetStringDiscountPercent(productPrice, totalDiscount,
                                                            ordCurrency.CurrencyValue, ordCurrency.CurrencySymbol,
                                                            ordCurrency.IsCodeBefore,
                                                            CurrencyService.CurrentCurrency.PriceFormat, false),
                    DataType.String, "s70");
            }

            // -------------------------------------------------------

            if (order.Certificate != null)
            {
                WorksheetRow Row28 = sheet.Table.Rows.Add();
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row28.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount;
                cell.Index     = 4;

                Row28.Cells.Add(
                    "-" +
                    string.Format("-{0}", CatalogService.GetStringPrice(order.Certificate.Price, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode)),
                    DataType.String, "s70");
            }

            // -----------------------------------------------

            float bonusPrice = order.BonusCost;

            if (bonusPrice > 0)
            {
                WorksheetRow Row28_5 = sheet.Table.Rows.Add();
                cell           = Row28_5.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row28_5.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_Bonuses;
                cell.Index     = 4;

                Row28_5.Cells.Add("-" + CatalogService.GetStringPrice(bonusPrice), DataType.String, "s70");
            }
            // -----------------------------------------------
            WorksheetRow Row29 = sheet.Table.Rows.Add();

            cell           = Row29.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row29.Cells.Add();
            cell.StyleID   = "s70";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_ShippingPrice;
            cell.Index     = 4;

            Row29.Cells.Add(
                "+" +
                CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency.CurrencyValue,
                                              order.OrderCurrency.CurrencyCode), DataType.String, "s70");

            if (taxedItems.Count > 0)
            {
                foreach (OrderTax tax in taxedItems)
                {
                    WorksheetRow Row = sheet.Table.Rows.Add();
                    cell           = Row.Cells.Add();
                    cell.StyleID   = "s70";
                    cell           = Row.Cells.Add();
                    cell.StyleID   = "s70";
                    cell.Data.Type = DataType.String;
                    cell.Data.Text = (tax.TaxShowInPrice ? Resource.Core_TaxServices_Include_Tax + " " : "") +
                                     tax.TaxName + ":";
                    cell.Index = 4;
                    Row.Cells.Add(
                        (tax.TaxShowInPrice ? "" : "+") +
                        CatalogService.GetStringPrice(tax.TaxSum, order.OrderCurrency.CurrencyValue,
                                                      order.OrderCurrency.CurrencyCode), DataType.String, "s70");
                }
            }
            else
            {
                WorksheetRow Row30a = sheet.Table.Rows.Add();
                cell           = Row30a.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row30a.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_Taxes;
                cell.Index     = 4;

                Row30a.Cells.Add(
                    "+" +
                    CatalogService.GetStringPrice(0, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode),
                    DataType.String, "s70");
            }
            // -----------------------------------------------

            if (order.PaymentCost > 0)
            {
                WorksheetRow Row291 = sheet.Table.Rows.Add();
                cell           = Row291.Cells.Add();
                cell.StyleID   = "s70";
                cell           = Row291.Cells.Add();
                cell.StyleID   = "s70";
                cell.Data.Type = DataType.String;
                cell.Data.Text = Resource.Admin_ViewOrder_PaymentExtracharge;
                cell.Index     = 4;

                Row291.Cells.Add(
                    "+" +
                    CatalogService.GetStringPrice(order.PaymentCost, order.OrderCurrency.CurrencyValue,
                                                  order.OrderCurrency.CurrencyCode), DataType.String, "s70");
            }

            // -----------------------------------------------

            WorksheetRow Row30 = sheet.Table.Rows.Add();

            cell           = Row30.Cells.Add();
            cell.StyleID   = "s70";
            cell           = Row30.Cells.Add();
            cell.StyleID   = "s135";
            cell.Data.Type = DataType.String;
            cell.Data.Text = Resource.Admin_ViewOrder_TotalPrice;
            cell.Index     = 4;

            cell =
                Row30.Cells.Add(
                    CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue,
                                                  order.OrderCurrency.CurrencyCode), DataType.String, "s135");

            // -----------------------------------------------
            WorksheetRow Row31 = sheet.Table.Rows.Add();

            cell             = Row31.Cells.Add();
            cell.StyleID     = "s135";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = Resource.Client_PrintOrder_YourComment;
            cell.MergeAcross = 4;
            // -----------------------------------------------
            WorksheetRow Row32 = sheet.Table.Rows.Add();

            cell             = Row32.Cells.Add();
            cell.StyleID     = "s141";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = order.CustomerComment;
            cell.MergeAcross = 4;
            cell.MergeDown   = 1;
            // -----------------------------------------------
            //  Options
            // -----------------------------------------------
            sheet.Options.Selected                     = true;
            sheet.Options.ProtectObjects               = false;
            sheet.Options.ProtectScenarios             = false;
            sheet.Options.PageSetup.Header.Margin      = 0.3F;
            sheet.Options.PageSetup.Footer.Margin      = 0.3F;
            sheet.Options.PageSetup.PageMargins.Bottom = 0.75F;
            sheet.Options.PageSetup.PageMargins.Left   = 0.7F;
            sheet.Options.PageSetup.PageMargins.Right  = 0.7F;
            sheet.Options.PageSetup.PageMargins.Top    = 0.75F;
            sheet.Options.Print.PaperSizeIndex         = 9;
            sheet.Options.Print.VerticalResolution     = 0;
            sheet.Options.Print.ValidPrinterInfo       = true;
        }
Exemplo n.º 10
0
        private void GenerateWorksheet(WorksheetCollection sheets, DateTime dttungay, DateTime dtdenngay, DataTable dt, ProgressBar progressBar)
        {
            Worksheet sheet = sheets.Add("TKDoanhThu");

            sheet.Table.DefaultRowHeight    = 15F;
            sheet.Table.ExpandedColumnCount = 10;
            sheet.Table.ExpandedRowCount    = 8 + dt.Rows.Count;
            sheet.Table.FullColumns         = 1;
            sheet.Table.FullRows            = 1;
            WorksheetColumn column0 = sheet.Table.Columns.Add();

            column0.Width   = 24;
            column0.StyleID = "s16";
            WorksheetColumn column1 = sheet.Table.Columns.Add();

            column1.Width   = 96;
            column1.StyleID = "s16";
            sheet.Table.Columns.Add(84);
            sheet.Table.Columns.Add(81);
            sheet.Table.Columns.Add(65);
            sheet.Table.Columns.Add(96);
            WorksheetColumn column6 = sheet.Table.Columns.Add();

            column6.Width = 97;
            column6.Span  = 1;
            WorksheetColumn column7 = sheet.Table.Columns.Add();

            column7.Index = 9;
            column7.Width = 75;
            sheet.Table.Columns.Add(144);
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            // -----------------------------------------------
            #region Add header
            WorksheetRow Row0 = sheet.Table.Rows.Add();
            Row0.Height = 23;
            WorksheetCell cell;
            cell             = Row0.Cells.Add();
            cell.StyleID     = "s110";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "TỔNG HỢP DOANH THU";
            cell.MergeAcross = 8;
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            // -----------------------------------------------
            WorksheetRow Row1 = sheet.Table.Rows.Add();
            cell             = Row1.Cells.Add();
            cell.StyleID     = "s111";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "(Từ ngày " + dttungay.ToString("dd/MM/yyyy") + " đến ngày " + dtdenngay.ToString("dd/MM/yyyy") + ")";
            cell.MergeAcross = 8;
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            // -----------------------------------------------
            WorksheetRow Row2 = sheet.Table.Rows.Add();
            Row2.AutoFitHeight = false;
            cell             = Row2.Cells.Add();
            cell.StyleID     = "s112";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Đơn vị tính: VNĐ";
            cell.MergeAcross = 8;
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            // -----------------------------------------------
            WorksheetRow Row3 = sheet.Table.Rows.Add();
            Row3.Cells.Add("Stt", DataType.String, "s19");
            Row3.Cells.Add("Ngày xuất hóa đơn", DataType.String, "s19");
            Row3.Cells.Add("Tên khách hàng", DataType.String, "s19");
            Row3.Cells.Add("Tổng tiền nhập hàng/Hóa đơn", DataType.String, "s19");
            Row3.Cells.Add("Tổng tiền/Hóa đơn", DataType.String, "s19");
            Row3.Cells.Add("Lợi nhuận/Hóa đơn", DataType.String, "s19");
            Row3.Cells.Add("Tiền đã thanh toán", DataType.String, "s19");
            Row3.Cells.Add("Chiết khấu/Hóa đơn", DataType.String, "s19");
            Row3.Cells.Add("Còn nợ/Hóa đơn", DataType.String, "s19");
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            #endregion
            // -----------------------------------------------
            #region Add Content
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Application.DoEvents();
                progressBar.PerformStep();
                Application.DoEvents();
                WorksheetRow row = sheet.Table.Rows.Add();
                row.Cells.Add((i + 1).ToString(), DataType.Number, "s113");
                DateTime dttemp = Convert.ToDateTime(dt.Rows[i]["ngaytao"].ToString().Trim());
                row.Cells.Add(dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim(), DataType.String, "s114");
                row.Cells.Add(dt.Rows[i]["tenkh"].ToString().Trim(), DataType.String, "s114");
                row.Cells.Add(dt.Rows[i]["gianhap"].ToString().Trim(), DataType.Number, "s115");
                row.Cells.Add(dt.Rows[i]["tongtien"].ToString().Trim(), DataType.Number, "s115");
                cell           = row.Cells.Add();
                cell.StyleID   = "s115";
                cell.Data.Type = DataType.Number;
                cell.Formula   = "=RC[-1]-RC[-2]-RC[2]";
                row.Cells.Add(dt.Rows[i]["tiendathanhtoan"].ToString().Trim(), DataType.Number, "s115");
                row.Cells.Add(dt.Rows[i]["chietkhau"].ToString().Trim(), DataType.Number, "s115");
                cell           = row.Cells.Add();
                cell.StyleID   = "s115";
                cell.Data.Type = DataType.Number;
                cell.Formula   = "=RC[-4]-RC[-2]-RC[-1]";
            }
            #endregion
            // -----------------------------------------------
            #region Add Tính toán (Sum)
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row8 = sheet.Table.Rows.Add();
            Row8.AutoFitHeight = false;
            cell             = Row8.Cells.Add();
            cell.StyleID     = "m92103616";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng doanh thu";
            cell.MergeAcross = 7;
            cell             = Row8.Cells.Add();
            cell.StyleID     = "s116";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + (dt.Rows.Count).ToString() + "]C[-4]:R[-1]C[-4])";
            // -----------------------------------------------
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row9 = sheet.Table.Rows.Add();
            Row9.AutoFitHeight = false;
            cell             = Row9.Cells.Add();
            cell.StyleID     = "m92103676";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng lợi nhuận";
            cell.MergeAcross = 7;
            cell             = Row9.Cells.Add();
            cell.StyleID     = "s117";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + (dt.Rows.Count + 1).ToString() + "]C[-3]:R[-2]C[-3])";
            // -----------------------------------------------
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row10 = sheet.Table.Rows.Add();
            Row10.AutoFitHeight = false;
            cell             = Row10.Cells.Add();
            cell.StyleID     = "m92103636";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng tiền đã thanh toán";
            cell.MergeAcross = 7;
            cell             = Row10.Cells.Add();
            cell.StyleID     = "s118";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + (dt.Rows.Count + 2).ToString() + "]C[-2]:R[-3]C[-2]) + SUM(R[-" + (dt.Rows.Count + 2).ToString() + "]C[-1]:R[-3]C[-1])";
            // -----------------------------------------------
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
            WorksheetRow Row11 = sheet.Table.Rows.Add();
            Row11.AutoFitHeight = false;
            cell             = Row11.Cells.Add();
            cell.StyleID     = "m92103656";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Tổng tiền còn nợ";
            cell.MergeAcross = 7;
            cell             = Row11.Cells.Add();
            cell.StyleID     = "s119";
            cell.Data.Type   = DataType.Number;
            cell.Formula     = "=SUM(R[-" + (dt.Rows.Count + 3).ToString() + "]C:R[-4]C)";
            #endregion
            // -----------------------------------------------
            //  Options
            // -----------------------------------------------
            sheet.Options.Selected                     = true;
            sheet.Options.ProtectObjects               = false;
            sheet.Options.ProtectScenarios             = false;
            sheet.Options.PageSetup.Header.Margin      = 0.3F;
            sheet.Options.PageSetup.Footer.Margin      = 0.3F;
            sheet.Options.PageSetup.PageMargins.Bottom = 0.75F;
            sheet.Options.PageSetup.PageMargins.Left   = 0.7F;
            sheet.Options.PageSetup.PageMargins.Right  = 0.7F;
            sheet.Options.PageSetup.PageMargins.Top    = 0.75F;
            sheet.Options.Print.HorizontalResolution   = 300;
            sheet.Options.Print.VerticalResolution     = 0;
            sheet.Options.Print.ValidPrinterInfo       = true;
            Application.DoEvents();
            progressBar.PerformStep();
            Application.DoEvents();
        }
Exemplo n.º 11
0
        /// <summary>
        /// Formatiere das Dokument
        /// </summary>
        /// <param name="sheets"></param>
        private void GenerateWorksheetAbhackeListe(WorksheetCollection sheets)
        {
            Worksheet sheet = sheets.Add(this._SheetName + "   vom   " + DateTime.Now.ToLongDateString());

            this._Sheet         = sheet;
            sheet.Table.StyleID = "ta1";
            WorksheetColumn column0 = sheet.Table.Columns.Add(58);
            WorksheetColumn column1 = sheet.Table.Columns.Add(60);
            WorksheetColumn column2 = sheet.Table.Columns.Add(48);
            WorksheetColumn column3 = sheet.Table.Columns.Add();

            column3.Width = 120;
            WorksheetColumn column44 = sheet.Table.Columns.Add();

            column44.Width = 80;
            WorksheetColumn column4 = sheet.Table.Columns.Add();

            column4.Width = 64;
            WorksheetColumn column7 = sheet.Table.Columns.Add();

            column7.Width = 20;
            WorksheetColumn column8 = sheet.Table.Columns.Add();

            column8.Width = 20;
            WorksheetColumn column9 = sheet.Table.Columns.Add();

            column9.Width = 20;
            WorksheetColumn column10 = sheet.Table.Columns.Add();

            column10.Width = 20;
            WorksheetColumn column11 = sheet.Table.Columns.Add();

            column11.Width = 20;
            WorksheetColumn column12 = sheet.Table.Columns.Add();

            column12.Width = 20;
            WorksheetColumn column13 = sheet.Table.Columns.Add();

            column13.Width = 170;

            // Generierung Daten Zellen
            WorksheetRow Row0 = sheet.Table.Rows.Add();

            Row0.Height        = 18;
            Row0.AutoFitHeight = true;
            WorksheetCell cell;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce1";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Ausweis Nummer";
            cell.MergeDown = 1;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce1";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Personen Haushalt";
            cell.MergeDown = 1;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce1";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Gruppe";
            cell.MergeDown = 1;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce1";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Name";
            cell.MergeDown = 1;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce1";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Ort";
            cell.MergeDown = 1;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce6";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Ausweis gültig bis";
            cell.MergeDown = 1;

            cell             = Row0.Cells.Add();
            cell.StyleID     = "ce8";
            cell.Data.Type   = DataType.String;
            cell.Data.Text   = "Wochen";
            cell.MergeAcross = 5;

            cell           = Row0.Cells.Add();
            cell.StyleID   = "ce9";
            cell.Data.Type = DataType.String;
            cell.Data.Text = "Bemerkungen";
            cell.MergeDown = 1;
            cell           = Row0.Cells.Add();
            cell.Index     = 1024;

            // -----------------------------------------------

            WorksheetRow Row1 = sheet.Table.Rows.Add();

            Row1.Height        = 12;
            Row1.AutoFitHeight = false;
            // Hole Kalenderwochen, für die nächsten 6 Wochen aus Klasse: CalendarWeekDays, und
            // schreibe diese in das DataSheet
            for (int rows = 1; rows <= 6; rows++)
            {
                Row1.Cells.Add();
            }
            CalendarWeekDays cal      = new CalendarWeekDays();
            List <int>       weekList = new List <int>();

            weekList = cal.NumberOfWeek(DateTime.Now);
            for (int week = 0; week <= 5; week++)
            {
                Row1.Cells.Add(weekList[week].ToString(), DataType.Number, "ce10");
            }
            cell.Index = 1016;
            // -----------------------------------------------
            //  Optionen
            // -----------------------------------------------
            sheet.Options.ProtectObjects   = false;
            sheet.Options.ProtectScenarios = false;
        }
Exemplo n.º 12
0
 /// <summary>
 /// Sets the width of the columns in the exported document.
 /// 
 /// </summary>
 /// 
 /// <remarks>
 /// If the contents of the current cell are wider than the
 ///             contents of previous cells in the same column, then the column's width
 ///             is expanded to accomodate the widest cell.
 /// 
 /// </remarks>
 private void SetColumnWidth(WorksheetColumn wsCol, WorksheetCell cell)
 {
     string str = cell.Value != null ? cell.Value.ToString() : string.Empty;
     if (str == string.Empty)
         return;
     double a = !str.Contains(Environment.NewLine) ? this.GetWidthOfText(str) : this.GetWidestLineLength(str);
     if (cell.CellFormat.Font.Height > 0 && cell.CellFormat.Font.Height != 240)
         a *= (double)cell.CellFormat.Font.Height / 240.0;
     if (cell.CellFormat.Font.Bold == ExcelDefaultableBoolean.True)
         a *= 1.1;
     double num = (double)(int)Math.Ceiling(a);
     if (num <= (double)wsCol.Width)
         return;
     wsCol.Width = (int)num;
 }