导出模板如上图
实体类:
/** 主键 */
private String id;
/** 班级名称 */
private String name;
/** 班级人数 */
private String num;
/**
* 导出Excel数据
* @param query
* @return
* @throws Exception
*/
public InputStream exportBwClass(BwClass query) throws Exception
{
// 导出路径
String logPath = "D:" + File.separator + "excel" + File.separator + "bwClassExport" + File.separator + "";
File file = new File(logPath);
if (!file.exists())
{
file.mkdirs();
}
// 模板路径
String FilePath = "D:" + File.separator + "excel" + File.separator + "Template.xlsx";
InputStream in = null;
Workbook wb = null;
try
{
in = new FileInputStream(new File(FilePath));
// 读取工作簿
wb = new Workbook(in);
}
finally
{
// 关闭输入流
in.close();
}
// 创建Aspose.Cells WorkbookDesigner 的对象
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(wb);
// 构建DataSource
List<BwClass> rList = queryList(query);
designer.setDataSource("a", rList);
// 根据数据源处理生成报表内容
designer.process();
// 输出流,写入到文件中
String tempFile = BwClassConstant.TEMP_EXCEL;
OutputStream out = new FileOutputStream(tempFile);
try
{
// 保存数据
wb.save(out, wb.getFileFormat());
FileInputStream inputStream = new FileInputStream(tempFile);
return inputStream;
}
finally
{
out.close();
}
}
/**
* 保存导入excel
*/
@Override
public ResponseDto saveImport(HttpServletRequest request, InputStream in)
{
ResponseDto dto = new ResponseDto();
// 1、打开文件,参数可以是文件的路径,也可以直接传入一个文件流
Workbook workbook = null;
try
{
workbook = new Workbook(in);
}
catch (Exception e)
{
LOG.error(e.getMessage(), e);
throw new NormalRuntimeException(e.getMessage());
}
// 2、获取sheet表
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(0);
Cells cells = worksheet.getCells();
List<BwClass> addList = new ArrayList<BwClass>();
for (int i = 1; i < cells.getMaxRow() + 1; i++)
{
int j = 0;
if (StringUtil.isEmpty(cells.get(i, j).getStringValue()))
{
throw new NormalRuntimeException("班级id不能为空");
}
BwClass bwClass = new BwClass(cells.get(i, j).getStringValue(), cells.get(i, j + 1).getStringValue(), cells.get(i, j + 2).getStringValue());
bwClass.setCreateTime(new Date());
if (bwClass != null)
{
addList.add(bwClass);
}
}
try
{
bwClassDao.batchCreate(addList, "insert");
dto.setSuccesResultObj("导入成功");
}
catch (Exception e)
{
dto.setErrMsg("导入失败!");
LOG.error(e.getMessage(), e);
}
worksheets.clear();
worksheet = null;
worksheets = null;
workbook = null;
return dto;
}