Example #1
0
        /// <summary>
        /// Sets a cell-value-based conditional formatting.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="rules">The conditional formatting rules to apply.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetCellValueConditionalFormatting(
            this Range range,
            IReadOnlyList <CellValueConditionalFormattingRule> rules)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (rules != null)
            {
                var formatConditionsIndex = range.Worksheet.ConditionalFormattings.Add();
                var formatConditions      = range.Worksheet.ConditionalFormattings[formatConditionsIndex];
                var cellArea = range.GetCellArea();
                formatConditions.AddArea(cellArea);

                foreach (var rule in rules)
                {
                    var operatorType    = rule.Operator.ToOperatorType();
                    var conditionIndex  = formatConditions.AddCondition(FormatConditionType.CellValue, operatorType, rule.Operand1Formula, rule.Operand2Formula);
                    var formatCondition = formatConditions[conditionIndex];

                    formatCondition.StopIfTrue = rule.StopIfTrue;

                    // need a way to leverage our Set... methods above to inflate the style.
                    var backgroundColor = rule.RangeStyle?.BackgroundColor;
                    if (backgroundColor != null)
                    {
                        formatCondition.Style.BackgroundColor = (Color)backgroundColor;
                    }
                }
            }
        }
Example #2
0
        /// <summary>
        /// Autofits rows for a range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="autofitRows">Use true to autofit rows.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetAutofitRows(
            this Range range,
            bool?autofitRows)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if ((autofitRows != null) && (bool)autofitRows)
            {
                var columnNumbers   = range.GetColumnNumbers();
                var minColumnNumber = columnNumbers.Min();
                var maxColumnNumber = columnNumbers.Max();
                foreach (var rowNumber in range.GetRowNumbers())
                {
                    var rowRangeCells = range.Worksheet.GetRange(rowNumber, rowNumber, minColumnNumber, maxColumnNumber).GetCells();
                    if (rowRangeCells.Any(_ => _.IsMerged))
                    {
                        var autoFitterOptions = new AutoFitterOptions {
                            AutoFitMergedCells = true
                        };
                        range.Worksheet.AutoFitRow(rowNumber - 1, minColumnNumber - 1, maxColumnNumber - 1, autoFitterOptions);
                    }
                    else
                    {
                        range.Worksheet.AutoFitRow(rowNumber - 1);
                    }
                }
            }
        }
Example #3
0
        private static void SetStyle(
            this Range range,
            StyleContainer styleContainer,
            Action <StyleContainer> configureStyleContainer)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (configureStyleContainer == null)
            {
                throw new ArgumentNullException(nameof(configureStyleContainer));
            }

            var applyToRange = styleContainer == null;

            if (styleContainer == null)
            {
                styleContainer = StyleContainer.BuildNew(range.Worksheet.Workbook);
            }

            configureStyleContainer(styleContainer);

            if (applyToRange)
            {
                styleContainer.ApplyToRange(range);
            }
        }
Example #4
0
        static void Main(string[] args)
        {
            //Instantiate a new Workbook.
            Workbook workbook = new Workbook();

            //Specify the template excel file path.
            string myPath = "Book1.xls";

            //Open the excel file.
            workbook.Open(myPath);

            //Get the range of cells i.e.., A1:C1.
            Aspose.Cells.Range rng1 = workbook.Worksheets[0].Cells.CreateRange("A1", "C1");

            //Merge the cells.
            rng1.Merge();

            Cells rng = workbook.Worksheets[0].Cells;

            //UnMerge the cell.
            rng.UnMerge(0, 0, 1, 3);

            //Save the file.
            workbook.Save("Book1.xls");
        }
