其实这个初期版本已经基本上把他要的功能都实现了,呵呵
他的要求是批量转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
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
其中需要注意一点:对于一个Workbook而言,同样的单元格样式会存储在同一个样式属性中,由不同的单元格分别引用
所以一个个复制单元格的属性是不行滴,这样会造成目标文件样式数过大。
也没有什么好办法,因为源文件简单,所以就流氓了一下,哪位有更好的办法能压缩这个样式表,让重复的样式合并呢?
上个图