Telerik Document Processing libraries to export the data table to excle

0 Answers 199 Views
Spreadsheet
Aravind
Top achievements
Rank 2
Iron
Iron
Iron
Aravind asked on 13 Mar 2023, 03:46 AM

Hi, i try to export data table to excel file using following code https://docs.telerik.com/devtools/aspnet-ajax/knowledge-base/common-export-large-amount-of-data-to-pdf-xlsx-and-csv-using-the-telerik-document-processing-libraries?_ga=2.266990472.785509911.1678626449-162692405.1645090225&_gl=1*1ns64vr*_ga*MTYyNjkyNDA1LjE2NDUwOTAyMjU.*_ga_9JSNBCSF54*MTY3ODY3Nzk2OS4xMTguMS4xNjc4Njc4MzQwLjM1LjAuMA.. , in data table we have number columns which is not export correct format ,we need to change format for apply formulas, we need to avoid this, bcz each time need to change that format in order to sum the column,


Private Sub SpreadStreamProcessingForXLSXAndCSV(ByVal dt As DataTable, ByVal filename As String, ByVal Optional docFormat As SpreadDocumentFormat = SpreadDocumentFormat.Xlsx, ByVal Optional sheetName As String = "Sheet1")

        If dt Is Nothing Then
            Dim manager As RadAjaxManager = RadAjaxManager.GetCurrent(Page)
            manager.Alert("No records to export to excel!")
            Exit Sub

        End If

        Using stream As MemoryStream = New MemoryStream()

            Using workbook As IWorkbookExporter = SpreadExporter.CreateWorkbookExporter(docFormat, stream)

                Using worksheetExporter As IWorksheetExporter = workbook.CreateWorksheetExporter(sheetName)

                    For i As Integer = 0 To dt.Columns.Count - 1

                        Using columnExporter As IColumnExporter = worksheetExporter.CreateColumnExporter()
                            columnExporter.SetWidthInPixels(100)

                        End Using
                    Next

                    ExportHeaderRows(worksheetExporter, dt)

                    For Each row As DataRow In dt.Rows

                        Using rowExporter As IRowExporter = worksheetExporter.CreateRowExporter()


                            For Each item In row.ItemArray
                                Dim normalFormat As SpreadCellFormat = New SpreadCellFormat()
                                normalFormat.FontSize = 10
                                normalFormat.VerticalAlignment = SpreadVerticalAlignment.Center
                                normalFormat.HorizontalAlignment = SpreadHorizontalAlignment.Center
                                Using cellExporter As ICellExporter = rowExporter.CreateCellExporter()
                                    cellExporter.SetValue(item.ToString())
                                    cellExporter.SetFormat(normalFormat)
                                End Using
                            Next
                        End Using
                    Next
                End Using
            End Using

            Dim output As Byte() = stream.ToArray()

            If docFormat = SpreadDocumentFormat.Csv Then
                FileExtension = "csv"
                filename = filename
                ContentType = "text/csv"
            ElseIf docFormat = SpreadDocumentFormat.Xlsx Then
                FileExtension = "xlsx"
                filename = filename
                ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            End If

            WriteFileToResponse(output, filename)
        End Using
    End Sub

    Private Sub ExportHeaderRows(ByVal worksheetExporter As IWorksheetExporter, ByVal dt As DataTable)
        Using rowExporter As IRowExporter = worksheetExporter.CreateRowExporter()
            Dim HeaderRowHeight As Double = 50
            rowExporter.SetHeightInPoints(HeaderRowHeight)
            Dim format As SpreadCellFormat = New SpreadCellFormat()
            format.IsBold = True
            format.Fill = SpreadPatternFill.CreateSolidFill(New SpreadColor(142, 196, 65))
            format.ForeColor = New SpreadThemableColor(New SpreadColor(255, 255, 255))
            format.HorizontalAlignment = SpreadHorizontalAlignment.Center
            format.VerticalAlignment = SpreadVerticalAlignment.Center

            For i As Integer = 0 To dt.Columns.Count - 1

                Using cellExporter As ICellExporter = rowExporter.CreateCellExporter()

                    cellExporter.SetFormat(format)
                    cellExporter.SetValue(dt.Columns(i).ColumnName)
                End Using
            Next
        End Using
    End Sub

    Private Sub WriteFileToResponse(ByVal content As Byte(), ByVal strfilename As String)
        Response.ContentType = ContentType
        Response.Headers.Remove("Content-Disposition")
        Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.{1}", strfilename, FileExtension))
        Response.BinaryWrite(content)
        Response.[End]()
    End Sub
Please help to how to apply correct format for number columns, 
Note: We don't know which column is number column, so we need to pass General format for number column.

Regards
Aravind

No answers yet. Maybe you can help?

Tags
Spreadsheet
Asked by
Aravind
Top achievements
Rank 2
Iron
Iron
Iron
Share this question
or