I have created a WPF application that has a graphical user interface that allows users to upload Excel files. These files are supposed to be stored in a designated SQL Server database. I would like each file to be stored as its own table and for the columns to match up with the columns in the file. However, the files aren't even saving to the database. The error messages are working. It tells me that there is no data in the worksheet in the file or the worksheet can't be located, but I know that the file that I am uploading has data and a worksheet in it.
To Select the File -- This part works. I am able to select file(s) in the GUI.
private void Button_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog() { Multiselect = true };
bool? response = openFileDialog.ShowDialog();
if (response == true)
{
//Get selected files
string[] files = openFileDialog.FileNames;
UploadFiles(files);
}
}
To Upload the File -- Not uploading into SQL
private void UploadFiles(string[] files)
{
//Iterate and add all selected files to upload
foreach (string filePath in files)
{
string filename = System.IO.Path.GetFileName(filePath);
FileInfo fileInfo = new FileInfo(filePath);
// Read Excel data into a DataTable
DataTable excelData = ReadExcelFile(filePath);
if (excelData != null)
{
// Get the table name from the file name or any other logic you prefer
string tableName = Path.GetFileNameWithoutExtension(filePath);
// Create the table in the SQL Server database
CreateTable(tableName, excelData);
UploadingFilesList.Items.Add(new fileDetail()
{
FileName = filename,
// To convert bytes to Mb
FileSize = string.Format("{0} {1}", (fileInfo.Length / 1.049e+6).ToString("0.0"), "Mb"),
UploadProgress = 100
});
MessageBox.Show("File uploaded and stored in the database.");
}
else
{
MessageBox.Show("Unable to read Excel file.");
}
}
}
Read the File -- Not acknowledging that there is data or a worksheet
private DataTable ReadExcelFile(string filePath)
{
try
{
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
ExcelRangeBase range = worksheet.Cells[worksheet.Dimension.Address];
DataTable dataTable = new DataTable();
foreach (var cell in range)
{
if (cell.Start.Row == 1)
{
dataTable.Columns.Add(cell.Text);
}
else
{
DataRow dataRow = dataTable.NewRow();
for (int col = 1; col <= range.Columns; col++)
{
dataRow[col - 1] = worksheet.Cells[cell.Start.Row, col].Value;
}
dataTable.Rows.Add(dataRow);
}
}
return dataTable;
}
}
catch (Exception ex)
{
MessageBox.Show("Error occurred while reading Excel file: " + ex.Message);
return null;
}
}
Create the Table -- Not creating tables
private void CreateTable(string tableName, DataTable tableData)
{
string connectionString = "Connection"; //I have my real connection in the code
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Create the SQL CREATE TABLE statement
string createTableQuery = "CREATE TABLE " + tableName + " (";
foreach (DataColumn column in tableData.Columns)
{
createTableQuery += "[" + column.ColumnName + "] VARCHAR(MAX), ";
}
createTableQuery = createTableQuery.TrimEnd(',', ' ') + ")";
using (SqlCommand command = new SqlCommand(createTableQuery, connection))
{
command.ExecuteNonQuery();
}
// Bulk insert data into the newly created table
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(tableData);
}
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Error occurred while creating table and storing data: " + ex.Message);
}
}
}
}
UPDATE I have added the following to my code to run diagnostics to see where the error occurs:
using System.Diagnostics;
catch (Exception ex)
{
// Log the error to the Output window in Visual Studio
Debug.WriteLine("Error occurred while reading Excel file: " + ex.Message);
return null;
}
The output is as follows:
"Exception thrown: 'System.IndexOutOfRangeException' in EPPlus.dll Error occurred while reading Excel file: Worksheet position out of range."
