Hi,
I want to insert new worksheet in my workbook at runtime, the number of the worksheet is randomly generated. And I have a standard template also which should be copied in those worksheet. Please help me with this.
Thanks
Monisha.
Hi Monisha,
Thanks for considering Aspose.
Well, you may add new worksheets to your workbook using Worksheets.Add() method. i.e.,
Workbook workbook = new Workbook();
//Adding a new worksheet to the Workbook object
int sheetIndex = workbook.Worksheets.Add();
//Get the newly added worksheet object.
Worksheet worksheet = workbook.Worksheets[sheetIndex];
.
.
And you may copy your desired worksheets in your template file to it using Worksheet.Copy() method.
For reference, please check the following topics:
Feel free to contact us any time
Thank you.
Hi Monisha,
And if you just want to create a duplicate copy of your template file (having worksheets), you are to just Open the template excel file and Save it with a different name, that's it!
Thank you.
Hi,
Thanks for the info. But I am still struggling with it, I am trying to add for example I would say
I want to add four worksheets in my workbook first and then copy my template in all those worksheets. Actually the number of the worksheets to be added will be dependant on the number of employees and that is random. Can you help me with. I am still getting errors.
Thanks
Monisha.
Hi Monisha,
Could you post sample file and code to show your problem? Then I can help you how to solve it.
And you can also try this attached version.
Here is the code.
Dim path_of_designer_file As String = Server.MapPath("~/UI/New_GTMIS_Download.xls")
Dim objWorkBook As Workbook = New Workbook
Dim intSheetIndex As Integer = 4
Dim objworksheet As Worksheet = objWorkBook.Worksheets(intSheetIndex)
intSheetIndex = objWorkBook1.Worksheets.Add()
objWorkBook.Open(path_of_designer_file)
Thanks
Monisha.
Hi Monisha,
I am still not sure about your code, there are some contradiction for your peice of code:
Dim intSheetIndex As Integer = 4 ............you have given a sheet index 4 that means sheet no. 5 in the template file.
Dim objworksheet As Worksheet = objWorkBook.Worksheets(intSheetIndex)..........you access the fifth sheet in your template file.
intSheetIndex = objWorkBook1.Worksheets.Add()............now intSheetIndex will be changed accordingly for your second workbook. you are adding worksheet. if you are creating a new workbook the intSheetIndex will be 1 so objworksheet will be changed and now it will point to second worksheet not the fifth one.
Now if you add a copy statement like:
objWorkBook1.Worksheets(intSheetIndex).Copy(objworksheet); .... you are actually the second sheet from the template file to your newer book.
Please adjust your code accordingly, so that you may copy the appropriate worksheets.
If you still not sure, please send your template file with all the relevant details ... how you want to copy the worksheet, which worksheets you want to copy etc, so that we may better figure it out
Thank you.
Alternatively, you may place the your code as folowing after you open your template file , it should also work fine.
.
.
Dim objWorkBook As Workbook = New Workbook
objWorkBook.Open(path_of_designer_file)
Dim intSheetIndex As Integer = 4
Dim objworksheet As Worksheet = objWorkBook.Worksheets(intSheetIndex)
intSheetIndex = objWorkBook1.Worksheets.Add()
Thank you.
Hi,
When I tried this part of code it is giving error "Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index".
What I am trying to do is add four worksheets in my workbook and then copy my template in all the four worksheet.(The count of worksheets is random I mean it can be 4 or 7 or 10)
After that I have to insert data to all the worksheets according to different user's.
Please see if you can help.
Thanks a lot
Monisha
Hi,
Well the error refers to that either your source template book or target workbook does not match to intSheetIndex (4 or so), the sheet index may be invalid, so you have to adjust it accordingly.
I have attached two excel files one template workbook "SampBook_Souce.xls" and other is "Details_Book.xls". Following is my code, you may consult it and run it as it works fine.
Description: I open a template workboo, the template file contains four worksheets. I create a new workbook from the scratch, remove/clear the default sheet1. Now loop through all the sheets of the template workbook worksheets. In the loop in every iteratation, I adds a new worksheet to the target workbook and copy the worksheets from the template excel file to the target workbook. I also change the name of newly copied sheets and add some extra data to a cell of each worksheet (in the target workbook).
Dim WBKSource As New Workbook
WBKSource.Open("d:\test\SampBook_Source.xls")
Dim workbook As New Workbook
workbook.Worksheets.Clear()
Dim worksheet As Worksheet
Dim intSheetIdx As Integer
Dim i As Integer
For i = 0 To WBKSource.Worksheets.Count - 1
intSheetIdx = workbook.Worksheets.Add()
worksheet = workbook.Worksheets(intSheetIdx)
worksheet.Copy(WBKSource.Worksheets(i))
worksheet.Name = "MySheet" & i + 1
Dim cells As Cells = worksheet.Cells
cells("M10").PutValue("Data " & i + 1)
Next
workbook.Save("d:\test\Details_Book.xls")
Hopefully it will help you.
For further reference, please check:
And alway try to use the latest version of Aspose.Cells for .Net downloading @:
Hello Amjad,
I am having problem while merging two or more worksheets in a empty workbook. Please check the following code:
--------------------------------------------------
//Open a Workbook.
Workbook yearly = new Workbook();
yearly.Open(@“C:\A.xls”);
//Open another Workbook.
Workbook monthly = new Workbook();
monthly.Open(@“C:\B.xls”);
//Open another Workbook merge the two.
Workbook main = new Workbook();
//Merging
main.Worksheets.Add(“Usage Year to Date”);
main.Worksheets[“Usage Year to Date”].Copy(yearly.Worksheets[0]);
main.Worksheets.Add(“Monthly Usage Trend”);
main.Worksheets[“Monthly Usage Trend”].Copy(monthly.Worksheets[0]);
//Save the file.
main.Worksheets.ActiveSheetIndex = 0;
main.Save(@“C:\Admin.xls”, FileFormatType.Default);
-------------------------------------------------------------------------------------------
I attached the generated file. When I open the report it shows the two worksheets are grouped, so I can’t select the chart in the first worksheet unless I click right and select “Ungroup Sheets” or go to another sheet and then comeback. As I cannot expect from my end user to ungroup sheets like this, so this is a critical issue for us, user will think it is not an Excel chart, just an image (because it is not selectable for the moment). Please help me on this issue.
Thanks,
Zerin.
Hi Zerin,
Kindly post your template “A.xls” and “B.xls” files here, we want to conduct a test for your issue.
Thank you.
Here they are…
Hi,
Please try the attached version.
I have tested using your template files and code with it, it works fine.
Thank you.
Hi,
Using the new DLL you provided, it works same as before. So my problem is not solved yet.
I am not sure whether I could make my point clear or not. So I am attaching a screen shot.
Thanks,
Zerin.
Hi,
I could not open your error.bmp file. But, I have tested your code with your template files again, I don’t find any issue (I might be missing something). I have also attached my output file. Kindly check it and let us know where is the problem.
My Sample code:
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“e:\Licenses\17-3-2010\Aspose.Total.lic”);
//Open a Workbook.
Workbook yearly = new Workbook();
yearly.Open(@“e:\test\A.xls”);
//Open another Workbook.
Workbook monthly = new Workbook();
monthly.Open(@“e:\test\B.xls”);
//Open another Workbook merge the two.
Workbook main = new Workbook();
main.Worksheets.Clear();
//Merging
main.Worksheets.Add(“Usage Year to Date”);
main.Worksheets[“Usage Year to Date”].Copy(yearly.Worksheets[0]);
main.Worksheets.Add(“Monthly Usage Trend”);
main.Worksheets[“Monthly Usage Trend”].Copy(monthly.Worksheets[0]);
//Save the file.
main.Worksheets.ActiveSheetIndex = 0;
main.Save(@“d:\test\Admin.xls”, FileFormatType.Default);
Please make sure that you are using the latest fix I attached.
Thank you.
Thanks a lot Amjad.
It is working now.
Zerin