Excel导入
- 工具类
import com.znfx.stockholder.api.exception.ExcelException;
import lombok.Getter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Getter
public class ReadExcel {
// 总行数
private final int totalRows = 0;
// 总条数
private final int totalCells = 0;
/**
* 读EXCEL文件,获取信息集合
*
* @param file :上传excel文件
* @return list
*/
public List<Map<String, String>> getExcelInfo(File file, String fileName) {
List<Map<String, String>> list = null;
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
assert fileName != null;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
list = createExcel(new FileInputStream(file), isExcel2003);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 根据excel里面的内容读取客户信息
*
* @param is:输入流
* @param isExcel2003 excel是2003还是2007版本
* @return list
*/
public List<Map<String, String>> createExcel(InputStream is, boolean isExcel2003) {
List<Map<String, String>> list = null;
try {
Workbook wb;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
list = readExcelValue(wb);// 读取Excel里面客户的信息
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
* 读取Excel里面客户的信息
*
* @param wb:文件内容
* @return list
*/
public List<Map<String, String>> readExcelValue(Workbook wb) {
List<Map<String, String>> list = new ArrayList<>();
try {
// sheet 从0开始
Sheet sheet = wb.getSheetAt(0);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
Row rowTitle = sheet.getRow(0);
// 标题行的最后一个单元格位置
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
for (int i = 0; i < cellTitleNum; i++) {
Cell cell = rowTitle.getCell(Short.parseShort(i + ""));
if (cell != null) {
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "";
}
}
// 行循环开始
int i = 1;
while (i < rowNum) {
Map<String, String> map = new HashMap<>();
// 行
Row row = sheet.getRow(i);
// 列循环开始
int j = 0;
while (j < cellTitleNum) {
Cell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = "";
if (cell != null) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
}
map.put(title[j], cellValue);
j++;
}
list.add(map);
i++;
}
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 验证EXCEL文件
*
* @param filePath:文件路径
*/
public boolean validateExcel(String filePath) throws ExcelException {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
throw new ExcelException("文件名不是excel格式");
}
return true;
}
// @描述:是否是2003的excel,返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述:是否是2007的excel,返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
- controller
package com.znfx.stockholder.stockholder.excel;
import com.znfx.stockholder.api.stockholder.stockholder.createStockholder.CreateStockholderCommand;
import com.znfx.stockholder.base.DomainCommandProcessorManager;
import com.znfx.stockholder.base.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.io.File;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
@Slf4j
@RestController
public class ExcelController {
private final DomainCommandProcessorManager processorManager;
@Autowired
public ExcelController(DomainCommandProcessorManager processorManager) {
this.processorManager = processorManager;
}
@RequestMapping("/import")
public void main() throws Exception {
File file = new File("E:\\file.xlsx");
ExcelController controller = SpringContextUtil.getApplicationContext().getBean(ExcelController.class);
controller.impExcel(file);
}
/**
* 导入excel模板
*/
@RequestMapping(value = "/impExcel", method = RequestMethod.POST)
@ResponseBody
public List<Map<String, String>> impExcel(File file) throws Exception {
// 创建处理EXCEL的类
ReadExcel readExcel = new ReadExcel();
// 解析excel,获取上传的事件单
List<Map<String, String>> list = readExcel.getExcelInfo(file, file.getName());
// 至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作
if (list != null && !list.isEmpty()) {
for (Map<String, String> m : list) {
ReadExcel.toString(m);
Integer sex = ReadExcel.getSex(m);
String national = ReadExcel.getNational(m);
String politic = ReadExcel.getPolitic(m);
Boolean expiryDateEndLongTerm = ReadExcel.getBoolean(m);
for (String key : m.keySet()) {
String value = m.get(key);
System.out.println(key + ":" + value);
}
System.out.println("请确定是否要创建股东:" + m.get("姓名") + ",创建请任意输入,点击回车");
Scanner scanner = new Scanner(System.in);
scanner.nextLine();
// 设置模板的表头
CreateStockholderCommand command = CreateStockholderCommand.build(m.get("姓名"), m.get("身份证号"),
m.get("电话"), sex, national, politic, Long.valueOf(m.get("出生日期")), m.get("学历"),
m.get("开户行"), m.get("银行卡号"), m.get("工作单位"), m.get("职务"), m.get("住址"), m.get("邮箱"),
m.get("邮寄地址"), Long.valueOf(m.get("身份证失效日期起")), Long.valueOf(m.get("身份证失效日期止")),
expiryDateEndLongTerm);
this.processorManager.process(command);
System.out.println("股东" + m.get("姓名") + "创建成功");
}
System.out.println("该excel表格中的股东已创建完毕");
}
return list;
}
}
Excel导出
-
工具类
package com.znfx.scale.util;
import com.znfx.scale.entity.dto.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* 导出Excel
*/
@Slf4j
public class ExcelUtil {
public static void exportExcel(HttpServletResponse response, ExcelData data) {
log.info("导出解析开始,fileName:{}", data.getFileName());
try {
//实例化HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Excel表单,参数为sheet的名字
HSSFSheet sheet = workbook.createSheet("sheet");
//设置表头
setTitle(workbook, sheet, data.getHead());
//设置单元格并赋值
setData(sheet, data.getData());
//设置浏览器下载
setBrowser(response, workbook, data.getFileName());
log.info("导出解析成功!");
} catch (Exception e) {
log.info("导出解析失败!");
e.printStackTrace();
}
}
/**
* 方法名:setTitle
* 功能:设置表头
*/
private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
try {
HSSFRow row = sheet.createRow(0);
//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
for (int i = 0; i <= str.length; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
//设置为居中加粗,格式化时间格式
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd"));
//创建表头名称
HSSFCell cell;
for (int j = 0; j < str.length; j++) {
cell = row.createCell(j);
cell.setCellValue(str[j]);
cell.setCellStyle(style);
}
} catch (Exception e) {
log.info("导出时设置表头失败!");
e.printStackTrace();
}
}
/**
* 方法名:setData
* 功能:表格赋值
*/
private static void setData(HSSFSheet sheet, List<String[]> data) {
try {
int rowNum = 1;
for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).length; j++) {
row.createCell(j).setCellValue(data.get(i)[j]);
}
rowNum++;
}
log.info("表格赋值成功!");
} catch (Exception e) {
log.info("表格赋值失败!");
e.printStackTrace();
}
}
/**
* 方法名:setBrowser
* 功能:使用浏览器下载
*/
private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(fileName, "UTF-8") + ".xls");
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//将excel写入到输出流中
workbook.write(os);
os.flush();
os.close();
log.info("设置浏览器下载成功!");
} catch (Exception e) {
log.info("设置浏览器下载失败!");
e.printStackTrace();
}
}
}
- service实现类
package com.znfx.scale.service.impl;
import com.znfx.scale.entity.dto.ExcelData;
import com.znfx.scale.entity.dto.StockDto;
import com.znfx.scale.entity.dto.StockInDto;
import com.znfx.scale.entity.dto.StockOutDto;
import com.znfx.scale.mapper.StockInMapper;
import com.znfx.scale.mapper.StockMapper;
import com.znfx.scale.mapper.StockOutMapper;
import com.znfx.scale.service.ExcelExportService;
import com.znfx.scale.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
@Slf4j
@Service
public class ExcelExportServiceImpl implements ExcelExportService {
@Resource
private StockInMapper stockInMapper;
@Resource
private StockOutMapper stockOutMapper;
@Resource
private StockMapper stockMapper;
/**
* 导出入库表
*/
public Boolean exportStockInExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
log.info("导出数据开始..............");
//查询数据并赋值给ExcelData
List<StockInDto> inDtoList = stockInMapper.selectAllStockIn(sessid, store);
List<String[]> list = new ArrayList<>();
for (StockInDto dto : inDtoList) {
String[] arrs = new String[7];
arrs[0] = String.valueOf(dto.getStoreName());
arrs[1] = String.valueOf(dto.getKindName());
arrs[2] = String.valueOf(dto.getLevelName());
arrs[3] = String.valueOf(dto.getNumber());
arrs[4] = String.valueOf(dto.getPrice());
arrs[5] = String.valueOf(dto.getOrigin());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String dateString = simpleDateFormat.format(dto.getDate());
arrs[6] = dateString;
list.add(arrs);
}
//表头赋值
String[] head = {"仓库名", "品种名", "品级名", "入库量", "价格", "产地", "入库日期"};
ExcelData data = new ExcelData();
data.setHead(head);
data.setData(list);
data.setFileName(fileName);
//实现导出
try {
ExcelUtil.exportExcel(response, data);
log.info("导出数据结束..............");
return true;
} catch (Exception e) {
log.info("导出数据失败..............");
return false;
}
}
@Override
public Boolean exportStockOutExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
//查询数据并赋值给ExcelData
List<StockOutDto> outDtoList = stockOutMapper.selectAllStockOut(sessid, store);
List<String[]> list = new ArrayList<>();
for (StockOutDto dto : outDtoList) {
String[] arrs = new String[7];
arrs[0] = String.valueOf(dto.getStoreName());
arrs[1] = String.valueOf(dto.getKindName());
arrs[2] = String.valueOf(dto.getLevelName());
arrs[3] = String.valueOf(dto.getNumber());
arrs[4] = String.valueOf(dto.getPrice());
arrs[5] = String.valueOf(dto.getAddress());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String dateString = simpleDateFormat.format(dto.getDate());
arrs[6] = dateString;
list.add(arrs);
}
//表头赋值
String[] head = {"仓库名", "品种名", "品级名", "出库量", "价格", "目的地", "出库日期"};
ExcelData data = new ExcelData();
data.setHead(head);
data.setData(list);
data.setFileName(fileName);
//实现导出
try {
ExcelUtil.exportExcel(response, data);
return true;
} catch (Exception e) {
return false;
}
}
@Override
public Boolean exportStockExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
//查询数据并赋值给ExcelData
List<StockDto> dtoList = stockMapper.selectAllStock(sessid, store);
List<String[]> list = new ArrayList<>();
for (StockDto dto : dtoList) {
String[] arrs = new String[5];
arrs[0] = String.valueOf(dto.getStoreName());
arrs[1] = String.valueOf(dto.getKindName());
arrs[2] = String.valueOf(dto.getLevelName());
arrs[3] = String.valueOf(dto.getNumber());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String dateString = simpleDateFormat.format(dto.getDate());
arrs[4] = dateString;
list.add(arrs);
}
//表头赋值
String[] head = {"仓库名", "品种名", "品级名", "库存量", "出库日期"};
ExcelData data = new ExcelData();
data.setHead(head);
data.setData(list);
data.setFileName(fileName);
//实现导出
try {
ExcelUtil.exportExcel(response, data);
return true;
} catch (Exception e) {
return false;
}
}
}
- controller
package com.znfx.scale.controller;
import com.znfx.scale.service.ExcelExportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
@Slf4j
@RestController
@RequestMapping("/export")
public class ExcelExportController {
@Resource
private ExcelExportService excelService;
/**
* 导出入库信息
*
* @param response response
* @param sessid 用户id
* @param store 仓库id
* @return Excel
*/
@RequestMapping("/in")
public String exportStockInExcel(HttpServletResponse response, Integer sessid, Integer store) {
Boolean isOk = excelService.exportStockInExcel(response, "入库信息表", sessid, store);
if (isOk) {
return "导出成功!";
} else {
return "导出失败!";
}
}
@RequestMapping("/out")
public String exportStockOutExcel(HttpServletResponse response, Integer sessid, Integer store) {
Boolean isOk = excelService.exportStockOutExcel(response, "出库信息表", sessid, store);
if (isOk) {
return "导出成功!";
} else {
return "导出失败!";
}
}
@RequestMapping("/stock")
public String exportStockExcel(HttpServletResponse response, Integer sessid, Integer store) {
Boolean isOk = excelService.exportStockExcel(response, "库存信息表", sessid, store);
if (isOk) {
return "导出成功!";
} else {
return "导出失败!";
}
}
}