Import data from excel without using OLEDB
I read from excel into a mysql database. Maybe not the nicest way todo it. But here is my code:
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"\\xxxx\yyyy.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
string Kund = "";
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if ((j == 1) && (i > 1))
{
MySqlConnection conn = new MySqlConnection(databas);
Kund = xlRange.Cells[i, j].Value2.ToString();
}
...
}
...
}
xlApp.Workbooks.Close();
You have to use using Excel = Microsoft.Office.Interop.Excel;
Developer
Updated on August 02, 2022Comments
-
Developer almost 2 years
Hi all is it possible to load excel data without using
OLEDB
, I have written a code using the connection string as follows for.xls and .xlsx
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
In my system as
12.0
relateddll
is not present I got an error. So I would like to know whether there is any way to achieve my requirement rather than the general approachSample Code written
public void Method(string pFilePath) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(pFilePath, false)) { WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart; IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>(); string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId); Worksheet workSheet = worksheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild<SheetData>(); IEnumerable<Row> rows = sheetData.Descendants<Row>(); foreach (Cell cell in rows.ElementAt(0)) { dt.Columns.Add(GetCellValue(spreadSheetDocument, cell)); } foreach (Row row in rows) { DataRow tempRow = dt.NewRow(); for (int i = 0; i < row.Descendants<Cell>().Count(); i++) { tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i - 1)); } dt.Rows.Add(tempRow); } } dt.Rows.RemoveAt(0); } public static string GetCellValue(SpreadsheetDocument document, Cell cell) { SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart; string value = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; } else { return value; } }
But in
foreach (Row row in rows)
this condition I am getting an exception asSpecified argument was out of the range of valid values. Parameter name: index
-
Developer almost 11 yearsHi if I need to Import both excel file and open office spread sheet data what should I use commonly