根据条件导出表格:

前端

<el-form-item label="">
<el-button type="warning" icon="el-icon-lightning" @click="exportExcel">导出</el-button>
</el-form-item>
//导出数据
exportExcel() {
const fileName = '药品清单'
medicineListApi.exportExcel({
fileName,
page: this.listQuery.page,
limit: this.listQuery.limit,
drugno: this.listQuery.drugno,
drugname: this.listQuery.drugname,
}).then(res => {
fileDownload(res.data, fileName + '.xlsx')
}, err => { console.log(err) })
},

在medicineList.js中的代码

//导入excel
exportExcel(data) {
return request({
url: baseUrl + '/export',
method: 'post',
data,
responseType: 'arraybuffer',
})
},

后台代码:

@PostMapping("/export")
public void exportMedicineList(@RequestBody JSONObject jsonObject, HttpServletResponse response) {
//根据条件查询数据
JSONObject result = medicineListService.selectPage(jsonObject);
//获取查询结果中的数据记录
List<DrugData> list = (List<DrugData>) result.get("records");
String fileName = jsonObject.getString("fileName");
ExcelData data = new ExcelData();
//设置工作表名称
data.setName(fileName);
//设置表头
List<String> titles = new ArrayList();
titles.add("药品编码");
titles.add("药品名称");
titles.add("适应症");
data.setTitles(titles);
//设置数据内容
List<List<Object>> rows = new ArrayList();
for (int i = 0; i < list.size(); i++) {
List<Object> row = new ArrayList();
row.add(list.get(i).getDrugno());
row.add(list.get(i).getDrugname());
row.add(list.get(i).getIndiction());
rows.add(row);
}
data.setRows(rows);
try {
ExcelUtil.exportExcel(response, fileName, data);
} catch (Exception e) {
e.printStackTrace();
log.info("=====药品清单导出发生异常=====" + e.getMessage());
}
}

service接口

public interface MedicineListService extends IService<DrugData> {
    JSONObject selectPage(JSONObject jsonObject);
}

service实现类

@Override
public JSONObject selectPage(JSONObject jsonObject) {
Integer page = jsonObject.getInteger("page");
Integer limit = jsonObject.getInteger("limit");
String drugno = jsonObject.getString("drugno");
String drugname = jsonObject.getString("drugname");
Page<DrugData> drugDataPage = new Page<>(page, limit);
QueryWrapper<DrugData> wrapper = new QueryWrapper<>();
// 使用模糊查询
wrapper.like(StringUtils.isNotBlank(drugno),"drugno",drugno);
wrapper.like(StringUtils.isNotBlank(drugname),"drugname",drugname);
drugDataPage = medicineListMapper.selectPage(drugDataPage, wrapper);
JSONObject result = new JSONObject();
result.put("total",drugDataPage.getTotal());
result.put("records",drugDataPage.getRecords());
return result;
}

ExcelUtil工具类的方法exportExcel()


public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
exportExcel(data, response.getOutputStream());
}
private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
int rowIndex = 0;
try {
//设置工作表的名字
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
//创建工作表
XSSFSheet sheet = wb.createSheet(sheetName);
rowIndex = writeExcel(wb, sheet, data);
wb.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
//此处需要关闭 wb 变量
out.close();
}
return rowIndex;
}
private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
autoSizeColumns(sheet, data.getTitles().size() + 1);
return rowIndex;
}
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
int rowIndex = 0;
int colIndex = 0;
Font titleFont = wb.createFont();
//设置字体
titleFont.setFontName("宋体");
//设置字号
titleFont.setFontHeightInPoints((short) 12);
//设置颜色
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN);
Row titleRow = sheet.createRow(rowIndex);
titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex;
Font dataFont = wb.createFont();
dataFont.setFontName("宋体");
dataFont.setFontHeightInPoints((short) 12);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN);
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
dataRow.setHeightInPoints(25);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
private static void setBorder(XSSFCellStyle style, BorderStyle border) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
}

 导出表格中的一行:

前端代码:

<el-table-column label="操作" align="center" class-name="small-padding fixed-width">
<template slot-scope="scope">
<el-button type="primary" class="el-button--mini" @click="handleDetail(scope.row)">查看</el-button>
<el-button :loading="downloadLoading" type="primary" class="el-button--mini" @click="handleExport(scope.row)">导出</el-button>
</template>
</el-table-column>
handleExport(row) {
this.downloadLoading = true;
inAPI.templateExport({
id: row.id,
fileName: "采购订单",
bean: "com.jawasoft.pts.exceltemplate.InTemplate"
}).then(response => {
fileDownload(response.data, "采购订单.xls");
}).finally(() => {
this.downloadLoading = false;
});
}

in.js中的代码:

import request from '@/utils/request'

  templateExport(query) {
    return request({
      url: '/in/templateExport',
      method: 'post',
      params: query,
      responseType: 'arraybuffer'
    })
  }
};

后台代码:

controller:

@RestController
@RequestMapping("api/in")
@Api(value = "采购订单控制器", tags = {"采购订单控制器"})
public class InController {
    @Autowired
    private InService inService;

