基于ssm完成mysql数据库数据导出到外部EXCEL表格

用POI完成数据导出

jar包

poi-3.16.jar

poi-examples-3.16.jar

poi-ooxml-3.16.jar

poi-ooxml-schemas-3.16.jar

方法

controller

 //导出excel
  @RequestMapping("exportexcel")
  public void admin_exportMembers(HttpServletResponse response) throws Exception{
   ExportExcelUtils excelUtils = new ExportExcelUtils(employeeMapper);
   excelUtils.buildExcelDocument(response);
  }

ExportExcelUtils

package com.mybatis.poi;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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

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.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.mybatis.Dao.EmployeeMapper;
import com.mybatis.bean.Employee;

public class ExportExcelUtils {
// @Autowired
 private EmployeeMapper employeeMapper;
 
 /*public ExportExcelUtils(String title,String[] rowName,List<Object[]>  dataList,HttpServletResponse  response){ 
        this.title=title; 
        this.rowName=rowName; 
        this.dataList=dataList; 
        this.response = response; 
    } */
 
 public ExportExcelUtils(EmployeeMapper employeeMapper) {
  super();
  this.employeeMapper = employeeMapper;
 }
 
 
 public void buildExcelDocument(HttpServletResponse response) throws Exception {
  String title = "表格";
  String [] rowName = {"编号","姓名","性别","邮箱"};
  List<Employee> dataList = employeeMapper.getEmps();
   HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet =workbook.createSheet(title); // 创建表格 
        // 产生表格标题行 
        HSSFRow rowm  =sheet.createRow(0);  // 行 
        HSSFCell cellTiltle =rowm.createCell(0);  // 单元格 
         
        // sheet样式定义 
        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); // 头样式 
        HSSFCellStyle style = this.getStyle(workbook);  // 单元格样式 
        /** 
         * 参数说明 
         * 从0开始   第一行 第一列 都是从角标0开始 
         * 行 列 行列    (0,0,0,5)  合并第一行 第一列  到第一行 第六列 
         * 起始行，起始列，结束行，结束列 
         *  
         * new Region()  这个方法使过时的 
         */ 
        // 合并第一行的所有列 
        sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (rowName.length-1))); 
        cellTiltle.setCellStyle(columnTopStyle); 
        cellTiltle.setCellValue(title);  
         
        int columnNum = rowName.length;  // 表格列的长度 
        HSSFRow rowRowName = sheet.createRow(1);  // 在第二行创建行 
        HSSFCellStyle cells =workbook.createCellStyle(); 
        cells.setBottomBorderColor(HSSFColor.BLACK.index);   
        rowRowName.setRowStyle(cells); 
         
        // 循环 将列名放进去 
        for (int i = 0; i < columnNum; i++) { 
            HSSFCell  cellRowName = rowRowName.createCell((int)i); 
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 单元格类型 
            HSSFRichTextString text = new HSSFRichTextString(rowName[i]);  // 得到列的值 
            cellRowName.setCellValue(text); // 设置列的值 
            cellRowName.setCellStyle(columnTopStyle); // 样式 
        } 
        //创建所需的行数 
        for (int j = 0; j < dataList.size(); j++) { 
             HSSFCell  cell = null;   //设置单元格的数据类型  
             Employee employee =  dataList.get(j);
             HSSFRow row = sheet.createRow(j+2);
             cell = row.createCell(0,HSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(employee.getId());                       //设置单元格的值
            // cell = row.createCell(1,HSSFCell.CELL_TYPE_STRING);
            // cell.setCellValue(15);
             cell = row.createCell(1,HSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(employee.getLastName());
             cell = row.createCell(2,HSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(employee.getGender());
             cell = row.createCell(3,HSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(employee.getEmail());
        } 
 
        //  让列宽随着导出的列长自动适应 
         sheet.setColumnWidth(4, 100 * 256);
         sheet.autoSizeColumn((short)0); //调整第一列宽度 
         sheet.autoSizeColumn((short)1); //调整第二列宽度 
         sheet.autoSizeColumn((short)2); //调整第三列宽度 
         sheet.autoSizeColumn((short)3); //调整第四列宽度 
         if(workbook !=null){   
                try   
                {   
                    // excel 表文件名 
                    String fileName = title + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";   
                    String fileName11 = URLEncoder.encode(fileName,"UTF-8"); 
                    String headStr = "attachment; filename=\"" + fileName11 + "\"";   
                    response.setContentType("APPLICATION/OCTET-STREAM");   
                    response.setHeader("Content-Disposition", headStr);   
                    OutputStream out = response.getOutputStream();   
                    workbook.write(out); 
                    out.flush(); 
                    out.close(); 
                }   
                catch (IOException e)   
                {   
                    e.printStackTrace();   
                }  
                 
            }   
   
        } 
  

 public void getdata(){
   List<Employee> dataList = employeeMapper.getEmps();
   List<String[]> datastring = new ArrayList<>();
   for(int i=0;i<dataList.size();i++){
   
   }
 }
   
   
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {   
       
        // 设置字体   
        HSSFFont font = workbook.createFont();   
        //设置字体大小   
        font.setFontHeightInPoints((short)11);   
        //字体加粗   
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   
        //设置字体名字    
        font.setFontName("Courier New");   
        //设置样式;    
        HSSFCellStyle style = workbook.createCellStyle();   
        //设置底边框;    
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   
        //设置底边框颜色;     
        style.setBottomBorderColor(HSSFColor.BLACK.index);   
        //设置左边框;      
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   
        //设置左边框颜色;    
        style.setLeftBorderColor(HSSFColor.BLACK.index);   
        //设置右边框;    
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);   
        //设置右边框颜色;    
        style.setRightBorderColor(HSSFColor.BLACK.index);   
        //设置顶边框;    
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);   
        //设置顶边框颜色;     
        style.setTopBorderColor(HSSFColor.BLACK.index);   
        //在样式用应用设置的字体;     
        style.setFont(font);   
        //设置自动换行;    
        style.setWrapText(false);   
        //设置水平对齐的样式为居中对齐;     
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
        //设置垂直对齐的样式为居中对齐;    
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   
           
        return style;   
           
  }   
     
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {   
        // 设置字体   
        HSSFFont font = workbook.createFont();   
        //设置字体大小   
        //font.setFontHeightInPoints((short)10);   
        //字体加粗   
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   
        //设置字体名字    
        font.setFontName("Courier New");   
        //设置样式;    
        HSSFCellStyle style = workbook.createCellStyle();   
        //设置底边框;    
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   
        //设置底边框颜色;     
        style.setBottomBorderColor(HSSFColor.BLACK.index);   
        //设置左边框;      
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   
        //设置左边框颜色;    
        style.setLeftBorderColor(HSSFColor.BLACK.index);   
        //设置右边框;    
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);   
        //设置右边框颜色;    
        style.setRightBorderColor(HSSFColor.BLACK.index);   
        //设置顶边框;    
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);   
        //设置顶边框颜色;     
        style.setTopBorderColor(HSSFColor.BLACK.index);   
        //在样式用应用设置的字体;     
        style.setFont(font);   
        //设置自动换行;    
        style.setWrapText(false);   
        //设置水平对齐的样式为居中对齐;     
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   
        //设置垂直对齐的样式为居中对齐;    
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   
          
        return style;   
  } 
  
 

}

mapper

public List<Employee> getEmps();

mapper.xml

<select id="getEmps" resultType="com.mybatis.bean.Employee">
  select * from tbl_employee
 </select>