Here man this is large piece of the puzzle and this should run but it is
missing a very important piece. Just look up file handles and how to release
them.
void BtnConvertClick(object sender, System.EventArgs e)
{
excelApp = new Excel.ApplicationClass();
System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo
("en-US");
workBook = excelApp.Workbooks.Add(Missing.Value);
//progressbar setting
prgProg.Maximum = chcklstTables.CheckedIndices.Count;
int prgValue = 0;
string TableName = string.Empty; //table name to naming the
sheets
//all tablenames are in checklistbox, so check only checked items
foreach(int indexChecked in chcklstTables.CheckedIndices)
{
Application.DoEvents();
prgValue++;
prgProg.Value = prgValue;
sqlCmd = "SELECT * FROM " +
chcklstTables.Items[indexChecked].ToString();
myDtExp =
dbe.GetDataTable(connstr,sqlCmd);
TableName =
chcklstTables.Items[indexChecked].ToString();
//check if table exists...
if (myDtExp == null)
{
lstProgress.Items.Add("TABLE " + TableName + " DOESN'T
EXISTS IN DB...");
continue;
}
//if table name is more than 31 characters - max. lenght for
Excel sheet name, shorten it...
if(TableName.Length > 31)
{
lstProgress.Items.Add("TABLE NAME " + TableName + " HAS
BEEN SHORTED TO 31 CHARACTERS: " + TableName.Substring(0,31));
TableName = TableName.Substring(0,31);
}
try
{
//add new sheet to workbook...
sheet = (Excel.Worksheet)
workBook.Sheets.Add(Missing.Value,Missing.Value,1,Excel.XlSheetType.xlWorksheet);
sheet.Name = TableName;
}
catch(Exception ex1)
{
lstProgress.Items.Add("TABLE " + TableName + " HAS
PRODUCED ERROR");
}
//this code produces 100% CPU usage...
try
{
int ColumnIndex=0;
foreach(DataColumn col in myDtExp.Columns)
{
Application.DoEvents();
ColumnIndex++;
excelApp.Cells[1,ColumnIndex]=col.ColumnName;
}
int rowIndex=0;
foreach(DataRow myRow in myDtExp.Rows)
{
Application.DoEvents();
rowIndex++;
ColumnIndex=0;
foreach(DataColumn col in myDtExp.Columns)
{
ColumnIndex++;
excelApp.Cells[rowIndex+1,ColumnIndex]=myRow[col.ColumnName].ToString();
}
}
}
catch(Exception ex2)
{
lstProgress.Items.Add("TABLE " + TableName + " HAS
PRODUCED ERROR");
}
lstProgress.Items.Add(TableName + " ... Done");
}
//save workbook to specified folder...
workBook.SaveAs("C:\\test.xls",Excel.XlFileFormat.xlXMLSpreadsheet,Missing.Value
,Missing.Value ,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value
,
Missing.Value ,Missing.Value ,Missing.Value
,Missing.Value);
//release objects from memory...
myDtExp.Dispose();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
workBook = null;
sheet = null;
excelApp = null;
MessageBox.Show ("Operation done...");
}
On Tue, Sep 23, 2008 at 11:53 AM, Mohd Irshad Hasmat Ansari <
[EMAIL PROTECTED]> wrote:
> I m developing a application where I have to make a report from sql
> server to excel. I don't know how to do that please somebody help me.
>
> Sql server à C# à excel
>
>
>
> I need to import some particular column only
>
>
>
> Thanks
>
> Irshad
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://cm.megasolutions.net/forums/default.aspx
-~----------~----~----~----~------~----~------~--~---
