I’m trying to sort an Excel file with 1 sheet using a column that contains dot notations.
My method to do this is to first extract the column of dot notations and bubble sort them, keeping track of the row numbers in the original file. Therefore, I now have 2 vectors: one with dot nations sorted, and another with the position of each sorted dot notation value in the unsorted file. My question: how do I loop over the file to contain the sorted results, inserting a new row each time from the row in the original file?
If there is a better way to do this, I’m open to suggestions.
We are not entirely sure about your issue/requirements. It seems you want to sort the data in the sheet, is not it? It looks like you are sorting the data by yourself and then want to build the new sheet or workbook by copying sorted rows from the original file. If this is the case, we recommend you to kindly try using data sorting (MS Excel feature) via Aspose.Cells APIs, see the document for your reference:
If this does not fulfill your needs, kindly do provide your input file, output file and sample code (that you are using) to show the issue. Also, provide your expected file (that you may create/update in Ms Excel manually) and some screenshot to highlight your issue/requirements, we will check it soon.
I’ve tried to sort my data in Excel but it’s a dot notation that not standard sort can handle. By dot notation, I mean string representation of test numbering, e.g., 1.1, 1.2, 1.2.1,…1.2.9, 1.2.10, which is sorted correctly. But using a standard sort gives 1.1, 1.10, 1.2, 1.2.1, 1.2.10, …1.2.9.
In my code, I’ve done as you have understood: I have a template that I read into memory, I read the unsorted file, then I extract the dot notation column, sort this column using my own bubble sort while keeping track of the row numbers, thus also sorting the row numbers in the unsorted file. Now I want to pick the sorted rows and insert them into the template file.
@Marston,
We have thoroughly investigated the requirement and suggest you to modify your code a little bit to achieve the required output as follows:
It is assumed that you have sorted the .dot notation data yourself and have created a correct int array sortedPos which contains the desired order of row numbers.
Step 1: Import this sorted integers array after the last column in the output workbook.
Step 2: Create a DataSorter object and sort the entire data based on this newly entered column
Step 3: Delete the last column
Step 4: Save the output
Please give a try to the following modified sample code and share the feedback.
String dhpTemplateFile = "DHP-template.xlsx";
//ClassLoader classLoader = SortExportedDHP.class.getClassLoader();
//URL f = classLoader.getResource(dhpTemplateFile);
InputStream inp = new FileInputStream("DHP-template.xlsx");
Workbook outWb = new Workbook(inp);
// Open unsorted, exported DHP file. Assumes this is in the resources dir
String unsortedFile = "DHP-TestMyndighet-redigeringskopia.xlsx";
//URL s = classLoader.getResource(unsortedFile);
InputStream sinp = new FileInputStream("DHP-TestMyndighet-redigeringskopia.xlsx");
Workbook wus = new Workbook(sinp);
// Copy the first column from the first worksheet of the first workbook into the first worksheet of the second workbook.
Cells cells = wus.getWorksheets().get(0).getCells();
Object[][] dataTable = cells.exportArray(1,
1,
cells.getLastDataRow(1 ),
1);
int size = cells.getLastDataRow(1);
int posArray[] = new int[cells.getLastDataRow(1)];
String dotNs[] = new String[posArray.length];
for(int row = 0; row < size; row++) {
//System.out.println(row + " = " + dataTable[row][0]);
posArray[row] = row+1;
dotNs[row] = (String) dataTable[row][0];
}
Map<String, Object> sortedObjs = bubbleSort(posArray, dotNs);
int[] sortedPos = (int[]) sortedObjs.get("positions");
String[] sortedDotNs = (String[]) sortedObjs.get("dotNs");
//Import the sorted pos into a column in the source workbook
wus.getWorksheets().get(0).getCells().importArray(sortedPos, 1, 20, true);
// Get the workbook datasorter object.
DataSorter sorter = wus.getDataSorter();
// Set the first order for datasorter object.
sorter.setOrder1(SortOrder.ASCENDING);
// Define the first key.
sorter.setKey1(20);
// Sort data in the specified data range (CellArea range: A1:B14)
CellArea cellArea = new CellArea();
cellArea.StartRow = 1;
cellArea.StartColumn = 0;
cellArea.EndRow = 157;
cellArea.EndColumn = 20;
sorter.sort(wus.getWorksheets().get(0).getCells(), cellArea);
wus.getWorksheets().get(0).getCells().deleteColumn(20);//Comment this line to check the data
wus.save("DHP-template.xlsx");
Apologies for my late reply.
I’m truly grateful for this test and help. I’ve taken the code, examined and tested it.
I see the idea by adding the sorted rows to an extra column and sorting the table using that column. Unfortunately, Discovered that, while the idea is flawless, the data are not I my case. In the dot notation array, there are multiple instances of the same dot notation, e.g., 1.1 occurs multiple times.
The occurrence of non-unique values renders my approach useless as it yields only a partial sorting.
@Marston,
Thank you for your feedback. Yes, you are right that if data contains multiple instances of same dot notation, then this scheme may not work for you. You may please device your own scheme as per your needs and feel free to write us back if you have further queries in this regard.
