Strange problem in passing datatable

Question

4.00/5 (1 vote)

See more:

In a project, ExcelLibrary is used to read data from an Excel file in xls format. I have met a strange problem in passing a datatable. In the main program, if I use such code:

C#

ClientXLS ClientNewXLS = new ClientXLS();
ClientNewXLS.ExcelInput(openFile.FileName, dataTable_Excel);

while in the class ClientXLS:

C#

class ClientXLS
{
    public void ExcelInput(string FileOpen, DataTable dataTable_Excel)
    {
        CompoundDocument doc = null;
        Workbook workbook = null;
        if (File.Exists(FileOpen))
        {
            doc = CompoundDocument.Open(FileOpen);

            byte[] bookdata = doc.GetStreamData("Workbook");
            workbook = WorkbookDecoder.Decode(new MemoryStream(bookdata));
            foreach (Worksheet worksheet in workbook.Worksheets)
            {
                if (worksheet.Visible)
                {
                    dataTable_Excel = DataSetHelper.CreateDataTable(worksheet, false);
                }
            }
            dataTable_Excel.Rows[0].Delete();
        }
        if (doc != null)
            doc.Close();
    }
}

The dataTable_Excel in the main program is null, while I find the dataTable_Excel has got the right data in class ClientXLS by debug. It is obvious that the passing of datatable was wrong.

If I use such code in the main program:

C#

ClientXLS ClientNewXLS = new ClientXLS();
dataTable_Excel = ClientNewXLS.ExcelInput(openFile.FileName);

while in the class ClientXLS:

C#

class ClientXLS
{
    public DataTable ExcelInput(string FileOpen)
    {
        DataTable dataTable_Excel = new DataTable();

        CompoundDocument doc = null;
        Workbook workbook = null;
        if (File.Exists(FileOpen))
        {
            doc = CompoundDocument.Open(FileOpen);

            byte[] bookdata = doc.GetStreamData("Workbook");
            workbook = WorkbookDecoder.Decode(new MemoryStream(bookdata));
            foreach (Worksheet worksheet in workbook.Worksheets)
            {
                if (worksheet.Visible)
                {
                    dataTable_Excel = DataSetHelper.CreateDataTable(worksheet, false);
                }
            }
            dataTable_Excel.Rows[0].Delete();
        }
        if (doc != null)
            doc.Close();

        return dataTable_Excel;
    }
}

All runs Ok! I have struggled for nearly a week, but got no answer. Has anyone met such problem before?

Posted 1-Aug-12 5:18am

daiwuju

Updated 1-Aug-12 5:51am

v10

Add a Solution

2 solutions

Solution 2

Asymmetrical Wedding Dresses

Asymmetrical Wedding gowns

cheap Asymmetrical Wedding Dresses

buy Asymmetrical Wedding Dresses

Ball Gown Wedding Dresses

Posted 1-Aug-12 6:55am

nrynoel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Philip Stuyck · Accepted Answer · 2012-08-01T05:57:00

Solution 1

Of course it does not work.
DataTable dataTable_Excel is being passed as parameter, but the dataTable_excel is null. If you want to create this reference dynamically in the function you need to pass it by reference like this :

ClientXLS ClientNewXLS = new ClientXLS();
DataTable dataTable_Excel=null;
ClientNewXLS.ExcelInput(openFile.FileName, ref dataTable_Excel);

The reason is that without ref, the passing is being done by value. So the reference to dataTable_Excel is passed by value. If you then assign a different value to it, it won't be noticed when the function returns. That is basically what you are doing.
So you are mixing up the concept of
pass by value
pass by reference.
note:

ClientNewXLS.ExcelInput(openFile.FileName, out dataTable_Excel);

this also works.

Posted 1-Aug-12 5:57am

Philip Stuyck

Comments

daiwuju 1-Aug-12 23:29pm

Thank you very much for the answer. Your code runs OK!
I don't use ref or out in the method because I have use the following code in the main program, the first if block is used to read file in Excel2007-2010 format(xlsx) by OLEDB (realized in class ClientExcelXML), and the second if block is used to read file in Excel97-2003 format(xls) by ExcelLibrary with the code of class ClientXLS in the question:
-------------------------------------------------------------------
if (openFile.FileName.EndsWith(".xlsx"))
{
ClientExcelXML ClientNewXML = new ClientExcelXML();
ClientNewXML.ExcelInput(openFile.FileName, dataTable_Excel);
}
else if (openFile.FileName.EndsWith(".xls"))
{
ClientXLS ClientNewXLS = new ClientXLS();
ClientNewXLS.ExcelInput(openFile.FileName, dataTable_Excel);
}
-------------------------------------------------------------------

The code of class ClientExcelXML is:
-------------------------------------------------------------------
class ClientExcelXML
{
public void ExcelInput(string FileOpen, DataTable dataTable_Excel)
{
string strExcelFile = FileOpen;
strExcelFile =string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties = \"Excel 12.0 Xml; HDR = YES; IMEX = 1; TypeGuessRows = 0; ImportMixedTypes = Text\";",strExcelFile);
OleDbConnection conOLDB = new OleDbConnection(strExcelFile);
conOLDB.Open();
DataTable dataTable_ExcelSheet = conOLDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dataTable_ExcelSheet.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectstr = string.Format("SELECT * FROM [{0}]", sheetName);
OleDbCommand selectCommand = new OleDbCommand(selectstr, conOLDB);
OleDbDataAdapter daExcel = new OleDbDataAdapter();
daExcel.SelectCommand = selectCommand;
daExcel.Fill(dataTable_Excel);
conOLDB.Close();
conOLDB.Dispose();
}
}
-------------------------------------------------------------------
The first if block runs Ok, but the second never runs. It seems to me there is no difference between these two if block, and this made me confused until now. Could you give me some tip?