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