Example #5
0
        private static Excel.Range GetUsedRange(Excel.Worksheet worksheet)
        {
            const int MAX_COLUMNS = 20;
            const int MAX_ROWS    = 200;

            int maxUsedRow    = 0;
            int maxUsedColumn = 0;

            Excel.Range initialRange = worksheet.Cells.CreateRange(0, 0, MAX_ROWS, MAX_COLUMNS);

            for (int counter = 0; counter < MAX_COLUMNS; counter++)
            {
                maxUsedColumn = counter;

                if (worksheet.Cells[0, counter].Value == null || worksheet.Cells[0, counter].Value.ToString().Length == 0)
                {
                    break;
                }
            }

            for (int counter = 0; counter < MAX_ROWS; counter++)
            {
                maxUsedRow = counter;

                if (worksheet.Cells[counter, 0].Value == null || worksheet.Cells[counter, 0].Value.ToString().Length == 0)
                {
                    break;
                }
            }

            Excel.Range result = worksheet.Cells.CreateRange(0, 0, maxUsedRow, maxUsedColumn);

            return(result);
        }
Example #6
0
        private void SetStyle(Cells cells, dynamic reportDefinition)
        {
            object startingRowPosition    = reportDefinition.RowPosition.Start;
            object endingRowPosition      = reportDefinition.RowPosition.End;
            string startingColumnPosition = reportDefinition.ColumnPosition.Start;
            string endingColumnPoisiton   = reportDefinition.ColumnPosition.End;
            string headerStart            = string.Concat(startingColumnPosition, startingRowPosition);
            string headerEnd     = string.Concat(endingColumnPoisiton, endingRowPosition);
            string TextAlignment = reportDefinition.TextAlign;

            cells[headerStart].PutValue(reportDefinition.content);
            Style headerStyleDefinition = _workbook.Styles[_workbook.Styles.Add()];

            headerStyleDefinition.ForegroundColor = Color.Black;
            headerStyleDefinition.Font.Name       = reportDefinition.Font.Name;
            headerStyleDefinition.Font.Size       = reportDefinition.Font.Size;
            headerStyleDefinition.Font.Color      = Color.FromName(reportDefinition.Font.Color);
            headerStyleDefinition.Font.IsBold     = true;

            headerStyleDefinition.HorizontalAlignment = TextAlignmentType.Center;
            cells[headerStart].SetStyle(headerStyleDefinition);

            if (reportDefinition.MergeCells.ToLower() == "true")
            {
                Aspose.Cells.Range rng1 = _workbook.Worksheets[0].Cells.CreateRange(headerStart, headerEnd);
                rng1.Merge();
                //int rowNmber = int.Parse(startingRowPosition.ToString());
            }
        }
Example #7
0
        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);
            }

            // Instantiate a new Workbook.
            Workbook workbook = new Workbook();

            // Get the first worksheet in the book.
            Worksheet WS = workbook.Worksheets[0];

            // Create a range of cells.
            Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18);

            // Name the range.
            range.Name = "MyRange";

            // Declare a style object.
            Style stl;

            // Create/add the style object.
            stl = workbook.CreateStyle();

            // Specify some Font settings.
            stl.Font.Name   = "Arial";
            stl.Font.IsBold = true;

            // Set the font text color
            stl.Font.Color = Color.Red;

            // To Set the fill color of the range, you may use ForegroundColor with
            // Solid Pattern setting.
            stl.ForegroundColor = Color.Yellow;
            stl.Pattern         = BackgroundType.Solid;

            // Create a StyleFlag object.
            StyleFlag flg = new StyleFlag();

            // Make the corresponding attributes ON.
            flg.Font        = true;
            flg.CellShading = true;

            // Apply the style to the range.
            range.ApplyStyle(stl, flg);

            // Save the excel file.
            workbook.Save(dataDir + "rangestyles.out.xls");
            // ExEnd:1
        }
Example #8
0
 public object GetNamedRangeValue(string NamedRangeName)
 {
     Aspose.Cells.Range range = GetNamedRange(NamedRangeName);
     if (range == null)
     {
         return(null);
     }
     return(range[0, 0].Value);
 }
