Thanks Shakeel.
I tried your build and followed the code changes suggested, but did not work. Here is my code block
void SeperateWorkSheets(int columnId, ITOFileFormat fileFormat)
{
string prevWsName = string.Empty;
string currWsName = string.Empty;
int worksheetIdx = -1;
Dictionary<string, string> worksheetNames = new Dictionary<string, string>();
Dictionary<string, int> worksheetRows = new Dictionary<string, int>();
<span style="color: blue;">for</span> (<span style="color: blue;">int</span> wsIdx = 0; wsIdx < oITOExcelWorkBook.Worksheets.Count; wsIdx++)
{
<span style="color: blue;">int</span> maxRow = oITOExcelWorkBook.Worksheets[wsIdx].Cells.MaxDataRow;
<span style="color: blue;">if</span> (maxRow < oITOExcelWorkBook.Worksheets[wsIdx].Cells.MaxRow)
maxRow = oITOExcelWorkBook.Worksheets[wsIdx].Cells.MaxRow;
maxRow++;
<span style="color: blue;">for</span> (<span style="color: blue;">int</span> rowIdx = 0; rowIdx < maxRow; rowIdx++)
{
currWsName = oITOExcelWorkBook.Worksheets[wsIdx].Cells[rowIdx, columnId].StringValue;
<span style="color: blue;">if</span> (prevWsName != currWsName)
{
worksheetNames[currWsName] = currWsName;
worksheetRows[currWsName + <span style="color: rgb(163, 21, 21);">"[START]"</span>] = rowIdx;
<span style="color: blue;">if</span> (rowIdx > 0)
{
worksheetRows[prevWsName + <span style="color: rgb(163, 21, 21);">"[END]"</span>] = rowIdx - 1;
worksheetNames[prevWsName] = prevWsName;
}
prevWsName = currWsName;
}
}
worksheetRows[currWsName + <span style="color: rgb(163, 21, 21);">"[END]"</span>] = oITOExcelWorkBook.Worksheets[wsIdx].Cells.MaxDataRow;
}
<span style="color: green;">/* Create the new Worksheets */</span>
<span style="color: blue;">int</span> tempWsId;
<span style="color: blue;">foreach</span> (<span style="color: blue;">string</span> key <span style="color: blue;">in</span> worksheetNames.Keys)
{
oTargetExcelWorkBook.Worksheets.Add(key);
<span style="color: green;">// tempWsId = oTargetExcelWorkBook.Worksheets.Add(key);</span>
<span style="color: green;">/*</span>
if (worksheetIdx < 0)
worksheetIdx = tempWsId - 1;
if (oTargetExcelWorkBook.Worksheets[key] != null)
oTargetExcelWorkBook.Worksheets[key].Name = (key + new Guid().ToString()).Substring(0, 30);
*/
// oTargetExcelWorkBook.Worksheets[tempWsId].Name = key;
}
<span style="color: green;">/* Split them */</span>
<span style="color: blue;">foreach</span> (<span style="color: blue;">string</span> key <span style="color: blue;">in</span> worksheetNames.Keys)
{
<span style="color: blue;">int</span> startRow = worksheetRows[key + <span style="color: rgb(163, 21, 21);">"[START]"</span>];
<span style="color: blue;">int</span> endRow = worksheetRows[key + <span style="color: rgb(163, 21, 21);">"[END]"</span>];
<span style="color: blue;">for</span> (<span style="color: blue;">int</span> wsIdx = 0; wsIdx < oITOExcelWorkBook.Worksheets.Count; wsIdx++)
{
<span style="color: green;">//if (oITOExcelWorkBook.Worksheets[wsIdx].Name != key)</span>
<span style="color: green;">//{</span>
<span style="color: blue;">if</span> (oITOExcelWorkBook.Worksheets[wsIdx].Cells[startRow, columnId].Value == key)
{
<span style="color: rgb(43, 145, 175);">Worksheet</span> destSheet = oTargetExcelWorkBook.Worksheets[key];
<span style="color: rgb(43, 145, 175);">Worksheet</span> sourceSheet = oITOExcelWorkBook.Worksheets[wsIdx];
<span style="color: rgb(43, 145, 175);">Range</span> sourceRange;
<span style="color: green;">//= sourceSheet.Cells.CreateRange(startRow, columnId + 1, (endRow - startRow) + 1, sourceSheet.Cells.MaxDataColumn);</span>
sourceRange = sourceSheet.Cells.MaxDisplayRange;
<span style="color: rgb(43, 145, 175);">Range</span> destRange;
<span style="color: green;">//= destSheet.Cells.CreateRange(0, 0, (endRow - startRow) + 1, sourceSheet.Cells.MaxDataColumn);</span>
destRange = destSheet.Cells.CreateRange(0, 0, sourceRange.RowCount, sourceRange.ColumnCount);
sourceRange.CopyStyle(destRange);
destRange.CopyData(sourceRange);
<span style="color: green;">// destRange.Worksheet.AutoFitColumns(0, 0, (endRow - startRow) + 1, sourceSheet.Cells.MaxDataColumn);</span>
destRange.Worksheet.AutoFitColumns(0, 0, sourceRange.RowCount, sourceRange.ColumnCount);
}
<span style="color: green;">//}</span>
}
}
<span style="color: green;">/* Now Remove the Original Ones * /</span>
for (int wsIdx = worksheetIdx; wsIdx >= 0; wsIdx–)
oITOExcelWorkBook.Worksheets.RemoveAt(wsIdx);
/* Remove the Extra Column * /
for (int wsIdx = 0; wsIdx < oITOExcelWorkBook.Worksheets.Count; wsIdx++)
try { oITOExcelWorkBook.Worksheets[wsIdx].Cells.Columns.RemoveAt(columnId); }
catch { }
*/
<span style="color: green;">/* Process the File Format */</span>
<span style="color: blue;">if</span> (fileFormat != <span style="color: blue;">null</span>)
{
<span style="color: rgb(43, 145, 175);">Worksheet</span> styleSheet;
<span style="color: rgb(43, 145, 175);">Range</span> styleRange;
<span style="color: rgb(43, 145, 175);">Style</span> style;
<span style="color: rgb(43, 145, 175);">StyleFlag</span> styleFlag;
<span style="color: blue;">foreach</span> (<span style="color: rgb(43, 145, 175);">ITOFileFormatStyle</span> itoFileFormatStyle <span style="color: blue;">in</span> fileFormat.StyleItems.Values)
{
styleFlag = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">StyleFlag</span>();
styleFlag.All = <span style="color: blue;">false</span>;
styleSheet = <span style="color: blue;">null</span>;
<span style="color: blue;">try</span>
{
styleSheet = oTargetExcelWorkBook.Worksheets[itoFileFormatStyle.WORKSHEETNAME];
<span style="color: blue;">if</span> (styleSheet != <span style="color: blue;">null</span>)
{
styleRange = styleSheet.Cells.CreateRange(itoFileFormatStyle.RangeStartRow, itoFileFormatStyle.RangeStartCol, itoFileFormatStyle.RangeTotalRows, itoFileFormatStyle.RangeTotalCols);
style = oTargetExcelWorkBook.Styles[oTargetExcelWorkBook.Styles.Add()];
<span style="color: green;">/* We need this for the BG Color */</span>
style.Pattern = <span style="color: rgb(43, 145, 175);">BackgroundType</span>.Solid;
<span style="color: green;">/* BGCOLOR */</span>
<span style="color: blue;">if</span> (itoFileFormatStyle.BGColorValue != Color.Empty)
{
styleFlag.CellShading = <span style="color: blue;">true</span>;
style.ForegroundColor = itoFileFormatStyle.BGColorValue;
style.BackgroundColor = itoFileFormatStyle.BGColorValue;
}
<span style="color: green;">/* FORECOLOR */</span>
<span style="color: blue;">if</span> (itoFileFormatStyle.ForeColorValue != Color.Empty)
{
styleFlag.FontColor = <span style="color: blue;">true</span>;
style.Font.Color = itoFileFormatStyle.ForeColorValue;
}
<span style="color: green;">/* BORDER */</span>
<span style="color: blue;">if</span> (itoFileFormatStyle.TopBorderValue != <span style="color: rgb(43, 145, 175);">CellBorderType</span>.None)
{
styleFlag.TopBorder = <span style="color: blue;">true</span>;
style.Borders[<span style="color: rgb(43, 145, 175);">BorderType</span>.TopBorder].LineStyle = itoFileFormatStyle.TopBorderValue;
}
<span style="color: blue;">if</span> (itoFileFormatStyle.LeftBorderValue != <span style="color: rgb(43, 145, 175);">CellBorderType</span>.None)
{
styleFlag.LeftBorder = <span style="color: blue;">true</span>;
style.Borders[<span style="color: rgb(43, 145, 175);">BorderType</span>.LeftBorder].LineStyle = itoFileFormatStyle.LeftBorderValue;
}
<span style="color: blue;">if</span> (itoFileFormatStyle.BottomBorderValue != <span style="color: rgb(43, 145, 175);">CellBorderType</span>.None)
{
styleFlag.BottomBorder = <span style="color: blue;">true</span>;
style.Borders[<span style="color: rgb(43, 145, 175);">BorderType</span>.BottomBorder].LineStyle = itoFileFormatStyle.BottomBorderValue;
}
<span style="color: blue;">if</span> (itoFileFormatStyle.RightBorderValue != <span style="color: rgb(43, 145, 175);">CellBorderType</span>.None)
{
styleFlag.RightBorder = <span style="color: blue;">true</span>;
style.Borders[<span style="color: rgb(43, 145, 175);">BorderType</span>.RightBorder].LineStyle = itoFileFormatStyle.RightBorderValue;
}
<span style="color: green;">/* FORMAT */</span>
<span style="color: blue;">if</span> (itoFileFormatStyle.FormatValue < 0 && !<span style="color: blue;">string</span>.IsNullOrEmpty(itoFileFormatStyle.FORMAT))
style.Custom = itoFileFormatStyle.FORMAT;
style.Number = itoFileFormatStyle.FormatValue;
<span style="color: blue;">if</span> (itoFileFormatStyle.FormatValue != 0 && !<span style="color: blue;">string</span>.IsNullOrEmpty(itoFileFormatStyle.FORMAT))
styleFlag.NumberFormat = <span style="color: blue;">true</span>;
styleRange.ApplyStyle(style, styleFlag);
}
}
<span style="color: blue;">catch</span> (<span style="color: rgb(43, 145, 175);">Exception</span> ex)
{ LogException(ex); }
}
}
<span style="color: green;">/* Worksheet Autofitcolumn */</span>
<span style="color: blue;">for</span> (<span style="color: blue;">int</span> wsIdx = 0; wsIdx < oTargetExcelWorkBook.Worksheets.Count; wsIdx++)
{
<span style="color: blue;">try</span> { oTargetExcelWorkBook.Worksheets[wsIdx].AutoFitColumns(); }
<span style="color: blue;">catch</span> { }
}
<span style="color: green;">/* Error Check Option */</span>
<span style="color: blue;">for</span> (<span style="color: blue;">int</span> wsIdx = 0; wsIdx < oTargetExcelWorkBook.Worksheets.Count; wsIdx++)
{
<span style="color: blue;">try</span> {
<span style="color: rgb(43, 145, 175);">ErrorCheckOptionCollection</span> opts = oTargetExcelWorkBook.Worksheets[wsIdx].ErrorCheckOptions;
<span style="color: blue;">int</span> optionIdx = opts.Add();
<span style="color: rgb(43, 145, 175);">ErrorCheckOption</span> opt = opts[optionIdx];
opt.SetErrorCheck(Aspose.Cells.<span style="color: rgb(43, 145, 175);">ErrorCheckType</span>.InconsistFormula, <span style="color: blue;">false</span>);
optionIdx = opts.Add();
opt = opts[optionIdx];
opt.SetErrorCheck(Aspose.Cells.<span style="color: rgb(43, 145, 175);">ErrorCheckType</span>.InconsistRange, <span style="color: blue;">false</span>);
optionIdx = opts.Add();
opt = opts[optionIdx];
opt.SetErrorCheck(Aspose.Cells.<span style="color: rgb(43, 145, 175);">ErrorCheckType</span>.TextDate, <span style="color: blue;">false</span>);
optionIdx = opts.Add();
opt = opts[optionIdx];
opt.SetErrorCheck(Aspose.Cells.<span style="color: rgb(43, 145, 175);">ErrorCheckType</span>.TextNumber, <span style="color: blue;">false</span>);
optionIdx = opts.Add();
opt = opts[optionIdx];
opt.SetErrorCheck(Aspose.Cells.<span style="color: rgb(43, 145, 175);">ErrorCheckType</span>.Validation, <span style="color: blue;">false</span>);
}
<span style="color: blue;">catch</span> { }
}
}
}
v