Copy range with validation

I am using the code below to duplicate a range.

This works fine, almost. Column widths, formatting and values are carried over. Validation, however, does not.

In the attached Xlsx, there are two cells with data validation (yellow). When running the code on this document, the result will have lost the validation on the cells.

Thanks!
/Fredrik
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Diagnostics;

namespace Infoweaver.Forms.AsposeTest
{
class Duplicate2
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “Duplicate2.xlsx”);
designer.Workbook = workbook;

Range range = workbook.Worksheets.GetRangeByName(“DUP_Project”);
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();

// We need to remember all column widths to the right of the range, since inserting ranges reset the columns widths to the right
Cells cells = range.Worksheet.Cells;
List columnWidths = new List();
for (int columnIndex = range.FirstColumn + range.ColumnCount; columnIndex <= cells.MaxDataColumn; columnIndex++)
{
Column column = cells.Columns[columnIndex];
columnWidths.Add(column.Width);
}

for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
DataRow row = dt.Rows[rowIndex];
Range newRange = worksheet.Cells.CreateRange(range.FirstRow, range.FirstColumn + range.ColumnCount, range.RowCount, range.ColumnCount);

if (rowIndex < dt.Rows.Count - 1)
{
// Create a new range…
InsertRange(range);

// …and paste the current range into the new range
PasteOptions pos = new PasteOptions();
pos.PasteType = PasteType.ColumnWidths;
// We first must paste the column widths…
newRange.Copy(range, pos);

pos.PasteType = PasteType.Validation;
// …then the validation…this does not work, however…
newRange.Copy(range, pos);

// …then the actual values
newRange.Copy(range);
}

range = newRange;
}

// Reset the widths of the columns to the right
for (int i = 0; i < columnWidths.Count; i++)
{
Column column = cells.Columns[i + range.FirstColumn];
column.Width = columnWidths[i];
}

string output = Constants.destPath + “Duplicate2_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}

private static void InsertRange2(Range range)
{

Cells cells = range.Worksheet.Cells;

//CellArea ca = new CellArea();

//ca.StartRow = range.FirstRow;

//ca.EndRow = cells.MaxRow;

//ca.StartColumn = range.FirstColumn + range.ColumnCount;

//ca.EndColumn = cells.MaxColumn;

// cells.InsertRange(ca, range.ColumnCount, ShiftType.Right, true);

cells.InsertColumns(range.FirstColumn, range.ColumnCount);

}

private static void InsertRange(Range range)
{
Cells cells = range.Worksheet.Cells;
CellArea ca = new CellArea();
ca.StartRow = range.FirstRow;
ca.EndRow = cells.MaxRow;
ca.StartColumn = range.FirstColumn + range.ColumnCount;
ca.EndColumn = cells.MaxColumn;
cells.InsertRange(ca, range.ColumnCount, ShiftType.Right, true);
}

private static DataTable CreateValidTable1()
{
DataTable dt = new DataTable(“MyDataSource”);
dt.Columns.Add(“Id”, typeof(int));
dt.Columns.Add(“Additional”, typeof(string));
dt.Rows.Add(1001, “Additional1”);
dt.Rows.Add(1002, “Additional2”);
dt.Rows.Add(1003, “Additional3”);
// dt.Rows.Add(“val2”, “val2”, 4);
//dt.Rows.Add(“val3”, “val3”, 4);
return dt;
}
}
}

Hi,

I have run your code and generated the output xlsx file attached by me. I have seen the validation has broken.

I have used the latest version:
Aspose.Cells for .NET v7.0.1.6

We have logged this bug in our database. Once the issue is fixed, we will let you know.

This issue has been logged as CELLSNET-31243.


This is strange: I received an email (topic reply) from you (mshakeel.faiz), but that doesn't show up here in the topic thread.

The email is this:

"Hi,

I have run your code and generated the output xlsx file attached by me. Please check if it is fine. If it has problems then please highlight your problems in a screenshot with red circles.

I have used the latest version: Aspose.Cells for .NET v7.0.1.6 "

So, are you still considering to fix this, or are you awaiting a reply from me? If the latter, please supply the output xlsx-file.

Thanks!

/Fredrik


Hi,


After an initial test, we did find the issue as you mentioned using your sample code and template file. Once we figure it out, we will let you know.

