dotNETportal.cz

Dobry den,

uz jsem zde psal jednou ale nikdo mi neodpovedel. Proc nemohu precist vsechna data z Excelu(formaty typu string, integer, Datum: 12.12.2012 Pondeli N 26 -0,123 12:13:23) ve sloupcich? Ve vyslednem gridu mam data ale ne vsechna? Ani zruseni vsech formatu v excelu na vysledku nic nezemnilo? Aby to bylo jeste vice zajimave - presunu li v excelu data ze sloupce ketry se precte do jineho - nic se nenacte! Ja se z toho asi zblaznim :-/

Dekuji za pripadny tip.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Xml;
using System.Diagnostics;


namespace ExcelReader
{
    public partial class mainForm : Form
    {
        public string[] workSheetNames = new string[] { };   //List of Worksheet names 
        private string fileName;  //Path and file name of the Excel Workbook
        private string connectionString;

        public mainForm()
        {
            InitializeComponent();
        }

        private void openToolStripButton_Click(object sender, EventArgs e)
        {
            if (openFileDialog.ShowDialog(this) == DialogResult.Cancel)
                return;

            fileName = openFileDialog.FileName;

            //split the text into an array using '.', this will put the
            //file extension in the last element of the array
            string[] splitByDots = fileName.Split(new char[1] { '.' });

            //Excel 97-2003 file
            if (splitByDots[splitByDots.Length - 1] == "xls")
                OpenExcelFile(false);

            //Excel 2007 file
            if (splitByDots[splitByDots.Length - 1] == "xlsx")
                OpenExcelFile(true);

            //load the sheet names in the ComboBox
            sheetsToolStripComboBox.Items.Clear();
            foreach (string sheetName in workSheetNames)
            {
                sheetsToolStripComboBox.Items.Add(sheetName);
            }

            //select the first sheet in the file
            sheetsToolStripComboBox.SelectedIndex = 0;
        }

        /// <summary>
        /// Open the Excel Workbook
        /// </summary>
        /// <param name="isOpenXMLFormat">Is the file Open XML Format (Excel 2007)</param>
        public void OpenExcelFile(bool isOpenXMLFormat)
        {
            //open the excel file using OLEDB
            OleDbConnection con;
            //string cmdExcelRangeSelection = "SELECT * FROM [A36:Z36S110]";

            if (isOpenXMLFormat)
                //read a 2007 file
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;";
            else
                //read a 97-2003 file
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    fileName + ";Extended Properties=Excel 8.0";

            con = new OleDbConnection(connectionString);
            //con = new OleDbConnection(connectionString + cmdExcelRangeSelection);
            con.Open();

            //get all the available sheets
            System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            //get the number of sheets in the file
            workSheetNames = new String[dataSet.Rows.Count];
            int i = 0;
            foreach (DataRow row in dataSet.Rows)
            {
                //insert the sheet's name in the current element of the array
                //and remove the $ sign at the end
                workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
                i++;
            }

            //13022012 ermitteln wie viele Zeilen sind belegt?
            
            int totalRows = dataSet.Rows.Count;

            

            if (con != null)
            {
                con.Close();
                con.Dispose();
            }

            if (dataSet != null)
                dataSet.Dispose();
        }

        /// <summary>
        /// Returns the contents of the sheet
        /// </summary>
        /// <param name="worksheetName">The sheet's name in a string</param>
        /// <returns>A DataTable containing the data</returns>
        public System.Data.DataTable GetWorksheet(string worksheetName)
        {

            

            //string range = "A36:Z" + totalRows - 36 ;

            OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM [" + worksheetName + "$]", con);

            con.Open();
            System.Data.DataSet excelDataSet = new DataSet();
            cmd.Fill(excelDataSet);
            con.Close();

            return excelDataSet.Tables[0];
        }

Excel 2003, Visual Studio Express 2010, .net 4.0

V Server Exploreru si vytvořte nové datové spojení použitím Microsoft JET 4.0 OLE DB Provideru, přidejte prázdný DataSet a do něj přetáhněte požadovanou tabulku ze Server Exploreru. VS automaticky vytvoří vše potřebné a není nutné napsat ani řádek kódu.

dekuji za rychlou odpoved! mam zde na vyber: Databanku, Servis, Objekt...nikde zde Jet 4.0 OLE DB nevydim ?

Jinak pracuji na malem programku(vetsinou opisovanim z knizek a tutorialu) ktery pokazde nacte data z Excelu do DataSet a postupne je zapisuje do pevne dane XML struktury radek po radku... proto to potrebuji programovat "na tvrdo"

jsem zacatecnik...