-
May 5th, 2014, 08:46 AM
#1
How to correctly transponate Excel Rows in Columns without loosing Formulas
Hello!
I am currently trying to implement the exact same Excel functionality: "special paste->transponate" where a certain area (like 256 rows) are correctly transfered in columns in another sheet.
Sadly I just cant manage to transponate the formula correctly, so that it is transponated as well.
I am developing under .NET Framework 4 and using Microsoft.Office.Interop.Excel. I thought this should be easy, but I just cant make it work oO.
Since I am using Excel 2003 I am limited to 256 cols maximum, wich is the reason for the following code.
I tried out many different stuff by now, but cant make it work. This is the current code, please let me know what I am doing wrong here:
Code:
//--------------------------------------------------------------------------
// convert col to row
//--------------------------------------------------------------------------
public void convertRow2Col(string workingTab)
{
xlInputTabName = workingTab;
int TabNameCounter = 1;
range_Input = xlWorkSheet_Input.UsedRange;
range_Output = xlWorkSheet_Output.UsedRange; // default value
Range ran = xlWorkSheet_Output.get_Range("A:A", System.Type.Missing);
ran.EntireColumn.ColumnWidth = 110;
xlWorkBook_Output.Worksheets[1].Name = xlInputTabName;
int prev_row_input = 1;
//----------------------------------------------------------------------------------------
//read and write numbers
//----------------------------------------------------------------------------------------
for (int row_input = 256; row_input <= range_Input.Rows.Count; row_input+=256)
{
try
{
string rangeStr = prev_row_input.ToString() + ":" + row_input.ToString();
Range input_rng = xlWorkSheet_Input.get_Range(rangeStr, Type.Missing);
Object[,] transposedRange = (Object[,])xlApp_Input.WorksheetFunction.Transpose(input_rng.Formula);
xlApp_Output.ActiveSheet.Range("A1").Resize(transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1));
// next sheet
xlSheetCounter++;
TabNameCounter++;
prev_row_input = row_input + 1;
this.xlApp_Output.Worksheets.Add();
xlWorkSheet_Output = (Excel.Worksheet)xlWorkBook_Output.Sheets[1];
xlWorkBook_Output.Worksheets[1].Name = xlInputTabName + TabNameCounter.ToString();
}
catch (COMException ex)
{
if (ex.Source != null)
Console.WriteLine("IOException source: {0}", ex.Source);
throw;
}
}
xlWorkBook_Output.Save();
}
Thanks for all replies.
-
May 5th, 2014, 08:54 AM
#2
Re: How to correctly transponate Excel Rows in Columns without loosing Formulas
This is the most important part:
string rangeStr = prev_row_input.ToString() + ":" + row_input.ToString();
Range input_rng = xlWorkSheet_Input.get_Range(rangeStr, Type.Missing);
Object[,] transposedRange = (Object[,])xlApp_Input.WorksheetFunction.Transpose(input_rng.Formula);
xlApp_Output.ActiveSheet.Range("A1").Resize(transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1));
-
May 7th, 2014, 09:29 AM
#3
Re: How to correctly transponate Excel Rows in Columns without loosing Formulas
I was able to find the correct function, I was just dumb -,-
-> range.Copy()
-> range.SpecialPaste(params)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|