1.加载文件方式:
import org.springframework.core.io.DefaultResourceLoader;
privateDefaultResourceLoader loader;
XSSFWorkbook workbook=
newXSSFWorkbook(
newFileInputStream(
newFile(
loader.getResource("/WEB-INF/classes/templates/filename.xlsx")
.getFile().getPath())));
XSSFSheet dataSheet=workbook.getSheetAt(0);
2.文件输出方式:
(HttpServletResponse response.....){
response.setContentType("application/vnd.ms-excel");
//response.setContentType("application/x-msdownload;"); //CSV 形式
response.setHeader("Content-Disposition","attachment; filename=${生成的名字带后缀}");
。。。
workbook.write(response.getOutputStream());
//输出到某个地方,Linux 路径
//FileOutputStream out = new FileOutputStream("/Users/rliu172/Documents/ImpactFolder/dataTest.xlsx");
//workbook.write(out);
}
3.Drop down如何实现
DataValidationHelper (createExplicitListConstraint/ createFormulaListConstraint)
DataValidationConstraint
CellRangeAddressList
DataValidation
2).依赖的dropdown
实现方式为:首先将数据展开输出到一个新的excel 中,然后将数据放入到数据引用中(createFormulaListConstraint)
importorg.apache.poi.ss.usermodel.Name;
Name namedRange =workbook.createName();
namedRange.setNameName("标识namerange的名称");
reference = 引用到sheet中找到数据范围区域,见例子
namedRange.setRefersToFormula(reference);
然后在子列表中,通过INDIRECT(父单元格位置)来进行引用
Excel小计:
3.加入merge 区域
sheet.addMergedRegion(row,row,col,col);
自动调整单元格大小: autoSizeColumn
4.excel 在创建nameRange 的时候,本身可能已经存在了重复的nameRange 就会报错,可以先检查下excel中的names:
打开excel
1.On the Ribbon, click the Formulas tab
2. Click Name Manager
6.文件的上传
uploadImpact(MultipartHttpServletRequest request, HttpServletResponse response){
MultipartFile file=request.getFile("file");
Long projectId= Long.valueOf(request.getParameter("projectId"));
.......
Workbookworkbook=newXSSFWorkbook(file.getInputStream());
}