java操纵excel文件，实现excel导入导出
前言
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(); } }
写1：
/** * 把内容写入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(); } }
写2：
/** * 把内容写入Excel * @param list 传入要写的内容，此处以一个List内容为例，先把要写的内容放到一个list中 * @param outputStream 把输出流怼到要写入的Excel上，准备往里面写数据 */ public static void writeExcel(List<Map<String, String>> list, ByteArrayOutputStream outputStream) { //创建工作簿 XSSFWorkbook xssfWorkbook = null; xssfWorkbook = new XSSFWorkbook(); //创建工作表 XSSFSheet xssfSheet; xssfSheet = xssfWorkbook.createSheet(); //创建行 XSSFRow xssfRow; //创建列，即单元格Cell XSSFCell xssfCell; if (list.size()>0) { // 第一行表头 xssfRow = xssfSheet.createRow(0); Object[] toArray = list.get(0).keySet().toArray(); for (int j=0;j<toArray.length;j++) { xssfCell = xssfRow.createCell(j); //创建单元格 xssfCell.setCellValue(toArray[j].toString()); //设置单元格内容 setReportStyle(xssfWorkbook, xssfCell, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, HSSFFont.BOLDWEIGHT_BOLD, HSSFColor.BLACK.index, (short) 14, "宋体"); } //把List里面的数据写到excel中 for (int i=0;i<list.size();i++) { //从第二行开始写入 xssfRow = xssfSheet.createRow(i+1); //创建每个单元格Cell，即列的数据 Object[] array = list.get(i).keySet().toArray(); for (int j=0;j<array.length;j++) { xssfCell = xssfRow.createCell(j); //创建单元格 xssfCell.setCellValue(list.get(i).get(array[j]).toString()); //设置单元格内容 } } } //用输出流写到excel try { xssfWorkbook.write(outputStream); outputStream.flush(); outputStream.close(); }catch (IOException e) { e.printStackTrace(); } } public static void setReportStyle(XSSFWorkbook workbook, XSSFCell cell, short zyjz, short sxjz, short bw, short color, short fh, String fontName) { XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle(); cellStyle.setAlignment(zyjz);//左右居中 cellStyle.setVerticalAlignment(sxjz);//上下居中 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框 cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框 cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框 cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框 cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(192, 192, 192)));//设置背景色 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//填充模式 cellStyle.setWrapText(true);// 自动调整行高[自动换行] Font font = workbook.createFont(); font.setBoldweight(bw);//粗体显示 font.setColor(color); font.setFontHeightInPoints(fh);// 设置字体大小 font.setFontName(fontName); cellStyle.setFont(font); cell.setCellStyle(cellStyle); }
工具类：
附：把一个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
导出后:
