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>