Reading XLS Data

[Buda56]

Bruce,
I am having difficulty trying to read data from a spreadsheet
using the column headers, for some reason it starts at row 0 but after
reading a couple of columns jumps to row 36??

I have included my code below for review..

protected void ddlFiles_SelectedIndexChanged(object sender,
EventArgs e)
{
lblError.Text = "";
string txtFile = ddlFiles.SelectedItem.Text;
if (txtFile != "")
{
string[] cHeader;
int HeadCount = 0;
int Flag = 0;
int errFlag = 0;
decimal THrs = 0;
decimal TTHrs = 0;
decimal DHrs = 0;
DateTime myDate;
TSComplianceCollection TColl = new
TSComplianceCollection();
TSCompliance TEnt = new TSCompliance();
XlsReader xlsData = new
XlsReader(Server.MapPath("TSCoImport") + "/" + txtFile);
xlsData.Settings.HasHeaders = true;
try
{
cHeader = xlsData.Headers;
for (int i = 0; i < cHeader.Length; i++)
{
if (cHeader[i] != "")
HeadCount += 1;
}
if (HeadCount == 18)
{
Flag = 0;
for (int i = 0; i < cHeader.Length; i++)
{
if (cHeader[i].ToUpper() == "ORG.NUM.")
Flag += 1;
if (cHeader[i].ToUpper() == "EMPLOYEE NAME")
Flag += 1;
if (cHeader[i].ToUpper() == "E")
Flag += 1;
if (cHeader[i].ToUpper() == "PER.NUM.")
Flag += 1;
if (cHeader[i].ToUpper() == "REJECTED HS.")
Flag += 1;
if (cHeader[i].ToUpper() == "CATS ABSEN.")
Flag += 1;
if (cHeader[i].ToUpper() == "PERS.FROM")
Flag += 1;
}

if (Flag != 7)
errFlag = 1;
else
errFlag = 0;

if (Flag == 7 && errFlag == 0)
{
//Process File for import
while (xlsData.ReadRecord())
{
myDate =
Convert.ToDateTime(xlsData["Pers.From"]);

TColl.Query.Where(TColl.Query.Sapid.Equal(Convert.ToInt32(xlsData["Per.Num."])));
TColl.Query.Load();

if (TColl.Count > 0)
{
////Do nothing
}
else
{
//Add new record
TEnt.AddNew();
TEnt.Orgid =
Convert.ToInt32(xlsData["Org.Num."]); (Record 1 here)
TEnt.Employee = xlsData["Employee
Name"]; (Record 1 here)
TEnt.Sapid =
Convert.ToInt32(xlsData["Per.Num."]); (Record 1 here)
if (xlsData["Released Hs."] !=
"") (Now at record 36)
TEnt.Released =
Convert.ToDecimal(xlsData["Released Hs."]);
if (xlsData["Approved Hs."] != "")
TEnt.Approved =
Convert.ToDecimal(xlsData["Approved Hs."]);
if (xlsData["Rejected Hs."] != "")
TEnt.Reject =
Convert.ToDecimal(xlsData["Rejected Hs."]);
if (xlsData["Del-Approved"] != "")
TEnt.DelApproved =
Convert.ToDecimal(xlsData["Del-Approved"]);
if (xlsData["Del-UnApprvd"] != "")
TEnt.DelUnApproved =
Convert.ToDecimal(xlsData["Del-Unappprvd"]);
if (xlsData["CATS Absen."] != "")
TEnt.CatsAbs =
Convert.ToDecimal(xlsData["CATS Absen."]);
if (xlsData["Total-CATS"] != "")
TEnt.Catstotal =
Convert.ToDecimal(xlsData["Total-CATS"]);
if (xlsData["HR Absences"] != "")
TEnt.HRAbs =
Convert.ToDecimal(xlsData["HR Absences"]);
if (xlsData["Total Hours"] != "")
THrs =
Convert.ToDecimal(xlsData["Total Hours"]);
else
THrs = 0;
if (xlsData["Target Hrs."] != "")
TTHrs =
Convert.ToDecimal(xlsData["Target Hrs."]);
else
TTHrs = 0;
DHrs = THrs - TTHrs;
TEnt.TotalHrs = THrs;
TEnt.TargetHrs = TTHrs;
TEnt.Difference = DHrs;
TEnt.PeriodStart =
Convert.ToDateTime(xlsData["Pers.From"]);
TEnt.PeriodEnd =
Convert.ToDateTime(xlsData["Pers.To"]);
if (DHrs < 0)
TEnt.Completed = false;
else
TEnt.Completed = true;
}
TEnt.Save();

[shriop]

I don't see anything in your code that would cause it. Does the same
issue appear if you try to load up the file into a grid using the demo
projects from the download on the site? Can you email me a sample
file?

Bruce Dunwiddie

[Buda56]

Bruce,
I am able to open the file okay using the Demo XLS project,
but that only uses a reader and readsd the whole file then assigned to
a grid.
If I can find your email address I will email you the file so you can
have a look at it..

Regards..
Peter

> >                             TEnt.Save();- Hide quoted text -
>
> - Show quoted text -

[shriop]

I was not able to reproduce the issue using the code below and the
file that you sent me. It loops the expected number of times and
outputs the values I would expect. I'm not sure how you're telling
that it jumps to record 36 on the particular line that you're
identifying because the released hours for record 1, per num 2000067,
is the same as record 36, per num 2086571, which both have 37.5. The
reason why I was interested in how it came up in the grid is that all
ways of accessing data using the XlsReader loads the entire file into
memory, it's just a nasty fact with Excel spreadsheets. ReadRecord is
basically just a very simple current record variable incrementer, so
it would be very odd for it to suddenly jump to 36. If the data loads
correctly into the grid, to me that means that the reader does know
the correct number of records, which would have been my other concern
if it internally only loaded a couple of the records. I have a
suspicion that you're looking at what data ends up in the database
instead of what data is actually being read. Try running the code
below and see if you see what I see with no obvious issue. Then, try
debugging your code stepping it line by line and putting watches on
the reader values to see if it actually is jumping to record 36. If
you do still see the issue, let me know what assembly version you're
using and maybe I can reproduce it on a particular version, although I
tried several. I'm also using the 2.0 built assembly in 2.0.

using System;
using System.Collections.Generic;
using System.Text;

using DataStreams.Xls;

namespace XlsSkippingRecordsTest
{
class Program
{
static void Main(string[] args)
{
using (XlsReader xlsData = new XlsReader("../../
WPSPreCheck080715.XLS"))

{
string[] cHeader;
int HeadCount = 0;
int Flag = 0;
int errFlag = 0;

DateTime myDate;
xlsData.Settings.HasHeaders = true;

cHeader = xlsData.Headers;
for (int i = 0; i < cHeader.Length; i++)
{
if (cHeader[i] != "")
HeadCount += 1;
}

Console.WriteLine("header count: " + HeadCount);

if (HeadCount == 18)
{
Flag = 0;
for (int i = 0; i < cHeader.Length; i++)
{
if (cHeader[i].ToUpper() == "ORG.NUM.")
Flag += 1;
if (cHeader[i].ToUpper() == "EMPLOYEE NAME")
Flag += 1;
if (cHeader[i].ToUpper() == "E")
Flag += 1;
if (cHeader[i].ToUpper() == "PER.NUM.")
Flag += 1;
if (cHeader[i].ToUpper() == "REJECTED HS.")
Flag += 1;
if (cHeader[i].ToUpper() == "CATS ABSEN.")
Flag += 1;
if (cHeader[i].ToUpper() == "PERS.FROM")
Flag += 1;

} // end for

if (Flag != 7)
errFlag = 1;
else
errFlag = 0;

if (Flag == 7 && errFlag == 0)
{
//Process File for import
while (xlsData.ReadRecord())
{
myDate =
Convert.ToDateTime(xlsData["Pers.From"]);

Console.WriteLine("mydate: " +
myDate.ToString());

Console.WriteLine("per num: " +
Convert.ToInt32(xlsData["Per.Num."]).ToString());

Console.WriteLine("org num: " +
Convert.ToInt32(xlsData["Org.Num."]).ToString()); //(Record 1 here)
Console.WriteLine("employee name: " +
xlsData["Employee Name"]); //(Record 1 here)
Console.WriteLine("per num: " +
Convert.ToInt32(xlsData["Per.Num."]).ToString()); // (Record 1 here)
Console.WriteLine("released hs: " +
xlsData["Released Hs."]);
if (xlsData["Released Hs."] != "") //(Now
at record 36)
{
Console.WriteLine("true");
}
else
{
Console.WriteLine("false");
}
} // end while (xlsData.ReadRecord())
} // end if
} // end if (HeadCount == 18)
} // end using (xlsData)
} // end Main
} // end class
} // end namespace

Bruce Dunwiddie

> > - Show quoted text -- Hide quoted text -