Example #9
0
 public Aspose.Cells.Range GetNamedRange(string NamedRangeName)
 {
     Aspose.Cells.Range r = _workbook.Worksheets.GetRangeByName(string.Format("{0}!{1}", ActiveSheet, NamedRangeName));
     if (r == null)
     {
         r = _workbook.Worksheets.GetRangeByName(NamedRangeName);
     }
     return(r);
 }
Example #10
0
        /// <summary>
        /// Creates an auto-filter on the specified range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetAutoFilter(
            this Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            range.Worksheet.AutoFilter.Range = range.GetName();
        }
Example #11
0
        /// <summary>
        /// Applies this style container to the specified range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public void ApplyToRange(
            Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            range.ApplyStyle(this.Style, this.StyleFlag);
        }
Example #12
0
 public bool SetNamedRangeValue(string NamedRangeName, object value)
 {
     Aspose.Cells.Range range = GetNamedRange(NamedRangeName);
     if (range == null)
     {
         return(false);
     }
     range[0, 0].PutValue(value);
     return(true);
 }
        /// <summary>
        /// Gets the column numbers in the specified range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <returns>
        /// The column numbers in the range.
        /// </returns>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static IReadOnlyList <int> GetColumnNumbers(
            this Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            var result = Enumerable.Range(range.FirstColumn + 1, range.ColumnCount).ToList();

            return(result);
        }
        /// <summary>
        /// Gets the upper-left most cell in the specified range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <returns>
        /// The upper-left most cell in the specified range.
        /// </returns>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static Cell GetUpperLeftmostCell(
            this Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            var result = range.Worksheet.Cells[range.FirstRow, range.FirstColumn];

            return(result);
        }
        /// <summary>
        /// Gets the individual cell ranges within a range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <returns>
        /// The individual cell ranges within the specified range.
        /// </returns>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static IReadOnlyCollection <Range> GetCellRanges(
            this Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            var result = range.GetCells().Select(_ => _.GetRange()).ToList();

            return(result);
        }
Example #16
0
 //Quang Huy 2014-02-25
 /// <summary>
 /// tô nền cho range
 /// </summary>
 /// <param name="range"></param>
 /// <param name="color"></param>
 private void SetRangeBgColor(Aspose.Cells.Range range, System.Drawing.Color?color = null)
 {
     System.Drawing.Color c = color ?? System.Drawing.Color.White;
     //Aspose.Cells.Style s = range.Style ?? range[0, 0].GetStyle();
     Aspose.Cells.Style s = range[0, 0].GetStyle();
     s.BackgroundColor = c;
     s.Update();
     range.ApplyStyle(s, (new StyleFlag()
     {
         All = true
     }));
 }
Example #17
0
        public static DataTable GetTableFromWorkSheet(string fileName, string worksheetName)
        {
            Excel.Workbook workbook = new Excel.Workbook(fileName);

            Excel.Worksheet worksheet = workbook.Worksheets[worksheetName];

            Excel.Range range = GetUsedRange(worksheet);

            DataTable result = worksheet.Cells.ExportDataTable(0, 0, range.RowCount, range.ColumnCount, true);

            return(result);
        }
        public async Task <IActionResult> ExportExcel(string receive_Date)
        {
            var data = await _serviceCompare.GetCompare(receive_Date);

            var count = data.Count;
            var path  = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\CompareReport.xlsx");
            WorkbookDesigner designer = new WorkbookDesigner();

            designer.Workbook = new Workbook(path);
            Worksheet worksheet = designer.Workbook.Worksheets[0];

            designer.SetDataSource("result", data);
            designer.Process();

            // ----------------Add style excel------------------------//
            Style stl = designer.Workbook.CreateStyle();

            stl.ForegroundColor = Color.FromArgb(210, 105, 30);
            stl.Pattern         = BackgroundType.Solid;
            StyleFlag flg = new StyleFlag();

            flg.Font        = true;
            flg.CellShading = true;
            flg.Alignments  = true;


            for (int i = 17; i < count + 17; i++)
            {
                var checkCell1           = worksheet.Cells["K" + i].Value.ToString();
                var checkCell2           = worksheet.Cells["O" + i].Value.ToString();
                Aspose.Cells.Range range = worksheet.Cells.CreateRange(i - 1, 0, 1, 15);
                if (checkCell1 == "0" || checkCell2 == "0")
                {
                    range.ApplyStyle(stl, flg);
                }
            }
            //--------------------End add Style-------------------------//

            // for (int i = 16; i < count + 16; i++)
            // {
            //     worksheet.Cells["H"+ i].PutValue(receive_Date);
            // }
            worksheet.Cells["E2"].PutValue(data[0].Freeze_Date);

            MemoryStream stream = new MemoryStream();

            designer.Workbook.Save(stream, SaveFormat.Xlsx);

            byte[] result = stream.ToArray();

            return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
        }
