package com.changhong.epc.rely.api.tool;
|
|
import com.github.andyczy.java.excel.LocalExcelUtils;
|
import com.iemsoft.framework.cloud.core.tools.ObjectUtil;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.function.Consumer;
|
|
public class ExcelTool {
|
|
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
|
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
|
|
public static int importExcel(InputStream inputStream, String fileName, Consumer<Map<String, String>> consumer) throws IOException {
|
Workbook workbook = getWorkBook(inputStream, fileName);
|
if (workbook == null) {
|
return 0;
|
}
|
Sheet sheet = workbook.getSheetAt(0);
|
if (sheet == null) {
|
return 0;
|
}
|
Iterator<Row> rowIterator = sheet.rowIterator();
|
// 略过汉字标题
|
if(rowIterator.hasNext()) rowIterator.next();
|
// 记录code标题
|
String[] columns, values;
|
if(rowIterator.hasNext()) {
|
columns = getCallVals(rowIterator.next());
|
}else{
|
return 0;
|
}
|
Map<String, String> res;
|
int line = 0;
|
while (rowIterator.hasNext()){
|
values = getCallVals(rowIterator.next());
|
res = new HashMap<>();
|
for (int i = 0, j = Math.min(columns.length, values.length); i < j; i++) {
|
res.put(columns[i], values[i]);
|
}
|
consumer.accept(res);
|
line++;
|
}
|
return line;
|
}
|
|
public static String[] getCallVals(Row row){
|
Iterator<Cell> cellIterator = row.cellIterator();
|
List<String> res = new LinkedList<>();
|
while (cellIterator.hasNext()){
|
Cell cell = cellIterator.next();
|
res.add(convertCellToString(cell));
|
}
|
return res.stream().toArray(String[]::new);
|
}
|
|
public static String convertCellToString(Cell cell){
|
//如果为null会抛出异常，应当返回空字符串
|
if (cell == null)
|
return "";
|
|
//POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型
|
//解决日期2006/11/02格式读入后出错的问题，POI读取后变成“02-十一月-2006”格式
|
if(cell.toString().contains("-") && checkDate(cell.toString())){
|
String ans;
|
try {
|
ans = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
|
} catch (Exception e) {
|
ans = cell.toString();
|
}
|
return ans;
|
}
|
|
cell.setCellType(CellType.STRING);
|
return cell.getStringCellValue();
|
}
|
|
/**
|
* 判断是否是“02-十一月-2006”格式的日期类型
|
*/
|
private static boolean checkDate(String str){
|
String[] dataArr =str.split("-");
|
try {
|
if(dataArr.length == 3){
|
int x = Integer.parseInt(dataArr[0]);
|
String y = dataArr[1];
|
int z = Integer.parseInt(dataArr[2]);
|
if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
|
return true;
|
}
|
}
|
} catch (Exception e) {
|
return false;
|
}
|
return false;
|
}
|
|
private static Workbook getWorkBook(InputStream inputStream, String fileName) throws IOException {
|
if(fileName.endsWith(OFFICE_EXCEL_2003_POSTFIX)){
|
return new HSSFWorkbook(inputStream);
|
}else if(fileName.endsWith(OFFICE_EXCEL_2010_POSTFIX)){
|
return new XSSFWorkbook(inputStream);
|
}else{
|
return null;
|
}
|
}
|
|
public static void outExcel(OutputStream os, Map<String, String> title, List<Map<String, String>> data) throws IOException {
|
SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook());
|
Sheet sheet = workbook.createSheet();
|
if(ObjectUtil.notEmpty(title)){
|
int i = 0;
|
Row titleName = sheet.createRow(0);
|
Row titleCode = sheet.createRow(1);
|
for(Map.Entry<String, String> entry : title.entrySet()){
|
Cell cellName = titleName.createCell(i);
|
Cell cellCode = titleCode.createCell(i);
|
cellName.setCellValue(entry.getValue());
|
cellCode.setCellValue(entry.getKey());
|
i++;
|
}
|
}
|
if(ObjectUtil.notEmpty(data)){
|
|
}
|
workbook.write(os);
|
}
|
|
|
}
|
