Exporting dataset to Excel

Aspose.Cells Product Family
1

I have a dataset filling a datagrid and want to export that to Excel Spreadsheet when I push the Expoprt to Excel Button. When I do click the button I do not get the spreadsheet.

Here is my code, what am I doing wrong???

Excel query = new Excel();

Excel.SetLicense(“C:\license.rtf”);

Worksheet sheet = query.Worksheets[0];

DataTable dt = sheet.Cells.ExportDataTable(1,1,35000, 20);

this.dataGrid1.DataSource = dt;

query.Save(“C:\Query.xls”, Aspose.Excel.FileFormatType.Excel2003);

2

How do you create your dataset? If you want to export data to an Excel file from a datagrid, please try this:

Excel query = new Excel();


Excel.SetLicense("C:\\license.rtf"); //Have you bought a license? If not, please remove this line of code. You will get a license file after you buy the license. License.rtf in setup package only includes license announcement.

Worksheet sheet = query.Worksheets[0];

sheet.Cells.ImportDataGrid(this.dataGrid1, 1, 1, this.dataGrid1.Items.Count, this.dataGrid1.Columns.Count);

query.Save("C:\\Query.xls", Aspose.Excel.FileFormatType.Excel2003);


3

OK I did that and now get this error when buiding

c:\inetpub\wwwroot\QueryPage\WebForm1.aspx.cs(130): No overload for method ‘ImportDataGrid’ takes ‘5’ arguments

4

Sorry, I made a mistake in the sample code. Please change it to:

sheet.Cells.ImportDataGrid(this.dataGrid1, 1, 1, this.dataGrid1.Items.Count, this.dataGrid1.Columns.Count, true);

5

Dear Lawrence,

I too am doing some evaluation and so following this blog. I did exactly as above and it did work but somehow skips the first column. There are also other column missing and so didnot know what the problem is. There are template columns in the Datagrid but did not skip all template columns. Pl see attchmt. Thanx!

Cheers
Pavan

6

Hi Paven,

Which version are you using? Could you please post your project here? Thank you.

7

Dear Lawrence,

You are asking about the Aspose Excel version I believe - 3.1.0.0
Posting the project? Its too big to post. Dont get me wrong - Let me know which code you need.
For now I am pasting the code for the export:

Dim query As Excel = New Excel()

Dim sheet As Worksheet = query.Worksheets(0)

sheet.Cells.ImportDataGrid(MyDataGrid, 0, 0, MyDataGrid.Items.Count, MyDataGrid.Columns.Count, True)

query.Save("C:\\Query.xls", Aspose.Excel.FileFormatType.Excel2000)

I am using VS.NET studio 2003. Let me know what else you need.

Cheers
Pavan

8

I don’t clearly understand the problem. What column are missing? Could you please post the screenshot on your web page with the datagrid? And please post your generated file. Thus I can see what happed in your place.

Thanks.

9

Dear Lawrence,

Its in the attachement inmy first mail. However sending again…
The columns not missing but the data is. You could compare the datagrid and the Excel file to see the difference.

Cheers
Pavan

10

Hi Pavan,

1. ImportDataGrid method only imports data in BoundColumn.
2. In your doc file, do you mean the top image is the screenshot of you datagrid and the bottom table is data in your excel file?
3. Are column “Carrier”, “20dv”-“40BAF” lost?
4. Aren’t data in “POL” column correct?

11

Please try:

sheet.Cells.ImportDataGrid(MyDataGrid, 0, 0, MyDataGrid.Items.Count, 17, True)

DataGrid.Columns only return column set at design time. It doesn’t return columns generated at run time.

For date time columns, please check : Setting Display Formats of Number and Dates