为了以后不再百度别人,我自己做个备忘吧。
直接上项目代码,步骤:(1)读取模板(2)插入数据(3)下载
用到的东西:# 设置sheet页名称 # 设置单元格边框样式 #行下移 # 单元格合并 #设置单元格格式(字体,行高,边框,列宽,内容对齐方式)#设置单元格公式 #设置公式强制计算
@RequestMapping("/exprotData.do")
public void exprotData(Integer id,HttpServletRequest request,HttpServletResponse response,String sheetName) {
//查询报价单信息
ActivityOfferSheetEntity sheet = sheetService.getSheetById(id);
List<ActivityOfferSheetDetailEntity> details = sheet.getSheetDetail();
String EXCEL_PATH = "erpExcelTemplate/quotationSheet.xlsx";
String realPath = request.getSession().getServletContext().getRealPath(EXCEL_PATH);
//软体详情
List<ActivityOfferSheetDetailEntity> ruanti =sheetService.getQuotationDetail(sheet.getId(),"软体");
//物料详情
List<ActivityOfferSheetDetailEntity> wuliao =sheetService.getQuotationDetail(sheet.getId(),"物料");
//人员差旅详情
List<ActivityOfferSheetDetailEntity> renyuan =sheetService.getQuotationDetail(sheet.getId(),"人员差旅");
//费用统计详情
//List<ActivityOfferSheetDetailEntity> tongji =sheetService.getQuotationDetail(sheet.getId(),"费用统计");
//其他
List<ActivityOfferSheetDetailEntity> other =sheetService.getQuotationDetail(sheet.getId(),"其他");
//读取模板
try {
InputStream input = new FileInputStream(realPath);
Workbook book = new XSSFWorkbook(input);
XSSFFormulaEvaluator e = new XSSFFormulaEvaluator((XSSFWorkbook) book);
book.setSheetName(0, sheetName);
Sheet sheet0 = book.getSheetAt(0);
//sheet0.setForceFormulaRecalculation(true);
//设置列宽 1/256
//sheet0.setColumnWidth(0, 50*256);
Row row = sheet0.getRow(1);
Cell rowData = row.getCell(1);
//String cellValue = rowData.getStringCellValue();
//项目名称
rowData.setCellValue(sheet.getProgramName());
//合并单元格
//sheet0.addMergedRegion(new CellRangeAddress(6,6,0,3));
//项目编号
row.getCell(6).setCellValue(sheet.getProgramCode());
// String str = row.getCell(2).getStringCellValue();
// String str2 = row.getCell(4).getStringCellValue();
Row row2 = sheet0.getRow(2);
//收件人
row2.getCell(1).setCellValue(sheet.getReceiver());
//发件人
row2.getCell(6).setCellValue(sheet.getSender());
Row row3 = sheet0.getRow(3);
//收件人电话
row3.getCell(1).setCellValue(sheet.getReceiverTel());
//发件人电话
row3.getCell(6).setCellValue(sheet.getSenderTel());
Row row4 = sheet0.getRow(4);
//收件人传真
row4.getCell(1).setCellValue(sheet.getReceiverFax());
//发件人传真
row4.getCell(6).setCellValue(sheet.getSenderFax());
Row row5 = sheet0.getRow(5);
//地点
row5.getCell(1).setCellValue(sheet.getLocation());
//报价时间
row5.getCell(6).setCellValue(sheet.getBudgetDate());
//设置样式
CellStyle cellStyle = book.createCellStyle();
Font font = book.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("微软雅黑");
cellStyle.setFont(font);
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
//第十四列是 合计
//从9行开始 软体
//记录到了第几行
int rowNumber = 9;
/* 小计单独占一行 */
int mergeNumber = 9;
//插入excel软体数据
for(ActivityOfferSheetDetailEntity data : ruanti) {
sheet0.shiftRows(rowNumber, sheet0.getLastRowNum(), 1,true,false);
Row new_ = sheet0.createRow(rowNumber);
new_.setHeightInPoints(18);
Cell c0 = new_.createCell(0);c0.setCellStyle(cellStyle);c0.setCellValue("软体");
Cell c1 = new_.createCell(1);c1.setCellStyle(cellStyle);c1.setCellValue(data.getProgramName());
Cell c2 = new_.createCell(2);c2.setCellStyle(cellStyle);c2.setCellValue(data.getSpecifications());
Cell c3 = new_.createCell(3);c3.setCellStyle(cellStyle);c3.setCellValue(data.getAmount());
Cell c4 = new_.createCell(4);c4.setCellStyle(cellStyle);c4.setCellValue(data.getUnit());
Cell c5 = new_.createCell(5);c5.setCellStyle(cellStyle);c5.setCellValue(data.getTimes());
Cell c6 = new_.createCell(6);c6.setCellStyle(cellStyle);c6.setCellValue(data.getUnitPrice() == null?null:data.getUnitPrice().toString());
//合计
Cell c14 = new_.createCell(14);c14.setCellStyle(cellStyle);//c14.setCellValue(data.getTotal()==null?null:data.getTotal().toString());
c14.setCellType(XSSFCell.CELL_TYPE_FORMULA);
c14.setCellFormula("D"+(rowNumber+1)+"*F"+(rowNumber+1)+"*G"+(rowNumber+1));
//备注
Cell c15 = new_.createCell(15);c15.setCellStyle(cellStyle);c15.setCellValue(data.getRemark());
rowNumber++;
}
//计算小记
Cell cell_ruanti = sheet0.getRow(rowNumber).getCell(14);
cell_ruanti.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//cell_ruanti.setCellValue();
cell_ruanti.setCellFormula("SUM(o"+(mergeNumber+1)+":o"+rowNumber+")");
//合并
sheet0.addMergedRegion(new CellRangeAddress(mergeNumber,mergeNumber+ruanti.size(),0,0));
sheet0.getRow(10).getCell(0).setCellValue("软体");
mergeNumber = rowNumber+2;
rowNumber = mergeNumber ;
//插入excel物料数据
for(ActivityOfferSheetDetailEntity data : wuliao) {
sheet0.shiftRows(rowNumber, sheet0.getLastRowNum(), 1,true,false);
Row new_ = sheet0.createRow(rowNumber);
new_.setHeightInPoints(18);
Cell c0 = new_.createCell(0);c0.setCellStyle(cellStyle);c0.setCellValue("物料");
Cell c1 = new_.createCell(1);c1.setCellStyle(cellStyle);c1.setCellValue(data.getProgramName());
Cell c2 = new_.createCell(2);c2.setCellStyle(cellStyle);c2.setCellValue(data.getSpecifications());
Cell c3 = new_.createCell(3);c3.setCellStyle(cellStyle);c3.setCellValue(data.getAmount());
Cell c4 = new_.createCell(4);c4.setCellStyle(cellStyle);c4.setCellValue(data.getUnit());
Cell c5 = new_.createCell(5);c5.setCellStyle(cellStyle);c5.setCellValue(data.getTimes());
Cell c6 = new_.createCell(6);c6.setCellStyle(cellStyle);c6.setCellValue(data.getUnitPrice() == null?null:data.getUnitPrice().toString());
//合计
Cell c14 = new_.createCell(14);c14.setCellStyle(cellStyle);c14.setCellValue(data.getTotal()==null?null:data.getTotal().toString());
c14.setCellType(XSSFCell.CELL_TYPE_FORMULA);
c14.setCellFormula("D"+(rowNumber+1)+"*F"+(rowNumber+1)+"*G"+(rowNumber+1));
//备注
Cell c15 = new_.createCell(15);c15.setCellStyle(cellStyle);c15.setCellValue(data.getRemark());
rowNumber++;
}
//计算小记
Cell cell_wuliao = sheet0.getRow(rowNumber).getCell(14);
cell_wuliao.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//cell_ruanti.setCellValue();
cell_wuliao.setCellFormula("SUM(o"+(mergeNumber+1)+":o"+rowNumber+")");
//合并
sheet0.addMergedRegion(new CellRangeAddress(mergeNumber,mergeNumber+wuliao.size(),0,0));
sheet0.getRow(12).getCell(0).setCellValue("物料");
mergeNumber = rowNumber+2;
rowNumber = mergeNumber;
//插入excel人员差旅数据
for(ActivityOfferSheetDetailEntity data : renyuan) {
sheet0.shiftRows(rowNumber, sheet0.getLastRowNum(), 1,true,false);
Row new_ = sheet0.createRow(rowNumber);
new_.setHeightInPoints(18);
Cell c0 = new_.createCell(0);c0.setCellStyle(cellStyle);c0.setCellValue("人员差旅");
Cell c1 = new_.createCell(1);c1.setCellStyle(cellStyle);c1.setCellValue(data.getProgramName());
Cell c2 = new_.createCell(2);c2.setCellStyle(cellStyle);c2.setCellValue(data.getSpecifications());
Cell c3 = new_.createCell(3);c3.setCellStyle(cellStyle);c3.setCellValue(data.getAmount());
Cell c4 = new_.createCell(4);c4.setCellStyle(cellStyle);c4.setCellValue(data.getUnit());
Cell c5 = new_.createCell(5);c5.setCellStyle(cellStyle);c5.setCellValue(data.getTimes());
Cell c6 = new_.createCell(6);c6.setCellStyle(cellStyle);c6.setCellValue(data.getUnitPrice() == null?null:data.getUnitPrice().toString());
//合计
Cell c14 = new_.createCell(14);c14.setCellStyle(cellStyle);c14.setCellValue(data.getTotal()==null?null:data.getTotal().toString());
c14.setCellType(XSSFCell.CELL_TYPE_FORMULA);
c14.setCellFormula("D"+(rowNumber+1)+"*F"+(rowNumber+1)+"*G"+(rowNumber+1));
//备注
Cell c15 = new_.createCell(15);c15.setCellStyle(cellStyle);c15.setCellValue(data.getRemark());
rowNumber++;
}
//计算小记
Cell cell_renyuan = sheet0.getRow(rowNumber).getCell(14);
cell_renyuan.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//cell_ruanti.setCellValue();
cell_renyuan.setCellFormula("SUM(o"+(mergeNumber+1)+":o"+rowNumber+")");
//合并
sheet0.addMergedRegion(new CellRangeAddress(mergeNumber,mergeNumber+renyuan.size(),0,0));
sheet0.getRow(12).getCell(0).setCellValue("人员差旅");
mergeNumber = rowNumber+2;
rowNumber = mergeNumber;
//插入excel 其他数据
for(ActivityOfferSheetDetailEntity data : other) {
sheet0.shiftRows(rowNumber, sheet0.getLastRowNum(), 1,true,false);
Row new_ = sheet0.createRow(rowNumber);
new_.setHeightInPoints(18);
Cell c0 = new_.createCell(0);c0.setCellStyle(cellStyle);c0.setCellValue("其他");
Cell c1 = new_.createCell(1);c1.setCellStyle(cellStyle);c1.setCellValue(data.getProgramName());
Cell c2 = new_.createCell(2);c2.setCellStyle(cellStyle);c2.setCellValue(data.getSpecifications());
Cell c3 = new_.createCell(3);c3.setCellStyle(cellStyle);c3.setCellValue(data.getAmount());
Cell c4 = new_.createCell(4);c4.setCellStyle(cellStyle);c4.setCellValue(data.getUnit());
Cell c5 = new_.createCell(5);c5.setCellStyle(cellStyle);c5.setCellValue(data.getTimes());
Cell c6 = new_.createCell(6);c6.setCellStyle(cellStyle);c6.setCellValue(data.getUnitPrice() == null?null:data.getUnitPrice().toString());
//合计
Cell c14 = new_.createCell(14);c14.setCellStyle(cellStyle);c14.setCellValue(data.getTotal()==null?null:data.getTotal().toString());
c14.setCellType(XSSFCell.CELL_TYPE_FORMULA);
c14.setCellFormula("D"+(rowNumber+1)+"*F"+(rowNumber+1)+"*G"+(rowNumber+1));
//备注
Cell c15 = new_.createCell(15);c15.setCellStyle(cellStyle);c15.setCellValue(data.getRemark());
rowNumber++;
}
//计算小记
Cell cell_other = sheet0.getRow(rowNumber).getCell(14);
cell_other.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//cell_ruanti.setCellValue();
cell_other.setCellFormula("SUM(o"+(mergeNumber+1)+":o"+rowNumber+")");
//合并
sheet0.addMergedRegion(new CellRangeAddress(mergeNumber,mergeNumber+renyuan.size(),0,0));
sheet0.getRow(12).getCell(0).setCellValue("其他");
e.evaluateAll();
//下载
response.setHeader("Content-Disposition","attachment;filename=sheet.xlsx");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
OutputStream out = response.getOutputStream();
book.write(out);
input.close();
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}