Free Support Forum - aspose.com

Excel Evaluation

I want to read the Excel file as XML Spreadsheet. Will Aspose read the data in the form of XML?

Hi,

Aspose.Cells supports to read, write or convert Spreadsheet ML files (XML). See the documents for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/spreadsheetml-xlsx-xml.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/opening-files.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/saving-files.html

Hi,

I want to read the excel(97-2010) files and I requested for the temporary licence. Now my question is can you please provide some sample code for reading Excel files in the form of XMLspreadsheets?

Below are the steps how we are reading currently

  • Open Excel file
  • Copy worksheet info to clipboard
  • Read data from Clipboard as XML Spreadsheet -- returns memory stream
  • Read that memory stream into a string builder

I think you understand about what is input and output for my requirement. Now we want to get rid of reading from clipboard and existing interop objects. If you have a doubt of how do we need to read the file as XML spreadsheet means we have configurations for existing clients, so they should not effect because of this new ASPOSE component if it reads in a different way.

Can you please let me know any sample code for this so that I can evaluate as soon as i get the temporary licence

Hi,

Please check my previous post and read the topics that I recommended for your reference.

Thank you.

I am getting an exception of "Root element Missing" when saving a file into stream and reading it back from that stream


Below are the steps that I used

MemoryStream stream = new MemoryStream();
workbook.Save(stream, SaveFormat.SpreadsheetML);


XmlReader xmlReader = XmlReader.Create(stream);
if (xmlReader.ReadToDescendant("Worksheet"))
{

--
--

}

I am getting exception at the highlighted line

Can you please let me know is it correct way of reading

Hi,

Could you add a line of code, it may work fine for you.

MemoryStream stream = new MemoryStream();
workbook.Save(stream, SaveFormat.SpreadsheetML);

stream.Position = 0;
XmlReader xmlReader = XmlReader.Create(stream);
//…


Thak you.

Thank you so much. it worked.

Can you tell me solution for one more problem. How to load a workbook to stream by ignoring Hidden rows/columns ?

I tried like below but it is not working as expected

for (int i = 0; i < workbook.Worksheets[0].Cells.MaxRow; i++)

{

if (workbook.Worksheets[0].Cells.GetRow(i).IsHidden)

{

workbook.Worksheets[0].Cells.DeleteRow(i);

}

}

even though there are two hidden rows in the worksheet it is deleting only one row.

Instead of looping throgh like this is there any way ignoring/deleting hidden rows/column ?

Many Thanks!!

Hi,

There is no such method I am afraid to ignore hidden rows/cols. I think you may change your loop a bit, it looks like your loop is not iterating through all the rows in a sheet.

See the updated code segment:
e.g

I tried like below but it is not working as expected


for (int i = 0; i <= workbook.Worksheets[0].Cells.MaxRow; i++)


{


if (workbook.Worksheets[0].Cells.GetRow(i).IsHidden)

{

workbook.Worksheets[0].Cells.DeleteRow(i);

}

}


Hi,

I tried as you are suggested, but it did not worked. I am attaching the excel file.in this file Line 14 and 15 are hidden when I am looping it is just deleting only ine 14.

I used below code:

for (int i = 0; i <= workbook.Worksheets[0].Cells.MaxRow; i++)

{

if (workbook.Worksheets[0].Cells.GetRow(i).IsHidden)

{

workbook.Worksheets[0].Cells.DeleteRow(i);

}

}

DataTable dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.Rows.Count,worksheet.Cells.Columns.Count);

Hi,


This is strange because your provided code is correct and it should remove the both Hidden Rows. But it is not working as expected with latest release of Aspose.Cells for .NET v6.0. Also, I have noticed that this issue occurs for every consecutive Hidden Row. So I have logged an investigation to probe further in this issue, Ticket ID for your reference is CELLSNET-28575. Soon we will sort this out and will let you know here.

Thank you

Hi,

Please change your code as the following :

C#


Workbook workbook = new Workbook(@“D:\FileTemp\New+Microsoft+Office+Excel+Worksheet.xlsx”);

var worksheet = workbook.Worksheets[0];


for (int i = 0; i <= workbook.Worksheets[0].Cells.MaxRow; i++)

{


if (workbook.Worksheets[0].Cells.GetRow(i).IsHidden)

{

//workbook.Worksheets[0].Cells.DeleteRow(i); // This line is hit twice for given Excel File

workbook.Worksheets[0].Cells.DeleteRow(i–);

}

}




Thank you!!!

Hi,

var worksheet = workbook.Worksheets[0];

for (int i = 0; i <= workbook.Worksheets[0].Cells.MaxRow; i++)
{

if (workbook.Worksheets[0].Cells.GetRow(i).IsHidden)
{
workbook.Worksheets[0].Cells.DeleteRow(i--);
}
}
I am getting exception 'Object Reference not set to an instance' at line number 22 for the attached file in the above loop. can you please check ?

Hi,

I can find the error when the value of “i” becomes 22 using your recent template file. I have reopened your issue “CELLSNET-28575” and we will soon figure it out.

Thank you.

Hi,

Please change your code as following:

C#


Workbook workbook = new Workbook(“d:\FileTemp\HiddenError.xls”);

var worksheet = workbook.Worksheets[0];


for (int i = 0; i <= workbook.Worksheets[0].Cells.MaxRow; i++)
{

Row row = workbook.Worksheets[0].Cells.GetRow(i);//if the Row oject is not created ,it will return null.

if (row == null)
continue;

if (row.IsHidden)
{
workbook.Worksheets[0].Cells.DeleteRow(i–);
}
}

workbook.Save(“d:\FileTemp\dest.xls”);

Thank you for this.

I am getting an error message "You are using an evaluation copy and have opened files exceeds limitaion"

Is there any limitaion on number of files processing using Aspose trail dll ? Is ther any way to solve this problem ? Because we have several clients, so we need to test whether it is processing as expected or not?

Hi,

Please check the document, there are some limitations using the product without license:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/licensing.html


"When running your program, you can only open 100 Excel files using Aspose.Cells library. If your application exceeds this number, an exception will be thrown."


Thank you.



Also, you may get a 30 days temporary license to evaluate the product with its full capacity:
http://www.aspose.com/corporate/purchase/temporary-license.aspx

Thank you.

Is ther any way to solve this problem ? Because we have several clients, so we need to test whether it is processing as expected or not?

Hi,

Please check my previous two replies.

Thank you.