Using Excel

[John Wright]

I have a datatable I need to export to excel and display on the screen. The
users will take the excel table and do some work with it and save it,
discard it etc. I am using Excel 2002 and vb.net 2005. I tried to use the
Microsoft.Office.Tools.Excel namespace but I am having problems. I assume I
need to create an application the workbook then worksheet but I am having
problems with this. Does anyone have some starting code to get me going, I
think once I have started I can get going. Thanks.

John

 

[Rick]

One way to easily "automate" this is to use the built in Windows Report
Viewer. Its relatively easy to get a single datatable to show in a report
and then you can have the user click on the export button (in the report
viewer) and export to Excel (also Pdf).

I think you can automate this export as well, but I have not done it.

Rick

 

[John Wright]

Thanks for the suggestion. I have mulled this over, but need to have excel
pop up after the export. I was able to get it done with some code I found
on the net and cobbled something together that works great.

John

 

[Kevin S Gallagher]

John,

The code below uses automation which has a reference to [Microsoft Excel
10.0 Object Library] requiring no database. Consider it code to pick things
out of, nothing fancy.

Private Sub Demo()
Dim ExcelApp As New Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelSheet As New Excel.Worksheet

Try
ExcelApp = DirectCast(CreateObject("Excel.Application"),
Excel.Application)
ExcelApp.Visible = True

ExcelWorkbook = ExcelApp.Workbooks.Add
ExcelSheet = DirectCast(ExcelWorkbook.ActiveSheet, Excel.Worksheet)
Catch ex As Exception
Console.WriteLine(ex.Message)
My.Application.ExceptionDialog("Failed to create needed COM/Excel
objects")
Exit Sub
End Try

With ExcelSheet.Range("A1")
.AddComment("Report Title goes here..whatever they want")
.Font.Bold = True
.Font.Size = 10
.Value = "Report Title for the report"
.ColumnWidth = 19.86
End With

With ExcelSheet.Range("A2")
.Font.Bold = True
.Font.Size = 10
.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
.Value = Now.Month.ToString & "/" & Now.Day.ToString & "/" &
Now.Year.ToString
End With

With ExcelSheet.Range("A5")
.Font.Bold = True
.Font.Size = 10
.Value = "Equipment Data"
.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End With

With ExcelSheet.Range("A6")
.Font.Bold = True
.Font.Size = 10
.Value = "Industry"
.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End With

With ExcelSheet.Range("B6")
.Font.Size = 10
.Value = "Food"
End With

With ExcelSheet.Range("A7")
.Font.Bold = True
.Font.Size = 10
.Value = "Other Ind. (Lib Filing)"
.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End With

With ExcelSheet.Range("B7")
.Font.Size = 10
.Value = "Data data ---- Equipment Type ---- data data data data
data"
End With


' More fields here


With ExcelSheet.Range("B16")
.Font.Bold = True
.Font.Size = 10
.Value = "Comments"
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
End With

With ExcelSheet.Range("B17")
.ColumnWidth = 50
.Font.Size = 10
.Value = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit,
sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat
volutpat. "
.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
.VerticalAlignment = Excel.XlVAlign.xlVAlignTop
.Orientation = 38
.WrapText = True
End With
Try
ExcelWorkbook.SaveAs("C:\test.xls")
ExcelApp.Quit()
Catch ex As Exception
End Try
End Sub

Other thoughts if you had your version of Office with the lastest patches
starting I believe with Office 2003 is to using file i/o and create your
Excel files using Office XML. I have some start up code if you like pop me
an email.

 

[Rad [Visual C# MVP]]

Thanks for the suggestion. I have mulled this over, but need to have excel
pop up after the export. I was able to get it done with some code I found
on the net and cobbled something together that works great.

John

[23 quoted lines suppressed]

Like Rick says, it should be possible to automate the Excel export to a
file. To pop up excel you can invoke a Process.Start and give it the excel
file as the file and it should launch excel and open the file