ListObject with Aspose

I have having trouble trying to port our Excel Addin to Aspose.

I use data tables (list objects) in Excel 2007 in my attached workbook (See loanapplicationdata sheet).

I open the workbook, delete the listobject, create a new listobject to replace it and then loop through a ADO.NET datatable and put a new row in the listobject. However I cannot get this to work correctly in Aspose.

I try different variations using Aspose with no luck. I notice the passing an array into putvalue is not supported in Aspose? I kept getting system.object appearing in all the cells. I tried looping with a standard putvalue but it didn’t add it to the listobject. Thoughts?


Here is a snippet of some code I use for my current Excel 2007 add in. It works fine. Trying to convert to Aspose.


Dim TblLoanApplicantData As Microsoft.Office.Interop.Excel.ListObject=ws.ListObjects(“TblLoanApplicantData”)

TblLoanApplicantData.Delete()
ExcelHelper.InsertListObject(ws, dt, “TblLoanApplicantData”, ws.Range(“A1”), pBar.pgBar)

Public Shared Sub InsertListObject(ByVal ws As Excel.Worksheet, ByVal dt As Data.DataTable, _
ByVal tblName As String, ByVal location As Range, _
ByVal pBar As System.Windows.Forms.ProgressBar)

Globals.ThisAddIn.Application.ScreenUpdating = False
Globals.ThisAddIn.Application.DisplayAlerts = False
ws.Activate()

Dim rangeApplicant As Excel.Range = location

For iCol = 0 To dt.Columns.Count - 1
rangeApplicant(1, iCol + 1).value2 = dt.Columns(iCol).ColumnName
Next

ws.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, ws.Range(location, location.Cells(1, dt.Columns.Count)), True, Excel.XlYesNoGuess.xlYes).Name = tblName

Dim rowIdx As Integer = 2
Dim colStart As Integer = 1
Dim colCount As Integer = dt.Columns.Count

For Each dr As DataRow In dt.Rows
Dim rowArray As Object = dr.ItemArray
ws.Range(ws.Cells(rowIdx, colStart), ws.Cells(rowIdx, colStart + colCount - 1)).Value2 = rowArray
rowIdx += 1

If Not (pBar Is Nothing) Then
pBar.Value += 1
End If
Next
Globals.ThisAddIn.Application.DisplayAlerts = True
Globals.ThisAddIn.Application.ScreenUpdating = True
End Sub


What is your design pattern for working with listobjects for 2007 with your component?

Thanks

Robin

Hi,

Currently Aspose.Cells only supported to get/set picture and chart(line, column, pie, bar) shape to .xlsx format file. Other type of shapes such as listObject, button will be preserved when load and save the file, but cannot be accessed or added to the file. We are working on these features. Thank you for your patience.

This is definitely a setback. The major benefit of your component to us was the perceived ability to open an Excel file, replace some data and shoot it down to the customer. They would open it and the data in the listobject (or Table as Excel calls it in the UI) would be updated. Thus, all the charts and pivot tables would now show the latest data.

This allows us to create really cool excel files in advance that do advanced analysis on a set of data without have to regularly paste the data into a spreadsheet and email them back the new version. Here we can dump the data straight from a Datatable into the sheet and the other sheets would update thier charts automatically. We have created an excel add in to do this. However I have concerns about rolling out client side applications that need to communicate to a server to get the data and also have to be updated. It is a much more complicated process compared to having Aspose on the web server that can open a spreadsheet and dump data into a table.

Do you have time frame for this feature. I really want to stick with Aspose solutions as your Word component has been just great over the years.

I checked your file, but could not find any listObject in it. Instead I found some autofilter that also can be dropped down, such as the autofilter in worksheet “TblLoanApplicantData” cell “A1”.

Do you need to change these autofilters or add some listObjects to the file?

Please give us more detail about your need, thank you.

Hi,

Sorry for my misreading. I look around your mail again, I guess you need to:

  1. Delete the data table in sheet “LoanApplicationData”

  2. Create a new data table named “TblLoanApplicantData” (same as the previous)

  3. Fill the new data table with an ADO.NET datatable

Since currently Aspose.Cells do not support data table feature, you cannot delete or create a data table in Aspose.Cells. But if you do not need to change the table’s structure (such as table name, columns name and the range of table), I think you can reach you need by only changing the table’s cell’s data with Aspose.Cells.

Please consult the following code:

string infn = @"E:\test\before.xlsx";

string outfn = @"E:\test\before_out.xlsx";

