CODEWITHSUNDEEP
c#excel
Romeo
Romeo

Reputation: 57

Store excel column in a string array ( store blank cell values too )

I want to store excel file column in a string array, but blank cell values are not stored as empty strings. Example:

excel column
C1 = "1"
C2 = "2"
C3 = blank
C4 = "3"

so it is stored as

mystring[0]="1"
mystring[1]="2"
mystring[2]="3"

the blank value is omitted. I want to store it as 

mystring[0]="1"
mystring[1]="2"
mystring[2]=" "
mystring[3]="3"

this is the code I use

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ConsoleApplication6
{
    class Program
    {
        public static void Main()
        {
            string path =@"e:\contacts.xls"; 
            Application xlApp = new Application();          
            Workbook xlWb = xlApp.Workbooks.Open(path,0,true);
            Worksheet xlSheet = xlWb.Sheets[1] ; 
            Range range = xlSheet.UsedRange.Columns[2];
            System.Array myvalues = (System.Array)range.Cells.Value;
            string[] mystring = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();

            foreach (var i in mystring)
                Console.WriteLine(i);
            Console.Read();
        }
    }
}

Upvotes: 1

Views: 771

Answers (2)

Romeo
Romeo

Reputation: 57

Thank u Vityata
I was having problems dealing with those null values from excel
I replaced this code

Range range = xlSheet.UsedRange.Columns[2];
System.Array myvalues = (System.Array)range.Cells.Value;
string[] mystring = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();

with this one

List<string> mystring = new List<string>();
for (int i = 1; i <= 300; ++i)            
mystring.Add(Convert.ToString((xlSheet.Cells[i, 3]).Value));

Thank u :)

Upvotes: 0

Vityata
Vityata

Reputation: 43565

Reading from to is always an interesting task. What I am usually using is this:

object[,] matrixRead = (object[,])currentRange.Value;

The empty cells are presented as Null, as you can see here:

enter image description here

and I am pretty sure that you can find a way to make them "" later. This is the code I was using:

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

class StartUp
{
    static void Main()
    {
        string filePath = @"E:\contacts.xls";

        int rowsCount = 5;
        int colsCount = 6;

        Excel.Application excel = new Excel.Application();
        excel.Visible = false;
        excel.EnableAnimations = false;

        Excel.Workbook wkb = Open(excel, filePath);
        Excel.Worksheet wk = (Excel.Worksheet)excel.Worksheets.get_Item(1);

        Excel.Range startCell = wk.Cells[1, 1];
        Excel.Range endCell = wk.Cells[rowsCount, colsCount];
        Excel.Range currentRange = wk.get_Range(startCell, endCell).Cells;
        currentRange.Interior.Color = Excel.XlRgbColor.rgbWhite;

        object[,] matrixRead = (object[,])currentRange.Value;
        bool[,] matrixResult = new bool[rowsCount + 1, colsCount + 1];

        excel.EnableAnimations = true;
        wkb.Close(true);
        excel.Quit();
        Console.WriteLine("Finished!");
    }

    private static Excel.Workbook Open(Excel.Application excelInstance,
            string fileName, bool readOnly = false,
            bool editable = true, bool updateLinks = true)
    {
        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;
    }
}

Source for the code (disclaimer - my site)

Upvotes: 1

Related Questions