Thank you.

That's good news!

This is a show-stopper for us. Do you have an estimate when this can be fixed?

Thanks!

/Fredrik

Hi,


We will check if we can provide you an eta for the fix. Normally if an issue is complicated, then we provide an eta for the issue/feature, otherwise, you should expect a fix within 3-5 days.

Keep in touch.

Thank you.


Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v7.0.1.7

The issues you have found earlier (filed as 31243) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Hi,


Please try this fixed version v7.0.2.6.

a) We have supported to move the data validation when inserting range.

b) Please change your codes about inserting range, see the following demo code:

Range range1 = workbook.Worksheets.GetRangeByName("Range1");

int cols = range1.ColumnCount;

for (int i = 0; i < 3; i++)

{

CellArea ca = new CellArea();

ca.StartRow = range1.FirstRow + 1;

ca.StartColumn = range1.FirstColumn;

ca.EndRow = ca.StartRow;

ca.EndColumn = cols;

cells.InsertRange(ca, ShiftType.Down);

}

Because the cells.MaxColumn is 1, Range2 could not be updated when inserting range (whose end column is 1);

Thank you.

This works better, thanks!


However, there seem to have been same changes to custom formula calculation with this version.

Two problems:
1. With the custom formula “=IW_VHLOOKUP(0;A9;“Investment”)”, the code will somewhere in Aspose crash with "Object reference not set to an instance of an object."

2. It seems as if it is the zero as the first parameter which causes the problem. If I remove this parameter, i.e. “=IW_VHLOOKUP(A9;“Investment”)”, the code works.
However, in the call to my implementation of CalculateCustomFunction, the passed contextObjects looks different. In version v7.0.2.6, there is an extra item - “ReferredAreaCollection”. This item is not present in v7.0.2.0.
My code checks that the number of items in contextObjects is correct, which makes my code crash. This is no big problem, I can relax my checking, but it doesn’t feel great that you introduce new items in the parameters. It’s a little like breaking the contract.

Anyways, the 1st problem is currently blocking us. Can you have a look at it?

The code as follows, the template is attached.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Diagnostics;
using System.Collections;

