Thursday, 05 April 2012 03:07

How to Set Word Table Style in C#, VB.NET

Table in Microsoft Word is used to present data information which can assist to explain specified paragraph contents. In order to have a better appearance, people can set Word table style. This guide shows how to use Spire.Doc to set table style in Word with C#/VB.NET.

Download Spire.Doc (or Spire.Office) with .NET Framework 2.0 (or above) together. Once make sure Spire.Doc (or Spire.Office) are correctly installed on system, follow the steps below to set Word table style

In this example, a Word document with table has been prepared. It is a student transcript template from Office.com.

Step 1Create a C#/VB.NET project in Visual Studio. Add Spire.Doc.dll as reference.

[C#]
Document document = new Document();
document.LoadFromFile(@"E:\work\Documents\Student Transcript.docx");
[VB.NET]
Dim document As New Document()
document.LoadFromFile("E:\work\Documents\Student Transcript.docx")

Step 2: Set Table Style

Get table which you want to set style

Because table1 type is different from document.Sections[0].Tables[1] type, so use (Table) to transformed forcibly.

[C#]
Table table1 = (Table)document.Sections[0].Tables[1];
[VB.NET]
Dim table1 As Table = CType(document.Sections(0).Tables(1), Table)

Set table row height.

[C#]
table1.Rows[0].Height = 25;
[VB.NET]
table1.Rows(0).Height = 25

Set Table Style

In order to have distinction. Keep the first cell in first row as before and set style for the second cell. Firstly, set alignment and background color for the second cell. Secondly, declare a paragraph style, including font size, color and apply this style in cell.

[C#]
table1.Rows[0].Cells[1].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
table1.Rows[0].Cells[1].CellFormat.BackColor = Color.LimeGreen;

ParagraphStyle style = new ParagraphStyle(document);
style.Name = "TableStyle";
style.CharacterFormat.FontSize = 14;
style.CharacterFormat.TextColor = Color.GhostWhite;
document.Styles.Add(style);
table1.Rows[0].Cells[1].Paragraphs[0].ApplyStyle(style.Name);
[VB.NET]
table1.Rows(0).Cells(1).CellFormat.VerticalAlignment = VerticalAlignment.Middle
table1.Rows(0).Cells(1).CellFormat.BackColor = Color.LimeGreen

Dim style As New ParagraphStyle(document)
style.Name = "TableStyle"
style.CharacterFormat.FontSize = 14
style.CharacterFormat.TextColor = Color.GhostWhite
document.Styles.Add(style)
table1.Rows(0).Cells(1).Paragraphs(0).ApplyStyle(style.Name)

Step 3: Save and Launch

[C#]
document.SaveToFile("WordTable.docx", FileFormat.Docx);
System.Diagnostics.Process.Start("WordTable.docx");
[VB.NET]
document.SaveToFile("WordTable.docx", FileFormat.Docx)
System.Diagnostics.Process.Start("WordTable.docx")

Effective Screenshot:

Word Table Format

This guide shows how to set Word table style such as size and color via Spire.Doc. However, Spire.Doc can do a lot on operating Word document Click to learn more

Wednesday, 04 April 2012 03:22

Excel Sort-Sort Excel Data in C#, VB.NET

Sorting Excel data is very useful in Excel. It helps people automatically sort the Excel data information quickly which saves much time and avoid mistakes. This section will introduce a solution to sort Excel data by four ways: Ascending, Descending, Top and Bottom via a .NET Excel component in C#, VB.NET.

Spire.XLS for .NET is a professional Excel component which is applied in .NET applications. This .NET component enables you to sort Excel data both in horizontal and vertical directions. Only by two lines’ of key code, you can easily sort excel data according to your need. First you are required to decide which column that you need to sort by calling this method: Workbook.DataSorter.SortColumn Add(int key, OrderBy orderBy). Then, you can set the cell range that you need to sort through Workbook. DataSorter.Sort(CellRange range). Please view the effect of this task as below picture:

Sort Excel Data

Here you can download Spire.XLS for .NET and install it in your system. After adding Spire.Xls dll from your Bin folder, please follow below code to accomplish your task directly.

[C#]
private void button1_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\sort excel.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];

            //append the sort column index and order by attributes 
            workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending);
 
            //set the range to sort.
            workbook.DataSorter.Sort(worksheet["A1:E12"]);

            //save and launch the file5
            workbook.SaveToFile(@"test.xlsx",ExcelVersion.Version2010);
            ExcelDocViewer(@"test.xlsx");
        }
            private void ExcelDocViewer(string fileName)
            {
                try
                {
                    System.Diagnostics.Process.Start(fileName);
                }
                catch { }
             }
VB.NET:
Private Sub button1_Click(sender As Object, e As EventArgs)
	Dim workbook As New Workbook()
	workbook.LoadFromFile("..\sort excel.xlsx")
	Dim worksheet As Worksheet = workbook.Worksheets(0)

	'append the sort column index and order by attributes 
	workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending)

	'set the range to sort.
	workbook.DataSorter.Sort(worksheet("A1:E12"))

	'save and launch the file5
	workbook.SaveToFile("test.xlsx", ExcelVersion.Version2010)
	ExcelDocViewer("test.xlsx")
End Sub
Private Sub ExcelDocViewer(fileName As String)
	Try
		System.Diagnostics.Process.Start(fileName)
	Catch
	End Try
End Sub

Wrap text and unwrap text in excel cells are indispensable for developers. Wrap text is specially required when the excel text exceeds the column width or excel data cannot be shown completely. While unwrap text enables the cell data to be displayed in orderly rows and columns. In this section, a solution will be introduced for you to quickly wrap or unwrap text in excel with C#, VB.NET via a .NET Excel component.

Spire.XLS for .NET, as a standalone excel component, can help both enterprises and individuals to operate a wide range of tasks in Excel document such as convert excel to other formats, encrypt/decrypt excel, generate different kinds of charts and tables etc. Using this Excel component, you can easily wrap and unwrap through two lines of core code.

Suppose you want to wrap text in A1, you can set up the A1 cell text and wrap properties by Worksheet. Range["A1"].Text and Worksheet.Range["A1"].Style.WrapText = true. Of course if you want to unwrap text in A1, you can set the wrap property to be false. Now, please download Spire.XLS for .NET and preview the effect of your text wrap and unwrap task as below picture.

Wrap and Unwrap Excel Data

[C#]
using Spire.Xls;

namespace wrap
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook;
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];


            //Wrap the excel text;
            sheet.Range["C1"].Text = "e-iceblue is in facebook and welcome to like us";
            sheet.Range["C1"].Style.WrapText = true;
            sheet.Range["D1"].Text = "e-iceblue is in twitter and welcome to follow us";
            sheet.Range["D1"].Style.WrapText=  true;


            //Unwrap the excel text;
            sheet.Range["C2"].Text = "http://www.facebook.com/pages/e-iceblue/139657096082266";
            sheet.Range["C2"].Style.WrapText= false;
            sheet.Range["D2"].Text = "https://twitter.com/eiceblue";
            sheet.Range["D2"].Style.WrapText = false;


            //set the text color of Range["C1:D1"]
            sheet.Range["C1:D1"].Style.Font.Size = 15;
            sheet.Range["C1:D1"].Style.Font.Color = Color.Blue;
            //set the text color of Range["C2:D2"]
            sheet.Range["C2:D2"].Style.Font.Size = 15;
            sheet.Range["C2:D2"].Style.Font.Color = Color.DeepSkyBlue;

            // Save the project;
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);

        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace wrap
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            'Create a new workbook;
            Dim workbook As Workbook = New Workbook
            Dim sheet As Worksheet = workbook.Worksheets(0)
            'Wrap the excel text;
            sheet.Range("C1").Text = "e-iceblue is in facebook and welcome to like us"
            sheet.Range("C1").Style.WrapText = true
            sheet.Range("D1").Text = "e-iceblue is in twitter and welcome to follow us"
            sheet.Range("D1").Style.WrapText = true
            'Unwrap the excel text;
            sheet.Range("C2").Text = "http://www.facebook.com/pages/e-iceblue/139657096082266"
            sheet.Range("C2").Style.WrapText = false
            sheet.Range("D2").Text = "https://twitter.com/eiceblue"
            sheet.Range("D2").Style.WrapText = false
            'set the text color of Range["C1:D1"]
            sheet.Range("C1:D1").Style.Font.Size = 15
            sheet.Range("C1:D1").Style.Font.Color = Color.Blue
            'set the text color of Range["C2:D2"]
            sheet.Range("C2:D2").Style.Font.Size = 15
            sheet.Range("C2:D2").Style.Font.Color = Color.DeepSkyBlue
            ' Save the project;
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
            System.Diagnostics.Process.Start(workbook.FileName)
        End Sub
    End Class
End Namespace

Excel comment is added especially when the cell information is changed or need some additional notice. When we manually add comments in excel, it is such easy. While how about add excel comments programmatically? Could we set excel comment format in C#, VB.NET? The answer is absolutely YES. Now let us go to the main solution.

Excel comment will be quickly added as a string by this .NET Excel component Spire.XLS for .NET. Then, using ExcelFont which is a class in Spire.Xls, we can set comment format such as font, color, bold. In order to fit the comment perfectly, we can use another class Spire.Xls.RichText to set the text box size. Finally, when we want to highlight the special text in comment we can call this method: ExcelComment.RichText.SetFont(int startPos, int endPos, ExcelFont font). Now please see the comment effect as below picture:

Add Excel Comments

Here you can download Spire.XLS for .NET and install it on your system. After adding Spire.Xls dll, you can follow below solution to add comments in Excel.

[C#]
String str = "E-mail has been changed as evanece@hotmail.com";
            ExcelFont font1 = workbook.CreateFont();
            font1.FontName = "Calibri";
            font1.Color = Color.Firebrick;
            font1.IsBold = true;
            sheet.Range["C6"].Comment.RichText.Text = str;
            sheet.Range["C6"].Comment.Width = 200;
            sheet.Range["C6"].Comment.Height = 50;
            sheet.Range["C6"].Comment.RichText.SetFont(26, 45, font1);
[VB.NET]
Dim str As String = "E-mail has been changed as evanece@hotmail.com"
Dim font1 As ExcelFont = workbook.CreateFont()
font1.FontName = "Calibri"
font1.Color = Color.Firebrick
font1.IsBold = True
sheet.Range("C6").Comment.RichText.Text = str
sheet.Range("C6").Comment.Width = 200
sheet.Range("C6").Comment.Height = 50
sheet.Range("C6").Comment.RichText.SetFont(26, 45, font1)
Friday, 23 March 2012 02:30

How to Convert Word to Tiff

TIFF (Tagged Image File Format) is a flexible file format that is used to store images, including photos and art images. It’s popular and widely supported by image-manipulation applications, publishing and page layout applications, and scanning, faxing, word processing applications, etc. It can be a container holding compressed (lossy) JPEG and (lossless) PackBits compressed images. The ability to store image data in a lossless format makes a TIFF file to be a useful image archive. Therefore, sometimes developers need to convert documents in other formats (such as word) to TIFF format.

Spire.Doc, a powerful .NET word component which enables developers to convert files from word to TIFF easily. This article is going to introduce you the solution of converting word to TIFF by using Spire.Doc.

Note: before start, please download the latest version of Spire.Doc, then add the .dll in the bin folder as the reference of Visual Studio.

The sample word file:

How to Convert Word to Tiff

Follow the detail steps below:

Step 1: create a new document instance and load a word document from file.

Document document = new Document(@"E:\Program Files\testing.docx");

Step 2: use document.SaveToImages() method to save the word document as Image array.

private static Image[] SaveAsImage(Document document)
{     
    Image[] images = document.SaveToImages(ImageType.Bitmap);    
    return images;
}

Step 3: use JoinTiffImages() method to save the images from word pages to tiff image type, with the specified encoder and image-encoder parameters.

public static void JoinTiffImages(Image[] images, string outFile, EncoderValue compressEncoder)
{
    //use the save encoder
    System.Drawing.Imaging.Encoder enc = System.Drawing.Imaging.Encoder.SaveFlag;
    EncoderParameters ep = new EncoderParameters(2);
    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
    ep.Param[1] = new EncoderParameter(System.Drawing.Imaging.Encoder.Compression, (long)compressEncoder);
    Image pages = images[0];
    int frame = 0;
    ImageCodecInfo info = GetEncoderInfo("image/tiff");
    foreach (Image img in images)
    {
        if (frame == 0)
        {
            pages = img;
            //save the first frame
            pages.Save(outFile, info, ep);
        }
        else
        {
            //save the intermediate frames
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.FrameDimensionPage);
            pages.SaveAdd(img, ep);
        }
        if (frame == images.Length - 1)
        {
            //flush and close.
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.Flush);
            pages.SaveAdd(ep);
        }
        frame++;
    }
}

