public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create directory if it is not already present.
            bool IsExists = System.IO.Directory.Exists(dataDir);

            if (!IsExists)
            {
                System.IO.Directory.CreateDirectory(dataDir);
            }

            // Instantiating a Workbook object
            Workbook workbook = new Workbook();

            // Obtaining the reference of the first (default) worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[0];

            // Adding a new Style to the styles
            Style style = workbook.CreateStyle();

            // Setting the vertical alignment of the text in the "A1" cell
            style.VerticalAlignment = TextAlignmentType.Center;

            // Setting the horizontal alignment of the text in the "A1" cell
            style.HorizontalAlignment = TextAlignmentType.Center;

            // Setting the font color of the text in the "A1" cell
            style.Font.Color = Color.Green;

            // Shrinking the text to fit in the cell
            style.ShrinkToFit = true;

            // Setting the bottom border color of the cell to red
            style.Borders[BorderType.BottomBorder].Color = Color.Red;

            // Setting the bottom border type of the cell to medium
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

            // Creating StyleFlag
            StyleFlag styleFlag = new StyleFlag();

            styleFlag.HorizontalAlignment = true;
            styleFlag.VerticalAlignment   = true;
            styleFlag.ShrinkToFit         = true;
            styleFlag.Borders             = true;
            styleFlag.FontColor           = true;

            // Accessing a row from the Rows collection
            Row row = worksheet.Cells.Rows[0];

            // Assigning the Style object to the Style property of the row
            row.ApplyStyle(style, styleFlag);

            // Saving the Excel file
            workbook.Save(dataDir + "book1.out.xls");
            // ExEnd:1
        }
Exemple #2
0
        public static Row SetFontUnderline(this Row row, FontUnderlineType value)
        {
            var style = row.Style;

            style.Font.Underline = value;
            row.ApplyStyle(style, new StyleFlag {
                FontUnderline = true
            });
            return(row);
        }
Exemple #3
0
        public static Row SetFontBold(this Row row, bool value = true)
        {
            var style = row.Style;

            style.Font.IsBold = value;
            row.ApplyStyle(style, new StyleFlag {
                FontBold = true
            });
            return(row);
        }
Exemple #4
0
        public static Row SetFontSize(this Row row, int value = Settings.FontSize)
        {
            var style = row.Style;

            style.Font.Size = value;
            row.ApplyStyle(style, new StyleFlag {
                FontSize = true
            });
            return(row);
        }
Exemple #5
0
        public static Row SetFontName(this Row row, string value = Settings.FontName)
        {
            var style = row.Style;

            style.Font.Name = value;
            row.ApplyStyle(style, new StyleFlag {
                FontName = true
            });
            return(row);
        }
Exemple #6
0
        public static Row SetVerticalAlignment(this Row row, TextAlignmentType value)
        {
            var style = row.Style;

            style.VerticalAlignment = value;
            row.ApplyStyle(style, new StyleFlag {
                VerticalAlignment = true
            });
            return(row);
        }
Exemple #7
0
        public static Row SetFontColor(this Row row, Color value)
        {
            var style = row.Style;

            style.Font.Color = value;
            row.ApplyStyle(style, new StyleFlag {
                FontColor = true
            });
            return(row);
        }
Exemple #8
0
        public static Row SetTextWrapped(this Row row, bool value = true)
        {
            var style = row.Style;

            style.IsTextWrapped = value;
            row.ApplyStyle(style, new StyleFlag {
                WrapText = true
            });
            return(row);
        }
Exemple #9
0
        public static Row SetFormat(this Row row, string value, params object[] args)
        {
            var style = row.Style;

            style.Custom = string.Format(value, args);
            row.ApplyStyle(style, new StyleFlag {
                NumberFormat = true
            });
            return(row);
        }
Exemple #10
0
        public static Row SetFormat(this Row row, int value)
        {
            var style = row.Style;

            style.Number = value;
            row.ApplyStyle(style, new StyleFlag {
                NumberFormat = true
            });
            return(row);
        }
