侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

excel export

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 132 阅读 / 2,833 字
温馨提示:
本文最后更新于 2022-04-30,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
package com.kang.controller;

import com.kang.entity.Drugs;
import com.kang.service.DrugsService;
import org.apache.ibatis.annotations.Mapper;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Controller
public class ExcelController {
@Autowired
public DrugsService drugsService;

@ResponseBody
@RequestMapping("/excelDrug")
public void excelDrug(HttpServletResponse response) {
    String msg = "";
    String fileName = "";
    String sheetName = "";
    String[] title = {"药品编号", "药品名称", "药品类型", "药品信息", "商品进价", "商品售价", "库存"};

    List<Drugs> valueList = drugsService.findAll();
    //设置日期格式
    SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    // new Date()为获取当前系统时间,也可使用当前时间戳
    String date = df.format(new Date());
    fileName = "drugs-" + date + ".xls";
    sheetName = "1";
    HSSFWorkbook wb = new HSSFWorkbook();


    // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
    HSSFSheet sheet = wb.createSheet(sheetName);

    // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    HSSFRow row = sheet.createRow(0);

    // 第四步,创建单元格,并设置值表头 设置表头居中
    HSSFCellStyle style = wb.createCellStyle();
    // 创建一个居中格式

// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    //声明列对象
    HSSFCell cell = null;

    //创建标题
    for (int i = 0; i < title.length; i++) {
        cell = row.createCell((short) i);
        cell.setCellValue(title[i]);
        cell.setCellStyle(style);
    }

    //创建内容
    for (int i = 0; i < valueList.size(); i++) {
        row = sheet.createRow(i + 1);
        Drugs value = valueList.get(i);

        String[] userInfoArray = {String.valueOf(value.getDid()), value.getDname(), value.getDtype(), value.getDintroduction(),
                String.valueOf(value.getJprice()), String.valueOf(value.getMprice()), String.valueOf(value.getStock())};

        for (int j = 0; j < userInfoArray.length; j++) {
            //将内容按顺序赋给对应的列对象
            row.createCell((short) j).setCellValue(userInfoArray[j]);
        }
    }

    try (ServletOutputStream outStr = response.getOutputStream();
         BufferedOutputStream buff = new BufferedOutputStream(outStr)) {
        response.setCharacterEncoding("UTF-8");
        // 设置响应的内容类型
        response.setContentType(MediaType.TEXT_PLAIN_VALUE);
        // 设置文件的名称和格式
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        //写入数据
        wb.write(buff);

        buff.flush();
    } catch (Exception e) {
        e.printStackTrace();
    }


}

}




0

评论区