SpringBoot导出excel

SpringBoot导出excel

引入maven依赖：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.0</version>
</dependency>

工具类：

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelOutUtil {
    /**
     * 导出Excel
     * @param sheetName
     *        sheet名称
     * @param title
     *        标题
     * @param values
     *        内容
     * @param wb
     *        HSSFWorkbook对象
     * @param head
     *        标题头
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,
                                               String[] title, String[][] values, HSSFWorkbook wb,String head) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第1行
        HSSFRow row = sheet.createRow(1);

        // 第四步，设置样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short)11);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);//自动换行

        // 创建总标题头
        HSSFRow rowHead = sheet.createRow(0);
        HSSFCell cellRowHead = rowHead.createCell(0);
        cellRowHead.setCellValue(head);
        cellRowHead.setCellStyle(cellStyle);
        rowHead.setHeightInPoints(36);
        CellRangeAddress cra = new CellRangeAddress(0,0,0,6);//合并单元格
        sheet.addMergedRegion(cra);//引用设计样式

        // 声明列对象
        HSSFRow row1=sheet.createRow(1);
        HSSFCell cell = row1.createCell(1);
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }

        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 2);
            row.setHeightInPoints(20);//设置行高
            int width=10;//设置宽度
            sheet.setColumnWidth(0,256*width+384);
            sheet.setColumnWidth(1,256*width+384);
            sheet.setColumnWidth(2,256*width+384);
            sheet.setColumnWidth(3,256*width+384);
            sheet.setColumnWidth(4,256*width+384);
            sheet.setColumnWidth(5,256*width+384);
            sheet.setColumnWidth(6,256*width+484);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
                row.setRowStyle(cellStyle);
            }
        }
        return wb;
    }

    // 根据参数返回一个二维数组
    public static String[][] getContent(int length) {
        return new String[length][];
    }

    /**
     * 用于时间命名
     * @return
     */
    public String getDataTime(){
        Date date = new Date();
        SimpleDateFormat dateFormat= new SimpleDateFormat("yyyyMMddhhmmss");
        String dataTime = dateFormat.format(date);
        return dataTime;
    }

    /**
     * 格式化列的类型
     * @param cell 列
     * @return 统一返回字符串
     */
	/*public static String formatCell(HSSFCell cell) {
		if (cell == null) {
			return "";
		} else {
			//return String.valueOf(cell.getRichStringCellValue()) 返回字符串
			if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {//布尔型
				return cell.getBooleanCellValue() ? "true" : "false";
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {//数值型
				cell.setCellType(cell.CELL_TYPE_STRING);//数值转为字符串类型
				return cell.getStringCellValue();
			}else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){//公式型
				return cell.getCellFormula();
			}else if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){//字符串
				return cell.getStringCellValue();
			}else {
				return "";
			}
		}
	}*/
}

cotroller类：

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.project.main.excel.out.entity.WorkersVO;
import com.project.main.excel.out.service.WorkersOutService;
import com.project.main.excel.out.utils.ExcelOutUtil;
import com.project.main.utils.R;

@RestController
@CrossOrigin
public class WorkersOutController {
	
	@Autowired
	private WorkersOutService workersService;
	
	@GetMapping("/excelDownload")
    public R excelDownload(HttpServletRequest request, HttpServletResponse response){
        //Map<String, Object> map = getQueryMap();
        List<WorkersVO> workersList = workersService.getWorkersToExcelOut();//要导出的数据集合
        //excel标题
        String[] title = {"工号","姓名","年龄","性别","居住地址","职位","入职日期"};
        // excel文件名
        ExcelOutUtil eu = new ExcelOutUtil();
        String dataName = eu.getDataTime();
        String fileName = dataName+"员工信息表.xls";
        String[][] content = ExcelOutUtil.getContent(workersList.size());
        String sheetName = "信息";
        String head = "员工信息表";
        for (int i = 0; i <workersList.size(); i++) {
            content[i] = new String[title.length];
            WorkersVO workersVO = workersList.get(i);
            content[i][0] = workersVO.getCardNum();
            content[i][1] = workersVO.getWorkerName();
            content[i][2] = workersVO.getAge();
            content[i][3] = workersVO.getSex();
            content[i][4] = workersVO.getAddress();
            content[i][5] = workersVO.getPosition();
            content[i][6] = workersVO.getWorkDate();
           
        }
        HSSFWorkbook hwb = ExcelOutUtil.getHSSFWorkbook(sheetName, title, content,
                null, head);
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            hwb.write(os);
            os.flush();
            os.close();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return R.ok("导出成功...");
    }

    private Map<String, Object> getQueryMap() {
		// TODO Auto-generated method stub
		return null;
	}

	// 发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO-8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            //log.info("导出excel功能,发送响应流方法异常!");
            ex.printStackTrace();
        }
    }
}

此导出excel适用于简易型导出，无过多的样式修饰
导出样例

更多开源开发软件，可在微信公众号搜索"小逸分享"前去提取哦！