Workbook book = new Workbook();

book.Open(infn, FileFormatType.Excel2007Xlsx);

Worksheet sheet = book.Worksheets["LoanApplicantData"];

// remove old datas

sheet.Cells.DeleteRows(1, 4151);

System.Data.DataTable dt = new System.Data.DataTable();

// todo: fill the datatable

// import the data table

sheet.Cells.ImportDataTable(dt, false, "A2");

book.Save(outfn, FileFormatType.Excel2007Xlsx);

Thank you

Everything seems fine with your code except, the range will change as the number of rows with vary with the parameters of the data range used to collect the row data.

That is why we use a data table rather than a range of rows. Because in the pivot tables and charts, we can reference a data table rather than a hardcoded range. That way the range can be a dynamic set of rows.

The data table on the sheet can be 4 rows or 40,000, the charts and pivot tables don’t care.

Your thoughts?



Hi,

It is suggested that you use named range instead of data table as the data source of pivot tables. So you can set the named range to new value with Aspose.Cells after you update the data source worksheet.

Please consult the following steps:

  1. Create a template file

  2. Define a name range “DataSourceLoanApplicantData” with “name manager” covering data table “TblLoanApplicantData”’s range

  3. Create pivot table with name range “DataSourceLoanApplicantData” and set data option to “Refresh on open”

(Please check the attached file. I create a pivot table in worksheet “New_Ethnicity_pivot” with name range “DataSourceLoanApplicantData”in it.)

  1. Open the template file with Aspose.Cells

  2. Update data in worksheet “LoanApplicantData” or other worksheet containing data source (With Cells.DeleteRows() and Cells.ImportDataTable() methods)

  3. Update the named range “DataSourceLoanApplicantData”’s refer range to new range

  4. When the file is opened, pivot table in worksheet “New_Ethnicity_pivot” will auto refresh with the new data

The following code demos how to update a named range:

string infn = @"E:\test\ template.xlsx";

string outfn = @"E:\test\ template_out.xlsx";

Workbook book = new Workbook();

book.Open(infn, FileFormatType.Excel2007Xlsx);

Worksheet sheet = book.Worksheets["LoanApplicantData"];

sheet.Cells.DeleteRows(500, 4000);

// todo: fill the datatable

//System.Data.DataTable dt = new System.Data.DataTable();

//sheet.Cells.ImportDataTable(dt, false, "A2");

// update named range

Name r = book.Worksheets.Names["DataSourceLoanApplicantData"];

r.RefersTo = "=LoanApplicantData!$A$1:$V$500";

book.Save(outfn, FileFormatType.Excel2007Xlsx);

Thank you

@RobinMarks,

Try using newer Aspose.Cells APIs which allows you create and manipulate Tables/List objects from the range of cells. The following are the steps to create a table in an Excel (XLSX) file using Aspose.Cells for .NET:

  • Load an Excel workbook or create a new one using Workbook class.
  • Add data to the cells of the worksheet.
  • Add a new ListObject to the worksheet.
  • Set ListObject.ShowTotals property to true .
  • Calculate the total and save the workbook as an Excel .xlsx file.

See the following code sample that shows on how to create a table in Excel worksheet.
e.g
Sample code:

// Instantiate a Workbook object that represents Excel file.
Workbook wb = new Workbook();

// Get the first worksheet.
Worksheet sheet = wb.Worksheets[0];

// Obtaining Worksheet's cells collection
Cells cells = sheet.Cells;

// Setting the value to the cells
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");

cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James"); 

cell = cells["B2"];
cell.PutValue(1);
cell = cells["B3"];
cell.PutValue(2);
cell = cells["B4"];
cell.PutValue(3);
cell = cells["B5"];
cell.PutValue(4);
cell = cells["B6"];
cell.PutValue(1); 

cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang"); 

cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe"); 


cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France"); 


cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500); 

// Adding a new List Object to the worksheet
Tables.ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add("A1", "F6", true)];

// Adding Default Style to the table
listObject.TableStyleType = Tables.TableStyleType.TableStyleMedium10;

// Show Total
listObject.ShowTotals = true;

// Set the Quarter field's calculation type
listObject.ListColumns[1].TotalsCalculation = Tables.TotalsCalculation.Count;

// Save the Excel file.
wb.Save("Excel_Table.xlsx", SaveFormat.Xlsx);

For further reference, learn more about working with tables in Excel worksheets.

We also recommend you to have a look at the documentation of Aspose.Cells for .NET to learn the advanced features for manipulation of Excel files.