读Excel代码示例

最新推荐文章于 2023-02-11 08:31:29 发布
u011287511
最新推荐文章于 2023-02-11 08:31:29 发布
阅读量275
点赞数
本文链接：https://blog.csdn.net/u011287511/article/details/74347042
版权
code保存专栏收录该内容
2 篇文章 0 订阅
订阅专栏
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import com.google.gson.JsonObject;
import com.mixky.app.eds.temp.BciccDataItem;
import com.mixky.toolkit.CharByteConverter;


/** 
 * ClassName: QrZYLUploadManager <br/> 
 * Function:  <br/> 
 * date: 2017年4月24日 下午1:37:22 <br/> 
 * @version  2.0
 */
public class QrZYLUploadManager {

private static final Logger LOG=LoggerFactory.getLogger(QrZYLUploadManager.class);
static String IMPORT_FAIL = "导入出现问题\r\n";// 异常头信息
static String IMPORT_SUCCESS = "导入操作成功\r\n";// 成功
static String IMPORT_FINISH = "导入完成，请查看以下情况是否有问题：\r\n";// 操作完成，但是可能有问题


public static JsonObject readZYLExcel(File file, long enterID, String time) throws IOException {
// 1.判断导入excel文件是否存在
JsonObject result = new JsonObject();// 导入结果json
StringBuilder mes = new StringBuilder();// 异常提示信息
StringBuilder tip=new StringBuilder();//需要关注的提示信息
String fileName = "";// 文件名

if (file.exists()) {
fileName = file.getName();
} else {
result.addProperty("success", false);
result.addProperty("message", IMPORT_FAIL + "文件不存在");
return result;
}


// 2.判断文件格式是否正确
Workbook wb = null;
InputStream is = new FileInputStream(file);
if (fileName.substring(fileName.lastIndexOf('.') + 1).equals("xlsx")) {
wb = new XSSFWorkbook(is);
} else if (fileName.substring(fileName.lastIndexOf('.') + 1).equals("xls")) {
wb = new HSSFWorkbook(is);
} else {
result.addProperty("success", false);
result.addProperty("message", IMPORT_FAIL + "文件格式不正确，只支持拓展名为xlsx和xls的表格文件");
is.close();
return result;
}


//循环整个表格
int sheetnum = wb.getNumberOfSheets();// 获取一共有多少个sheet

for (int i = 0; i < sheetnum; i++) {// 循环sheet
boolean isExport=true;//是否导入当前循环的数据
Sheet sheet = wb.getSheetAt(i);
String sheetName = sheet.getSheetName().trim();// 获取sheet名

//第一行第二行都为空 ，忽略该工作表
Row firstRow=sheet.getRow(0);
Row secondRow=sheet.getRow(1);
if(firstRow==null&&secondRow==null){
continue;
}
//没有XX，忽略该工作表
Row brandRow=sheet.getRow(6);
if(brandRow==null){
tip.append("(提示信息)XX["+sheetName+"]没有XX,导入忽略\r\n");
continue;
}

// 3.判断日期是否正确,避免3月份数据错当成了2月份数据导入数据库
//3.1获取日期
String cellValueZYLDate = "";// 存excel中占有率日期
String errorLocation="";//存储格式错误位置


errorLocation = createErrorLocation(sheetName, 4, 1);//B5
try {
if(sheet.getRow(4)!=null){
cellValueZYLDate = getCellValue(sheet.getRow(4).getCell(1));
}
} catch (Exception e) {
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
}
//3.2判断空值和格式
String regex="^(\\d{4}(\\.)\\d{1,2})$";//规定一下excel中日期的格式yyyy.MM或yyyy.M
if(StringUtils.isEmpty(cellValueZYLDate)){
mes.append("填报日期为空,错误位置：[").append(errorLocation).append("](正确格式如：2016.09)\r\n");isExport=false;
}else{
cellValueZYLDate=CharByteConverter.ToDBC(cellValueZYLDate.trim());
}
if(!cellValueZYLDate.matches(regex)){
mes.append("填报日期格式不正确,错误位置：[").append(errorLocation).append("](正确格式如：2016.09)\r\n");isExport=false;
}
//3.3选择导入日期是否正确
String tempTime=time;//和参数的time值一样，避免修改time
tempTime=tempTime.replaceAll("\\-", "\\.");
cellValueZYLDate=cellValueZYLDate.replaceAll("\\-", "\\.").replaceAll("\\/", "\\.").replaceAll("\\\\", "\\.");
if(cellValueZYLDate.contains(".")){
if(cellValueZYLDate.length()==6){//把yyyy.M变成yyyy.MM
StringBuilder sb=new StringBuilder();
sb.append(cellValueZYLDate);
sb.insert(5, "0");
cellValueZYLDate=sb.toString();
}
}else{
tempTime=tempTime.replaceAll("\\.", "");//与yyyyMM格式一样
}
LOG.info("tempTime:"+tempTime);
LOG.info("exceltime:"+cellValueZYLDate);
if(!cellValueZYLDate.equals(tempTime)){
mes.append("请检查您选择的导入日期和[").append(errorLocation).append("]的填报日期是否一致\r\n");isExport=false;
}


//4.判断企业id是否正确
//4.1获取企业id
String cellValueEnterid="";//存企业id
errorLocation=createErrorLocation(sheetName, 2, 1);//B3
try{
if(sheet.getRow(2)!=null){
cellValueEnterid=getCellValue(sheet.getRow(2).getCell(1));
}
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
}
//4.2判断企业id
long excelEnterID=0;
if(StringUtils.isEmpty(cellValueEnterid)){
mes.append("企业编号为空,错误位置：[").append(errorLocation).append("]\r\n");isExport=false;
}else{
cellValueEnterid=CharByteConverter.ToDBC(cellValueEnterid.trim());
try{
excelEnterID = Long.parseLong(cellValueEnterid);
}catch(Exception e){
e.printStackTrace();
mes.append("企业编号解析错误,错误位置：[").append(errorLocation).append("]\r\n");isExport=false;
}
}
if(excelEnterID!=enterID){
mes.append("请检查您选择企业的企业编号和[").append(errorLocation).append("]是否一致\r\n");isExport=false;
} 

//5.判断XX
//5.1判断XX与该sheet名称是否一致
String cellValueCommType="";//XX
errorLocation=createErrorLocation(sheetName, 2, 1);//B4
try{
if(sheet.getRow(3)!=null){
cellValueCommType=getCellValue(sheet.getRow(3).getCell(1));
}
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
}
if(StringUtils.isEmpty(cellValueCommType)){
mes.append("XX为空,错误位置：[").append(errorLocation).append("]\r\n");isExport=false;
}else{
cellValueCommType=CharByteConverter.ToDBC(cellValueCommType.trim());
}
if(!cellValueCommType.equals(sheetName)){
tip.append("请检查工作表(sheet)的名称和XX[").append(cellValueCommType).append("]位置[").append(errorLocation).append("]是否一致\r\n");
}


// 5.2数据库中是否有这个XX
List<ReportTempInfo> list = ShareReportService.getTempNo(cellValueCommType);// 根据XX查询
int listSize = 0;
if (!list.isEmpty()) {
listSize = list.size();
}
String[] times=time.split("-");
if (listSize < 1) {// 没有
mes.append("不存在名为[").append(cellValueCommType).append("]的XX\r\n");isExport=false;
} else {
//有重复XX，忽略第一个以外的
//5.3判断本期的商品是否已经上报
if(StringUtils.isNotEmpty(time)){
List<ReportBasicInfo> iu = ShareReportService.isUpload(times[0],times[1], list.get(0).getId(),(int)excelEnterID);
if(!iu.isEmpty()){
tip.append("(提示信息)该商品类别[").append(cellValueCommType).append("]已上报,该月导入忽略\r\n");
continue;
}
}else{
result.addProperty("success", false);
result.addProperty("message", IMPORT_FAIL + "必须选择导入时间");
is.close();
return result;
}
}

//6.验证第六行标题行
List<BciccDataItem> itemList=null;
if(list==null||list.size()<1){
continue;
}else{
itemList = ShareReportService.getTempItem(list.get(0).getId());
Row tempRow = sheet.getRow(5);
if(tempRow==null){
mes.append("XX[").append(cellValueCommType).append("]第6行不能为空，应包含[XX名称]、[零售额（元）]、[零售量（件）]\r\n");isExport=false;
}else{
//确认第6行表格头正确
String cellValue="";
errorLocation=createErrorLocation(sheetName, 5, 0);//A6 B6 C6
try{
if(sheet.getRow(5)!=null){
cellValue=getCellValue(sheet.getRow(5).getCell(0));
}
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
}
if(StringUtils.isEmpty(cellValue)){
mes.append("[XX名称]").append("为空,错误位置：[").append(errorLocation).append("]\r\n");isExport=false;
}else{
cellValue=CharByteConverter.ToDBC(cellValue.trim());
if(!"XX名称".equals(cellValue)){
mes.append("请检查[").append(errorLocation).append("]是否为[XX名称]\r\n");isExport=false;
} 
} 
if(itemList==null||itemList.size()<0){
continue;
}else{
for(int index=1;index<3;index++){
errorLocation=createErrorLocation(sheetName, 5, index);//A6 B6 C6
try{
if(sheet.getRow(5)!=null){
cellValue=getCellValue(sheet.getRow(5).getCell(index));
}
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
}
if(StringUtils.isEmpty(cellValue)){
mes.append(itemList.get(index-1).getF_name()).append("为空,错误位置：[").append(errorLocation).append("]\r\n");isExport=false;
}else{
cellValue=CharByteConverter.ToDBC(cellValue.trim());
if(StringUtils.isEmpty(itemList.get(index-1).getF_name())){
mes.append("XX [").append(cellValueCommType).append("]的数据项为空(请联系管理员处理)\r\n");
isExport=false;
}else{
String dataitemName=CharByteConverter.ToDBC(itemList.get(index-1).getF_name());
if(!cellValue.equals(dataitemName)){
mes.append("请检查XX [").append(cellValueCommType).append("]的位置[").append(errorLocation).append("]是否为[").append(dataitemName).append("]\r\n");
isExport=false;
} 
}
}
}
}
//7.检验XX，判断对应的XX是否在表中存在
boolean isOver=false;
for (int itemLine = 6; itemLine <= sheet.getLastRowNum(); itemLine++) {
if(sheet.getRow(itemLine)==null){
tip.append("(提示信息)XX[").append(cellValueCommType).append("]第"+(itemLine+1)+"行是空行\r\n");
}else{
//XX检验
errorLocation=createErrorLocation(sheetName,itemLine, 0);
String itemName = "";
try{
if(sheet.getRow(itemLine)!=null){
itemName=getCellValue(sheet.getRow(itemLine).getCell(0));
}
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false; 
}

if(StringUtils.isEmpty(itemName)){
if(isOver){
break;
}else{
isOver=true;
continue;
}
//mes.append("XX [").append(cellValueCommType).append("]的XX为空,位置：[").append(errorLocation).append("]\r\n");
}else{
itemName=CharByteConverter.ToDBC(itemName.trim());
//XX
List<Brand> wareList=ShareReportService.getBrand(itemName,list.get(0).getId());
int wareNum = 0;
if (!wareList.isEmpty()) {
wareNum = wareList.size();
}
if (wareNum < 1) {
mes.append("XX[").append(cellValueCommType).append("]的XX[").append(itemName).append("]不存在\r\n");isExport=false;
} 
}
//数据检验
Cell cell = sheet.getRow(itemLine).getCell(1);
String cellLSE="";
errorLocation=createErrorLocation(sheetName,itemLine, 1);
if (cell == null) {
mes.append("XX[").append(cellValueCommType).append("]的XX[").append(itemName).append("]的零售额[").append(errorLocation).append("]为空\r\n");
isExport=false;
} else{
try{
cellLSE =getCellValue(cell);
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");isExport=false;
} 
cellLSE=CharByteConverter.ToDBC(cellLSE.trim());
if(StringUtils.isEmpty(cellLSE)){
mes.append("XX[").append(cellValueCommType).append("]的XX[").append(itemName).append("]的零售额[").append(errorLocation).append("]为空\r\n");
isExport=false;
};
}

cell = sheet.getRow(itemLine).getCell(2);
String cellLSL="";
errorLocation=createErrorLocation(sheetName,itemLine, 2);
if (cell == null) {
mes.append("XX[").append(cellValueCommType).append("]的XX[").append(itemName).append("]的零售量[").append(errorLocation).append("]为空\r\n");
isExport=false;
} else{
try{
cellLSL =getCellValue(cell);
}catch(Exception e){
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");
isExport=false;
} 
cellLSL=CharByteConverter.ToDBC(cellLSL.trim());
if(StringUtils.isEmpty(cellLSL)){
mes.append("XX[").append(cellValueCommType).append("]的XX[").append(itemName).append("]的零售量[").append(errorLocation).append("]为空\r\n");
isExport=false;
}
}
}
}
} 
}

LOG.info("isExport:"+isExport+","+mes.toString());
//8.该XX有问题，不能导入，继续下一个品类
if(!isExport){
continue ;
}

//9.将Excel信息插入基本信息basic表
ReportBasicInfo bInfo = new ReportBasicInfo();
...
bInfo.save();


//10.插入data表
// 最多有多少行   将Excel中对应单元格的内容插入数据表
boolean isBreak=false;
for (int j = 6; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue ;
} else {
// 最多有多少列
DecimalFormat format = new DecimalFormat("0.00"); 
Cell cell = row.getCell(0);
String wareName=null;
try {
errorLocation=createErrorLocation(sheetName,j, 0);
wareName = getCellValue(cell);
} catch (Exception e) {
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");
}

if(StringUtils.isEmpty(wareName)){
if(isBreak){
break;
}else{
isBreak=true;
continue;
}
}
wareName=CharByteConverter.ToDBC(wareName);
List<Brand> wareitem = ShareReportService.getBrand(wareName,list.get(0).getId());
long wareid = 0;
if(wareitem.size()==1){
wareid = wareitem.get(0).getId();
}else if(wareitem.size()>1){
for(int index=0;index<wareitem.size();index++){
if(wareitem.get(index).getF_parent_id()==0){
wareid = wareitem.get(index).getId();
break;
}
}
if(wareid==0){
wareid = wareitem.get(0).getId();
}
}

for (int k = 1; k < 3; k++) {
String itemValue = "";
String tempvalue=null;
try {
errorLocation=createErrorLocation(sheetName,j, k);
tempvalue = String.valueOf(getCellValue(row.getCell(k)));
} catch (Exception e) {
e.printStackTrace();
mes.append("数据解析错误,错误位置：[").append(errorLocation).append("](请修改单元格格式为常规)\r\n");
}
tempvalue=tempvalue.replaceAll("#", "").replaceAll("￥", "").replaceAll(",", "");//replaceAll去掉￥和，如￥61,980.00变为61980.00
BigDecimal big=new BigDecimal(tempvalue);
itemValue = format.format(big);
//ID不能为0且Excel中对应单元格的内容不能为空
if (wareid != 0&&StringUtils.isNotEmpty(itemValue)) {
ShareReportService.saveTempData(bInfo.getId(), wareid, itemList.get(k-1).getId(), itemValue);
}

}
}
}
}

//11.输出提示信息
StringBuilder message = new StringBuilder();// 最后输出的信息
//根据不同情况输出提示信息
if(StringUtils.isEmpty(mes.toString())){
message.append(IMPORT_SUCCESS);//成功
}else{
message.append(IMPORT_FAIL).append(mes.toString());//失败，有数据导入失败
}


result.addProperty("success", true);
result.addProperty("message", message.toString());
is.close();
return result;
}

/**
* 获取Excel单元格的内容值
* @param cell
* @return
* @throws Exception
*/
private static String getCellValue(Cell cell) throws Exception {
String cellValue = "";
if (cell != null) {
short format = cell.getCellStyle().getDataFormat();
LOG.info("formatvalue:"+format);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
LOG.info("数字");
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
} else {
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
LOG.info(cellValue);
break;
case Cell.CELL_TYPE_STRING:// 文本49
LOG.info("字符串");
cellValue = cell.getStringCellValue();
LOG.info(cellValue);
break;
case Cell.CELL_TYPE_BOOLEAN:
LOG.info("真假值");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
LOG.info("公式值");
cellValue = String.valueOf(cell.getNumericCellValue());// 公式结果的值
break;
case Cell.CELL_TYPE_BLANK:
LOG.info("空值");
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
LOG.info("错误值");
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
return cellValue.trim();
}else{
return null;
}

}

/**
* 生成excel中错误位置
* @param sheetName
* @param row
* @param col
* @return
*/
public static String createErrorLocation(String sheetName,int row,int col){
String loc="";
loc+=sheetName+":";
char letter=(char)(65+col);//列要转为字母
loc+=letter;
loc+=row+1;//行+1为实际行
return loc;
}
}