Hi there,
I have a spreadsheet where I am sorting two tables of data. The first time I use the DataSorter like so, it works perfectly:
// Sort the data
Cells cells = workbook.Worksheets[n].Cells;
CellArea ca = new CellArea();
ca.StartRow = table1RowRange.FirstRow;
ca.StartColumn = table1RowRange.FirstColumn;
ca.EndRow = table1DataRange.FirstRow + table1DataRange.RowCount - 1;
ca.EndColumn = table1DataRange.FirstColumn + table1DataRange.ColumnCount - 1;
DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 2;
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca);
sorter.Clear();
However, when I then attempt to use the DataSorter again, it does not work. When debugging, I am looking at all of the values of the objects right before the sorter.Sort(cells, ca) is called. The CellArea ca columns are from 4 to 5, and the rows are from 5 to 10. When I look at each of these cell’s values, they are the following:
cells[5,5].Value = "Chest"
cells[6,5].Value = "Finger"
cells[7,5].Value = "Foot"
cells[8,5].Value = "Head"
cells[9,5].Value = "Multiple"
cells[10,5].Value = "Neck"
cells[4, 6].Value = 3
cells[5,6].Value = 1
cells[6,6].Value = 3
cells[7,6].Value = 1
cells[8,6].Value = 1
cells[9,6].Value = 9
cells[10,6].Value =1
But then right after the sorter.Sort(cells, ca) is called, none of these values change. They are not sorted. The code I use is below, exactly like the first time I called it and it did work, just with a different cell area specified:
// Sort the data
Cells cells = workbook.Worksheets[n].Cells;
CellArea ca = new CellArea();
ca.StartRow = table2RowRange.FirstRow;
ca.StartColumn = table2RowRange.FirstColumn;
ca.EndRow = table2DataRange.FirstRow + table2DataRange.RowCount - 1;
ca.EndColumn = table2DataRange.FirstColumn + table2DataRange.ColumnCount - 1;
DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 1;
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca);
sorter.Clear();
I need it to sort both areas, not just the first. Any idea what is going wrong?
Hi,
Thanks for giving us sample code and providing us some details.
I have tested your scenario a bit but could not reproduce the issue you have mentioned. Could you post your template file here, we will check it soon.
And, by the way, how do you implement your codes, I mean how do you write and run your code, Have you got separate procedures etc. for both the codes or you use single procedure/method/event-handler where you place both the codes and run at once.
Thank you.
My program is very long and it generates an excel file populated with information from a database and charts created from that information. I have attached the sheet my program produces where the problem is occurring.
Whether I call both snippets of code in the same method or different ones does not matter, the sorting for the second table does not work, and nor does it matter which I run first, the sorting for the first table works and the second does not. Here is the method I ran during the generation of the attached excel sheet. I have put in comments with // where I stepped through during debug and observed the current values of the program. All of the values I observed seem correct, there is just no change in the order of the data after the sort method is called for the second table.
private void CreateFacilityInjuryTypeChart(Workbook workbook)
{
//------------------------------------------------------------------------------------------------------------------
// Create Facility Injury Type chart
//------------------------------------------------------------------------------------------------------------------
// Retrieve some Ranges to be referenced here
int n = workbook.Worksheets.Count-1;
Range table1RowRange = workbook.Worksheets.GetRangeByName(“table1RowRange” + n);
Range table1DataRange = workbook.Worksheets.GetRangeByName(“table1DataRange” + n);
Range table2RowRange = workbook.Worksheets.GetRangeByName(“table2RowRange” + n);
Range table2DataRange = workbook.Worksheets.GetRangeByName(“table2DataRange” + n);
// Attempt to sort the data for the second table - this does not work
Cells cells = workbook.Worksheets[n].Cells; //n = 2, the current worksheet, this is correct
CellArea ca = new CellArea();
ca.StartRow = table2RowRange.FirstRow; // 4
ca.StartColumn = table2RowRange.FirstColumn; // 5
ca.EndRow = table2DataRange.FirstRow + table2DataRange.RowCount - 1; // 10
ca.EndColumn = table2DataRange.FirstColumn + table2DataRange.ColumnCount - 1; // 6
DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 1; // This is the column that contains the numbers I want sorted
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca); // After this is executed, the watch values for all of the cells do not change
sorter.Clear();
// Sort the data for the first table - this does work
//Cells cells = workbook.Worksheets[n].Cells;
//CellArea ca = new CellArea();
ca.StartRow = table1RowRange.FirstRow;
ca.StartColumn = table1RowRange.FirstColumn;
ca.EndRow = table1DataRange.FirstRow + table1DataRange.RowCount - 1;
ca.EndColumn = table1DataRange.FirstColumn + table1DataRange.ColumnCount - 1;
//DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 2; // This is the column that contains the numbers I want sorted
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca); // After this is executed, the watch values for all of the cells do change
sorter.Clear();
// Create chart
int chartIndex = 0;
chartIndex = workbook.Worksheets[n].Charts.Add(ChartType.Bar, startRowS3T1, startColS3T1, startRowS3T1 + 35, startColS3T1 + 5);
Chart chart = workbook.Worksheets[n].Charts[chartIndex];
//Set chart properties
chart.ChartObject.Width = 326;
chart.ChartObject.Height = 360;
chart.PlotArea.Border.IsVisible = false;
chart.PlotArea.Area.FillFormat.Pattern = FillPattern.Solid;
workbook.ChangePalette(Color.FromArgb(255, 255, 255), 1);
chart.PlotArea.Area.ForegroundColor = Color.FromArgb(255, 255, 255);
chart.ValueAxis.MinorUnit = 2;
chart.MajorGridLines.IsVisible = true;
chart.PlotEmptyCellsType = PlotEmptyCellsType.NotPlotted;
chart.ChartArea.Shadow = true;
chart.ChartArea.TextFont.Size = 7;
chart.PlotArea.Area.FillFormat.Pattern = FillPattern.Solid;
workbook.ChangePalette(Color.FromArgb(255, 255, 255), 1);
chart.PlotArea.Area.ForegroundColor = Color.FromArgb(255, 255, 255);
// Set Properties of chart title
chart.Title.Text = selectedYearShortLabel + " Injury Type";
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 10;
//Set properties of nseries
chart.NSeries.Add(table1DataRange[0, 0].Name + “:” + table1DataRange[table1DataRange.RowCount - 1, 0].Name, true);
chart.NSeries.CategoryData = table1RowRange[0, 0].Name + “:” + table1RowRange[table1RowRange.RowCount - 1, 0].Name;
chart.NSeries.IsColorVaried = false;
chart.NSeries[0].DataLabels.IsValueShown = false;
//Set properties of CategoryAxis title
chart.CategoryAxis.DisplayUnitLabel.Rotation = 90;
//Hide the legend
chart.IsLegendShown = false;
}
I was running the Aspose.Cells.dll before the latest hotfix, but I have updated it now to the most current hotfix provided on your website and this issue remains. Thanks,
Christine
Hi Christine,
Thanks for your template file,
We will look into your issue soon.
Thank you.
Hi Christine,
You should use column index as the sorted key. Please change your codes as the following :
Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\MonthlyReport_2008-10-17.xls");
// Retrieve some Ranges to be referenced here
int n = 2;//
Range table1RowRange = workbook.Worksheets.GetRangeByName("table1RowRange" + n);
Range table1DataRange = workbook.Worksheets.GetRangeByName("table1DataRange" + n);
Range table2RowRange = workbook.Worksheets.GetRangeByName("table2RowRange" + n);
Range table2DataRange = workbook.Worksheets.GetRangeByName("table2DataRange" + n);
// Attempt to sort the data for the second table - this does not work
Cells cells = workbook.Worksheets[0].Cells; //n = 2, the current worksheet, this is correct
CellArea ca = new CellArea();
ca.StartRow = table2RowRange.FirstRow; // 4
ca.StartColumn = table2RowRange.FirstColumn; // 5
ca.EndRow = table2DataRange.FirstRow + table2DataRange.RowCount - 1; // 10
ca.EndColumn = table2DataRange.FirstColumn + table2DataRange.ColumnCount - 1; // 6
DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 1 + ca.StartColumn; // This is the column that contains the numbers I want sorted
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca); // After this is executed, the watch values for all of the cells do not change
sorter.Clear();
// Sort the data for the first table - this does work
//Cells cells = workbook.Worksheets[n].Cells;
//CellArea ca = new CellArea();
ca.StartRow = table1RowRange.FirstRow;
ca.StartColumn = table1RowRange.FirstColumn;
ca.EndRow = table1DataRange.FirstRow + table1DataRange.RowCount - 1;
ca.EndColumn = table1DataRange.FirstColumn + table1DataRange.ColumnCount - 1;
//DataSorter sorter = workbook.DataSorter;
sorter.Key1 = 2 +ca.StartColumn; // This is the column that contains the numbers I want sorted
sorter.Order1 = SortOrder.Ascending;
sorter.Sort(cells, ca); // After this is executed, the watch values for all of the cells do change
sorter.Clear();
Ah yes. I had been assigning the Key column as an offset from the cell area, not from the entire worksheet. It was coincidence that it had worked for me up until this point. I amended the assignment as you suggested and now it works perfectly. Thank you very much,
Christine