package com.inspur.icpmg.itss.asset.dao.impl;
import com.inspur.icpmg.util.DBHelper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.io.*;
/**
* Create by wjup on 2019/5/31 17:00
*/
public class ImportExcel {
/**
* 上传的excel文件和文件名称
*
* form表单上传框name为acUpload
* acUploadFileName:xxFileName格式自动获取上传的文件名
*
*/
private File acUpload;
private String acUploadFileName;
public String getAcUploadFileName() {
return acUploadFileName;
}
public void setAcUploadFileName(String acUploadFileName) {
this.acUploadFileName = acUploadFileName;
}
public File getAcUpload() {
return acUpload;
}
public void setAcUpload(File acUpload) {
this.acUpload = acUpload;
}
private static JdbcTemplate jdbcTemplate = new JdbcTemplate(DBHelper.getDataSource());// 数据库连接池
public void file() throws IOException {
InputStream in = new FileInputStream(acUpload);
if (acUploadFileName.endsWith("xls") || acUploadFileName.endsWith("xlsx")) {
Workbook wb = null;
if (acUploadFileName.endsWith("xls")) {
//2003
wb = new HSSFWorkbook(in);
} else if (acUploadFileName.endsWith("xlsx")) {
//2007
wb = new XSSFWorkbook(in);
}
// 获取excel表第一张sheet页
Sheet sheet1 = wb.getSheetAt(0);
importExcel(sheet1);
}
}
public void importExcel(Sheet sheet) {
//添加事务
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(jdbcTemplate.getDataSource());
TransactionStatus status = transactionManager.getTransaction(def);
Boolean Iscommit = false;
String name = "";
for (Row row : sheet) {
if (row.getRowNum() >= 1) {
boolean flag = false;
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
if (row.getCell(c) != null && row.getCell(c).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
flag = true;
}
}
if (flag) {
if (row.getCell(0) != null) {
name = row.getCell(0).toString().trim();
} else {
String msg = "不能为空";
Iscommit = true;
}
}
}
}
String sql = "insert into user values("+name+")";
if (Iscommit) {
transactionManager.rollback(status);
} else {
try {
// 出现异常回滚事务
jdbcTemplate.batchUpdate(sql);
} catch (Exception ex) {
transactionManager.rollback(status);
} finally {
transactionManager.commit(status);
}
}
}
}