    @PostMapping(value = "templateExport")
    public void templateExport(Integer id, String fileName, String bean, HttpServletResponse response) {
        inService.templateExport(id, fileName, bean, response);
    }
}

service:


public void templateExport(Integer id, String fileName, String bean, HttpServletResponse response) {
try {
List<InTemplate> templates = new ArrayList<>();
Map param = new HashMap();
User user = SessionCache.get();
param.put("userId",user.getUserId());
param.put("id", id);
List<Map> inList = inMapper.getInList(param);
if (inList != null) {
Map in = inList.get(0);
Example example = new Example(InDetail.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("inId", in.get("id"));
List<InDetail> list = inDetailMapper.selectByExample(example);
if (list != null) {
for (InDetail inDetail : list) {
InTemplate template = new InTemplate();
template.setInNo(in.get("inNo").toString());
template.setInDate(DateUtil.dateFormat((Date) in.get("inDate")));
//template.setOrgName(in.get("orgName").toString());
template.setEnterpriseName(in.get("companyName") != null ? in.get("companyName").toString() : "");
template.setDeliveryEntity(in.get("deliveryEntity").toString());
template.setBusinessEntity(in.get("businessEntity").toString());
template.setMaterialCode(inDetail.getMaterialCode());
template.setMaterialName(inDetail.getMaterialName());
template.setInType1(inDetail.getInType1());
template.setUnit(inDetail.getUnit());
template.setPrice(inDetail.getPrice());
template.setInNum(inDetail.getInNum().toString());
template.setStatus(inDetail.getStatus());
templates.add(template);
}
}
}
EasyPOIUtils.exportExcel(templates, fileName, LocalDate.now().toString(), Class.forName(bean), fileName, true, response);
} catch (Exception e) {
e.printStackTrace();
log.error("导出失败------->" + e.getMessage());
}
}
 

Dao接口:

@org.apache.ibatis.annotations.Mapper
public interface InMapper extends Mapper<In> {
    List<Map> getInList(Map map);
}

Mapper.xml:

<mapper namespace="com.jawasoft.pts.dao.coordination.InMapper">
    <select id="getInList" resultType="java.util.Map">
        SELECT
        t1.in_id AS "id",
        t1.in_no AS "inNo",
        t1.enterprise_id AS "enterpriseId",
        t1.company_code AS "companyCode",
        t1.in_date AS "inDate",
        t1.company_address AS "companyAddress",
        t1.delivery_entity AS "deliveryEntity",
        t1.business_entity AS "businessEntity",
        t1.status AS "status",
        t1.in_man AS "inMan",
        t1.org_id AS "orgId",
        t2.enterprise_name AS "enterpriseName",
        t4.id AS "enterpriseId2",
        t4.enterprise_name AS "companyName"
        FROM
        b_in t1
        LEFT JOIN sys_enterprise t2 ON t1.enterprise_id = t2.id
        LEFT JOIN sys_enterprise_association t3 ON t1.company_code = t3.company_code and t2.id = t3.sub_enterprise_id
        LEFT JOIN sys_enterprise t4 ON t3.affiliated_enterprise_id = t4.id
        WHERE 1 = 1 and t1.org_id in (SELECT
                                        d.org_id AS "orgId"
                                        FROM
                                        SYS_DEPARTMENT_USER du
                                        INNER JOIN SYS_DEPARTMENT d ON du.department_id = d.id
                                        WHERE
                                        du.del_flag = 0
                                        AND d.del_flag = 0
                                        AND du.user_id = #{ userId } )
        <if test="id!=null and id!=''">
            AND t1.in_id = #{id}
        </if>
        <if test="enterpriseId!=null and enterpriseId!=''">
            AND t1.enterprise_id = #{enterpriseId}
        </if>
        <if test="inNo!=null and inNo!=''">
            AND t1.in_no LIKE '%'||#{inNo}||'%'
        </if>
        <if test="companyName!=null and companyName!=''">
            AND t4.enterprise_name LIKE '%'||#{companyName}||'%'
        </if>
        <if test="inDate!=null and inDate!=''">
            AND to_char(t1.in_date, 'yyyy-mm-dd') = #{inDate}
        </if>
        ORDER BY t1.in_date DESC
    </select>
</mapper>

InTemplate实现类:

@Data
@ExcelTarget("inTemplate")
public class InTemplate implements Serializable {
    /**
     * 采购订单号
     */
    @Excel(name = "采购订单号", width = 30)
    private String inNo;
    /**
     * 订单日期
     */
    @Excel(name = "订单日期", width = 30)
    private String inDate;
    /**
     * 组织
     */
    @Excel(name = "组织", width = 30)
    private String orgName;
    /**
     * 供应商名称
     */
    @Excel(name = "供应商名称", width = 30)
    private String enterpriseName;
    /**
     * 收货方
     */
    @Excel(name = "收货方", width = 30)
    private String deliveryEntity;
    /**
     * 收单方
     */
    @Excel(name = "收单方", width = 30)
    private String businessEntity;
    /**
     * 物料编号
     */
    @Excel(name = "物料编号", width = 30)
    private String materialCode;
    /**
     * 物料名称
     */
    @Excel(name = "物料名称", width = 30)
    private String materialName;
    /**
     * 类别
     */
    @Excel(name = "类别", width = 30)
    private String inType1;
    /**
     * 单位
     */
    @Excel(name = "单位", width = 30)
    private String unit;
    /**
     * 价格
     */
    @Excel(name = "价格", width = 30)
    private String price;
    /**
     * 采购数量
     */
    @Excel(name = "采购数量", width = 30)
    private String inNum;
    /**
     * 状态
     */
//    @Excel(name = "状态", width = 30)
    @Excel(name = "状态", width = 30, replace = {"正常_0","关闭_4"})
    private String status;
    /**
     * 供货总重量(KG)
     */
    @Excel(name = "供货总重量", width = 30)
    private String supplyWt;
    /**
     * 到货截止时间
     */
    @Excel(name = "到货截止时间(yyyy-MM-dd)", width = 30)
    private String planToDate;
    /**
     * 送货地址
     */
    @Excel(name = "送货地址", width = 30)
    private String receivedAddr;
    /**
     * 备注
     */
    @Excel(name = "备注", width = 30)
    private String remark;
    /**
     * 提示
     */
    @Excel(name = "多条记录可往后加", width = 30)
    private String tip;
}

@ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理

@Excel 作用到filed上面,是对Excel一列的一个描述,width为列宽,默认为10.

EasyPOIUtils工具类:

public class EasyPOIUtils {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        exportParams.setStyle(PtsExcelExportStyler.class);  // 设置Excel表中的字体的样式和背景的样式
        //exportParams.setMaxNum(1000000); //设置单sheet页最大导出数据量
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            String filePath = createExportDir2() + fileName + "_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() + ".xls";
            FileOutputStream out = new FileOutputStream(filePath);
            workbook.write(out);
            out.flush();
            out.close();
            File file = new File(filePath);

            InputStream fis;
            fis = new BufferedInputStream(new FileInputStream(filePath));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.setHeader("Content-type", "text/html;charset=UTF-8");
            response.setCharacterEncoding("utf-8");//设置编码集,文件名不会发生中文乱码

            response.setContentType("application/force-download");//
            response.setHeader("content-type", "application/octet-stream");
            response.addHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(), "utf-8"));// 设置文件名
            response.addHeader("Content-Length", "" + file.length());
            response.setHeader("Access-Control-Allow-Origin", "*");

            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            file.delete();
        } catch (IOException e) {
            throw new BaseException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new BaseException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new BaseException("excel文件不能为空");
        } catch (Exception e) {
            throw new BaseException(e.getMessage());
        }
        return list;
    }

    public static String createExportDir() {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String rootPath = EasyPOIUtils.class.getResource("/").getPath();
        String path1 = rootPath + "export_files/";
        File exportPath1 = new File(path1);
        if (!exportPath1.exists()) exportPath1.mkdir();
        String path2 = path1 + simpleDateFormat.format(new Date());
        File exportPath2 = new File(path2);
        if (!exportPath2.exists()) exportPath2.mkdir();
        return path2;
    }

    public static String createExportDir2() {
      //  SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String rootPath = EasyPOIUtils.class.getResource("/").getPath();
        String path1 = rootPath + "export_files/";
        File exportPath1 = new File(path1);
        if (!exportPath1.exists()) exportPath1.mkdir();
      //  String path2 = path1 + simpleDateFormat.format(new Date());
        File exportPath2 = new File(path1);
        if (!exportPath2.exists()) exportPath2.mkdir();
        return path1;
    }

}

