Free Support Forum - aspose.com

Working with multiple sheets

Hi,

I am currently using an eval. version of ASPOSE Excel, but the PO has already been raised. I am facing a problem while trying to insert imported data into multiple sheets. Basically, I’ve got 1 DataSet with 2 DataTables in it and I’m trying to insert data from the 1st DataTable into the 1st WorkSheet and data from the 2nd DataTable into the 2nd Worksheet, and then open the file in a browser.

First of all, is this possible in the eval. version, second, if possible, can you please send me the C# code to achieve this functionality ?

I’m using the latest hotfix. I’m really very sorry, this question might be answered by you earlier, but due to time constraints, I could not go through the entire forum and all your replies.

I tried using -

Excel objAsposeExcel = new Excel();

Worksheet objWkSheet = objAsposeExcel.Worksheets[1];

But this doesn’t work, I might be going wrong somewhere.

Thanks in advance. Keep up the good work, you guys are doing a great job.

- Parry

Parry,

per default, the Excel instance you create only contains a single Worksheet that is located at index 0 (all the Aspose collections are 0 based). However you may Open() a Workbook that contains multiple worksheets.

To add a new worksheet to the Worksheets collection, use

Worksheet wks = excel.Worksheets[excel.Worksheets.Add()]

Regards

Kai

Kai’s answer is perfect. Thank you, Kai.

Parry,

Aspose.Excel can meet your needs with just a few lines of code. You can try this:

//When an Excel object is created, it only contains 1 worksheet at first
Excel objAsposeExcel = new Excel();
//Add a new worksheet
objAsposeExcel.Add();

Worksheet objWkSheet = objAsposeExcel.Worksheets[0];

//Import your data starting at A1, ds is the DataSet object
objWkSheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0); //Second parameter is set to true to show the field name

objWkSheet = objAsposeExcel.Worksheets[1];
objWkSheet.Cells.ImportDataTable(ds.Tables[1], true, 0, 0);



Thank you Kai, Thank you Laurence. But it seems I was not very clear at explaining you my problem.

Here’s the code snippet that I am actually using:

// *******************************************

///

/// This function merges the Data with the Template and

/// sends the formatted output to the client’s browser.

///

/// The Excel Template (without the data)

/// The Data (DataSet) which is to be imported into the Excel file

///

/// The DataSet which holds the StartRow and Start Col positions for each of

/// the DataTables in dsDataToExport DataSet.

///

/// HttpResponse object

private void BeginExport( string strTemplateFile, DataSet dsDataToExport,

DataSet dsSettings, string strSaveAsFile, System.Web.HttpResponse objResponse)

{

try

{

Excel objAsposeExcel = new Excel();

// Open the Excel template

objAsposeExcel.Open(strTemplateFile);

if (dsDataToExport.Tables.Count > 0)

{

for (int intCtr = 0; intCtr < dsDataToExport.Tables.Count; intCtr++)

{

Worksheet objWkSheet = objAsposeExcel.Worksheets[Convert.ToInt32(dsSettings.Tables[intCtr].Rows[0][“intSheetIndex”])];

objWkSheet.Cells.ImportDataTable( dsDataToExport.Tables[intCtr], false,

Convert.ToInt32(dsSettings.Tables[intCtr].Rows[0][“intStartRow”]),

Convert.ToByte(dsSettings.Tables[intCtr].Rows[0][“intStartCol”]), false);

}

objAsposeExcel.Save( strSaveAsFile, SaveType.OpenInBrowser, FileFormatType.Excel2000, objResponse);

}

}

catch(Exception objEx)

{

throw objEx;

}

}

// *******************************************

As you can see above, I have 2 DataSets, one which holds the actual data to be exported, and second which holds the RowStart and ColStart positions where the data in the 1st DataSet should be copied into the Excel file. See the function header comments for more details.

Coming back to my question: I have multiple sheets in my Excel template. Now, let’s assume that I have 2 DataTables in the 1st DataSet and 2 DataTables in the 2nd (for settings), how do I insert data in the 2nd sheet ? I can import data into the 1st sheet successfully, but when I do that for the 2nd, it displays a blank excel file (blank template).

Please see my first message post (one which I had sent on 09-03-2004) for more details.

