`import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.;
public class ExcelUtil {
private static final DataFormatter FORMATTER = new DataFormatter();
public static List<ImportBean> importExcel(String path) throws ExcelException{
List<ImportBean> list = new ArrayList<> ();
ImportBean importBean = null;
File file = new File(path);
if(!file.exists()){
throw new ExcelException("文件不存在!");
}
Workbook wb = null;
InputStream in = null;
try{
in = new FileInputStream(file);
//通过后缀判断格式
String fileName = path;
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
in = new FileInputStream(file);
if( fileType.equals("xls") ) {
//创建excel文件对象
wb = new HSSFWorkbook(in);
} else if (fileType.equals("xlsx")){
wb = new XSSFWorkbook(in);
} else {
throw new ExcelException("模板格式不正确,请选择xls或者xlsx文件!");
}
//获取第一张表
Sheet s = wb.getSheetAt(0);
String[] cloumnTitle = new String[]{"发货单位", "发货人姓名", "发货人联系电话", "发货人备用电话", "启运地(省)", "启运地(市)", "启运地(区)", "具体发货地址"};
//判断第二行,表格式是否正确
Row rowTwo = s.getRow(1);
if(null ==rowTwo){
throw new ExcelException("模板导入有误!");
}
int cells = rowTwo.getPhysicalNumberOfCells();
//getPhysicalNumberOfCells()获得的是不为空的列数,getLastCellNum()最后一不为空列数;实际列数比设定表头多即可,有一个cell空值判断
if(cells < cloumnTitle.length){
throw new ExcelException("模板格式不正确,请选择xls或者xlsx文件!");
}
for(int i = 0; i < cells; i++){
//获取列的值,校验
Cell cell = rowTwo.getCell(i);
if(null != cell){
String cloumnValue = FORMATTER.formatCellValue(cell);
if(!cloumnTitle[i].equals(cloumnValue)){
throw new ExcelException("模板格式不正确,请选择xls或者xlsx文件!");
}
}
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for(int i = 2; i <= s.getLastRowNum(); i++){
Row row = s.getRow(i);
String value = "";
if(null != row){
Map<String,Object> rowMap = new HashMap<>();
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
for(int j=firstCellIndex; j < lastCellIndex; j++){
Cell cell = row.getCell(j);
if(null == cell) {
continue;
}
int type = cell.getCellType();
if (type==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
value = sdf.format(date);
} else {
double doubleValue = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("#,######");
value = df.format(cell.getNumericCellValue());
}
rowMap.put(j+"", value);
} else {
//Cell有6种值类型,未知类型,数值类型(整小时间),字符串,布尔,空,error。校验剩下的只有
//字符串,布尔
value = cell.toString();
rowMap.put(j+"", value);
}
}
importBean = new ImportBean();
String consCompanyName = "";
String consName = "";
String consMobile = "";
String spareMobile = "";
String consProvince = "";
String consCity = "";
String consArea = "";
String consDetailAddr = "";
if(null != rowMap.get("0") && !"".equals(rowMap.get("0"))){
consCompanyName = rowMap.get("0").toString().trim();
}
if(null != rowMap.get("1") && !"".equals(rowMap.get("1"))){
consName = rowMap.get("1").toString().trim();
}
if(null != rowMap.get("2") && !"".equals(rowMap.get("2"))){
consMobile = rowMap.get("2").toString().trim();
}
if(null != rowMap.get("3") && !"".equals(rowMap.get("3"))){
spareMobile = rowMap.get("3").toString().trim();
}
if(null != rowMap.get("4") && !"".equals(rowMap.get("4"))){
consProvince = rowMap.get("4").toString().trim();
}
if(null != rowMap.get("5") && !"".equals(rowMap.get("5"))){
consCity = rowMap.get("5").toString().trim();
}
if(null != rowMap.get("6") && !"".equals(rowMap.get("6"))){
consArea = rowMap.get("6").toString().trim();
}
if(null != rowMap.get("7") && !"".equals(rowMap.get("7"))){
consDetailAddr = rowMap.get("7").toString().trim();
}
importBean.setConsCompanyName(consCompanyName);
importBean.setConsName(consName);
importBean.setConsProvince(consProvince);
importBean.setConsCity(consCity);
importBean.setConsArea(consArea);
importBean.setConsDetailAddr(consDetailAddr);
importBean.setConsMobile(consMobile);
importBean.setSpareMobile(spareMobile);
list.add(importBean);
}
}
}catch (IOException e){
e.printStackTrace();
}
return list;
}
public static void exportExcel(List<ImportBean> list){
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyMMddhhmmss");
Date date = new Date();
String dateString = sdf.format(date);
String path = "consignor_" + dateString + ".xls";
File file = new File(path);
if(!file.exists()){
file.createNewFile();
}
OutputStream out = new FileOutputStream(file);
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("发货人导出表");
//设置表头
Row rowOne = sheet.createRow(0);
String[] cloumnTitle = new String[]{"发货单位", "发货人姓名", "发货人联系电话", "发货人备用电话", "启运地(省)", "启运地(市)", "启运地(区)", "具体发货地址"};
for (int i = 0; i < cloumnTitle.length; i++) {
Cell cell = rowOne.createCell(i);
cell.setCellValue(cloumnTitle[i]);
}
//导入各行数据
for (int i = 0; i < list.size(); i++) {
ImportBean importBean = list.get(i);
if (null == importBean) {
continue;
}
Row row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(importBean.getConsCompanyName());
row.createCell(1).setCellValue(importBean.getConsName());
row.createCell(2).setCellValue(importBean.getConsMobile());
row.createCell(3).setCellValue(importBean.getSpareMobile());
row.createCell(4).setCellValue(importBean.getConsProvince());
row.createCell(5).setCellValue(importBean.getConsCity());
row.createCell(6).setCellValue(importBean.getConsArea());
row.createCell(7).setCellValue(importBean.getConsDetailAddr());
}
wb.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
}
}
}