M
mdengler
When trying to set the range value of the spreadsheet I get the following
error: Exception from HRESULT: 0x800A03EC.
BuildXls method follows:
****************** start of BuildXls method *********************
public string BuildXls(string FileName, ref string[,] AryFieldsAll,
System.Data.DataTable MyDataTable)
{
System.Globalization.CultureInfo enUS = new
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 single 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;
}
****************** end of BuildXls method *********************
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.
error: Exception from HRESULT: 0x800A03EC.
BuildXls method follows:
****************** start of BuildXls method *********************
public string BuildXls(string FileName, ref string[,] AryFieldsAll,
System.Data.DataTable MyDataTable)
{
System.Globalization.CultureInfo enUS = new
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 single 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;
}
****************** end of BuildXls method *********************
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.