The result TIFF file:

How to Convert Word to Tiff

Full codes:

[C#]
using Spire.Doc;
using Spire.Doc.Documents;
using System;
using System.Drawing;
using System.Drawing.Imaging;

namespace convert_word_to_tiff
{
    class Program
    {
        static void Main(string[] args)
        {
            Document document = new Document(@"E:\Program Files\testing.docx");
            JoinTiffImages(SaveAsImage(document),"6056result.tiff",EncoderValue.CompressionLZW);
            System.Diagnostics.Process.Start("6056result.tiff");
        }
        private static Image[] SaveAsImage(Document document)
        {     
            Image[] images = document.SaveToImages(ImageType.Bitmap);    
            return images;
        }

        private static ImageCodecInfo GetEncoderInfo(string mimeType)
        {
            ImageCodecInfo[] encoders = ImageCodecInfo.GetImageEncoders();
            for (int j = 0; j < encoders.Length; j++)
            {
                if (encoders[j].MimeType == mimeType)
                    return encoders[j];
            }
            throw new Exception(mimeType + " mime type not found in ImageCodecInfo");
        }

        public static void JoinTiffImages(Image[] images, string outFile, EncoderValue compressEncoder)
        {
            //use the save encoder
            System.Drawing.Imaging.Encoder enc = System.Drawing.Imaging.Encoder.SaveFlag;
            EncoderParameters ep = new EncoderParameters(2);
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
            ep.Param[1] = new EncoderParameter(System.Drawing.Imaging.Encoder.Compression, (long)compressEncoder);
            Image pages = images[0];
            int frame = 0;
            ImageCodecInfo info = GetEncoderInfo("image/tiff");
            foreach (Image img in images)
            {
                if (frame == 0)
                {
                    pages = img;
                    //save the first frame
                    pages.Save(outFile, info, ep);
                }

                else
                {
                    //save the intermediate frames
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.FrameDimensionPage);

                    pages.SaveAdd(img, ep);
                }
                if (frame == images.Length - 1)
                {
                    //flush and close.
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.Flush);
                    pages.SaveAdd(ep);
                }
                frame++;
            }
        }
    }
}
[VB.NET]
Imports Spire.Doc
Imports Spire.Doc.Documents
Imports System.Drawing
Imports System.Drawing.Imaging

