1>引入依赖
<!--使用POI读取文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2>application.yml
spring:
datasource:
driver-class-name: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/orcl
username: sgsjsbzhz
password: sgsjsbzhz
servlet:
multipart:
max-file-size: 100MB
max-request-size: 1000MB
server:
port: 8080
mybatis:
mapper-locations: classpath:/mapper/*.xml
type-aliases-package: com.example.daorushuju.entity
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
3>utils
package com.example.daorushuju.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* 新增值班排班表导入Excel表工具类
* zyw
*/
public class ImportExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述:获取IO流中的数据,组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws Exception
*/
public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = ImportExcelUtil.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(ImportExcelUtil.getCellValue(cell));
}
list.add(li);
}
}
// work.close();
return list;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误!");
}
return wb;
}
/**
* 描述:对表格中数值进行格式化
* @param cell
* @return
*/
public static Object getCellValue(Cell cell){
Object value = null;
// DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
if (cell!=null){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
// value = df2.format(cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}
else{
value = sdf.format(cell.getDateCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
}
return value;
}
}
4>导入
package com.example.daorushuju.service;
import com.example.daorushuju.entity.SGS;
import com.example.daorushuju.mapper.Daorumapper;
import com.example.daorushuju.utils.ImportExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class Daoruserviceimpl implements Daoruservice {
@Autowired
private Daorumapper daorumapper;
public void importTprkxx(MultipartFile file) {
Map<String, Object> resultMap = new HashMap<>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
List<SGS> SGSList = new ArrayList<>();
try {
//验证文件类型
if (!file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xls") && !file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xlsx")) {
resultMap.put("mete", "文件类型有误!请上传Excle文件");
throw new Exception("文件类型有误!请上传Excle文件");
}
//获取数据
List<List<Object>> olist = ImportExcelUtil.getListByExcel(file.getInputStream(), file.getOriginalFilename());
resultMap.put("导入成功", 200);
//封装数据
for (int i = 0; i < olist.size(); i++) {
List<Object> list = olist.get(i);
SGS sgs = new SGS();
sgs.setDWMC(String.valueOf(list.get(0)));
sgs.setYJLBBH(String.valueOf(list.get(1)));
sgs.setYJLBMC(String.valueOf(list.get(2)));
sgs.setEJLBBH(String.valueOf(list.get(3)));
sgs.setEJLBMC(String.valueOf(list.get(4)));
sgs.setXH(String.valueOf(list.get(5)));
sgs.setBZH(String.valueOf(list.get(6)));
sgs.setZWMC(String.valueOf(list.get(7)));
sgs.setBTDBZH(String.valueOf(list.get(8)));
sgs.setCYGX(String.valueOf(list.get(9)));
if (String.valueOf(list.get(10))==null||String.valueOf(list.get(10)).equals("")) {
sgs.setFBRQ(null);
}else{
sgs.setFBRQ(String.valueOf(list.get(10)));
}
if (String.valueOf(list.get(11))==null||String.valueOf(list.get(11)).equals("")) {
sgs.setSSRQ(null);
}else{
sgs.setSSRQ(String.valueOf(list.get(11)));
}
sgs.setSYGW(String.valueOf(list.get(12)));
sgs.setBZ(String.valueOf(list.get(13)));
// SGSList.add(sgs);
daorumapper.insertSGS(sgs);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
5>导出
package com.example.daorushuju.controller;
import com.example.daorushuju.mapper.DaochuMapper;
import com.example.daorushuju.service.DaochuService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/daochu")
public class DaochuController extends HttpServlet {
@Autowired
private DaochuMapper daochuMapper;
@RequestMapping("/daochu")
public void daochu(HttpServletRequest req, HttpServletResponse resp) throws IOException {
OutputStream out = resp.getOutputStream();
try {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("15");
String ejjlbh = "12.1,12.2,12.3,12.4,12.5,12.6,12.6.1,12.6.2,12.7,12.7.1,12.7.2,12.8,12.9,12.10,12.11,12.12,12.13,12.14,12.15,12.16,12.17";
ArrayList<Object> list = new ArrayList<>();
String[] iv = ejjlbh.split(",");
for (int i = 0; i < iv.length; i++) {
list.add(iv[i]);
}
int i = 0;
for (int j = 0; j < list.size(); j++) {
List<Map<String, Object>> sgslist = daochuMapper.querysgsbyejbh(list.get(j).toString());
List<Map<String, Object>> dkylist = daochuMapper.querydkybyejbh(list.get(j).toString());
List<Map<String, Object>> reslist = new ArrayList<>();
reslist.addAll(sgslist);
reslist.addAll(dkylist);
// Row row = sheet.createRow(0);
for (Map<String, Object> map : reslist) {
Row row = sheet.createRow(i);
Cell cell0 = row.createCell(0);
cell0.setCellValue(map.get("DWMC").toString());
Cell cell1 = row.createCell(1);
cell1.setCellValue(map.get("YJLBBH").toString());
Cell cell2 = row.createCell(2);
cell2.setCellValue(map.get("YJLBMC").toString());
Cell cell3 = row.createCell(3);
cell3.setCellValue(map.get("EJLBBH").toString());
Cell cell4 = row.createCell(4);
cell4.setCellValue(map.get("EJLBMC").toString());
Cell cell5 = row.createCell(5);
cell5.setCellValue(map.get("XH").toString());
Cell cell6 = row.createCell(6);
cell6.setCellValue(map.get("BZH").toString());
Cell cell7 = row.createCell(7);
cell7.setCellValue(map.get("ZWMC").toString());
Cell cell8 = row.createCell(8);
cell8.setCellValue(map.get("BTDBZH").toString());
Cell cell9 = row.createCell(9);
cell9.setCellValue(map.get("CYGX").toString());
Cell cell10 = row.createCell(10);
cell10.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("FBRQ")));
Cell cell11 = row.createCell(11);
cell11.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("SSRQ")));
Cell cell12 = row.createCell(12);
cell12.setCellValue(map.get("SYGW").toString());
Cell cell13 = row.createCell(13);
cell13.setCellValue(map.get("BZ").toString());
i++;
}
}
List<Map<String, Object>> sgsnulllist = daochuMapper.querysgsnulllist("12");
List<Map<String, Object>> dkynulllist = daochuMapper.querydkynulllist("12");
List<Map<String, Object>> reslist = new ArrayList<>();
reslist.addAll(sgsnulllist);
reslist.addAll(dkynulllist);
for (Map<String, Object> map : reslist) {
Row row = sheet.createRow(i);
Cell cell0 = row.createCell(0);
cell0.setCellValue(map.get("DWMC").toString());
Cell cell1 = row.createCell(1);
cell1.setCellValue(map.get("YJLBBH").toString());
Cell cell2 = row.createCell(2);
cell2.setCellValue(map.get("YJLBMC").toString());
Cell cell3 = row.createCell(3);
cell3.setCellValue(map.get("EJLBBH").toString());
Cell cell4 = row.createCell(4);
cell4.setCellValue(map.get("EJLBMC").toString());
Cell cell5 = row.createCell(5);
cell5.setCellValue(map.get("XH").toString());
Cell cell6 = row.createCell(6);
cell6.setCellValue(map.get("BZH").toString());
Cell cell7 = row.createCell(7);
cell7.setCellValue(map.get("ZWMC").toString());
Cell cell8 = row.createCell(8);
cell8.setCellValue(map.get("BTDBZH").toString());
Cell cell9 = row.createCell(9);
cell9.setCellValue(map.get("CYGX").toString());
Cell cell10 = row.createCell(10);
cell10.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("FBRQ")));
Cell cell11 = row.createCell(11);
cell11.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("SSRQ")));
Cell cell12 = row.createCell(12);
cell12.setCellValue(map.get("SYGW").toString());
Cell cell13 = row.createCell(13);
cell13.setCellValue(map.get("BZ").toString());
i++;
}
resp.setHeader("Content-Disposition", "attachment;filename=" + new String("12.xls".getBytes("utf-8"), "iso8859-1"));
resp.setContentType("application/ynd.ms-excel;charset=UTF-8");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
}
// @Override
// protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//
// OutputStream out = resp.getOutputStream();
// try {
// Workbook workbook = new HSSFWorkbook();
// Sheet sheet = workbook.createSheet("13调度与交易");//设置题目
//
// String ejjlbh = "13.1,13.2,13.3,13.4,13.5,13.6,13.7,13.8,13.9,13.10,13.11,13.12";
// ArrayList<Object> list = new ArrayList<>();
// String[] iv = ejjlbh.split(",");
// for (int i = 0;i<iv.length;i++){
// list.add(iv[i]);
// }
//
// for (int j = 0;j<list.size();j++){
// List<Map<String, Object>> sgslist = daochuMapper.querysgsbyejbh(list.get(j).toString());
// List<Map<String, Object>> dkylist = daochuMapper.querydkybyejbh(list.get(j).toString());
// List<Map<String, Object>> reslist = new ArrayList<>();
// reslist.addAll(sgslist);
// reslist.addAll(dkylist);
//
// Row row = sheet.createRow(0);
// int i = 1;
// for (Map<String, Object> map : reslist) {
// row = sheet.createRow(i);
// Cell cell0 = row.createCell(0);
// cell0.setCellValue(map.get("DWMC").toString());
// Cell cell1 = row.createCell(1);
// cell1.setCellValue(map.get("YJLBBH").toString());
// Cell cell2 = row.createCell(2);
// cell2.setCellValue(map.get("YJLBMC").toString());
// Cell cell3 = row.createCell(3);
// cell3.setCellValue(map.get("EJLBBH").toString());
// Cell cell4 = row.createCell(4);
// cell4.setCellValue(map.get("EJLBMC").toString());
// Cell cell5 = row.createCell(5);
// cell5.setCellValue(map.get("XH").toString());
// Cell cell6 = row.createCell(6);
// cell6.setCellValue(map.get("BZH").toString());
// Cell cell7 = row.createCell(7);
// cell7.setCellValue(map.get("ZWMC").toString());
// Cell cell8 = row.createCell(8);
// cell8.setCellValue(map.get("BTDBZH").toString());
// Cell cell9 = row.createCell(9);
// cell9.setCellValue(map.get("CYGX").toString());
// Cell cell10 = row.createCell(10);
// cell10.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("FBRQ")));
// Cell cell11 = row.createCell(11);
// cell11.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("SSRQ")));
// Cell cell12 = row.createCell(12);
// cell12.setCellValue(map.get("SYGW").toString());
// Cell cell13 = row.createCell(13);
// cell13.setCellValue(map.get("BZ").toString());
// i++;
// }
// }
//
//
// resp.setHeader("Content-Disposition", "attachment;filename=" + new String("13调度与交易.xls".getBytes("utf-8"), "iso8859-1"));
// resp.setContentType("application/ynd.ms-excel;charset=UTF-8");
//
// workbook.write(out);
// } catch (Exception e) {
// e.printStackTrace();
// } finally {
// out.flush();
// out.close();
// }
//
// }
//
// @Override
// protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// super.doPost(req, resp);
// }
}
}