/**
**单元格值对象
**/
public class Cells {
/***
* 行
*/
private int row;
/**
* 列
*/
private int column;
/**
* 单元格的值
*/
private Object val;
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public int getColumn() {
return column;
}
public void setColumn(int column) {
this.column = column;
}
public Object getVal() {
return val;
}
public void setVal(Object val) {
this.val = val;
}
public Cells(int row, int column, Object val) {
this.row = row;
this.column = column;
this.val = val;
}
public Cells() {}
}
/**
**表空间对象
**/
public class Sheet {
private String sheetName;
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Cells> getCells() {
return cells;
}
public void setCells(List<Cells> cells) {
this.cells = cells;
}
private List<Cells> cells=Lists.newArrayList();
public Sheet(String sheetName, List<Cells> cells) {
this.sheetName = sheetName;
this.cells = cells;
}
public Sheet() {}
}
1 public final class ExcelUntil {
2
3 /**
4 * 根据模板导出excel
5 * @param templatePath 模板路径
6 * @param sheets 设置sheet 表空间的单元格具体的值对象
7 * @param exportPath 导出路径
8 * @throws Exception
9 */
10 @SuppressWarnings("resource")
11 public static void exportExcelByTemplate(String templatePath, List<Sheet> sheets, String exportPath)
12 throws Exception {
13 if (Strings.isStringEmpty(templatePath) || CollectionUtils.isEmpty(sheets) || Strings.isStringEmpty(exportPath)) {
14 return;
15 }
16 InputStream in = ExcelUntil.class.getResourceAsStream(templatePath);
17 POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
18 HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
19 for (int i = 0; i < sheets.size(); i++) {
20 HSSFSheet sheet = workbook.getSheetAt(i);
21 sheet.setForceFormulaRecalculation(true);
22 List<Cells> cells = sheets.get(i).getCells();
23 cellSetValue(sheet, cells);
24 }
25 FileOutputStream out = new FileOutputStream(exportPath);
26 workbook.write(out);
27 out.close();
28 }
29
30
31 public static HSSFWorkbook exportExcel(String templatePath, List<Sheet> sheets, String exportPath)
32 throws Exception {
33 InputStream in = ExcelUntil.class.getResourceAsStream(templatePath);
34 POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
35 HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
36 for (int i = 0; i < sheets.size(); i++) {
37 HSSFSheet sheet = workbook.getSheetAt(i);
38 sheet.setForceFormulaRecalculation(true);
39 List<Cells> cells = sheets.get(i).getCells();
40 cellSetValue(sheet, cells);
41 }
42 return workbook;
43
44 }
45
46
47 /**
48 * 设置具体单元格的值
49 * @param sheet 具体表空间
50 * @param cells 设置的具体的单元格
51 */
52 private static void cellSetValue(HSSFSheet sheet, List<Cells> cells) {
53 // 设置数据行列并设置值
54 Cells c = null;
55 Object val = null;
56 HSSFCell cell = null;
57 for (int i = 0; i < cells.size(); i++) {
58 c = cells.get(i);
59 if(c==null) {
60 continue;
61 }
62 val = c.getVal();
63 cell = sheet.getRow(c.getRow() - 1).getCell(c.getColumn() - 1);
64 if (val instanceof Integer) {
65 cell.setCellValue((Integer) val);
66 } else if (val instanceof Double) {
67 cell.setCellValue((Double) val);
68 } else if (val instanceof Date) {
69 cell.setCellValue((Date) val);
70 } else if (val instanceof Short) {
71 cell.setCellValue((short) val);
72 } else {
73 cell.setCellValue(val + "");
74 }
75 }
76 }
77 }
