Sort does not work properly when using Formula's #105
Comments
|
Hi I have a similar/same problem. The Cell L3132 had a formula =D3132-(D3132K3132) Also - after sorting, the sheet was saved and then processed by OpenXML. In OpenXML, the "value" of the Cell L1239 is returned as the formula, and not the calculated value. Trefor |
|
Please post a code sample (short, minimal code to reproduce the problem) and attach your spreadsheet. |
|
I'll reopen this when someone posts a full code sample. |
|
Just ran up against this issue. Surprised no one has posted a sample or the "bug" been fixed in four years. Seems pretty fundamental. At any rate... I used the code listed at the bottom to generate the attached sample XLSX file. The code was grabbed from https://github.com/ClosedXML/ClosedXML/wiki/Sorting-Data and modified slightly to accomplish my purposes. Column 3, in each table, is a formula adding the first two columns together. The second and third tables are identical, aside from the fact that the third one was sorted in C# code. The second table sorts correctly in Excel, however the third column has quite a few issues. First, if you click any of the cells in column M, you'll notice that the formula is not updated correctly. The table was sorted but the formula wasn't updated to match its new row number. Unless I misunderstood, this is the subject of the thread. Second, if you sort column M by "Largest to smallest" things blow up (for lack of a suitable explanation). The issue is easily reproducible. Please let me know if you need more information. |
|
Is it alphanumeric sorting or numeric. Note you are using setvalue with
strings.
…On Mon, Nov 16, 2020, 2:05 PM justanothergituser ***@***.***> wrote:
sample.xlsx
<https://github.com/ClosedXML/ClosedXML/files/5548996/sample.xlsx>
Just ran up against this issue. Surprised no one has posted a sample or
the "bug" been fixed in four years. Seems pretty fundamental. At any rate...
I used the code listed at the bottom to generate the attached sample XLSX
file. The code was grabbed from
https://github.com/ClosedXML/ClosedXML/wiki/Sorting-Data and modified
slightly to accomplish my purposes.
Column 3, in each table, is a formula adding the first two columns
together. The second and third tables are identical, aside from the fact
that the third one was sorted in C# code. The second table sorts correctly
in Excel, however the third column has quite a few issues.
First, if you click any of the cells in column M, you'll notice that the
formula is not updated correctly. The table was sorted but the formula
wasn't updated to match its new row number. Unless I misunderstood, this is
the subject of the thread. Second, if you sort column M by "Largest to
smallest" things blow up (for lack of a suitable explanation).
The issue is easily reproducible. Please let me know if you need more
information.
using (var wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add("Table");
ws.Cell("A1").SetValue("1");
ws.Cell("A2").SetValue("2");
ws.Cell("A3").SetValue("3");
ws.Cell("A4").SetValue("4");
ws.Cell("A5").SetValue("5");
ws.Cell("A6").SetValue("6");
ws.Cell("A7").SetValue("7");
ws.Cell("A8").SetValue("8");
ws.Cell("B1").SetValue("1");
ws.Cell("B2").SetValue("2");
ws.Cell("B3").SetValue("3");
ws.Cell("B4").SetValue("4");
ws.Cell("B5").SetValue("5");
ws.Cell("B6").SetValue("6");
ws.Cell("B7").SetValue("7");
ws.Cell("B8").SetValue("8");
ws.Cell("C1").FormulaA1 = "=A1+B1";
ws.Cell("C2").FormulaA1 = "=A2+B2";
ws.Cell("C3").FormulaA1 = "=A3+B3";
ws.Cell("C4").FormulaA1 = "=A4+B4";
ws.Cell("C5").FormulaA1 = "=A5+B5";
ws.Cell("C6").FormulaA1 = "=A6+B6";
ws.Cell("C7").FormulaA1 = "=A7+B7";
ws.Cell("C8").FormulaA1 = "=A8+B8";
var header = ws.Row(1).InsertRowsAbove(1).First();
for (Int32 co = 1; co <= ws.LastColumnUsed().ColumnNumber(); co++)
{
header.Cell(co).Value = "Column" + co.ToString();
}
var rangeTable = ws.RangeUsed();
var table = rangeTable.CopyTo(ws.Column(ws.LastColumnUsed().ColumnNumber() + 3)).CreateTable();
var rangeTable2 = rangeTable.RangeUsed();
var table2 = rangeTable2.CopyTo(ws.Column(ws.LastColumnUsed().ColumnNumber() + 3)).CreateTable();
table2.Sort("Column3 Desc");
wb.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\sample.xlsx");
}
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
<#105 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AA3WDMMOFOMZQGE3DOY6FP3SQFZYTANCNFSM4CXGQTCQ>
.
|
|
@vbjay, no, there is something different: Expected: Actual: |
An excel doc is generated with columns A thru O. The headers are there and sized right.
In column A there is just an item number, in column K there is a formula to compare against 2 other cells.
After the iterations of filling in the values from every record in the table, the next part of the code builds the headers, then the sorting done by: Sort("A");
The result is that in my first data row, which is row 4 (the first 3 rows are header or other info) A4 is right, it has the first item, it sorted right. but K4 ? totally wrong value. Click and it says: =(C$2-E33/(F33-E33) and then K5? is for row 65, not 5.
Is it not possible to do a Sort if the Excel spreadsheet uses Formula's for values? I thought that the reference C$2 means C2 absolute; so when the others are in there without '$' then they would be relative indexers and would update?
reference info:
Problem:
Microsoft Excel: What are Absolute and Relative Cell References?
Solution:
Absolute means that the reference in a formula will always look for the value in a specific cell, even if you copy the cell to a different location. The format for an absolute reference is $A$1 (referring to cell A1).
Relative references are in the format A1 and if copied to a different cell the cell reference changes relative to the new location. For example, if the formula specifies A1 and is copied to a new location two rows down the new formula would specify A3.
http://help.isu.edu/index.php?action=faq&catid=38&docid=410
The text was updated successfully, but these errors were encountered: