Hi.
With Microsoft interop Excel object, we can use this code( Excel.Range.value with Array )
---------------------------------------------------------------------------------------------------------------------
DataSet dsExcel = MakeDataSet()
int DtColLength;
int DtRowLength;
Excel.Range cel = worksheet.get_Range(app.ActiveCell, app.ActiveCell.get_Offset(DtRowLength - 1, DtColLength - 1)); //starting point
string[,] MyArray = new string[DtRowLength, DtColLength
for (int i = 1; i < DtColLength + 1; i++) { for (int j = 1; j < DtRowLength + 1; j++) { MyArray[j - 1, i - 1] = dsExcel.Tables[0].Rows[j - 1][i - 1].ToString(); } } cel.Value2 = MyArray;// Databind cel.Columns.AutoFit();
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
but I have no idea with "Infragistics.Excel.Workbook" object.
Thanks in advance.
I found the solution you want. ^^
step 1
drag UltraWebGridDocumentExporter control to ur page( include ultragrid )
step 2
drag button control and doubleclick.
and modify this code
declare this to your Grid and EXport to EXcel
DataTable m_oHeadDT = new DataTable(); protected void Page_Load(object sender, EventArgs e) { for (int i = 0; i < 24; i++) { m_oHeadDT.Columns.Add("HAHA" + i.ToString()); } for (int k = 0; k< 100; k++) { DataRow oDR; oDR = m_oHeadDT.NewRow(); oDR.ItemArray = new string[] {"Code : [" + k.ToString() + "]" , "Company Name", "Fiscal Year", "Sales", "Operating Income", "Pretax Continued Operaion Income", "Net Income", "EPS", "", "Sales", "Operating Income", "Pretax Continued Operaion Income", "Net Income", "EPS", "", "Sales", "Operating Income", "Pretax Continued Operaion Income", "Net Income", "EPS", "MKF500 Sector", "Announ. Date" }; m_oHeadDT.Rows.Add(oDR); } this.UltraWebGrid1.DataSource = m_oHeadDT; this.UltraWebGrid1.DataBind(); }
protected void Button1_Click(object sender, EventArgs e) {
//version 10.X //Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add(table.TableName);
/// Version 8.2 ?? Infragistics.Excel.Workbook workbook = new Infragistics.Excel.Workbook (); // Create the worksheet to represent this data table Infragistics.Excel.Worksheet worksheet = workbook.Worksheets.Add("EXCELTEST"); // Create column headers for each column for (int columnIndex = 0; columnIndex < m_oHeadDT.Columns.Count; columnIndex++) { worksheet.Rows[0].Cells[columnIndex].Value = m_oHeadDT.Columns[columnIndex].ColumnName; } // Starting at row index 1, copy all data rows in // the data table to the worksheet int rowIndex = 1; foreach (DataRow dataRow in m_oHeadDT.Rows) { Infragistics.Excel.WorksheetRow row = worksheet.Rows[rowIndex++]; for (int columnIndex = 0; columnIndex < dataRow.ItemArray.Length; columnIndex++) { row.Cells[columnIndex].Value = dataRow.ItemArray[columnIndex]; } } workbook.Save("C:\\aaa.xls"); Page.Response.Write("<script language='javascript'>alert('Check it out C:\\aaa.xls');</script>"); }
you can see the result like this.
Good Luck!!
Thank you so much for the link.
Can the work book created be wriiten to memory stream ,so that users can view it and save it too?
Thanks,
Praba
Thank you so much for the code .Is that a custom control ,do I have to add the dll to GAC?
I tried registering it in the page like an user control , I could not call the methods of the user control.
Can you please help me ?
Here is an updated link: http://help.infragistics.com/NetAdvantage/ASPNET/2011.1/CLR4.0/?page=ExcelEngine_Populating_a_Worksheet_from_a_DataSet.html
Hi,
I suprised when I got the mail about " ExcelExport .........".
Actually I forgot the source code about that.
but when I click your ID in Infragistics webpage then some questions are poped up!!
I thought that you are in a hurry. Right?
So I decided to find that source code. :)
Hers is My Code.
Custome Control
using System;using System.Collections.Generic;using System.ComponentModel;using System.Text;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Drawing; using Infragistics.WebUI.UltraWebGrid; namespace FrankoControl{ //Custom Control Icon. [ToolboxBitmap(typeof(FrankoExcelExport))] public class FnExcelExport : Infragistics.WebUI.UltraWebGrid.ExcelExport.UltraWebGridExcelExporter { #region
string[] m_Title; int m_Width = 0; Boolean bHeaderInitChk = true; int m_LastRow = 0; #endregion #region - Step First public void _FnInitExcel(UltraWebGrid FnGrid, string FnFileName, string[] FnTiltle) { try { if (FnGrid.Rows.Capacity != 0) { DownloadName = FnFileName; m_Title = FnTiltle; m_LastRow = FnGrid.Rows.Count; this.ExcelStartRow = FnTiltle.Length + 2; this.BeginExport += new Infragistics.WebUI.UltraWebGrid.ExcelExport.BeginExportEventHandler(FnExcelExport_BeginExport); this.InitializeRow += new Infragistics.WebUI.UltraWebGrid.ExcelExport.InitializeRowEventHandler(FnExcelExport_InitializeRow); this.Export(FnGrid); } else { //_FnAlert("NO Data."); } } catch (Exception ex) { } } #endregion #region - Step Third void FnExcelExport_InitializeRow(object sender, Infragistics.WebUI.UltraWebGrid.ExcelExport.ExcelExportInitializeRowEventArgs e) { try { // e.Row.Style.Font.Name = "Tahoma"; e.Row.Style.Font.Size = 8; if (bHeaderInitChk) { for (int i = 0; i < m_Width + 1; i++) { //e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.BottomBorderStyle = Infragistics.Excel.CellBorderLineStyle.Thin; //e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.TopBorderStyle = Infragistics.Excel.CellBorderLineStyle.Thin; //e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.RightBorderStyle = Infragistics.Excel.CellBorderLineStyle.Thin; e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.FillPatternForegroundColor = Color.FromArgb(140, 204, 222); e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.Font.Name = "Tahoma"; e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True; e.CurrentWorksheet.Rows[m_Title.Length + 1].Cells[i].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center; } bHeaderInitChk = false; } if (m_LastRow - 1 == e.Row.Index) { for (int i = 0; i < m_Width; i++) { e.CurrentWorksheet.Columns[i].Width += 500; } } } catch (Exception ex) { } } #endregion #region - Step Two void FnExcelExport_BeginExport(object sender, Infragistics.WebUI.UltraWebGrid.ExcelExport.BeginExportEventArgs e) { try { m_Width = e.Rows[0].Cells.Count - 1; for (int i = 0; i < m_Title.Length; i++) { e.CurrentWorksheet.Rows[i].Cells[0].Value = m_Title[i]; e.CurrentWorksheet.Rows[i].Cells[0].CellFormat.Font.Color = Color.FromArgb(0, 51, 102); e.CurrentWorksheet.Rows[i].Cells[0].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True; e.CurrentWorksheet.Rows[i].Cells[0].CellFormat.Font.Name = "Tahoma"; e.CurrentWorksheet.MergedCellsRegions.Add(i, 0, i, m_Width); e.CurrentWorksheet.Rows[i].Cells[0].CellFormat.FillPatternForegroundColor = Color.FromArgb(204, 255, 204); e.CurrentWorksheet.Rows[i].Cells[0].CellFormat.RightBorderStyle = Infragistics.Excel.CellBorderLineStyle.Thin; } e.CurrentWorksheet.Rows[m_Title.Length - 1].Cells[0].CellFormat.BottomBorderStyle = Infragistics.Excel.CellBorderLineStyle.Thin; e.CurrentWorksheet.Name = m_Title[0]; e.CurrentWorksheet.DisplayOptions.TabColor = Color.FromArgb(151, 0, 0); e.CurrentWorksheet.Columns[1].CellFormat.FormatString = "####.##"; } catch (Exception ex) { } } #endregion #region
protected void _FnAlert(string strErrMsg) {
Page.Response.Write("<script language='javascript'>alert('" + strErrMsg + "');</script>"); } #endregion }}
* how to use
Make a useControl using above Class.
then make a test page then use this code..
protected void Button1_Click(object sender, EventArgs e){ string[] FnTitle = { "test", "test1", "test2", "test3","test4" }; FrankoExcelExport1._FnInitExcel(UltraWebGrid1, "FnExcel.xls", FnTitle); // ==>
}
Good luck!!
Sorry for my Poor English!!