导出试剂

李宇

2021-06-29 e997eee6b747c343e432783111371aca0bd89306

导出试剂

 pom.xml

@@ -140,7 +140,7 @@
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
      <version>1.1.2</version>
    </dependency>

    <dependency>
@@ -376,20 +376,18 @@
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.0.1</version>
      <version>4.1.1</version>
    </dependency>

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
      <version>4.1.1</version>
    </dependency>

      <dependency>
          <groupId>org.apache.commons</groupId>
          <artifactId>commons-compress</artifactId>
          <version>1.9</version>
      </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-compress</artifactId>
      <version>1.20</version>
    </dependency>
  </dependencies>

  <profiles>

 src/main/java/com/nanometer/smartlab/controller/ReagentMngController.java

@@ -7,6 +7,7 @@
import com.nanometer.smartlab.util.Constants;
import com.nanometer.smartlab.util.FacesUtils;
import com.nanometer.smartlab.util.IDUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
@@ -839,6 +840,20 @@
        return dataModel;
    }

    public void export2Excel() {

        List<Map> list = sysReagentService.selectAll(name, cas, supplierId,getType(), productSn);
        try{
            boolean isexport = sysReagentService.export2Excel(list,getType());
            if (isexport){

            }
        }catch (Exception e){
            e.printStackTrace();
            FacesUtils.warn("导出失败");
        }
    }

    public SysReagent getSysReagent() {
        return sysReagent;
    }

 src/main/java/com/nanometer/smartlab/controller/SupplierOrderMngController.java