Namespace convert_word_to_tiff
	Class Program
		Private Shared Sub Main(args As String())
			Dim document As New Document("E:\Program Files\testing.docx")
			JoinTiffImages(SaveAsImage(document), "6056result.tiff", EncoderValue.CompressionLZW)
			System.Diagnostics.Process.Start("6056result.tiff")
		End Sub
		Private Shared Function SaveAsImage(document As Document) As Image()
			Dim images As Image() = document.SaveToImages(ImageType.Bitmap)
			Return images
		End Function

		Private Shared Function GetEncoderInfo(mimeType As String) As ImageCodecInfo
			Dim encoders As ImageCodecInfo() = ImageCodecInfo.GetImageEncoders()
			For j As Integer = 0 To encoders.Length - 1
				If encoders(j).MimeType = mimeType Then
					Return encoders(j)
				End If
			Next
			Throw New Exception(mimeType & Convert.ToString(" mime type not found in ImageCodecInfo"))
		End Function

		Public Shared Sub JoinTiffImages(images As Image(), outFile As String, compressEncoder As EncoderValue)
			'use the save encoder
			Dim enc As System.Drawing.Imaging.Encoder = System.Drawing.Imaging.Encoder.SaveFlag
			Dim ep As New EncoderParameters(2)
			ep.Param(0) = New EncoderParameter(enc, CLng(EncoderValue.MultiFrame))
			ep.Param(1) = New EncoderParameter(System.Drawing.Imaging.Encoder.Compression, CLng(compressEncoder))
			Dim pages As Image = images(0)
			Dim frame As Integer = 0
			Dim info As ImageCodecInfo = GetEncoderInfo("image/tiff")
			For Each img As Image In images
				If frame = 0 Then
					pages = img
					'save the first frame
					pages.Save(outFile, info, ep)
				Else

					'save the intermediate frames
					ep.Param(0) = New EncoderParameter(enc, CLng(EncoderValue.FrameDimensionPage))

					pages.SaveAdd(img, ep)
				End If
				If frame = images.Length - 1 Then
					'flush and close.
					ep.Param(0) = New EncoderParameter(enc, CLng(EncoderValue.Flush))
					pages.SaveAdd(ep)
				End If
				frame += 1
			Next
		End Sub
	End Class
