java 导出excel模板_java 根据excel模板导出文件

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

package com.example.demo;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.UnsupportedEncodingException;

import java.net.URLEncoder;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.core.io.ClassPathResource;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

@RestController

@RequestMapping(value = "/index")

public class IndexController {

@RequestMapping(value = "/excel2007Export")

public ResponseEntity excel2007Export(HttpServletResponse response, HttpServletRequest request)

{

try

{

ClassPathResource cpr = new ClassPathResource("/student.xlsx");

InputStream is = cpr.getInputStream();

Workbook workbook = new XSSFWorkbook(is);

org.apache.poi.ss.usermodel.Sheet sheet0 = workbook.getSheetAt(0);

// 这里作为演示，造几个演示数据，模拟数据库里查数据

List list = new ArrayList();

list.add("张三");

list.add("张四");

list.add("王五");

for (int i = 0; i < list.size(); i++)

{

Row row = sheet0.createRow(i + 5);

row.createCell(0).setCellValue(i);

row.createCell(1).setCellValue(list.get(i));

}

String fileName = "eeeee.xlsx";

downLoadExcel(fileName, response, workbook);

}

catch (FileNotFoundException e)

{

e.printStackTrace();

}

catch (IOException e)

{

e.printStackTrace();

}

return new ResponseEntity(HttpStatus.OK);

}

public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)

{

try

{

response.setCharacterEncoding("UTF-8");

response.setHeader("content-Type", "application/vnd.ms-excel");

response.setHeader("Content-Disposition",

"attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");

workbook.write(response.getOutputStream());

}

catch (IOException e)

{

e.printStackTrace();

}

}

@RequestMapping(value = "/exportExcel", method = { RequestMethod.GET, RequestMethod.POST })

public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException {

String fileName = "导出excel.xls";

response.setContentType("application/excel");

response.setHeader("Content-disposition",

"attachment;filename=" + fileName + ";filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();

int rowNum = 0;

// 添加标题

String[] headers = { "标题", "版块", "作者", "创建时间", "内容" };

HSSFRow row = sheet.createRow(rowNum);

List list = new ArrayList();

list.add("111");

list.add("222");

list.add("333");

for (String text : list) {

HSSFRow row1 = sheet.createRow(rowNum);

row1.createCell(0).setCellValue(rowNum);

row1.createCell(1).setCellValue(text);

rowNum++;

}

try {

workbook.write(response.getOutputStream());

} catch (IOException e) {

e.printStackTrace();

}

}

}