Add new rows in a spreadsheet with the total at the bottom of the page

Aspose.Cells Product Family
1

Hello i’m using an console application in vb.net 3.5


it’s possible to insert new rows from array in worksheet?

I attached an excel sample.

I must add a new array in a new row, under the date “04/05/2011”
This procedure will be done every time I start the application (adding a new line)

It is possible by adding rows, so that the total at bottom will not be overwritten?


In this example I add an array in the last line:

Sub start()
Dim xls_folder_name As String = “GMAQITRX.006”
Dim xls_total_docs, xls_total_pages As Integer
xls_total_docs = 55
xls_total_pages = 77
Dim xls_file_name As String = Now.ToString(“MM-yyyy”) & “.xls”


ExcelWriter(xls_file_name, xls_folder_name, xls_total_docs, xls_total_pages)

End Sub

Public Sub ExcelWriter(ByVal filename As String, ByVal xls_folder_name As String, ByVal xls_total_docs As Integer, ByVal xls_total_pages As Integer)

Dim fln As New FileInfo(filename)

If fln.Exists Then
'Creating a Workbook object and opening an Excel file using its file path
Dim workbook As Workbook = New Workbook(filename)


Dim sheet As Worksheet = workbook.Worksheets(0)

ImportArrayList(sheet, xls_folder_name, xls_total_docs, xls_total_pages)
workbook.Save(filename)
Else
'Creating a new Workbook object
Dim workbook As Workbook = New Workbook()


Dim sheet As Worksheet = workbook.Worksheets(0)
ImportArrayList(sheet, xls_folder_name, xls_total_docs, xls_total_pages)
workbook.Save(filename)
End If
End Sub

Private Sub ImportArrayList(ByVal sheet As Worksheet, ByVal xls_nome_scatola As String, ByVal xls_totale_doc As Integer, ByVal xls_totale_pagine As Integer)

Dim datetime As DateTime = Now()
Dim date_folder1 As String = datetime.ToString(“dd/MM/yyyy”)

'Import Data from an ArrayList
If sheet.Cells(“A1”).Value = “DATE” Then

Else
sheet.Cells(“A1”).PutValue(“DATE”)
sheet.Cells(“B1”).PutValue(“FOLDER NAME”)
sheet.Cells(“C1”).PutValue(“TOTAL DOCS”)
sheet.Cells(“D1”).PutValue(“TOTAL PAGES”)
End If

Dim next_row As Integer = sheet.Cells.End.Row + 1

Dim list As ArrayList = New ArrayList()
list.Add(date_folder1)
list.Add(xls_nome_scatola)
list.Add(xls_totale_doc)
list.Add(xls_totale_pagine)

sheet.Cells.ImportArrayList(list, next_row, 0, False)

End Sub



Any help is appreciated

A.N.

2

Hi,

I have evaluated your case. I think you may change your logic a bit accordingly. I have changed your code a bit, see the updated code below. It may suit your requirement. Please run my code with your template file, hopefully it will be fine for your requirement. You may run/close the console application continuously adding some values (although these would be constant values here, you may add/update code segments to it accordingly etc.) to the files and finally see the output file after some iteration (run/close) for adding values to the file. You may also change/update my code accordingly for your needs.

Sample code (Console Application):

Imports Aspose.Cells
Imports System.IO
Module Module1

Sub Main()
Dim xls_folder_name As String = “GMAQITRX.006”
Dim xls_total_docs, xls_total_pages As Integer
xls_total_docs = 55
xls_total_pages = 77

Dim xls_file_name As String = “e:\test2\05-2011” & “.xls”


ExcelWriter(xls_file_name, xls_folder_name, xls_total_docs, xls_total_pages)
End Sub
Public Sub ExcelWriter(ByVal filename As String, ByVal xls_folder_name As String, ByVal xls_total_docs As Integer, ByVal xls_total_pages As Integer)

Dim fln As New FileInfo(filename)

If fln.Exists Then
'Creating a Workbook object and opening an Excel file using its file path
Dim workbook As Workbook = New Workbook(filename)


Dim sheet As Worksheet = workbook.Worksheets(0)

ImportArrayList(sheet, xls_folder_name, xls_total_docs, xls_total_pages)
workbook.Save(filename)

Else
'Creating a new Workbook object
Dim workbook As Workbook = New Workbook()


Dim sheet As Worksheet = workbook.Worksheets(0)
ImportArrayList(sheet, xls_folder_name, xls_total_docs, xls_total_pages)
workbook.Save(filename)

End If
End Sub

Private Sub ImportArrayList(ByVal sheet As Worksheet, ByVal xls_nome_scatola As String, ByVal xls_totale_doc As Integer, ByVal xls_totale_pagine As Integer)

Dim datetime As DateTime = Now()
Dim date_folder1 As String = datetime.ToString(“dd/MM/yyyy”)

'Import Data from an ArrayList
If sheet.Cells(“A1”).Value = “DATE” Then

Else
sheet.Cells(“A1”).PutValue(“DATE”)
sheet.Cells(“B1”).PutValue(“FOLDER NAME”)
sheet.Cells(“C1”).PutValue(“TOTAL DOCS”)
sheet.Cells(“D1”).PutValue(“TOTAL PAGES”)
End If

Dim lastrow As Integer = sheet.Cells.End.Row
'Add a new sheet, this sheet would be temporary.
Dim hsheet As Worksheet = sheet.Workbook.Worksheets.Add(“hsheet”)
'Copy the grand total row to that sheet.
hsheet.Cells.CopyRow(sheet.Cells, lastrow, lastrow)
'Delete the original row in the sheet.
sheet.Cells.DeleteRow(lastrow)

'Now get the next row to the last row
Dim next_row As Integer = sheet.Cells.End.Row + 1



Dim list As ArrayList = New ArrayList()
list.Add(date_folder1)
list.Add(xls_nome_scatola)
list.Add(xls_totale_doc)
list.Add(xls_totale_pagine)

'Copy the row back to its original position.
sheet.Cells.CopyRow(hsheet.Cells, lastrow, lastrow)
'Important: you need to insert row every time you import the array list.
sheet.Cells.InsertRow(next_row)
'Import the array list.
sheet.Cells.ImportArrayList(list, next_row, 0, False)
'Remove the temporary sheet now.
sheet.Workbook.Worksheets.RemoveAt(hsheet.Name)

End Sub


End Module


Thank you.

3

Wonderful

Fast, precise and perfect


Thank You
4

Hi,

You are always well come.

Good to know that it works for your requirements.

Have a good day!