program that can put a datatable into an excel spreadsheet

I need to look at a program that can put a datatable into an excel spreadsheet in asp.net 2.0 vb.net

Customer      Shopper ID      Year      Period No      Period Name      Prestige Gross Sales      Prestige Net Sales      Prestige Discount      Nb. Prestige Transactions
256039201      1      2005      12      Jan      64      50.15      -5.6      3

this is a datatable using the viewer built in.
mathieu_cuprykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShazbotOKCommented:
create a class file for this code and set a reference to the  Microsoft.Office.Interop.Excel
after that set this:

Using Excel = Microsoft.Office.Interop.Excel;
public class ExcelPush
{
        Excel.ApplicationClass _excel = new Excel.ApplicationClass();

        public ExcelPush()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Excel.Worksheet Activate()
        {
            // open new excel spreadsheet
            try
            {
                Excel.Workbook workbook = _excel.Workbooks.Add(Type.Missing);
                _excel.Visible = true;
                Excel.Worksheet ws = (Excel.Worksheet)_excel.ActiveSheet;
                ws.Activate();
                return ws;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            return null;
        }


        public void AddItemToSpreadsheet(int row, int column, Excel.Worksheet ws, string item)
        {
            ((Excel.Range)ws.Cells[row, column]).Value2 = item;
        }
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mathieu_cuprykAuthor Commented:
Can you help me go back to vb.net
Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
        Try
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
 
            xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
            xlWorkBook.Application.Visible = True
            xlWorkSheet = xlWorkBook.ActiveSheet
 
            '   Gets the dataset containing the data
            Dim dsData As DataSet = getData()
            Dim i As Integer = 2
 
            '   Outputting the fieldnames in pink bold color
            xlWorkSheet.Cells(1, 1) = "Student ID"
            xlWorkSheet.Cells(1, 2) = "Student Name"
            xlWorkSheet.Cells(1, 3) = "Mathematics"
            xlWorkSheet.Cells(1, 4) = "Geography"
            xlWorkSheet.Cells(1, 5) = "Total"
 
            xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
            xlWorkSheet.Range("$A1:$E1").Font.Bold = True
 
            '   Outputting the data
            For Each dr As DataRow In dsData.Tables(0).Rows
                xlWorkSheet.Cells(i, 1) = dr(0)
                xlWorkSheet.Cells(i, 2) = dr(1)
                xlWorkSheet.Cells(i, 3) = dr(2)
                xlWorkSheet.Cells(i, 4) = dr(3)
 
                '   Building the formula for calculating the sum
                xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())
 
                '   Going to the next row
                i = i + 1
            Next
 
            '   Auto fit the columns
            xlWorkSheet.Columns.AutoFit()
 
            '   Generating the graph
            Dim chart As Excel.Chart
            chart = xlWorkBook.Charts.Add()
 
            With chart
                .ChartType = Excel.XlChartType.xlColumnClustered
                .SetSourceData(xlWorkSheet.Range("A1:E11"), 2)
 
                .HasTitle = True
                .ChartTitle.Characters.Text = "Students' marks"
 
                .Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
                .Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
            End With
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
0
ShazbotOKCommented:
My VB.Net is far to rusty... however you could just use the C# class file as a separate project that your vb app would reference calls...  sorry.. I am Pro-CSharp
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mathieu_cuprykAuthor Commented:
Where are u passing the datatable?
0
ShazbotOKCommented:
you would have to loop through the datatable rows/columns and call the AddItemToSpreadsheet function.  
0
mathieu_cuprykAuthor Commented:
Can you help me get started.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.