I have an app that "finds" the cell that contains a string. What I need to do then is copy the style, to include borders of the cell next to it. It is a style formating sheet that is used to apply styles and borders to output based on findibg it on the sheet. If I want 'std-widget-style' I look for that string value on the worksheet. If it is found in "A7" then I want to apply the style and any borders in cell "B7" to my output destination workheet.
Hi,
Well, Aspose.Cells for .NET does provide some APIs if you want to find a string in the worksheet cells, e.g Cells.FindString.
//…
Worksheet worksheet = new Workbook().Worksheets[0];
Aspose.Cells.Cell foundCell = worksheet.Cells.FindString(stringtofind, null);
string cellName = foundCell.Name;
//…
See the document for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/find-or-search-data.html
Moreover, to copy the style formatting from one cell to other cell, you can use Cell.GetStyle method to get the style object of the cell and the use Cell.SetStyle method to specify that style to other cell.
See the documents for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/approaches-to-format-data-in-cells.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/colors-background-patterns.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/adding-borders-to-cells.html
Thank you.
I wasn't clear. I have an existing excel automation program being converted.
I can find the cell I want, say "A7". Now I want copy copy the style/borders of "B7" to another cell on another workbook/worksheet.
I've spent hours searching your forum and I find lots of hits that involve literals, or aspose constants. What I'm looking for is how to reference "B7" (being an offset from the cell variable set using the find that now references "A7"). I want to copy "B7" style/borders to WB1.WS(0).cells("F7").
Hi,
We are not very sure about your requirement. Kindly provide us more details about your needs. We appreciate if you could perform your task manually in MS Excel and create Excel file(s), also, do highlight (by marking using lines, arrows, circles etc with details) if possible. In this way, we can clearly understand you and then help you how you can implement the task using Aspose.Cells API.
Thanks for your understanding!
I cannot easily reproduce in Excel. In order to improve the Excel performance I manually copied 20 cell attributes that I used, borders, font, size, bold, etc. Rather than doing an Excel Copy and Paste methods.
What I have done is show you some same output and my input spreadsheet. In practice the 2 sheets are in different files. The formatting tab is one of many table that control look and feel. In this specific instance I would do a find on the Formatting tab, looking for "std-prod-detail' and I would find cell "A6". I would then want to copy cell style and borders from "B6" to my SampleOutput tab cell "A18", and I would copy it to "A19" also, changing the cell value as I output data.
I know I'm position at SampleOutput "A18" and I have found style I want at Formatting tab cell "B7" but I'm I only know this because I have a reference to "A7" and I know I want whatever style/borders are in the cell next to it.
I am already doing copy and paste of everything on the page, except the product min-max ranges and the rates, just can't get the style and borders piece.
Hi,
Thanks for providing us the template file with some details.
I have tried to write a sample code, if it helps you a bit for implementing your requirement. I also added comments with the lines of code.
Sample code:
//Instantiate a workbook
Workbook workbook = new Workbook();
//Open the template file.
workbook.Open(“e:\test\SampleOutput.xlsm”,FileFormatType.Excel2007Xlsm);
//Get the second worksheet.
Worksheet sheet2 = workbook.Worksheets[1];
Cells cells = sheet2.Cells;
FindOptions findoption = new FindOptions();
findoption.IsCaseSensitive = false;
//Find the cell that contains the string: std-prod-detail
Aspose.Cells.Cell fcell = sheet2.Cells.FindString(“std-prod-detail”, null, findoption);
MessageBox.Show(fcell.Name); //it will be A6
int row, col;
//Get the cell next to the found cell.
CellsHelper.CellNameToIndex(fcell.Name, out row, out col);
Aspose.Cells.Cell nextcell = cells[row,col+1];
MessageBox.Show(nextcell.Name); //It will be B6
//Define the style object with all the style formattings of this cell
Style style = nextcell.GetStyle();
//Get the sheet “SampleOutput”
Worksheet sheet = workbook.Worksheets[“SampleOutput”];
Cells mycells = sheet.Cells;
Aspose.Cells.Cell mycell = mycells[“A18”];
//Set the style from B6 cell to A18 with all the formatting applied.
mycell.SetStyle(style);
workbook.Save(“e:\test\ouputfile.xlsm”,FileFormatType.Excel2007Xlsm);
Thank you.