Example #19
0
        /// <summary>
        /// Creates an auto-filter on the specified range and
        /// freezes the top row of that range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetAutoFilterAndFreezeTopRow(
            this Range range)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            range.SetAutoFilter();

            var cellToFreezeAt = range.Worksheet.Cells[range.GetRowNumbers().First(), 0];

            cellToFreezeAt.SetFreezePanes(PaneKinds.Row);
        }
Example #20
0
        public void FillAssetData(List <AssetModel> source, Workbook wb, int barcodeId)
        {
            Style style1 = wb.Styles[wb.Styles.Add()];                               //新增样式

            style1.IsTextWrapped = false;                                            //单元格内容自动换行
            style1.Borders[BorderType.LeftBorder].LineStyle   = CellBorderType.Thin; //应用边界线 左边界线
            style1.Borders[BorderType.RightBorder].LineStyle  = CellBorderType.Thin; //应用边界线 右边界线
            style1.Borders[BorderType.TopBorder].LineStyle    = CellBorderType.Thin; //应用边界线 上边界线
            style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
            wb.Worksheets.RemoveAt(0);                                               //移除第一个空sheet
            var groupTypes = source.Select(it => new { Id = it.TypeId, Name = it.TypeName }).Distinct();

            foreach (var type in groupTypes)
            {
                int row = 0;
                wb.Worksheets.Add(type.Name);
                var ws          = wb.Worksheets[type.Name];
                var itemsByType = source.Where(it => it.TypeId == type.Id).ToList();

                //添加表头
                var first = itemsByType.First();
                ws.Cells[row, 0].Value = "类别";
                ws.Cells[row, 1].Value = "状态";
                ws.Cells[row, 2].Value = "用户";
                int headerCols = 3;
                foreach (var attr in first.AssetInfoes)
                {
                    ws.Cells[0, headerCols].Value = attr.AttributeName;
                    headerCols += 1;
                }
                row += 1;

                //填充数据
                foreach (var item in itemsByType)
                {
                    ws.Cells[row, 0].Value = item.TypeName;
                    ws.Cells[row, 1].Value = item.StatusName;
                    ws.Cells[row, 2].Value = item.UserName;
                    int col = 3;
                    foreach (var assetInfo in item.AssetInfoes)
                    {
                        ws.Cells[row, col].Value = assetInfo.AttributeValue;
                        col += 1;
                    }
                    row += 1;
                }
                Aspose.Cells.Range r = ws.Cells.CreateRange(0, 0, row, headerCols);
                r.SetStyle(style1);
            }
        }
