Insert Data into Excel File generated using Excel PIA's

Question

0.00/5 (No votes)

See more:

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 19:52pm

Ston Cold

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mukund Thakker · Answer 1 · 2011-10-20T20:33:00

Solution 1

Please refer following url,

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

Hope this may help you...

Posted 20-Oct-11 20:33pm

Mukund Thakker

Comments

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.

Ston Cold · Answer 2 · 2012-02-28T00:03:00

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