【SpringBoot】自定义工具类实现Excel数据新建表存入MySQL数据库

以下是一个简化的代码示例，展示了如何在Spring Boot应用程序中使用Apache POI读取Excel文件，并将数据存储到MySQL数据库中。

import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
 
import javax.sql.DataSource;
import java.sql.*;
 
@Service
public class ExcelToMySQLService {
 
    @Autowired
    private DataSource dataSource;
 
    @Transactional
    public void importExcelData(MultipartFile file) throws Exception {
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
 
        Connection connection = null;
        PreparedStatement preparedStatement = null;
 
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
 
            preparedStatement = connection.prepareStatement("INSERT INTO your_table (column1, column2) VALUES (?, ?)");
 
            for (Row row : sheet) {
                int columnIndex = 0;
                for (Cell cell : row) {
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            preparedStatement.setString(++columnIndex, cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            preparedStatement.setDouble(++columnIndex, cell.getNumericCellValue());
                            break;
                        // Handle other cell types if needed
                        default:
                            break;
                    }
                }
                preparedStatement.executeUpdate();
            }
 
            connection.commit();
 
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null)