Example #21
0
        /// <summary>
        /// Sets a data validation.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="dataValidation">The validation styling to apply.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetDataValidation(
            this Range range,
            DataValidation dataValidation)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (dataValidation != null)
            {
                var validations = range.Worksheet.Validations;
                var cellArea    = range.GetCellArea();
                var validation  = validations[validations.Add(cellArea)];

                validation.Type     = dataValidation.Kind.ToValidationType();
                validation.Operator = dataValidation.Operator.ToOperatorType();

                if (dataValidation.Operand1Value != null)
                {
                    validation.Value1 = dataValidation.Operand1Value;
                }

                if (dataValidation.Operand2Value != null)
                {
                    validation.Value2 = dataValidation.Operand2Value;
                }

                if (dataValidation.Operand1Formula != null)
                {
                    validation.Formula1 = dataValidation.Operand1Formula;
                }

                if (dataValidation.Operand2Formula != null)
                {
                    validation.Formula2 = dataValidation.Operand2Formula;
                }

                validation.IgnoreBlank    = dataValidation.IgnoreBlank;
                validation.ShowInput      = dataValidation.ShowInputMessage;
                validation.InputTitle     = dataValidation.InputMessageTitle;
                validation.InputMessage   = dataValidation.InputMessageBody;
                validation.ShowError      = dataValidation.ShowErrorAlertAfterInvalidDataIsEntered;
                validation.AlertStyle     = dataValidation.ErrorAlertStyle.ToValidationAlertType();
                validation.ErrorTitle     = dataValidation.ErrorAlertTitle;
                validation.ErrorMessage   = dataValidation.ErrorAlertBody;
                validation.InCellDropDown = dataValidation.ShowListDropdown;
            }
        }
Example #22
0
        /// <summary>
        /// Sets the outside border of a range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="border">The border.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetOutsideBorder(
            this Range range,
            Border border)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (border != null)
            {
                var borderEdges = border.Edges.ToBorderType();
                var borderStyle = border.Style.ToCellBorderType();
                range.SetOutlineBorder(borderEdges, borderStyle, border.Color);
            }
        }
Example #23
0
        public static void Run()
        {
            // Instantiate a new Workbook.
            Workbook workbook = new Workbook();

            // Get the first worksheet in the book.
            Worksheet WS = workbook.Worksheets[0];

            // Create a range of cells.
            Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 5, 5);

            // Name the range.
            range.Name = "MyRange";

            // Declare a style object.
            Style stl;

            // Create/add the style object.
            stl = workbook.CreateStyle();

            // Specify some Font settings.
            stl.Font.Name   = "Arial";
            stl.Font.IsBold = true;

            // Set the font text color
            stl.Font.Color = Color.Red;

            // To Set the fill color of the range, you may use ForegroundColor with
            // Solid Pattern setting.
            stl.ForegroundColor = Color.Yellow;
            stl.Pattern         = BackgroundType.Solid;

            // Create a StyleFlag object.
            StyleFlag flg = new StyleFlag();

            // Make the corresponding attributes ON.
            flg.Font        = true;
            flg.CellShading = true;

            // Apply the style to the range.
            range.ApplyStyle(stl, flg);

            // Save the excel file.
            workbook.Save(outputDir + "outputFormatRanges1.xlsx");

            Console.WriteLine("FormatRanges1 executed successfully.");
        }
Example #24
0
        /// <summary>
        /// Sets the per-row height for a range, in pixels.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="rowHeightInPixels">The row height, in pixels.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetPerRowHeightInPixels(
            this Range range,
            int?rowHeightInPixels)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (rowHeightInPixels != null)
            {
                foreach (var rowNumber in range.GetRowNumbers())
                {
                    range.Worksheet.Cells.SetRowHeightPixel(rowNumber - 1, (int)rowHeightInPixels);
                }
            }
        }
Example #25
0
        /// <summary>
        /// Sets the per-column width for a range, in pixels.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="columnWidthInPixels">The column width, in pixels.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetPerColumnWidthInPixels(
            this Range range,
            int?columnWidthInPixels)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (columnWidthInPixels != null)
            {
                foreach (var columnNumber in range.GetColumnNumbers())
                {
                    range.Worksheet.Cells.SetColumnWidthPixel(columnNumber - 1, (int)columnWidthInPixels);
                }
            }
        }
