loading comma delimited text into Excel spreadsheet

[hne]

Hello,
My application pulls data from an SQL dbase and I create a comma delimited StringBuilder output. I want to place this result into three separate columns (per my delimited field). I just cannot get the 'TextToColumns' method to work. Below is a snippet of my code. Your help is highly appreciated. Thx.

Below is an example of the data that I pulled. My code follows after...

1,2006030300,21676
1,2006030301,18945
1,2006030302,17504
1,2006030303,16128
2,2006030304,12486
2,2006030305,8425
2,2006030306,4661
2,2006030307,2616
3,2006030308,1621
3,2006030309,1702
3,2006030310,2635
3,2006030311,4000
3,2006030312,7648
4,2006030313,13947
4,2006030314,13496
4,2006030300,21088

My code...

// Initiallize the excel range variable...
Excel.Range range;
Excel.Range cellFound;

// Get the path of the workbook...
string xlWorkBookPath = "c:\\Compare" + sDate + ".xls";
// Create application object...
Excel.Application XcelApp = new Excel.ApplicationClass();

XcelApp.Visible = true; // dont' hide the app...

// I want to open an existing document for editing instead
// of creating a new one...
Excel.Workbook XcelWorkBook = XcelApp.Workbooks.Open(xlWorkBookPath, 0, false, 5, Type.Missing, Type.Missing, false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

// Get my worksheets in the workbook...
Excel.Sheets XcelWorkSheets = XcelWorkBook.Worksheets;
if (XcelWorkSheets == null)
MessageBox.Show("Error in worksheet == null");
// get worksheet name and load ech data...
if ((day >= 1) && (day <= 10))
{
// first I need to get the individual worksheet...
string currentSheet = "1-10";
Excel.Worksheet XcelWorkSheet = (Excel.Worksheet) XcelWorkSheets.get_Item(currentSheet);
XcelWorkSheet.Activate();
// next I need to find the specific cell that I want to load my data on...
// therefore, I'll start with range search...
range = (Excel.Range)XcelWorkSheet.get_Range("A1", Type.Missing);
// my range is from cell 'A1' to the end where my value ends
range = range.get_End(XlDirection.xlToRight);
// next I neet to search in this range for my column...
cellFound = range.Find(day, Type.Missing,
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);

// now that I have a specific cell location, I need to copy my
// data from my grid to a clipboard for copying...
Clipboard.SetDataObject(sw.ToString(), false);
//Clipboard.SetDataObject(dt.Select(), false);
// first I need to go the specific column...
column = cellFound.Column - 1;
// I need to get my active row...
row = cellFound.Row + 1;
cellFound = (Excel.Range)XcelWorkSheet.Cells[row, column];

// I need to select the cells I want to paste my comma delimited data...
object col = cellFound.Select();
XcelWorkSheet.Select(cellFound);

// Paste the data ...
XcelWorkSheet.Paste(Type.Missing, Type.Missing);

/* THIS IS WHERE I AM HAVING MY PROBLEMS... HELP!!! */
XcelWorkSheet = (Excel.Worksheet)XcelWorkSheet.Cells.TextToColumns((object) cellFound.Column, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, false, false, true, false, false, false, false, false, false, false);


XcelWorkBook.Save();
XcelWorkBook.Close(false, Type.Missing, Type.Missing);
XcelApp.Quit();
}

 

[hne]

Can anyone help me with this problem? Thanks.

 

[misterstick]

you're assuming TextToColumns is a function that returns a WorkSheet object.

i haven't tried this in c#, but in Excel vba itself it's a method of the range object and you just call <range>.TextToColumns(...).

so, try:

cellFound.Column.TextToColumns((object) cellFound.Column, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, false, false, true, false, false, false, false, false, false, false);

hth,


mr s. <