Hi,
is there a proper way to insert a Datatable into a worksheet like a transposed version of .Cells.ImportDataTable()?
I need the datatables columns in the worksheets rows and each row of the datatable in a column of the worksheet.
Any suggestions?
<span style=“color: rgb(0, 0, 0); font-family: “Times New Roman”; font-size: medium; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; display: inline !important; float: none;”>Hi,<div style=“color: rgb(0, 0, 0); font-family: “Times New Roman”; font-size: medium; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;”>
Thanks for your posting and using Aspose.Cells.
You will have to first import your data into worksheet, then you can transpose your entire worksheet.
<div style=“color: rgb(0, 0, 0); font-family: “Times New Roman”; font-size: medium; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;”>Please see the following sample code. I have also attached the source excel file used in this code and the output excel file generated by it for your reference.
Please check the second worksheet in the output excel file which contains transposed data of first worksheet.
<div style=“color: rgb(0, 0, 0); font-family: “Times New Roman”; font-size: medium; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;”>
<div style=“color: rgb(0, 0, 0); font-family: “Times New Roman”; font-size: medium; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;”>C#
Workbook wb = new Workbook(“sample.xlsx”);
Worksheet ws1 = wb.Worksheets[0];
Worksheet ws2 = wb.Worksheets[1];
Range rng1 = ws1.Cells.MaxDisplayRange;
Range rng2 = ws2.Cells.CreateRange(0,0, rng1.ColumnCount, rng1.RowCount);
PasteOptions opts = new PasteOptions();
opts.Transpose = true;
rng2.Copy(rng1, opts);
ws2.Cells.DeleteBlankColumns();
ws2.Cells.DeleteBlankRows();
wb.Save(“output.xlsx”);
Worksheet ws1 = wb.Worksheets[0];
Worksheet ws2 = wb.Worksheets[1];
Range rng1 = ws1.Cells.MaxDisplayRange;
Range rng2 = ws2.Cells.CreateRange(0,0, rng1.ColumnCount, rng1.RowCount);
PasteOptions opts = new PasteOptions();
opts.Transpose = true;
rng2.Copy(rng1, opts);
ws2.Cells.DeleteBlankColumns();
ws2.Cells.DeleteBlankRows();
wb.Save(“output.xlsx”);
Hi,
thanks for the quick response.
my DataTable contains data from a database. Inserting it to a temporare worksheet and then transpose copy it do the destination worksheet will work but is kind of an workaround.
If there is no better solution this will do it.
Hi,
Yes, you got to do it by yourself following the workaround (as suggested by Shakeel Faiz). I am afraid, Cells.ImportDataTable() has no such overload available to cope with your custom needs directly.
Thank you.