Example #26
0
        /// <summary>
        /// Groups the rows in the range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="collapseGroup">Optional value indicating whether to collapse the group.  Default is false; the group will be expanded.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetGroupRows(
            this Range range,
            bool collapseGroup = false)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            var rowNumbers = range.GetRowNumbers();

            if (rowNumbers.Count == 1)
            {
                throw new ArgumentOutOfRangeException(nameof(range), "There are too few rows specified in the range.  To group rows, at least 2 rows must be specified.");
            }

            range.Worksheet.Cells.GroupRows(rowNumbers.First() - 1, rowNumbers.Last() - 1 - 1, collapseGroup);
        }
Example #27
0
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool SaveExcel(DataTable dt, string fileName)
        {
            Workbook  workbooks = new Workbook();
            Worksheet worksheet = workbooks.Worksheets[0];
            Style     style     = CreateStyle(workbooks);

            try
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    worksheet.Cells[0, j].PutValue(dt.Columns[j].ColumnName);
                    worksheet.Cells[0, j].SetStyle(style);
                }
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        worksheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
                        worksheet.Cells[r + 1, c].SetStyle(style);
                    }
                }

                var s = worksheet.Cells[1, 1].GetStyle();
                s.ForegroundColor = System.Drawing.Color.FromArgb(216, 228, 188);
                s.Pattern         = BackgroundType.Solid;
                s.Font.IsBold     = true;


                Aspose.Cells.Range rang = worksheet.Cells.CreateRange(0, 0, 1, dt.Columns.Count);
                rang.SetStyle(s);
                //Aspose.Cells.Range rang1 = worksheet.Cells.CreateRange(0, 0, dt.Rows.Count, 1);
                //rang1.SetStyle(s);

                worksheet.AutoFitColumns();
                worksheet.FreezePanes(1, 0, dt.Rows.Count, dt.Columns.Count);

                workbooks.Save(fileName);
            }
            catch (Exception ex)
            {
                return(false);
            }
            return(true);
        }
Example #28
0
        /// <summary>
        /// Sets the indent level for a range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="indentLevel">The indent level.</param>
        /// <param name="styleContainer">The style container.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetIndentLevel(
            this Range range,
            int?indentLevel,
            StyleContainer styleContainer = null)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (indentLevel != null)
            {
                range.SetStyle(styleContainer, _ =>
                {
                    _.Style.IndentLevel = (int)indentLevel;
                    _.StyleFlag.Indent  = true;
                });
            }
        }
Example #29
0
        /// <summary>
        /// Sets the horizontal alignment of a range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="horizontalAlignment">The horizontal alignment.</param>
        /// <param name="styleContainer">The style container.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetHorizontalAlignment(
            this Range range,
            HorizontalAlignment?horizontalAlignment,
            StyleContainer styleContainer = null)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (horizontalAlignment != null)
            {
                range.SetStyle(styleContainer, _ =>
                {
                    _.Style.HorizontalAlignment     = ((HorizontalAlignment)horizontalAlignment).ToTextAlignmentType();
                    _.StyleFlag.HorizontalAlignment = true;
                });
            }
        }
Example #30
0
        /// <summary>
        /// Sets the total width for a range, in pixels.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="totalWidthInPixels">The total width, in pixels.</param>
        /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
        public static void SetTotalColumnWidthInPixels(
            this Range range,
            int?totalWidthInPixels)
        {
            if (range == null)
            {
                throw new ArgumentNullException(nameof(range));
            }

            if (totalWidthInPixels != null)
            {
                var columnNumbers = range.GetColumnNumbers();
                var pixelsToUse   = DivideEvenly((int)totalWidthInPixels, columnNumbers.Count).ToArray();
                for (var x = 0; x < columnNumbers.Count; x++)
                {
                    range.Worksheet.Cells.SetColumnWidthPixel(columnNumbers[x] - 1, pixelsToUse[x]);
                }
            }
        }