?
Solved

How to save data from database to Excel worksheet

Posted on 2009-04-05
32
Medium Priority
?
1,221 Views
Last Modified: 2012-06-21
Hello all,

I'm new to C#.

I've made a simple database with a NAME grid and LASTNAME grid.

I want to save the data that I write in but when I press the SAVE button the data doesn't shown at my next run.

What is the best and easiest way to save my data?

And I want to save it to an Excel worksheet.
0
Comment
Question by:liorb
[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
  • 16
  • 15
32 Comments
 
LVL 6

Expert Comment

by:Ramone_Hamilton
ID: 24071958
I'm a little confused as to what is saving data where?  Do you have an application that you've created that's saving this data to the database/excel?  If so could you post a sample of the cpde that you added on your onClick event for your save button.
0
 
LVL 1

Author Comment

by:liorb
ID: 24075151
Ramon Hello

I've made a simple database with one Table that has two columns 'First Name' & 'Last Name'

I didn't wrote any code about saving the data from the table to an Excel worksheet, because I don't know how to do that.
Can you or anyone help me with that???
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24076852
do u know to extract data from sql table .. ? or u want code for retrieving data from SQl and saving in excel
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 200 total points
ID: 24076866
the following code will dump the data in a datatable to an excel file
Excel.Application ea = new Excel.ApplicationClass();
 
Excel.Workbook wb = ea.Workbooks.Open(template, 0, true, 5, "", "", true, 
Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
 
string currentSheet = "Daily Data";
 
Excel.Sheets eSheets = wb.Worksheets;
Excel.Worksheet eWs = (Excel.Worksheet)eSheets.get_Item(currentSheet);
                  
ea.Visible = true;
                  
DataTable table = ds.Tables[0]; 
                  
int ColumnIndex=0; 
foreach(DataColumn col in table.Columns) 
{ 
      ColumnIndex++; 
      eWs.Cells[1,ColumnIndex]=col.ColumnName;
} 
 
int count = table.Rows.Count;
 
int rowIndex=0; 
foreach(DataRow row in table.Rows) 
{ 
      rowIndex++; 
 
      Console.WriteLine(count + " out of " + rowIndex);
      ColumnIndex=0; 
      foreach(DataColumn col in table.Columns) 
      { 
            ColumnIndex++;
            eWs.Cells[rowIndex+1,ColumnIndex] = row[col.ColumnName];
      } 
} 
 
Excel.Worksheet worksheet = (Excel.Worksheet)ea.ActiveSheet; 
worksheet.Activate(); 
}

Open in new window

0
 
LVL 1

Author Comment

by:liorb
ID: 24078206
Thanks for the code

But the EXCEL word is not recognized!

What should I do to fix the problem?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24078388
u have to add the excel library from the com tab..
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24078423
th name will be Micorosoft.Excel.5.0 Object Library, the version will change according to the office vesion installed in ur machine
0
 
LVL 1

Author Comment

by:liorb
ID: 24078808
com tab????
adding Excel library - How do I do that???

Sorry but I'm very new to C#

Thanks
0
 
LVL 1

Author Comment

by:liorb
ID: 24080852
Figured it out - the way to add the Excel library!

In line 3

Excel.Workbook wb = ea.Workbooks.Open(template, .......

The word "template" is not recognized in my code, what this string represent?
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24085808
'template' will be the path of the excel u want to open.
0
 
LVL 1

Author Comment

by:liorb
ID: 24100674
OK

You forgot to place " before and after the 'template' word
But still I keep getting this error:

Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))

How can I solve it???
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24104237
when are u getting this error ?
0
 
LVL 1

Author Comment

by:liorb
ID: 24105146
I get the error at the OBJECT ORIGIN point

Here is the code line:

Excel.Workbook wb = ea.Workbooks.Open("myStringFile", 3, false , 1,"" ,"" , false , Excel.XlPlatform.xlWindows , "", false , false ,"" , true , true ,"" );
I've done some searching on the internet and found that if I change my regional settings to English(united states) the error will be solved - and it did!
But now the error changed on the same OBJECT ORIGIN point.
This is the new error:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24106556

Excel.Workbook wb = ea.Workbooks.Open("", 3, false , 1,"" ,"" , false , Excel.XlPlatform.xlWindows , "", false , false ,"" , true , true ,"" );

Open in new window

0
 
LVL 1

Author Comment

by:liorb
ID: 24114321
No
Don't work!
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24117226
are u getting the same exception ?...
0
 
