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() {} }
 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 + "");
}
}
}
}
上一篇:SqlServer基础之(触发器)


下一篇:(转)SqlServer基础之(触发器)(清晰易懂)