how can we create excel sheet in c# using data grid

Joined:	29/03/2005
Level:	Bronze
Points:	8

Joined:	30/03/2005
Level:	Gold
Points:	785

Points:	2

For this problem you have ot create excel application object..
hope so this code will help you.

You can transfer an array of data to a range of multiple cells at one time: // Start a new workbook in Excel.


m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

// Create an array for the headers and add it to cells A1:C1.
object[] objHeaders = {"Order ID", "Amount", "Tax"};
m_objRange = m_objSheet.get_Range("A1", "C1");
m_objRange.Value = objHeaders;
m_objFont = m_objRange.Font;
m_objFont.Bold=true;

// Create an array with 3 columns and 100 rows and add it to
// the worksheet starting at cell A2.
object[,] objData = new Object[100,3];
Random rdm = new Random((int)DateTime.Now.Ticks);
double nOrderAmt, nTax;
for(int r=0;r<100;r++)
{
	objData[r,0] = "ORD" + r.ToString("0000");
	nOrderAmt = rdm.Next(1000);
	objData[r,1] = nOrderAmt.ToString("c");
	nTax = nOrderAmt*0.07;
	objData[r,2] = nTax.ToString("c");
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(100,3);
m_objRange.Value = objData;

// Save the Workbook and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "Book2.xls", m_objOpt, m_objOpt,
	m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
	m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();

Joined:	14/02/2007
Level:	Gold
Points:	13087

Points:	6

Generating native Excel spreadsheets from your web server is not that difficult with ASP.NET. What can be difficult is making instances of Excel.exe go away so you don't open up TaskMgr and see 123 instances of EXCEL.EXE still sitting in memory. I provide here a solution that has two methods, "CreateExcelWorkbook", which runs a stored proceduire that returns a DataReader and assembles a native Excel Workbook from it, saves it to the filesystem, and creates a "Download" link so the user can either load the report into Excel in their browser, or download the XLS file. The second method, GenerateCSVReport, does essentially the same thing but creates a CSV file that will, of course, also load into Excel. The CSV code correctly handles a common developer problem in that if you have a column that has leading zeroes, they are preserved.

There is also, in the downloadable solution, a utility class "SPGen" that handles running stored procedures and returning DataReaders, and a RemoveFiles utility method that cleans up any XLS or CSV file older than the specified number of minutes. The key method presented below is the CreateExcelWorkbook method.

NOTE: You should be aware that you will probably need to run this page under an account that has administrative privileges as it needs write permissions to store the generated Excel or CSV files on the webserver's file system. Probably the easiest way to handle this is to have the page in its own folder with its own web.config, and insert an <identity impersonate ="true" ... elment. You may also need to enable ACL permissions on the physical folder as well so that the identity the page runs under has write permissions. Finally, you'll need to set a COM reference to the Excel 9.0 or Excel 10 Typelibrary and let VS.NET generate the Interop assemblies for you. I believe MS also has a link on their Office site where you can download the Office primary Interop Assemblies.

<identity impersonate="true" userName="adminuser" password="adminpass" />

Note especially the code block that does the "cleanup" of the Excel objects:

// Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
oSheet=null;
oWB=null;
oXL = null;
GC.Collect(); // force final cleanup!

This is necessary because all those littlle objects "oSheet", "oWb" , 'oRng", etc. are all COM instances and we need to use the InteropServices ReleaseComObject method of the Marshal class to get rid of them in .NET.

private void CreateExcelWorkbook(string spName, SqlParameter[] parms)
{
string strCurrentDir = Server.MapPath(".") + "\\";
RemoveFiles(strCurrentDir); // utility method to clean up old files
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
try
{
GC.Collect();// clean up any other excel guys hangin' around...
oXL = new Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//get our Data

string strConnect = System.Configuration.ConfigurationSettings.AppSettings["connectString"];
SPGen sg = new SPGen(strConnect,spName,parms);
SqlDataReader myReader = sg.RunReader();
// Create Header and sheet...
int iRow =2;
for(int j=0;j<myReader.FieldCount;j++)
{
oSheet.Cells[1, j+1] = myReader.GetName(j).ToString();
}
// build the sheet contents
while (myReader.Read())
{
for(int k=0;k < myReader.FieldCount;k++)
{
oSheet.Cells[iRow,k+1]= myReader.GetValue(k).ToString();
}
iRow++;
}// end while
myReader.Close();
myReader=null;
//Format A1:Z1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "Z1").Font.Bold = true;
oSheet.get_Range("A1", "Z1").VerticalAlignment =Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheet.get_Range("A1", "Z1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
string strFile ="report" + System.DateTime.Now.Ticks.ToString() +".xls";
oWB.SaveAs( strCurrentDir + strFile,Excel.XlFileFormat.xlWorkbookNormal,
null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);
// Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
oSheet=null;
oWB=null;
oXL = null;
GC.Collect(); // force final cleanup!
string strMachineName = Request.ServerVariables["SERVER_NAME"];
errLabel.Text="<A href=http://" + strMachineName +"/ExcelGen/" +strFile + ">Download Report</a>";

}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
errLabel.Text= errorMessage ;
}
}



refer for complete code


http://www.eggheadcafe.com/articles/20021012.asp
Thanks
Shivshanker Cheral
"If you share your assets (money etc..) it will decrease, But if you share your knowledge it will increase!!! ".

how can we create excel sheet in c# using data grid

About dotnetspider.com

Quick Links

General

Services