Hi, please help,
I want to make excel from importing datatable to excel but with formating down up,
MY FRIED HOBBY
NO NAME HOBBY NO NAME HOBBY
1 Agnes Shopping 6 Eroze Party
2 Angelica Playing Biola 7 Frank Soccer
3 Bernald Playing Soccer 8 Frans Surfing
4 Cross Zero Travelling 9 Gordon Travelling
5 David Surfing 10 Jack Surfing
I attached the excel file about this,
Is it possible?
Hi Adi,
Thank you for contacting Aspose support.
If you are importing data from DataTable then you should have two DataTables so that you could import each DataTable on different regions of the worksheet. This is because you have to specify only one start location while importing data with Cells.ImportDataTable method. In order to use aforesaid method, you should split your data in multiple DataTables and then import them accordingly.
Please check the following code snippet that does the following.
- Creates a DataTable from scratch
- Adds some rows to the DataTable
- Splits the DataTable into 2 DataTables
- Imports each DataTable on different areas of the worksheet just to mimic your provided sample spreadsheet.
C#
var table = new DataTable();
table.Columns.Add(“NO”, typeof(int));
table.Columns.Add(“NAME”, typeof(string));
table.Columns.Add(“HOBBY”, typeof(string));
table.Rows.Add(1, “Agnes”, “Shopping”);
table.Rows.Add(2, “Angelica”, “Playing Biola”);
table.Rows.Add(3, “Bernald”, “Playing Soccer”);
table.Rows.Add(4, “Cross Zero”, “Travelling”);
table.Rows.Add(5, “David”, “Surfing”);
table.Rows.Add(6, “Eroze”, “Party”);
table.Rows.Add(7, “Frank”, “Soccer”);
table.Rows.Add(8, “Frans”, “Surfing”);
table.Rows.Add(9, “Gordon”, “Travelling”);
table.Rows.Add(10, “Jack”, “Surfing”);
List tables = SplitTable(table, 5);
var book = new Workbook();
var cells = book.Worksheets[0].Cells;
cells.ImportDataTable(tables[0], true, 2, 0, false);
cells.ImportDataTable(tables[1], true, 2, 4, false);
book.Worksheets[0].AutoFitColumns();
book.Save(“D:/output.xlsx”);
Here is the code to split the DataTable. Also attached is the resultant spreadsheet for your reference.
C#
private static List SplitTable(DataTable originalTable, int batchSize)
{
List tables = new List();
int i = 0;
int j = 1;
DataTable newDt = originalTable.Clone();
newDt.TableName = "Table_" + j;
newDt.Clear();
foreach (DataRow row in originalTable.Rows)
{
DataRow newRow = newDt.NewRow();
newRow.ItemArray = row.ItemArray;
newDt.Rows.Add(newRow);
i++;
if (i == batchSize)
{
tables.Add(newDt);
j++;
newDt = originalTable.Clone();
newDt.TableName = "Table_" + j;
newDt.Clear();
i = 0;
}
}
return tables;
}
Please feel free to write back in case you have any concerns or questions.
Hi Babar,
Its worked, Thank you,
But i have some problem with new rule,
And I have format excel in my attachment,
This format is simple and very complicated,
Thank You,
Warm Regards,
Adi
Hi Adi,
Thank you for the confirmation on previously provided solution. Could you please explain your next requirement in more detail so we could assist you further in this regard?
Hi Babar,
My requirement below :
I have an excel template that i have to set first, then i want to import the data from one or many table to this template. Example :
I have three tables (Class A, Class B, Class C) which contains same fields : No, Nip, Name
Rules :
One excel page contains 63 rows and 3 major columns
if the records in the datatable has not finish, it can continue to write the records (if the are different then, it will create the header and subheader again before writing the records; B and class C in my attachment)
how to create output as my given example?
In simple output excel:
page 1 page 2-beside
--------------------------------------------------------------------- -----------------------
Class A Class B Class C empty (not used)
No Nip Nama No Nip Nama No Nip Nama
1. 11 A 3. 23 C 1. 51 A
2. 12 B 4. 24 C 2. 52 A
3. 13 B 5 25 D 3. 53 B
… 6 26 D 4. 54 C
Class B 7 27 E 5. 55 D
No Nip Nama 8 28 F 6. 56 D
1. 21 A 9 29 F 7. 57 E
2. 22 B 10 30 G 8. 58 F
---------------------------------------------------------------------
page 2 -down
---------------------------------------------------------------------
No Nip Nama
9. 59 G
10. 60 H
11. 61 I
---------------------------------------------------------------------
please help, thank you,
Warm Regards,
Adi
Hi Adi,
Thank you for providing further details.
What I have understood from your requirement is that you wish to import the DataTable in down & up manner where each insertion should have a header with merged cells and the number of rows per printed PDF page should be constant.
I believe this task involves more of your own business logic and less Aspose.Cells API integration. In order to achieve this, you should first simplify the task by having all the data into one DataTable rather than distributed over several DataTables. This way you can retrieve only a specified number of rows from the DataTable and import them on the worksheet at specified place as demonstrated earlier in this thread. The flow of your application should be as follow.
- Modify the SplitDataTable method shared in this thread so that it gives you two DataTables where first DataTable should have specified number of rows (row limit) and the second DataTable should have all the remaining rows.
- Devise a recursive call or simple foreach loop that iterates over the list of DataTables obtained by the SplitDataTable method as discussed above.
- Within the recursive method or foreach loop, first insert the header (containing the Class name, whereas the table headers will be imported from DataTable itself). Please use the Cells.Merge method to merge three cells in a row and apply the style to the header merged cell using the Cell.ApplyStyle method.
- Import the DataTable (containing the specified number of rows) onto the worksheet starting from the row next to the header merged cells.
- Repeat 3 to 4 until all the rows from the master DataTable have been imported.
In order to ease the task, please check the following article.