Why i can not copy a worksheet from a excel to another excel?


#1

My code is here,can you check it for me?
thanks !
public void InitiFile(Excel excel,string tempfile,string stempid)
{
Worksheet sheet=getTemplateSheet(tempfile,stempid);
Cells cells = sheet.Cells;
string fName=sheet.Name;
int index=excel.Worksheets.Add();
excel.Worksheets[index].Copy(sheet);
Worksheets sheets=excel.Worksheets;
sheets[0].Name=fName+"(1)";
this.FillSheet(sheets[0],opid);
//excel.Save(resultfile,FileFormatType.Default);

}
public Worksheet getTemplateSheet(string tempfilename,string stempid)
{
Excel excel=new Excel();
excel.Open(tempfilename);
Worksheet sheet=null;
switch (stempid)
{
case "9":
sheet=excel.Worksheets["Upper HF Welding Process"];
break;
case "2":
sheet=excel.Worksheets["Skiving Instruction"];
break;
case "10":
goto case "9";
}
DeleteTempletSheet(excel.Worksheets,sheet.Name);
return excel.Worksheets[0];
}

public void DeleteTempletSheet(Worksheets sheets,string sheetName)
{
int index=sheets.Count;
while(index > 0)
{
Worksheet sheet=sheets[index-1];
if (sheet.Name!=sheetName.ToString())
{
sheets.RemoveAt(index-1);
}
index--;
}

}


#2

Please try this attached fix and remove this line of code from your program:

DeleteTempletSheet(excel.Worksheets,sheet.Name);


#3

Thanks for your reply quickly! I do it as you saying,but throw exception
'The same worksheet name already exists'. Where is the wrong?
Can you help me? Thanks!


#4

Do you use this new fix? In which line of code this exception happens?

My test code is :

public static void InitiFile(Excel excel,string tempfile,string stempid)
{
Worksheet sheet=getTemplateSheet(tempfile,stempid);
Cells cells = sheet.Cells;
string fName=sheet.Name;
int index=excel.Worksheets.Add();
excel.Worksheets[index].Copy(sheet);
Worksheets sheets=excel.Worksheets;
sheets[0].Name=fName+"(1)";
//this.FillSheet(sheets[0],opid);
excel.Save(“d:\test\abc.xls”,FileFormatType.Default);

}
public static Worksheet getTemplateSheet(string tempfilename,string stempid)
{
Excel excel=new Excel();
excel.Open(tempfilename);
Worksheet sheet=null;
switch (stempid)
{
case “9”:
sheet=excel.Worksheets[“Upper HF Welding Process”];
break;
case “2”:
sheet=excel.Worksheets[“Skiving Instruction”];
break;
case “10”:
goto case “9”;
}
return excel.Worksheets[0];
}


static void Main(string[] args)
{
Excel excel = new Excel();
InitiFile(excel, “d:\test\PFC _FORM_Template.XLS”, “9”);
}

And the generated file is attached.


#5

Thanks for your reply,I use the new fix, i test the code of you provide,it's ok,but when i changed the line 'return excel.worksheets[0]' to 'return sheet' ,it can
gets the file 'abc.xls' .but when i open the file throw exception,so i can't open the file .


#6

Thanks for the report. Please try this fix.


#7

Thanks for you reply .The code occur the exception "the same worksheet name has exists" as below:

private int icount=1;
Excel excel=new Excel();

for(int k=0;k<arr1.Length;k++)
{
InitiFile(excel,tempfile,stempid,ref icount);

}

public void InitiFile(Excel excel,string tempfile,string stempid,ref int icount)
{
Worksheet sheet=getTemplateSheet(tempfile,stempid);
string fName=sheet.Name;
int index=excel.Worksheets.Add();
excel.Worksheets[index].Copy(sheet);
excel.Worksheets[index].Name=fName+"("+icount.ToString()+")";
Cells cells=excel.Worksheets[index].Cells; cells.ClearContents(6,0,cells.Rows.Count-1,9);
cells[4,3].PutValue(PartNameCh);
this.FillSheet(excel.Worksheets[index],opid);
icount++;

}


