Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManagerImpl.java
===================================================================
diff -u -r30944 -r31206
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManagerImpl.java (.../ReportFormsManagerImpl.java) (revision 30944)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManagerImpl.java (.../ReportFormsManagerImpl.java) (revision 31206)
@@ -3,26 +3,34 @@
*/
package com.forgon.disinfectsystem.reportforms.service;
+import java.awt.image.BufferedImage;
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
+import java.net.URLDecoder;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
+import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import sun.misc.BASE64Decoder;
+
import com.forgon.databaseadapter.service.DateQueryAdapter;
import com.forgon.disinfectsystem.diposablegoods.service.DiposableGoodBatchStockManager;
import com.forgon.disinfectsystem.diposablegoods.service.GodownEntryManager;
@@ -44,10 +52,12 @@
import java.text.SimpleDateFormat;
+import javax.imageio.ImageIO;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
/**
* @author WangYi
@@ -411,5 +421,162 @@
e.printStackTrace();
}
}
- };
+ }
+
+ /**
+ * 通用导出图片到excel
+ * @param fileContents base64图片内容
+ * @param fileName excel文件名
+ * @param fileSizes 文件尺寸，宽:高，因为在excel中是按单元格来设置图片的宽和高，所以要按图片的宽高比例算出在excel中需要多少个单元格
+ * @param request 请求
+ * @param response 返回
+ */
+ @Override
+ public void exportExcelForImg(String fileContent, String fileName, String fileSize, HttpServletRequest request,HttpServletResponse response)
+ {
+ OutputStream out = null;
+ try {
+ //创建Excel工作簿，xls格式用HSSFWorkbook，xlsx格式用SXSSFWorkbook
+ HSSFWorkbook wb = new HSSFWorkbook();
+ //创建sheet页
+ HSSFSheet sheet = wb.createSheet(fileName);
+ //创建绘图(画布)，注明：一个sheet只能创建一个画布，但一个画布中可以添加多张图片
+ HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
+ // 图片宽度
+ Double width;
+ // 图片高度
+ Double height;
+ // 指定起始的单元格行索引,默认1
+ int col1 = 1;
+ // 指定起始的单元格列索引，row1=上一个row2+1，+1是为了两个图片之间隔开一行
+ int row1;
+ // 指定结束的单元格行索引，相当于宽，默认18
+ int col2 = 18;
+ // 指定结束的单元格列索引，相当于高,row2=row1+col2*(height/width)*3.5，乘以3.5是因为单元格的宽是高的3.5倍
+ int row2 = 0;
+ if(fileContent.length() > 0){
+ width = Double.valueOf(fileSize.split(":")[0]);
+ height = Double.valueOf(fileSize.split(":")[1]);
+ // 图片编码只要‘base64,’后面的部分
+ fileContent = URLDecoder.decode(fileContent,"UTF-8").substring(22);
+ row1 = row2 + 1;
+ row2 = row1 + (int) Math.ceil(col2 * (height / width) * 3.5);
+ createPictureInExcel(fileContent, patriarch, wb, (short) col1, row1, (short) col2, row2);
+ }
+ out = response.getOutputStream();
+ response.setContentType("application/vnd.ms-excel");
+ response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1" ));
+ wb.write(out);
+ out.flush();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }finally{
+ try {
+ if(out != null){
+ out.close();
+ }
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ @Override
+ public void exportExcelForImgAndTable(String fileContent,
+ String htmlTable, String fileName, String fileSize,
+ String jsFileName, HttpServletRequest request,
+ HttpServletResponse response) {
+ OutputStream out = null;
+ try {
+ //创建Excel工作簿，xls格式用HSSFWorkbook，xlsx格式用SXSSFWorkbook
+ HSSFWorkbook wb = new HSSFWorkbook();
+ //创建sheet页
+ HSSFSheet sheet = wb.createSheet(fileName);
+ //创建绘图(画布)，注明：一个sheet只能创建一个画布，但一个画布中可以添加多张图片
+ HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
+ // 图片宽度
+ Double width;
+ // 图片高度
+ Double height;
+ // 指定起始的单元格行索引,默认1
+ int col1 = 1;
+ // 指定起始的单元格列索引，row1=上一个row2+1，+1是为了两个图片之间隔开一行
+ int row1;
+ // 指定结束的单元格行索引，相当于宽，默认18
+ int col2 = 18;
+ // 指定结束的单元格列索引，相当于高,row2=row1+col2*(height/width)*3.5，乘以3.5是因为单元格的宽是高的3.5倍
+ int row2 = 0;
+ if(fileContent.length() > 0){
+ width = Double.valueOf(fileSize.split(":")[0]);
+ height = Double.valueOf(fileSize.split(":")[1]);
+ // 图片编码只要‘base64,’后面的部分
+ fileContent = URLDecoder.decode(fileContent,"UTF-8").substring(22);
+ row1 = row2 + 1;
+ row2 = row1 + (int) Math.ceil(col2 * (height / width) * 3.5);
+ createPictureInExcel(fileContent, patriarch, wb, (short) col1, row1, (short) col2, row2);
+ }
+ //解压缩
+ ScriptEngineManager manager = new ScriptEngineManager();
+ ScriptEngine engine = manager.getEngineByName("javascript");
+ FileReader reader = new FileReader(PathUtils.getWebAppRootPath() + jsFileName); // 执行指定脚本
+ engine.eval(reader);
+ htmlTable = (String)(engine.eval("LZString.decompressFromEncodedURIComponent('"+ htmlTable +"');"));
+ //解BASE64
+ BASE64Decoder decoder = new BASE64Decoder();
+ htmlTable = new String(decoder.decodeBuffer(htmlTable));
+ htmlTable = java.net.URLDecoder.decode(htmlTable,"UTF-8");
+ //htmlTable = "| 日期 | 平面器械类 | 齿类器械类 | 剪刀器械类 | 结构复杂类 | 腔镜器械类 | 管腔器械类 | 牙科手机类 | 牙科小器械 | 电动工具类 | 外来器械类 | 其他器械类 | 未设置 | 玻璃 | 敷料 | 棉布 | 木质 | 平面类器械 | 塑料 | 橡胶 | 合计 |
| 清洗数量 | 不合格数 | 不合格率 |
| 2020-04-01 | 1948 | 43 | 0.0221% | 2067 | 23 | 0.0111% | 215 | 2 | 0.0093% | 506 | 0 | 0.0000% | 58 | 0 | 0.0000% | 172 | 0 | 0.0000% | 25 | 0 | 0.0000% | 221 | 0 | 0.0000% | 16 | 0 | 0.0000% | 376 | 0 | 0.0000% | 12 | 0 | 0.0000% | 20 | 0 | 0.0000% | 2 | 0 | 0.0000% | 1406 | 0 | 0.0000% | 2 | 0 | 0.0000% | 3 | 0 | 0.0000% | 39 | 0 | 0.0000% | 266 | 0 | 0.0000% | 1490 | 0 | 0.0000% | 8844 | 68 | 0.0425% |
| 合计 | 1948 | 43 | 0.0221% | 2067 | 23 | 0.0111% | 215 | 2 | 0.0093% | 506 | 0 | 0.0000% | 58 | 0 | 0.0000% | 172 | 0 | 0.0000% | 25 | 0 | 0.0000% | 221 | 0 | 0.0000% | 16 | 0 | 0.0000% | 376 | 0 | 0.0000% | 12 | 0 | 0.0000% | 20 | 0 | 0.0000% | 2 | 0 | 0.0000% | 1406 | 0 | 0.0000% | 2 | 0 | 0.0000% | 3 | 0 | 0.0000% | 39 | 0 | 0.0000% | 266 | 0 | 0.0000% | 1490 | 0 | 0.0000% | 8844 | 68 | 0.0425% |
";
+ //System.out.println("htmlTable:"+htmlTable);
+ ConvertHtml2Excel.table2Excel(row2 + 5, wb, sheet, htmlTable);
+ out = response.getOutputStream();
+ response.setContentType("application/vnd.ms-excel");
+ response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1" ));
+ wb.write(out);
+ out.flush();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }finally{
+ try {
+ if(out != null){
+ out.close();
+ }
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ /**
+ * 将图片保存到excel中
+ * @param dataChart 图片的BASE64格式编码
+ * @param patriarch Excel-sheet 画布
+ * @param wb Excel工作簿
+ * @param col1 指定起始的单元格行索引
+ * @param row1 指定起始的单元格列索引
+ * @param col2 指定结束的单元格行索引
+ * @param row2 指定结束的单元格列索引
+ * @throws Exception
+ */
+ @SuppressWarnings("restriction")
+ public void createPictureInExcel(String dataChart, HSSFPatriarch patriarch, HSSFWorkbook wb,
+ short col1, int row1, short col2, int row2) throws Exception{
+ //用于将BASE64编码格式转为byte数组
+ BASE64Decoder base64Decoder = new BASE64Decoder();
+ ByteArrayOutputStream dataChartoutStream = new ByteArrayOutputStream();
+ //将dataChartStringin作为输入流，读取图片存入image中
+ ByteArrayInputStream dataChartin = new ByteArrayInputStream( base64Decoder.decodeBuffer(dataChart));
+ BufferedImage dataChartbufferImg = ImageIO.read(dataChartin);
+ //利用HSSFPatriarch将图片写入EXCEL
+ ImageIO.write(dataChartbufferImg, "png", dataChartoutStream);
+ /*
+ * 指定绘图区域位置及大小
+ * HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
+ * 参数说明：
+ * dx1 dy1 起始单元格中的x,y坐标.
+ * dx2 dy2 结束单元格中的x,y坐标.
+ * col1,row1 指定起始的单元格，下标从0开始.
+ * col2,row2 指定结束的单元格 ，下标从0开始.
+ */
+ HSSFClientAnchor anchorCostStr = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
+ //画图
+ patriarch.createPicture(anchorCostStr, wb.addPicture(dataChartoutStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
+ }
}
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManager.java
===================================================================
diff -u -r30932 -r31206
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManager.java (.../ReportFormsManager.java) (revision 30932)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ReportFormsManager.java (.../ReportFormsManager.java) (revision 31206)
@@ -6,6 +6,7 @@
import java.io.OutputStream;
import javax.servlet.http.HttpServletRequest;
+import javax.servlet.http.HttpServletResponse;
/**
* @author WangYi
@@ -24,4 +25,24 @@
* @param jsFileName js文件名
*/
public void exportHtmlTable(HttpServletRequest request,OutputStream ops, String jsFileName);
+ /**
+ * 导出图片到excel
+ * @param fileContent 图片内容
+ * @param fileName 导出excel的fileName
+ * @param fileSize 图片大小
+ * @param request
+ * @param response
+ */
+ public void exportExcelForImg(String fileContent, String fileName, String fileSize, HttpServletRequest request,HttpServletResponse response);
+ /**
+ * 导出图和表到excel
+ * @param fileContent 图片内容
+ * @param htmlTable
+ * @param fileName 导出excel的fileName
+ * @param fileSize 图片大小
+ * @param jsFileName 解压缩的js路径
+ * @param request
+ * @param response
+ */
+ public void exportExcelForImgAndTable(String fileContent, String htmlTable, String fileName, String fileSize, String jsFileName,HttpServletRequest request,HttpServletResponse response);
}
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java
===================================================================
diff -u -r31145 -r31206
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 31145)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManager.java (.../JasperReportManager.java) (revision 31206)
@@ -586,8 +586,14 @@
public List getMaterialStockReport(String startDate,String endDate,Long materialId,Long warehouseId,String summaryType);
public List findMaterialTypeWorkloadReportList(String month,String querySupplyRoom);
-
/**
+ * 获取清洗区材料类型工作量统计报表年份数据
+ * @param queryYear 查询年份
+ * @param querySupplyRoom 供应室编码
+ * @return
+ */
+ public List findMaterialTypeWorkloadReportListByYear(String queryYear,String querySupplyRoom);
+ /**
* 获取"外来器械包的植入物和植入型器械灭菌监测及发放报表"的javabean
* @param startTime 开始时间
* @param endTime 结束时间
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java
===================================================================
diff -u -r31145 -r31206
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 31145)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/action/JasperreportsAction.java (.../JasperreportsAction.java) (revision 31206)
@@ -88,6 +88,7 @@
import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialDamageItemBean;
import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialDamageTotal_child;
import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialErrorDamageSummaryOfDepartmentVO;
+import com.forgon.disinfectsystem.jasperreports.javabeansource.MaterialTypeWorkloadReport;
import com.forgon.disinfectsystem.jasperreports.javabeansource.MonthReportBean;
import com.forgon.disinfectsystem.jasperreports.javabeansource.MonthReportBeanWith2Column;
import com.forgon.disinfectsystem.jasperreports.javabeansource.PackingFractionDefectiveBean;
@@ -1329,8 +1330,11 @@
}
}else if(reportName.equals("materialTypeWorkloadReport")){ // 清洗区材料类型工作量统计报表
String month = StrutsParamUtils.getPraramValue("month", "");
+ String year = StrutsParamUtils.getPraramValue("year", "");
String querySupplyRoom = StrutsParamUtils.getPraramValue("querySupplyRoom", "");
- if(StringUtils.isNotBlank(month)){
+ if(StringUtils.isNotBlank(year)){
+ return jasperReportManager.findMaterialTypeWorkloadReportListByYear(year, querySupplyRoom);
+ }else if(StringUtils.isNotBlank(month)){
return jasperReportManager.findMaterialTypeWorkloadReportList(month,querySupplyRoom);
}
}else if(reportName.equals("godownEntryDetailStatistic")){
@@ -4568,4 +4572,76 @@
String htmlTable = request.getParameter("htmlTable");
request.getSession().setAttribute("htmlTable", htmlTable);
}
+ /**
+ * 获取Echarts图形数据
+ */
+ public void getReportDataOfEcharts(){
+ JSONObject resultObject = new JSONObject();
+ try {
+ String reportName = StrutsParamUtils.getPraramValue("reportName", "");
+ resultObject = getReportDataByReportName(reportName);
+ resultObject.put("success", true);
+ } catch (Exception e) {
+ e.printStackTrace();
+ resultObject.put("success", false);
+ }finally{
+ StrutsResponseUtils.output(resultObject);
+ }
+ }
+ /**
+ * 根据报表名称获取报表数据
+ * @param reportName 报表名
+ * @return
+ */
+ private JSONObject getReportDataByReportName(String reportName){
+ if("materialTypeWorkloadReport".equals(reportName)){
+ JSONObject obj = new JSONObject();
+ try {
+ String month = StrutsParamUtils.getPraramValue("month", "");
+ String year = StrutsParamUtils.getPraramValue("year", "");
+ String querySupplyRoom = StrutsParamUtils.getPraramValue("querySupplyRoom", "");
+ List list = null;
+ if(StringUtils.isNotBlank(year)){
+ list = jasperReportManager.findMaterialTypeWorkloadReportListByYear(year, querySupplyRoom);
+ }else if(StringUtils.isNotBlank(month)){
+ list = jasperReportManager.findMaterialTypeWorkloadReportList(month,querySupplyRoom);
+ }else{
+ obj.put("success", false);
+ return null;
+ }
+ Map sumMap = new HashMap();
+ MaterialTypeWorkloadReport totalSumVo = new MaterialTypeWorkloadReport();
+ for (MaterialTypeWorkloadReport item : list) {
+ String dayOfMonth = item.getDayOfMonth();
+ MaterialTypeWorkloadReport sumVo = null;
+ if(sumMap.containsKey(dayOfMonth)){
+ sumVo = sumMap.get(dayOfMonth);
+ sumVo.setAmount(MathTools.add(item.getAmount(), sumVo.getAmount()).intValue());
+ sumVo.setWashUnqualifiedAmount(MathTools.add(item.getWashUnqualifiedAmount(), sumVo.getWashUnqualifiedAmount()).intValue());
+ sumVo.setWashUnqualifiedPercentage(MathTools.add(item.getWashUnqualifiedPercentage(), sumVo.getWashUnqualifiedPercentage()).doubleValue());
+ }else{
+ sumVo = new MaterialTypeWorkloadReport();
+ sumVo.setAmount(item.getAmount());
+ sumVo.setWashUnqualifiedAmount(item.getWashUnqualifiedAmount());
+ sumVo.setWashUnqualifiedPercentage(item.getWashUnqualifiedPercentage());
+ sumMap.put(dayOfMonth, sumVo);
+ }
+ totalSumVo.setAmount(MathTools.add(item.getAmount(), totalSumVo.getAmount()).intValue());
+ totalSumVo.setWashUnqualifiedAmount(MathTools.add(item.getWashUnqualifiedAmount(), totalSumVo.getWashUnqualifiedAmount()).intValue());
+ totalSumVo.setWashUnqualifiedPercentage(MathTools.add(item.getWashUnqualifiedPercentage(), totalSumVo.getWashUnqualifiedPercentage()).doubleValue());
+ }
+ JSONObject data = new JSONObject();
+ sumMap.put("合计", totalSumVo);
+ data.put("dataList", list);
+ data.put("sumData", JSONObject.fromObject(sumMap).toString());
+ obj.put("data", data);
+ obj.put("success", true);
+ } catch (Exception e) {
+ obj.put("success", false);
+ e.printStackTrace();
+ }
+ return obj;
+ }
+ return new JSONObject();
+ }
}
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ConvertHtml2Excel.java
===================================================================
diff -u
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ConvertHtml2Excel.java (revision 0)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/ConvertHtml2Excel.java (revision 31206)
@@ -0,0 +1,248 @@
+package com.forgon.disinfectsystem.reportforms.service;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.commons.lang3.StringUtils;
+import org.apache.commons.lang3.math.NumberUtils;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFFont;
+import org.apache.poi.hssf.usermodel.HSSFPalette;
+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.hssf.util.HSSFColor;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.dom4j.Document;
+import org.dom4j.DocumentException;
+import org.dom4j.DocumentHelper;
+import org.dom4j.Element;
+/**
+ * htmltable to Excel
+ */
+public class ConvertHtml2Excel {
+
+ /**
+ * html表格转excel
+ * @param rowIndex row开始下标
+ * @param HSSFWorkbook 一个webbook，对应一个Excel文件 用于在已有的文件中添加表格
+ * @param tableHtml
+ * @return
+ */
+ public static HSSFWorkbook table2Excel(int rowIndex, HSSFWorkbook wb, HSSFSheet sheet,String tableHtml) {
+ if(wb == null){
+ wb = new HSSFWorkbook();
+ }
+ if(sheet == null){
+ sheet = wb.createSheet("sheet");
+ }
+ HSSFCellStyle style = wb.createCellStyle();
+ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
+ List crossRowEleMetaLs = new ArrayList<>();
+ try {
+ Document data = DocumentHelper.parseText(tableHtml);
+ // 生成表头
+ Element thead = data.getRootElement().element("thead");
+ HSSFCellStyle titleStyle = getTitleStyle(wb);
+ if (thead != null) {
+ List trLs = thead.elements("tr");
+ for (Element trEle : trLs) {
+ HSSFRow row = sheet.createRow(rowIndex);
+ List thLs = trEle.elements("td");
+ makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
+ row.setHeightInPoints(17);
+ rowIndex++;
+ }
+ }
+ // 生成表体
+ Element tbody = data.getRootElement().element("tbody");
+ if (tbody != null) {
+ HSSFCellStyle contentStyle = getContentStyle(wb);
+ List trLs = tbody.elements("tr");
+ for (Element trEle : trLs) {
+ HSSFRow row = sheet.createRow(rowIndex);
+ //List thLs = trEle.elements("th");
+ // int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
+ List tdLs = trEle.elements("td");
+ makeRowCell(tdLs, rowIndex, row, 0, contentStyle, crossRowEleMetaLs);
+ row.setHeightInPoints(18);
+ rowIndex++;
+ }
+ }
+ //生成页脚
+ HSSFCellStyle sumStyle = getSumStyle(wb);
+ Element tfoot = data.getRootElement().element("tfoot");
+ if (tbody != null) {
+ List trLs = tfoot.elements("tr");
+ for (Element trEle : trLs) {
+ HSSFRow row = sheet.createRow(rowIndex);
+ //List thLs = trEle.elements("th");
+ // int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
+ List tdLs = trEle.elements("td");
+ makeRowCell(tdLs, rowIndex, row, 0, sumStyle, crossRowEleMetaLs);
+ row.setHeightInPoints(19);
+ rowIndex++;
+ }
+ }
+ // 合并表头
+ for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
+ sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
+ }
+ } catch (DocumentException e) {
+ e.printStackTrace();
+ }
+ //自动调整列宽
+ for (int i = 0; i < 15; i++) {
+ sheet.autoSizeColumn((short)i);
+ }
+ return wb;
+ }
+
+ /**
+ * 生产行内容
+ * @param tdLs th或者td集合
+ * @param rowIndex 行号
+ * @param row POI行对象
+ * @param startCellIndex 开始下标
+ * @param cellStyle 样式
+ * @param crossRowEleMetaLs 跨行元数据集合
+ * @return 最后一列的cell index
+ */
+ private static int makeRowCell(List tdLs, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle,
+ List crossRowEleMetaLs) {
+ int i = startCellIndex;
+ for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
+ int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
+ while (captureCellSize > 0) {
+ for (int j = 0; j < captureCellSize; j++) {// 当前行跨列处理（补单元格）
+ HSSFCell c1 = row.createCell(i);
+ c1.setCellStyle(cellStyle);
+ i++;
+ }
+ captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
+ }
+ Element thEle = tdLs.get(eleIndex);
+ String val = thEle.getTextTrim();
+
+ if (StringUtils.isBlank(val)) {
+ Element e = thEle.element("a");
+ if (e != null) {
+ val = e.getTextTrim();
+ }
+ }
+ HSSFCell c = row.createCell(i);
+ if (NumberUtils.isNumber(val)) {
+ c.setCellValue(Double.parseDouble(val));
+ c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
+ } else {
+ c.setCellValue(val);
+ }
+ c.setCellStyle(cellStyle);
+ int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
+ int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
+ if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
+ crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
+ }
+ if (colSpan > 1) {// 当前行跨列处理（补单元格）
+ for (int j = 1; j < colSpan; j++) {
+ i++;
+ HSSFCell c2 = row.createCell(i);
+ c2.setCellStyle(cellStyle);
+ }
+ }
+ }
+ return i;
+ }
+
+ /**
+ * 获得因rowSpan占据的单元格
+ * @param rowIndex 行号
+ * @param colIndex 列号
+ * @param crossRowEleMetaLs 跨行列元数据
+ * @return 当前行在某列需要占据单元格
+ */
+ private static int getCaptureCellSize(int rowIndex, int colIndex, List crossRowEleMetaLs) {
+ int captureCellSize = 0;
+ for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
+ if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
+ if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
+ captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
+ }
+ }
+ }
+ return captureCellSize;
+ }
+
+ /**
+ * 获得标题样式
+ * @param workbook
+ * @return
+ */
+ private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
+ short fontSize = 12;
+ String fontName = "宋体";
+ HSSFCellStyle style = workbook.createCellStyle();
+ style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
+ style.setBorderBottom((short) 1);
+ style.setBorderTop((short) 1);
+ style.setBorderLeft((short) 1);
+ style.setBorderRight((short) 1);
+ style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
+ //设置自定义颜色
+ style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
+ HSSFFont font = workbook.createFont();
+ font.setFontName(fontName);
+ font.setFontHeightInPoints(fontSize);
+ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
+ style.setFont(font);
+ return style;
+ }
+ /**
+ * 获得合计样式
+ *
+ * @param workbook
+ * @return
+ */
+ private static HSSFCellStyle getSumStyle(HSSFWorkbook workbook) {
+ short fontSize = 12;
+ String fontName = "宋体";
+ HSSFCellStyle style = workbook.createCellStyle();
+ style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
+ style.setBorderBottom((short) 1);
+ style.setBorderTop((short) 1);
+ style.setBorderLeft((short) 1);
+ style.setBorderRight((short) 1);
+ style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
+ HSSFPalette customPalette = workbook.getCustomPalette();
+ customPalette.setColorAtIndex(HSSFColor.TAN.index, (byte) 0, (byte) 95, (byte) 179);
+ style.setFillForegroundColor(HSSFColor.TAN.index);
+ HSSFFont font = workbook.createFont();
+ font.setFontName(fontName);
+ font.setFontHeightInPoints(fontSize);
+ font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
+ style.setFont(font);
+ return style;
+ }
+ /**
+ * 获得内容样式
+ * @param wb
+ * @return
+ */
+ private static HSSFCellStyle getContentStyle(HSSFWorkbook wb) {
+ short fontSize = 12;
+ String fontName = "宋体";
+ HSSFCellStyle style = wb.createCellStyle();
+ style.setBorderBottom((short) 1);
+ style.setBorderTop((short) 1);
+ style.setBorderLeft((short) 1);
+ style.setBorderRight((short) 1);
+ style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
+ HSSFFont font = wb.createFont();
+ font.setFontName(fontName);
+ font.setFontHeightInPoints(fontSize);
+ style.setFont(font);
+ return style;
+ }
+}
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java
===================================================================
diff -u -r31190 -r31206
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 31190)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 31206)
@@ -14039,27 +14039,79 @@
calendar.add(Calendar.MONTH, 1);
Date endDate = calendar.getTime();
List list = new ArrayList();
+ Map materialTypeSecquenceMap = queryMaterialTypeSecquenceMap();
+ String undefinedType = "未设置";
while(endDate.after(statDate)){
String startDateStr = sdf.format(statDate);
calendar.setTime(statDate);
calendar.add(Calendar.DAY_OF_MONTH, 1);
statDate = calendar.getTime();
String endDateStr = sdf.format(statDate);
- materialTypeWorkloadReportList(startDateStr,endDateStr,querySupplyRoom,list);
+ materialTypeWorkloadReportList(undefinedType, materialTypeSecquenceMap, startDateStr, startDateStr,endDateStr,querySupplyRoom,list);
}
return list;
}
-
/**
+ * 获取设置的材料类型和对应的序号。类型有设置序号的 第一优先级；类型为空第二优先级， 也就是未设置列；有类型，但是没序号的第三优先级。
+ * @return
+ */
+ private Map queryMaterialTypeSecquenceMap(){
+ List hos = httpOptionManager.getHttpOptionListById(HttpOption.SYSTEMSETTING_MATERIALTYPE);
+ Map materialTypeSecquenceMap = new HashMap();
+ // 最大的顺序号，用来设置‘未设置’类型的列号
+ Integer maxSecquence = 1;
+ if(CollectionUtils.isNotEmpty(hos)){
+ for(HttpOption ho : hos){
+ String typeName = ho.getOptionText();
+ Integer sec = ho.getSequence();
+ if(sec > maxSecquence){
+ maxSecquence = sec;
+ }
+ if(StringUtils.isNotBlank(typeName)){
+ materialTypeSecquenceMap.put(typeName, sec);
+ }
+ }
+ }
+ materialTypeSecquenceMap.put("未设置", maxSecquence + 1);
+ return materialTypeSecquenceMap;
+ }
+ @Override
+ public List findMaterialTypeWorkloadReportListByYear(String queryYear,String querySupplyRoom){
+ String startYear = queryYear + "-01-01 00:00:00";
+ String nextYear = DataIndex.getNextYear(queryYear) + " 00:00:00";
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ SimpleDateFormat showSdf = new SimpleDateFormat("yyyy-MM");
+ Date statDate = null;
+ try {
+ statDate = sdf.parse(startYear);
+ } catch (ParseException e) {
+ e.printStackTrace();
+ }
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(statDate);
+ calendar.add(Calendar.YEAR, 1);
+ Date endDate = calendar.getTime();
+ List list = new ArrayList();
+ Map materialTypeSecquenceMap = queryMaterialTypeSecquenceMap();
+ String undefinedType = "未设置";
+ while(endDate.after(statDate)){
+ String startDateStr = sdf.format(statDate);
+ String showStartDateStr = showSdf.format(statDate);
+ calendar.setTime(statDate);
+ calendar.add(Calendar.MONTH, 1);
+ statDate = calendar.getTime();
+ String endDateStr = sdf.format(statDate);
+ materialTypeWorkloadReportList(undefinedType, materialTypeSecquenceMap, showStartDateStr,startDateStr,endDateStr,querySupplyRoom,list);
+ }
+ return list;
+ }
+ /**
* 获取每日清洗数据
* @param statDate
* @param endDate
* @return
*/
- private void materialTypeWorkloadReportList(String statDate,String endDate,String querySupplyRoom,List list){
-
- String UNDEFINED_TYPE= "未设置";
-
+ private void materialTypeWorkloadReportList(String UNDEFINED_TYPE, Map materialTypeSecquenceMap, String showStatDate, String statDate,String endDate,String querySupplyRoom,List list){
ReportQueryParams params = new ReportQueryParams();
params.betweenSql = String.format(" between %s and %s ",
dateQueryAdapter.dateConverAdapter2(statDate, "yyyy-mm-dd"),
@@ -14160,25 +14212,6 @@
} finally {
DatabaseUtil.closeResultSetAndStatement(rs2);
}
-
- List hos = httpOptionManager.getHttpOptionListById(HttpOption.SYSTEMSETTING_MATERIALTYPE);
- Map materialTypeSecquenceMap = new HashMap();
- // 最大的顺序号，用来设置‘未设置’类型的列号
- Integer maxSecquence = 1;
- if(CollectionUtils.isNotEmpty(hos)){
- for(HttpOption ho : hos){
- String typeName = ho.getOptionText();
- Integer sec = ho.getSequence();
- if(sec > maxSecquence){
- maxSecquence = sec;
- }
- if(StringUtils.isNotBlank(typeName)){
- materialTypeSecquenceMap.put(typeName, sec);
- }
- }
- }
- materialTypeSecquenceMap.put(UNDEFINED_TYPE, maxSecquence);
-
Integer totalAmount = 0;
Integer totalUnqualifiedAmount = 0;
ResultSet rs = objectDao.executeSql(sql);
@@ -14190,14 +14223,16 @@
MaterialTypeWorkloadReport data = new MaterialTypeWorkloadReport();
data.setAmount(amount);
data.setColumnNum(1);
- data.setDayOfMonth(statDate);
+ data.setDayOfMonth(showStatDate);
if(StringUtils.isBlank(materialType)){
materialType = UNDEFINED_TYPE;
- data.setColumnNum(materialTypeSecquenceMap.size() + 1);
+ data.setColumnNum(materialTypeSecquenceMap.get(UNDEFINED_TYPE));
}else{
- Integer sec = materialTypeSecquenceMap.get(materialType);
- data.setColumnNum(sec);
+ if(!materialTypeSecquenceMap.containsKey(materialType)){
+ materialTypeSecquenceMap.put(materialType, materialTypeSecquenceMap.size() + 1);
+ }
+ data.setColumnNum(materialTypeSecquenceMap.get(materialType));
}
Integer washUnqualifiedAmount = monitoringMap.get(materialType);
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/CrossRangeCellMeta.java
===================================================================
diff -u
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/CrossRangeCellMeta.java (revision 0)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/reportforms/service/CrossRangeCellMeta.java (revision 31206)
@@ -0,0 +1,44 @@
+package com.forgon.disinfectsystem.reportforms.service;
+/**
+ * htmltable to Excel
+ * 单元格跨行跨列合并
+ */
+public class CrossRangeCellMeta {
+ public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {
+ super();
+ this.firstRowIndex = firstRowIndex;
+ this.firstColIndex = firstColIndex;
+ this.rowSpan = rowSpan;
+ this.colSpan = colSpan;
+ }
+ private int firstRowIndex;
+ private int firstColIndex;
+ /**
+ * 跨越行数
+ */
+ private int rowSpan;
+ /**
+ * 跨越列数
+ */
+ private int colSpan;
+
+ int getFirstRow() {
+ return firstRowIndex;
+ }
+
+ int getLastRow() {
+ return firstRowIndex + rowSpan - 1;
+ }
+
+ int getFirstCol() {
+ return firstColIndex;
+ }
+
+ int getLastCol() {
+ return firstColIndex + colSpan - 1;
+ }
+
+ int getColSpan(){
+ return colSpan;
+ }
+}