I have a WPF App that allows the user to choose one to many spreadsheets and performs a process to clean the data and write out a pipe delimited files. I am trying to use a Background Worker in order to display a progress bar. The problem I am having is that I have the backgroundworker_DoWork routine to perform the read the excel file, clean the data and write out the data. For each spreadsheet the user is allowed to specify the range for the data or to let Excel decided the range. I call a dialog for this input. I get the error "The calling thread must be STA, because many UI components require this." I have been trying to find a fix for this but everything I try doesn't work.
Here is my code. I have put comments where the offending code is and what I have tried so far.
private void ProcessFiles()
{
int intNumFiles = 0;
string strFileType = "";
int intPos = 0;
string strMsg = "";
intNumFiles = strExFileNames.Length;
foreach (string strInputFile in strExFileNames)
{
intPos = strInputFile.LastIndexOf(".");
strFileType = strInputFile.Substring(intPos + 1);
if(!blnEXTextFileFound)
{
strExcelInputFile = strInputFile;
if (blnExParamCancel || blnMWCancel)
{
strMsg = "Processing has been cancelled.";
System.Windows.MessageBox.Show(strMsg);
break;
}
prgExcelProgress.Visibility = Visibility.Visible;
txtExcelPercent.Visibility = Visibility.Visible;
EnterExcelStateRunning();
try
{
bgwExcelRunner.RunWorkerAsync(strInputFile);
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("Trying to run the Excel/Text file clean process resulted in this error: " + ex.Message, " Error");
prgExcelProgress.Foreground = new SolidColorBrush(Colors.DarkRed);
prgExcelProgress.Value = 100;
}
//ReadWriteExcelData(strInputFile);
}
else
{
if (blnEXTextFileFound)
{
ProcessDelimitedFile(strInputFile);
}
else
{
strMsg = "File type " + strFileType + " is not supported.";
strMsg += " Please contact support.";
System.Windows.MessageBox.Show(strMsg);
}
}
}
prgExcelProgress.Value = 100;
//prgIndicator.Width = 400;
//lblPrctPrgrs.Content = "100%";
//grdProgressIndicator.InvalidateVisual();
//System.Windows.Forms.Application.DoEvents();
//Thread.Sleep(2000);
//txtIndicator.Text = "All files have been processed and created in " + strExOutputPath + ".";
blnDoForAll = false;
}
private void bgwExcelRunner_DoWork(object sender, DoWorkEventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range, colrange, rowrange;
string strCellData, strMsg;
string strDataRow = "";
long lngNumRows, lngNumCols = 0;
long intModNumber, lngProgressPct = 0;
double dblProgress = 0;
double dblProgressPct = 0;
string strOutputFileName = "";
int intPos, intProgress = 0;
string strSubFileName = "";
string strFullOutputFileName = "";
string strOutName = "";
long lngColCnt;
string[] strWSNames = new string[0];
Type typCellType;
bool blnMultiWS = false;
string strNumberFormat;
string strFileName = (string)e.Argument;
//string strFileName = (string)((object[])e.Argument)[0];
intPos = strFileName.IndexOf(".");
strSubFileName = strFileName.Substring(0, intPos);
strOutputFileName = strFileName.Substring(strFileName.LastIndexOf("\\") + 1);
strOutName = strOutputFileName.Substring(0, strOutputFileName.IndexOf("."));
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(strFileName, 0, true, 5, "", "", true,
Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Sheets excelSheets = xlWorkBook.Worksheets;
if (excelSheets.Count > 1)
{
blnMultiWS = CheckMultipleWorksheets(excelSheets);
if (!blnMultiWS)
{
Array.Clear(strChoosenNames, 0, strChoosenNames.Length);
Array.Resize(ref strChoosenNames, 1);
strChoosenNames[0] = strOutName;
}
}
else
{
Array.Resize(ref strChoosenNames, 1);
strChoosenNames[0] = strOutName;
}
if (blnMWCancel)
{
strMsg = "Processing has been cancelled.";
System.Windows.MessageBox.Show(strMsg);
goto ReadWriteExcelDataExit;
}
foreach (string strCurrWSName in strChoosenNames)
{
//grdProgressIndicator.Visibility = Visibility.Visible;
//txtIndicator.Text = "File: " + strCurrWSName;
//prgIndicator.Width = 0;
//lblPrctPrgrs.Content = "0%";
//System.Windows.Forms.Application.DoEvents();
//txtIndicator.Text = " Processing File: " + strCurrWSName + ". Please wait...";
strFullOutputFileName = strExOutputPath + "\\" + strCurrWSName + "_duc.txt";
//if (strChoosenNames.Length > 1)
//{
// xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[strCurrWSName];
//}
//else
//{
// xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//}
try
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[strCurrWSName];
}
catch (Exception exQuery)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
if (!blnDoForAll)
{
// I ALSO TRIED TO USE A THREAD. THIS SEEMS TO WORK EXCEPT THAT IT PROCESSING DOESN'T RUN IN THE FOREGROUND SO IT FALLS TO THE CODE AFTER BEFORE THE DIALOG IS DISPLAYED
//Thread thread = new Thread(() =>
//{
// ExcelRowColInfo ercWin = new ExcelRowColInfo(xlWorkSheet.Name);
// ercWin.Left = System.Windows.Application.Current.MainWindow.Left + 15;
// ercWin.Top = System.Windows.Application.Current.MainWindow.Top + 15;
// ercWin.ShowDialog();
// blnExParamCancel = ercWin.blnExCancel;
// strExcelStartCol = ercWin.strStartCol;
// strExcelEndCol = ercWin.strEndCol;
// lngExcelStartRow = ercWin.lngStartRow;
// lngExcelEndRow = ercWin.lngEndRow;
// blnLetExcelDecide = ercWin.blnExcelDecide;
// blnDoForAll = ercWin.blnDoForAll;
//});
//thread.SetApartmentState(ApartmentState.STA);
//thread.IsBackground = false;
//thread.Start();
// THIS IS ONE ATTEMPT TO USE A DISPATCHER, STILL GET THE STA ERROR
//ExcelRowColInfo ercWin = new ExcelRowColInfo(xlWorkSheet.Name);
//ercWin.Left = System.Windows.Application.Current.MainWindow.Left + 15;
//ercWin.Top = System.Windows.Application.Current.MainWindow.Top + 15;
//ercWin.Dispatcher.BeginInvoke
// (
// System.Windows.Threading.DispatcherPriority.Normal,
// (Action)(() =>
// {
// ercWin.ShowDialog();
// blnExParamCancel = ercWin.blnExCancel;
// }
// )
// );
// THIS IS WHERE THE ERROR OCCURS
ExcelRowColInfo ercWin = new ExcelRowColInfo(xlWorkSheet.Name);
ercWin.Left = System.Windows.Application.Current.MainWindow.Left + 15;
ercWin.Top = System.Windows.Application.Current.MainWindow.Top + 15;
ercWin.ShowDialog();
blnExParamCancel = ercWin.blnExCancel;
if (blnExParamCancel)
{
prgExcelProgress.Foreground = new SolidColorBrush(Colors.LightPink);
Dispatcher.BeginInvoke((Action)delegate
{
prgExcelProgress.Value = 100;
txtExcelPercent.Text = "Process has been cancelled";
});
blnExParamCancel = false;
goto ReadWriteExcelDataExit;
}
strExcelStartCol = ercWin.strStartCol;
strExcelEndCol = ercWin.strEndCol;
lngExcelStartRow = ercWin.lngStartRow;
lngExcelEndRow = ercWin.lngEndRow;
blnLetExcelDecide = ercWin.blnExcelDecide;
blnDoForAll = ercWin.blnDoForAll;
if (blnLetExcelDecide)
{
range = xlWorkSheet.UsedRange;
}
else
{
Excel.Range c1 = xlWorkSheet.Cells[lngExcelStartRow, strExcelStartCol];
Excel.Range c2 = xlWorkSheet.Cells[lngExcelEndRow, strExcelEndCol];
range = (Excel.Range)xlWorkSheet.get_Range(c1, c2);
}
colrange = range.Columns;
lngNumCols = colrange.Count;
rowrange = range.Rows;
lngNumRows = rowrange.Count;
if (lngNumRows < 10)
{
intModNumber = lngNumRows;
}
else
{
intModNumber = lngNumRows / 10;
}
if (System.IO.File.Exists(@strFullOutputFileName))
{
System.IO.File.Delete(@strFullOutputFileName);
}
object[,] values = (object[,])range.Value;
long NumRow = 1;
using (StreamWriter file = new StreamWriter(@strFullOutputFileName, true, Encoding.GetEncoding("iso-8859-1")))
{
while (NumRow <= values.GetLength(0))
{
strDataRow = "";
for (lngColCnt = 1; lngColCnt <= lngNumCols; lngColCnt++)
{
if (values[NumRow, lngColCnt] == null)
{
typCellType = typeof(System.String);
}
else
{
typCellType = values[NumRow, lngColCnt].GetType();
}
strCellData = Convert.ToString(values[NumRow, lngColCnt]);
if (typCellType == typeof(System.DateTime))
{
strCellData = strCellData.Substring(0, strCellData.IndexOf(" "));
}
else
{
if (typCellType == typeof(System.Decimal))
{
if (Convert.ToDecimal(values[NumRow, lngColCnt]) == 0)
{
strCellData = Convert.ToString(0);
}
else
{
strCellData = Convert.ToString(values[NumRow, lngColCnt]);
}
}
else
{
if (typCellType != typeof(System.String))
{
strNumberFormat = range[NumRow, lngColCnt].NumberFormat.ToString();
if (strNumberFormat != "General" && strNumberFormat != "Text" && strNumberFormat != "@")
{
if (typCellType == typeof(System.Double))
{
if (strNumberFormat.IndexOf("[Red]") > 0)
{
strCellData = Convert.ToString(range[NumRow, lngColCnt].Value2);
}
else
{
double dblCellValue = double.Parse(strCellData);
strCellData = dblCellValue.ToString(strNumberFormat);
}
}
}
}
}
}
if (strCellData == null)
{
strCellData = string.Empty;
}
else
{
strCellData = strCellData.Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
}
if (lngColCnt == lngNumCols)
{
strDataRow += strCellData;
}
else
{
strDataRow += strCellData + "|";
}
}
file.WriteLine(strDataRow);
if (NumRow % intModNumber == 0)
{
lngProgressPct = (NumRow / lngNumRows);
bgwExcelRunner.ReportProgress((int)lngProgressPct);
}
NumRow++;
}
}
releaseObject(xlWorkSheet);
}
xlWorkBook.Close(false, null, null);
ReadWriteExcelDataExit:
blnMWCancel = false;
xlApp.Quit();
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
Here is the entry point for the ExcelRowColInfo window.
public ExcelRowColInfo(string strWSName)
{
InitializeComponent();
strCurrWSName = string.Copy(strWSName);
InitializeExcelParams();
}
I tried to put [STAThread] above this but get the error "Attributre 'STAThread' is not valid on this declaration type. It is only valid on 'Method' declaration.
How do I do this correctly?