Exemple #11
0
        public static Row SetForegroundColor(this Row row, System.Drawing.Color value)
        {
            var style = row.Style;

            style.ForegroundColor = value;
            style.Pattern         = BackgroundType.Solid;
            row.ApplyStyle(style, new StyleFlag {
                CellShading = true
            });
            return(row);
        }
        private void UpdateSheet(DataTable dt)
        {
            Session["AsposeDynamicFormsdataTable"] = dt;

            //Creating a file stream containing the Excel file to be opened
            FileStream fstream = new FileStream(Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);

            //Instantiating a Workbook object
            //Opening the Excel file through the file stream
            Workbook workbook = new Workbook(fstream);

            //Accessing a worksheet using its sheet name
            Worksheet worksheet = workbook.Worksheets["Settings"];

            //Closing the file stream to free all resources
            fstream.Close();

            workbook.Worksheets.RemoveAt("Settings");
            worksheet = workbook.Worksheets.Add("Settings");
            worksheet.Cells.ImportDataTable(dt, true, "A1");

            Aspose.Cells.Style objStyle = workbook.CreateStyle();
            objStyle.Font.IsBold = true;

            //Bold style flag options
            StyleFlag objStyleFlag = new StyleFlag();

            objStyleFlag.FontBold = true;
            //Apply this style to row 1

            Row row1 = workbook.Worksheets[0].Cells.Rows[0];

            row1.ApplyStyle(objStyle, objStyleFlag);
            worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);

            //Auto-fit all the columns
            workbook.Worksheets["Data"].AutoFitColumns();
            workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);


            PopulateGrid();

            if (!ProcessButton.Text.Equals("Update"))
            {
                lbl_Msg.Visible  = true;
                lbl_Msg.CssClass = "Success";
                lbl_Msg.Text     = "Field added successfully";
            }
            ClearFields();
        }
        protected void btnUpdate_OnClick(object sender, EventArgs e)
        {
            try
            {
                if (Session["AsposeDynamicFormsdataTable"] != null)
                {
                    DataTable dt = (DataTable)Session["AsposeDynamicFormsdataTable"];
                    if (dt != null)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            string setupSeletectedItemId = (string)Session["SetupSeletectedItemID"];

                            DataRow[] drs = dt.Select("[Field ID] = '" + setupSeletectedItemId + "'");
                            if (true)
                            {
                                if (drs.Length > 0)
                                {
                                    drs = dt.Select("[Field ID] = '" + txtFieldId.Text.Trim() + "'"); // checking field type constraint
                                    if (drs != null)
                                    {
                                        if (drs.Length > 0)
                                        {
                                            if (setupSeletectedItemId == txtFieldId.Text.Trim())
                                            {
                                                drs[0]["Field Type"]       = ddlFieldType.SelectedValue.Trim();
                                                drs[0]["Field ID"]         = txtFieldId.Text.Trim();
                                                drs[0]["Field Label Text"] = txtFieldLableText.Text.Trim();
                                                drs[0]["Field Values"]     = txtFieldValues.Text.Trim();
                                                if (chkIsDisplay.Checked)
                                                {
                                                    drs[0]["Is Display"] = "TRUE";
                                                }
                                                else
                                                {
                                                    drs[0]["Is Display"] = "FALSE";
                                                }
                                                drs[0]["Sort ID"] = txtSortId.Text.Trim();


                                                lbl_Msg.Visible  = true;
                                                lbl_Msg.CssClass = "alertupdate";
                                                lbl_Msg.Text     = "Field updated successfully";

                                                dt.AcceptChanges();
                                            }
                                            else
                                            {       // on updating field user Enter other Field ID
                                                lbl_Msg.Visible   = true;
                                                lbl_Msg.CssClass  = "alertdanger";
                                                lbl_Msg.Text      = "Field ID already exists ";
                                                btnUpdate.Visible = true;
                                                return;
                                            }
                                        }
                                        else
                                        {
                                            lbl_Msg.Visible   = true;
                                            lbl_Msg.CssClass  = "alertdanger";
                                            lbl_Msg.Text      = "Field ID not exists ";
                                            btnUpdate.Visible = true;
                                            return;
                                        }
                                    }
                                }
                                else
                                {
                                    // new field record case
                                    DataRow dr = dt.NewRow();

                                    dr["Field Type"]       = ddlFieldType.SelectedValue.Trim();
                                    dr["Field ID"]         = txtFieldId.Text.Trim();
                                    dr["Field Label Text"] = txtFieldLableText.Text.Trim();
                                    dr["Field Values"]     = txtFieldValues.Text.Trim();
                                    if (chkIsDisplay.Checked)
                                    {
                                        dr["Is Display"] = "TRUE";
                                    }
                                    else
                                    {
                                        dr["Is Display"] = "FALSE";
                                    }
                                    dr["Sort ID"] = txtSortId.Text.Trim();

                                    dr["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");

                                    dt.Rows.Add(dr);

                                    lbl_Msg.Visible  = true;
                                    lbl_Msg.CssClass = "Success";
                                    lbl_Msg.Text     = "Field added successfully";
                                }

                                Session["AsposeDynamicFormsdataTable"] = dt;

                                //Creating a file stream containing the Excel file to be opened
                                FileStream fstream = new FileStream(Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);

                                //Instantiating a Workbook object
                                //Opening the Excel file through the file stream
                                Workbook workbook = new Workbook(fstream);

                                //Accessing a worksheet using its sheet name
                                Worksheet worksheet = workbook.Worksheets["Settings"];


                                //Closing the file stream to free all resources
                                fstream.Close();

                                workbook.Worksheets.RemoveAt("Settings");
                                worksheet = workbook.Worksheets.Add("Settings");
                                worksheet.Cells.ImportDataTable(dt, true, "A1");

                                Aspose.Cells.Style objStyle = workbook.CreateStyle();
                                objStyle.Font.IsBold = true;

                                //Bold style flag options
                                StyleFlag objStyleFlag = new StyleFlag();
                                objStyleFlag.FontBold = true;
                                //Apply this style to row 1

                                Row row1 = workbook.Worksheets[0].Cells.Rows[0];
                                row1.ApplyStyle(objStyle, objStyleFlag);
                                worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);

                                //Auto-fit all the columns
                                workbook.Worksheets["Data"].AutoFitColumns();
                                workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);

                                PopulateGrid();
                                ClearFields();      // clear field function which clear all fields while update/adding record
                            }
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                ShowException(exc);
            }
        }
