c# Import excel file

Question

I importing excel to datatable in my asp.net project.

I have below code:

 string excelConString = string.Format(
 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" +
 "Extended Properties='Excel 8.0;" + 

 "IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;'", filepath);



using (OleDbConnection connection = new OleDbConnection(excelConString))
{

connection.Open();
string worksheet;

worksheet = "Sheet 1$";

string connStr;
connStr = string.Format("Select * FROM `{0}`", worksheet);
OleDbDataAdapter daSheet = new OleDbDataAdapter(connStr, connection);
DataSet dataset = new DataSet();
DataTable table;
table = new DataTable();
daSheet.Fill(table);
dataset.Tables.Add(table);

connStr = string.Format("Select * FROM `{0}$`", worksheet);

table = new DataTable();
daSheet.Fill(table);
dataset.Tables.Add(table);

}

When i run above code in order to import excel, last data always missing because last data has special character like below

"İ,Ö,Ş" etc.

How can i solve this problem.I added below code

"IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;

however it is not working for me.

Any help will be appreciated.

Thank you

Answer 1

Just answer this question for other readers if any. If you prefer to handle with POCO directly with Excel file, recommend to use my tool Npoi.Mapper, a convention based mapper between strong typed object and Excel data via NPOI.

Get objects from Excel (XLS or XLSX)

var mapper = new Mapper("Book1.xlsx");
var objs1 = mapper.Take<SampleClass>("sheet2");

// You can take objects from the same sheet with different type.
var objs2 = mapper.Take<AnotherClass>("sheet2");

Export objects to Excel (XLS or XLSX)

//var objects = ...
var mapper = new Mapper();
mapper.Save("test.xlsx",  objects, "newSheet", overwrite: false);

Put different types of objects into memory workbook and export together.

var mapper = new Mapper("Book1.xlsx");
mapper.Put(products, "sheet1", true);
mapper.Put(orders, "sheet2", false);
mapper.Save("Book1.xlsx");