Setting Dynamic Starting Cell when ImportDataTable

Aspose.Cells Product Family
1

I’m evaluating the Excel library for a project. I need the ability add columns of strings into an excel worksheet template with a dynamic starting cell. It seems when I get an Excel.Cell object and then try to use ImportDataTable I receive an “Object reference not set to an instance of an object.” Error. Is there another approach to this using your library?

My Code is Below (VB)

Dim ee as New Excel.Excel
Dim ec as Excel.Cell
Dim ecs as Excel.Cells

‘ Open the template
ee.open(c:\Template.xls”

‘ Grab the starting cell which hold the row/column index
ec = ee.WorkSheets(0).Cells(“B14”) ‘ Does not error if this line is commented.
‘ B14 Will be dynamic

ecs = ee.WorkSheets(0).Cells
ecs.ImportDataTable(dt, False, ec.Row, ec.Column)

Thanks
Randy

2

Hi Randy,

I will check this issue. But are you using the latest version/fix?

3

Hi Randy,

Thanks for your report. I found this bug and fixed it. Please download the latest hotfix and have a try.

In this hotfix, I also add a new ImportDataTable method:

public int ImportDataTable(DataTable dataTable, bool isFieldNameShown, string startCell)

so you can directly try the following code:

ecs.ImportDataTable(dt, False, “B14”)

4

Thanks for your quick response Laurence. The new method works, but [the new] ImportDatabale() takes the datatable and starts on a cell and assumes the next column in the datasource belongs in the next column in the excel file. In most cases this would work and I could code my project this way.

I was looking at the SmartTags feature and it is really nice. Using SmartTags would allow a lot of flexibility in the templates I need to modify. However, I think I found two bugs. The excel templates I need to edit are sitting on a server. I have a user interface to choose what columns need to go in a template. Each column has a starting cell. Since the user can’t possible know what the smarttags would be to point to a Table.Column I need to open the template and check for the smarttag in the starting cell for each custom column the user wants. If the smarttag is not there I need to add it. Then open the excel file (with the smarttags) using the ExcelDesigner, set the datasource and process.

I get a “File Already Exists” error when I try to save over the original excel file after I attempt to add smarttags. So, I tried to open the excel file, add the necessary smarttags and save the file with another name. I receive another error when I try to open the second file using the ExcelDesigner. The error is “Access denied because another user has the file open and locked...”

I’d rater not have to save a second “TAGGED” version of every excel template I need to modify. As for the second error I don’t see a way to “close” the excel file so I can open it again. My code is below…

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim ee As Excel.Excel
Dim ed As Excel.ExcelDesigner
Dim ecs As Excel.Cells
Dim fn As String = "c:\" & Guid.NewGuid.ToString & ".xls"

Dim strTemplate As String
strTemplate = "C:\TEST.xls"

ee = New Excel.Excel
With ee
.Open(strTemplate)

If .Worksheets(0).Cells("B14").StringValue <> "&=CUSTOMERS.FNAME" Then
.Worksheets(0).Cells("B14").PutValue("&=CUSTOMERS.FNAME")
End If

If .Worksheets(0).Cells("C14").StringValue <> "&=CUSTOMERS.LNAME" Then
.Worksheets(0).Cells("C14").PutValue("&=CUSTOMERS.LNAME")
End If

.Save(strTemplate, Excel.SaveType.Default) 'ERROR WHEN SAVING OVER TEMPLATE
End With

ed = New Excel.ExcelDesigner
With ed
.Open(strTemplate) 'ERROR HERE WHEN I OPEN MODIFIED TEMPLATE.
.SetDataSource(Create_Datasource)
.Process()
.Save(fn, Excel.FileFormatType.Default)
End With

End Sub


Private Function Create_Datasource() As DataTable

Dim intLoop As Integer
Dim dt As New DataTable("CUSTOMERS")
Dim dr As DataRow

dt.Columns.Add("FNAME", Type.GetType("System.String"))
dt.Columns.Add("LNAME", Type.GetType("System.String"))
For intLoop = 0 To 20
dr = dt.NewRow
dr.Item("FNAME") = "first" & intLoop
dr.Item("LNAME") = "last" & intLoop

dt.Rows.Add(dr)

Next

Create_Datasource = dt

End Function
5

Hi Randy,

You should change this line of code:
.Save(strTemplate, Excel.SaveType.Default)

to
.Save(strTemplate, Excel.FileFormatType.Default)

That’s a problem of VB. It cannot prompt the error message while compiling while C# can.

I tested your code in my machine. When this line is changed, all worked fine.