This project has moved and is read-only. For the latest updates, please go here.

wrkbook.Worksheet("wksheet").Cell(2, 1).InsertData(dt.AsEnumerable)

Nov 4, 2011 at 9:04 PM

I am executing the following code in preparation to exporting to an excel spreadsheet over http stream

Protected Sub OutputExcel(ByVal dt As Data.DataTable, ByVal wrkbook As ClosedXML.Excel.XLWorkbook)

 Dim httpresp As HttpResponse = Response

Dim outfile As String

wrkbook.Worksheet("wrksheet").Cell(2, 1).InsertData(dt.AsEnumerable)

...

and I get the error "

The given key was not present in the dictionary.

on the InsertData. The query string i am using follows and returns data in management studio as well as a valid dataset that is bound to a listview which shows the data. When I export the data I use the same command to build the datatable (dt above). If you look at dt in the debuger it shows the same number of rows returned as if you ran the cmd as a query and was working until I added

AND [Product Code and Product Description] = 'LC11   Commercial Loan'  

where the value compared in the where comes from a drop down list.  What is weird is if I change it to

AND [Product Code and Product Description] <>  ''

which I hard coded as a test, works and returns all the products. Any ideas and assistance would be appreciated. BTW, .59 fixed a problem I was having exporting more than around 45K rows to excell, yeah!

select  [Account Holder Number], [Account Number],
CASE WHEN [Last_Name] is Null THEN '' ELSE [LAST_NAME] +  CASE WHEN [Suffix] is Null THEN ''  ELSE ' ' + [Suffix] End End + CASE WHEN [FIRST_NAME] is NULL THEN '' ELSE   CASE WHEN [Last_Name] is Null THEN '' ELSE ', ' END End + CASE WHEN [First_Name] is Null THEN '' ELSE [First_Name] END + CASE WHEN [MIDDLE_NAME]  is NULL THEN '' else ' ' END + CASE WHEN [MIDDLE_NAME] is NULL THEN '' else [MIDDLE_NAME] END as Name,
[Product Code and Product Description], 
sum(Balance) as 'Balance', sum(Profit) as 'Profit',
[Open Date],
DATEDIFF(month,[Open Date],getdate()) as 'Months Old',
[Account Holder Grade],
[Service Propensity],
[Account Type Propensity],
CASE WHEN ISDATE([DOB]) = 1 then DATEDIFF(yy,[DOB],[Process Date]) else Null END as Age, Min([Branch Description]) as Branch,
Min([Officer Name]) as Officer,
[First_Name] as 'First Name',
[Middle_Name] as 'Middle Name',
[Last_Name] as 'Last Name',
[Suffix] as Suffix, Address_1 as 'Address 1',
Address_2 as 'Address 2',
City as City, State as State,
Zip_Code as 'Zip Code',
Phone as Phone,
[E-mail]
from  dbo.webArchive Left Join Customer on CIF = [ACCOUNT HOLDER NUMBER] 
WHERE LTRIM(RTRIM([Account Holder Number])) <> '0'
AND LTRIM(RTRIM([Account Holder Number])) <> 'Manually Added Accounts' 
AND [Open Date] >= '1/1/2000' 
AND [Open Date] <= '1/1/2011' 
AND [Product Code and Product Description] = 'LC11   Commercial Loan' 
group by [Account Holder Number],
[Account Number],
[Product Code and Product Description],
[Balance],
[Profit],
[Open Date],
[Account Holder Grade],
[Service Propensity],
[Account Type Propensity],
[First_Name],
[Middle_Name],
[Last_Name],
[Suffix],
Address_1,
Address_2,
City,
State,
Zip_Code,
Phone,
[E-mail],
[DOB],
[Process Date]
order by [Open Date] Desc

Thanks!!!!

 

  

Nov 4, 2011 at 10:34 PM

Are you sure there's a worksheet called "wrksheet" in the workbook you're passing?

Nov 5, 2011 at 4:44 AM

Positive, otherwise it would not work when I change the comparison to <>'', but it does. It actually builds the spreadsheet just fine if I either delete it from the where or change it to what I mentioned above. In either case, even when i get the error, the sql string returns data as previously mentioned. Its kinda weird, maybe a bug. I've been out this afternoon but going to play with it some more to see if I can narrow it down. But it is 100% reproducible.

Nov 5, 2011 at 4:18 PM

Well, I just fixed it and MDeLeon was right. When I added the worksheet I had misspelled it. Whats weird is that it would work if I removed that particular part of the where clause. Weird, but thanks MDeLeon! I love OpenXML, so much simpler than OpenXML to use.