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