Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Sep 14, 2018 12:24 pm

Hi, I've created a simple database windows form app using LINQ to SQL. Can I use LINQ to SQL to export Datatable to Excel from Database using spire.XLS?
I've uploaded my project, if that helps :|
Attachments
testdbAutoIncrement.zip
My solution file(VS2015 community)
(76.72 KiB) Downloaded 317 times

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Mon Sep 17, 2018 8:07 am

Hi,

Thanks for your inquiry and sorry for late reply as weekend.
Please add below code to export datatable to Excel. If there is any question, welcome to write back.
Code: Select all
private void ExportToExcel_Click(object sender, EventArgs e)
{
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];

    //Export datatable to excel
    sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 1, 1, -1, -1);
   
    sheet.AllocatedRange.AutoFitColumns();
    sheet.AllocatedRange.AutoFitRows();

    //Save the file
    workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
}

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Mon Sep 17, 2018 9:59 am

Hi Nina,
I'm getting the following error in the line of code
Code: Select all
sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 1, 1, -1, -1);

An unhandled exception of type 'System.InvalidCastException' occurred in testdbAutoIncrement.exe
Additional information: Unable to cast object of type 'System.Data.Linq.DataQuery`1[testdbautoincrement.TestTable]' to type 'System.Data.DataTable'.


BTW, I'm usin Free Spire.XLS.

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Tue Sep 18, 2018 6:09 am

Hello,

Thanks for your feedback.
Please use below code to avoid the issue. Additionally, I suggest that you evaluate our latest commercial version (Spire.XLS Pack(Hotfix) Version:8.9.3), since it is more stable than the free version. And we could offer a license (1 month free) to help you remove the warning message. If interested, please contact our sales team (sale@e-iceblue.com) to get it.
Code: Select all
 private void ExportToExcel_Click(object sender, EventArgs e)
 {
     Workbook workbook = new Workbook();
     Worksheet sheet = workbook.Worksheets[0];

     //Convert data from datagridview to datatable
     DataTable dt=GetDgvToTable(this.dataGridView1);

     //Export datatable to excel
     sheet.InsertDataTable(dt, true, 1, 1, -1, -1);

     sheet.AllocatedRange.AutoFitColumns();
     sheet.AllocatedRange.AutoFitRows();

     workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
 }
 public DataTable GetDgvToTable(DataGridView dgv)
 {
     DataTable dt = new DataTable();

     //Column
     for (int count = 0; count < dgv.Columns.Count; count++)
     {
         DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
         dt.Columns.Add(dc);
     }

     //Row
     for (int count = 0; count < dgv.Rows.Count; count++)
     {
         DataRow dr = dt.NewRow();
         for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
         {
             dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
         }
         dt.Rows.Add(dr);
     }
     return dt;
 }

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Thu Sep 20, 2018 9:46 am

Hi,

Greetings from E-iceblue.
Did my code help you?
Your feedback will be greatly appreciated.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Fri Sep 21, 2018 6:04 am

Hi Nina,
Yes, your code did help.
Thanks a lot :)

DB_007
 
Posts: 36
Joined: Sat Sep 08, 2018 4:45 pm

Fri Sep 21, 2018 6:25 am

Hi,

Thanks for your feedback.
If you need assistance in the future, just feel free to contact us.
Wish you all the best!

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Return to Spire.XLS