StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Connect to the Excel sheet via the ADO.NET OleDb provider. Then, use a JSON library for C# to generate the JSON string, and save the file. (Or use the JavascriptSerialzer, as @boades suggested).</p> <p>This example uses the JSON.NET library.</p> <pre><code>using System; using System.Linq; using System.Data.OleDb; using System.Data.Common; using Newtonsoft.Json; using System.IO; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { var pathToExcel = @"C:\path\to\excel\file.xlsx"; var sheetName = "NameOfSheet"; var destinationPath = @"C:\path\to\save\json\file.json"; //Use this connection string if you have Office 2007+ drivers installed and //your data is saved in a .xlsx file var connectionString = $@" Provider=Microsoft.ACE.OLEDB.12.0; Data Source={pathToExcel}; Extended Properties=""Excel 12.0 Xml;HDR=YES"" "; //Creating and opening a data connection to the Excel sheet using (var conn=new OleDbConnection(connectionString)) { conn.Open(); var cmd=conn.CreateCommand(); cmd.CommandText = $"SELECT * FROM [{sheetName}$]"; using (var rdr=cmd.ExecuteReader()) { //LINQ query - when executed will create anonymous objects for each row var query = rdr.Cast<DbDataRecord>().Select(row => new { name = row[0], regno = row[1], description = row[2] }); //Generates JSON from the LINQ query var json = JsonConvert.SerializeObject(query); //Write the file to the destination path File.WriteAllText(destinationPath, json); } } } } } </code></pre> <p>Links:</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/ms254937%28v=vs.110%29.aspx" rel="nofollow noreferrer">Retrieving and Modifying Data in ADO.NET</a> - how to use connections, commands and readers</li> <li><a href="http://www.connectionstrings.com/excel-2007" rel="nofollow noreferrer">Excel 2007 connection strings</a></li> <li><a href="http://www.connectionstrings.com/excel" rel="nofollow noreferrer">Older Excel connection strings</a> under the 'OLEDB Providers' section</li> <li>JSON libraries for C# can be found on the <a href="http://www.json.org/" rel="nofollow noreferrer">JSON</a> page</li> <li><a href="http://json.codeplex.com/" rel="nofollow noreferrer">JSON.NET</a></li> <li><a href="http://www.microsoft.com/en-gb/download/details.aspx?id=13255" rel="nofollow noreferrer">Microsoft Access Database Engine 2010 Redistributable</a>, if you don't have Excel 2007+ installed</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload