包含复杂函数的excel 并下载
POI 版本：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
@RequestMapping("/exportList")
@ResponseBody
public Map<String, Object> exportList(HttpServletRequest request, HttpServletResponse response, HttpSession session,
WarehouseInboundParamVO vo) throws IOException, InvalidFormatException {
vo.setPage(0);
vo.setPageSize(10);
List<WarehouseRecordVO> list=warehouseInboundService.selectWarehouseRecordList(vo);
if (list == null ||list.size()==0) {
logger.info("导出入库记录数据为空，没有查询到数据！！！！");
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("1");
Row row = sheet.createRow((short) 0);
row.createCell(0).setCellValue("没有数据");
row = sheet.createRow(1);
row.createCell(0).setCellValue("没有找到数据 - - !!!!!");
ExcelUtil.downloadExcel(response, wb, "入库记录");
return APIUtil.toMap(HttpStatus.SUCCESS.getValue(), true);
}
logger.info("finance..记录数：{}", list.size());
// -----------------
// String TEMPLATE_PATH = "E://ziyuan/ruku.xls";
String TEMPLATE_PATH = "/mnt/tmpl/inventory/warehousingRecord.xlsx";
Resource resource = new FileSystemResource(TEMPLATE_PATH);
Workbook workbook = WorkbookFactory.create(resource.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
final int startRow = 2;
Cell cellFourteen =null;
Cell cellSeventeen =null;
Cell cellEighteen =null;
for (int i = startRow; i < list.size() + startRow; i++) {
int rowNum = i - startRow;
WarehouseRecordVO recordVO=list.get(rowNum);
Calendar calendar = Calendar.getInstance();
calendar.setTime(recordVO.getConfirmDate());
Integer year = calendar.get(Calendar.YEAR);
Integer month = calendar.get(Calendar.MONTH) + 1; // 第一个月从0开始，所以得到月份＋1
Integer day = calendar.get(Calendar.DAY_OF_MONTH);
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
CellStyle contextstyle = workbook.createCellStyle();
DataFormat df = workbook.createDataFormat();
contextstyle.setDataFormat(df.getFormat("#,##0.00"));
Cell cell = row.createCell(0);
cell.setCellValue(rowNum);
cell = row.createCell(1);
cell.setCellValue(year+"/"+month+"/"+day);
cell = row.createCell(2);
cell.setCellValue(year);
cell = row.createCell(3);
cell.setCellValue(month);
cell = row.createCell(4);
cell.setCellValue(day);
if (!StringUtil.isEmpty(recordVO.getItemNum())) {
cell = row.createCell(5);
cell.setCellValue(recordVO.getItemNum());
}
if (!StringUtil.isEmpty(recordVO.getBarCode())) {
cell = row.createCell(6);
cell.setCellValue(recordVO.getBarCode());
}
if (!StringUtil.isEmpty(recordVO.getCategoryOneName() )) {
cell = row.createCell(7);
cell.setCellValue(recordVO.getCategoryOneName());
}
if (!StringUtil.isEmpty(recordVO.getCategoryTwoName() )) {
cell = row.createCell(8);
cell.setCellValue(recordVO.getCategoryTwoName());
}
if (!StringUtil.isEmpty(recordVO.getBrandName() )) {
cell = row.createCell(9);
cell.setCellValue(recordVO.getBrandName());
}
if (!StringUtil.isEmpty(recordVO.getItemName() )) {
cell = row.createCell(10);
cell.setCellValue(recordVO.getItemName());
}
if (!StringUtil.isEmpty(recordVO.getSpecification() )) {
cell = row.createCell(11);
cell.setCellValue(recordVO.getSpecification());
}
if (!StringUtil.isEmpty(recordVO.getSpecificationNum() )) {
cell = row.createCell(12);
cell.setCellValue(recordVO.getSpecificationNum());
}
if (!StringUtil.isEmpty(recordVO.getSpecificationName() )) {
cell = row.createCell(13);
cell.setCellValue(recordVO.getSpecificationName());
}
cell = row.createCell(15);
cell.setCellValue(recordVO.getRealNum());
BigDecimal price = BigDecimal.valueOf(recordVO.getPrice());
BigDecimal num = BigDecimal.valueOf(recordVO.getRealNum());
BigDecimal total = price.multiply(num).divide(BigDecimal.valueOf(100));
cell = row.createCell(16);
cell.setCellValue(total.doubleValue());
if (!StringUtil.isEmpty(recordVO.getSupplierName() )) {
cell = row.createCell(20);
cell.setCellValue(recordVO.getSupplierName());
}
if(rowNum==0){
cell =row.getCell(14);
cellFourteen=cell;
cell =row.getCell(18);
cellEighteen=cell;
cell =row.getCell(17);
cellSeventeen=cell;
}else{
int temp=i+startRow-1;
cell = row.createCell(14);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
String cellFormula = cellFourteen.getCellFormula();
String s = cellFormula.replaceAll("(\\w)\\d", "$1" + (temp));
cell.setCellFormula(s);
cell =row.createCell(17);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cellFormula = cellSeventeen.getCellFormula();
s = cellFormula.replaceAll("(\\w)\\d", "$1" + (temp));
cell.setCellFormula(s);
cell =row.createCell(18);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cellFormula = cellEighteen.getCellFormula();
s = cellFormula.replaceAll("(\\w)\\d", "$1" + (temp));
cell.setCellFormula(s);
}
}
// // 重新计算公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (Row r : sheet) {
for (Cell c : r) {
if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
ExcelUtil.downloadExcel(response, workbook, "入库记录");
return APIUtil.toMap(HttpStatus.SUCCESS.getValue(), true);
}
package com.baoqilai.ddg.util; import java.io.*; import java.text.SimpleDateFormat; import java.util.*; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; /** * Excel操作工具类 */ public class ExcelUtil { private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class); /** * 解析Excel * * @param excel 文件 * @return List集合 */ public static List<Map<Integer, Object>> parseExcel(File excel) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excel)); HSSFSheet sheet = workbook.getSheetAt(0); int lastRowIndex = sheet.getLastRowNum(); List<Map<Integer, Object>> excelData = new ArrayList<>(); for (int i = 1; i <= lastRowIndex; i++) { HSSFRow row = sheet.getRow(i); Iterator<Cell> cells = row.cellIterator(); Map<Integer, Object> rowData = new HashMap<>(); while (cells.hasNext()) { Cell cell = cells.next(); Integer columnIndex = cell.getColumnIndex(); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 数值 if (DateUtil.isCellDateFormatted(cell)) { rowData.put(columnIndex, cell.getDateCellValue()); } else { rowData.put(columnIndex, cell.getNumericCellValue()); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 布尔 rowData.put(columnIndex, cell.getBooleanCellValue()); } else { // 字符串 rowData.put(columnIndex, cell.getStringCellValue()); } } excelData.add(rowData); } // workbook.close(); return excelData; } public static List<Map<Integer, Object>> parseExcel2(MultipartFile excel) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(excel.getInputStream()); XSSFSheet sheet = workbook.getSheetAt(0); int lastRowIndex = sheet.getLastRowNum(); List<Map<Integer, Object>> excelData = new ArrayList<>(); for (int i = 1; i <= lastRowIndex; i++) { XSSFRow row = sheet.getRow(i); if (null == row ) continue; Iterator<Cell> cells = row.cellIterator(); Map<Integer, Object> rowData = new HashMap<>(); while (cells.hasNext()) { Cell cell = cells.next(); Integer columnIndex = cell.getColumnIndex(); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 数值 if (DateUtil.isCellDateFormatted(cell)) { rowData.put(columnIndex, cell.getDateCellValue()); } else { rowData.put(columnIndex, cell.getNumericCellValue()); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 布尔 rowData.put(columnIndex, cell.getBooleanCellValue()); } else { // 字符串 rowData.put(columnIndex, cell.getStringCellValue()); } } excelData.add(rowData); } // workbook.close(); return excelData; } /** * desc: 导出excel表格 * author: liuchenyu * date: 2017/4/8 14:30 * * @param titles Excel表各列字段名 * @param sheetname 工作表标签名 * @param data 导出的数据源 * @param filename 导出的文件名 * @param response * @param request * @throws Exception */ public static void exportExcel(String[] titles, String sheetname, List<Map<String , Object>> data, String filename, ArrayList<String> list,HttpServletResponse response, HttpServletRequest request) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(sheetname); HSSFRow row = sheet.createRow(0); for (int i = 0; i < titles.length; i++) { row.createCell(i).setCellValue(titles[i]); } for (int i = 0; i < data.size(); i++) { Map<String , Object> obj = data.get(i); row = sheet.createRow(i + 1); for (int j = 0; j < list.size(); j++) { String key = list.get(j); HSSFCell cell = row.createCell(j); cell.setCellValue(obj.get(key)==null?"--":obj.get(key)+""); } } //filename = new String(filename.getBytes("gbk-8"), "iso8859-1"); String encoding = "utf-8"; String userAgent = request.getHeader("user-agent"); System.out.println("userAgent: " + userAgent); if (userAgent.toLowerCase().indexOf("msie") != -1) { encoding = "gbk"; } filename = new String(filename.getBytes(encoding), "iso8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); workbook.write(response.getOutputStream()); // workbook.close(); } public static void downloadExcel(HttpServletResponse response, Workbook workbook, String execelName) { ByteArrayOutputStream os = new ByteArrayOutputStream(); try { workbook.write(os); } catch (IOException e) { logger.error("write data to ByteArrayOutputStream fail.", e); } byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 //HttpServletResponse response = WebUtils.getResponse(); //response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); ServletOutputStream out = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String format = sdf.format(new Date()); try { response.setHeader("Content-Disposition", "attachment;filename=" + new String((execelName + format + ".xls").getBytes(), "iso-8859-1")); out = response.getOutputStream(); } catch (Exception e1) { logger.error("write data to ServletOutputStream fail.", e1); } BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final IOException e) { logger.error("write data to ServletOutputStream fail.", e); } finally { if (bis != null) try { bis.close(); } catch (IOException e) { logger.error("close InputStream fail.", e); } if (bos != null) try { bos.close(); } catch (IOException e) { logger.error("close OutputStream fail.", e); } } } public static void noDataExcel(HttpServletResponse response, String fileName) { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("1"); Row row = sheet.createRow((short) 0); row.createCell(0).setCellValue("没有数据"); row = sheet.createRow( 1); row.createCell(0).setCellValue("没有找到数据 - - !!!!!"); ExcelUtil.downloadExcel(response, wb, fileName); } }
/** * 导出列表 */ $scope.exportList = function () { var postData = { page: $scope.paginationConf.currentPage, pageSize: $scope.paginationConf.itemsPerPage, warehouseId:$scope.warehouseId }; if ($scope.submitted == 'pending') { postData.status = 0; } if ($scope.review == 'pending') { postData.status = 1; } if($scope.inboundNum){ postData.inboundNum = $scope.inboundNum; sessionStorage.setItem('inboundNum', $scope.inboundNum); }else { delete postData.inboundNum; sessionStorage.removeItem('inboundNum'); } if($scope.linkDocType || $scope.linkDocType==''){ postData.linkDocType = $scope.linkDocType; sessionStorage.setItem('linkDocType', $scope.linkDocType); }else { delete postData.linkDocType; sessionStorage.removeItem('linkDocType'); } if($scope.linkDocNum){ postData.linkDocNum = $scope.linkDocNum; sessionStorage.setItem('linkDocNum', $scope.linkDocNum); }else { delete postData.linkDocNum; sessionStorage.removeItem('linkDocNum'); } if($scope.supplierName!='' &&$scope.supplierName!=undefined){ postData.supplierName = $scope.supplierName; sessionStorage.setItem('supplierName', $scope.supplierName); }else { delete postData.supplierName; sessionStorage.removeItem('supplierName'); } if($scope.supplierId){ postData.supplierId = $scope.supplierId; sessionStorage.setItem('supplierId', $scope.supplierId); }else if($scope.supplierId!='' && $scope.supplierId!=undefined){ $scope.supplierId = Number(sessionStorage.getItem('supplierId')); postData.supplierId = $scope.supplierId; }else { delete postData.supplierId; sessionStorage.removeItem('supplierId'); } if($('#date_first').val() && $('#date_last').val()){ postData.createStartDate = $('#date_first').val() + ' 00:00:00'; postData.createEndDate = $('#date_last').val() + ' 23:59:59'; sessionStorage.setItem('createStartDate', $('#date_first').val()); sessionStorage.setItem('createEndDate', $('#date_last').val()); }else if(sessionStorage.getItem('createStartDate')){ $('#date_first').val(sessionStorage.getItem('createStartDate')); $('#date_last').val(sessionStorage.getItem('createEndDate')); postData.createStartDate = $('#date_first').val() + ' 00:00:00'; postData.createEndDate = $('#date_last').val() + ' 23:59:59'; }else { delete postData.createStartDate; delete postData.createEndDate; sessionStorage.removeItem('createStartDate'); sessionStorage.removeItem('createEndDate'); } $http({ url: adminUrl+'scp/inbound/exportList', method: "GET",//接口方法 params: postData, headers: { 'Content-type': 'application/json' }, responseType: 'arraybuffer' }).success(function (data, status, headers, config) { var blob = new Blob([data], {type: "application/vnd.ms-excel"}); var objectUrl = URL.createObjectURL(blob); var a = document.createElement('a'); document.body.appendChild(a); a.setAttribute('style', 'display:none'); a.setAttribute('href', objectUrl); var filename="入库记录.xls"; a.setAttribute('download', filename); a.click(); URL.revokeObjectURL(objectUrl); }).error(function (data, status, headers, config) { }); };