namespace Infoweaver.Forms.AsposeTest
{
class Duplicate3
{
private WorkbookDesigner wbd;

private Range GetRange(Workbook wb, string rangeName, bool throwIfNotFound)
{
Range range = wb.Worksheets.GetRangeByName(rangeName);
if (range == null && throwIfNotFound)
{
throw new ApplicationException(string.Format(“No range exists with name ‘{0}’”, rangeName));
}

return range;
}

public enum RangeRepeatDirection
{
Horizontal,
Vertical
}

///
/// Used to specify repeating of a range.
///
public class RepeatRange
{
///
/// The name of the name Range that should be repeated
///
public string RangeName = null;
///
/// The direction of repeating
///
public RangeRepeatDirection RepeatDirection = RangeRepeatDirection.Horizontal;
}



private void PerformRangeRepeat(DataTable tbl, RepeatRange repeatRange)
{
Range range = GetRange(wbd.Workbook, repeatRange.RangeName, true);
Cells cells = range.Worksheet.Cells;

List columnWidths = new List();

if (repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal)
{
// We need to remember all column widths to the right of the range, since inserting ranges reset the columns widths to the right
for (int columnIndex = range.FirstColumn + range.ColumnCount; columnIndex <= cells.MaxDataColumn; columnIndex++)
{
Aspose.Cells.Column column = cells.Columns[columnIndex];
columnWidths.Add(column.Width);
}
}

for (int rowIndex = 0; rowIndex < tbl.Rows.Count; rowIndex++)
{
DataRow row = tbl.Rows[rowIndex];
int rowOffset = repeatRange.RepeatDirection == RangeRepeatDirection.Vertical ? range.RowCount : 0;
int columnOffset = repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal ? range.ColumnCount : 0;
Range newRange = cells.CreateRange(range.FirstRow + rowOffset, range.FirstColumn + columnOffset, range.RowCount, range.ColumnCount);

if (rowIndex < tbl.Rows.Count - 1)
{
ShiftType shiftType = repeatRange.RepeatDirection == RangeRepeatDirection.Vertical ? ShiftType.Down : ShiftType.Right;
InsertRange(range, rowOffset, columnOffset, shiftType);

PasteOptions pos = new PasteOptions();
pos.PasteType = PasteType.ColumnWidths; // For some reason, we must first copy the column widths…
newRange.Copy(range, pos);
newRange.Copy(range); // …and then the actual values
}

ReplaceTags(cells, range, row);
range = newRange;
}

if (repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal)
{
// Reset the widths of the columns to the right
for (int i = 0; i < columnWidths.Count; i++)
{
Aspose.Cells.Column column = cells.Columns[i + range.FirstColumn];
column.Width = columnWidths[i];
}
}
}

private static void ReplaceTags(Cells cells, Range range, DataRow row)
{
}

private static void InsertRange(Range range, int rowOffset, int columnOffset, ShiftType shiftType)
{
Cells cells = range.Worksheet.Cells;
CellArea ca = new CellArea();
ca.StartRow = range.FirstRow + rowOffset;
ca.EndRow = ca.StartRow + range.RowCount - 1;
ca.StartColumn = range.FirstColumn + columnOffset;
ca.EndColumn = ca.StartColumn + range.ColumnCount - 1;
cells.InsertRange(ca, rowOffset + columnOffset, shiftType, true);
}


public void DoIt()
{
wbd = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “Duplicate3.xlsx”);
wbd.Workbook = workbook;

DataTable dt = CreateValidTable1();
RepeatRange repeatRange = new RepeatRange();
repeatRange.RangeName = “Range1”;
repeatRange.RepeatDirection = RangeRepeatDirection.Vertical;
PerformRangeRepeat(dt, repeatRange);

repeatRange.RangeName = “Range2”;
PerformRangeRepeat(dt, repeatRange);

repeatRange.RepeatDirection = RangeRepeatDirection.Horizontal;
repeatRange.RangeName = “Range3”;
PerformRangeRepeat(dt, repeatRange);

repeatRange.RangeName = “Range4”;
PerformRangeRepeat(dt, repeatRange);

workbook.CalculateFormula(false, new LookupCustomFunction());
workbook.Worksheets[“Formula”].Cells[“A1”].PutValue(workbook.Worksheets[“Formula”].Cells[“A1”].Value);

string output = Constants.destPath + “Duplicate3_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}

private class LookupCustomFunction : ICustomFunction
{

#region ICustomFunction Members

public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
{
return string.Format(“contextObjects.Count = {0}”, contextObjects.Count);
}

#endregion
}

private DataTable CreateValidTable1()
{
DataTable dt = new DataTable(“MyDataSource”);
dt.Columns.Add(“Id”, typeof(int));
dt.Columns.Add(“Additional”, typeof(string));
dt.Rows.Add(1001, “Additional1”);
dt.Rows.Add(1002, “Additional2”);
dt.Rows.Add(1003, “Additional3”);
// dt.Rows.Add(“val2”, “val2”, 4);
// dt.Rows.Add(“val3”, “val3”, 4);
return dt;
}
}
}

By the way, here’s a question that I have been thinking of asking a long time:

Why is the code like
workbook.Worksheets[“Formula”].Cells[“A1”].PutValue(workbook.Worksheets[“Formula”].Cells[“A1”].Value);
necessary?
The value is already in the cell, given by the custom formula. The code above looks like it doesn’t do anything, but it is necessary for the value to be shown.

Hi,


Thanks for providing details about the two problems you mentioned. I have logged them and we will look into them soon. I have also reopened the issue again. Once we figure it out, we will let you know here.

For:
" workbook.Worksheets[“Formula”].Cells[“A1”].PutValue(workbook.Worksheets[“Formula”].Cells[“A1”].Value);"

Well, if a cell has formula in it and you need to replace it with the calculated output value in it, you should use this line of code.

Thank you.

The issues you have found earlier (filed as xxxxx) have been fixed in this update.

Hi,


In the new version v7.0.3 we did:

a) Fixed a bug of calculating the custom functions.

<o:p></o:p>

b) If you do not want MS Excel calculate the formula when opening the file, please set workbook.Settings.ReCalculateOnOpen = false;

string output = @"D:\FileTemp\dest.xlsx";

workbook.Settings.ReCalculateOnOpen = false;

workbook.Save(output);


Thank you.