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
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