Converting this excel to datatable

Question

I was earlier successful at converting Excel to datatable, but now, I have weirdly formatted table, following picture will show excel table, and table that I want to achieve.

var workbook = Workbook.Load("file.xls");
var worksheet = workbook.Worksheets[0];
var cells = worksheet.Cells;
var dataTable = new DataTable("datatable");


for (int colIndex = cells.FirstColIndex; colIndex <= cells.LastColIndex; colIndex++)
{
    dataTable.Columns.Add(cells[0, colIndex].StringValue);
}

for (int rowIndex = cells.FirstRowIndex; rowIndex <= cells.LastRowIndex; rowIndex++)
{
    var values = new List<string>();
    foreach(var cell in cells.GetRow(rowIndex))
    {
        values.Add(cell.Value.StringValue);
    }

    dataTable.LoadDataRow(values.ToArray(), true);
}

This is the code. When I use this with mentioned .xls I'm getting "price: row shifted left. Example: https://i.stack.imgur.com/2jZQY.png So, any help about how to solve this is more than welcome. Thanks.

Answer 1

Here is a reference link that someone had the same issue with and how to resolve the Issue

How To Resolve Exce.Interop Issue Culture Settings

The bug is that .NET checks that your thread (C# or VB code) localization is suitable to MS Excel localization you installed earlier, and if not it tells that the Microsoft.Office.Interop library is old or invalid. Your thread localization is derived from your computer regional settings (from the control panel --> regional and language)

Then there are two options to solve this problem: 1. To change your thread localization (by code) 2. Install language pack for your Office

The first solution goes like this:

using System.Threading;     // For setting the Localization of the thread to fit
using System.Globalization; // the of the MS Excel localization, because of the MS bug.

            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
            excelFileName = System.IO.Path.Combine(excelPath, "Ziperty Buy Model for Web 11_11_2011.xlsm");

Answer 2

You can retrieve it without opening it

use "Excel 12.0 Macro" instead of "Excel 12.0" only for xlsm file

HDR=YES means that your Excel table has header

 Dim myWorkBookPath AS String = "C:\test.xlsx"
    Dim mySheetName As String= "Sheet1"
    Dim myRangeAddress as String = "A1:K10"  'if you retrieve this by program, think to remove all $ signs . .Replace("$","")
         Dim myXlConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES""", myWorkBookPath)
              Dim myXlConnection = New OleDb.OleDbConnection(myXlConnectionString)
              Dim sXlCommand As OleDbCommand = New OleDbCommand(String.Format("Select * FROM [{0}${1}]", mySheetName, myRangeAddress), myXlConnection)
              Dim dt = New Data.DataTable()
              Dim da = New OleDbDataAdapter(sXlCommand)
              myXlConnection.Open()
              da.Fill(dt)
              myXlConnection.Close()
              Return dt

Answer 3

put into your using at the top of the .cs file

using System.Reflection; 
using Microsoft.Office.Interop.Excel;

        // TO USE:
        // 1) include COM reference to Microsoft Excel Object library
        // add namespace...
        // 2) using Excel = Microsoft.Office.Interop.Excel;
        private static void Excel_FromDataTable(<strong class="highlight">DataTable</strong> dt)
        {
            // Create an Excel object and add workbook...
            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???

            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;

                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }

            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;

            // If wanting to Save the workbook...
            workbook.SaveAs("TestExcel.xls",
                Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                missing, missing, missing, missing, missing);

            // If wanting to make Excel visible and activate the worksheet...
            excel.Visible = true;
            Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
            ((Excel._Worksheet)worksheet).Activate();

            // If wanting excel to shutdown...
            ((Excel._Application)excel).Quit();
        }