/**
**单元格值对象
**/
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() {}
}
public final class ExcelUntil {
/**
* 根据模板导出excel
* @param templatePath 模板路径
* @param sheets 设置sheet 表空间的单元格具体的值对象
* @param exportPath 导出路径
* @throws Exception
*/
@SuppressWarnings("resource")
public static void exportExcelByTemplate(String templatePath, List<Sheet> sheets, String exportPath)
throws Exception {
if (Strings.isStringEmpty(templatePath) || CollectionUtils.isEmpty(sheets) || Strings.isStringEmpty(exportPath)) {
return;
}
InputStream in = ExcelUntil.class.getResourceAsStream(templatePath);
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
for (int i = 0; i < sheets.size(); i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
sheet.setForceFormulaRecalculation(true);
List<Cells> cells = sheets.get(i).getCells();
cellSetValue(sheet, cells);
}
FileOutputStream out = new FileOutputStream(exportPath);
workbook.write(out);
out.close();
}
public static HSSFWorkbook exportExcel(String templatePath, List<Sheet> sheets, String exportPath)
throws Exception {
InputStream in = ExcelUntil.class.getResourceAsStream(templatePath);
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
for (int i = 0; i < sheets.size(); i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
sheet.setForceFormulaRecalculation(true);
List<Cells> cells = sheets.get(i).getCells();
cellSetValue(sheet, cells);
}
return workbook;
}
/**
* 设置具体单元格的值
* @param sheet 具体表空间
* @param cells 设置的具体的单元格
*/
private static void cellSetValue(HSSFSheet sheet, List<Cells> cells) {
// 设置数据行列并设置值
Cells c = null;
Object val = null;
HSSFCell cell = null;
for (int i = 0; i < cells.size(); i++) {
c = cells.get(i);
if(c==null) {
continue;
}
val = c.getVal();
cell = sheet.getRow(c.getRow() - 1).getCell(c.getColumn() - 1);
if (val instanceof Integer) {
cell.setCellValue((Integer) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Date) {
cell.setCellValue((Date) val);
} else if (val instanceof Short) {
cell.setCellValue((short) val);
} else {
cell.setCellValue(val + "");
}
}
}
}