Exemple #14
0
        // Save data to excel file
        protected void ProcessButton_Click(object sender, EventArgs e)
        {
            try
            {
                // Check for license and apply if exists


                //Creating a file stream containing the Excel file to be opened
                FileStream fstream = new FileStream(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"),
                                                    FileMode.Open, FileAccess.Read);

                //Instantiating a Workbook object
                //Opening the Excel file through the file stream
                Workbook workbook = new Workbook(fstream);

                //Accessing a worksheet using its sheet name
                //Worksheet worksheet = workbook.Worksheets["Data"];
                //Worksheet worksheet1 = workbook.Worksheets["Settings"];


                Worksheet worksheet  = workbook.Worksheets["Data"];
                Worksheet worksheet1 = workbook.Worksheets["Settings"];

                //Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
                DataTable dataTable = null;

                if (worksheet.Cells.Rows.Count <= 0)
                {
                    dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true);
                }
                else
                {
                    dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count,
                                                                        worksheet.Cells.Columns.Count, true);
                }

                //Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable from setting
                DataTable dataTable1 = worksheet1.Cells.ExportDataTableAsString(0, 0, worksheet1.Cells.Rows.Count, 10,
                                                                                true);



                if (dataTable1 != null)
                {
                    if (dataTable != null)
                    {
                        if (dataTable.Columns.Count <= 1)
                        {
                            dataTable.Columns.RemoveAt(0);
                        }
                        foreach (DataRow row in dataTable1.Rows)
                        {
                            if (!row[1].ToString().Trim().Equals("Title") && !row[1].ToString().Trim().Equals("Success"))
                            {
                                foreach (string strItem in row[2].ToString().Trim().Split(';'))
                                {
                                    if (!strItem.Trim().Equals(""))
                                    {
                                        if (dataTable.Columns[strItem.Trim()] == null)
                                        {
                                            dataTable.Columns.Add(strItem.Trim());
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                //Closing the file stream to free all resources
                fstream.Close();

                DataRow dr = dataTable.NewRow();

                foreach (Control ctrl in myPlaceHolder.Controls)
                {
                    if (ctrl != null)
                    {
                        if (ctrl is TextBox)
                        {
                            dr[ctrl.ID] = ((TextBox)ctrl).Text.Trim();
                            continue;
                        }

                        if (ctrl is RadioButton)
                        {
                            if (((RadioButton)ctrl).Checked)
                            {
                                dr[ctrl.ID] = ((RadioButton)ctrl).Text.Trim();
                                continue;
                            }
                        }

                        if (ctrl is CheckBox)
                        {
                            if (((CheckBox)ctrl).Checked)
                            {
                                dr[ctrl.ID] = ((CheckBox)ctrl).Text.Trim();
                                continue;
                            }
                        }

                        if (ctrl is DropDownList)
                        {
                            dr[ctrl.ID] = ((DropDownList)ctrl).SelectedItem.Text.Trim();
                            continue;
                        }
                    }
                }
                dataTable.Rows.Add(dr);
                workbook.Worksheets.RemoveAt("Data");
                worksheet = workbook.Worksheets.Add("Data");
                worksheet.Cells.ImportDataTable(dataTable, true, "A1");

                // Apply Hearder Row/First Row text to Bold
                Aspose.Cells.Style objStyle = workbook.CreateStyle();
                objStyle.Font.IsBold = true;

                //Bold style flag options
                StyleFlag objStyleFlag = new StyleFlag();
                objStyleFlag.FontBold = true;
                //Apply this style to row 1

                Row row1 = workbook.Worksheets[0].Cells.Rows[0];
                row1.ApplyStyle(objStyle, objStyleFlag);
                worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);


                worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);

                //Auto-fit all the columns
                workbook.Worksheets["Data"].AutoFitColumns();

                workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
                error_msg.Visible   = false;
                success_msg.Visible = true;
            }
            catch (Exception exc)
            {
                success_msg.Visible = false;
                error_msg.Visible   = true;
                error_msg.InnerText = exc.Message;
            }
        }
Exemple #15
0
        public List <CustomApartmentDTO> Apartment_ExportExcel(List <CustomApartmentDTO> listInput)
        {
            if (listInput == null)
            {
                return(null);
            }

            //Create the workbook and the worksheet
            Workbook  workbook = new Workbook();
            Worksheet sheet    = workbook.Worksheets[0];

            sheet.Name = "Danh sách căn hộ";

            //Set a title and format its style
            var cell = sheet.Cells["A1"];

            cell.PutValue("DANH SÁCH CĂN HỘ");

            Style style = cell.GetStyle();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Size           = 20;
            style.Font.IsBold         = true;
            style.ForegroundColor     = Color.AliceBlue;
            style.Pattern             = BackgroundType.Solid;
            cell.SetStyle(style);

            List <string> listProperty = new List <string> {
                "Mã căn hộ", "Tên căn hộ", "Loại căn hộ", "Tòa nhà", "Tầng", "Số người ở", "Giá", "Trạng thái căn hộ", "Trạng thái duyệt"
            };
            int countProperty = 0;

            for (char c = 'A'; c <= 'Z' && countProperty < listProperty.Count; c++)
            {
                var headerCell = sheet.Cells[c + "3"];
                headerCell.PutValue(listProperty[countProperty]);
                countProperty++;
            }

            Row       header     = sheet.Cells.Rows[2];
            StyleFlag headerFlag = new StyleFlag()
            {
                All = true
            };

            style = header.Style;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor     = Color.LightGreen;
            style.BackgroundColor     = Color.LightGreen;
            style.Font.Size           = 15;
            style.Font.IsBold         = true;
            header.ApplyStyle(style, headerFlag);

            sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
                                            , new string[] { "APARTMENT_CODE", "APARTMENT_NAME", "APARTMENT_TYPE_NAME", "BUILDING_NAME", "Floor_NAME", "NUMBER_OF_PEOPLE", "APARTMENT_PRICE", "APARTMENT_STATUS", "AUTH_STATUS" }
                                            , false, 3, 0, listInput.Count
                                            , false
                                            , "dd/mm/yyyy"
                                            , false);


            //Set the columns to fit the size of their content
            sheet.AutoFitColumns();

            //Save the Excel workbook
            var fileName1 = sheet.Name + ".xlsx";

            workbook.Save(fileName1, SaveFormat.Xlsx);


            return(listInput);
        }
        public List <NSX_DTO> NSX_ExportExcel(List <NSX_DTO> listInput)
        {
            if (listInput == null)
            {
                return(null);
            }

            //Create the workbook and the worksheet
            Workbook  workbook = new Workbook();
            Worksheet sheet    = workbook.Worksheets[0];

            sheet.Name = "Danh sách nhà sản xuất";

            //Set a title and format its style
            var cell = sheet.Cells["A1"];

            cell.PutValue("DANH SÁCH NHÀ SẢN XUẤT");

            Style style = cell.GetStyle();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Size           = 20;
            style.Font.IsBold         = true;
            style.ForegroundColor     = Color.AliceBlue;
            style.Pattern             = BackgroundType.Solid;
            cell.SetStyle(style);

            List <string> listProperty = new List <string> {
                "Mã nhà sản xuất", "Tên nhà sản xuất", "Địa chỉ", "Ghi chú"
            };
            int countProperty = 0;

            for (char c = 'A'; c <= 'D'; c++)
            {
                var headerCell = sheet.Cells[c + "3"];
                headerCell.PutValue(listProperty[countProperty]);
                countProperty++;
            }

            Row       header     = sheet.Cells.Rows[2];
            StyleFlag headerFlag = new StyleFlag()
            {
                All = true
            };

            style = header.Style;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor     = Color.LightGreen;
            style.BackgroundColor     = Color.LightGreen;
            style.Font.Size           = 15;
            style.Font.IsBold         = true;
            header.ApplyStyle(style, headerFlag);

            sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
                                            , new string[] { "NSX_CODE", "NSX_NAME", "NSX_FROM", "NOTES" }
                                            , false, 3, 0, listInput.Count
                                            , false
                                            , "dd/mm/yyyy"
                                            , true);


            //Set the columns to fit the size of their content
            sheet.AutoFitColumns();

            //Save the Excel workbook
            var fileName1 = sheet.Name + ".xlsx";

            workbook.Save(fileName1, SaveFormat.Xlsx);


            return(listInput);
        }
        public PagedResultDto <PTX_DTO> PTX_ExportExcel()
        {
            List <PTX_DTO> listPTX = procedureHelper.GetData <PTX_DTO>("PTX_SEARCH", new PTX_DTO());
            int            count   = listPTX.Count();

            if (listPTX == null)
            {
                return(null);
            }

            //Create the workbook and the worksheet
            Workbook  workbook = new Workbook();
            Worksheet sheet    = workbook.Worksheets[0];

            sheet.Name = "Danh sách phiếu thuê";

            //Set a title and format its style
            var cell = sheet.Cells["A1"];

            cell.PutValue("DANH SÁCH PHIẾU THUÊ");

            Style style = cell.GetStyle();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Size           = 20;
            style.Font.IsBold         = true;
            style.ForegroundColor     = Color.AliceBlue;
            style.Pattern             = BackgroundType.Solid;
            cell.SetStyle(style);

            List <string> listProperty = new List <string> {
                "Mã phiếu thuê", "Ngày thuê", "Ngày hết hạn", "Ngày trả", "Tổng tiền", "Ghi chú", "Mã xe", "Mã người thuê", "Ngày tạo"
            };
            int countProperty = 0;

            for (char c = 'A'; c <= 'I'; c++)
            {
                var headerCell = sheet.Cells[c + "3"];
                headerCell.PutValue(listProperty[countProperty]);
                countProperty++;
            }

            Row       header     = sheet.Cells.Rows[2];
            StyleFlag headerFlag = new StyleFlag()
            {
                All = true
            };

            style = header.Style;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor     = Color.LightGreen;
            style.BackgroundColor     = Color.LightGreen;
            style.Font.Size           = 15;
            style.Font.IsBold         = true;
            header.ApplyStyle(style, headerFlag);

            sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listPTX
                                            , new string[] { "PTX_CODE", "PTX_RENT_DT", "PTX_EXP_DT", "PTX_RETURN_DT", "PTX_PRICE", "PTX_NOTE", "XE_ID", "NTX_ID", "CREATED_DT" }
                                            , false, 3, 0, listPTX.Count
                                            , false
                                            , "dd/mm/yyyy"
                                            , false);


            //Set the columns to fit the size of their content
            sheet.AutoFitColumns();

            //Save the Excel workbook
            var fileName1 = sheet.Name + ".xlsx";

            workbook.Save(fileName1, SaveFormat.Xlsx);


            return(new PagedResultDto <PTX_DTO> {
                TotalCount = count, Items = listPTX
            });
        }
        private static void CreateCellsFormatting(Workbook workbook)
        {
            //Define a style object adding a new style
            //to the collection list.
            Style stl0 = workbook.Styles[workbook.Styles.Add()];

            //Set a custom shading color of the cells.
            stl0.ForegroundColor = Color.FromArgb(155, 204, 255);
            //Set the solid background fillment.
            stl0.Pattern = BackgroundType.Solid;
            //Set a font.
            stl0.Font.Name = "Trebuchet MS";
            //Set the size.
            stl0.Font.Size = 18;
            //Set the font text color.
            stl0.Font.Color = Color.Maroon;
            //Set it bold
            stl0.Font.IsBold = true;
            //Set it italic.
            stl0.Font.IsItalic = true;
            //Define a style flag struct.
            StyleFlag flag = new StyleFlag();

            //Apply cell shading.
            flag.CellShading = true;
            //Apply font.
            flag.FontName = true;
            //Apply font size.
            flag.FontSize = true;
            //Apply font color.
            flag.FontColor = true;
            //Apply bold font.
            flag.FontBold = true;
            //Apply italic attribute.
            flag.FontItalic = true;
            //Get the first row in the first worksheet.
            Row row = workbook.Worksheets[0].Cells.Rows[0];

            //Apply the style to it.
            row.ApplyStyle(stl0, flag);

            //Obtain the cells of the first worksheet.
            Cells cells = workbook.Worksheets[0].Cells;

            //Set the height of the first row.
            cells.SetRowHeight(0, 30);

            //Define a style object adding a new style
            //to the collection list.
            Style stl1 = workbook.Styles[workbook.Styles.Add()];

            //Set the rotation angle of the text.
            stl1.RotationAngle = 45;
            //Set the custom fill color of the cells.
            stl1.ForegroundColor = Color.FromArgb(0, 51, 105);
            //Set the solid background pattern for fillment.
            stl1.Pattern = BackgroundType.Solid;
            //Set the left border line style.
            stl1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            //Set the left border line color.
            stl1.Borders[BorderType.LeftBorder].Color = Color.White;
            //Set the horizontal alignment to center aligned.
            stl1.HorizontalAlignment = TextAlignmentType.Center;
            //Set the vertical alignment to center aligned.
            stl1.VerticalAlignment = TextAlignmentType.Center;
            //Set the font.
            stl1.Font.Name = "Times New Roman";
            //Set the font size.
            stl1.Font.Size = 10;
            //Set the font color.
            stl1.Font.Color = Color.White;
            //Set the bold attribute.
            stl1.Font.IsBold = true;
            //Set the style flag struct.
            flag = new StyleFlag();
            //Apply the left border.
            flag.LeftBorder = true;
            //Apply text rotation orientation.
            flag.Rotation = true;
            //Apply fill color of cells.
            flag.CellShading = true;
            //Apply horizontal alignment.
            flag.HorizontalAlignment = true;
            //Apply vertical alignment.
            flag.VerticalAlignment = true;
            //Apply the font.
            flag.FontName = true;
            //Apply the font size.
            flag.FontSize = true;
            //Apply the font color.
            flag.FontColor = true;
            //Apply the bold attribute.
            flag.FontBold = true;
            //Get the second row of the first worksheet.
            row = workbook.Worksheets[0].Cells.Rows[1];
            //Apply the style to it.
            row.ApplyStyle(stl1, flag);

            //Set the height of the second row.
            cells.SetRowHeight(1, 48);

            //Define a style object adding a new style
            //to the collection list.
            Style stl2 = workbook.Styles[workbook.Styles.Add()];

            //Set the custom cell shading color.
            stl2.ForegroundColor = Color.FromArgb(155, 204, 255);
            //Set the solid background pattern for fillment color.
            stl2.Pattern = BackgroundType.Solid;
            //Set the font.
            stl2.Font.Name = "Trebuchet MS";
            //Set the font color.
            stl2.Font.Color = Color.Maroon;
            //Set the font size.
            stl2.Font.Size = 10;
            //Set the style flag struct.
            flag = new StyleFlag();
            //Apply cell shading.
            flag.CellShading = true;
            //Apply the font.
            flag.FontName = true;
            //Apply the font color.
            flag.FontColor = true;
            //Apply the font size.
            flag.FontSize = true;
            //Get the first column in the first worksheet.
            Column col = workbook.Worksheets[0].Cells.Columns[0];

            //Apply the style to it.
            col.ApplyStyle(stl2, flag);

            //Define a style object adding a new style
            //to the collection list.
            Style stl3 = workbook.Styles[workbook.Styles.Add()];

            //Set the custom cell filling color.
            stl3.ForegroundColor = Color.FromArgb(124, 199, 72);
            //Set the solid background pattern for fillment color.
            stl3.Pattern = BackgroundType.Solid;
            //Apply the style to A2 cell.
            cells["A2"].SetStyle(stl3);

            //Define a style object adding a new style
            //to the collection list.
            Style stl4 = workbook.Styles[workbook.Styles.Add()];

            //Set the custom font text color.
            stl4.Font.Color = Color.FromArgb(0, 51, 105);
            //Set the bottom border line style.
            stl4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            //Set the bottom border line color to custom color.
            stl4.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
            //Set the background fill color of the cells.
            stl4.ForegroundColor = Color.White;
            //Set the solid fillcolor pattern.
            stl4.Pattern = BackgroundType.Solid;
            //Set custom number format.
            stl4.Custom = "$#,##0.0";
            //Set a style flag struct.
            flag = new StyleFlag();
            //Apply font color.
            flag.FontColor = true;
            //Apply cell shading color.
            flag.CellShading = true;
            //Apply custom number format.
            flag.NumberFormat = true;
            //Apply bottom border.
            flag.BottomBorder = true;

            //Define a style object adding a new style
            //to the collection list.
            Style stl5 = workbook.Styles[workbook.Styles.Add()];

            //Set the bottom borde line style.
            stl5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            //Set the bottom border line color.
            stl5.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
            //Set the custom shading color of the cells.
            stl5.ForegroundColor = Color.FromArgb(250, 250, 200);
            //Set the solid background pattern for fillment color.
            stl5.Pattern = BackgroundType.Solid;
            //Set custom number format.
            stl5.Custom = "$#,##0.0";
            //Set font text color.
            stl5.Font.Color = Color.Maroon;

            //Create a named range of cells (B3:M25)in the first worksheet.
            Range range = workbook.Worksheets[0].Cells.CreateRange("B3", "M25");

            //Name the range.
            range.Name = "MyRange";
            //Apply the style to cells in the named range.
            range.ApplyStyle(stl4, flag);

            //Apply different style to alternative rows
            //in the range.
            for (int i = 0; i <= 22; i++)
            {
                for (int j = 0; j < 12; j++)
                {
                    if (i % 2 == 0)
                    {
                        range[i, j].SetStyle(stl5);
                    }
                }
            }

            //Define a style object adding a new style
            //to the collection list.
            Style stl6 = workbook.Styles[workbook.Styles.Add()];

            //Set the custom fill color of the cells.
            stl6.ForegroundColor = Color.FromArgb(0, 51, 105);
            //Set the background pattern for fillment color.
            stl6.Pattern = BackgroundType.Solid;
            //Set the font.
            stl6.Font.Name = "Arial";
            //Set the font size.
            stl6.Font.Size = 10;
            //Set the font color
            stl6.Font.Color = Color.White;
            //Set the text bold.
            stl6.Font.IsBold = true;
            //Set the custom number format.
            stl6.Custom = "$#,##0.0";
            //Set the style flag struct.
            flag = new StyleFlag();
            //Apply cell shading.
            flag.CellShading = true;
            //Apply the arial font.
            flag.FontName = true;
            //Apply the font size.
            flag.FontSize = true;
            //Apply the font color.
            flag.FontColor = true;
            //Apply the bold attribute.
            flag.FontBold = true;
            //Apply the number format.
            flag.NumberFormat = true;
            //Get the 26th row in the first worksheet which produces totals.
            row = workbook.Worksheets[0].Cells.Rows[25];
            //Apply the style to it.
            row.ApplyStyle(stl6, flag);
            //Now apply this style to those cells (N3:N25) which
            //has productwise sales totals.
            for (int i = 2; i < 25; i++)
            {
                cells[i, 13].SetStyle(stl6);
            }
            //Set N column's width to fit the contents.
            workbook.Worksheets[0].Cells.SetColumnWidth(13, 9.33);
            //ExEnd:1
        }
        public static MemoryStream ToExcel <T>(this List <T> list)
        {
            //configure embeded license
            Aspose.Cells.License license = new Aspose.Cells.License();
            license.SetLicense("Aspose.Cells.lic");

            try
            {
                //Create excel workbook
                Workbook  book  = new Workbook();
                Worksheet sheet = book.Worksheets[0];

                List <string>  objHeaders = new List <string>();
                PropertyInfo[] headerInfo = typeof(T).GetProperties();
                foreach (var property in headerInfo)
                {
                    var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
                                    .Cast <DisplayNameAttribute>().FirstOrDefault();

                    //Avoid columns from Base Business Entity
                    if (property.Name == "ChangedFields")
                    {
                        break;
                    }
                    objHeaders.Add(property.Name);
                }

                sheet.Cells.ImportCustomObjects(list, objHeaders.ToArray(), true, 1, 0, list.Count, true, string.Empty, false);

                #region Header style

                Style style = book.CreateStyle();
                style.Font.IsBold = true;
                // Define a style flag struct.
                StyleFlag flag = new StyleFlag();
                flag.FontBold = true;
                // Get the first row in the first worksheet.
                Row row = book.Worksheets[0].Cells.Rows[1];
                // Apply the style to it.
                row.ApplyStyle(style, flag);

                #endregion

                // Auto-fit all the columns
                book.Worksheets[0].AutoFitColumns();

                MemoryStream outputStream = new MemoryStream();
                //Save workbook as stream instead of saving it to the Disk
                book.Save(outputStream, SaveFormat.Xlsx);

                //Call dispose methods
                sheet.Dispose();
                book.Dispose();

                outputStream.Position = 0;
                return(outputStream);
            }
            catch (Exception)
            {
                // TODO : Handle out of memory exception
                throw;
            }
        }
