Exemplo n.º 1
0
        /// <summary>
        /// Converts a worksheet to a simple DataTable.
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="wsp"></param>
        /// <returns></returns>
        protected DataTable WorksheetToDatatable(SpreadsheetDocument doc, WorksheetPart wsp)
        {
            //Read the first Sheet from Excel file.
            // Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();

            //Get the Worksheet instance.
            //Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;

            Worksheet worksheet = wsp.Worksheet;

            //Fetch all the rows present in the Worksheet.
            IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>();

            //Create a new DataTable.
            DataTable dt = new DataTable();


            //Loop through the Worksheet rows.
            // NOTE:  This loop is the longest part of the import process by far.
            var rowCount = 0;

            foreach (Row row in rows)
            {
                rowCount++;

                // When we run out of content rows, get out.
                // There are blank rows within the first 12 rows, hence the 75 threshold.
                if (rowCount > 75 && row.InnerText == "")
                {
                    break;
                }

                var newRow   = dt.Rows.Add();
                int colIndex = 0;
                for (int j = 0; j < row.Descendants <Cell>().Count(); j++)
                {
                    var cell = row.Descendants <Cell>().ElementAt(j);

                    // lazily add column on the fly, if necessary
                    if (dt.Columns.Count < j + 1)
                    {
                        dt.Columns.Add(ImportManagerAwwa.ColumnIndexToColumnLetter(j + 1));
                    }

                    newRow[colIndex] = ImportManagerAwwa.GetValue(doc, cell);
                    colIndex++;
                }
            }

            return(dt);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Converts a worksheet to a simple DataTable.
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="wsp"></param>
        /// <returns></returns>
        protected DataTable WorksheetToDatatable(SpreadsheetDocument doc, WorksheetPart wsp)
        {
            //Read the first Sheet from Excel file.
            // Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();

            //Get the Worksheet instance.
            //Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;

            Worksheet worksheet = wsp.Worksheet;

            //Fetch all the rows present in the Worksheet.
            IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>();

            //Create a new DataTable.
            DataTable dt = new DataTable();



            //Loop through the Worksheet rows.
            foreach (Row row in rows)
            {
                var newRow   = dt.Rows.Add();
                int colIndex = 0;
                for (int j = 0; j < row.Descendants <Cell>().Count(); j++)
                {
                    var cell = row.Descendants <Cell>().ElementAt(j);

                    // lazily add column on the fly, if necessary
                    if (dt.Columns.Count < j + 1)
                    {
                        dt.Columns.Add(ImportManagerAwwa.ColumnIndexToColumnLetter(j + 1));
                    }

                    newRow[colIndex] = ImportManagerAwwa.GetValue(doc, cell);
                    colIndex++;
                }
            }

            return(dt);
        }