internal List <string> GetSplitValues(SplitFileParameters splitParams)
{
List <string> splitValues = new List <string>();
try
{
InitializeExcelApplication();
this.xlWorkBook = IExcelProcessor.OpenWorkbook(this.xlApp, splitParams.FilePath, true);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[splitParams.SheetIndex];
object val = string.Empty;
for (int i = splitParams.RowBegin; i <= splitParams.RowEnd; i++)
{
val = xlWorkSheet.Range[splitParams.ColumnSplit + i].Value;
if (val != null)
{
string value = val.ToString().ToUpper().Trim();
if (value?.Length != 0)
{
splitValues.Add(value);
}
}
}
xlWorkBook.Close();
ExitExcelApplication();
splitValues = splitValues.Distinct().ToList();
splitValues.Sort();
return(splitValues);
}
catch
{
if (xlWorkBook != null)
{
xlWorkBook.Close();
}
if (xlApp != null)
{
ExitExcelApplication();
}
throw;
}
}
internal ExcelFileInfo ReadExcelFileInfo(string fileName)
{
try
{
InitializeExcelApplication();
this.xlWorkBook = IExcelProcessor.OpenWorkbook(this.xlApp, fileName, true);
List <ExcelSheet> sheets = new List <ExcelSheet>();
Excel.Sheets xlWorkSheets = xlWorkBook.Sheets;
foreach (Excel.Worksheet sheet in xlWorkSheets)
{
sheets.Add(GetSheetInfo(sheet));
}
ExcelFileInfo result = new ExcelFileInfo(fileName, sheets);
xlWorkBook.Close();
ExitExcelApplication();
return(result);
}
catch
{
if (xlWorkBook != null)
{
xlWorkBook.Close();
}
if (xlApp != null)
{
ExitExcelApplication();
}
throw;
}
}
internal void SplitExcelFile(object sender, DoWorkEventArgs e)
{
BackgroundWorker bw = sender as BackgroundWorker;
List <string> splitValues = e.Argument as List <string>;
string fileExt = System.IO.Path.GetExtension(Parameters.FilePath);
string filename = System.IO.Path.GetFileNameWithoutExtension(Parameters.FilePath);
string fileDir = System.IO.Path.GetDirectoryName(Parameters.FilePath);
try
{
InitializeExcelApplication();
e.Result = this;
object val = string.Empty;
foreach (string splitVal in splitValues)
{
if (bw.CancellationPending)
{
e.Cancel = true;
break;
}
this.xlWorkBook = IExcelProcessor.OpenWorkbook(this.xlApp, this.Parameters.FilePath, true);
this.xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[Parameters.SheetIndex];
for (int i = Parameters.RowEnd; i >= Parameters.RowBegin; i--)
{
val = xlWorkSheet.Range[Parameters.ColumnSplit + i].Value;
if (val == null || val.ToString().ToUpper().Trim() != splitVal)
{
((Excel.Range)xlWorkSheet.Rows[i, System.Reflection.Missing.Value]).Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
bw.ReportProgress(0);
}
xlWorkBook.SaveAs(fileDir + @"/" + ParseStringToFileName(filename + "_" + splitVal) + fileExt);
xlWorkBook.Close();
bw.ReportProgress(0, splitVal);
}
ExitExcelApplication();
}
catch
{
if (xlWorkBook != null)
{
xlWorkBook.Close();
}
if (xlApp != null)
{
ExitExcelApplication();
}
throw;
}
finally
{
ReleaseUnmanaged();
}
}