Exemple #20
0
        public dynamic TTYCSC_ExportExcel(int pageNumber, int pageSize, string TTYCSCName, string TTYCSCCode, string TTYCSCDesc, string APPROVEStatus)
        {
            try
            {
                var list = procedureHelper.GetData <dynamic>("TTYCSC_SearchFilter", new
                {
                    PageNumber     = pageNumber,
                    PageSize       = pageSize,
                    TTYCSC_NAME    = TTYCSCName,
                    TTYCSC_CODE    = TTYCSCCode,
                    TTYCSC_DESC    = TTYCSCDesc,
                    APPROVE_STATUS = APPROVEStatus
                });

                if (list == null)
                {
                    return(null);
                }

                list.ForEach(l =>
                {
                    l.AUTH_STATUS = l.AUTH_STATUS == "A" ? "Đã duyệt" : (l.AUTH_STATUS == "U" ? "Từ chối" : "Chưa duyệt");
                });


                Workbook  book  = new Workbook();     // Create A Workbook
                Worksheet sheet = book.Worksheets[0]; // Create a worksheet
                sheet.Name = "Danh sách căn hộ";

                var cell = sheet.Cells["A1"];
                cell.PutValue("DANH SÁCH TRẠNG THÁI YÊU CẦU SỬA CHỮA");

                // Create Styles
                Style style = cell.GetStyle();
                style.HorizontalAlignment = TextAlignmentType.Center;
                style.Font.Size           = 20;
                style.Font.IsBold         = true;
                style.ForegroundColor     = Color.AliceBlue;
                style.Pattern             = BackgroundType.Solid;
                cell.SetStyle(style);

                List <string> listProperty = new List <string> {
                    "Mã trạng thái", "Tên trạng thái", "Chi tiết trạng thái", "Trạng thái duyệt"
                };
                int countProperty = 0;
                for (char c = 'A'; c <= 'Z' && countProperty < listProperty.Count; c++)
                {
                    var headerCell = sheet.Cells[c + "3"];
                    headerCell.PutValue(listProperty[countProperty]);
                    countProperty++;
                }

                Row       header     = sheet.Cells.Rows[2];
                StyleFlag headerFlag = new StyleFlag()
                {
                    All = true
                };
                style = header.Style;
                style.HorizontalAlignment = TextAlignmentType.Center;
                style.ForegroundColor     = Color.LightGreen;
                style.BackgroundColor     = Color.LightGreen;
                style.Font.Size           = 15;
                style.Font.IsBold         = true;
                header.ApplyStyle(style, headerFlag);

                sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list
                                                , new string[] { "TTYCSC_CODE", "TTYCSC_NAME", "TTYCSC_DECS", "AUTH_STATUS" }
                                                , false, 3, 0, list.Count
                                                , false
                                                , "dd/mm/yyyy"
                                                , false);

                //Set the columns to fit the size of their content
                sheet.AutoFitColumns();

                //Save the Excel workbook
                var fileName1 = sheet.Name + ".xlsx";
                book.Save(fileName1, SaveFormat.Xlsx);

                return(list);
            }
            catch
            {
                return(null);
            }
        }
        public List <BD_DTO> ExportData(List <BD_DTO> listInput)
        {
            if (listInput == null)
            {
                return(null);
            }

            //Create the workbook and the worksheet
            Workbook  workbook = new Workbook();
            Worksheet sheet    = workbook.Worksheets[0];

            sheet.Name = "Danh Sách Phiếu Bảo Dưỡng";

            //Set a title and format its style
            var cell = sheet.Cells["A1"];

            cell.PutValue("BẢO DƯỠNG XE");

            Style style = cell.GetStyle();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Size           = 20;
            style.Font.IsBold         = true;
            style.ForegroundColor     = Color.AliceBlue;
            style.Pattern             = BackgroundType.Solid;
            cell.SetStyle(style);

            List <string> listProperty = new List <string> {
                "Mã Bảo Dưỡng", "Garage", "Địa chỉ", "Tổng tiền", "Mã xe", "Ngày bắt đầu", "Ngày kết thúc", "Ngày tạo"
            };
            int countProperty = 0;

            for (char c = 'A'; c <= 'H'; c++)
            {
                var headerCell = sheet.Cells[c + "2"];
                headerCell.PutValue(listProperty[countProperty]);
                countProperty++;
            }

            Row       header     = sheet.Cells.Rows[1];
            StyleFlag headerFlag = new StyleFlag()
            {
                All = true
            };

            style = header.Style;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor     = Color.LightGreen;
            style.BackgroundColor     = Color.LightGreen;
            style.Font.Size           = 15;
            style.Font.IsBold         = true;
            header.ApplyStyle(style, headerFlag);

            sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
                                            , new string[] { "BD_CODE", "BD_GARAGE", "BD_ADDRESS", "BD_PRICE", "XE_ID", "BD_FROM_DT", "BD_TO_DT", "CREATED_DT" }
                                            , false, 3, 0, listInput.Count
                                            , false
                                            , "dd/mm/yyyy"
                                            , false);


            //Set the columns to fit the size of their content
            sheet.AutoFitColumns();

            //Save the Excel workbook
            var fileName1 = sheet.Name + ".xlsx";

            workbook.Save(fileName1, SaveFormat.Xlsx);

            return(listInput);
        }