LVL 1

Author Comment

by:liorb
ID: 24122793
I wrote this code

I've started with populating the HEADERS first
I got an error on this line :
foreach (DataColumn col in table .Columns )
the error points to the table.columns and is as follows :
Object reference not set to an instance of an object.
How to solve that?
I'm sure that somthing missing but don't know what.


Microsoft.Office.Interop.Excel.Workbook book;
Microsoft.Office.Interop.Excel.Worksheet sheet;
Microsoft.Office.Interop.Excel.Application application;
 
application = new Microsoft.Office.Interop.Excel.ApplicationClass();
            
book = application.Workbooks.Add("");
 
sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
sheet.Name = "myStringFile";
 
Excel.Application ea = new Excel.ApplicationClass();
 
Excel.Workbook wb = ea.Workbooks.Open("myStringFile", 0, true, 5,
                Type.Missing, Type.Missing, true,
            Excel.XlPlatform.xlWindows, Type.Missing,
            true, false, 0, true, false, false);
                      
DataSet ds = new DataSet("testingdataDataSet");
DataTable table = ds.Tables["switch_detailsTableAdapter" ];
 
int ColumnIndex = 0;
foreach (DataColumn col in table .Columns )
{
   ColumnIndex++;
   sheet.Cells[1, ColumnIndex] = col.ColumnName;                
}
 
book.SaveAs("D:\\Visual Projects\\testingData\\myStringFile",
                Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12 ,
                Type .Missing , Type .Missing , false,
                false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Type .Missing , Type .Missing , Type .Missing ,
                Type .Missing , Type .Missing );
 
book.Close(true, Type .Missing , Type .Missing );
application.Visible = true; //true=open Excel ; false=don't open Excel
 
application.Quit();

Open in new window

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24124575
change
DataTable table = ds.Tables["switch_detailsTableAdapter" ];
to
DataTable table = ds.Tables[0 ]; and try
 
0
 
LVL 1

Author Comment

by:liorb
ID: 24124630
Still an error :

"Cannot find table 0."
 
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24125715
u are just initializing it and there wont be any data int it ? what are u trying to write to the excel and where does the data come from ?
DataSet ds = new DataSet("testingdataDataSet");
DataTable table = ds.Tables["switch_detailsTableAdapter" ];
 
0
 
LVL 1

Author Comment

by:liorb
ID: 24125936
Very simple
As I wrote before - I have made a simple data base in C#
The DATASET is called "testingdataDataSet"
In it I have made a simple TABLE with 4 parameters(columns): "switchID, switchName, switchLast, switchLocation"

What the program supose to do is to get the data that I write into those parameters and save/load them into/from an excel sheet.
And thats all !

I have two problems in which you helped me greatly!

The first one is when I press the save button the program don't keep the data I write into it when I rerun the program.
also I cann't see the data I write during running mode in editing mode when pressing "preview data"

The second one is saving and loading data to and from an Excel sheet (on that problem we focused on)
I hope that now it's more clear
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24127989
is the testingdataDataSet created using the designer in VS ? the u can instantiate it using the proxy class testingdataDataSet data = new testingdataDataSet(); then u can use the data.Load() function to load the data using the connection u specified in the desginer.then u can use that data set instead of DataSet ds = new DataSet("testingdataDataSet");
0
 
LVL 1

Author Comment

by:liorb
ID: 24132169
So the code should be somthing like this???

testingdataDataSet data = new testingdataDataSet();
data.load();  //don't know what to write in the brackets
DataSet ds = new DataSet("data");
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24135005
testingDataSet is the data set where u will have the data and u dont need to initialize a new data set ( DataSet ds = new DataSet("data");)
0
 
LVL 1

Author Comment

by:liorb
ID: 24136501
OK

What should be in the brackets of the data.load(???)
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24137317
if testdataset is generated using the VS designer it will have a load function without any parameters which loads the data into the dataset from the data source u specified
0
 
LVL 1

Author Comment

by:liorb
ID: 24139168
OK
Did it the columns headers are saved in the Excel worksheet

And for the last part - populating the rows
I've copied your code and nothing happens!
The rows in the Excel sheet don't fill up.

Here is the code:


