M
mdengler
When trying to set the range value of the spreadsheet I get th
following error: Exception from HRESULT: 0x800A03EC.
BuildXls method follows:
public string BuildXls(string FileName, ref string[,] AryFieldsAll
System.Data.DataTable MyDataTable)
{
System.Globalization.CultureInfo enUS = ne
System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = enUS;
string return_message;
int num_rows=MyDataTable.Rows.Count;
int num_cols=MyDataTable.Columns.Count;
int max_rows_per_sheet=65000;
if (num_rows<=max_rows_per_sheet)
{
max_rows_per_sheet=num_rows;
}
int row_index;
int col_index;
int sheet_index=1;
int sheet_row_counter=0;
object[,] objData = new object[num_rows,num_cols];
object[,] objData2 = new object[max_rows_per_sheet,num_cols];
object[] objHeaders = new object[num_cols];
// Excel object references.
Excel.Application objExcel = null;
Excel.Workbooks objBooks = null;
Excel._Workbook objBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet objSheet = null;
Excel.Range objRange = null;
Excel.Font objFont = null;
// Start a new workbook in Excel.
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(sheet_index));
//build a header array
col_index=0;
foreach(DataColumn col in MyDataTable.Columns)
{
objHeaders[col_index] = col.ColumnName;
col_index++;
}
//build an array of data rows
row_index=0;
foreach(DataRow row in MyDataTable.Rows)
{
col_index=0;
foreach(object item in row.ItemArray)
{
//create an array of row values. prefix all values with a singl
quote.
objData[row_index,col_index] = "'"+item.ToString();
col_index++;
}
row_index++;
}
//build the spreadsheet
try
{
for(row_index=0; row_index<num_rows; row_index++)
{
if (sheet_row_counter==0)
{
objData2 = new object[max_rows_per_sheet,num_cols];
}
//create an array of row values
for (col_index=0; col_index<num_cols; col_index++)
{
objData2[sheet_row_counter,col_index]
objData[row_index,col_index];
}
sheet_row_counter++;
if (sheet_row_counter==max_rows_per_sheet |
row_index==num_rows-1)
{
if (sheet_index>3)
{
objSheet
(Excel._Worksheet)objBook.Worksheets.Add(Missing.Value,objSheet,Missing.Value,Missing.Value)
objSheet.Name="Sheet"+(sheet_index);
}
else
{
objSheet
((Excel._Worksheet)objExcel.Worksheets["Sheet"+sheet_index]);
}
//add the header to the worksheet
objRange
objSheet.get_Range((Excel.Range)objSheet.Cells[1,1],(Excel.Range)objSheet.Cells[1,num_cols]);
objRange.set_Value(Missing.Value, objHeaders);
objFont = objRange.Font;
objFont.Bold=true;
//add the data rows to the worksheet
objRange
objSheet.get_Range((Excel.Range)objSheet.Cells[2,1],(Excel.Range)objSheet.Cells[max_rows_per_sheet+1,num_cols]);
objRange.Value2 = objData2; //this is where the error occurs
//objRange.set_Value(Missing.Value, objData2);
//objRange.set_Value(OWC10.XlRangeValueType.xlRangeValueDefault,objData2);
sheet_row_counter=0;
sheet_index++;
}
}
MyDataTable.Dispose();
GC.Collect();
//format the spreadsheet
for(int i=1;i<=objBook.Worksheets.Count;i++)
{
objSheet=(Excel._Worksheet)objBook.Worksheets["Sheet"+i];
int j=0;
foreach(DataColumn col in MyDataTable.Columns)
{
j++;
objRange=(Excel.Range)objSheet.Cells[1,j];
objRange=objRange.EntireColumn;
//objRange.AutoFit();
//objRange.set_HorizontalAlignment(Excel.XlHAlign.xlHAlignRight)
// set the font
objFont = objRange.Font;
objFont.Name = "Arial";
objFont.Size = 8;
// format the columns
for (int f=0; f<AryFieldsAll.GetLength(0); f++)
{
// does the selected column name equal the AryFieldsAll field
name value?
if (col.ColumnName.ToString() == AryFieldsAll[f,3])
{
switch (AryFieldsAll[f,17]) //format info
{
case "{0:N0}": // number
objRange.NumberFormat = "###,##0;[Red]###,##0";
break;
case "{0:c}": // currency
objRange.NumberFormat = "$###,##0.00;[Red]$###,##0.00";
break;
case "{0
}": // percentage
objRange.NumberFormat = "###,##0.00%;[Red]###,##0.00%";
break;
case "{mm/dd/yyyy}": // date
objRange.NumberFormat = "mm/dd/yyyy";
break;
} //switch (AryFieldsAll[f,17]) //format info
break;
} //if (MyDataColumn.ColumnName.ToString() ==
AryFieldsAll[f,0])
} //for (int f=0; f<AryFieldsAll.GetLength(0); f++)
} //foreach(DataColumn col in MyDataTable.Columns)
} //for(int i=1;i<=objBook.Worksheets.Count;i++)
((Excel._Worksheet)objExcel.Worksheets["Sheet1"]).Activate();
// Save the Workbook
objBook.SaveAs(FileName, objOpt, objOpt, objOpt, objOpt, objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt,
objOpt);
return_message = "success";
}
catch (Exception e)
{
return_message = e.Message.ToString();
}
finally
{/*
// Need all following code to clean up and extingush all
references!!!
objBook.Close(null,null,null);
objExcel.Workbooks.Close();
objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (objBook);
objSheet=null;
objBook=null;
objExcel = null;
GC.Collect(); // force final cleanup!
*/
}
return return_message;
}
when the Datatable contains this data, it works:
Placement #365 - 02/14/02 - Keyword Bundle: hotel reservation,
hotel reservations, car rental, car rentals Placement #366 -
02/14/02 - Keyword Bundle: cruise line, cruise lines, cruise ship,
cruise ships, luxury cruise, luxury cruises, cruises Placement
#367 - 02/14/02 - Keyword Bundle: broadband, portal
Placement #368 - 02/14/02 - keyword Bundle: personal ads, online
personals Placement #369 - 02/14/02 - Keyword Bundle: bed
and breakfast; bed and breakfasts; bed and breakfast inn; bed and
breakfast inns; bed breakfast; bed breakfasts; bed breakfast inn; bed
breakfast inns; bed and breakfast guide; bed and breakfast directory;
bed breakfast directory; country inn; country inns; inns
when the DataTable contains this data, it fails:
This Order replaces BS03210110 Placement 514 Exclusive
Keywords;camcorder accessories; camcorder bags; camcorder lights;
camcorder microphones; camcorder stand; camcorder stands; camcorder
tripods; camera bags; conversion lens; sony accessories; sony
accessorry; tripod stands; video accessories; video cables; video
editing; video lights; camcorder lens Placement 524
audiovox accessories; audiovox chargers; audiovox free; audiovox
headsets; cell accessories; cellular accessories; cellular accessory;
digital accessories; ericsson accessories; ericsson chargers; ericsson
free; ericsson headsets; headset; headsets; motorola accessories;
motorola chargers; motorola free; motorola headsets; nextel
accessories; nextel chargers; nextel free; nextel headsets; nokia
accessories; nokia chargers; nokia free; nokia headsets
Placement 523 9 volt; aa batteries; aa battery; aaa batteries;
battery; batteries; alkaline batteries; alkaline battery; battery
charger; battery chargers; ; camcorder batteries; camcorder battery;
cell phone batteries; cell phone battery; cellphone batteries;
cellphone battery; cellular batteries; cellular battery; cellular
batteries; cellular battery; computer batteries; computer battery;
cordless phone batteries; cordless battery; digital camera batteries;
digit Placement 513 Exclusive Keywords;audiovox
accessories; audiovox chargers; audiovox free; audiovox headsets; cell
accessories; cellular accessories; cellular accessory; digital
accessories; ericsson accessories; ericsson chargers; ericsson free;
ericsson headsets; headset; headsets; motorola accessories; motorola
chargers; motorola free; motorola headsets; nextel accessories; nextel
chargers; nextel free; nextel headsets; nokia accessories; nokia
chargers; nokia fre Placement 525 camcorder accessories;
camcorder bags; camcorder lights; camcorder microphones; camcorder
stand; camcorder stands; camcorder tripods; camera bags; conversion
lens; sony accessories; sony accessorry; tripod stands; video
accessories; video cables; video editing; video lights; camcorder lens
Placement 515 keyword; Battery Placement 516
All Placements in Shopping Channel Placement 512 9 volt; aa
batteries; aa battery; aaa batteries; batteries; alkaline batteries;
alkaline battery; battery charger; battery chargers; ; camcorder
batteries; camcorder battery; cell phone batteries; cell phone battery;
cellphone batteries; cellphone battery; cellular batteries; cellular
battery; cellular batteries; cellular battery; computer batteries;
computer battery; cordless phone batteries; cordless battery; digital
camera batteries; digital camera
Any ideas or thoughts would be appreciated.
following error: Exception from HRESULT: 0x800A03EC.
BuildXls method follows:
public string BuildXls(string FileName, ref string[,] AryFieldsAll
System.Data.DataTable MyDataTable)
{
System.Globalization.CultureInfo enUS = ne
System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = enUS;
string return_message;
int num_rows=MyDataTable.Rows.Count;
int num_cols=MyDataTable.Columns.Count;
int max_rows_per_sheet=65000;
if (num_rows<=max_rows_per_sheet)
{
max_rows_per_sheet=num_rows;
}
int row_index;
int col_index;
int sheet_index=1;
int sheet_row_counter=0;
object[,] objData = new object[num_rows,num_cols];
object[,] objData2 = new object[max_rows_per_sheet,num_cols];
object[] objHeaders = new object[num_cols];
// Excel object references.
Excel.Application objExcel = null;
Excel.Workbooks objBooks = null;
Excel._Workbook objBook = null;
Excel.Sheets objSheets = null;
Excel._Worksheet objSheet = null;
Excel.Range objRange = null;
Excel.Font objFont = null;
// Start a new workbook in Excel.
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(sheet_index));
//build a header array
col_index=0;
foreach(DataColumn col in MyDataTable.Columns)
{
objHeaders[col_index] = col.ColumnName;
col_index++;
}
//build an array of data rows
row_index=0;
foreach(DataRow row in MyDataTable.Rows)
{
col_index=0;
foreach(object item in row.ItemArray)
{
//create an array of row values. prefix all values with a singl
quote.
objData[row_index,col_index] = "'"+item.ToString();
col_index++;
}
row_index++;
}
//build the spreadsheet
try
{
for(row_index=0; row_index<num_rows; row_index++)
{
if (sheet_row_counter==0)
{
objData2 = new object[max_rows_per_sheet,num_cols];
}
//create an array of row values
for (col_index=0; col_index<num_cols; col_index++)
{
objData2[sheet_row_counter,col_index]
objData[row_index,col_index];
}
sheet_row_counter++;
if (sheet_row_counter==max_rows_per_sheet |
row_index==num_rows-1)
{
if (sheet_index>3)
{
objSheet
(Excel._Worksheet)objBook.Worksheets.Add(Missing.Value,objSheet,Missing.Value,Missing.Value)
objSheet.Name="Sheet"+(sheet_index);
}
else
{
objSheet
((Excel._Worksheet)objExcel.Worksheets["Sheet"+sheet_index]);
}
//add the header to the worksheet
objRange
objSheet.get_Range((Excel.Range)objSheet.Cells[1,1],(Excel.Range)objSheet.Cells[1,num_cols]);
objRange.set_Value(Missing.Value, objHeaders);
objFont = objRange.Font;
objFont.Bold=true;
//add the data rows to the worksheet
objRange
objSheet.get_Range((Excel.Range)objSheet.Cells[2,1],(Excel.Range)objSheet.Cells[max_rows_per_sheet+1,num_cols]);
objRange.Value2 = objData2; //this is where the error occurs
//objRange.set_Value(Missing.Value, objData2);
//objRange.set_Value(OWC10.XlRangeValueType.xlRangeValueDefault,objData2);
sheet_row_counter=0;
sheet_index++;
}
}
MyDataTable.Dispose();
GC.Collect();
//format the spreadsheet
for(int i=1;i<=objBook.Worksheets.Count;i++)
{
objSheet=(Excel._Worksheet)objBook.Worksheets["Sheet"+i];
int j=0;
foreach(DataColumn col in MyDataTable.Columns)
{
j++;
objRange=(Excel.Range)objSheet.Cells[1,j];
objRange=objRange.EntireColumn;
//objRange.AutoFit();
//objRange.set_HorizontalAlignment(Excel.XlHAlign.xlHAlignRight)
// set the font
objFont = objRange.Font;
objFont.Name = "Arial";
objFont.Size = 8;
// format the columns
for (int f=0; f<AryFieldsAll.GetLength(0); f++)
{
// does the selected column name equal the AryFieldsAll field
name value?
if (col.ColumnName.ToString() == AryFieldsAll[f,3])
{
switch (AryFieldsAll[f,17]) //format info
{
case "{0:N0}": // number
objRange.NumberFormat = "###,##0;[Red]###,##0";
break;
case "{0:c}": // currency
objRange.NumberFormat = "$###,##0.00;[Red]$###,##0.00";
break;
case "{0
}": // percentageobjRange.NumberFormat = "###,##0.00%;[Red]###,##0.00%";
break;
case "{mm/dd/yyyy}": // date
objRange.NumberFormat = "mm/dd/yyyy";
break;
} //switch (AryFieldsAll[f,17]) //format info
break;
} //if (MyDataColumn.ColumnName.ToString() ==
AryFieldsAll[f,0])
} //for (int f=0; f<AryFieldsAll.GetLength(0); f++)
} //foreach(DataColumn col in MyDataTable.Columns)
} //for(int i=1;i<=objBook.Worksheets.Count;i++)
((Excel._Worksheet)objExcel.Worksheets["Sheet1"]).Activate();
// Save the Workbook
objBook.SaveAs(FileName, objOpt, objOpt, objOpt, objOpt, objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt,
objOpt);
return_message = "success";
}
catch (Exception e)
{
return_message = e.Message.ToString();
}
finally
{/*
// Need all following code to clean up and extingush all
references!!!
objBook.Close(null,null,null);
objExcel.Workbooks.Close();
objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (objBook);
objSheet=null;
objBook=null;
objExcel = null;
GC.Collect(); // force final cleanup!
*/
}
return return_message;
}
when the Datatable contains this data, it works:
Placement #365 - 02/14/02 - Keyword Bundle: hotel reservation,
hotel reservations, car rental, car rentals Placement #366 -
02/14/02 - Keyword Bundle: cruise line, cruise lines, cruise ship,
cruise ships, luxury cruise, luxury cruises, cruises Placement
#367 - 02/14/02 - Keyword Bundle: broadband, portal
Placement #368 - 02/14/02 - keyword Bundle: personal ads, online
personals Placement #369 - 02/14/02 - Keyword Bundle: bed
and breakfast; bed and breakfasts; bed and breakfast inn; bed and
breakfast inns; bed breakfast; bed breakfasts; bed breakfast inn; bed
breakfast inns; bed and breakfast guide; bed and breakfast directory;
bed breakfast directory; country inn; country inns; inns
when the DataTable contains this data, it fails:
This Order replaces BS03210110 Placement 514 Exclusive
Keywords;camcorder accessories; camcorder bags; camcorder lights;
camcorder microphones; camcorder stand; camcorder stands; camcorder
tripods; camera bags; conversion lens; sony accessories; sony
accessorry; tripod stands; video accessories; video cables; video
editing; video lights; camcorder lens Placement 524
audiovox accessories; audiovox chargers; audiovox free; audiovox
headsets; cell accessories; cellular accessories; cellular accessory;
digital accessories; ericsson accessories; ericsson chargers; ericsson
free; ericsson headsets; headset; headsets; motorola accessories;
motorola chargers; motorola free; motorola headsets; nextel
accessories; nextel chargers; nextel free; nextel headsets; nokia
accessories; nokia chargers; nokia free; nokia headsets
Placement 523 9 volt; aa batteries; aa battery; aaa batteries;
battery; batteries; alkaline batteries; alkaline battery; battery
charger; battery chargers; ; camcorder batteries; camcorder battery;
cell phone batteries; cell phone battery; cellphone batteries;
cellphone battery; cellular batteries; cellular battery; cellular
batteries; cellular battery; computer batteries; computer battery;
cordless phone batteries; cordless battery; digital camera batteries;
digit Placement 513 Exclusive Keywords;audiovox
accessories; audiovox chargers; audiovox free; audiovox headsets; cell
accessories; cellular accessories; cellular accessory; digital
accessories; ericsson accessories; ericsson chargers; ericsson free;
ericsson headsets; headset; headsets; motorola accessories; motorola
chargers; motorola free; motorola headsets; nextel accessories; nextel
chargers; nextel free; nextel headsets; nokia accessories; nokia
chargers; nokia fre Placement 525 camcorder accessories;
camcorder bags; camcorder lights; camcorder microphones; camcorder
stand; camcorder stands; camcorder tripods; camera bags; conversion
lens; sony accessories; sony accessorry; tripod stands; video
accessories; video cables; video editing; video lights; camcorder lens
Placement 515 keyword; Battery Placement 516
All Placements in Shopping Channel Placement 512 9 volt; aa
batteries; aa battery; aaa batteries; batteries; alkaline batteries;
alkaline battery; battery charger; battery chargers; ; camcorder
batteries; camcorder battery; cell phone batteries; cell phone battery;
cellphone batteries; cellphone battery; cellular batteries; cellular
battery; cellular batteries; cellular battery; computer batteries;
computer battery; cordless phone batteries; cordless battery; digital
camera batteries; digital camera
Any ideas or thoughts would be appreciated.