sample.zip (8.3 KB)
sample.JPG (59.5 KB)
Dear Team,
We are using Aspose license product, we are stuck with once of the implementation, could you please support in resolving below issue.
Issue: Based on the array list size and values dynamically excel cell should get applied with border, fixed bordering the table is working fine but we are facing issue applying dynamic bordering to table.
Thanks for support.
Chandan(Infosys)
@dnchandan1 and @shantanu.romios
Thanks for considering Aspose APIs.
You can modify top, left, right, bottom and diagonal borders using Aspose.Cells APIs. You can apply them to a single cell, range of cells, entire column or entire row. You can write your algorithm to apply the borders dynamically.
Please see the following sample code, its output Excel file, its comments and screenshot for a help. It should help you know how to deal with borders and fix your issue.
Download Link:
outputBorders.zip (5.8 KB)
C#
//Create workbook
Workbook wb = new Workbook();
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Create style object
Style st = wb.CreateStyle();
//Modify left border
Border bd = st.Borders[BorderType.LeftBorder];
bd.Color = Color.Red;
bd.LineStyle = CellBorderType.Thick;
//Modify right border
//bd = st.Borders[BorderType.RightBorder];
//bd.Color = Color.Blue ;
//bd.LineStyle = CellBorderType.Thick;
//Modify top border
bd = st.Borders[BorderType.TopBorder];
bd.Color = Color.Green;
bd.LineStyle = CellBorderType.Thick;
//Modify bottom border
bd = st.Borders[BorderType.BottomBorder];
bd.Color = Color.Purple;
bd.LineStyle = CellBorderType.Thick;
//Modify diagonal down border
bd = st.Borders[BorderType.DiagonalDown];
bd.Color = Color.Pink;
bd.LineStyle = CellBorderType.Thick;
//Modify diagonal up border
//bd = st.Borders[BorderType.DiagonalUp];
//bd.Color = Color.DeepSkyBlue;
//bd.LineStyle = CellBorderType.Thick;
//Apply the style object to a range, you can also apply it to entire row or column
Range rng = ws.Cells.CreateRange("A2:Q2");
StyleFlag flg = new StyleFlag();
flg.Borders = true;
rng.ApplyStyle(st, flg);
//Apply the style object to anoter range
rng = ws.Cells.CreateRange("D5:D20");
rng.ApplyStyle(st, flg);
wb.Save("outputBorders.xlsx");
Screenshot:
Thank you for your reply/
But the excel sample file and the OutputBorders zip files are inaccessible.
Even the screenshot is not visible.
A message pops up.
“Sorry, this file is private. Only visible to topic owner and staff members.”
Can you make it available for me as well as me and @dnchandan1 are in a same team and face this issue.
Your help is appreciated.
Please recheck, I have added your name in the above post. It should fix the issue possibly. Let us know your feedback.
Thanks for your reply , but still the same issue and its showing that warning message
“Sorry, this file is private. Only visible to topic owner and staff members.”
even after adding my name.
Anyways one more thing , i used some of the code in my java aspose class , the method you have suggested is for Formatting the borders and table outlines either Left margin or Right Margin.
What we aspire for is , we have an array list coming , and that can be of any length depending upon the Db.
I have an excel template at some particular location which show the borders ,coloum names etc and the part where the data will come is left blank.
So now the situation is When i will click a button from my UI the array list will be generated with required data and by using FOR each loop we are setting the data into that excel template that we have designed, NOW the problem is since excel template that we have defined is of fixed rows and coloums , so if the array list is large it will goo outside the layout or borders that we have designed for our template , and hence our target of generating PDF report from excel template is not achieved.
This is the issue we are facing , I hope i you are clear what i am trying to ask and what problem i am facing.
So we need Dynamic expansion of that excel template so it will fit the array list of any size automatically.
Thanks in Advance
Thanks for using Aspose APIs.
Suppose, you have filled your Excel file with your desired data coming from the list. Now, call your Excel file as input Excel file i.e. sampleExcel.xlsx inside the code. Now you could quickly apply built-in formatting. Please create a Table aka List Object and then apply built-in formatting and then convert it to range.
Download Links:
Input and Output Excel Files.zip (14.0 KB)
Raw Links:
https://forum.aspose.com/uploads/default/11192
https://i.imgur.com/dZzjVbK.png
Here is the code in Java and C#.
Java
//Load your sample Excel file
Workbook wb = new Workbook(dirPath + "sampleExcel.xlsx");
//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);
//Find the last cell of your sheet
String endCell = ws.getCells().getLastCell().getName();
//Add a list object, format it with built-in formats and convert it to range
int idx = ws.getListObjects().add("A1", endCell, true);
ListObject lo = ws.getListObjects().get(idx);
lo.setTableStyleType(TableStyleType.TABLE_STYLE_MEDIUM_2);
lo.convertToRange();
//Save the output Excel file
wb.save(dirPath + "output.xlsx");
C#
//Load your sample Excel file
Workbook wb = new Workbook("sampleExcel.xlsx");
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Find the last cell of your sheet
String endCell = ws.Cells.LastCell.Name;
//Add a list object, format it with built-in formats and convert it to range
int idx = ws.ListObjects.Add("A1", endCell, true);
ListObject lo = ws.ListObjects[idx];
lo.TableStyleType = TableStyleType.TableStyleMedium2;
lo.ConvertToRange();
//Save the output Excel file
wb.Save("output.xlsx");
Screenshot:
Thanks a lot for your reply and putting an effort to it.
But i have come up with a new and very short way to achieve this , i will share it with you ,
create a worksheet object .
Worksheet dataSheet = wb.getWorksheets().get(0);
Now,
use pre defined methods in java for aspose to increase the size of the excel sheet as per the requirement or as per the required Araay list size and use.
dataSheet.getCells().insertRows(rowIndex, totalRows, updateReference);
This will do the job and make the sheet dynamic.
Thank you 
Please show your sample code so that we know what you are doing. Insert rows method already exist in Aspose.Cells.
Next thing, are you using some import methods e.g.
//Import Data
public int ImportData(DataTable table, int firstRow, int firstColumn, ImportTableOptions options);
Please note, here you can set ImportTableOptions.InsertRows as true and it will automatically insert rows as per your data in array list. For example, if your data has 150 rows, it will first add 150 rows and then import your data in those rows and if your data has 240 rows, it will first add 240 rows and import your data in those rows dynamically.
For your requirement, we think, you can insert rows first, then inserted rows will inherit the existing styles and thus the formatted range will be expanded, then you can fill data into the cells in the inserted rows.