Export to excel

[Administrator]

Hello,
How do i use the xlsio library and the xlsconfig clas to export my grid values to an excel file?

I have one more question.
Is there any way to re-arrange the row in the grid.


Thanks,
hadiya

[Administrator]

Here is a minimal sample showing how you can iterate through the grid and export its data to an XLS file using XlsIO.

The move rows, you need to be using the latest code base, Vol3 (6.3.0.30). It has a method exposed, grid.Model.MoveRows. The sample also has a button showing its use.



WpfApplication1_52d0a3a0.zip

[Administrator]

This works fine. But i am not getting a column to the size as required to hold the value. Whether i could able to merge cells?

[Administrator]

We will be offerring full excel converter support similar to what we offer with our Windows Forms Grid. This support will allow you to move over column widths, colors, etc.

For now, if you want to support particular options, then you would have to explicitly add such support to your code. For column widths, you can do this using IWorkSheet.SetColumnWidth. Here is the button handler from the sample above with support for exporting columns widths added.

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            //Export to Excel
             
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Add();
            IWorksheet mySheet = myWorkbook.Worksheets[0];

            for (int i = 1; i <= this.grid.Model.RowCount; i++)
            {
                for (int j = 1; j <= this.grid.Model.ColumnCount; j++)
                {
                   mySheet.Range[i, j].Value2 = this.grid.Model[i, j].Text;
                }
            }

            for (int j = 1; j < this.grid.Model.ColumnCount; j++)
            {
                mySheet.SetColumnWidthInPixels(j, (int)grid.Model.ColumnWidths[j]);
            }
          
            myWorkbook.SaveAs("SampleOne.xls");
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
            System.Diagnostics.Process.Start("SampleOne.xls");
        }

[Administrator]

One more thing.
pls tell me abouthow to set border for group of cells, and merging a group of cells.
I don know how to use those merge functions with IRange as parameter

[Administrator]

If you own our Windows Forms Source code, you can see how to go about doing these things from that code.

Here is the sample modified to show CoveredCells and also to mark special boundaries. It is not full-featured so if you need other support, you will have to add additional code.

http://www.syncfusion.com/support/user/uploads/WpfApplication1_1_542eb1b7.zip

[Administrator]

its fine..

i am writing a title for a grid in excel. In that situation i used the mergewith() function like this
myWorksheet.Range[RowIndex,ColumnIndex] .MergeWith(myWorksheet.Range[RowIndex, ColumnIndex + DataGrid.ColumnCount]);

but i am not able to merge the first row with the number of columns.

[Administrator]

Here is code that I placed in the last posted sample that merges the first row in Excel even though ity is not covered in the grid. Note that the range was defined using 4 arguments. In your code, you only have 2.

//....
HandleCoveredCells(mySheet);
            IRange xlRange = mySheet.Range[1, 1, 1, 1 + grid.Model.ColumnCount - 1];  //inserted...
            xlRange.Merge();  //inserted...

            for (int j = 1; j < this.grid.Model.ColumnCount; j++)
            {
                mySheet.SetColumnWidthInPixels(j, (int)grid.Model.ColumnWidths[j]);
            }
//.....

[Administrator]

Thanks. I tried ur code and it works fine

[Administrator]

Hello Clay,

I return that Iworkbook to another assembly and tried to save it from there. But i am getting an exception like

"exception = {"Style with specified name does not exist. Name: Normal\rParameter name: value"}"

IWorkbook workBook = SomeInstance.ExportToExcel(gridControl)
workBook.SaveAs(fileName, ExcelSaveType.SaveAsXLS);

fileName = "c:\\test.xls"

[Administrator]

Its resolved..
Its because of disposing excel engine:-)

[Giancarlo Aguilera]

Clay,

Is this Excel converter ready yet for the WPF grid?

Thanks

>We will be offerring full excel converter support similar to what we offer with our Windows Forms Grid. This support will allow you to move over column widths, colors, etc.