Have you checked how many rows returned in the second exported DataTable?

Please change your code to the following and see what happens:

if (dsDataToExport.Tables.Count > 0)

{

for (int intCtr = 0; intCtr < dsDataToExport.Tables.Count; intCtr++)

{

Worksheet objWkSheet = objAsposeExcel.Worksheets[intCtr];

objWkSheet.Cells.ImportDataTable( dsDataToExport.Tables[intCtr], false,

0, 0, false);

}

objAsposeExcel.Save( strSaveAsFile, SaveType.OpenInBrowser, FileFormatType.Excel2000, objResponse);

}

Also please check the start row and column index. MS Excel limits row number to 65536 and column number to 256. So the max start row index is 65535 and max column index is 255.

You can also have a live chat with me at http://www.aspose.com/Corporate/Teams/Product/Nanjing/Contactus.html

Hey Laurence,

I tried out the code snippet that you had given but I’m really sorry to say, it still does not work. I am able to import data in the first (zeroth) worksheet without any hassles absolutely, but when I try to import data into the 2nd sheet, I just get a blank sheet without any errors.

Sorry for troubling you again and again with this silly problem.

- Parry

Hi Parry,

I think maybe your second worksheet in the designer file(template) is a hidden sheet. So you cannot see the data in it. Please check it.

If you still cannot figure it out, could you zip your project and designer file, then send them to me by email?

Hi Parry,

I get your zip file and find there are two hidden worksheets in your designer file. The two hidden worksheets are the second and third worksheet. You can use the following method to unhide those two hidden worksheets:

Tools -> Macro -> Visual Basic Editor
In Project Explorer window, you can find 5 worksheets in IS-2511.xls. I will send you an new file with all worksheets unhiden.

In Property window of the hidden worksheet, you can make them visible in MS Excel.

If you don’t want to unhide the worksheet, you can use the following property of Worksheets object to get the Worksheet element with the specified name.

public Worksheet this[string] {get;}


And I also find a problem in your test project:

objWkSheet = objAsposeExcel.Worksheets[Convert.ToInt32(dsSettings.Tables[intCtr].Rows[0][“intSheetIndex”])];

This line of code always gets the second worksheet. Please check it.

Hey Laurence,

You are just great !!. I have figured out the problem. Thanks ever so much. I am heading straight to the Tech Infrastructure dept and asking them to get licensed versions of ASPOSE Excel and ASPOSE Word.

Are you planning to come out with a version for Visio too ? If yes, by when can we expect it ? My project involves Visio automation and I would be interested in buying ASPOSE Visio too.

Thanks once again !!..

Regards,
Parry

Dear Parry,

I just address Aspose.Visio.

Yeah we were planning to make Aspose.Visio but we didn’t get any requests so we didn’t begin to make it.

I’m very glad that you’re interested in Aspose.Visio.

Why not post your feature requests at:

http://www.aspose.com/forums/ShowForum.aspx?ForumID=113

Try to elaborate on your needs then those featues will be implemented at our earliest convenience.

Hi Parry,

Thank you. We hope you can enjoy our components.

About Aspose.Visio, Ben will address it later.

Hi Laurence/Ben,

Just wanted a quick information on ASPOSE Licensing issue. Well, as I told you earlier, I have initiated a PO for ASPOSE Excel Standard & ASPOSE Word Professional. But I would also like to know the cost if I want to upgrade in future.

In other words, I would like to know the cost of licenses, 1) if I want to upgrade from Standard version to Professional version of ASPOSE Excel and; 2) if I want to upgrade from Professional version to Corporate version of ASPOSE Word (in future)

This would help me make a better decision. Please let me know.

Thanks in advance.

Regards,
Parry

Dear Parry,

At the moment I failed to find the details of your PO, say, the exact pricing options you have chosen except the Edition Type, so I can not quote the exact upgrade price. If you can send email to info@aspose.com about the exact options you have chosen at page http://www.aspose.com/Purchase/Pricing.aspx for both Aspose.Excel and Aspose.Word, I would give you exact upgrade quotes.

However, actually you can figure it out by yourself as our upgrade policy is quite straightforward: Just the difference of the prices of the existing order and the upgraded order. More info please check http://www.aspose.com/Purchase/Upgradepolicy.html.