Friday, August 2, 2013

to add / append / ammend a new column to existing excel file in asp.net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Private Sub ExcelColumnAppend(ByVal ExcelData As DataSet)
        Dim excelApp As Microsoft.Office.Interop.Excel.Application
        Dim workBook As Microsoft.Office.Interop.Excel.Workbook
        Dim workSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim ds As DataSet
        Dim RowNum As Integer = 0
        Dim ColumnNum As Integer = 0
        Try
            ds = New DataSet
            ds = ExcelData
            Dim MyExcel As Object = CreateObject("Excel.Application")
            If MyExcel Is Nothing Then
                WebMessage("Unable to generate excel file")
                Return
            End If
            System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
            MyExcel = Nothing
            Dim SourceFilePath As String = Server.MapPath("~/Data/Test.xlsx")
            Dim DestFilePath As String = Server.MapPath("~/Data/Test_Modified.xlsx")
            excelApp = New Microsoft.Office.Interop.Excel.Application
            workBook = excelApp.Workbooks.Open(SourceFilePath)
            workSheet = workBook.Worksheets("Sheet1")
            workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape
            excelApp.DisplayAlerts = False
            With workSheet
                RowNum = 2
                .Cells(1, 21).value = "AppendedColumn"
                For Each DR As DataRow In ds.Tables(0).Rows
                        For ColumnNum = 21 To ds.Tables(0).Columns.Count
                            .Cells(RowNum, 21).value = DR(21 - 1).ToString().Trim()
                        Next
                    RowNum = RowNum + 1
                Next
            End With
            workSheet.SaveAs(DestFilePath)
            workBook.Close()
            workBook = Nothing
            excelApp.Quit()
            excelApp = Nothing
           'To Open file
            Response.AddHeader("Content-Disposition", "attachment; filename=" & Path.GetFileName(DestFilePath))
            Response.WriteFile(DestFilePath)
            Response.Flush()
            Response.Close()
         Catch ex As Exception
            WebMessage(ex.ToString())
        End Try
    End Sub

No comments:

Post a Comment