@@ -269,28 +269,21 @@
                        try {
                            if(allOpeOrder != null && allOpeOrder.size() > 0)
                            {
                                    int count = allOpeOrder.size();
                                    this.setRowCount(count);
                                    //list = queryByPage();
                                    list = supplierOpeOrderService.getSupplierOpeOrderList(orderCode,applyCode,status,orderTime,orderEndTime,first,pageSize);
                                    for(int i=0;i<list.size();i++){
                                        System.out.println(list.get(i));
                                        /*if(list.get(i).getSecondUserName()!=null&&!list.get(i).getSecondUserName().equals("")){
                                            String name=list.get(i).getApproveUserName();
                                            list.get(i).setApproveUserName(list.get(i).getSecondUserName());
                                            list.get(i).setSecondUserName(name);
                                        }*/
                                        if(list.get(i).getBeforeApproveUserId()!=null&&!list.get(i).getBeforeApproveUserId().equals("")){
                                            String name=list.get(i).getApproveUserName();
                                            list.get(i).setFirst(list.get(i).getFirName());
                                            list.get(i).setSecond(name);
                                        }else {
                                            list.get(i).setFirst(list.get(i).getApproveUserName());
                                            list.get(i).setSecond(list.get(i).getSecondUserName());
                                        }
                                int count = allOpeOrder.size();
                                this.setRowCount(count);

                                list = supplierOpeOrderService.getSupplierOpeOrderList(orderCode,applyCode,status,orderTime,orderEndTime,first,pageSize);
                                for (int i=0;i<list.size();i++) {
                                    if (list.get(i).getBeforeApproveUserId() != null && !list.get(i).getBeforeApproveUserId().equals("")) {
                                        String name = list.get(i).getApproveUserName();
                                        list.get(i).setFirst(list.get(i).getFirName());
                                        list.get(i).setSecond(name);
                                    } else {
                                        list.get(i).setFirst(list.get(i).getApproveUserName());
                                        list.get(i).setSecond(list.get(i).getSecondUserName());
                                    }
                                for (SupplierOrder supplierOrder:list
                                     ) {
                                }
                                for (SupplierOrder supplierOrder:list) {
                                    if(supplierOrder.getIds()!=null&&!supplierOrder.getIds().equals("")){
                                        List ids = Arrays.asList(supplierOrder.getIds().split(","));
                                        supplierOrder.setApplyIds(ids);
@@ -322,17 +315,36 @@

                    @Override
                    public SupplierOrder getRowData(String rowKey) {
//                        Iterator<SupplierOrder> iterator = this.iterator();
//                        if (iterator != null) {
//                                SupplierOrder ss = null;
//                            while (iterator.hasNext()) {
//                                ss = iterator.next();
//                                if (rowKey.equals(ss.getId())) {
//                                    return ss;
//                                }
//                            }
//                        }
                        return supplierOpeOrderService.selectById(rowKey);
                        SupplierOrder supplierOrder = supplierOpeOrderService.selectById(rowKey);
                        if (supplierOrder.getBeforeApproveUserId() != null && !supplierOrder.getBeforeApproveUserId().equals("")) {
                            String name = supplierOrder.getApproveUserName();
                            supplierOrder.setFirst(supplierOrder.getFirName());
                            supplierOrder.setSecond(name);
                        } else {
                            supplierOrder.setFirst(supplierOrder.getApproveUserName());
                            supplierOrder.setSecond(supplierOrder.getSecondUserName());
                        }

                        if(supplierOrder.getIds()!=null&&!supplierOrder.getIds().equals("")){
                            List ids = Arrays.asList(supplierOrder.getIds().split(","));
                            supplierOrder.setApplyIds(ids);
                            Double a=0.00;
                            BigDecimal total=BigDecimal.valueOf(a);
                            for (int j=0;j<ids.size();j++) {
                                OpeApply app=opeApplyService.getOpeApply(ids.get(j).toString());
                                if(app.getStockFlag()!=0){//判断申购试剂是否缺货
                                    if(app.getApplyPrice()!=null){//判断试剂是否有实际售价
                                        BigDecimal b=app.getApplyPrice().multiply(BigDecimal.valueOf(app.getNum()));
                                        total=b.add(total);
                                    }else{
                                        BigDecimal c=app.getReagent().getPrice().multiply(BigDecimal.valueOf(app.getNum()));
                                        total=c.add(total);
                                    }
                                }
                            }
                            supplierOrder.setTotalPrice(total.doubleValue());
                        }
                        return supplierOrder;
                    }
                };
            }

 src/main/java/com/nanometer/smartlab/dao/SysReagentDao.java

@@ -51,4 +51,6 @@
    SysReagent getRowData(@Param("id") String rowKey);

    SysReagent getSysReagentListNewRowData(@Param("id")String rowKey);

    List<Map> selectAll(Map params);
}

 src/main/java/com/nanometer/smartlab/dao/SysReagentDao.xml

@@ -549,4 +549,47 @@
    left join sys_supplier supplier on supplier.id = sr.supplier_id
    where sr.id = #{id}
  </select>
  <select id="selectAll" resultType="java.util.Map">
    select
    sr.name,
    sr.product_sn as productSn,
    sr.cas,
    sr.price,
    sr.per_box as perBox,
    sr.safetynum,

    CASE WHEN type = 0 THEN '耗材' WHEN type = 1 THEN '试剂' ELSE '' END AS kind,
    ss.meta_value as productHomeName,
    bm.meta_value as control_products_name,
    supplier.name as supplierName,
    bm1.meta_value as reagentType,
    bm2.meta_value as reagentCharacter,
    bm3.meta_value as reagentFormat,
    CONCAT(sr.main_metering,bm4.meta_value) as reagentUnit
    from sys_reagent as sr
    left join base_meta as ss on sr.product_home = ss.id
    left join base_meta bm    on bm.id = sr.control_products
    left join base_meta bm1    on bm1.id = sr.reagent_type
    left join base_meta bm2    on bm2.id = sr.reagent_character
    left join base_meta bm3    on bm3.id = sr.reagent_format
    left join base_meta bm4    on bm4.id = sr.reagent_unit
    left join sys_supplier supplier on supplier.id = sr.supplier_id
    where sr.valid_flag = 1
    <if test="cas != null and cas != ''">
      and sr.cas = #{cas}
    </if>
    <if test="supplierId != null and supplierId != ''">
      and sr.supplier_id = #{supplierId}
    </if>
    <if test="name != null and name != ''">
      and sr.name like concat("%", #{name} ,"%")
    </if>
    <if test="type != null">
      and sr.type = #{type}
    </if>
    <if test="productSn != null and productSn != ''">
      and sr.product_sn like concat("%", #{productSn} ,"%")
    </if>
    ORDER BY length(sr.name),sr.create_time desc
  </select>
</mapper>

 src/main/java/com/nanometer/smartlab/service/SysReagentService.java

@@ -52,4 +52,8 @@
    SysReagent getRowData(String rowKey);

    SysReagent getSysReagentListNewRowData(String rowKey);

    boolean export2Excel(List<Map> list, Integer type) throws Exception;

    List<Map> selectAll(String name, String cas, String supplierId, Integer type, String productSn);
}

 src/main/java/com/nanometer/smartlab/service/SysReagentServiceImpl.java

@@ -8,6 +8,7 @@
import com.nanometer.smartlab.exception.AlarmException;
import com.nanometer.smartlab.exception.BusinessException;
import com.nanometer.smartlab.exception.ExceptionEnumCode;
import com.nanometer.smartlab.util.ExcelUtils;
import com.nanometer.smartlab.util.IDUtils;
import com.nanometer.smartlab.util.MessageUtil;
import org.apache.commons.lang.StringUtils;
@@ -21,10 +22,7 @@

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;

/**
 * Created by johnny on 17/11/29.
@@ -192,6 +190,55 @@
        return sysReagentDao.getSysReagentListNewRowData(rowKey);
    }

    @Override
    public boolean export2Excel(List<Map> list, Integer type) throws Exception {
        Map<String,String> map = new LinkedHashMap<>();
        if (type == 0){
            map.put("kind", "种类");
            map.put("name", "名称");
            map.put("productSn", "产品编号");
            map.put("cas", "CAS");
            map.put("reagentType", "试剂类型");
            map.put("reagentCharacter", "危险性质");
            map.put("supplierName", "供应商");
            map.put("productHomeName", "厂家");
            map.put("reagentFormat", "规格");
            map.put("reagentUnit", "包装");
            map.put("price", "含税售价");
            map.put("perBox", "每箱数量");
            ExcelUtils.export2Excel(list,"耗材列表",map);
        }else {
            map.put("kind", "种类");
            map.put("name", "试剂名称");
            map.put("control_products_name", "管制品");
            map.put("productSn", "产品编号");
            map.put("cas", "CAS");
            map.put("reagentType", "试剂类型");
            map.put("reagentCharacter", "危险性质");
            map.put("supplierName", "供应商");
            map.put("productHomeName", "厂家");
            map.put("reagentFormat", "规格");
            map.put("reagentUnit", "包装");
            map.put("price", "含税售价");
//            map.put("originprice", "原始售价");
            map.put("perBox", "每箱数量");
            map.put("safetynum", "安全库存");
            ExcelUtils.export2Excel(list,"试剂列表",map);
        }
        return true;
    }

    @Override
    public List<Map> selectAll(String name, String cas, String supplierId, Integer type, String productSn) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("name", name);
        params.put("cas", cas);
        params.put("supplierId", supplierId);
        params.put("type", type);
        params.put("productSn", productSn);
        return sysReagentDao.selectAll(params);
    }


    @Transactional(propagation = Propagation.REQUIRED)
    public int getSysReagentTotalCount(String name, String cas, String supplierId,Integer type) {

 src/main/java/com/nanometer/smartlab/util/ExcelUtils.java

@@ -3,24 +3,28 @@
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.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    public static HSSFWorkbook exportExcelNew(Map<String, String> map, List<Map> dataList, String name){
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
    public static SXSSFWorkbook exportExcelNew(Map<String, String> map, List<Map> dataList, String name){
        SXSSFWorkbook sb = new SXSSFWorkbook(1000);
        Integer total = null;
        //sheet名字
        HSSFSheet sheet = hssfWorkbook.createSheet(name);
        HSSFRow titlerRow = sheet.createRow(0);
        SXSSFSheet sheet = sb.createSheet(name);
        SXSSFRow titlerRow = sheet.createRow(0);

        int k = 0;
        for(Map.Entry<String, String> entry:map.entrySet()){
@@ -30,7 +34,7 @@
        }
        //数据
        for (int i = 0; i < dataList.size(); i++) {
            HSSFRow dataRow = sheet.createRow(i + 1);
            SXSSFRow dataRow = sheet.createRow(i + 1);
            int j = 0;
            for(Map.Entry<String, String> entry:map.entrySet()){
                //表头
@@ -41,7 +45,7 @@



        return hssfWorkbook;
        return sb;
    }


@@ -50,7 +54,7 @@
            throw new Exception("没有数据可以导出");
        }

        ServletOutputStream out = null;
        OutputStream out = null;
        InputStream is = null;
        try {
            FacesContext ctx = FacesContext.getCurrentInstance();
@@ -62,18 +66,18 @@
            StringBuffer contentDisposition = new StringBuffer();
            contentDisposition.append("attachment;");
            contentDisposition.append("filename=\"");
            contentDisposition.append(name).append(".xls");
            contentDisposition.append(name).append(".xlsx");
            contentDisposition.append("\"");
            response.setHeader(
                    "Content-Disposition",
                    new String(contentDisposition.toString().getBytes(
                            System.getProperty("file.encoding")), "ISO8859-1"));
            out = response.getOutputStream();
            HSSFWorkbook hssfWorkbook = ExcelUtils.exportExcelNew(map, list, name);
            hssfWorkbook.write(out);
//            out.flush();
            SXSSFWorkbook sb = ExcelUtils.exportExcelNew(map, list, name);
            sb.write(out);
            out.flush();
            sb.dispose();// 释放workbook所占用的所有windows资源
            ctx.responseComplete();

        } catch (Exception e) {
            if (is != null) {
                try {

 src/main/webapp/reagent_mng.xhtml

@@ -39,7 +39,7 @@
            </p:panelGrid>
        </p:panel>
        <p:panel styleClass="center-body">
            <p:panelGrid columns="8" styleClass="btn">
            <p:panelGrid columns="9" styleClass="btn">
                <p:commandButton value="新建" styleClass="new-btn"
                                 process="@this"
                                 actionListener="#{reagentMngController.onNewBtnClick}"
@@ -66,7 +66,9 @@
                <p:commandButton value="耗材导入" styleClass="import-btn" onclick="importReagent2()"
                ></p:commandButton>
                <a href="resources/template/耗材导入模板.xlsx" style="display: inline-block;"><img src="resources/images/xlsx.png" width="30px;" alt=""/></a>
                <script>
                <p:commandButton value="导出" styleClass="new-btn"  ajax="false" id="exportButton" widgetVar="exportButton"
                                  actionListener="#{reagentMngController.export2Excel}" />
                <script type="text/javascript">
                    function importReagent(){
                        $("#reagentMngForm\\:importReagentBtn_input").click();
                    }
@@ -142,6 +144,9 @@
                <p:column headerText="安全库存" rendered="#{reagentMngController.type == 1}">
                    <h:outputText value="#{row.safetynum}" ></h:outputText>
                </p:column>
                <p:column headerText="管制品" rendered="#{reagentMngController.type == 1}">
                    <h:outputText value="#{row.controlProductsName}" ></h:outputText>
                </p:column>
<!--                        <p:column headerText="库存">-->
<!--                            <h:outputText value="#{row.reserve}"></h:outputText>-->
<!--                        </p:column>-->
@@ -162,6 +167,7 @@
                          update="@form" auto="true" sizeLimit="5000000"
                          mode="advanced"/>
        </div>

    </h:form>

    <p:dialog modal="true" header="#{reagentMngController.action==constants.ACTION_ADD?'新建':'修改'}" appendTo="@(body)"
@@ -528,5 +534,7 @@
            </p:panel>
        </h:form>
    </p:dialog>


</ui:composition>
</html>