ByteScout Spreadsheet SDK – C# – Convert XLS to SQL Server

Home
/
Articles
/
ByteScout Spreadsheet SDK – C# – Convert XLS to SQL Server

printable version:
ByteScout-Spreadsheet-SDK-C-sharp-Convert-XLS-to-SQL-Server.pdf

How to convert XLS to SQL server in C# and ByteScout Spreadsheet SDK

This code in C# shows how to convert XLS to SQL server with this how to tutorial

We made thousands of pre-made source code pieces for easy implementation in your own programming projects. ByteScout Spreadsheet SDK: the SDK to create, read, modify and calculate spreadsheets. Formula calculations are supported, import and export to and from JSON, CSV, XML, databases, arrays. It can convert XLS to SQL server in C#.

You will save a lot of time on writing and testing code as you may just take the C# code from ByteScout Spreadsheet SDK for convert XLS to SQL server below and use it in your application. In your C# project or application you may simply copy & paste the code and then run your app! This basic programming language sample code for C# will do the whole work for you to convert XLS to SQL server.

Free trial version of ByteScout Spreadsheet SDK is available on our website. Documentation and source code samples are included.

On-demand (REST Web API) version:
Web API (on-demand version)

On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)

Program.cs

      using System;
using System.IO;
using Bytescout.Spreadsheet;
using System.Data.SqlClient;

namespace ExportToSQLServer
{
	class Program
	{
		static void Main(string[] args)
		{
			try
			{
				// MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
				string connectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=true;";

				using (SqlConnection connection = new SqlConnection(connectionString))
				{
					connection.Open();

					// Drop test database if exists
					ExecuteQueryWithoutResult(connection,
											  "IF DB_ID ('XlsTests') IS NOT NULL DROP DATABASE XlsTests");
					// Create empty database
					ExecuteQueryWithoutResult(connection, "CREATE DATABASE XlsTests");
					// Switch to created database
					ExecuteQueryWithoutResult(connection, "USE XlsTests");
					// Create a table for XLS data
					ExecuteQueryWithoutResult(connection,
											  "CREATE TABLE XlsTest (Name VARCHAR(40), FullName VARCHAR(255))");

					// Load XLS document
					using (Spreadsheet document = new Spreadsheet())
					{
						document.LoadFromFile("SimpleReport.xls");
						Worksheet worksheet = document.Workbook.Worksheets[0];

						for (int row = 0; row <= worksheet.UsedRangeRowMax; row++)
						{
							String insertCommand = string.Format("INSERT XlsTest VALUES('{0}','{1}')",
								worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value);
							ExecuteQueryWithoutResult(connection, insertCommand);
						}
					}
					
					// Check the data successfully exported
					using (SqlCommand command = new SqlCommand("SELECT * from XlsTest", connection))
					{
						SqlDataReader reader = command.ExecuteReader();

						if (reader != null)
						{
							Console.WriteLine();
							Console.WriteLine("Exported XLS data:");
							Console.WriteLine();

							while (reader.Read())
							{
								Console.WriteLine(String.Format("{0}  |  {1}", reader[0], reader[1]));
							}
						}
					}

					Console.WriteLine();
					Console.WriteLine("Press any key.");
					Console.ReadKey();
				}
			}
			catch(Exception ex)
			{
				Console.WriteLine("Error: " + ex.Message);
				Console.ReadKey();
			}
		}

		static void ExecuteQueryWithoutResult(SqlConnection connection, string query)
		{
			using (SqlCommand command = new SqlCommand(query, connection))
			{
				command.ExecuteNonQuery();
			}
		}
	}
}