java数据库数据导入excel

data导出入excel中

controller：

package com.longfor.hrssc.api.controller;

import com.longfor.hrssc.api.model.BasicInformation;

import com.longfor.hrssc.api.service.IBasicInformationService;

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

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

import org.springframework.beans.factory.annotation.Autowired;

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

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

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

import javax.servlet.http.HttpServletResponse;

import java.io.File;

import java.io.FileOutputStream;

import java.io.OutputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

 * Created by fmgao on 2019/5/5.

 */

@RestController

@RequestMapping("/export")

public class ExportExcel {

    @Autowired

    private IBasicInformationService basicInformationService;

    @RequestMapping(value = "/excel", method = RequestMethod.GET)

    public Object excel2(HttpServletResponse response) throws Exception {

//        list = getUsers();

        String columnName = "t_basic_information";

        BasicInformation basicInformation = new BasicInformation();

        basicInformation.setTableName(columnName);

        List<String> titles = new ArrayList();

        titles = basicInformationService.getColumnNames(basicInformation);

        System.out.println(titles);

        List<BasicInformation> list = new ArrayList();

        list = basicInformationService.getAllDatas(basicInformation);

        stuList2Excel(list,titles);

        return null;

    }

    /**

     * @param stuList 从数据库中查询需要导入excel文件的信息列表

     * @return 返回生成的excel文件的路径

     * @throws Exception

     */

    public static String stuList2Excel(List<BasicInformation> stuList,List<String> titles) throws Exception {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd hhmmss");

        Workbook wb = new XSSFWorkbook();

        //标题行抽出字段

//        String[] title = titles;

        //设置sheet名称，并创建新的sheet对象

        String sheetName = "学生信息一览";

        Sheet stuSheet = wb.createSheet(sheetName);

        //获取表头行

        Row titleRow = stuSheet.createRow(0);

        //创建单元格，设置style居中，字体，单元格大小等

        CellStyle style = wb.createCellStyle();

        Cell cell = null;

        //把已经写好的标题行写入excel文件中

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

            cell = titleRow.createCell(i);

            cell.setCellValue(titles.get(i));

            cell.setCellStyle(style);

        }

        //把从数据库中取得的数据一一写入excel文件中

        Row row = null;

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

            //创建list.size()行数据

            row = stuSheet.createRow(i + 1);

            //把值一一写进单元格里

            //设置第一列为自动递增的序号

//            row.createCell(0).setCellValue(i + 1);

            row.createCell(0).setCellValue(stuList.get(i).getId());

            row.createCell(1).setCellValue(stuList.get(i).getBasicCode());

            row.createCell(2).setCellValue(stuList.get(i).getBasicName());

            row.createCell(3).setCellValue(stuList.get(i).getBasicType());

            row.createCell(4).setCellValue(stuList.get(i).getBasicPid());

            row.createCell(5).setCellValue(stuList.get(i).getIsDelete());

            row.createCell(6).setCellValue(stuList.get(i).getCreateUserId());

            //把时间转换为指定格式的字符串再写入excel文件中

            if (stuList.get(i).getCreateTime() != null) {

                row.createCell(7).setCellValue(sdf.format(stuList.get(i).getCreateTime()));

            }

            if (stuList.get(i).getUpdateTime() != null) {

                row.createCell(8).setCellValue(sdf.format(stuList.get(i).getUpdateTime()));

            }

        }

        //设置单元格宽度自适应，在此基础上把宽度调至1.5倍

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

            stuSheet.autoSizeColumn(i, true);

            stuSheet.setColumnWidth(i, stuSheet.getColumnWidth(i) * 15 / 10);

        }

        //获取配置文件中保存对应excel文件的路径，本地也可以直接写成F：excel/stuInfoExcel路径

//        String folderPath = ResourceBundle.getBundle("systemconfig").getString("downloadFolder") + File.separator + "stuInfoExcel";

        String folderPath = "F:\\file_soft\\me\\excel\\";

        //创建上传文件目录

        File folder = new File(folderPath);

        //如果文件夹不存在创建对应的文件夹

        if (!folder.exists()) {

            folder.mkdirs();

        }

        //设置文件名

        String fileName = sdf1.format(new Date()) + sheetName + ".xlsx";

        String savePath = folderPath + File.separator + fileName;

        // System.out.println(savePath);

        OutputStream fileOut = new FileOutputStream(savePath);

        wb.write(fileOut);

        fileOut.close();

        //返回文件保存全路径

        System.out.println(savePath);

        return savePath;

    }

}

sevice：

/**

     * 获取所有的列

     * @param basicInformation

     * @return

     */

    public List<String> getColumnNames(BasicInformation basicInformation){

        List<String> names = basicInformationMapper.getColumnNames(basicInformation);

        return names;

    }

    /**

     * 获取所有的数据

     * @param basicInformation

     * @return

     */

    public List<BasicInformation> getAllDatas(BasicInformation basicInformation){

        List<BasicInformation> list = basicInformationMapper.getAllDatas(basicInformation);

        return list;

    }

dao:

    List<String> getColumnNames(BasicInformation basicInformation);

    List<BasicInformation> getAllDatas(BasicInformation basicInformation);

xml：

<!--getColumnNames-->

<select id="getColumnNames" resultType="java.lang.String"

        parameterType="com.longfor.hrssc.api.model.BasicInformation">

    select DISTINCT COLUMN_NAME from information_schema.COLUMNS where table_name=#{tableName}

</select>

<!--get all-->

<select id="getAllDatas" resultMap="BaseResultMap"

        parameterType="com.longfor.hrssc.api.model.BasicInformation">

    select * from t_basic_information;

</select>

pom:

<dependency>

    <groupId>javax.servlet</groupId>

    <artifactId>javax.servlet-api</artifactId>

    <version>3.1.0</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>3.16</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>3.16</version>

</dependency>