Solved

program that can put a datatable into an excel spreadsheet

Posted on 2007-03-29
6
371 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:mathieu_cupryk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
ShazbotOK earned 500 total points
ID: 18817881
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
 

Author Comment

by:mathieu_cupryk
ID: 18817958
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
 
LVL 12

Expert Comment

by:ShazbotOK
ID: 18818077
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:mathieu_cupryk
ID: 18818107
Where are u passing the datatable?
0
 
LVL 12

Expert Comment

by:ShazbotOK
ID: 18818122
you would have to loop through the datatable rows/columns and call the AddItemToSpreadsheet function.  
0
 

Author Comment

by:mathieu_cupryk
ID: 18818179
Can you help me get started.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question