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[]>());
}
}