#8

Please check if you set same name for different worksheets. I cannot test all of your code. The following is my sample code:

public static void InitiFile(Excel excel,string tempfile,string stempid,ref int icount)
{
Worksheet sheet=getTemplateSheet(tempfile,stempid);
string fName=sheet.Name;
int index=excel.Worksheets.Add();
excel.Worksheets[index].Copy(sheet);
excel.Worksheets[index].Name=fName+"("+icount.ToString()+")";
Cells cells=excel.Worksheets[index].Cells;
cells.ClearContents(6,0,cells.Rows.Count-1,9);
cells[4,3].PutValue("abc");
//this.FillSheet(excel.Worksheets[index],opid);
icount++;

}

static void InitFile()
{
int icount=1;
Excel excel=new Excel();

for(int k=0;k<10;k++)
{
if(k % 2 == 0)
InitiFile(excel,"d:\\test\\PFC _FORM_Template.XLS","9",ref icount);
else
InitiFile(excel,"d:\\test\\PFC _FORM_Template.XLS","2",ref icount);

}

}


#9

thanks for your reply,The following is my test code ,throw exception 'The same worksheet name already exists' yet.
if the reason is :the aspose excel i used is evaluation?
if i use the formal copyright will to solve the problem?

public static void InitiFile(Excel excel,string tempfile,string stempid,ref int icount)
{
if (File.Exists("d:\\test\\abc.xls"))
excel.Open("d:\\test\\abc.xls");
Worksheet sheet=getTemplateSheet(tempfile,stempid);
string fName=sheet.Name;
int index=excel.Worksheets.Add();
excel.Worksheets[index].Copy(sheet);
excel.Worksheets[index].Name=fName+"("+icount.ToString()+")";
Cells cells=excel.Worksheets[index].Cells;
cells.ClearContents(6,0,cells.Rows.Count-1,9);
cells[4,3].PutValue("abc");
excel.Save("d:\\test\\abc.xls");
icount++;

}

static void InitFile()
{
int icount=1;
Excel excel=new Excel();

for(int k=0;k<10;k++)
{
if(k % 2 == 0)
InitiFile(excel,"d:\\test\\PFC _FORM_Template.XLS","9",ref icount);
else
InitiFile(excel,"d:\\test\\PFC _FORM_Template.XLS","2",ref icount);

}

}


#10

It doesn't matter with an evaluation copy or licensed copy. This exception occurs if same name exists different worksheets.

Please add several line of code to check what may cause your problem:

int index=excel.Worksheets.Add();

for(int i = 0; i < excel.Worksheets.Count; i ++)
Console.WriteLine(excel.WorksheetsIdea);

Console.WriteLine(sheet.Name);


excel.Worksheets[index].Copy(sheet);

Please check if there is a worksheet whose name is same as copied worksheet.


#11

If you still cannot figure out the problem, please zip all your project and post it here. I will check it ASAP.


#12

Has this been fixed? I can duplicate the problem easily. I am using the latest posted version. The spreadsheet I am starting with, template.xls, is empty and I have removed all substituion code for clarity.



private void foo()

{

string templateFile = “template.xls”;

string masterTemplateFile = “masterTemplate.xls”;

string finalReport = “report.xls”;



Excel excelm = new Excel();

Excel excelt = new Excel();



excelt.Open(templateFile);

excelm.Worksheets[0].Copy(excelt.Worksheets[0]);

excelm.Save(masterTemplateFile);



ExcelDesigner d = new ExcelDesigner();

d.Open(masterTemplateFile);

d.Process();



//Error is kicked up here.

d.Save(finalReport);





}


#13

Thank you for the report.

This problem is caused by duplicated evaluation worksheet name and only occurs in evaluation copy. I have fixed it. Please try this attached version.