# 数据重整：用Java实现精准Excel数据排序的实用策略

2023/10/09 15:01

• 按值排序
• 按图标排序
• 按字体颜色排序
• 按背景色排序
• 根据自定义序列排序

private void SetExampleData(Workbook wb) {

Object data = new Object[][]{
{"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
{"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
{"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
{"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
{"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
{"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
{"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
};

IWorksheet sheet = wb.getWorksheets().get(0);
sheet.getRange("A1:F7").setValue(data);
sheet.getRange("A:F").setColumnWidth(15);

wb.save("output/sampleData.xlsx");
}


1. 按值排序

public void SortByValue() {
Workbook wb = new Workbook();
SetExampleData(wb);
IWorksheet sheet = wb.getActiveSheet();
//对F列做升序排序.
sheet.getRange("A2:F7").sort(sheet.getRange("F2:F7"), SortOrder.Ascending, SortOrientation.Columns);

wb.save("output/sortByValue.xlsx");
}


2. 按图标排序

public void SortByIcon() {
Workbook wb = new Workbook();
SetExampleData(wb);
IWorksheet sheet = wb.getActiveSheet();
iconset.setIconSet(wb.getIconSets().get(IconSetType.Icon3TrafficLights1));

wb.getIconSets().get(IconSetType.Icon3TrafficLights1).get(2), SortOrder.Ascending));
sheet.getSort().setRange(sheet.getRange("A2:F7"));
sheet.getSort().setOrientation(SortOrientation.Columns);
sheet.getSort().apply();

wb.save("output/sortByIcon.xlsx");
}


3. 按字体颜色排序

public void SortByFontColor() {
Workbook wb = new Workbook();
SetExampleData(wb);
IWorksheet sheet = wb.getActiveSheet();
sheet.getRange("F2").getFont().setColor(Color.GetRed());
sheet.getRange("F3").getFont().setColor(Color.GetGainsboro());
sheet.getRange("F4").getFont().setColor(Color.GetGreen());
sheet.getRange("F5").getFont().setColor(Color.GetGainsboro());
sheet.getRange("F6").getFont().setColor(Color.GetGainsboro());
sheet.getRange("F7").getFont().setColor(Color.GetGainsboro());

sheet.getRange("F7").getFont().getColor(), SortOrder.Ascending));
sheet.getSort().setRange(sheet.getRange("A2:F7"));
sheet.getSort().setOrientation(SortOrientation.Columns);
sheet.getSort().apply();

wb.save("output/sortByFontColor.xlsx");
}


4. 按背景色排序

public void SortByBackgroundColor() {
Workbook wb = new Workbook();
SetExampleData(wb);
IWorksheet sheet = wb.getActiveSheet();
sheet.getRange("F2").getInterior().setColor(Color.GetLightPink());
sheet.getRange("F3").getInterior().setColor(Color.GetLightGreen());
sheet.getRange("F4").getInterior().setColor(Color.GetLightPink());
sheet.getRange("F5").getInterior().setColor(Color.GetLightGreen());
sheet.getRange("F6").getInterior().setColor(Color.GetLightBlue());
sheet.getRange("F7").getInterior().setColor(Color.GetLightPink());

sheet.getRange("F4").getDisplayFormat().getInterior(), SortOrder.Ascending));
sheet.getSort().setRange(sheet.getRange("A2:F7"));
sheet.getSort().setOrientation(SortOrientation.Columns);
sheet.getSort().apply();

wb.save("output/sortByBackgroundColor.xlsx");
}


5. 根据自定义序列排序

public void CustomSort() {
Workbook wb = new Workbook();
SetExampleData(wb);
IWorksheet sheet = wb.getActiveSheet();

ValueSortField sortkey = new ValueSortField(sheet.getRange("A2:A7"), "\"Angela\", \"Damon\"");
sheet.getRange("A2:F7").sort(SortOrientation.Columns, false, sortkey);

wb.save("output/customSort.xlsx");
}


2 评论
0 收藏
0