Using .NET to get data from Excel as a database
I would like to get data from Excel files using .NET. The Excel file is in use, so I cannot open it. What I am doing is related to it as a data source using OleDB.
The problem is I am getting the cell data but not its style. The date value in Excel changes to date and time format in .NET, and the cell set as currency in Excel is displayed as an integer in .NET. When exporting data from Excel using OleDB, you don't know if the cell contains date, currency, etc. For example, if a cell was set as text and contained five digits starting with zero, you would just get four digits.
I am looking for a way to get the data the way it appears if you view the data in Excel.
Using .NET 3.5 and Excel 2007.
source to share
To find out the data type of the columns, you must use the FillSchema method:
OleDbConnection conn = new OleDbConnection(...);
conn.Open();
DataSet dataSet = new DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM <table>", conn);
dataAdapter.FillSchema(dataSet, SchemaType.Source);
dataAdapter.Fill(dataSet);
//Now you can access the data type like this
dataSet.Tables[0].Columns[0].DataType
source to share
SpreadsheetGear for .NET can open an Excel 2007 xlsx workbook while a workbook is open in Excel 2007.
Excel stores dates, times, currencies, numbers, etc. as numbers (C # doubles). The number format tells Excel how to display the number. Excel and SpreadsheetGear have a property to return formatted text - Range.Text in Excel and IRange.Text in SpreadsheetGear.
SpreadsheetGear also has a property that tells you the type of cell number format so that you can determine if the cell will be formatted as date, currency, etc. if that's important to your application. See IRange.NumberFormatType Property for more information.
You can download a free trial here if you want to give it a try.
Disclaimer: I own SpreadsheetGear LLC
source to share
To connect to the excel file, you need the appropriate connection string:
string connString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=<YourExcelPath>;Extended Properties=\"Excel 12.0;HDR=YES;\"";
After using the OleDb classes to request information from a file:
string selectCmd = "SELECT * FROM <SheetName>";
using(OleDbConnection excelConn = new OleDbConnection(connString))
{
excelConn.Open();
OleDbCommand command = new OleDbCommand(selectCmd, excelConn);
OleDbDataAdapter da = new OleDbDataAdapter(command);
DataTable sheetInfo = new DataTable();
dataAdapter.Fill(sheetInfo);
//Do something with the data.
}
So, you need to replace "YourExcelPath" with the path of your excel file and "SheetName" with the name of the sheet you want to get the data on.
source to share
It's not pretty, but you can use COM Interop in Excel to read the worksheet.
This will allow you to access everything you like about the cells, but is much more error prone.
System.Reflection.Missing ms = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Open(@"C:\Demo.xlsx", ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Range rng = xlSheet.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range;
System.Console.WriteLine(String.Format("{0} {1} {2}", rng.Value2, rng.Text, rng.NumberFormat));
xlBook.Close(false, ms, ms);
xlApp.Quit();
System.Console.ReadLine();
source to share
You can use IMEX = 1 on the connection string to force all columns to be read as text, rather than trying to infer the data type from OleDB.
I believe this will take into account the formatting of cells containing numeric values, but leave it to you to check and validate.
source to share
There are several ways to accomplish this. But I will tell you a very easy way to get data from an excel file as a database using a very useful and efficient third party library.
1. First, you will need to export the data to a Spreadsheet file in C # memory.
2. And then execute the SQL script to upload the data to your MySql server. As for the first step, you can use a third party component for ASP.NET . One of them is Ultimate Excel . It is a very large library for using excel with .Net using C #. Here is a sample code on how to get data from an Excel file using this library:
// Create a Workbook Manager to load the file.
WorkbookManager manager = new WorkbookManager();
// Load the Excel file. 'FileName' variable contains the file path to load.
//You can also specify the formal of excel file by using LoadFormat.SpreadsheetML
//SpreadsheetML load XML schema
var FileName = C:/"sample.xls"
Workbook workbook = manager.Workbooks.Open(FileName,
LoadFormat.SpreadsheetML);
// Access the 1st sheet
IWorksheet sheet = workbook.Worksheets[0];
// Now we export the data to a DataTable object.
dataTable = sheet.ExportDataTable(sheet["A1:C10"], ExportDataTableOptions.None);
// We can now work with the exported data in the dataTable object.
// ...
In the second step you will need to use EntityFramework , Ado.Net any SQL ORM library. I think this part is pretty easy for everyone.
Hope this question helps you accomplish your task.
source to share