internal void ArrangeInsertCols(TXlsCellRange CellRange, int aColCount, TSheetInfo SheetInfo) { if (SheetInfo.SourceFormulaSheet != SheetInfo.InsSheet) { return; } int ColOffset = CellRange.ColCount * aColCount; if (aColCount < 0) //Deleting columns. ColOffset is < 0. { DeleteColumns(CellRange.Left, -ColOffset); } else { //the check below might throw unwanted exceptions when all columns are formatted (even with fake formatting) //and disallow to insert columns on some sheets. (for example all pxl files have the full 255 columns formatted) //so we will allow to "lose" formatted columns if there is no data on them. //if (LastColumn+ColOffset>FlxConsts.Max_Columns) XlsMessages.ThrowException(XlsErr.ErrTooManyColumns, LastColumn + ColOffset + 1, FlxConsts.Max_Columns+1); if (CellRange.Left + ColOffset > FlxConsts.Max_Columns) { XlsMessages.ThrowException(XlsErr.ErrTooManyColumns, CellRange.Left + ColOffset + 1, FlxConsts.Max_Columns + 1); } InsertColumns(CellRange.Left, ColOffset); } }
internal void InsertAndCopyRange(TXlsCellRange SourceRange, TFlxInsertMode InsertMode, int DestRow, int DestCol, int aRowCount, int aColCount, TSheetInfo SheetInfo) { FNames.ArrangeInsertRange(SourceRange.OffsetForIns(DestRow, DestCol, InsertMode), aRowCount, aColCount, SheetInfo); #if (FRAMEWORK30 && !COMPACTFRAMEWORK) FXlsxPivotCache.ArrangeInsertRange(SourceRange.OffsetForIns(DestRow, DestCol, InsertMode), aRowCount, aColCount, SheetInfo); #endif }
internal void MoveRange(TXlsCellRange CellRange, int NewRow, int NewCol, TSheetInfo SheetInfo) { FNames.ArrangeMoveRange(CellRange, NewRow, NewCol, SheetInfo); #if (FRAMEWORK30 && !COMPACTFRAMEWORK) FXlsxPivotCache.ArrangeMoveRange(CellRange, NewRow, NewCol, SheetInfo); #endif }
private void writeHyperLinks_Click(object sender, System.EventArgs e) { if (Xls == null) { MessageBox.Show("You need to open a file first."); return; } ExcelFile XlsOut = new XlsFile(true); XlsOut.NewFile(1, TExcelFileFormat.v2019); for (int i = 1; i <= Xls.HyperLinkCount; i++) { TXlsCellRange Range = Xls.GetHyperLinkCellRange(i); THyperLink HLink = Xls.GetHyperLink(i); int XF = -1; object Value = Xls.GetCellValue(Range.Top, Range.Left, ref XF); XlsOut.SetCellValue(i, 1, Value, XlsOut.AddFormat(Xls.GetFormat(XF))); XlsOut.AddHyperLink(new TXlsCellRange(i, 1, i, 1), HLink); } if (saveFileDialog1.ShowDialog() != DialogResult.OK) { return; } XlsOut.Save(saveFileDialog1.FileName); if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes) { Process.Start(saveFileDialog1.FileName); } }
public override TFlxPartialFormat Evaluate(ExcelFile workbook, TXlsCellRange rangeToFormat, object[] parameters) { //Again, this example is not supposed to make sense, only to show how you can code a complex rule. //This method will format the rows with a color that depends in the length of the first parameter, //and if the second parameter starts with "B" it will make the text red. if (parameters == null || parameters.Length != 2) { throw new ArgumentException("Bad parameter count in call to ShipFormat() user-defined format"); } int len = Convert.ToString(parameters[0]).Length; string country = Convert.ToString(parameters[1]); Int32 color = 0xFFFFFF - len * 100; TFlxFormat fmt = workbook.GetDefaultFormat; fmt.FillPattern.Pattern = TFlxPatternStyle.Solid; fmt.FillPattern.FgColor = TExcelColor.FromArgb(color); fmt.FillPattern.BgColor = TExcelColor.Automatic; TFlxApplyFormat apply = new TFlxApplyFormat(); apply.FillPattern.SetAllMembers(true); if (country.StartsWith("B")) { fmt.Font.Color = Colors.OrangeRed; apply.Font.Color = true; } return(new TFlxPartialFormat(fmt, apply, false)); }
internal void ArrangeMoveRange(TXlsCellRange CellRange, int NewRow, int NewCol, TSheetInfo SheetInfo) { for (int i = 0; i < Count; i++) { this[i].ArrangeMoveRange(CellRange, NewRow, NewCol, SheetInfo); } }
/// <summary> /// Creates a new FlexCelImgExport instance. /// </summary> public FlexCelImgExport() { FRenderer = new FlexCelRender(); FPrintRange = new TXlsCellRange(0, 0, 0, 0); PageSize = null; Resolution = 96; }
internal static void ArrangeInsertRange(TXlsCellRange Refe, TXlsCellRange CellRange, int aRowCount, int aColCount) { InsertFirst(ref Refe.Top, CellRange.Top, FlxConsts.Max_Rows, CellRange.RowCount, aRowCount, XlsErr.ErrTooManyRows); InsertLast(ref Refe.Bottom, CellRange.Bottom, FlxConsts.Max_Rows, CellRange.RowCount, aRowCount); InsertFirst(ref Refe.Left, CellRange.Left, FlxConsts.Max_Columns, CellRange.ColCount, aColCount, XlsErr.ErrTooManyColumns); InsertLast(ref Refe.Right, CellRange.Right, FlxConsts.Max_Columns, CellRange.ColCount, aColCount); }
private static void PartialSort(TKeyList Items, ExcelFile xls, TXlsCellRange Range, bool ByRows, int FirstRow, int StagingRow) { int Slot = FirstRow - Range.Top; int LastSlot = -1; int destRow = StagingRow; int Row = FirstRow; do { if (Row == destRow) { FlxMessages.ThrowException(FlxErr.ErrInternal); } MoveRows(xls, Range, ByRows, Row, destRow); if (LastSlot >= 0) { Items[LastSlot].Position = destRow; } int MovedFrom = Row; if (Slot == Items.Count) { break; //we are moving from the staging area. } Row = Items[Slot].Position; if (Row == FirstRow) { Row = StagingRow; } LastSlot = Slot; Slot = Row - Range.Top; destRow = MovedFrom; } while (true); }
internal void ArrangeMoveRange(TXlsCellRange CellRange, int NewRow, int NewCol, TSheetInfo SheetInfo) { if (Source != null) { Source.ArrangeMoveRange(CellRange, NewRow, NewCol, SheetInfo); } }
internal static void Write(TextWriter OutString, ExcelFile Workbook, char Delim, TXlsCellRange Range, bool ExportHiddenRowsOrColumns) { if (Range == null) { Range = new TXlsCellRange(1, 1, Workbook.RowCount, Workbook.GetColCount(Workbook.ActiveSheet, false)); } for (int r = Range.Top; r <= Range.Bottom; r++) { if (!ExportHiddenRowsOrColumns && Workbook.GetRowHidden(r)) { continue; } for (int c = Range.Left; c <= Range.Right; c++) { if (!ExportHiddenRowsOrColumns && Workbook.GetColHidden(c)) { continue; } string s = Workbook.GetStringFromCell(r, c).ToString(); if ((s.IndexOf(Delim) >= 0) || (s.IndexOf('"') >= 0) || (s.IndexOf("\r") > 0) || (s.IndexOf("\n") > 0)) { s = QuotedStr(s, "\""); } OutString.Write(s); if (c < Range.Right) { OutString.Write(Delim); } else { OutString.Write(TCompactFramework.NewLine); } } } }
internal void ArrangeInsertRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo) { if (Source != null) { Source.ArrangeInsertRange(CellRange, aRowCount, aColCount, SheetInfo); } }
internal void ArrangeMoveRange(TXlsCellRange CellRange, int NewRow, int NewCol, TSheetInfo SheetInfo) { foreach (TRangeSet rs in RangeSets) { rs.ArrangeMoveRange(CellRange, NewRow, NewCol, SheetInfo); } }
internal void ArrangeInsertRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo) { foreach (TRangeSet rs in RangeSets) { rs.ArrangeInsertRange(CellRange, aRowCount, aColCount, SheetInfo); } }
protected override void CalcIncludedRangeRecords(TXlsCellRange CellRange, ref int FirstRecord, ref int RecordCount) { base.CalcIncludedRangeRecords(CellRange, ref FirstRecord, ref RecordCount); int LastRecord = -1; for (int i = 0; i < VirtualCount; i++) { if ((FirstRecord < 0) && (FList[i].Col >= CellRange.Left)) { FirstRecord = i; } if (FList[i].Col <= CellRange.Right) { LastRecord = i; } } if ((FirstRecord >= 0) && (LastRecord >= 0) && (FirstRecord <= LastRecord)) { RecordCount = LastRecord - FirstRecord + 1; } else { FirstRecord = 0; RecordCount = 0; } }
private static void FillRowRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer) { for (int r = Range.Top; r <= Range.Bottom; r++) { object[] Values; if (Keys == null) { int Len = Range.ColCount; if (Len > 8) { Len = 8; //avoid taking up too much memory. } Values = new object[Len]; for (int c = 0; c < Values.Length; c++) { Values[c] = xls.GetCellValue(r, Range.Left + c); } } else { Values = new object[Keys.Length]; for (int c = 0; c < Keys.Length; c++) { Values[c] = xls.GetCellValue(r, Keys[c]); } } Items.Add(new TKeyItem(r, Values, SortOrder, Comparer)); } }
internal void ArrangeInsertRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo, bool Forced) { for (int i = 0; i < Count; i++) { this[i].ArrangeInsertRange(CellRange, aRowCount, aColCount, SheetInfo, Forced); } }
private static void FillColRange(TKeyList Items, XlsFile xls, TXlsCellRange Range, int[] Keys, TSortOrder[] SortOrder, IComparer Comparer) { for (int c = Range.Left; c <= Range.Right; c++) { object[] Values; if (Keys == null) { int Len = Range.RowCount; if (Len > 8) { Len = 8; //avoid taking up too much memory. } Values = new object[Len]; for (int r = 0; r < Values.Length; r++) { Values[r] = xls.GetCellValue(Range.Top + r, c); } } else { Values = new object[Keys.Length]; for (int r = 0; r < Keys.Length; r++) { Values[r] = xls.GetCellValue(Keys[r], c); } } Items.Add(new TKeyItem(c, Values, SortOrder, Comparer)); } }
internal void SaveToPxl(TPxlStream PxlStream, TPxlSaveData SaveData) { if (ActiveRow > FlxConsts.Max_PxlRows) { return; } if (ActiveCol > FlxConsts.Max_PxlColumns) { return; } TXlsCellRange Selection = new TXlsCellRange(ActiveRow, ActiveCol, ActiveRow, ActiveCol); if (SelectedCells != null && SelectedCells.Length > 0) { TXlsCellRange Selection2 = SelectedCells[0]; if (Selection2.Left >= 0 && Selection2.Right <= FlxConsts.Max_PxlColumns && Selection2.Left <= Selection2.Right) { if (Selection2.Top >= 0 && Selection2.Bottom <= FlxConsts.Max_PxlRows && Selection2.Top <= Selection2.Bottom) { Selection = Selection2; } } } PxlStream.WriteByte((byte)pxl.SELECTION); PxlStream.Write16((UInt16)Selection.Top); PxlStream.WriteByte((byte)Selection.Left); PxlStream.Write16((UInt16)Selection.Bottom); PxlStream.WriteByte((byte)Selection.Right); PxlStream.Write16((UInt16)ActiveRow); PxlStream.WriteByte((byte)ActiveCol); }
internal void SetCellRange(TXlsCellRange CellRange) { FirstRow = CellRange.Top; FirstCol = CellRange.Left; LastRow = CellRange.Bottom; LastCol = CellRange.Right; }
private void ReadHyperLinks_Click(object sender, System.EventArgs e) { if (openFileDialog1.ShowDialog() != DialogResult.OK) { return; } Xls = new XlsFile(); Xls.Open(openFileDialog1.FileName); dataGrid.CaptionText = "Hyperlinks on file: " + openFileDialog1.FileName; HlDataTable.Rows.Clear(); for (int i = 1; i <= Xls.HyperLinkCount; i++) { TXlsCellRange Range = Xls.GetHyperLinkCellRange(i); THyperLink HLink = Xls.GetHyperLink(i); string HLinkType = Enum.GetName(typeof(THyperLinkType), HLink.LinkType); object[] values = { i, TCellAddress.EncodeColumn(Range.Left) + Range.Top.ToString(), TCellAddress.EncodeColumn(Range.Right) + Range.Bottom.ToString(), HLinkType, HLink.Text, HLink.Description, HLink.TextMark, HLink.TargetFrame, HLink.Hint }; HlDataTable.Rows.Add(values); } }
internal void ArrangeInsertRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo) { //Hyperlink data doesn't move when you insert/copy cells or sheets. It is a static text. if ((SheetInfo.InsSheet < 0) || (SheetInfo.SourceFormulaSheet != SheetInfo.InsSheet)) { return; } if (aRowCount != 0 && FirstCol >= CellRange.Left && LastCol <= CellRange.Right) { if (FirstRow >= CellRange.Top) { BitOps.IncWord(ref FirstRow, aRowCount * CellRange.RowCount, FlxConsts.Max_Rows, XlsErr.ErrTooManyRows); //firstrow; } if (LastRow >= CellRange.Top) { BitOps.IncWord(ref LastRow, aRowCount * CellRange.RowCount, FlxConsts.Max_Rows, XlsErr.ErrTooManyRows); // lastrow; } } if (aColCount != 0 && FirstRow >= CellRange.Top && LastRow <= CellRange.Bottom) { if (FirstCol >= CellRange.Left) { BitOps.IncWord(ref FirstCol, aColCount * CellRange.ColCount, FlxConsts.Max_Columns, XlsErr.ErrTooManyColumns); //firstcol; } if (LastCol >= CellRange.Left) { BitOps.IncWord(ref LastCol, aColCount * CellRange.ColCount, FlxConsts.Max_Columns, XlsErr.ErrTooManyColumns); //lastcol; } } }
internal TBand(TFlexCelDataSource aDataSource, TBand aMasterBand, TXlsCellRange aCellRange, string aName, TBandType aBandType, bool aDeleteLastRow, string aDataSourceName) : this(aDataSource, aMasterBand, aMasterBand, aCellRange, aName, aBandType, aDeleteLastRow, aDataSourceName) { //On an normal band, Searchband=Masterband. The only case this doesn't happen is on Sheet bands, //where we search on all other sheets but they are not in master/detail relationship. }
internal void DeleteRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo) { int aCount = Count; for (int i = aCount - 1; i >= 0; i--) { THLinkRecord r = this[i]; int bRowCount = aRowCount - 1; if (bRowCount < 0) { bRowCount = 0; } int bColCount = aColCount - 1; if (bColCount < 0) { bColCount = 0; } if (r.FirstRow >= CellRange.Top && r.LastRow <= CellRange.Bottom + CellRange.RowCount * bRowCount && r.FirstCol >= CellRange.Left && r.LastCol <= CellRange.Right + CellRange.ColCount * bColCount) { FList.RemoveAt(i); } else { r.ArrangeInsertRange(CellRange, -aRowCount, -aColCount, SheetInfo); if (r.LastRow < r.FirstRow) { FList.RemoveAt(i); } } } }
public override TFlxPartialFormat Evaluate(ExcelFile workbook, TXlsCellRange rangeToFormat, object[] parameters) { if (parameters == null || parameters.Length != 1) { throw new ArgumentException("Bad parameter count in call to ZipCode() user-defined format"); } int color; //If the zip code is not valid, don't modify the format. if (parameters[0] == null || !int.TryParse(Convert.ToString(parameters[0]), out color)) { return(new TFlxPartialFormat(null, null, false)); } //This code is not supposed to make sense. We will convert the zip code to a color based in the numeric value. TFlxFormat fmt = workbook.GetDefaultFormat; fmt.FillPattern.Pattern = TFlxPatternStyle.Solid; fmt.FillPattern.FgColor = TExcelColor.FromArgb(color); fmt.FillPattern.BgColor = TExcelColor.Automatic; fmt.Font.Color = TExcelColor.FromArgb(~color); TFlxApplyFormat apply = new TFlxApplyFormat(); apply.FillPattern.SetAllMembers(true); apply.Font.Color = true; return(new TFlxPartialFormat(fmt, apply, false)); }
internal void MoveRange(TXlsCellRange CellRange, int NewRow, int NewCol, TSheetInfo SheetInfo) { if ((SheetInfo.InsSheet < 0) || (SheetInfo.SourceFormulaSheet != SheetInfo.InsSheet)) { return; } int aCount = Count; for (int i = aCount - 1; i >= 0; i--) { THLinkRecord r = this[i]; if (r.FirstRow >= CellRange.Top && r.LastRow <= CellRange.Bottom && r.FirstCol >= CellRange.Left && r.LastCol <= CellRange.Right) { //Hyperlink data doesn't move when you insert/copy cells or sheets. It is a static text. r.Offset(NewRow - CellRange.Top, NewCol - CellRange.Left); } else { if (r.FirstRow >= NewRow && r.LastRow <= NewRow + CellRange.RowCount - 1 && r.FirstCol >= NewCol && r.LastCol <= NewCol + CellRange.ColCount - 1) { //Hyperlink data doesn't move when you insert/copy cells or sheets. It is a static text. FList.RemoveAt(i); } } } }
internal void DeleteRange(TXlsCellRange CellRange, int aRowCount, int aColCount, TSheetInfo SheetInfo) { FNames.ArrangeInsertRange(CellRange, -aRowCount, -aColCount, SheetInfo); #if (FRAMEWORK30 && !COMPACTFRAMEWORK) FXlsxPivotCache.ArrangeInsertRange(CellRange, -aRowCount, -aColCount, SheetInfo); #endif }
internal void SaveRangeToStream(IDataStream DataStream, TSaveData SaveData, TXlsCellRange CellRange) { int FirstRecord = 0; int RecordCount = 0; CalcIncludedRangeRecords(CellRange, ref FirstRecord, ref RecordCount); if (RecordCount > MaxPageBreaks) { if (SaveData.ThrowExceptionOnTooManyPageBreaks) { XlsMessages.ThrowException(XlsErr.ErrTooManyPageBreaks); } else { RecordCount = MaxPageBreaks; } if (FlexCelTrace.Enabled) { FlexCelTrace.Write(new TXlsTooManyPageBreaksError(XlsMessages.GetString(XlsErr.ErrTooManyPageBreaks), DataStream.FileName)); } } SaveToStreamExt(DataStream, SaveData, FirstRecord, RecordCount); }
internal override void Execute(ExcelFile Workbook, TWaitingCoords Coords, TBand Band) { int t, l, b, r; GetBounds(Coords, out t, out l, out b, out r); TXlsCellRange rangeToDelete = new TXlsCellRange(t, l, b, r); if (Sheet1 <= 0 || Sheet2 <= 0) { Workbook.DeleteRange(rangeToDelete, InsertMode); } else { Workbook.DeleteRange(Sheet1, Sheet2, rangeToDelete, InsertMode); } if (Sheet1 <= 0 || Sheet2 <= 0 || (Sheet1 <= Workbook.ActiveSheet && Sheet2 >= Workbook.ActiveSheet)) { if (Band != null) { if (InsertMode == TFlxInsertMode.ShiftRangeDown || InsertMode == TFlxInsertMode.ShiftRowDown) { Band.AddTmpExpandedRows(-rangeToDelete.RowCount, Left + Coords.ColOfs, Left + Coords.ColOfs + rangeToDelete.ColCount); Band.TmpPartialRows += -rangeToDelete.RowCount; } else if (InsertMode == TFlxInsertMode.ShiftColRight || InsertMode == TFlxInsertMode.ShiftRangeRight) { Band.AddTmpExpandedCols(-rangeToDelete.ColCount, Top + Coords.RowOfs, Top + Coords.RowOfs + rangeToDelete.RowCount); Band.TmpPartialCols += -rangeToDelete.ColCount; } } } }
internal long TotalSizeNoStdWidth(TXlsCellRange CellRange) { int Start = CellRange == null ? 0 : CellRange.Left; int Finish = CellRange == null ? FlxConsts.Max_Columns97_2003 + 1 : CellRange.Right; return(SaveRangeEx(null, new TSaveData(), Start, Finish)); }