V2AS问路
导出----用Excel导出数据库表
阅读原文时间：2023年07月11日阅读：5

根据条件导出表格：

@click="exportExcel">导出

//导出数据
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 list = (List) result.get("records");
String fileName = jsonObject.getString("fileName");
ExcelData data = new ExcelData();
//设置工作表名称
data.setName(fileName);
//设置表头
List titles = new ArrayList();
titles.add("药品编码");
titles.add("药品名称");
titles.add("适应症");
data.setTitles(titles);
//设置数据内容
List> rows = new ArrayList();
for (int i = 0; i < list.size(); i++) { List 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 {
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 drugDataPage = new Page<>(page, limit);
QueryWrapper 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 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> 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 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);
}

导出表格中的一行：

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 templates = new ArrayList<>();
Map param = new HashMap();
User user = SessionCache.get();
param.put("userId",user.getUserId());
param.put("id", id);
List 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 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 {
List getInList(Map map);
}

Mapper.xml：

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的依赖：

cn.afterturn easypoi-base 3.2.0
cn.afterturn easypoi-web 3.2.0
cn.afterturn easypoi-annotation 3.2.0

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章

[EasyExcel] 导出合并单元格
【反反爬】使用Jsoup爬取数据保存Excel
如何使用Java + React计算个人所得税？

4

基于ASP.NET ZERO，开发SaaS版供应链管理系统

5

想让你的工作轻松高效吗？揭秘Java + React导出Excel/PDF的绝妙技巧！

6

GrapeCity Documents V6.0 Update 2发布，新增支持SpreadJS的.sjs文件格式

7

将实体类对象数据存入和读取进csv文件（可追加）

8

聊聊Excel解析：如何处理百万行EXCEL文件

9

java接口返回图片链接或pdf链接如何设置在线预览还是下载

10

Hutool：一行代码搞定数据脱敏