For now, if you want to support particular options, then you would have to explicitly add such support to your code. For column widths, you can do this using IWorkSheet.SetColumnWidth. Here is the button handler from the sample above with support for exporting columns widths added.

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            //Export to Excel
             
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Add();
            IWorksheet mySheet = myWorkbook.Worksheets[0];

            for (int i = 1; i <= this.grid.Model.RowCount; i++)
            {
                for (int j = 1; j <= this.grid.Model.ColumnCount; j++)
                {
                   mySheet.Range[i, j].Value2 = this.grid.Model[i, j].Text;
                }
            }

            for (int j = 1; j < this.grid.Model.ColumnCount; j++)
            {
                mySheet.SetColumnWidthInPixels(j, (int)grid.Model.ColumnWidths[j]);
            }
          
            myWorkbook.SaveAs("SampleOne.xls");
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
            System.Diagnostics.Process.Start("SampleOne.xls");
        }

[Administrator]

The ExcelConverter will not be in the Vol 2 release this month, but it is scheduled to be part of the Vol 2 Refresh build which will be out in May.

[Administrator]

Has this ExcelConverter been released as yet?

[Clay Burch]

No, not yet. We will have excel export support in the Vol 3 Refresh which is due out next week.

[Administrator]

Could you ensure that this handles grids with relations?

Perhaps providing the option to export the inner relations (or not in what i'm attempting to do!)

Thanks
Heath

[Mohamed Suhaib Fahad A.]

Hi Heath,

Yes, the excel export will include handling relations too. Please let me know if you need any more details.

Thanks,
Fahad
Grid.WPF Team
Syncfusion Inc.,

[Sunil]

Hi
 I am using syncfusion grid and trying to export grid data to excel. My grid contains more than 800 columns. As there is a limitation on maximum number of columns i exported data to 2 work sheets , but still some columns are missing. Is it possible to change the limitaion on Number of worksheets or number of columns in a worksheet.
Is there any direct method which exports the entire data into an excel with same format ,pictures and merging of cells??               

[Sharal DCosta]

Hi ,

 I am using syncfusion grid and trying to export grid data to excel. 

My grid contains 146 columns and 48000 rows. 

I'm using below code to achieve the same. 

private void Button_Click_1(object sender, RoutedEventArgs e)

{

//Export to Excel

ExcelEngine excelEngine = new ExcelEngine();

IApplication application = excelEngine.Excel;

IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Add();

IWorksheet mySheet = myWorkbook.Worksheets[0];

for (int i = 1; i <= this.grid.Model.RowCount; i++)

{

for (int j = 1; j <= this.grid.Model.ColumnCount; j++)

{

mySheet.Range[i, j].Value2 = this.grid.Model[i, j].Text;

}

}

for (int j = 1; j < this.grid.Model.ColumnCount; j++)

{

mySheet.SetColumnWidthInPixels(j, (int)grid.Model.ColumnWidths[j]);

}

myWorkbook.SaveAs("SampleOne.xls");

excelEngine.ThrowNotSavedOnDestroy = false;

excelEngine.Dispose();

System.Diagnostics.Process.Start("SampleOne.xls");

}

My grid looks similar to the attached image. Please could you let me know how  to set background colors as well as font colur to each cell. 

Thanks. 

Grid_Sample_8d8962a1.zip

[Saravanan M]

Hi Sharal,

We have analyzed your query.When you export more number of row and columns it could take more time to export.If you want to export background color you can export only background by using FillBackgroundRGB API.You can refer the below codesnippet.

Codesnippet[C#]:

mySheet.Range[i, j].Value2 = this.grid.Model[i, j].Text;

var color=(this.grid.Model[i,j].Background as SolidColorBrush).Color;

mySheet.Range[i, j].CellStyle.FillBackgroundRGB = System.Drawing.Color.FromArgb(color.A, color.R, color.G, color.B);

 

We have attached the sample based on your requirement. Please find it

Please let us know if you need further assistance.

Regards,

Saravanan..

Excel_Export_Demo_Sample_b3162188.zip