End Namespace

Spire.Doc can convert Word to most of popular file formats. It can convert Word to PDF, HTML, XML, RTF, Text, ePub, etc. Click to learn more

Wednesday, 21 March 2012 09:41

Set PDF Page Orientation in Silverlight

The sample demonstrates how to set PDF page orientation for Silverlight via Spire.PDF.

 

Wednesday, 21 March 2012 09:36

Lock Worksheet in Silverlight

The sample demonstrates how to Lock Worksheet in Silverlight via Spire.XLS.

 

Wednesday, 21 March 2012 09:29

Insert Comments into Word in Silverlight

The sample demonstrates how to insert comments into Word for Silverlight via Spire.Doc.

 

Tuesday, 20 March 2012 05:20

Set Excel Properties in C#, VB.NET

Excel Properties, which can be taken as metadata, present details of one file, including title, author, subject etc. Users can change properties according to data information, for example, adding key words or category for the file. But some properties are read-only, such as file size, created date, location and so on.

Spire.XLS for .NET, a professional component to manipulate Excel files, enables users to set Excel properties by using C#, VB.NET. This guide focuses on introducing method to set properties for Excel file.

Set Excel Properties

Users can set DocumentProperties property of Workbook class to change Excel properties as they want. The following screenshot shows properties after changing. Download and install Spire.XLS for .NET and use the following code to set Excel properties.

