Excel Transpose

need code for tranpose

Hi,

A little more description of what you need would have clarified your requirement, anyway I think below given code is what you need…

static void Job18()
{
CCADOConnection cn;
CCADOCommand adoCommand;
CCADORecordset rs;
FileName fileName;
CCADOFields adoFields;
str strSQL;
str strTransType;
int col, counter=0,cols=1, i=0,j=0;
str indexCnt, TransData = ‘’;
Map cell = new Map(Types::String, Types::String);
MapEnumerator cellEnum;
;
fileName = ‘c:\test2.xls’;
cn = new CCADOConnection();
cn.open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + fileName + “;Extended Properties=‘Excel 8.0;HDR=No;IMEX=1’”);
rs = new CCADORecordset();
adoCommand = new CCADOCommand();
adoCommand.activeConnection(cn);
adoCommand.commandText(@“SELECT * FROM [SHEET1$]”);
rs = adoCommand.execute();

while(!rs.EOF())
{
adoFields = rs.fields();
for (col = 0; col < adoFields.count(); col ++)
{
indexCnt = strfmt("%1,%2",int2str(counter),int2str(col));
cell.insert(indexCnt, adoFields.itemIdx(col).value());
}
counter++;
cols = (adoFields.count()> cols) ? adoFields.count() : cols;
rs.recordSet().moveNext();
}
for(i=0; i<= col-1; i++)
{
for(j=0; j<= counter-1; j++)
{
indexCnt = strfmt("%1,%2",j,i);
TransData += ’ ’ +(any2str(cell.lookup(indexCnt)));
}
info(TransData);
TransData = ‘’;
}
}

Hi Abhinay ,

Thnx for quick rply but m getting following error "Method ‘open’ in COM object of class ‘ADODB.Connection’ returned error code 0x800A0E7A () which means: Provider cannot be found. It may not be properly installed.

i want to transpose a matrix taken from excel

"

Check the file name you are providing, and check the version of MS Office installed. The code I had posted is for a sheet with “.xls” file extension. You may have to make some changes to the code if the file type is different.

You may also have to download and install 2007 Office System Driver: Data Connectivity Components and make changes in the code as cn.open(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + fileName + “;Extended Properties=‘Excel 12.0 Xml;HDR=No;IMEX=1’”);

Now it is solved.

Regards,

Aditya Sharma.

Hi, Aditya,

If i need to export transpose matrix to excel sheet.

As you did it in a string and used infolog to check data, similarly i want to get transposed matrix on excel sheet.

any help?

Regards,

Geetika