Anyone know how to programmatically change the datatype on a column in an Excel worksheet?

Alaric_

Using COM interop with Excel|Ace 12 driver Short background: Vendor delivers excel spreadsheet to be imported into our system. We had an agreed upon interface, and vendor did not meet the interface. I have been called in to shoehorn their data into the process. Essentially, they put a piece of data in the incorrect column and it needs to be extracted from the existing column into a column of its own. They messed up on the datatype of another column, and TypeGuessRows is causing the Excel engine to infer that the column is numeric when in fact it is text. If I can COM interop with the spreadsheet, extract the requisite data out of the current column, place it in its proper location within the sheet, then programmatically alter the datatype of the offending column from "General" directly to "Text", our existing process will be able to pick up fine. If not, the changes will be more invasive. I cannot find any information at all on how to programmatically extract a column nor can I find any information on the interface that would allow me to change the datatype on an existing worksheet. Does anyone know where I might find this information? Can anyone provide any examples of how either of these 2 tasks might be accomplished? Would COM interop even be the way to go with this? Can you think of a better way?

"I need build Skynet. Plz send code"

Wayne Gaylard

Couldn't you write a function that uses OleDb to tranfer the column to the correct place and convert the data at the same time. Using OleDb you could treat the data as strings right from the start. This would be much easier than trying to do it with Interop. Then you could use your existing methods as they were meant to.

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Alaric_

How would *you* go about using OleDb to perform this task? The only thing I can think of would be to use an OleDbDataReader to walk through the sheet and build another that matches the correct format in memory and when finished, save out to the original location.

"I need build Skynet. Plz send code"

Wayne Gaylard

I assumed you knew the name of the sheet and the index of the column the data is currently in and the index of the column you wish the data was in. Then it is merely a case of using a datareader to swop the columns with a data type change in the process.

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Alaric_

oh, I must have described the problem wrong then... This is contrived, but I think it traps everything: Sheet looks like this: ColumnA | ColumnB | ColumnC | ColumnD 1)ColumnA contains data that needs to have a substring extracted and inserted as a new column between the existing ColumnA and ColumnB. 2)ColumnC is the wrong datatype. maybe I'm just overthinking the problem.

"I need build Skynet. Plz send code"

Alaric_

never mind...I just needed to actually start writing the code to get the juices flowing

using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string foo = reader[0].ToString();
string newcolumn = foo.Substring(foo.IndexOf(":") + 1);

    object\[\] meta = new object\[reader.FieldCount\];
    reader.GetValues(meta);
    List<string> list = meta.ToList().ConvertAll(o => o.ToString());
}

}

"I need build Skynet. Plz send code"

Wayne Gaylard

Glad to help, even if it was just to get you thinking on a different track :laugh:

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Alaric_

Ok...another mental block: I can read the rows in the sheet, I can access the cells from the row via an OleDbDataReader ...but how do I hook directly into a row in the Excel spreadsheet to update it once I have my update record crafted?

using (OleDbCommand command = connection.CreateCommand())
{
int idx = 0;
command.CommandText = string.Format("select * from [{0}$]", "Report_12251104");
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string foo = reader[0].ToString();
string newcolumn = foo.Substring(foo.IndexOf(":") + 1);

      object\[\] meta = new object\[reader.FieldCount\];
      reader.GetValues(meta);
      List<object> list = meta.ToList();
      list.Insert(2,newcolumn);
   }
   OleDbCommand update = connection.CreateCommand();
   //I want 
   update.CommandText = "update ??what exactly?? Can I use idx to id a row by number??";
   update.ExecuteNonQuery();
   idx++;
}

}

"I need build Skynet. Plz send code"