private void button2_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Workbook book;
            Microsoft.Office.Interop.Excel.Worksheet sheet;
            Microsoft.Office.Interop.Excel.Application myApplication;
 
            myApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
            
            book = myApplication.Workbooks.Add(Type .Missing );
 
            sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
            sheet.Name = "myStringFile";
 
            Excel.Application ea = new Excel.ApplicationClass();
 
            testingdataDataSet data = new testingdataDataSet();
           
            DataTable myTable = data.Tables[0];
                            
 
            int ColumnIndex = 0;
            
 
                foreach (DataColumn myColumn in myTable.Columns)
                {
                    ColumnIndex++;
                    sheet.Cells[1, ColumnIndex] = myColumn.ColumnName;
                    sheet.Columns.AutoFit();
                }
 
                int count = myTable.Rows.Count;
 
                int rowIndex = 0;
                foreach (DataRow row in myTable.Rows)
                {
                    rowIndex++;
 
                    Console.WriteLine(count + " out of " + rowIndex);
                    ColumnIndex = 0;
                    foreach (DataColumn col in myTable.Columns)
                    {
                        ColumnIndex++;
                        sheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
                    }
                } 
 
            
 
            book.SaveAs("D:\\Visual Projects\\testingData\\myStringFile",
                Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12 ,
                Type .Missing , Type .Missing , false,
                false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                false , Type .Missing , Type .Missing ,
                Type .Missing , true  );
 
            book.Close(true, "myStringFile",true );
            myApplication.Visible = true; //true=open Excel ; false=don't open Excel
                        
            myApplication.Workbooks.Close();
            myApplication.Quit();
        }

Open in new window

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24144988
testingdataDataSet data = new testingdataDataSet();
data.Load();          
DataTable myTable = data.Tables[0];
0
 
LVL 1

Author Comment

by:liorb
ID: 24145151
The "data.load();" is marked with a red line
Somthing must be written in the brackets !

Will the - "data.load" - fill the cells in the Excel file???
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24154821
no data.Load loads data into the dataset and from the dataset u populate into the excel using the rest of the code .. U need to check the TestingDataSet class or use intellisense to see what is the overload for the Load method.
0
 
LVL 1

Author Comment

by:liorb
ID: 24155723
With the intelisense the data.load() needs:

IDataReader reader , loadoption loadoption , params string[] tables

I don't know what to place in each one of them
What should I do now???

0
 
LVL 1

Accepted Solution

by:
liorb earned 0 total points
ID: 24166084
OK
Made some more advancment
But I solve one problem and a new one pops up

I know what to write inside the data.load() brackets and for the I need to write some more code
For the reader I need to write this line:

DataTableReader myReader = GetReader();

But the getreader() is marked red - the error says "does not exist in the current context"
How do I solve that????

Here is the code:


private void button2_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Workbook book;
            Microsoft.Office.Interop.Excel.Worksheet sheet;
            Microsoft.Office.Interop.Excel.Application myApplication;
 
            myApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); //Start the EXCEL application
            
            book = myApplication.Workbooks.Add(""); //Adds a new Workbook
 
            sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets[1];
            sheet.Name = "myStringFile"; //Sets the name of the EXCEL file.
            DataTable testingData = new DataTable();
            DataTableReader myReader = GetReader(); 
            testingdataDataSet data = new testingdataDataSet();
            data.Load(myReader ,LoadOption.OverwriteChanges , testingData );
            DataTable myTable = data.Tables[0];
                  
            //Fill the columns headers
            int ColumnIndex = 0;
            
                foreach (DataColumn myColumn in myTable.Columns)
                {
                    ColumnIndex++;
                    sheet.Cells[1, ColumnIndex] = myColumn.ColumnName;
                    sheet.Columns.AutoFit();                    
                }
 
            //Fill the rows
            int count = myTable.Rows.Count;
            int rowIndex = 0;
 
            foreach (DataRow row in myTable.Rows)
            {
                rowIndex++;
 
                Console.WriteLine(count + " out of " + rowIndex);
                ColumnIndex = 0;
 
                foreach (DataColumn col in myTable.Columns)
                {
                    
                    ColumnIndex++;
                    sheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName];
                }
 
            } 
 
            book.SaveAs("D:\\Visual Projects\\testingData\\myStringFile",
                Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel12 ,
                Type .Missing , Type .Missing , false,
                false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                false , Type .Missing , Type .Missing ,
                Type .Missing , true  );
 
            book.Close(true, "myStringFile",true );
            myApplication.Visible = true; //true=open Excel ; false=don't open Excel
            myApplication.Workbooks.Close();
            myApplication.Quit();
                                                
        }

Open in new window

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

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses

770 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