[C#]
workbook.DocumentProperties.Author = "E-iceblue Support Team";
workbook.DocumentProperties.Title = "Excel Properties";
workbook.DocumentProperties.Keywords = "Excel Properties C#, Excel Properties VB.NET";
workbook.DocumentProperties.Category = "Excel Demo";
workbook.DocumentProperties.Company = "E-iceblue";
[VB.NET]
workbook.DocumentProperties.Author = "E-iceblue Support Team"
workbook.DocumentProperties.Title = "Excel Properties"
workbook.DocumentProperties.Keywords = "Excel Properties C#, Excel Properties VB.NET"
workbook.DocumentProperties.Category = "Excel Demo"
workbook.DocumentProperties.Company = "E-iceblue"
Monday, 19 March 2012 06:55

Merge Excel Files into One in C#, VB.NET

Sometimes, people feel annoyed when they have to open a lot of Excel files simultaneously. Thus, Merging Excel files of the same type or category can help us avoid the trouble and save us much time. Besides, we would also like to merge multiple files in a single file for easy sharing and saving.

In the following section, an easy method is introduced to show how users can merge Excel files into one using Spire.XLS in C# and VB.NET.

Code Snippet:

Step 1: Create a new Workbook and remove the blank worksheets.

Workbook newbook = new Workbook();
newbook.Version = ExcelVersion.Version2010;
newbook.Worksheets.Clear();

Step 2: Create a temporary Workbook and load the sample files that you want to merge. Traversal every worksheet in tempbook, and add the copy of each worksheet to the new Workbook using AddCopy() method. By doing so, all the worksheets in the sample files will be added to the new Workbook.

Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
    tempbook.LoadFromFile(excelFiles[i]);
    foreach (Worksheet sheet in tempbook.Worksheets)
    {
        newbook.Worksheets.AddCopy(sheet);
    }
}

Step 3: Save the changes to the new Workbook and launch the file.

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");  

Output:

Each sample file only contains one worksheet in its workbook, therefore there would be three sheets in the result file.

Merge Excel Files into One in C#, VB.NET

Full Code:

[C#]
Workbook newbook = new Workbook();
newbook.Version = ExcelVersion.Version2010;
newbook.Worksheets.Clear();

Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
    tempbook.LoadFromFile(excelFiles[i]);
    foreach (Worksheet sheet in tempbook.Worksheets)
    {
        newbook.Worksheets.AddCopy(sheet);
    }
}

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
[VB.NET]
Dim newbook As New Workbook()
newbook.Version = ExcelVersion.Version2010
newbook.Worksheets.Clear()

Dim tempbook As New Workbook()
Dim excelFiles As String() = New [String]() {"sample1.xlsx", "sample2.xlsx", "sample3.xlsx"}
For i As Integer = 0 To excelFiles.Length - 1
	tempbook.LoadFromFile(excelFiles(i))
	For Each sheet As Worksheet In tempbook.Worksheets
		newbook.Worksheets.AddCopy(sheet)
	Next
Next

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")