前言
java操纵excel文件常用的有jxl和poi两种方式，其中最主要的区别在于jxl不支持.xlsx，而poi支持.xlsx。
这里介绍的使用poi方式，poi提供了HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook。
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
第一种：HSSFWorkbook
poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）。
第二种：XSSFWorkbook
这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。
第三种：SXSSFWorkbook
从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。
SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。
SXSSF与XSSF的对比：
- a. 在一个时间点上，只可以访问一定数量的数据
- b. 不再支持Sheet.clone()
- c. 不再支持公式的求值
- d. 在使用Excel模板下载数据时将不能动态改变表头，因为这种方式已经提前把excel写到硬盘的了就不能再改了
当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
实现步骤
本次使用XSSFWorkbook，HSSFWorkbook实现请移步：使用HSSFWorkbook导出、操作excel
创建流程：(上级为 下级的载体)
1:.创建 工作簿
2.创建 sheet（可以创建多个）
3.创建行
4.创建单元格
引入相关依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency> 读：
/**
* 读取Excel文件的内容
* @param inputStream excel文件，以InputStream的形式传入
* @param sheetName sheet名字
* @return 以List返回excel中内容
*/
public static List<Map<String, String>> readExcel(InputStream inputStream, String sheetName) {
//定义工作簿
XSSFWorkbook xssfWorkbook = null;
try {
xssfWorkbook = new XSSFWorkbook(inputStream);
} catch (Exception e) {
System.out.println("Excel data file cannot be found!");
}
//定义工作表
XSSFSheet xssfSheet;
if (sheetName.equals("")) {
// 默认取第一个子表
xssfSheet = xssfWorkbook.getSheetAt(0);
} else {
xssfSheet = xssfWorkbook.getSheet(sheetName);
}
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
//定义行
//默认第一行为标题行，index = 0
XSSFRow titleRow = xssfSheet.getRow(0);
//循环取每行的数据
for (int rowIndex = 1; rowIndex < xssfSheet.getPhysicalNumberOfRows(); rowIndex++) {
XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
if (xssfRow == null) {
continue;
}
Map<String, String> map = new LinkedHashMap<String, String>();
//循环取每个单元格(cell)的数据
for (int cellIndex = 0; cellIndex < xssfRow.getPhysicalNumberOfCells(); cellIndex++) {
XSSFCell titleCell = titleRow.getCell(cellIndex);
XSSFCell xssfCell = xssfRow.getCell(cellIndex);
map.put(getString(titleCell),getString(xssfCell));
}
list.add(map);
}
return list;
}
/**
* 把单元格的内容转为字符串
* @param xssfCell 单元格
* @return 字符串
*/
public static String getString(XSSFCell xssfCell) {
if (xssfCell == null) {
return "";
}
if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else {
return xssfCell.getStringCellValue();
}
} 写：
/**
* 把内容写入Excel
* @param list 传入要写的内容，此处以一个List内容为例，先把要写的内容放到一个list中
* @param outputStream 把输出流怼到要写入的Excel上，准备往里面写数据
*/
public static void writeExcel(List<List> list, OutputStream outputStream) {
//创建工作簿
XSSFWorkbook xssfWorkbook = null;
xssfWorkbook = new XSSFWorkbook();
//创建工作表
XSSFSheet xssfSheet;
xssfSheet = xssfWorkbook.createSheet();
//创建行
XSSFRow xssfRow;
//创建列，即单元格Cell
XSSFCell xssfCell;
//把List里面的数据写到excel中
for (int i=0;i<list.size();i++) {
//从第一行开始写入
xssfRow = xssfSheet.createRow(i);
//创建每个单元格Cell，即列的数据
List sub_list =list.get(i);
for (int j=0;j<sub_list.size();j++) {
xssfCell = xssfRow.createCell(j); //创建单元格
xssfCell.setCellValue((String)sub_list.get(j)); //设置单元格内容
}
}
//用输出流写到excel
try {
xssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();
}catch (IOException e) {
e.printStackTrace();
}
} 工具类：
附：把一个Map中的所有键和值分别放到一个list中，再把这两个list整个放到一个大的list里面，即 [ [key1,key2,key3...] , [value1,value2,value3...] ]
public static List<List> convertMapToList(Map map) {
List<List> list = new ArrayList<List>();
List<String> key_list = new LinkedList<String>();
List<String> value_list = new LinkedList<String>();
Set<Entry<String,String>> set = map.entrySet();
Iterator<Entry<String,String>> iter1 = set.iterator();
while (iter1.hasNext()) {
key_list.add(iter1.next().getKey());
}
list.add(key_list);
Collection<String> value = map.values();
Iterator<String> iter2 = value.iterator();
while (iter2.hasNext()) {
value_list.add(iter2.next());
}
list.add(value_list);
return list;
} 项目实例代码：
/**
* 下载汇总数据（月餐费）
* 汇总步骤:遍历单位,在单位中遍历月餐费表人员,判断遍历单位与人员单位是否一致;
* 在单位中遍历临时卡公务卡人员,通过临时卡公务卡绑定人员表,判断遍历单位与人员单位是否一致
*/
public void exportSumDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)throws Exception{
Context ctx = SHRContext.getInstance().getContext();
String templateName = "月餐费汇总结果";
String initPath = (new StringBuilder()).append(System.getProperty("EAS_HOME")).append("/server/deploy/easweb.ear/eas_web.war/perTemplate/").append(templateName).append(".xlsx").toString();
SimpleDateFormat DateFormat = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
File file = new File(initPath);//File类型可以是文件也可以是文件夹
String yearMonthTime = request.getParameter("yearMonthTime");
String CmpSchemeId = request.getParameter("CmpSchemeId");
String[] split = yearMonthTime.split("-");
String title = split[0]+split[1]+"月餐费汇总结果";
yearMonthTime = yearMonthTime+"-01";
FileInputStream fileInputStream = null;
fileInputStream = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
//标题
XSSFRow row = sheet.getRow(0);
XSSFCell cell = row.getCell(0);
cell.setCellValue(title);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);// 设置字体大小
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
cell.getCellStyle().setFont(font);
//查询员工月餐费
int month = Integer.parseInt(split[1]);
StringBuffer sql = new StringBuffer();
sql.append(" where year = '"+split[0]+"' and month = '"+String.valueOf(month)+"' ");
if (CmpSchemeId!=null&&!CmpSchemeId.isEmpty()) {
StringBuffer personIds = new StringBuffer();
StringBuffer sql1 = new StringBuffer();
sql1.append("/*dialect*/ ");
sql1.append("SELECT FPERSONID FROM T_HR_SCmpCalTable ");
sql1.append("where FCALSCHEMEID ='"+CmpSchemeId+"' ");
sql1.append(" and FPERIODYEAR ='"+split[0]+"' ");
sql1.append("and FPERIODMONTH ='"+month+"' ");
IRowSet executeQuery1 = DbUtil.executeQuery(ctx, sql1.toString());
while (executeQuery1.next()) {
personIds.append(executeQuery1.getString("FPERSONID")).append("','");
}
executeQuery1.close();
if (personIds.length()>0) {
sql.append(" and personId in ('"+personIds.substring(0, personIds.length()-3)+"') ");
}
}
MonthMealSumResultCollection monthMealSumResultCollection = MonthMealSumResultFactory.getLocalInstance(ctx).getMonthMealSumResultCollection(sql.toString());
//查询员公务卡临时卡餐费(绑定人员)
// List<Map<String, String>> tempList = new ArrayList<Map<String,String>>();
// String stardate = yearMonthTime;
// String enddate = DateFormat.format(getDayEndTime(DateFormat.parse(yearMonthTime)));
// StringBuffer tempSql = new StringBuffer();
// tempSql.append("/*dialect*/ ");
// tempSql.append("SELECT FPERSONNUMBER, ");
// tempSql.append("sum(FBREAKFAST) BREAKFAST,sum(FLUNCH) LUNCH,sum(FDINNER) DINNER, ");
// tempSql.append("sum(FBREAKFASTGRMONEY) BREAKFASTGRMONEY,sum(FLUNCHGRMONEY) LUNCHGRMONEY,sum(FDINNERTGRMONEY) DINNERTGRMONEY ");
// tempSql.append("FROM T_ATS_GlHaveMeals ");
// tempSql.append("where FEATTIME >= '"+stardate+"' ");
// tempSql.append("and FEATTIME <= '"+enddate+"' ");
// tempSql.append(" GROUP BY FPERSONNUMBER");
// IRowSet tempExecuteQuery = DbUtil.executeQuery(ctx, tempSql.toString());
// String FPERSONNUMBER = "";//卡号
// String pername = "";//卡名称
// int BREAKFAST = 0;//早餐次数
// int LUNCH = 0;//午餐次数
// int DINNER = 0;//晚餐次数
// BigDecimal BREAKFASTGRMONEY = new BigDecimal("0");//早餐费用
// BigDecimal LUNCHGRMONEY = new BigDecimal("0");//午餐费用
// BigDecimal DINNERTGRMONEY = new BigDecimal("0");//晚餐费用
// IRowSet tempExecuteQuery1;
// String tempSql1 = "";
// while (tempExecuteQuery.next()) {
// Map<String, String> tempMap = new HashMap<String, String>();
// BigDecimal Sum = new BigDecimal("0");//合计
// FPERSONNUMBER = tempExecuteQuery.getString("FPERSONNUMBER");
// tempSql1 = "SELECT FPERSONNAME FROM T_ATS_GlHaveMeals where FEATTIME >= '"+stardate+"' and FEATTIME <= '"+enddate+"' and FPERSONNUMBER ='"+FPERSONNUMBER+"'";
// tempExecuteQuery1 = DbUtil.executeQuery(ctx, tempSql1);
// if (tempExecuteQuery1.next()) {
// pername = tempExecuteQuery1.getString("FPERSONNAME");
// }
// tempExecuteQuery1.close();
// BREAKFAST = tempExecuteQuery.getInt("BREAKFAST");
// LUNCH = tempExecuteQuery.getInt("LUNCH");
// DINNER = tempExecuteQuery.getInt("DINNER");
// BREAKFASTGRMONEY = tempExecuteQuery.getBigDecimal("BREAKFASTGRMONEY");
// LUNCHGRMONEY = tempExecuteQuery.getBigDecimal("LUNCHGRMONEY");
// DINNERTGRMONEY = tempExecuteQuery.getBigDecimal("DINNERTGRMONEY");
// Sum = Sum.add(BREAKFASTGRMONEY).add(LUNCHGRMONEY).add(DINNERTGRMONEY);
// tempMap.put("FPERSONNUMBER", FPERSONNUMBER);
// tempMap.put("BREAKFAST", String.valueOf(BREAKFAST));
// tempMap.put("LUNCH", String.valueOf(LUNCH));
// tempMap.put("DINNER", String.valueOf(DINNER));
// tempMap.put("BREAKFASTGRMONEY", BREAKFASTGRMONEY.stripTrailingZeros().toPlainString());
// tempMap.put("LUNCHGRMONEY", LUNCHGRMONEY.stripTrailingZeros().toPlainString());
// tempMap.put("DINNERTGRMONEY", DINNERTGRMONEY.stripTrailingZeros().toPlainString());
// tempMap.put("Sum", Sum.stripTrailingZeros().toPlainString());
// tempMap.put("pername", pername);
// tempList.add(tempMap);
// }
// tempExecuteQuery.close();
//调用公务临时卡餐费汇总Handler
String FPERSONNUMBER = "";//卡号
String stardate = yearMonthTime;
String enddate = DateFormat.format(getDayEndTime(DateFormat.parse(yearMonthTime)));
List<Map<String, Object>> queryData = getQueryData(stardate,enddate,"");
//查询单位
List<MonthMealSumResultInfo> list = new ArrayList<MonthMealSumResultInfo>();
List<Map<String, Object>> list1 = new ArrayList<Map<String,Object>>();
ICoreBase corebasexm = MetaDataServerUtil.getBizInterface(ctx, "com.kingdee.eas.hr.base.app.ZDY7HRBase");
CoreBaseCollection collxm = corebasexm.getCollection();
String orgUnit = "";
//从第3行开始插入
int a = 2;
int zBreakfastTimes = 0;
int zLunchTimes = 0;
int zDinnerTimes = 0;
BigDecimal zBreakfastCost = new BigDecimal("0");
BigDecimal zLunchCost = new BigDecimal("0");
BigDecimal zDinnerCost = new BigDecimal("0");
BigDecimal zCostSum = new BigDecimal("0");
String sql3 = "";
IRowSet executeQuery3;
String businSql = "";
IRowSet businExecuteQuery;
String businSql1 = "";
IRowSet businExecuteQuery1;
//遍历单位
for (int i = 0; i < collxm.size(); i++) {
orgUnit = collxm.get(i).getString("name_l2");
int BreakfastTimes = 0;
int LunchTimes = 0;
int DinnerTimes = 0;
BigDecimal BreakfastCost = new BigDecimal("0");
BigDecimal LunchCost = new BigDecimal("0");
BigDecimal DinnerCost = new BigDecimal("0");
BigDecimal CostSum = new BigDecimal("0");
//遍历人
for (int j = 0; j < monthMealSumResultCollection.size(); j++) {
MonthMealSumResultInfo monthMealSumResultInfo = monthMealSumResultCollection.get(j);
String personid = monthMealSumResultInfo.getPersonId().getId().toString();
//查询人员薪酬相关信息用人单位
sql3 = "SELECT b.FNAME_l2 FROM CT_MP_SALARYINFO a LEFT JOIN T_HR_ZDY7HRBase b on a.cforgunitid = b.FID where a.fpersonid = '"+personid+"'";
executeQuery3 = DbUtil.executeQuery(ctx, sql3);
String perorgUnit = "";
if (executeQuery3.next()) {
perorgUnit = executeQuery3.getString("FNAME_l2");
}else {
//第一遍遍历人
if (i == 0) {
list.add(monthMealSumResultInfo);
}
}
executeQuery3.close();
//判断查询单位和人的单位是否一致
if (orgUnit.equals(perorgUnit)) {
XSSFRow rowdata = sheet.createRow(a);
rowdata.createCell(0).setCellValue(orgUnit);//单位
rowdata.createCell(1).setCellValue(monthMealSumResultInfo.getSimpleName());//职员编码
rowdata.createCell(2).setCellValue(monthMealSumResultInfo.getName());//职员
rowdata.createCell(3).setCellValue(monthMealSumResultInfo.getDescription());//部门长名称
rowdata.createCell(4).setCellValue(monthMealSumResultInfo.getYear());//年份
rowdata.createCell(5).setCellValue(monthMealSumResultInfo.getMonth());//月份
rowdata.createCell(6).setCellValue(String.valueOf(monthMealSumResultInfo.getBreakfastTimes()));//早餐次数
rowdata.createCell(7).setCellValue(String.valueOf(monthMealSumResultInfo.getLunchTimes()));//午餐次数
rowdata.createCell(8).setCellValue(String.valueOf(monthMealSumResultInfo.getDinnerTimes()));//晚餐次数
rowdata.createCell(9).setCellValue(monthMealSumResultInfo.getBreakfastCost().stripTrailingZeros().toPlainString());//早餐费用
rowdata.createCell(10).setCellValue(monthMealSumResultInfo.getLunchCost().stripTrailingZeros().toPlainString());//午餐费用
rowdata.createCell(11).setCellValue(monthMealSumResultInfo.getDinnerCost().stripTrailingZeros().toPlainString());//晚餐费用
rowdata.createCell(12).setCellValue(monthMealSumResultInfo.getCostSum().stripTrailingZeros().toPlainString());//费用合计
BreakfastTimes+=monthMealSumResultInfo.getBreakfastTimes();//合计早餐次数
LunchTimes+=monthMealSumResultInfo.getLunchTimes();//合计午餐次数
DinnerTimes+=monthMealSumResultInfo.getDinnerTimes();//合计晚餐次数
BreakfastCost = BreakfastCost.add(monthMealSumResultInfo.getBreakfastCost());//合计早餐费用
LunchCost = LunchCost.add(monthMealSumResultInfo.getLunchCost());//合计午餐费用
DinnerCost = DinnerCost.add(monthMealSumResultInfo.getDinnerCost());//合计晚餐费用
CostSum = CostSum.add(monthMealSumResultInfo.getCostSum());//合计费用
a+=1;
}
}
//遍历公务卡临时卡人
for (Map<String, Object> tempMap : queryData) {
FPERSONNUMBER = String.valueOf(tempMap.get("PERSON_NUMBER"));//卡号
BigDecimal BREAKFASTGRMONEY = new BigDecimal("0");//早餐费用
BigDecimal LUNCHGRMONEY = new BigDecimal("0");//午餐费用
BigDecimal DINNERTGRMONEY = new BigDecimal("0");//晚餐费用
BigDecimal sum = new BigDecimal("0");//合计费用
//查询公务卡临时卡绑定人员id
businSql = "SELECT b.FID FROM T_HR_ZDY2HRBase a LEFT JOIN t_bd_person b on a.FNAME_l2 = b.FNUMBER where a.FNUMBER = '"+FPERSONNUMBER+"'";
businExecuteQuery = DbUtil.executeQuery(ctx, businSql);
String perid = "";
if (businExecuteQuery.next()) {
perid = businExecuteQuery.getString("FID");
//查询人员薪酬相关信息用人单位
businSql1 = "SELECT b.FNAME_l2 FROM CT_MP_SALARYINFO a LEFT JOIN T_HR_ZDY7HRBase b on a.cforgunitid = b.FID where a.fpersonid = '"+perid+"'";
businExecuteQuery1 = DbUtil.executeQuery(ctx, businSql1);
String perorgUnit1 = "";
if (businExecuteQuery1.next()) {
perorgUnit1 = businExecuteQuery1.getString("FNAME_l2");
}else {
//第一遍遍历人
if (i == 0) {
list1.add(tempMap);
}
}
businExecuteQuery1.close();
//判断查询单位和人的单位是否一致
if (orgUnit.equals(perorgUnit1)) {
XSSFRow rowdata = sheet.createRow(a);
rowdata.createCell(0).setCellValue(orgUnit);//单位
rowdata.createCell(1).setCellValue(FPERSONNUMBER);//职员编码
rowdata.createCell(2).setCellValue(String.valueOf(tempMap.get("PERSON_NAME")));//职员
rowdata.createCell(3).setCellValue(String.valueOf(tempMap.get("DEPARTMENT_LongNAME,")==null?"":tempMap.get("DEPARTMENT_LongNAME,")));//部门长名称
rowdata.createCell(4).setCellValue(split[0]);//年份
rowdata.createCell(5).setCellValue(String.valueOf(month));//月份
rowdata.createCell(6).setCellValue(String.valueOf(tempMap.get("CCCS")));//早餐次数
rowdata.createCell(7).setCellValue(String.valueOf(tempMap.get("ZCCS")));//午餐次数
rowdata.createCell(8).setCellValue(String.valueOf(tempMap.get("WCCS")));//晚餐次数
BREAKFASTGRMONEY = BREAKFASTGRMONEY.add((BigDecimal)tempMap.get("CCCS_FY"));
LUNCHGRMONEY = LUNCHGRMONEY.add((BigDecimal)tempMap.get("ZCCS_FY"));
DINNERTGRMONEY = DINNERTGRMONEY.add((BigDecimal)tempMap.get("WCCS_FY"));
sum = sum.add((BigDecimal)tempMap.get("HJ_FY"));
rowdata.createCell(9).setCellValue(BREAKFASTGRMONEY.stripTrailingZeros().toPlainString());//早餐费用
rowdata.createCell(10).setCellValue(LUNCHGRMONEY.stripTrailingZeros().toPlainString());//午餐费用
rowdata.createCell(11).setCellValue(DINNERTGRMONEY.stripTrailingZeros().toPlainString());//晚餐费用
rowdata.createCell(12).setCellValue(sum.stripTrailingZeros().toPlainString());//费用合计
BreakfastTimes+=Integer.parseInt(String.valueOf(tempMap.get("CCCS")));//合计早餐次数
LunchTimes+=Integer.parseInt(String.valueOf(tempMap.get("ZCCS")));//合计午餐次数
DinnerTimes+=Integer.parseInt(String.valueOf(tempMap.get("WCCS")));//合计晚餐次数
BreakfastCost = BreakfastCost.add(BREAKFASTGRMONEY);//合计早餐费用
LunchCost = LunchCost.add(LUNCHGRMONEY);//合计午餐费用
DinnerCost = DinnerCost.add(DINNERTGRMONEY);//合计晚餐费用
CostSum = CostSum.add(sum);//合计费用
a+=1;
}
}else {
//第一遍遍历人
if (i == 0) {
list1.add(tempMap);
}
}
businExecuteQuery.close();
}
//统计此单位人员
XSSFRow rowdata = sheet.createRow(a);
XSSFCell cell0 = rowdata.createCell(0);
cell0.setCellValue(orgUnit+" 汇总");//单位
//设置格式
Font orgunitfont = workbook.createFont();
orgunitfont.setBold(true);//粗体显示
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(orgunitfont);
cell0.setCellStyle(cellStyle);
rowdata.createCell(1).setCellValue("");//职员编码
rowdata.createCell(2).setCellValue("");//职员
rowdata.createCell(3).setCellValue("");//部门长名称
rowdata.createCell(4).setCellValue(split[0]);//年份
rowdata.createCell(5).setCellValue(String.valueOf(month));//月份
rowdata.createCell(6).setCellValue(String.valueOf(BreakfastTimes));//早餐次数
rowdata.createCell(7).setCellValue(String.valueOf(LunchTimes));//午餐次数
rowdata.createCell(8).setCellValue(String.valueOf(DinnerTimes));//晚餐次数
rowdata.createCell(9).setCellValue(BreakfastCost.stripTrailingZeros().toPlainString());//早餐费用
rowdata.createCell(10).setCellValue(LunchCost.stripTrailingZeros().toPlainString());//午餐费用
rowdata.createCell(11).setCellValue(DinnerCost.stripTrailingZeros().toPlainString());//晚餐费用
rowdata.createCell(12).setCellValue(CostSum.stripTrailingZeros().toPlainString());//费用合计
a+=1;
zBreakfastTimes+=BreakfastTimes;//总计早餐次数
zLunchTimes+=LunchTimes;//总计午餐次数
zDinnerTimes+=DinnerTimes;//总计晚餐次数
zBreakfastCost = zBreakfastCost.add(BreakfastCost);//总计早餐费用
zLunchCost = zLunchCost.add(LunchCost);//总计午餐费用
zDinnerCost = zDinnerCost.add(DinnerCost);//总计晚餐费用
zCostSum = zCostSum.add(CostSum);//总计费用
}
//总计
XSSFRow rowdata = sheet.createRow(a);
XSSFCell cell0 = rowdata.createCell(0);
cell0.setCellValue("总计");//单位
//设置格式
Font orgunitfont = workbook.createFont();
orgunitfont.setBold(true);//粗体显示
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(orgunitfont);
cell0.setCellStyle(cellStyle);
rowdata.createCell(1).setCellValue("");//职员编码
rowdata.createCell(2).setCellValue("");//职员
rowdata.createCell(3).setCellValue("");//部门长名称
rowdata.createCell(4).setCellValue(split[0]);//年份
rowdata.createCell(5).setCellValue(String.valueOf(month));//月份
rowdata.createCell(6).setCellValue(zBreakfastTimes);//早餐次数
rowdata.createCell(7).setCellValue(zLunchTimes);//午餐次数
rowdata.createCell(8).setCellValue(zDinnerTimes);//晚餐次数
rowdata.createCell(9).setCellValue(zBreakfastCost.stripTrailingZeros().toPlainString());//早餐费用
rowdata.createCell(10).setCellValue(zLunchCost.stripTrailingZeros().toPlainString());//午餐费用
rowdata.createCell(11).setCellValue(zDinnerCost.stripTrailingZeros().toPlainString());//晚餐费用
rowdata.createCell(12).setCellValue(zCostSum.stripTrailingZeros().toPlainString());//费用合计
a+=2;
//未分类单位人员
XSSFRow rowdataNo = sheet.createRow(a);
XSSFCell Nopercell0 = rowdataNo.createCell(0);
Nopercell0.setCellValue("未分类人员：");//单位
//设置格式
Font Noperfont = workbook.createFont();
Noperfont.setBold(true);//粗体显示
XSSFCellStyle cellStyleNoper = workbook.createCellStyle();
cellStyleNoper.setFont(Noperfont);
Nopercell0.setCellStyle(cellStyleNoper);
a+=1;
for (MonthMealSumResultInfo monthMealSumResultInfo : list) {
XSSFRow rowdataNoper = sheet.createRow(a);
rowdataNoper.createCell(0).setCellValue("");//单位
rowdataNoper.createCell(1).setCellValue(monthMealSumResultInfo.getSimpleName());//职员编码
rowdataNoper.createCell(2).setCellValue(monthMealSumResultInfo.getName());//职员
rowdataNoper.createCell(3).setCellValue(monthMealSumResultInfo.getDescription());//部门长名称
rowdataNoper.createCell(4).setCellValue(monthMealSumResultInfo.getYear());//年份
rowdataNoper.createCell(5).setCellValue(monthMealSumResultInfo.getMonth());//月份
rowdataNoper.createCell(6).setCellValue(String.valueOf(monthMealSumResultInfo.getBreakfastTimes()));//早餐次数
rowdataNoper.createCell(7).setCellValue(String.valueOf(monthMealSumResultInfo.getLunchTimes()));//午餐次数
rowdataNoper.createCell(8).setCellValue(String.valueOf(monthMealSumResultInfo.getDinnerTimes()));//晚餐次数
rowdataNoper.createCell(9).setCellValue(monthMealSumResultInfo.getBreakfastCost().stripTrailingZeros().toPlainString());//早餐费用
rowdataNoper.createCell(10).setCellValue(monthMealSumResultInfo.getLunchCost().stripTrailingZeros().toPlainString());//午餐费用
rowdataNoper.createCell(11).setCellValue(monthMealSumResultInfo.getDinnerCost().stripTrailingZeros().toPlainString());//晚餐费用
rowdataNoper.createCell(12).setCellValue(monthMealSumResultInfo.getCostSum().stripTrailingZeros().toPlainString());//费用合计
a+=1;
}
for (Map<String, Object> tempMap : list1) {
BigDecimal BREAKFASTGRMONEY = new BigDecimal("0");//早餐费用
BigDecimal LUNCHGRMONEY = new BigDecimal("0");//午餐费用
BigDecimal DINNERTGRMONEY = new BigDecimal("0");//晚餐费用
BigDecimal sum = new BigDecimal("0");//合计费用
XSSFRow rowdataNoper = sheet.createRow(a);
rowdataNoper.createCell(0).setCellValue("");//单位
rowdataNoper.createCell(1).setCellValue(String.valueOf(tempMap.get("PERSON_NUMBER")));//职员编码
rowdataNoper.createCell(2).setCellValue(String.valueOf(tempMap.get("PERSON_NAME")));//职员
rowdataNoper.createCell(3).setCellValue(String.valueOf(tempMap.get("DEPARTMENT_LongNAME,")==null?"":tempMap.get("DEPARTMENT_LongNAME,")));//部门长名称
rowdataNoper.createCell(4).setCellValue(split[0]);//年份
rowdataNoper.createCell(5).setCellValue(String.valueOf(month));//月份
rowdataNoper.createCell(6).setCellValue(String.valueOf(tempMap.get("CCCS")));//早餐次数
rowdataNoper.createCell(7).setCellValue(String.valueOf(tempMap.get("ZCCS")));//午餐次数
rowdataNoper.createCell(8).setCellValue(String.valueOf(tempMap.get("WCCS")));//晚餐次数
BREAKFASTGRMONEY = BREAKFASTGRMONEY.add((BigDecimal)tempMap.get("CCCS_FY"));
LUNCHGRMONEY = LUNCHGRMONEY.add((BigDecimal)tempMap.get("ZCCS_FY"));
DINNERTGRMONEY = DINNERTGRMONEY.add((BigDecimal)tempMap.get("WCCS_FY"));
sum = sum.add((BigDecimal)tempMap.get("HJ_FY"));
rowdataNoper.createCell(9).setCellValue(BREAKFASTGRMONEY.stripTrailingZeros().toPlainString());//早餐费用
rowdataNoper.createCell(10).setCellValue(LUNCHGRMONEY.stripTrailingZeros().toPlainString());//午餐费用
rowdataNoper.createCell(11).setCellValue(DINNERTGRMONEY.stripTrailingZeros().toPlainString());//晚餐费用
rowdataNoper.createCell(12).setCellValue(sum.stripTrailingZeros().toPlainString());//费用合计
a+=1;
}
String time = sdf.format(new Date());
String outPath = new StringBuilder().append(Constants.TEMP_DIR).append(title).append(time).append(".xlsx").toString();
FileOutputStream out = new FileOutputStream(outPath);
workbook.write(out);
out.close();
setExportExcelHead(response, outPath, title+time);
}
//调用系统自带导出功能
public void setExportExcelHead(HttpServletResponse response, String path, String fileName)
throws Exception{
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", (new StringBuilder()).append("attachment;filename=").append(URLEncoder.encode((new StringBuilder()).append(fileName).append(".xlsx").toString(), "UTF-8")).toString());
OutputStream out = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(path));
byte buffer[] = new byte[5120];
int len;
while((len = bis.read(buffer)) != -1)
out.write(buffer, 0, len);
out.flush();
out.close();
}
// 获取某个日期的开始时间
public static Timestamp getDayStartTime(Date d) {
Calendar calendar = Calendar.getInstance();
if (null != d)
calendar.setTime(d);
calendar.set(calendar.get(Calendar.YEAR), calendar.get(Calendar.MONTH), calendar.get(Calendar.DAY_OF_MONTH), 0,
0, 0);
calendar.set(Calendar.MILLISECOND, 0);
return new Timestamp(calendar.getTimeInMillis());
}
// 获取某个日期的结束时间
public static Timestamp getDayEndTime(Date d) {
Calendar calendar = Calendar.getInstance();
if (null != d)
calendar.setTime(d);
int day = calendar.getActualMaximum(5);
calendar.set(calendar.DAY_OF_MONTH, day);
calendar.set(calendar.get(Calendar.YEAR), calendar.get(Calendar.MONTH), calendar.get(Calendar.DAY_OF_MONTH), 23,
59, 59);
calendar.set(Calendar.MILLISECOND, 999);
return new Timestamp(calendar.getTimeInMillis());
} 模板：月餐费汇总结果.xlsx
导出后:
