Converting Excel into XML – .NET

we have a scenario, where we need to pull the report from SSRS server using ReportExecution Service (check Get SSRS report programatically using C#/.NET).

However the stream returned in some cases contains some special characters and we were not able to load the stream in an xml document.

Then we decided to export the SSRS report in Excel and then convert the excel in XML format.

For this we first saved the excel in a physical location, and then convert the excel into XML using .NET’s OLEDB object.

//excelFile is path of the excel file that we have retrieved from SSRS service.
public static XmlDocument GetXMLFromExcel(string excelFile)
{
	OleDbConnection objConn = null;
	System.Data.DataTable dt = null;
	String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
			"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";// HDR=Yes; IMEX=1;";
	// Create connection object by using the preceding connection string.
	objConn = new OleDbConnection(connString);
	// Open connection with the database.
	objConn.Open();
	// Get the data table containg the schema guid.
	dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
	//get the first sheet name
	string strSheetName = dt.Rows[0]["TABLE_NAME"].ToString();

	string strComand;
	strComand = "select * from [" + strSheetName + "]";

	OleDbDataAdapter daAdapter = new OleDbDataAdapter(strComand, objConn);
	dt = new DataTable(strSheetName);
	daAdapter.FillSchema(dt, SchemaType.Source);
	daAdapter.Fill(dt);
	objConn.Close();

	DataSet ds = new DataSet();
	//remove $ sign from table name
	dt.TableName = dt.TableName.Replace("$", string.Empty);
             ds.Tables.Add(dt);
	string spaceString = "_x0020_";
	//replace special character space with _ (underscore)
	string xmlString = ds.GetXml().Replace(spaceString, "_");

	XmlDocument xmlDoc = new XmlDocument();
	xmlDoc.LoadXml(xmlString);

	return xmlDoc;
}