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

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.

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.

Wonderful

Fast, precise and perfect


Thank You

Hi,

You are always well come.

Good to know that it works for your requirements.

Have a good day!