excle导出

最新推荐文章于 2023-07-13 10:50:55 发布

原创最新推荐文章于 2023-07-13 10:50:55 发布 · 214 阅读

CC 4.0 BY-SA版权

该博客主要介绍了实现Excel模板下载的相关代码。编写了工具类，创建了Controller，在Controller里通过HSSFWorkbook创建Excel表单，设置表头，虽填入数据部分被注释，但可实现Excel模板的下载，最后将Excel写入输出流完成下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.导入jar包

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>4.0.1</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>4.0.1</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>4.0.1</version>
</dependency>

2.编写工具类

/**
 * 描述:ExcelUtil
 *
 * @author 何志鹏
 * @ClassName:ExcelUtil
 * @create 2019-05-27 10:31
 * Version 1.0
 */
public class ExcelUtil {

    /**
     * excle导入
     * @param input 
     * @param startLine
     * @return
     */
    public static List<String> importExcel(InputStream input, int startLine) {
        Workbook workbook = null;
        List<String> dataList = new ArrayList();


        try {
            workbook = WorkbookFactory.create(input);
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (null != workbook) {
            Sheet readSheet = workbook.getSheetAt(0);
            //获取第一行数据
            int cellNum = null == readSheet.getRow(0) ? 0 : readSheet.getRow(0).getLastCellNum();
            Row readRow = null;
            Cell readCell = null;
            StringBuffer buff = new StringBuffer();
            for (int i = startLine; i < readSheet.getLastRowNum() + 1; i++) {
                buff.delete(0, buff.length());
                readRow = readSheet.getRow(i);
                if (readRow != null) {
                    for (int j = 0; j < cellNum; j++) {
                        readCell = readRow.getCell(j);
                        if (j < (cellNum - 1)) {
                            if (readCell != null) {
                                readCell.setCellType(CellType.STRING);
                                buff.append(readCell.getStringCellValue() + ",");
                            } else {
                                buff.append(",");
                            }
                        } else {
                            if (readCell != null) {
                                readCell.setCellType(CellType.STRING);
                                buff.append(StringUtils.isBlank(readCell.getStringCellValue()) ? "" : readCell.getStringCellValue());
                            } else {
                                buff.append(",");
                            }
                        }
                    }
                    dataList.add(buff.toString());
                }
            }

        }
        return dataList;
    }

    /**
     * 导出模板
     * @param workbook 
     * @param sheet
     */
    public static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 100 * 256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("定额发票编号");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("定额发票额度");
        cell.setCellStyle(style);

    }

}

3.写Controller 本项目只需要导出模板所以填入数据注释掉了

@RestController
@RequestMapping("/excel")
public class ExcelController extends BaseController{

@Autowired
private IAnswerService answerService;

/***
* 下载Excel
* @throws IOException
*/
@GetMapping("download")
public void download() throws IOException{

HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Excel表单,参数为sheet的名字
HSSFSheet sheet = workbook.createSheet("定额票据");

//创建表头
setTitle(workbook, sheet);
/** List<Answer> answers = answerService.findAll();

//新增数据行，并且设置单元格数据
int rowNum = 1;
for (Answer answer:answers) {
HSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(answer.getId());
row.createCell(1).setCellValue(answer.getSelections());
row.createCell(2).setCellValue(answer.getCheckes());
row.createCell(3).setCellValue(answer.getContent());
rowNum++;
}*/
String fileName = "定额票据";
//清空response
response.reset();
//设置response的Header
response.addHeader("Content-Disposition", "attachment;filename="+ fileName);
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
}