Click here to Skip to main content
11,480,626 members (63,286 online)
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 Sergey Alexandrovich Kryukov 294
1 OriginalGriff 155
2 Richard MacCutchan 150
3 CHill60 130
4 CPallini 125
0 Sergey Alexandrovich Kryukov 8,184
1 OriginalGriff 7,556
2 Sascha Lefèvre 3,114
3 Maciej Los 2,491
4 Richard Deeming 2,335


Advertise | Privacy | Mobile
Web03 | 2.8.150520.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