Click here to Skip to main content
Rate this: bad
good
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
good
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...
  Permalink  
Comments
Ston Cold at 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
good
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 =     
                     System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                  ran.Borders.Color =  
                     System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            }
            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 =  
                  System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
         }
   }
 
   // Resize the columns
   Microsoft.Office.Interop.Excel.Range oRange =
            excelSheet.get_Range(excelSheet.Cells[1, 1],
            excelSheet.Cells[rowCount, dt.Columns.Count]);
   oRange.EntireColumn.AutoFit();
   oRange = null;
  
This is how it did worked for me...I hope it will help
Thanks everyone
Stone
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 515
1 Maciej Los 349
2 Richard MacCutchan 220
3 BillWoodruff 209
4 Mathew Soji 160
0 OriginalGriff 8,654
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,229
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 28 Feb 2012
Copyright © CodeProject, 1999-2014
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