liorb
asked on
How to save data from database to Excel worksheet
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.
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.
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.
ASKER
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???
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???
do u know to extract data from sql table .. ? or u want code for retrieving data from SQl and saving in excel
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the code
But the EXCEL word is not recognized!
What should I do to fix the problem?
But the EXCEL word is not recognized!
What should I do to fix the problem?
u have to add the excel library from the com tab..
th name will be Micorosoft.Excel.5.0 Object Library, the version will change according to the office vesion installed in ur machine
ASKER
com tab????
adding Excel library - How do I do that???
Sorry but I'm very new to C#
Thanks
adding Excel library - How do I do that???
Sorry but I'm very new to C#
Thanks
ASKER
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?
In line 3
Excel.Workbook wb = ea.Workbooks.Open(template
The word "template" is not recognized in my code, what this string represent?
'template' will be the path of the excel u want to open.
ASKER
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???
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???
when are u getting this error ?
ASKER
I get the error at the OBJECT ORIGIN point
Here is the code line:
Excel.Workbook wb = ea.Workbooks.Open("myStrin gFile", 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))
Here is the code line:
Excel.Workbook wb = ea.Workbooks.Open("myStrin
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))
Excel.Workbook wb = ea.Workbooks.Open("", 3, false , 1,"" ,"" , false , Excel.XlPlatform.xlWindows , "", false , false ,"" , true , true ,"" );
ASKER
No
Don't work!
Don't work!
are u getting the same exception ?...
ASKER
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.
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();
change
DataTable table = ds.Tables["switch_detailsT ableAdapte r" ];
to
DataTable table = ds.Tables[0 ]; and try
DataTable table = ds.Tables["switch_detailsT
to
DataTable table = ds.Tables[0 ]; and try
ASKER
Still an error :
"Cannot find table 0."
"Cannot find table 0."
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("testingdataDataSe t");
DataTable table = ds.Tables["switch_detailsT ableAdapte r" ];
DataSet ds = new DataSet("testingdataDataSe
DataTable table = ds.Tables["switch_detailsT
ASKER
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
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
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("testingdataDataSe t");
ASKER
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");
testingdataDataSet data = new testingdataDataSet();
data.load(); //don't know what to write in the brackets
DataSet ds = new DataSet("data");
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");)
ASKER
OK
What should be in the brackets of the data.load(???)
What should be in the brackets of the data.load(???)
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
ASKER
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:
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();
}
testingdataDataSet data = new testingdataDataSet();
data.Load();
DataTable myTable = data.Tables[0];
data.Load();
DataTable myTable = data.Tables[0];
ASKER
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???
Somthing must be written in the brackets !
Will the - "data.load" - fill the cells in the Excel file???
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.
ASKER
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???
IDataReader reader , loadoption loadoption , params string[] tables
I don't know what to place in each one of them
What should I do now???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.