public static SheetSelection Open(List <string> sheets) { var x = new SheetSelection(); sheets.ForEach(s => x.SheetList.Items.Add(s)); x.TopLevel = true; return(x); }
public static void GetArrayFromFile(string filename, Action<List<string[]>> action) { Sheets objSheets; _Worksheet objSheet; Range range; var xlApp = new Excel.Application(); var objBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; Action<string> next = (sheet_name) => { var count = objSheets.Cast<_Worksheet>().Count(); objSheet = new Worksheet(); for (int i = 1; i <= count; i++) { var x = (_Worksheet)objSheets.get_Item(i); if (x.Name == sheet_name) objSheet = x; } range = objSheet.UsedRange; //Retrieve the data from the range. Object[,] saRet; saRet = (System.Object[,])range.get_Value(Missing.Value); //Determine the dimensions of the array. long iRows; long iCols; iRows = saRet.GetUpperBound(0); iCols = saRet.GetUpperBound(1); //Build a string that contains the data of the array. List<string[]> data = new List<string[]>(); for (int rowCounter = 1; rowCounter <= iRows; rowCounter++) { var row = new string[iCols]; for (int colCounter = 1; colCounter <= iCols; colCounter++) { //Write the next value into the string. row[colCounter - 1] = (saRet[rowCounter, colCounter] ?? "").ToString(); } //Write in a new line. data.Add(row); } MessageBox.Show("Returning data for Sheet--> " + objSheet.Name); action?.Invoke(data); }; try { try { var listofnames = objSheets.Cast<_Worksheet>().Select(x => x.Name).ToList(); var temp = SheetSelection.Open(listofnames); temp.Selected += (s, e) => { next.Invoke(s as string); }; temp.ShowDialog(); } catch (Exception theException) { MessageBox.Show(theException.Message, "Missing Workbook?"); //You can't automate Excel if you can't find the data you created, so //leave the subroutine. action?.Invoke(new List<string[]>()); } } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); action?.Invoke(new List<string[]>()); } }