Exemple #22
0
        public List <XE_DTO> XE_ExportExcel(List <XE_DTO> listInput)
        {
            if (listInput == null)
            {
                return(null);
            }

            //Create the workbook and the worksheet
            Workbook  workbook = new Workbook();
            Worksheet sheet    = workbook.Worksheets[0];

            sheet.Name = "Danh sách xe";

            //Set a title and format its style
            var cell = sheet.Cells["A1"];

            cell.PutValue("DANH SÁCH XE");

            Style style = cell.GetStyle();

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Font.Size           = 20;
            style.Font.IsBold         = true;
            style.ForegroundColor     = Color.AliceBlue;
            style.Pattern             = BackgroundType.Solid;
            cell.SetStyle(style);

            List <string> listProperty = new List <string> {
                "Mã xe", "Tên xe", "Màu xe", "Số chỗ ngồi", "Tên mẫu", "Chứng chỉ", "Giá", "Tiêu thụ", "Ghi chú"
                , "Tốc độ tối đa", "Nhà sản xuất", "Năm sản xuất", "Trạng thái", "Thời gian tạo", "Tổng quãng đường đi"
            };
            int countProperty = 0;

            for (char c = 'A'; c <= 'O'; c++)
            {
                var headerCell = sheet.Cells[c + "3"];
                headerCell.PutValue(listProperty[countProperty]);
                countProperty++;
            }

            Row       header     = sheet.Cells.Rows[2];
            StyleFlag headerFlag = new StyleFlag()
            {
                All = true
            };

            style = header.Style;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.ForegroundColor     = Color.LightGreen;
            style.BackgroundColor     = Color.LightGreen;
            style.Font.Size           = 15;
            style.Font.IsBold         = true;
            header.ApplyStyle(style, headerFlag);

            sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
                                            , new string[] { "XE_CODE", "XE_NAME", "XE_COLOR", "XE_SEATS", "XE_MODEL", "XE_LICENSE_PLATE", "XE_PRICE", "XE_CONSUMPTION", "XE_NOTES"
                                                             , "XE_MAX_SPEED", "XE_MANUFACTURER", "XE_MANUFACTURER_YEAR", "XE_STATUS", "CREATED_DT", "XE_TOTAL_KM" }
                                            , false, 3, 0, listInput.Count
                                            , false
                                            , "dd/mm/yyyy"
                                            , false);


            //Set the columns to fit the size of their content
            sheet.AutoFitColumns();

            //Save the Excel workbook
            var fileName1 = sheet.Name + ".xlsx";

            workbook.Save(fileName1, SaveFormat.Xlsx);


            return(listInput);
        }