//
// Summary:
// copy the style of old range to new range
//
// Parameters:
// to:
// the new range where style will applied to
// from:
// the old range where style will be copied
public static ExcelRange CopyStyleFrom(this ExcelRange to, ExcelRange from, bool withValue = false)
{
var fromIndex = from.GetRangeIndex();
var toIndex = to.GetRangeIndex();
var offset = fromIndex[3] - fromIndex[1] + 1;
var range = toIndex[3] - toIndex[1] + 1;
var rest = range % offset;
var loop = (range - rest) / offset;
var index = 0;
for (; index < loop; index++)
{
var tmpRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + (index + 1) * offset, fromIndex[2], fromIndex[3] + (index + 1) * offset });
from.Copy(tmpRange);
if (!withValue)
{
tmpRange.ForEach(t => t.Value = "");
}
}
if (rest > 0)
{
var restRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + index * offset, fromIndex[2], fromIndex[1] + index * offset + rest - 1 });
var x = from.Start.Address.AddressToNumber();
var y = from.End.Address.AddressToNumber();
var restFrom = from[x[0], x[1], y[0], x[1] + rest - 1];
restFrom.Copy(restRange);
if (!withValue)
{
restRange.ForEach(t => t.Value = "");
}
}
return(to);
}
public static ExcelRange GetRange(this ExcelRange range, string type, int offset = 0)
{
int[] index; // RowST, ColST, RowED, ColED
switch (type)
{
case "Revenue Estimates_Exp":
index = new[] { 4, 2, 13, 5 };
break;
case "Summary_Exp":
index = new[] { 4, 2, 12, 2 };
break;
case "Value by city_Exp":
index = new[] { 3, 2, offset, 7 };
break;
case "Consumer Profile_Exp":
index = new[] { 4, 2, 37, 6 };
break;
default:
index = new[] { 1, 1, 1, 1 };
break;
}
return(range.GetRange(index));
}
/// <summary>
/// copy the style of old range to new range
/// </summary>
/// <param name="to">the new range where style will applied to</param>
/// <param name="offset">the old range where style will be copied</param>
/// <param name="skip">the number of rows or columns to skip</param>
/// <returns>The <paramref name="ExcelRange"/> after new style applied</returns>
public static ExcelRange CopyStyleFrom(this ExcelRange to, ExcelRange from, InsertMode mode = InsertMode.ColumnRight, bool withValue = false, int skip = 0)
{
var fromIndex = from.GetRangeIndex();
var toIndex = to.GetRangeIndex();
var offset = (mode.In(InsertMode.RowAfter, InsertMode.RowBefore) ? fromIndex[2] - fromIndex[0] : fromIndex[3] - fromIndex[1]) + 1;
var range = (mode.In(InsertMode.RowAfter, InsertMode.RowBefore) ? toIndex[2] - toIndex[0] : toIndex[3] - toIndex[1]) + 1;
var rest = range % offset;
var loop = (range - rest) / offset;
var index = 0;
for (; index < loop; index++)
{
ExcelRange tmpRange = null;
if (mode.In(InsertMode.RowAfter, InsertMode.RowBefore))
{
tmpRange = to.GetRange(new[] { fromIndex[0] + (index + 1 + skip) * offset, fromIndex[1], fromIndex[2] + (index + 1 + skip) * offset, fromIndex[3] });
}
else
{
tmpRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + (index + 1 + skip) * offset, fromIndex[2], fromIndex[3] + (index + 1 + skip) * offset });
}
from.Copy(tmpRange);
if (!withValue)
{
tmpRange.ForEach(t => t.Value = "");
}
}
if (rest > 0)
{
var restRange = to.GetRange(new[] { fromIndex[0], fromIndex[1] + index * offset, fromIndex[2], fromIndex[1] + index * offset + rest - 1 });
var x = from.Start.Address.AddressToNumber();
var y = from.End.Address.AddressToNumber();
var restFrom = from[x[0], x[1], y[0], x[1] + rest - 1];
restFrom.Copy(restRange);
if (!withValue)
{
restRange.ForEach(t => t.Value = "");
}
}
return(to);
}