JSON to Excel : How to export SharePoint list data into excel using JavaScript?

<script type="text/javascript" src="/SiteAssets/scripts/grid/jquery-2.2.4.min.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/xlsx.core.min.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/FileSaver.js"></script>
<script type="text/javascript" src="/SiteAssets/scripts/grid/jhxlsx.js"></script>


$(document).ready(function(){
loadData();
});


var exportData=[[{"text":"Resource Name"},{"text":"Resource Email"},{"text":"Status1"},{"text":"Status2"},{"text":"Supervisor Email"},{"text":"Career Level"},{"text":"Current Work Location"},{"text":"Project"},{"text":"Enterprise ID"},{"text":"Start Date"}]];

function jsonToExcel()
{

var tabularData = [{
    "sheetName": "All Resources",
    "data": exportData
}];

var options = {
    fileName: "All Resources"
};
Jhxlsx.export(tabularData, options);

}

function loadData()
{//start loadData

$().SPServices({//sart service call
    operation: "GetListItems",
    async: false,
    listName: "Resource List",
    CAMLViewFields: "<ViewFields Properties='True' />",
    CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where><OrderBy><FieldRef Name='Column1' Ascending='True' /></OrderBy></Query>",
    CAMLRowLimit: 0,
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
 
      exportData.push(
      [
         {"text": $(this).attr("ows_Title")},
         {"text": $(this).attr("ows_emailaddress")==undefined?"-":($(this).attr("ows_emailaddress"))},
{"text": $(this).attr("ows_status1")==undefined?"-":($(this).attr("ows_status1"))},
{"text": $(this).attr("ows_Status2")==undefined?"-":($(this).attr("ows_Status2"))},
{"text": $(this).attr("ows_SupervisorEmailID")==undefined?"-":($(this).attr("ows_SupervisorEmailID"))},
{"text": $(this).attr("ows_career_x0020_Level")==undefined?"-":($(this).attr("ows_career_x0020_Level"))},
{"text": $(this).attr("ows_Curr_Loc")==undefined?"-":($(this).attr("ows_Curr_Loc"))},
{"text": $(this).attr("ows_Project")==undefined?"-":($(this).attr("ows_Project"))},
{"text": $(this).attr("ows_Employee_x0020_ID")==undefined?"-":($(this).attr("ows_Employee_x0020_ID"))},
{"text": $(this).attr("ows_Start_x0020_Date")==undefined?"-":($(this).attr("ows_Start_x0020_Date"))}
]
       );
     
      });//end loop
    }

  });//end service call

}//end loadData

Reference: https://www.jqueryscript.net/other/JavaScript-JSON-Data-Excel-XLSX.html

SharePoint Application Page: How to export Gridview data to Excel file using ActiveX?

Note: This feature works only in IE. Also, you would need to enable ActiveX in IE settings and add the URL in trusted sites.

Prerequisite 1: IE Settings -> Internet Options -> Security -> Trusted Sites -> Custom Levels -> ActiveX Settings -> Enable/Prompt for ActiveX not marked as safe.

Prerequisite 2: IE Settings -> Internet Options -> Security -> Trusted Sites -> Add URL


Javascript:

<script language="javascript" type="text/javascript">
function exportTable() {
var x = document.getElementById('table_id').rows;
var xls = new ActiveXObject("Excel.Application");
xls.visible = true
xls.Workbooks.Add
for (i = 0; i < x.length; i++) {
var y = x[i].cells;
for (j = 0; j < y.length; j++) {
xls.cells(i + 1, j + 1).value = y[j].innerText;
}
}
}
</script>


HTML


<input type='button' value='Export To Excel' onclick="exportTable();"/>

How to export SharePoint survey into an Excel file with date fields?

Go to the survey actions and click on Export to spreadsheet.

If you want to include any of the missing fields then open the 'Overview' list view of the survey in SharePoint designer. Add the missing fields like below in the advanced mode.

    <ViewFields>
<FieldRef Name="Created"/>
<FieldRef Name="Author"/>
<FieldRef Name="Sample_x0020_Text"/>
</ViewFields>

SharePoint Application Page: How to export DataTable data to Excel file using ClosedXML?

Installing ClosedXML in your project (VS)

Go to Tools > Library Package Manager > Manage NuGet Packages..

Search for ClosedXML and install it. This will install DocumentFormat.OpenXML as well for you.

Reference to above should be added under References automatically. If not then you can add them manually.

Code:

using DocumentFormat.OpenXml;
using ClosedXML.Excel;
using System.Xml;

using System.IO;

protected void Page_Load(object sender, EventArgs e)
        {
//So that page does not stop responding after generating Excel file (Response.End())
btnExportExcel.OnClientClick = "_spFormOnSubmitCalled = false;_spSuppressFormOnSubmitWrapper=true;";
}


protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = new DataSet();
                //Call method which returns DataTable
                ds.Tables.Add(GetDataTable());
                ExportToExcel(ds);
            }
            catch (Exception ex)
            {
                lblMessage.Text = "Error: " + ex.Message;
            }
        }


protected void ExportToExcel(DataSet ds)
        {
            XLWorkbook wb = new XLWorkbook();
            DataTable dt = ds.Tables[0];
            wb.Worksheets.Add(dt);

            string fileName = Server.UrlEncode("Report" +  "_" + DateTime.Now.ToShortDateString() + ".xlsx");
            MemoryStream stream = (MemoryStream)GetStream(wb);

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
            Response.ContentType = "application/vnd.ms-excel";
            Response.BinaryWrite(stream.ToArray());
            Response.Flush();
            Response.End();
        }


        public Stream GetStream(XLWorkbook excelWorkbook)
        {
            Stream fs = new MemoryStream();
            excelWorkbook.SaveAs(fs);
            fs.Position = 0;
            return fs;
        }

How to export DataTable or Data Grid View data into Excel file ?

Method to convert DataTable rows into Excel file:

using SWF = System.Windows.Forms;using IO = System.IO;
 public static string pPopulateExcel(DataTable myTable)
{  StringBuilder sb = new StringBuilder();
sb.AppendLine("<table cellspacing='0' cellpadding='4' rules='all' bordercolor='#CCCCCC' border='1' style='color:Black;background-color:White;border-color:#CCCCCC;border-width:1px;border-style:Solid;font-family:Tahoma;font-size:10pt;height:24px;border-collapse:collapse;'>");
sb.AppendLine("<tr style='color:Blue;background-color:aliceblue;font-weight:bold;'>");
sb.AppendLine("<td align='center'>Sl.No.</td>");
for (int llngCol = 0; llngCol < myTable.Columns.Count; llngCol++)
sb.AppendLine("<td align='center'>" + myTable.Columns[llngCol].ColumnName + "</td>");
sb.AppendLine("</tr>");
if (myTable.Rows.Count > 0)

{ int i = 1;
foreach (DataRow objDR in myTable.Rows)
{
 sb.AppendLine("<tr class='body'>");

sb.AppendLine("<td align='right'>" + i + "</td>");
for (int llngCol = 0; llngCol < myTable.Columns.Count; llngCol++)
{
switch (myTable.Columns[llngCol].DataType.ToString())

{
 case "System.Int32":
case "System.Decimal":
case "System.Double":
sb.AppendLine("<td align='right'>" + objDR[llngCol]);
break;
case "System.DateTime":
sb.AppendLine("<td align='center'>");
if (Convert.ToDateTime(objDR[llngCol]) != DateTime.MinValue)
sb.AppendLine((objDR[llngCol].ToString().Length == 0 ? "" : Convert.ToDateTime(objDR[llngCol].ToString()).ToString("dd-MMM-yyyy")) + "");
else
sb.AppendLine("&nbsp;");
break;
case "System.String":
sb.AppendLine("<td align='left'>" + objDR[llngCol]);
break;
default:
sb.AppendLine("<td align='center'>" + objDR[llngCol]);
break;

}
sb.AppendLine("</td>");

}
sb.AppendLine("</tr>");

i++;

}
sb.AppendLine("</table>");

}
else
sb.AppendLine("<tr class='body'><td colspan='" + (myTable.Columns.Count + 1) + "' align='left'>No Records found!</td></tr></table>");
return sb.ToString();

}

Export To Excel Button:

private void btnExportToExcel_Click(object sender, EventArgs e)
{ string XLSFileName = IO.Path.Combine(CurrentDirectory(), "Status Report " + DateTime.Now.ToString("ddMMMyyyyHHmm") + ".xls");

StringBuilder sbExcelText = new StringBuilder();

sbExcelText.AppendLine(ExcelUtil.pPopulateExcel((DataTable)dataGridResults.DataSource));

sbExcelText.AppendLine("<br />");

IO.File.WriteAllText(XLSFileName, sbExcelText.ToString());

SWF.MessageBox.Show("Completed! File: " + XLSFileName, "Status Report");

}


Method to get current directory path:

internal static string CurrentDirectory()

{string lstrPath = System.IO.Path.GetDirectoryName(SWF.Application.ExecutablePath).ToLower();

if (lstrPath.Contains(@"\bin\debug") || lstrPath.Contains(@"\bin\release") || lstrPath.Contains(@"\bin\x86"))

{lstrPath = System.IO.Path.GetDirectoryName(SWF.Application.ExecutablePath).Substring(0, lstrPath.IndexOf(@"\bin"));

}return lstrPath + @"\Reports";
}