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
Solved

program that can put a datatable into an excel spreadsheet

Posted on 2007-03-29
6
361 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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