?
Solved

program that can put a datatable into an excel spreadsheet

Posted on 2007-03-29
6
Medium Priority
?
390 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 1500 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

601 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