样式设置相关的实体类PtsExcelExportStyler.java:

public class PtsExcelExportStyler extends AbstractExcelExportStyler implements IExcelExportStyler {
    public PtsExcelExportStyler(Workbook workbook) {
        super.createStyles(workbook);
    }

    public CellStyle getTitleStyle(short color) {   // 表头样式  setColor方法可以设置所有字体的颜色
        CellStyle titleStyle = this.workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setFontHeightInPoints((short)12);
        titleStyle.setFont(font);
        titleStyle.setAlignment((short)2);
        titleStyle.setVerticalAlignment((short)1);
        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());   // 表头的背景色为黄色
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return titleStyle;
    }

    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment((short)2);
        style.setVerticalAlignment((short)1);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }

        return style;
    }

    public CellStyle getHeaderStyle(short color) {   // 标题样式
        CellStyle headerStyle = this.workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setFontHeightInPoints((short)12);
        headerStyle.setFont(font);
        headerStyle.setAlignment((short)2);
        headerStyle.setVerticalAlignment((short)1);
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());   // 标题的背景色设置为黄色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return headerStyle;
    }

    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment((short)2);
        style.setVerticalAlignment((short)1);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }

        return style;
    }
}

导入EasyPOI的依赖:

<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>

 

posted on 2020-09-28 18:10  周文豪  阅读(543)  评论(0编辑  收藏  举报