上传excel文件，读取内容，增加事务写入数据库

曾经终败给现在 2022-01-23 22:31 261阅读 0赞

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);
                }
            }
        }
    
    
    }