Hello,
I’m trying to import from multiple repeaters (I have 4) into a single worksheet. The problem I’m facing is that when I specify the cells i.e. A3 for 1st repeater, A7 for 2nd repeater and so on… it’s sort of hardcoded in there. So when the data is looped for the first repeater and pumps out 46 results, I want the second repeater’s data to start from A50 BUT right now the second repeater fires up on A7 messing up the worksheet and so if the 2nd repeater has 4 results what happens is from A3 to A6 is the first three results of 1st repeater, from A7 to A10 are the four results for 2nd repeater and then from A11 to A46 are the remaining results from the 1st repeater! I know this sounds confusing but I’m trying to be as precise as possible. For further explanation please review my code below. I will highly appreciate any further help… Thanks.
Private Sub ExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ExportExcel.Click
'Instantiate a Workbook object that represents an Excel file
Dim workbook As Workbook = New Workbook
'Creating a file stream containing the Excel file to be opened
Dim fstream As FileStream = New FileStream(Server.MapPath(“StatementTemplate.xls”), FileMode.Open)
'Opening the Excel file through the file stream
workbook.Open(fstream)
'Get the reference of “A1” cell from the cells collection of a worksheet
Dim dt1 As DataTable = dsCommissionSummary.Tables(1)
dt1.Columns.Remove(“ProjectID”)
dt1.Columns.Remove(“LineType”)
dt1.Columns.Remove(“Period_Code”)
dt1.Columns.Remove(“Project_YTD_Fee_Revenue”)
dt1.Columns.Remove(“Project_YTD_Project_Margin”)
dt1.Columns.Remove(“CR_YTD_Revenue”)
dt1.Columns.Remove(“Current_Amount_Due”)
dt1.Columns.Remove(“Unapplied_Negative_Commissions”)
dt1.Columns.Remove(“Offshore_Uplift”)
dt1.Columns.Remove(“Actual_AR_Holdback”)
dt1.Columns.Remove(“Prior_Month_Holdback_Reversal”)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Cells.ImportDataTable(dt1, False, “A3”)
sheet.AutoFitColumns()
Dim dt2 As DataTable = dsCommissionSummary.Tables(2)
Dim sheet1 As Worksheet = workbook.Worksheets(0)
sheet1.Cells.ImportDataTable(dt2, False, “A7”)
sheet1.AutoFitColumns()
'Save the Excel file
workbook.Save(Server.MapPath(“StatementTemplate2.xls”), FileFormatType.Default)
fstream.Close()
workbook = Nothing
End Sub
Regards,
S
Hi,
Well, when you specify A7 cell for the second repeater, the data will be imported starting at the A7 cell. I think you should use Cells.MaxDataRow, Cells.MaxRow, Cells.MaxDataRowInColumn etc. properties after the first importdatatable method to obtain the cell row number, so that you may set the start point for the second import. In this way when the first sets of data is imported, the second would be started soon after the first ends.
Here is the sample example:
Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\test\Northwind.mdb")
con.Open()
Dim cmd As OleDbCommand = New OleDbCommand("Select CustomerID, City from Customers",con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet()
da.Fill(ds,"Customers")
cmd = New OleDbCommand("Select EmployeeID, Title from Employees",con)
da = New OleDbDataAdapter()
da.SelectCommand = cmd
da.Fill(ds,"Employees")
Dim wb As Workbook = New Workbook()
wb.Open("d:\test\TemplateBook1.xls")
Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim cells As Cells = sheet1.Cells
'Import the datatable and it starts at A3 Cells
cells.ImportDataTable(ds.Tables("Customers"),False,2,0,False)
'Import the datatable and it starts at the cell when the first set of records ends.
cells.ImportDataTable(ds.Tables("Employees"),False,sheet1.Cells.MaxDataRowInColumn(0) + 1, 0,False)
wb.Save("d:\test\outs_datatableBook1.xls")
Thank you.
Amjad,
Cells.MaxDataRow worked great for me. Really appreciate your help.
Thanks,
S
cells.ImportDataTable(ds.Tables(“Employees”),False,sheet1.Cells.MaxDataRowInColumn(0) + 1, 0,False
On above if I change the value of isFieldNameShown to true, how can I make the result of it in Excel bold? Basically so it comes out bold automatically?
Thanks,
S
Hi,
Well, it is simple. You may apply style formattings to a cell, cells, ranges, rows, column with ease.
Take a look at the code now, I add a few lines:
Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\test\Northwind.mdb")
con.Open()
Dim cmd As OleDbCommand = New OleDbCommand("Select CustomerID, City from Customers",con)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet()
da.Fill(ds,"Customers")
cmd = New OleDbCommand("Select EmployeeID, Title from Employees",con)
da = New OleDbDataAdapter()
da.SelectCommand = cmd
da.Fill(ds,"Employees")
Dim wb As Workbook = New Workbook()
wb.Open("d:\test\TemplateBook1.xls")
Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim cells As Cells = sheet1.Cells
'Import the datatable and it starts at A3 Cells
cells.ImportDataTable(ds.Tables("Customers"),False,2,0,False)
Dim style As Style = wb.Styles(wb.Styles.Add())
style.Font.IsBold = True
Dim flag As StyleFlag = New StyleFlag()
flag.FontBold = True
cells.ApplyRowStyle(sheet1.Cells.MaxDataRow +1,style, flag)
'Import the datatable and it starts at the cell when the first set of records ends.
cells.ImportDataTable(ds.Tables("Employees"),True,sheet1.Cells.MaxDataRowInColumn(0) + 1, 0,False)
wb.Save("d:\test\outs_datatableBook1.xls")
For further reference, please check:
Thank you.
Thank you…
Below is my code. The bold/italicized part is whats important. I’m also attaching the resulting spreadsheet from this code.
The data in cells (Rows and Columns) A3 until A6 should start from A46 (in this case only…but not always, it should be dynamic). It should come after the first datatable data.
Please advise on what I’m doing wrong! Thanks…
#Region “Export”
Private Sub ExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ExportExcel.Click
'Instantiate a Workbook object that represents an Excel file
Dim workbook As Workbook = New Workbook
'Creating a file stream containing the Excel file to be opened
Dim fstream As FileStream = New FileStream(Server.MapPath(“Template.xls”), FileMode.Open)
'Opening the Excel file through the file stream
workbook.Open(fstream)
'Get the reference of “A1” cell from the cells collection of a worksheet
Dim dt1 As DataTable = dsCommissionSummary.Tables(1)
dt1.Columns.Remove(“ProjectID”)
dt1.Columns.Remove(“LineType”)
dt1.Columns.Remove(“Period_Code”)
dt1.Columns.Remove(“Project_YTD_Fee_Revenue”)
dt1.Columns.Remove(“Project_YTD_Project_Margin”)
dt1.Columns.Remove(“CR_YTD_Revenue”)
dt1.Columns.Remove(“Current_Amount_Due”)
dt1.Columns.Remove(“Unapplied_Negative_Commissions”)
dt1.Columns.Remove(“Offshore_Uplift”)
dt1.Columns.Remove(“Actual_AR_Holdback”)
dt1.Columns.Remove(“Prior_Month_Holdback_Reversal”)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Cells.InsertRows(3, dt1.Rows.Count - 1)
For i As Int16 = 0 To dt1.Rows.Count - 1
sheet.Cells.CopyRow(sheet.Cells, 2, 3 + i)
Next
sheet.Cells.ImportDataTable(dt1, False, 2, 0, False)
Dim dt2 As DataTable = dsCommissionSummary.Tables(2)
dt2.Columns.Remove(“LineType”)
Dim sheet1 As Worksheet = workbook.Worksheets(0)
sheet.Cells.InsertRows(3, dt2.Rows.Count - 1)
For i As Int16 = 0 To dt2.Rows.Count - 1
sheet.Cells.CopyRow(sheet1.Cells, 2, 3 + i)
Next
sheet1.Cells.ImportDataTable(dt2, False, 2, 0, False)
'Save the Excel file
workbook.Save(“WorkBook.xls”, FileFormatType.Default, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response)
fstream.Close()
workbook = Nothing
End Sub
It seems like with this method the Adjustments data tables gets inserted into the 4 top rows. and with the other method you had provided me of Cells.MaxDataRow it would at the least export data tables by order but then, after exporting the first datatable, the second, third and fourth data tables would skip the headers and go in the bottom.
Regards,
S
Hi,
I think you can try to change your code to:
Dim dt1 As DataTable = dsCommissionSummary.Tables(1)
dt1.Columns.Remove("ProjectID")
dt1.Columns.Remove("LineType")
dt1.Columns.Remove("Period_Code")
dt1.Columns.Remove("Project_YTD_Fee_Revenue")
dt1.Columns.Remove("Project_YTD_Project_Margin")
dt1.Columns.Remove("CR_YTD_Revenue")
dt1.Columns.Remove("Current_Amount_Due")
dt1.Columns.Remove("Unapplied_Negative_Commissions")
dt1.Columns.Remove("Offshore_Uplift")
dt1.Columns.Remove("Actual_AR_Holdback")
dt1.Columns.Remove("Prior_Month_Holdback_Reversal")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Cells.InsertRows(3, dt1.Rows.Count)
For i As Int16 = 0 To dt1.Rows.Count - 1
sheet.Cells.CopyRow(sheet.Cells, 2, 3 + i)
Next
sheet.Cells.ImportDataTable(dt1, False, 2, 0, False)
Dim dt2 As DataTable = dsCommissionSummary.Tables(2)
dt2.Columns.Remove("LineType")
Dim sheet1 As Worksheet = workbook.Worksheets(0)
sheet.Cells.InsertRows(3, dt2.Rows.Count)
For i As Int16 = 0 To dt2.Rows.Count - 1
sheet.Cells.CopyRow(sheet1.Cells, 2, 3 + i)
Next
sheet1.Cells.ImportDataTable(dt2, False, 2, 0, False)
'Save the Excel file
workbook.Save("WorkBook.xls", FileFormatType.Default, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response)
Thank you.
Hi,
Thanks for the try. I really do appreciate it. I tried it but it’s not what I want. But I have with some help from a freind of mine gotten to solve this issue. There are still a few outstanding issues such as formatting that need to be taken care of. And while I’m here telling you this… I might as well ask, lol…
cells.Merge(5,2,2,3)
I looked up that on Aspose.Cells API documentation. Can you please explain to me what the (5,2,2,3) are in details? A scenario would be very nice as well to clear up some concepts in my head. Thanks.
Regards,
S
Hi,
Thanks for considering Aspose.
Well, related cells.Merge(5,2,2,3), kindly check the following API reference for the method:
[Visual Basic]
Public Sub Merge( _
ByVal firstRow As Integer, _
ByVal firstColumn As Integer, _
ByVal rowNumber As Integer, _
ByVal columnNumber As Integer _
)
Parameters
firstRow
First row of this range(zero based) ………. Here “5” means 6th row since it’s zero indexed base.
firstColumn
First column of this range(zero based) ………….Here “2” means third column i.e. “C” column since it’s zero indexed base.
So if we evaluate both the above parameters, we can say that the starting point for the cells to be merged would be “C6”.
rowNumber
Number of rows(one based) ………Here “2” means we have to merge “2” rows only considering in mind that the addition should be started from 1 and not from 0 to the previous reading. Since the previous row index is “5”, if we have to merge “1” row then the ending row index would be still “5”. And if we have to merge “2” rows then the ending row index would be “6” (means 7th row). If we have to merge “3” rows then the ending row index would be “7” (means 8th row) and so on.
columnNumber
Number of columns(one based)……………Here “3” means we have to merge “3” columns only considering in mind that the addition should be started from 1 and not from 0 to the previous reading. Since the previous column index is “2”, if we have to merge “1” column only then the ending row index would be still “2”. And if we have to merge “2” columns then the ending column index would be “3” (means D column). If we have to merge “3” columns then the ending column index would be “4” (means E column) and so on.
So, in the above perceptive, cells.Merge(5,2,2,3) defines that we will merge “C6:E7”.
Well, generally Aspose.Cells works in the same way as MS Excel. Could you elaborate which other formatting issues you got, If possible create sample tests to show them. We will check it soon.
Thank you.
Wow!
Great explanation Amjad. That helped alot to clear up the concept.
Basically, in my project I have 4 data tables exporting to one worksheet which can have any number of records, so each data table starting from 1 until 4 keeps pushing down the other data tables headings dynamically depending on number of records each DT has.
Now, my scenario being a little complicated where I have nothing static (compared to Aspose examples) I am having problems merging the cells. I wish it could have been this easy to be able to say okay I want to merge C6:C9. But the problem I face is that since its dynamic.. it can change depending on the data in the DB.
So how do you approach merging cells in a dynamic worksheet which has no fixed or statis columns and rows.
Also as an FYI, I use a template pre-formatted with headers and footers which is then populated. I tried pre-merging within the excel template but that would just overwrite the data.
Thanks and I hope I was clear enough in my explanation.
Regards,
S
P.S. Attached is the export I get into excel. As you can see the "Adjustments", "Payments" and "Future Adjustment" sections are not properly aligned with the headers above them due to merging issues. I have no merge code being run right now.
And below is the code to generate the above attached Example.xls. Also.. attached to this post is the pre-formatted template.
'Data Table 1
Dim dt1 As DataTable = dsCommissionSummary.Tables(1)
dt1.Columns.Remove("ProjectID")
dt1.Columns.Remove("LineType")
dt1.Columns.Remove("Period_Code")
dt1.Columns.Remove("Project_YTD_Fee_Revenue")
dt1.Columns.Remove("Project_YTD_Project_Margin")
dt1.Columns.Remove("CR_YTD_Revenue")
dt1.Columns.Remove("Current_Amount_Due")
dt1.Columns.Remove("Unapplied_Negative_Commissions")
dt1.Columns.Remove("Offshore_Uplift")
dt1.Columns.Remove("Actual_AR_Holdback")
dt1.Columns.Remove("Prior_Month_Holdback_Reversal")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Cells.InsertRows(3, dt1.Rows.Count - 1)
For i As Int16 = 0 To dt1.Rows.Count - 2
sheet.Cells.CopyRow(sheet.Cells, 2, 3 + i)
Next
Dim intTemplateRow As Int16 = 2
'delete the first row which used as a template to copy
sheet.Cells.DeleteRow(intTemplateRow)
sheet.Cells.ImportDataTable(dt1, False, 2, 0, False)
'Data Table 2
intTemplateRow = dt1.Rows.Count + 5
Dim dt2 As DataTable = dsCommissionSummary.Tables(2)
If dt2.Rows.Count > 1 Then
dt2.Columns.Remove("LineType")
sheet.Cells.InsertRows(dt1.Rows.Count + 6, dt2.Rows.Count - 2)
'sheet.Cells.Merge(dt1.Rows.Count + 6, 2, 0, 5)
For i As Int16 = 0 To dt2.Rows.Count - 3
sheet.Cells.CopyRow(sheet.Cells, intTemplateRow, dt1.Rows.Count + 6 + i)
Next
sheet.Cells.DeleteRow(intTemplateRow)
sheet.Cells.ImportDataTable(dt2, False, dt1.Rows.Count + 5, 0, False)
End If
'Omairs DT3 Test
intTemplateRow = dt1.Rows.Count + 5 + dt2.Rows.Count + 3
Dim dt3 As DataTable = dsCommissionSummary.Tables(3)
If dt3.Rows.Count > 1 Then
dt3.Columns.Remove("LineType")
sheet.Cells.InsertRows(dt1.Rows.Count + 6 + dt2.Rows.Count + 3, dt3.Rows.Count - 1)
For i As Int16 = 0 To dt3.Rows.Count - 2
sheet.Cells.CopyRow(sheet.Cells, intTemplateRow, dt1.Rows.Count + 6 + dt2.Rows.Count + 3 + i)
Next
sheet.Cells.DeleteRow(intTemplateRow)
sheet.Cells.ImportDataTable(dt3, False, dt1.Rows.Count + 5 + dt2.Rows.Count + 3, 0, False)
End If
'Omair DT4 Test
'intTemplateRow = dt1.Rows.Count + 5 + dt2.Rows.Count + 4 + dt3.Rows.Count + 3
intTemplateRow += dt3.Rows.Count + 3 + 1
Dim dt4 As DataTable = dsCommissionSummary.Tables(4)
If dt4.Rows.Count > 1 Then
dt4.Columns.Remove("LineType")
sheet.Cells.InsertRows(intTemplateRow, dt4.Rows.Count - 1)
For i As Int16 = 0 To dt4.Rows.Count - 2
sheet.Cells.CopyRow(sheet.Cells, intTemplateRow, intTemplateRow + i)
Next
sheet.Cells.DeleteRow(intTemplateRow)
sheet.Cells.ImportDataTable(dt4, False, intTemplateRow - 1, 0, False)
End If
Thanks,
S
Hi,
Since your scenario is a bit complicated. I think you may try to utilize some CellsHelper class's static methods to dynamically merge cells to adjust your data after filling data into the related cells for a specific data table. The class provides some useful static methods to get all the info about the cells like its, row/column index, name etc.
E.g..,
sheet.Cells.Merge(3, CellsHelper.ColumnNameToIndex("C"),3,1);
Thank you.