Knowledgebase (2330)
Children categories
Copy Formatting from One Cell or Range to Another in C#, VB.NET
2014-08-28 06:30:47 Written by KoohjiWhen we are dealing with Excel spreadsheets, we may get a cell or a range of cells formatted, then we can use the Format Painter to quickly copy formatting from one place and apply it to another. It saves us much time to do so if we want to format a cell or cells with the previous cell formatting. In this article, I'll introduce you how to copy formatting from a range of cells to another using Spire.XLS.
As is shown in the screenshot of test file, contents in the first column have been formatted with different styles, what we want is to correspondingly apply cell formatting from column 1 to column 3.
Code snippets:
Step 1: Create a new instance of Wordbook class and load the test file from disk.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Test.xlsx");
Step 2: Get the worksheet from workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Copy the cell formatting from column 1 and apply to cells of column 3.
int count = sheet.Rows.Count();
for (int i = 1; i < count + 1; i++)
{
sheet.Range[string.Format("C{0}", i)].Style = sheet.Range[string.Format("A{0}", i)].Style;
}
Step 4: Save to a new Excel file.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
Result:
Full Code:
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Test.xlsx");
Worksheet sheet = workbook.Worksheets[0];
int count = sheet.Rows.Count();
for (int i = 1; i < count + 1; i++)
{
sheet.Range[string.Format("C{0}", i)].Style = sheet.Range[string.Format("A{0}", i)].Style;
}
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Test.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim count As Integer = sheet.Rows.Count()
For i As Integer = 1 To count
sheet.Range(String.Format("C{0}", i)).Style = sheet.Range(String.Format("A{0}", i)).Style
Next
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010)
End Sub
All too often, we start a table near the bottom of a page. Microsoft Word automatically places a page break in a table if a single row or several rows are too long to fit between the top and bottom margins of the page. So we'd rather the same row can be placed on one page or have the entire table on the next page instead of being broken over two pages. In this article, I'll introduce you two ways to avoid page breaks in Word tables via Spire.Doc.
Assume that we have a Word table like this (Row 2 splits to different pages), we may want to optimize the layout by the following two methods.
Method 1: Keep the entire table on the same page.
Step 1: Create a new Word document and load the test file.
Document doc = new Document("Test.docx");
Step 2: Get the table from Word document.
Table table = doc.Sections[0].Tables[0] as Table;
Step 3: Change the paragraph setting to keep them together.
foreach (TableRow row in table.Rows)
{
foreach (TableCell cell in row.Cells)
{
foreach (Paragraph p in cell.Paragraphs)
{
p.Format.KeepFollow = true;
}
}
}
Step 4: Save and launch the file.
doc.SaveToFile("Result.docx", FileFormat.Docx2010);
System.Diagnostics.Process.Start("Result.docx");
Output:
Use this sentence to replace Step 3, you'll get the layout as below:
Full C# Code:
Method 1
using Spire.Doc;
using Spire.Doc.Documents;
namespace PreventPageBreak
{
class Program
{
static void Main(string[] args)
{
Document doc = new Document("Test.docx");
Table table = doc.Sections[0].Tables[0] as Table;
foreach (TableRow row in table.Rows)
{
foreach (TableCell cell in row.Cells)
{
foreach (Paragraph p in cell.Paragraphs)
{
p.Format.KeepFollow = true;
}
}
}
doc.SaveToFile("Result.docx", FileFormat.Docx2010);
System.Diagnostics.Process.Start("Result.docx");
}
}
}
Spire.Doc has a powerful function of processing word table such as create and remove word table, set the table column width, style and so on. Spire.Doc also supports to add the table in the middle of the word document. This article will show you how to replace text with table by finding the key text in the word document.
Download and install Spire.Doc for .NET and then add Spire.Doc.dll as reference in the downloaded Bin folder though the below path: "..\Spire.Doc\Bin\NET4.0\ Spire.Doc.dll". Here comes to the details of how to finding the text and then replace it with table in C#.
Check the original word document at first:
Step 1: Create a new document and load from file.
Document doc = new Document();
doc.LoadFromFile("sample.docx");
Step 2: Find the place where you want to replace with table.
Section section = doc.Sections[0];
//"Fortune" as a "key text"
TextSelection selection = doc.FindString("Fortune", true, true);
TextRange range = selection.GetAsOneRange();
Paragraph paragraph = range.OwnerParagraph;
Body body = paragraph.OwnerTextBody;
int index = body.ChildObjects.IndexOf(paragraph);
Step 3: Add a table and set its style.
Table table = section.AddTable(true); table.ResetCells(3,3);
Step 4: Remove the paragraph and insert the table.
body.ChildObjects.Remove(paragraph); body.ChildObjects.Insert(index, table);
Step 5: Save the document to file.
doc.SaveToFile("Result.docx", FileFormat.Docx);
Effective screenshot of add a table in the middle of the word document by replacing the text.
Full codes:
namespace replaceTextwithTable
{
class Program
{
static void Main(string[] args)
{
Document doc = new Document();
doc.LoadFromFile("sample.docx");
Section section = doc.Sections[0];
TextSelection selection = doc.FindString("Fortune", true, true);
TextRange range = selection.GetAsOneRange();
Paragraph paragraph = range.OwnerParagraph;
Body body = paragraph.OwnerTextBody;
int index = body.ChildObjects.IndexOf(paragraph);
Table table = section.AddTable(true);
table.ResetCells(3,3);
body.ChildObjects.Remove(paragraph);
body.ChildObjects.Insert(index, table);
doc.SaveToFile("Result.docx", FileFormat.Docx);
}
}
}
