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));
}
