CODEWITHSUNDEEP
c#excel
kb_
kb_

Reputation: 630

Cast Excel Cells

I want to generate a SQL Script from an Excel Sheet and therefore i need to know the types from all cells.

Therefore i tried to get the type from the cells in the following code with the results afterward in a textbox

C# code:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

namespace Export
{
    internal class ExcelWorker
    {
        Excel.Application _xlApp = new Excel.Application();
        Excel.Workbook _xlWorkBook;
        Excel.Range _range;
        object misValue = System.Reflection.Missing.Value;
        private void Show(string value) => MessageBox.Show(value);
        internal void ReadExcelFile(string path, string cell)
        {
            try
            {
                _xlWorkBook = OpenBook(_xlApp, path, false, true, false);
                Excel.Worksheet sheet = _xlWorkBook.Sheets["Sheet1"] as Excel.Worksheet;
                Show(string.Format("Cell {0} \n\n Cell Number Format {1}", cell, sheet.get_Range(cell).NumberFormat));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        private static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
        bool updateLinks)


 {
                Excel.Workbook book = excelInstance.Workbooks.Open(
                    fileName, updateLinks, readOnly,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                return book;
          }
    }
}

excel sheet: excel sheet results for each cell:

enter image description here

Now i'm not sure if that's the only to get the "correct" type to cast the excel cell values to the exact sql type or if there's a better solution i don't know.

Is there a better way to get the type for each cell or should I create a enum or something like this and map each possible cell format ?

Upvotes: 2

Views: 932

Answers (1)

Marcel Theis
Marcel Theis

Reputation: 313

You won´t be able to get the type of a cell. If you want to enter data from an excel sheet into a db, try casting to the needed SQL-type and fromat. Especially for datetime this is mandatory as the datetime format depends on you db settings.
Be carefull with empty cells. You have to check them before casting. In Office 2010 and 2013 empty cells are NULL. You need to check this before using ToString() or something like this, because this would throw an exceeption. Also be cautious with double, e.g. using "," or "." this depends on you settings in Ecxel.

Upvotes: 1

Related Questions