poi根据excel模板导出Excel

/**
**单元格值对象
**/
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 }

 

posted @ 2019-08-13 20:14  全力以赴001  阅读(922)  评论(0编辑  收藏  举报