none
Exporting SharePoint 2013 list in a specific format to excel RRS feed

  • Question

  • Hi,

    I have a SharePoint 2013 list. The form is designed with InfoPath 2013. I am allowing people to enter multiple employee info in a single item for a particular company. My list is collecting data like below-

    SP list view

    But what I need is, when I will export the list to excel, it will comes in the following format-

    Could anyone help on this?

    Thanks.

    Thursday, December 6, 2018 4:14 AM

Answers

All replies

  • Hello Pointtoshare,

    Out of the box, you can't change the format of "Export to Spreadsheet".

    However, you can export to spreadsheet and then format the workbook how you need it to look.


    Thanks Ravikant Chaturvedi

    Thursday, December 6, 2018 7:33 AM
  • Hi,

    if you using client context code then may be below code it's useful for you, possible to convert c# code

    Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click

            Dim log As LogWriter = New LogWriter("btnExportExcel_Click")

            Try

                If chartTable.Rows.Count > 0 Then

                    btnExportExcel.Enabled = False

                    Dim xlApp As Excel.Application

                    Dim xlWorkBook As Excel.Workbook

                    Dim xlWorkSheet As Excel.Worksheet

                    Dim misValue As Object = System.Reflection.Missing.Value

                    xlApp = New Excel.Application()

                    xlWorkBook = xlApp.Workbooks.Add(misValue)

                    xlWorkSheet = CType(xlWorkBook.Worksheets(1), Excel.Worksheet)

                    Dim i As Integer = 0

                    Dim j As Integer = 0

                    For i = 0 To chartTable.Columns.Count - 1

                        xlWorkSheet.Cells(1, i + 1) = chartTable.Columns(i).ColumnName

                    Next

                    For i = 0 To chartTable.Rows.Count - 1

                        For j = 0 To chartTable.Columns.Count - 1

                            xlWorkSheet.Cells(i + 2, j + 1) = chartTable.Rows(i)(j)

                        Next

                    Next

                    xlWorkSheet.Columns.AutoFit()

                    Dim chartRange As Excel.Range

                    Dim xlCharts As Excel.ChartObjects = CType(xlWorkSheet.ChartObjects(Type.Missing), Excel.ChartObjects)

                    Dim myChart As Excel.ChartObject = CType(xlCharts.Add(350, 80, 500, 300), Excel.ChartObject)

                    Dim chartPage As Excel.Chart = myChart.Chart

                    chartRange = xlWorkSheet.Range("B1", "C" + (chartTable.Rows.Count + 1).ToString() + "")

                    chartPage.SetSourceData(chartRange, misValue)

                    If (ComboBox1.SelectedItem = "Doughnut") Then

                        chartPage.ChartType = Excel.XlChartType.xlDoughnut

                    ElseIf (ComboBox1.SelectedItem = "Area") Then

                        chartPage.ChartType = Excel.XlChartType.xlArea

                    ElseIf (ComboBox1.SelectedItem = "Pie") Then

                        chartPage.ChartType = Excel.XlChartType.xlPie

                    ElseIf (ComboBox1.SelectedItem = "RangeColumn") Then

                        chartPage.ChartType = Excel.XlChartType.xlColumnClustered

                    ElseIf (ComboBox1.SelectedItem = "Bar") Then

                        chartPage.ChartType = Excel.XlChartType.xlBarClustered

                    ElseIf (ComboBox1.SelectedItem = "Pyramid") Then

                        chartPage.ChartType = Excel.XlChartType.xlPyramidBarClustered

                    ElseIf (ComboBox1.SelectedItem = "Radar") Then

                        chartPage.ChartType = Excel.XlChartType.xlRadar

                    End If

                    chartPage.ApplyLayout(1)

                    chartPage.ChartTitle.Text = "Statistics report"

                    Dim filePath As String = ExportToExcelFilePath

                    Dim count As Integer = 1

                    Dim fileNameOnly As String = IO.Path.GetFileNameWithoutExtension(filePath)

                    Dim extension As String = IO.Path.GetExtension(filePath)

                    Dim path As String = IO.Path.GetDirectoryName(filePath)

                    Dim newFullPath As String = filePath

                    While IO.File.Exists(newFullPath)

                        Dim tempFileName As String = String.Format("{0}({1})", fileNameOnly, Math.Min(System.Threading.Interlocked.Increment(count), count - 1))

                        newFullPath = IO.Path.Combine(path, tempFileName & extension)

                    End While

                    xlWorkBook.SaveAs(newFullPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)

                    xlWorkBook.Close(True, misValue, misValue)

                    xlApp.Quit()

                    releaseObject(xlWorkSheet)

                    releaseObject(xlWorkBook)

                    releaseObject(chartRange)

                    releaseObject(xlCharts)

                    releaseObject(xlCharts)

                    releaseObject(myChart)

                    releaseObject(xlApp)

                    MessageBox.Show("Excel file created , you can find the file " + newFullPath)

                    btnExportExcel.Enabled = True

                Else

                    MessageBox.Show("No Item's found")

                End If

            Catch ex As Exception

                btnExportExcel.Enabled = True

                log.LogWrite("ExportExcel_Exception : " & ex.Message & " Stacktrace :  " + ex.StackTrace)

            End Try

        End Sub


    Thanks
    Jaison A
    http://infomoss.blogspot.in

    Thursday, December 6, 2018 8:01 AM
  • Hi,

    Is there any way that I can create the excel template first and then map with SP list fields?

    Thanks.

    Thursday, December 6, 2018 9:07 AM
  • Hi Pointtoshare,

    Try to use PowerPivot table in Excel. Get information from SharePoint list and set the relevant format for your requirement.

    Check the reference about PowerPivot table Below:

    https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2013/07/11/excel-services-using-a-sharepoint-list-as-a-data-source/

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, December 7, 2018 7:50 AM
    Moderator