public void Parse_WithValidValues_ReturnsAddress(string input, uint expectedRow, uint expectedCol)
{
var result = CellAddress.Parse(input);
Assert.Equal(expectedCol, result.Col);
Assert.Equal(expectedRow, result.Row);
}
internal IEnumerable <string> Values(XElement row, GetValue getValue = null)
{
if (row != null)
{
int col = 0;
foreach (var cell in row.Elements(xmlnsC))
{
var nextColumn = CellAddress.ColumnOffset(cell);
while (nextColumn > col)
{
yield return(null);
col += 1;
}
if (getValue == null)
{
yield return(Value(cell));
}
else
{
yield return(getValue(cell, col));
}
col += 1;
}
}
}
public void TestCompareTo()
{
CellAddress A1 = new CellAddress(0, 0);
CellAddress A2 = new CellAddress(1, 0);
CellAddress B1 = new CellAddress(0, 1);
CellAddress B2 = new CellAddress(1, 1);
Assert.AreEqual(0, A1.CompareTo(A1));
Assert.AreEqual(-1, A1.CompareTo(B1));
Assert.AreEqual(-1, A1.CompareTo(A2));
Assert.AreEqual(-1, A1.CompareTo(B2));
Assert.AreEqual(1, B1.CompareTo(A1));
Assert.AreEqual(0, B1.CompareTo(B1));
Assert.AreEqual(-1, B1.CompareTo(A2));
Assert.AreEqual(-1, B1.CompareTo(B2));
Assert.AreEqual(1, A2.CompareTo(A1));
Assert.AreEqual(1, A2.CompareTo(B1));
Assert.AreEqual(0, A2.CompareTo(A2));
Assert.AreEqual(-1, A2.CompareTo(B2));
Assert.AreEqual(1, B2.CompareTo(A1));
Assert.AreEqual(1, B2.CompareTo(B1));
Assert.AreEqual(1, B2.CompareTo(A2));
Assert.AreEqual(0, B2.CompareTo(B2));
CellAddress[] sorted = { A1, B1, A2, B2 };
CellAddress[] unsorted = { B1, B2, A1, A2 };
Assume.That(!sorted.Equals(unsorted));
Array.Sort(unsorted);
CollectionAssert.AreEqual(sorted, unsorted);
}
/// <summary>
/// Run the generator against a world.
/// </summary>
/// <param name="world">The world to generate against.</param>
public void RunGeneration(TWorld world)
{
var oceans = IdentifyPointOceans(world);
var rainfallMap = new float[world.Width, world.Height];
for(int x = 0; x < world.Width; x++)
{
for(int y = 0; y < world.Height; y++)
{
var ca = new CellAddress(x, y);
if(world.GetCell(ca.X,ca.Y).Height >= world.SeaLevel)
{
rainfallMap[x, y] = CalculateRainfallAt(ca, world, oceans);
}
}
}
rainfallMap = MapUtil.Normalize(rainfallMap);
for(int x = 0; x < world.Width; x++)
{
for(int y = 0; y < world.Height; y++)
{
var ca = new CellAddress(x, y);
world.GetCell(ca.X, ca.Y).Rainfall = rainfallMap[x, y];
}
}
}
/**
* Creates a comment.
* @param anchor the client anchor describes how this comment is attached
* to the sheet.
* @return the newly Created comment.
*/
public IComment CreateCellComment(IClientAnchor anchor)
{
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
XSSFSheet sheet = (XSSFSheet)GetParent();
//create comments and vmlDrawing parts if they don't exist
CommentsTable comments = sheet.GetCommentsTable(true);
XSSFVMLDrawing vml = sheet.GetVMLDrawing(true);
NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape();
if (ca.IsSet())
{
// convert offsets from emus to pixels since we get a DrawingML-anchor
// but create a VML Drawing
int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL;
int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL;
int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL;
int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL;
String position =
ca.Col1 + ", " + dx1Pixels + ", " +
ca.Row1 + ", " + dy1Pixels + ", " +
ca.Col2 + ", " + dx2Pixels + ", " +
ca.Row2 + ", " + dy2Pixels;
vmlShape.GetClientDataArray(0).SetAnchorArray(0, position);
}
CellAddress ref1 = new CellAddress(ca.Row1, ca.Col1);
if (comments.FindCellComment(ref1) != null)
{
throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1);
}
return(new XSSFComment(comments, comments.NewComment(ref1), vmlShape));
}
private float CalculateRainfallAt(CellAddress address, TWorld world, List<CellAddress> oceans)
{
var horizontalWind = -1 * Math.Sin(address.Y * Math.PI / (world.Height / 6));
var verticalWind = address.Y > world.Width / 2 ? -0.1 : 0.1;
var windDir = Math.Atan2(verticalWind, horizontalWind);
//Result is normalized version of above
var windDirX = Math.Cos(windDir);
var windDirY = Math.Sin(windDir);
var totalRainfall = 0.0;
foreach(var ocean in oceans)
{
var dY = ocean.Y - address.Y;
var dX = ocean.X - address.X;
var oceanDir = Math.Atan2(dY, dX);
var oceanDirX = Math.Cos(oceanDir);
var oceanDirY = Math.Sin(oceanDir);
var rainFactor = oceanDirX * windDirX + oceanDirY * windDirY;
rainFactor = rainFactor / 2 + 0.5; //value between 0 and 1
rainFactor = rainFactor / Math.Max(_pointOceanSize / 2, Math.Sqrt(dX * dX + dY * dY)); //divide by distance
totalRainfall += rainFactor;
}
return (float)totalRainfall;
}
/**
* Remove the comment at cellRef location, if one exists
*
* @param cellRef the location of the comment to remove
* @return returns true if a comment was removed
*/
public bool RemoveComment(CellAddress cellRef)
{
String stringRef = cellRef.FormatAsString();
CT_CommentList lst = comments.commentList;
if (lst != null)
{
CT_Comment[] commentArray = lst.GetCommentArray();
for (int i = 0; i < commentArray.Length; i++)
{
CT_Comment comment = commentArray[i];
if (stringRef.Equals(comment.@ref))
{
lst.RemoveComment(i);
if (commentRefs != null)
{
commentRefs.Remove(cellRef);
}
return(true);
}
}
}
return(false);
}
CellAddress GetLabelOrigin()
{
if (MetaData != null)
{
if (!string.IsNullOrEmpty(this.MetaData.LabelOrigin))
{
return(new CellAddress(this.MetaData.LabelOrigin));
}
if (!string.IsNullOrEmpty(MetaData.DataOrigin))
{
var lo = new CellAddress(MetaData.DataOrigin);
if (Dimensions.Length == 0)
{
lo.Row = -1;
}
else
{
lo.Row -= Horizontal.Length;
lo.Column -= Vertical.Length;
}
return(lo);
}
}
var lc = DataSheet.GetLeftCorner();
return(lc == null ? CellAddress.Origin : new CellAddress(lc.Elements(xmlnsC).First()));
}
private static void CreateMatrixHeaderRow(WorkSheet sheet, WfActivityMatrixResourceDescriptor activityMatrix, int startRowIndex)
{
Row headerRow = new Row(startRowIndex);
headerRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid);
headerRow.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Top.Color.SetColor(Color.Black);
headerRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Bottom.Color.SetColor(Color.Black);
headerRow.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headerRow.Style.Border.Left.Color.SetColor(Color.Black);
headerRow.Style.Font.Bold = true;
sheet.Rows.Add(headerRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in activityMatrix.PropertyDefinitions)
{
sheet.Cells[headerRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
sheet.Names.Add(CellAddress.Parse(columnIndex, headerRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
private CellAddress findNextMinimum(ProblemTable problemTable, ResolvedTable resolvedTable)
{
double minimum = Double.MaxValue;
CellAddress minCellAddress = null;
double[,] prices = problemTable.prices;
for (int i = 0; i < prices.GetLength(0); i++)
{
if (resolvedTable.warehouses[i].getTotal() == 0)
{
continue;
}
for (int j = 0; j < prices.GetLength(1); j++)
{
if (resolvedTable.consumers[j].getRequired() == 0)
{
continue;
}
if (minimum > prices[i, j] && resolvedTable.consumed[i, j].getUsage() == 0)
{
minimum = prices[i, j];
minCellAddress = new CellAddress(i, j);
}
}
}
if (minCellAddress == null)
{
throw new InvalidOperationException("The resolved table is full");
}
return(minCellAddress);
}
public void RemoveComment()
{
CellAddress addrA1 = new CellAddress("A1");
CellAddress addrA2 = new CellAddress("A2");
CellAddress addrA3 = new CellAddress("A3");
CommentsTable sheetComments = new CommentsTable();
CT_Comment a1 = sheetComments.NewComment(addrA1);
CT_Comment a2 = sheetComments.NewComment(addrA2);
CT_Comment a3 = sheetComments.NewComment(addrA3);
Assert.AreSame(a1, sheetComments.GetCTComment(addrA1));
Assert.AreSame(a2, sheetComments.GetCTComment(addrA2));
Assert.AreSame(a3, sheetComments.GetCTComment(addrA3));
Assert.AreEqual(3, sheetComments.GetNumberOfComments());
Assert.IsTrue(sheetComments.RemoveComment(addrA1));
Assert.AreEqual(2, sheetComments.GetNumberOfComments());
Assert.IsNull(sheetComments.GetCTComment(addrA1));
Assert.AreSame(a2, sheetComments.GetCTComment(addrA2));
Assert.AreSame(a3, sheetComments.GetCTComment(addrA3));
Assert.IsTrue(sheetComments.RemoveComment(addrA2));
Assert.AreEqual(1, sheetComments.GetNumberOfComments());
Assert.IsNull(sheetComments.GetCTComment(addrA1));
Assert.IsNull(sheetComments.GetCTComment(addrA2));
Assert.AreSame(a3, sheetComments.GetCTComment(addrA3));
Assert.IsTrue(sheetComments.RemoveComment(addrA3));
Assert.AreEqual(0, sheetComments.GetNumberOfComments());
Assert.IsNull(sheetComments.GetCTComment(addrA1));
Assert.IsNull(sheetComments.GetCTComment(addrA2));
Assert.IsNull(sheetComments.GetCTComment(addrA3));
}
/// <summary>
/// 设置单元格内容
/// </summary>
/// <param name="cell"></param>
/// <param name="value"></param>
public static void SetValue(this CellAddress cell, object value)
{
if (value.IsNull())
{
cell.SetValueInternal(new object[cell.Rows, cell.Columns]);
}
else if (cell.Count == 1)
{
var vt = new object[1, 1];
vt[0, 0] = value;
cell.SetValueInternal(vt);
}
else
{
if (value is object[,] array)
{
cell.SetValueInternal(array);
}
else if (value is string)
{
var str = (string)value;
var arr = str.Split(',');
var vt = arr.ToMatrix(cell.Rows, cell.Columns);
cell.SetValueInternal(vt);
}
}
}
/// <summary>
/// 获取单元格序列
/// </summary>
/// <param name="range"></param>
/// <param name="direction">遍历方向</param>
/// <returns></returns>
public static IEnumerable <CellAddress> GetCells(this CellAddress range, XlFillDirection direction = XlFillDirection.RowFirst)
{
for (var i = 0; i < range.Count; i++)
{
yield return(range.GetCell(i, direction));
}
}
static void Main(string[] args)
{
// Open spreadsheet from file
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("example.xls");
// Get first worksheet
Worksheet worksheet = document.Workbook.Worksheets[0];
// Find cells containing "in" substring
Cell cell = worksheet.Find(
"in", false /*case insesitive*/, false /*not regexp*/, false /*search forward*/);
while (cell != null)
{
// Print found cell address and value to console
CellAddress address = cell.GetAddress();
string message = string.Format(
"({0}, {1}): {2}", address.Column, address.Row, cell.ValueAsExcelDisplays);
Console.WriteLine(message);
cell = worksheet.FindNext();
}
document.Dispose();
Console.WriteLine();
Console.WriteLine("Press any key to continue.");
Console.ReadKey(true);
}
public static void CreateMatrixHeaderRow(this IWfMatrixContainer matrix, WorkSheet sheet)
{
matrix.NullCheck("matrix");
sheet.NullCheck("sheet");
Row headRow = new Row(3);
headRow.Style.Fill.SetBackgroundColor(Color.Gold, ExcelFillStyle.Solid);
headRow.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Top.Color.SetColor(Color.Black);
headRow.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Bottom.Color.SetColor(Color.Black);
headRow.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headRow.Style.Border.Left.Color.SetColor(Color.Black);
headRow.Style.Font.Bold = true;
sheet.Rows.Add(headRow);
int columnIndex = 1;
foreach (SOARolePropertyDefinition dimension in matrix.PropertyDefinitions)
{
sheet.Cells[headRow.Index, columnIndex].Value = dimension.Description.IsNotEmpty() ? dimension.Description : dimension.Name;
sheet.Names.Add(CellAddress.Parse(columnIndex, headRow.Index).ToString(), dimension.Name);
columnIndex++;
}
}
public void TestStringConstructor(string address, int row, int col)
{
CellAddress ca = new CellAddress(address);
Assert.Equal(row, ca.Row);
Assert.Equal(col, ca.ColumnNumber);
}
public void AddIgnoredError(CellAddress cellAddress, IgnoredError ignoredErrors)
{
if (!ignoredErrors.IsDifferentFromDefault)
{
return;
}
var id = ignoredErrors.GetHashCode();
foreach (var distinctIgnored in DistinctIgnoredErrors)
{
if (distinctIgnored.IgnoredErrorId == id)
{
distinctIgnored.Cells.Add(cellAddress);
return;
}
}
var newIgnoredError = new XlsxIgnoredError
{
IgnoredError = ignoredErrors
};
newIgnoredError.Cells.Add(cellAddress);
DistinctIgnoredErrors.Add(newIgnoredError);
}
public void TestURLsWithHashMark()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("59775.xlsx");
XSSFSheet sh = wb.GetSheetAt(0) as XSSFSheet;
CellAddress A2 = new CellAddress("A2");
CellAddress A3 = new CellAddress("A3");
CellAddress A4 = new CellAddress("A4");
CellAddress A7 = new CellAddress("A7");
XSSFHyperlink link = sh.GetHyperlink(A2) as XSSFHyperlink;
Assert.AreEqual("address", "A2", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://twitter.com/#!/apacheorg", link.Address, "link target");
link = sh.GetHyperlink(A3) as XSSFHyperlink;
Assert.AreEqual("address", "A3", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://www.bailii.org/databases.html#ie", link.Address, "link target");
link = sh.GetHyperlink(A4) as XSSFHyperlink;
Assert.AreEqual("address", "A4", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("https://en.wikipedia.org/wiki/Apache_POI#See_also", link.Address, "link target");
link = sh.GetHyperlink(A7) as XSSFHyperlink;
Assert.AreEqual("address", "A7", link.CellRef);
Assert.AreEqual(HyperlinkType.Document, link.Type, "link type");
Assert.AreEqual("Sheet1", link.Address, "link target");
wb.Close();
}
//获取指定cell的值
private static string getSpecificCellValue(ScriptState script, ISheet sheet, string starttag, string start, string defaultvalue, string formatter, string offset, string val)
{
CellAddress celladdress = null;
if (!string.IsNullOrEmpty(starttag))
{
celladdress = findXslx(sheet, starttag);
}
else if (!string.IsNullOrEmpty(start))
{
celladdress = new CellAddress(new CellReference(start));
}
if (celladdress != null)
{
var r = 0;
var c = 0;
if (!string.IsNullOrEmpty(offset))
{
var sp = offset.Split(';');
foreach (var ts in sp)
{
var sparray = ts.Split(':');
if (sparray[0].Equals("c", StringComparison.OrdinalIgnoreCase))
{
c = Convert.ToInt32(sparray[1]);
}
else
{
r = Convert.ToInt32(sparray[1]);
}
}
}
var cell = sheet.GetRow(celladdress.Row + r).GetCell(celladdress.Column + c);
val = getCellValue(cell);
if (string.IsNullOrEmpty(val) && !string.IsNullOrEmpty(defaultvalue))
{
val = defaultvalue;
}
//执行动态脚本
if (!string.IsNullOrEmpty(formatter))
{
val = executeScript(script, formatter, val);
}
}
else
{
if (!string.IsNullOrEmpty(defaultvalue))
{
val = defaultvalue;
}
//执行动态脚本
if (!string.IsNullOrEmpty(formatter))
{
val = executeScript(script, formatter, val);
}
}
return(val);
}
public void TestGetRange()
{
var cell1 = CellAddress.Parse("A1");
var cell2 = CellAddress.Parse("C4");
var range = new[] { cell1, cell2 }.GetRange();
Assert.Equal("$A$1:$C$4", range.LocalAddress);
}
public void TestMax()
{
var c1 = CellAddress.Parse("A1");
var c2 = CellAddress.Parse("A2");
Assert.IsTrue(c1.Max(c2) == c2);
}
public void TestGetRange()
{
var cells = new CellAddress[] {
"A1", "B2", "D5", "F3"
};
Assert.AreEqual("$A$1:$F$5", cells.GetRange().LocalAddress);
}
/// <summary>
/// 返回 <see cref="CellAddress"/> 对象，它代表位于指定单元格区域的一定的偏移量位置上的区域。
/// 返回的区域和原始区域大小相同
/// <remarks>
/// <seealso cref="Microsoft.Office.Interop.Excel.Range.Offset"/>
/// </remarks>
/// </summary>
/// <param name="cell"></param>
/// <param name="rowOffset">
/// 区域偏移的行数（正数、负数或 0（零））。正数表示向下偏移，负数表示向上偏移。默认值是 0
/// </param>
/// <param name="columnOffset">
///
/// </param>
/// <returns></returns>
public static CellAddress Offset(this CellAddress cell, int rowOffset = 0, int columnOffset = 0)
{
return(new CellAddress(cell.SheetName,
cell.RowFirst + rowOffset,
cell.RowLast + rowOffset,
cell.ColumnFirst + columnOffset,
cell.ColumnLast + columnOffset));
}
/// <summary>
/// 从单元格读取数据
/// </summary>
/// <returns></returns>
public static IEnumerable <T> GetValues <T>(this CellAddress address)
{
if (address.Count == 1)
{
return(new T[] { address.GetValue <T>() });
}
return(address.GetValuesInternal <T>());
}
/**
* Get the underlying CTComment xmlbean for a comment located at cellRef, if it exists
*
* @param cellRef the location of the cell comment
* @return CTComment xmlbean if comment exists, otherwise return null.
*/
public CT_Comment GetCTComment(CellAddress cellRef)
{
// Create the cache if needed
PrepareCTCommentCache();
// Return the comment, or null if not known
return(commentRefs[cellRef]);
}
public void TestCellNameToIndex(string address, int row, int col)
{
int r, c;
CellAddress.CellNameToIndex(address, out r, out c);
Assert.Equal(row, r);
Assert.Equal(col, c);
}
/**
* Called after the reference is updated, so that
* we can reflect that in our cache
* @param oldReference the comment to remove from the commentRefs map
* @param comment the comment to replace in the commentRefs map
*/
public void ReferenceUpdated(CellAddress oldReference, CT_Comment comment)
{
if (commentRefs != null)
{
commentRefs.Remove(oldReference);
commentRefs[new CellAddress(comment.@ref)] = comment;
}
}
public void TestParse()
{
var address = CellAddress.Parse("A1");
Assert.Equal(0, address.RowFirst);
Assert.Equal(0, address.ColumnFirst);
Assert.Equal(1, address.Count);
}
private XlsColumnAttribute(CellAddress address, string headerText, string numberFormat) : this(
address.StartColumn ?? 0, headerText, numberFormat)
{
if (address.StartRow != null || address.StartColumn == null || !string.IsNullOrWhiteSpace(address.Sheet) || address.IsRange)
{
throw new ArgumentException("Invalid cell address - address of single column without row or sheet reference required");
}
}
public override Task <CellValueType> GetCellValueType(CellAddress addr, ServerCallContext context)
=> Task.Factory.StartNew(() =>
{
_logger.LogDebug("GetCellValueType(row:{0}, col:{1})", addr.Row, addr.Col);
return(new CellValueType {
Value = GetOrCreateWrapper(context).GetCellValueType(addr)
});
});
public CellRefereceExpression(CellAddress address)
{
Address = address;
}