用java操作excel，jexcelapi

1
public void generateExcel(List<?> entities, Column[] columns, String fileName);

1
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
package com.yingxia.trms.excel;
import java.io.File;
import java.lang.reflect.Method;
import java.util.List;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExcelGeneratorImpl implements ExcelGenerator {
@Override
public void generateExcel(List<?> entities, Column[] columns, String fileName) {
try {
String path = ExcelGeneratorImpl.class.getResource("/").getPath();
path = path.replace("WEB-INF/classes/", "") + "excel/" + fileName + ".xls";
File file = new File(path);
if(file.exists()) {
file.delete();
}
WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
WritableSheet sheet = workbook.createSheet("First sheet", 0);
// header
for (int col = 0; col < columns.length; col++) {
Label labelHeader = new Label(col, 0, columns[col].getHeader());
sheet.addCell(labelHeader);
}
// body
for (int row = 1; row <= entities.size(); row++) {
for (int col = 0; col < columns.length; col++) {
String columnName = columns[col].getName();
Object columnValue = getFieldValue(entities.get(row - 1), columnName);
WritableCell cell = null;
if(columns[col].getType().equals("string")) {
cell = new Label(col, row, columnValue.toString());
} else {
cell = new jxl.write.Number(col, row, Double.valueOf(columnValue.toString()));
}
sheet.addCell(cell);
}
}
// sum
for (int col = 0; col < columns.length; col++) {
Column column =
columns[col];
if(column.isNeedSum() && column.getType().equals("number")) {
Double columnSum = 0.0;
for (int row = 1; row <= entities.size(); row++) {
Object fieldValue = getFieldValue(entities.get(row - 1), column.getName());
columnSum += Double.valueOf(fieldValue.toString());
}
WritableFont font = new WritableFont(WritableFont.ARIAL);
font.setColour(Colour.RED);
font.setBoldStyle(WritableFont.BOLD);
jxl.write.Number number = new jxl.write.Number(col, entities.size() + 1, columnSum, new WritableCellFormat(font));
sheet.addCell(number);
}
}
workbook.write();
workbook.close();
} catch(Exception ex) {
ex.printStackTrace();
}
}
private Object getFieldValue(Object obj, String fieldName) throws Exception {
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Method getMethod = obj.getClass().getMethod(getMethodName);
return getMethod.invoke(obj);
}
}

1
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
package com.yingxia.trms.excel;
public class Column {
private String name;
private String header;
private String type; // string, number
private boolean needSum = false; // must be number
public Column() {
}
public Column(String name, String header, String type) {
super();
this.name = name;
this.header = header;
this.type = type;
}
public Column(String name, String header, String type, boolean needSum) {
super();
this.name = name;
this.header = header;
this.type = type;
this.needSum = needSum;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getHeader() {
return header;
}
public void setHeader(String header) {
this.header = header;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public boolean isNeedSum() {
return needSum;
}
public void setNeedSum(boolean needSum) {
this.needSum = needSum;
}
}