ERROR:The command could not be completed by using the range specified. Select a single cell within the range and try the command again.
string str = "";
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
//app.Visible = true;
try
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
gvPEC.AllowPaging = false;
//Export Data Into Excel
for (int i = 1; i < gvPEC.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = gvPEC.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gvPEC.Rows.Count; i++)
{
int l = gvPEC.Rows.Count;
for (int j = 0; j < gvPEC.Columns.Count; j++)
{
if (gvPEC.Rows[i].Cells[j].Text != " ")
{
worksheet.Cells[i + 2, j + 1] = gvPEC.Rows[i].Cells[j].Text;
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
//Freeze first Panes
if (i == 3)
{
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[1];
firstRow.Activate();
firstRow.Select();
//firstRow.AutoFilter(1, Type.Missing, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
app.DisplayAlerts = false;
}
}
//app.Visible = true;
//Merging duplicate Data
Microsoft.Office.Interop.Excel.Range firstColumn = (Microsoft.Office.Interop.Excel.Range)worksheet.Columns[1];
string OldVal = ((Microsoft.Office.Interop.Excel.Range)firstColumn.Cells[2]).Value.ToString();
int StartCell = 2;
int EndCell = 0;
for (int k = 2; k <= worksheet.UsedRange.Rows.Count; k++)
{
string val = ((Microsoft.Office.Interop.Excel.Range)firstColumn.Cells[k]).Value.ToString();
if (OldVal == val)
{
EndCell = k;
OldVal = ((Microsoft.Office.Interop.Excel.Range)firstColumn.Cells[k]).Value.ToString();
continue;
}
if (StartCell != 0 && EndCell != 0)
{
((Microsoft.Office.Interop.Excel.Range)worksheet.Range["A" + StartCell + ":A" + EndCell]).Merge();
StartCell = EndCell + 1;
OldVal = ((Microsoft.Office.Interop.Excel.Range)firstColumn.Cells[StartCell]).Value.ToString();
k = StartCell;
// break;
}
if (OldVal == null)
break;
}
((Microsoft.Office.Interop.Excel.Range)worksheet.Range["A" + StartCell + ":A" + EndCell]).Merge();
string fileName = "CourseMaster";
app.Visible = true;
workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
catch (System.Exception ex)
{
}
finally
{
//app.Quit();
//System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
//workbook = null;
//app = null;
}