其实这个初期版本已经基本上把他要的功能都实现了,呵呵
他的要求是批量转Excel,把所有用黄色标出的单元格所在的列拷到一个新文件中
其实底层很简单,界面做了倒有一会,还是一如既往的喜欢SWT啊~
附上Java利用Apache POI进行Excel文件的转换的核心代码。可能算法上不是很好,但数据量也不是很大。
1
public static HSSFWorkbook convert(HSSFWorkbook wb,byte[] rgb){
2
short high = wb.getCustomPalette().findSimilarColor(rgb[0], rgb[1], rgb[2]).getIndex();
3
HSSFWorkbook dest = new HSSFWorkbook();
4
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
5
6
Sheet sheet = wb.getSheetAt(0);
7
HashSet<Integer> toCopyColumns = new HashSet<Integer>();
8
9
Cell copyHighCell = null;
10
Cell copyNormalCell = null;
11
CellStyle csHigh;
12
CellStyle csNormal;
13
//read row
14
for (Row row:sheet){
15
for (Cell cell :row){
16
//find high lighted cell
17
if (cell.getCellStyle().getFillForegroundColor() == high){
18
//hit
19
toCopyColumns.add(cell.getColumnIndex());
20
copyHighCell = cell;
21
}else{
22
copyNormalCell = cell;
23
}
24
}
25
}
26
csHigh = dest.createCellStyle();
27
csNormal = dest.createCellStyle();
28
if (copyHighCell !=null){
29
csHigh.cloneStyleFrom(copyHighCell.getCellStyle());
30
copyHighCell = null;
31
}
32
if (copyNormalCell !=null){
33
csNormal.cloneStyleFrom(copyNormalCell.getCellStyle());
34
copyNormalCell = null;
35
}
36
37
38
39
//read over, toCopyColumns now has all column that need to copy
40
Integer[] columns = new Integer[toCopyColumns.size()];
41
toCopyColumns.toArray(columns);
42
Arrays.sort(columns);
43
44
Sheet destSheet = dest.createSheet("Sheet1");
45
for (Row row:sheet){
46
Row newRow = destSheet.createRow(row.getRowNum());
47
for (int i = 0;i < columns.length;i++){
48
Cell newCell = newRow.createCell(i);
49
Cell oldCell = row.getCell(columns[i]);
50
if (oldCell == null){
51
continue;
52
}
53
switch (oldCell.getCellType()){
54
case Cell.CELL_TYPE_FORMULA:
55
CellValue value = fe.evaluate(oldCell);
56
//judge the new cell format
57
newCell.setCellType(value.getCellType());
58
switch (value.getCellType()){
59
case Cell.CELL_TYPE_NUMERIC:
60
newCell.setCellValue(value.getNumberValue());break;
61
case Cell.CELL_TYPE_BOOLEAN:
62
newCell.setCellValue(value.getBooleanValue());break;
63
case Cell.CELL_TYPE_ERROR:
64
newCell.setCellErrorValue(value.getErrorValue());break;
65
case Cell.CELL_TYPE_STRING:
66
newCell.setCellValue(value.getStringValue());break;
67
}
68
break;
69
case Cell.CELL_TYPE_NUMERIC:
70
newCell.setCellValue(oldCell.getNumericCellValue());
71
newCell.setCellType(oldCell.getCellType());break;
72
case Cell.CELL_TYPE_BOOLEAN:
73
newCell.setCellValue(oldCell.getBooleanCellValue());
74
newCell.setCellType(oldCell.getCellType());break;
75
case Cell.CELL_TYPE_ERROR:
76
newCell.setCellErrorValue(oldCell.getErrorCellValue());
77
newCell.setCellType(oldCell.getCellType());break;
78
case Cell.CELL_TYPE_STRING:
79
newCell.setCellValue(oldCell.getStringCellValue());
80
newCell.setCellType(oldCell.getCellType());break;
81
}
82
if (oldCell.getCellStyle().getFillForegroundColor() == high){
83
newCell.setCellStyle(csHigh);
84
}else{
85
newCell.setCellStyle(csNormal);
86
}
87
88
}
89
}
90
// System.out.println(wb.getNumCellStyles());
91
// System.out.println(dest.getNumCellStyles());
92
return dest;
93
}
94
95
public static void main(String args[]) throws FileNotFoundException, IOException{
96
97
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("test.xls"));
98
HSSFWorkbook wb2 = convert (wb,new byte[]{(byte) 0xff,(byte) 0xff,0x0});
99
wb2.write(new FileOutputStream("tt.xls"));
100
}

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

其中需要注意一点:对于一个Workbook而言,同样的单元格样式会存储在同一个样式属性中,由不同的单元格分别引用
所以一个个复制单元格的属性是不行滴,这样会造成目标文件样式数过大。
也没有什么好办法,因为源文件简单,所以就流氓了一下,哪位有更好的办法能压缩这个样式表,让重复的样式合并呢?
上个图