|
Hello
I want to export the data from ACCDB file to Excel and below is my code , i am able to download excel file but its empty pls help me.
string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\Test.accdb";
DataTable Data = new DataTable();
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand("select tb3.SeqNo as 'Session Sequence Number',tb3.SessionDate as 'Session Date',tb3.CustomerName as 'Customer Name',tb3.RepID as 'Rep ID',tb3.RepName as 'Rep Name',CaseRef as 'Ticket No',PracticeName as 'Practice Name',PostCode as 'Post Code',PhoneManner as 'Phone Manner',Satisfaction,iif (Satisfaction='EXCELLENT',5, iif (Satisfaction='VERY GOOD' ,4, iif (Satisfaction='GOOD' ,3, iif (Satisfaction='NEUTRAL' ,2, iif (Satisfaction='POOR' ,1))))) as Ratings,Ratings/5*100 as 'Percentage' , Consultant,CustomerComments as 'Customer Comments',Recommendation from tb1,tb3 where tb3.SeqNo=tb1.SeqNo and tb3.SessionDate between #" + fromdate + "# and #" + todate + "#", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(Data);
conn.Close();
dataGridView1.DataSource = Data;
Microsoft.Office.Interop.Excel.Application objexcelapp = new Microsoft.Office.Interop.Excel.Application();
objexcelapp.Application.Workbooks.Add(Type.Missing);
objexcelapp.Columns.ColumnWidth = 25;
for (int i = 1; i < gridviewID.Columns.Count + 1; i++)
{
objexcelapp.Cells[1, i] = gridviewID.Columns[i - 1].HeaderText;
}
for (int i = 0; i < gridviewID.Rows.Count; i++)
{
for (int j = 0; j < gridviewID.Columns.Count; j++)
{
if (gridviewID.Rows[i].Cells[j].Value != null)
{
objexcelapp.Cells[i + 2, j + 1] = gridviewID.Rows[i].Cells[j].Value.ToString();
}
}
}
MessageBox.Show("Your excel file exported successfully at d:\\" + excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.SaveCopyAs("d:\\" + excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.Saved = true;
}
}
|
|
|
|
|
Very good. solved my problems.
|
|
|
|
|
Help me a lot but i have reference problm.i cant find Excel.ApplicationClass reference.
modified on Tuesday, March 8, 2011 7:53 AM
|
|
|
|
|
|
The the PFGrid.NET has this feature included without using a COM-libary. Excel must not be installed on your computer to use the feature:
PFGrid.NET
|
|
|
|
|
Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
Dim excel As Excel.ApplicationClass = New ApplicationClass()
excel.Application.Workbooks.Add(True)
Dim dtDataTable As System.Data.DataTable = dsHoldTheData.Tables(0)
Dim ColumnIndex As Integer = 0
For Each col As DataColumn In dtDataTable.Columns
ColumnIndex += 1
excel.Cells(1, ColumnIndex) = col.ColumnName
Next
Dim rowIndex As Integer = 0
For Each row As DataRow In dtDataTable.Rows
rowIndex += 1
ColumnIndex = 0
For Each col As DataColumn In dtDataTable.Columns
ColumnIndex += 1
excel.Cells(rowIndex + 1, ColumnIndex) = row.Item(col.ColumnName).ToString
Next
Next
excel.Visible = True
Dim worksheet As Worksheet = DirectCast(excel.ActiveSheet, Worksheet)
worksheet.Activate()
End Sub
|
|
|
|
|
Thx for sharing the code, but kindly do help this newbie by telling that what if my dataGridview is populated using ReadXml() in my formload event?
What shall I do in this Export_ButtonClick function? Using the below code:
Excel.ApplicationClass excelSheet = new Excel.ApplicationClass();
excelSheet.Application.Workbooks.Add(true);
DataSet ds = new DataSet();
DataTable dt = ds.Tables[cdtext];
int columnIndex = 0;
foreach (DataGridViewColumn column in dt.Columns)
{
columnIndex++;
excelSheet.Cells[1, columnIndex] = column.HeaderText;
}
int rowIndex = 0;
foreach (DataGridViewRow row in dt.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataGridViewColumn column in dt.Columns)
{
columnIndex++;
excelSheet.Cells[rowIndex + 1, columnIndex] = row.Cells[column.Name].FormattedValue;
}
}
excelSheet.Visible = true;
Excel.Worksheet workSheet = (Excel.Worksheet)excelSheet.ActiveSheet;
((Excel._Worksheet)workSheet).Activate();
NullReferenceException is handled both in dt.Columns & dt.Rows. What should I do now?
|
|
|
|
|
Is the code you provided is for c#.net window application? can you please give more details for it.Like adding reference which excel should be selected.look forward for more.
|
|
|
|
|
Hi the article is great and working perfect.. !
Just a little thing I need is How to make the Column hearders bold and May be need them to make visualize by having different color.
Any Idea ??
Regards
|
|
|
|
|
How can I export from a DataGridView instead of a Dataset ?
Thanks.
|
|
|
|
|
This the modified code to use a datagridview instead of a dataset
using Excel = Microsoft.Office.Interop.Excel;
....
Excel.ApplicationClass excelSheet = new Excel.ApplicationClass();
excelSheet.Application.Workbooks.Add(true);
int columnIndex = 0;
foreach (DataGridViewColumn column in dgResults.Columns)
{
columnIndex++;
excelSheet.Cells[1, columnIndex] = column.HeaderText;
}
int rowIndex = 0;
foreach (DataGridViewRow row in dgResults.Rows)
{
rowIndex++;
columnIndex = 0;
foreach (DataGridViewColumn column in dgResults.Columns)
{
columnIndex++;
excelSheet.Cells[rowIndex + 1, columnIndex] = row.Cells[column.Name].FormattedValue;
}
}
excelSheet.Visible = true;
Excel.Worksheet workSheet = (Excel.Worksheet)excelSheet.ActiveSheet;
workSheet.Activate();
|
|
|
|
|
Hi. Nice addition to the article. Just what i needed. But what is dlResults? If it is a variable from DataGrid, it contains no reference to dgResults.Columns.
shamahamid wrote: foreach (DataGridViewColumn column in dgResults.Columns)
{
columnIndex++;
excelSheet.Cells[1, columnIndex] = column.HeaderText;
}
Please assist.
Wamuti: Any man can be an island, but islands to need water around them!
Edmund Burke: No one could make a greater mistake than he who did nothing because he could do only a little.
|
|
|
|
|
|
Hello, I'm a very beginner C# programmer. I don't know how to add Excel dll to references. Can somebody help me with this, please?. Also, I'm trying, after I click a button, to export the data in a datagrid, not a dataset, displayed on a window forms app an excel sheet.
Thank
|
|
|
|
|
hi
i have a datagrid/datagridview with some data in a form. how can I export this to excel with the code sample?
really need help,
thnx!
Mike
|
|
|
|
|
First of all, thx for this code.
It really came in handy.
I ran into a problem with incompatible values.
This happens when the language of excel is different the the windows environment settings.
you have to manually set the culture to the same one as excell
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
So my full code is = ( don't mind the timers .. it takes a while with 5000+ rows. So i added a progressbar since people will be doing this over vpn too)
private void export_To_XLS()<br />
{<br />
timer1.Start();<br />
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");<br />
<br />
Excel.ApplicationClass excel = new Excel.ApplicationClass();<br />
excel.Application.Workbooks.Add(true);<br />
System.Data.DataTable table = ds.Tables[0];<br />
int ColumnIndex=0;<br />
foreach(DataColumn col in table.Columns)<br />
{<br />
ColumnIndex++;<br />
excel.Cells[1,ColumnIndex]=col.ColumnName;<br />
}<br />
int rowIndex=0;<br />
foreach(System.Data.DataRow row in table.Rows)<br />
{<br />
rowIndex++;<br />
ColumnIndex=0;<br />
foreach(DataColumn col in table.Columns)<br />
{<br />
ColumnIndex++;<br />
excel.Cells[rowIndex+1,ColumnIndex]=row[col.ColumnName].ToString();<br />
}<br />
}<br />
excel.Visible = true;<br />
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;<br />
worksheet.Activate(); <br />
<br />
<br />
timer1.Stop();<br />
}
Hope it helps
xor
|
|
|
|
|
Hi,
My spreadsheet only shows the headings. It does not show the rest of the data. I wrote the code exactly as yours. Please help!
Mvelo Walaza
Developer
Telkom SA
|
|
|
|
|
i am unable to convert my xml data in to excel format and save it in workbook
shashank
|
|
|
|
|
I use an ArrayList item as the DataGrid's DataSource.
How do I export it in this case?
I'd also like to keep the formatting I defined with DataGridTableStyle.
Thanks!
|
|
|
|
|
I have datagrid with images in the header and checkbox as item template in the first column.
I need to export this datagrid in to excel.
For that first I need to replace these image buttons with literal text.
Can anyone help with this???
Thanks in Advance
Ashwini
|
|
|
|
|
Hi,
the source code is perfect! Thanks
Now i need to edit the cells styles : for example the background color,make text bold and so on...
How i can do that?
Thanks
|
|
|
|
|
Hi,
Your code is very usefull. But I have a problem.
I'm customizing dataset object and converting to excel but. I would like to export modified and not modified datasets as seperate worksheets.
How can I do it?
Thank you.
Serkan UZ
|
|
|
|
|
Hi serkanuz,
Easiest and fastest way to deal with Excel files in C# is with some 3rd party component like GemBox .NET Excel component. It is free for commercial use if you need less then 150 rows.
For more information how to use GemBox Spreadsheet check out a list of articles about component.
Filip
|
|
|
|
|
Hi.
I'm using Visual C# 2005 Express, and I've got Excel 2003.
I get an exception on this line:
excel.Application.Workbooks.Add(true);
COMException was unhandled.
Gammelt format eller ugyldig typebibliotek. -> something like...Old format or invalid type library.
Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
I also get a warning on this line.
worksheet.Activate();
Warning 1 Ambiguity between method 'Microsoft.Office.Interop.Excel._Worksheet.Activate()' and non-method 'Microsoft.Office.Interop.Excel.DocEvents_Event.Activate'. Using method group. C:\Stairway2.0\Stairway program\Stairway2.0\Stairway2.0\Table.cs 91 23 Stairway2.0
|
|
|
|
|
I found the solution for the exception.
All info is here: http://support.microsoft.com/kb/320369
But I still got the warning though.
|
|
|
|