Click here to Skip to main content
11,932,048 members (58,856 online)
Rate this:
Please Sign up or sign in to vote.
I have generated excel file using Excel 2007 PIA's and now I want to insert data from database into excel file.I am having SQL Server 2005 database.I also want to format the data in excel file.

Guide me.. Thanks
Posted 20-Oct-11 20:52pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Please refer following url,

Generating Excel (XML Spreadsheet) in C#[^]

Hope this may help you...
Ston Cold 21-Oct-11 2:39am
Mukund I am dynamically generating the excel so i don't use xml and I want the methods that I may use to dynamically insert data into excel sheet from the database. Thanks for ur suggestion.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I have data in my datatable 'dt' and I also have a worksheet wherein I want to add my data from datatable.

public void AddToExcel(DataTable dt, ref Microsoft.Office.Interop.Excel.Worksheet excelSheet)           
   int rowCount = 1;

   //data is added to excel sheet row by row
   foreach (DataRow dr in dt.Rows)
         rowCount += 1;
         CurrentProcressValue = rowCount;
         for (int i = 1; i < dt.Columns.Count + 1; i++)
            // Add the column headers the first time through
            if (rowCount == 2)
                  excelSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                  Microsoft.Office.Interop.Excel.Range ran =              
                        (Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[1, i];
                  ran.Font.Bold = true;
                  ran.Interior.Color =     
                  ran.Borders.Color =  
            excelSheet.Cells[rowCount, i] = dr[i - 1].ToString();
            Microsoft.Office.Interop.Excel.Range range =
                  (Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[rowCount, i];
            //Give black color to borders
            range.Borders.Color =  

   // Resize the columns
   Microsoft.Office.Interop.Excel.Range oRange =
            excelSheet.get_Range(excelSheet.Cells[1, 1],
            excelSheet.Cells[rowCount, dt.Columns.Count]);
   oRange = null;
This is how it did worked for me...I hope it will help
Thanks everyone

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web04 | 2.8.151126.1 | Last Updated 28 Feb 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100