Administrator Blog

Tuesday, 09 September 2014 03:39

Marker Designer

Data

Name	Capital	Continent	Area	Population
Argentina	Buenos Aires	South America	2777815	32300003
Bolivia	La Paz	South America	1098575	7300000
Brazil	Brasilia	South America	8511196	150400000
Canada	Ottawa	North America	9976147	26500000
Chile	Santiago	South America	756943	13200000
Colombia	Bagota	South America	1138907	33000000
Cuba	Havana	North America	114524	10600000
Ecuador	Quito	South America	455502	10600000
El Salvador	San Salvador	North America	20865	5300000
Guyana	Georgetown	South America	214969	800000

Option

Excel Version:

downloads

Demo
Java
C# source

This demo shows you the usage of WorkbookDesigner.

import com.spire.data.table.DataTable;
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class MarkerDesignerDemo {

    public void markerDesignerDemo(String filePath, String dataFilePath, String resultFilePath){
        Workbook data_book = new Workbook();
        data_book.loadFromFile(dataFilePath);
        DataTable table = data_book.getWorksheets().get(0).exportDataTable();

        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath);
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        sheet.setName( "Result");
        sheet2.setName("DataSource");
        sheet2.insertDataTable(table,true,1,1);

        workbook.getMarkerDesigner().addParameter("Variable1", 1234.5678);
        workbook.getMarkerDesigner().addDataTable("Country", table);
        workbook.getMarkerDesigner().apply();

        sheet.getAllocatedRange().autoFitRows();
        sheet.getAllocatedRange().autoFitColumns();

        workbook.saveToFile(resultFilePath, FileFormat.Version2013);


    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class MarkerDesigner
    {

        public void demoMarkerDesigner(string filePath, string dataFilePath, string resultFilePath)
        {
            Workbook data_book = new Workbook();
            data_book.LoadFromFile(dataFilePath);
            DataTable table = data_book.Worksheets[0].ExportDataTable();

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath);
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.Worksheets[1];

            sheet.Name = "Result";
            sheet2.Name = "DataSource";
            sheet2.InsertDataTable(table, true, 1, 1);

            workbook.MarkerDesigner.AddParameter("Variable1", 1234.5678);
            workbook.MarkerDesigner.AddDataTable("Country", table);
            workbook.MarkerDesigner.Apply();

            sheet.AllocatedRange.AutoFitRows();
            sheet.AllocatedRange.AutoFitColumns();
            workbook.SaveToFile(resultFilePath, ExcelVersion.Version2010);
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:38

Calculate Formulas

Mathematic Functions:

Calculate symbol :

Calculate Data:

Logic Function:

Calculate symbol :

Calculate Data:

Simple Expression:

Calculate symbol :

Calculate Data:

MID Functions:

Text :

Start Number:

Number Charts:

Option:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to calculate formulas and export data to datatable with calculating formulas.

import com.spire.xls.*;

public class CalculateFormulaDemo {
    public void CalculateFormulas(String resultFile){
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);
        Calculate(workbook, sheet);
        workbook.saveToFile(resultFile, ExcelVersion.Version2010);
    }

    public void Calculate(Workbook workbook, Worksheet worksheet){
        int currentRow = 1;
        String currentFormula = null;
        Object formulaResult = null;
        String value = null;

        // Set width respectively of Column A ,Column B,Column C
        worksheet.setColumnWidth(1,32);
        worksheet.setColumnWidth(2,16);
        worksheet.setColumnWidth(3,16);

        //Set the value of Cell A1
        worksheet.getRange().get(currentRow++, 1).setValue("Examples of formulas :");

        // Set the value of Cell A2
        worksheet.getRange().get(++currentRow, 1).setValue("Test data:");

        // Set the style of Cell A1
        CellRange range = worksheet.getRange().get("A1");
        range.getStyle().getFont().isBold(true);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Additive operation of mutiple cells

        worksheet.getRange().get(currentRow, 2).setNumberValue(7.3);
        worksheet.getRange().get(currentRow, 3).setNumberValue(5);
        worksheet.getRange().get(currentRow, 4).setNumberValue(8.2);
        worksheet.getRange().get(currentRow, 5).setNumberValue(4);
        worksheet.getRange().get(currentRow, 6).setNumberValue(3);
        worksheet.getRange().get(currentRow, 7).setNumberValue(11.3);

        // Create arithmetic expression string about cells
        currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

        //Caculate arithmetic expression  about cells
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Set the value and format of two head cell
        worksheet.getRange().get(currentRow,1).setValue("Formulas");
        worksheet.getRange().get(currentRow,2).setValue("Results");
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);
        range = worksheet.getRange().get(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Expression caculation
        // Create arithmetic tables enclosed type string
        currentFormula = "=33*3/4-2+10";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate arithmetic expression
        formulaResult = workbook.calculateFormulaValue(currentFormula);

        value = formulaResult.toString();

        worksheet.getRange().get(currentRow,2).setValue(value);

        //Absolute value function
        // Create abosolute value function string
        currentFormula = "=ABS(-1.21)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate abosulte value function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Sum function
        // Create sum function string
        currentFormula = "=SUM(18,29)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate sum function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        //NOT function
        // Create NOT function string
        currentFormula = "=NOT(true)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate NOT function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        //String Manipulation function
        //Get the substring
        // Build substring function
        currentFormula = "=MID(\"world\",4,2)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate substring function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        // Random function

        // Create random function string.
        currentFormula = "=RAND()";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate random function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);


    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CalculateFormulas
    {
        public void demoCalculateFormulas(string resultFile)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            Calculate(workbook, sheet);
            workbook.SaveToFile(resultFile, ExcelVersion.Version2010);
        }
        public void Calculate(Workbook workbook, Worksheet sheet)
        {
            int currentRow = 1;
            string currentFormula = string.Empty;
            object formulaResult = null;
            string value = string.Empty;

            // Set width respectively of Column A ,Column B,Column C
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Set the value of Cell A1
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";

            // Set the value of Cell A2
            sheet.Range[++currentRow, 1].Value = "Test data:";

            // Set the style of Cell A1
            CellRange range = sheet.Range["A1"];
            range.Style.Font.IsBold = true;

            range.Style.FillPattern = ExcelPatternType.Solid;

            range.Style.KnownColor = ExcelColors.LightGreen1;

            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Additive operation of mutiple cells
            sheet.Range[currentRow, 2].NumberValue = 7.3;
            sheet.Range[currentRow, 3].NumberValue = 5;
            sheet.Range[currentRow, 4].NumberValue = 8.2;
            sheet.Range[currentRow, 5].NumberValue = 4;
            sheet.Range[currentRow, 6].NumberValue = 3;
            sheet.Range[currentRow, 7].NumberValue = 11.3;

            // Create arithmetic expression string about cells
            currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

            //Caculate arithmetic expression  about cells
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            // Set the value and format of two head cell
            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
            range = sheet.Range[currentRow, 1, currentRow, 2];
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Expression caculation
            // Create arithmetic tables enclosed type string
            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate arithmetic expression
            formulaResult = workbook.CaculateFormulaValue(currentFormula);

            value = formulaResult.ToString();

            sheet.Range[currentRow, 2].Value = value;

            /// The mathematics function ///

            //Absolute value function

            // Create abosolute value function string
            currentFormula = "=ABS(-1.21)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate abosulte value function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;


            ///  Statistical function///
            // Sum function

            // Create sum function string
            currentFormula = "=SUM(18,29)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate sum function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            ///logic function///
            //NOT function
            // Create NOT function string
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;

            //Caculate NOT function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            //String Manipulation function//
            //Get the substring
            // Build substring function
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate substring function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            // Random function

            // Create random function string.
            currentFormula = "=RAND()";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate random function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:33

Create Pivot Table

Data

e-iceblue

Option

Average Column:			Sum Column:
Max Column:			Min Column:
Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create a privot table in excel.

import com.spire.xls.*;
import com.spire.xls.core.IPivotField;


public class CreatePivotTableDemo {
    public void createPivotTableExcel(String filePath, String resultFilePath){
        Workbook workbook = createPivotTable(filePath);
        workbook.saveToFile(resultFilePath);
    }

    public Workbook createPivotTable(String filePath){
        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath, ExcelVersion.Version2010);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("Data Source");
        Worksheet worksheet1 = workbook.createEmptySheet();
        worksheet1.setName("Pivot Table");

        CellRange dataRange = worksheet.getRange().get("A1:G17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = worksheet1.getPivotTables().add("Pivot Table",worksheet.getRange().get("A1"),cache);

        IPivotField r1 = pt.getPivotFields().get("Vendor No");
        r1.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Vendor No");

        IPivotField r2 = pt.getPivotFields().get("Name");
        r2.setAxis(AxisTypes.Row);

        pt.getDataFields().add(pt.getPivotFields().get("Area"), "Average of Area", SubtotalTypes.Average);
        pt.getDataFields().add(pt.getPivotFields().get("Sales"), "SUM of Sales", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("OnHand"), "Max of OnHand", SubtotalTypes.Max);
        pt.getDataFields().add(pt.getPivotFields().get("OnOrder"), "Min of OnOrder", SubtotalTypes.Min);
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
        pt.calculateData();
        worksheet1.getAllocatedRange().autoFitColumns();
        worksheet1.getAllocatedRange().autoFitRows();
        return workbook;
    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CreatePivotTableInExcel
    {
        public void demoCreatePivotTableInExcel(string filePath, string resultFilePath)
        {
            Workbook workbook = CreatePivotTable(filePath);
            workbook.SaveToFile(resultFilePath);
        }

        public Workbook CreatePivotTable(string filePath)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:G17"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["Vendor No"];
            r1.Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "Vendor No";

            var r2 = pt.PivotFields["Name"];
            r2.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
            pt.CalculateData();
            sheet2.AllocatedRange.AutoFitColumns();
            sheet2.AllocatedRange.AutoFitRows();
            return workbook;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:28

Charts

Data

e-iceblue

Chart Option

Chart format:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create chart in an excel workbook.

import com.spire.xls.*;

public class ChartDemo {
    public void chartDemo(String excelFile, ExcelChartType chartType, String resultFileName){
        Workbook workbook = new Workbook();
		workbook.loadFromFile(excelFile);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        setChart(worksheet,chartType);
        sheetStyle(workbook,worksheet);
        workbook.saveToFile(resultFileName+".xlsx",FileFormat.Version2013);

    }
    private void setChart(Worksheet sheet, ExcelChartType chartType){
        sheet.setName("Chart data");
        sheet.setGridLinesVisible(false);

        //Add a new  chart worsheet to workbook
        Chart chart = sheet.getCharts().add();
        chart.setChartType(chartType);

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C7"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(4);
        chart.setTopRow(2);
        chart.setRightColumn(12);
        chart.setBottomRow(22);

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        chart.getPrimarySerieAxis().setTitle("Country");
        chart.getPrimarySerieAxis().getFont().isBold(true);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPrimarySerieAxis().setTitle("Sales(in Dollars)");
        chart.getPrimarySerieAxis().hasMajorGridLines(false);
        chart.getPrimarySerieAxis().getTitleArea().setTextRotationAngle(90);
        chart.getPrimarySerieAxis().setMinValue(1000);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPlotArea().getFill().setFillType(ShapeFillType.SolidColor);
        chart.getPlotArea().getFill().setForeKnownColor(ExcelColors.White);

        for (int i = 0; i < chart.getSeries().getCount(); i++){
            chart.getSeries().get(i).getFormat().getOptions().isVaryColor(true);
            chart.getSeries().get(i).getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        chart.getLegend().setPosition(LegendPositionType.Top);
    }

    public static void sheetStyle(Workbook workbook, Worksheet sheet){
        CellStyle oddStyle = workbook.getStyles().addStyle("oddStyle");
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        oddStyle.setKnownColor(ExcelColors.LightGreen1);

        CellStyle evenStyle = workbook.getStyles().addStyle("evenStyle");
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        evenStyle.setKnownColor(ExcelColors.LightTurquoise);

        for (int i = 0; i < sheet.getAllocatedRange().getRows().length; i++) {
            CellRange[] ranges = sheet.getAllocatedRange().getRows();
            if (ranges[i].getRow() != 0){
                if (ranges[i].getRow() % 2 == 0)
                {
                    ranges[i].setCellStyleName(evenStyle.getName());
                }
                else
                {
                    ranges[i].setCellStyleName(oddStyle.getName());
                }
            }
        }

        //Sets header style
        CellStyle styleHeader = workbook.getStyles().addStyle("headerStyle");
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        styleHeader.setVerticalAlignment(VerticalAlignType.Center);
        styleHeader.setKnownColor(ExcelColors.Green);
        styleHeader.getFont().setKnownColor(ExcelColors.White);
        styleHeader.getFont().isBold(true);
        styleHeader.setHorizontalAlignment(HorizontalAlignType.Center);

        for (int i = 0; i < sheet.getRows()[0].getCount(); i++) {
            CellRange range = sheet.getRows()[0];
            range.setCellStyleName(styleHeader.getName());
        }

        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -1].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -2].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getRows()[0].getStyle().setNumberFormat("General");

        sheet.getAllocatedRange().autoFitColumns();
        sheet.getAllocatedRange().autoFitRows();

        sheet.getRows()[0].setRowHeight(20);
    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class Charts
    {
        public void demoChart(DataTable table, ExcelChartType chartFormat, string resultFileName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(table, true, 1, 1);
            SetChart(sheet, chartFormat);
            sheetStyle(book, sheet);
            book.SaveToFile(resultFileName, ExcelVersion.Version2010);

        }
        private void SetChart(Worksheet sheet, ExcelChartType chartFormat)
        {
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            //CreateChartData(sheet);
            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();

            //Set region of chart data

            chart.DataRange = sheet.Range["A1:C7"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 8;
            chart.RightColumn = 11;
            chart.BottomRow = 29;
            chart.ChartType = chartFormat;


            //Chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;


            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Position = LegendPositionType.Top;

        }
        public static void sheetStyle(Workbook book, Worksheet sheet)
        {
            CellStyle oddStyle = book.Styles.Add("oddStyle");
            oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            oddStyle.KnownColor = ExcelColors.LightGreen1;

            CellStyle evenStyle = book.Styles.Add("evenStyle");
            evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            evenStyle.KnownColor = ExcelColors.LightTurquoise;


            foreach (CellRange range in sheet.AllocatedRange.Rows)
            {
                if (range.Row % 2 == 0)
                    range.CellStyleName = evenStyle.Name;
                else
                    range.CellStyleName = oddStyle.Name;
            }

            //Sets header style
            CellStyle styleHeader = sheet.Rows[0].Style;
            styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            styleHeader.VerticalAlignment = VerticalAlignType.Center;
            styleHeader.KnownColor = ExcelColors.Green;
            styleHeader.Font.KnownColor = ExcelColors.White;
            styleHeader.Font.IsBold = true;

            sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
            sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";

            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();

            sheet.Rows[0].RowHeight = 20;
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 02:17

Conversion

Upload

Maximum file size: 1 MB. Files accepted: xls, xlsx, xlsb, ods.

Click here to browse files.

fileerrors

Convert to

Source file:

filename

Target file type:

Demo
Java
C# source

This demo shows you how to convert a Excel document (xls/xlsx/xlsb/ods) to PDF, HTML, Image.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.core.spreadsheet.HTMLOptions;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;


public class ConvertDemo {

   public void convertDemo(String filePath, String convertTo, String resultFileName) throws IOException {
       Workbook workbook = new Workbook();
       workbook.loadFromFile(filePath);
       ConvertFormat(workbook,convertTo,resultFileName);

   }
    private void ConvertFormat(Workbook workbook, String convertTo, String resultFileName) throws IOException {
        switch (convertTo){
            case "PDF":
                workbook.getConverterSetting().setSheetFitToPage(true);
                workbook.saveToFile(resultFileName + ".pdf", FileFormat.PDF);
                break;
            case "IMAGE":
                BufferedImage[] images = (BufferedImage[]) new Image[workbook.getWorksheets().size()];
                for (int i = 0; i < workbook.getWorksheets().size();i++){
                    images[i] = workbook.saveAsImage(i,300,300);
                }
                if (images != null && images.length > 0){
                    if (images.length == 1){
                        ImageIO.write(images[0],".PNG", new File(resultFileName+".png"));
                    }
                }else {
                    for (int j = 0; j < images.length;j++){
                        String fileName = String.format("image-{0}.png",j);
                        ImageIO.write(images[j],".PNG",new File(fileName));
                    }
                }
                    break;
            case "HTML":
                for (int i = 0; i < workbook.getWorksheets().size(); i++) {
                    HTMLOptions options = new HTMLOptions();
                    options.setImageEmbedded(true);
                    String htmlPath = String.format(resultFileName+"-{0}.html",i++);
                    workbook.getWorksheets().get(i).saveToHtml(htmlPath,options);
                }
                break;
            case "TIFF":
                workbook.saveToTiff(resultFileName+".tiff");
                break;
            case "XPS":
                workbook.saveToFile(resultFileName+".xps",FileFormat.XPS);
                break;
        }
    }
}

using System;
using Spire.Xls;
using System.Drawing;
using System.Drawing.Imaging;

namespace DemoOnlineCode
{
    class Convertors
    {
        public void demoConvert(String filePath, string format, string resultFileName)
        {
            Workbook book = new Workbook();
            book.LoadFromFile(filePath);
            ConvertFormat(book, format, resultFileName);
        }
        private void ConvertFormat(Workbook workbook, string format, string resultFileName)
        {
            switch (format)
            {
                case "PDF":
                    workbook.ConverterSetting.SheetFitToPage = true;
                    workbook.SaveToFile(resultFileName + ".pdf", Spire.Xls.FileFormat.PDF);
                    break;
                case "IMAGE":
                    Image[] images = new Image[workbook.Worksheets.Count];
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        images[i] = workbook.SaveAsImage(i, 300, 300);
                    }
                    if (images != null && images.Length > 0)
                    {
                        if (images.Length == 1)
                        {
                            images[0].Save(resultFileName + ".png", ImageFormat.Png);
                        }
                        else
                        {
                            for (int i = 0; i < images.Length; i++)
                            {
                                String fileName = String.Format("{0}-Image-{1}.png", resultFileName, i);
                                images[i].Save(fileName, ImageFormat.Png);
                            }
                        }
                    }
                    break;

                case "HTML":
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        Worksheet sheet = workbook.Worksheets[i];
                        string htmlPath = string.Format(resultFileName + "-{0}.html", i++);
                        sheet.SaveToHtml(htmlPath);
                    }
                    break;
                case "TIFF":
                    JoinTiffImages(ToImage(workbook), resultFileName + ".tiff", EncoderValue.CompressionLZW);
                    break;
                case "XPS":
                    workbook.SaveToFile(resultFileName+".xps",FileFormat.XPS);
                    break;
            }
        }
        private static Image[] ToImage(Workbook workbook)
        {
            //Get the worksheet count of workbook
            int workSheetNo = workbook.Worksheets.Count;

            //Create an array
            Image[] images = new Image[workSheetNo];

            //Save worksheet to image and add the array
            for (int i = 0; i < workSheetNo; i++)
            {
                Worksheet workSheet = workbook.Worksheets[i];
                string output = string.Format("result{0}.jpg", i + 1);
                workSheet.SaveToImage(output);
                Image image = Image.FromFile(output);
                images[i] = image;
            }
            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");
        }

        private void JoinTiffImages(Image[] images, string outFile, EncoderValue compressEncoder)
        {
            //Use the save encoder
            Encoder enc = Encoder.SaveFlag;
            EncoderParameters ep = new EncoderParameters(2);
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
            ep.Param[1] = new EncoderParameter(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++;
            }
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Thursday, 19 June 2014 01:50

This demo shows you how to merge some data into a Word template. Our Spire.Doc provides also the function NestedMailMerge with which you can merge the main-table and sub-table into a Word template to get a professional report. You can get a full demo from the article How to Use Mail Merge to Create Report

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class MailMargeDemo {

     public void mailMerge(String docFile, String resultFilePath) throws Exception {
        Document doc = new Document();
        doc.loadFromFile(docFile);

        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar=Calendar.getInstance();

        ArrayList dateList=new ArrayList<>();
        int []offset={-5,5,6,-2,2};
        for(int data:offset){
            calendar.setTime(new Date());
            calendar.add(Calendar.YEAR,data);
            dateList.add(format.format(calendar.getTime()));
        }
        String[] values =new String[dateList.size()];
        dateList.toArray(values);
        
        String []fields={
                "SubGrantPAStartDateValue",
                "SubGrantPAEndDateValue",
                "SubGrantPAExtensionDateValue",
                "SubGrantPSStartDateValue",
                "SubGrantPSEndDateValue"
        };
        doc.getMailMerge().execute(fields,values);
        doc.saveToFile(resultFilePath, FileFormat.Docx);
    }
}

using System;
using Spire.Doc;

namespace DemoOnlineCode
{
    class MailMerge
    {
        public void demoMailMerge(String docFile)
        {
            Document document = new Document(docFile, FileFormat.Auto);

            string[] values = {
                                  DateTime.Today.AddYears(-5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(6).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(-2).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(2).ToString("yyyy-MM-dd")
                              };
            string[] fields = {
                                   "SubGrantPAStartDateValue",
                                   "SubGrantPAEndDateValue",
                                   "SubGrantPAExtensionDateValue",
                                   "SubGrantPSStartDateValue",
                                   "SubGrantPSEndDateValue"
                                 };
            document.MailMerge.Execute(fields, values);
            document.SaveToFile("demo.doc", FileFormat.Doc);

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples

Thursday, 19 June 2014 01:48

Table

Data

Name	Capital	Continent	Area	Population	Flag
Argentina	Buenos Aires	South America	2777815	32300003
Bolivia	La Paz	South America	1098575	7300000
Brazil	Brasilia	South America	8511196	150400000
Canada	Ottawa	North America	9976147	26500000
Chile	Santiago	South America	756943	13200000
Colombia	Bagota	South America	1138907	33000000
Cuba	Havana	North America	114524	10600000
Ecuador	Quito	South America	455502	10600000
El Salvador	San Salvador	North America	20865	5300000
Guyana	Georgetown	South America	214969	800000

Option

Header Back Color:
Row Back Color:
Alternation Back Color:
Border Color:

downloads

Demo
Java
C# source

This demo shows you how to create a table with specified data in a Word document. We also show you how to set the border and background color of the table.

import com.spire.doc.Document;
import com.spire.doc.Section;
import com.spire.doc.Table;
import com.spire.doc.TableRow;
import com.spire.doc.documents.*;
import com.spire.doc.fields.TextRange;
import com.spire.doc.formatting.CellFormat;

import java.awt.*;
import java.util.Base64;
import java.util.List;


public class TableDemo {

    public static String []headerName={"Name","Capital","Continent","Area","Population"};
    public void addTable(String docFile, Color headerBackColor, Color rowBackColor,
                         Color alternationBackColor, Color borderColor,List dataList){
        Document doc = new Document();
        doc.loadFromFile(docFile);
        Section section = doc.getSections().get(0);
        Table table = section.addTable();
        int rowCount = dataList.size();
        table.setDefaultRowHeight(25f);
        table.setDefaultColumnWidth(0f);
        table.resetCells(rowCount + 1, headerName.length);
        //Borders
        Borders borders = table.getTableFormat().getBorders();
        //Left
        borders.getLeft().setBorderType(BorderStyle.Hairline);
        borders.getLeft().setColor(borderColor);
        //Right
        borders.getRight().setBorderType(BorderStyle.Hairline);
        borders.getRight().setColor(borderColor);
        //Bottom
        borders.getBottom().setBorderType(BorderStyle.Hairline);
        borders.getBottom().setColor(borderColor);
        //Top
        borders.getTop().setBorderType(BorderStyle.Hairline);
        borders.getTop().setColor(borderColor);
        //Horizontal
        borders.getHorizontal().setBorderType(BorderStyle.Hairline);
        borders.getHorizontal().setColor(borderColor);
        //Vertical
        borders.getVertical().setBorderType(BorderStyle.Hairline);
        borders.getVertical().setColor(borderColor);

        //Table header
        TableRow headerRow = table.getRows().get(0);
        headerRow.isHeader(true);
        for (int i = 0; i < headerName.length; i++) {
            Paragraph p = headerRow.getCells().get(i).addParagraph();
            p.getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
            TextRange headerText = p.appendText(headerName[i]);
            headerText.getCharacterFormat().setBold(true);
            CellFormat cellStyle = headerRow.getCells().get(i).getCellFormat();
            cellStyle.setVerticalAlignment(VerticalAlignment.Middle);
            cellStyle.setBackColor(headerBackColor);
        }
        //Data row
        for (int i = 0; i < rowCount; i++) {
            CountryModel countryModel = dataList.get(i);
            //Country name
            Paragraph p=table.getRows().get(i+1).getCells().get(0).addParagraph();
            p.appendText(countryModel.getName());
            //Capital
            p=table.getRows().get(i+1).getCells().get(1).addParagraph();
            p.appendText(countryModel.getCapital());
            //Continent
            p=table.getRows().get(i+1).getCells().get(2).addParagraph();
            p.appendText(countryModel.getContinent());
            //Area
            p=table.getRows().get(i+1).getCells().get(3).addParagraph();
            p.appendText(String.valueOf(countryModel.getArea()));
            //Population
            p=table.getRows().get(i+1).getCells().get(4).addParagraph();
            p.appendText(String.valueOf(countryModel.getPopulation()));
            //Flag image
            p.getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
            byte[] imgData = Base64.getDecoder().decode(String.valueOf(countryModel.getFlag()));
            p.appendPicture(imgData);
        }
        //Set cell styles
         for (int i = 0; i < rowCount; i++) {
            for(int j = 0; j < headerName.length; j++){
                CellFormat cellFormat = table.getRows().get(i + 1).getCells().get(j).getCellFormat();
                cellFormat.setVerticalAlignment(VerticalAlignment.Middle);
                cellFormat.setBackColor(rowBackColor);

                if (alternationBackColor != null && i % 2 == 1) {
                    cellFormat.setBackColor(alternationBackColor);
                }
            }
        }
    }
}

class CountryModel {
    private String name;
    private String capital;
    private String continent;
    private int area;
    private long population;
    /**
     * base64 image data
     */
    private String flag;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCapital() {
        return capital;
    }

    public void setCapital(String capital) {
        this.capital = capital;
    }

    public String getContinent() {
        return continent;
    }

    public void setContinent(String continent) {
        this.continent = continent;
    }

    public int getArea() {
        return area;
    }

    public void setArea(int area) {
        this.area = area;
    }

    public long getPopulation() {
        return population;
    }

    public void setPopulation(long population) {
        this.population = population;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }
}

using System;
using System.Data;
using System.Drawing;

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Formatting;


namespace DemoOnlineCode
{
    class Table
    {
        public void demoTable(String docFile, DataTable dataTable,
            string borderColor = null,
            string headerBackColor = null,
            string rowBackColor = null,
            string alternationRowColor = null)
        {
            Document document = new Document(docFile, FileFormat.Auto);
            addTable(document.Sections[0], dataTable,
               ConvertStrToColor(borderColor),
               ConvertStrToColor(headerBackColor),
               ConvertStrToColor(rowBackColor),
               ConvertStrToColor(alternationRowColor));
            document.SaveToFile("demo.doc", FileFormat.Doc);
        }

        private void addTable(Section section,
            DataTable dataTable,
            Color borderColor,
            Color headerBackColor,
            Color rowBackColor,
            Color alternationRowColor)
        {
            Spire.Doc.Table table = section.AddTable();
            int rowCount = dataTable.Rows.Count;
            int columnCount = dataTable.Columns.Count;
            table.DefaultRowHeight = 25;
            table.DefaultColumnWidth = 0;
            table.ResetCells(rowCount + 1, columnCount);

            table.TableFormat.Borders.Left.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Left.Color = borderColor;

            table.TableFormat.Borders.Top.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Top.Color = borderColor;

            table.TableFormat.Borders.Right.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Right.Color = borderColor;

            table.TableFormat.Borders.Bottom.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Bottom.Color = borderColor;

            table.TableFormat.Borders.Horizontal.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Horizontal.Color = borderColor;

            table.TableFormat.Borders.Vertical.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Vertical.Color = borderColor;


            TableRow headerRow = table.Rows[0];
            headerRow.IsHeader = true;

            for (int c = 0; c < columnCount; c++)
            {
                Paragraph p = headerRow.Cells[c].AddParagraph();
                p.Format.HorizontalAlignment = HorizontalAlignment.Center;

                Spire.Doc.Fields.TextRange headerText = p.AppendText(dataTable.Columns[c].ColumnName);
                headerText.CharacterFormat.Bold = true;

                CellFormat cellStyle = headerRow.Cells[c].CellFormat;
                cellStyle.VerticalAlignment = VerticalAlignment.Middle;
                headerRow.Cells[c].CellFormat.BackColor = headerBackColor;
            }

            for (int i = 0; i < rowCount; i++)
            {
                object[] rowContent = dataTable.Rows[i].ItemArray;
                DataRow row = dataTable.Rows[i];

                for (int j = 0; j < columnCount; j++)
                {
                    Paragraph p = table.Rows[i + 1].Cells[j].AddParagraph();

                    if (rowContent[j] is byte[])
                    {
                        p.Format.HorizontalAlignment = HorizontalAlignment.Center;
                        p.AppendPicture(rowContent[j] as byte[]);
                    }
                    else
                    {
                        p.AppendText(rowContent[j].ToString());
                    }

                    CellFormat cellStyle = table.Rows[i + 1].Cells[j].CellFormat;
                    cellStyle.VerticalAlignment = VerticalAlignment.Middle;

                    cellStyle.BackColor = rowBackColor;
                    if (i % 2 == 1 && alternationRowColor != Color.Empty)
                    {
                        cellStyle.BackColor = alternationRowColor;
                    }
                }
            }
        }

        private Color ConvertStrToColor(string strColor)
        {
            if (String.IsNullOrWhiteSpace(strColor))
            {
                return Color.Empty;
            }
            else
            {
                return ColorTranslator.FromHtml("#" + strColor);
            }
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples

Thursday, 19 June 2014 01:42

HeaderAndFooter

Word Document

Maximum file size: 1 MB. Files accepted: doc, docx, txt, rtf.

Click here to browse files.

fileerrors

Page Size

Source file:

filename

Target file type:

Page Header

Header Text:	Header Image:	Click here to browse files CustomValidator
Header Font:	Font Size:	The font size must be a valid number. The font size must be a valid number.
Color:	Alignment :

Page Footer

Footer Text:	Footer Image:	Click here to browse files CustomValidator
Footer Font:	Font Size:	The font size must be a valid number. The font size must be a valid number.
Color:	Alignment:

Demo
Java
C# source

This demo shows you how to set the page size, page header and footer of the Word document. We show you also set some picture as the background of the header/footer.

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.DocPicture;
import com.spire.doc.fields.TextRange;

import java.awt.*;
import java.awt.geom.Dimension2D;
import java.io.IOException;


public class AddHeaderAndFooterDemo {

    public void addHeaderAndFooter(String docFile,String headerImageFile, String footerImageFile, String headerColor, String footerColor, String resultFilePath) throws IOException {
        Document document = new Document();
        document.loadFromFile(docFile,FileFormat.Auto);
        pageSet(document,PageSize.A4);
        setHeader(document, String2Color(headerColor),headerImageFile);
        setFooter(document,String2Color(footerColor),footerImageFile);
        document.saveToFile(resultFilePath,FileFormat.Doc);
    }

    private void pageSet(Document document, Dimension2D pageSize){
        for (int i = 0; i < document.getSections().getCount(); i++) {
            //Page size
            Section section = document.getSections().get(i);
            section.getPageSetup().setPageSize(pageSize);
            section.getPageSetup().getMargins().setTop(72f);
            section.getPageSetup().getMargins().setBottom(72f);
            section.getPageSetup().getMargins().setLeft(89f);
            section.getPageSetup().getMargins().setRight(89f);
        }
    }

    private void setFooter(Document document,Color color,String footerImageFile ) throws IOException {
        String footerText = "Spire.Doc";
        String fontName = "Calibri";
        float fontSize = (float)12.0;
        for (int i = 0; i < document.getSections().getCount(); i++) {
            Section section = document.getSections().get(i);
            HeaderFooter footer = section.getHeadersFooters().getFooter();
            //Footer
            Paragraph footerParagraph = footer.addParagraph();
            DocPicture footerPicture = footerParagraph.appendPicture(footerImageFile);
            footerPicture.setTextWrappingStyle(TextWrappingStyle.Behind);
            footerPicture.setHorizontalOrigin(HorizontalOrigin.Page);
            footerPicture.setHorizontalAlignment(ShapeHorizontalAlignment.Center);
            footerPicture.setVerticalOrigin(VerticalOrigin.Page);
            footerPicture.setVerticalAlignment(ShapeVerticalAlignment.Bottom);
            //Footer text
            TextRange text = footerParagraph.appendText(footerText);
            text.getCharacterFormat().setFontName(fontName);
            text.getCharacterFormat().setFontSize(fontSize);
            text.getCharacterFormat().setTextColor(color);
            footerParagraph.getFormat().setHorizontalAlignment(HorizontalAlignment.Left);
            //border
            footerParagraph.getFormat().getBorders().getBottom().setBorderType(BorderStyle.Hairline);
            footerParagraph.getFormat().getBorders().getBottom().setSpace(0.03f);

            //Insert page number
            footerParagraph = footer.addParagraph();
            footerParagraph.appendField("page number", FieldType.Field_Page);
            footerParagraph.appendText("of");
            footerParagraph.appendField("number of pages", FieldType.Field_Num_Pages);
            footerParagraph.getFormat().setHorizontalAlignment(HorizontalAlignment.Right);
        }
    }

    private void setHeader(Document document, Color color, String headerImageFile){
        String headerText = "E-iceblue";
        String fontName = "Calibri";
        float fontSize = (float)12.0;
        for (int i = 0; i < document.getSections().getCount(); i++) {
            Section section = document.getSections().get(i);
            HeaderFooter header = section.getHeadersFooters().getHeader();
            Paragraph headerParagraph = header.addParagraph();
            DocPicture headerPicture = headerParagraph.appendPicture(headerImageFile);
            headerPicture.setTextWrappingStyle(TextWrappingStyle.Behind);
            headerPicture.setHorizontalOrigin(HorizontalOrigin.Page);
            headerPicture.setHorizontalAlignment(ShapeHorizontalAlignment.Center);
            headerPicture.setVerticalOrigin(VerticalOrigin.Page);
            headerPicture.setVerticalAlignment(ShapeVerticalAlignment.Top);
            //Header text
            TextRange text = headerParagraph.appendText(headerText);
            text.getCharacterFormat().setFontName(fontName);
            text.getCharacterFormat().setFontSize(fontSize);
            text.getCharacterFormat().setTextColor(color);
            headerParagraph.getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
            //border
            headerParagraph.getFormat().getBorders().getBottom().setBorderType(BorderStyle.Hairline);
            headerParagraph.getFormat().getBorders().getBottom().setSpace(0.03f);
        }
    }

    public static Color String2Color(String str) {
        int rgb = Integer.parseInt(str.substring(1), 16);
        Color color = null;
        try {
            color = new Color(rgb);
            return color;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

using System;
using System.Drawing;

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;

namespace DemoOnlineCode
{
    class HeaderAndFooter
    {
        public void demoHeaderAndFooter(String docFile,
            String headerImageFile = null,
            String footerImageFile = null,
            string pageSizeName = "A4",
            String headerFontColor = null,
            String footerFontColor = null)
        {
            Document document = new Document(docFile, FileFormat.Auto);
            SizeF pageSize = (SizeF)typeof(PageSize).GetField(pageSizeName).GetValue(null);
            PageSetup(document, pageSize);
            SetHeader(document, ConvertStrToColor(headerFontColor), headerImageFile);
            SetFooter(document, ConvertStrToColor(footerFontColor), footerImageFile);
            document.SaveToFile("demo.docx", FileFormat.Docx);

        }

        private void PageSetup(Document document, SizeF pageSize)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                section.PageSetup.PageSize = pageSize;
                section.PageSetup.Margins.Top = (float)72.0;
                section.PageSetup.Margins.Bottom = (float)72.0;
                section.PageSetup.Margins.Left = (float)89.0;
                section.PageSetup.Margins.Right = (float)89.0;
            }
        }

        private void SetFooter(Document document,
            Color fontColor,
            string footerImageFile = null,
            string footerText = "Spire.Doc",
            string fontName = "Calibri",
            float fontSize = (float)12.0,
            HorizontalAlignment alignment = HorizontalAlignment.Left)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                HeaderFooter footer = section.HeadersFooters.Footer;
                Paragraph footerParagraph = footer.AddParagraph();
                TextRange text = footerParagraph.AppendText(footerText);

                text.CharacterFormat.FontName = fontName;
                text.CharacterFormat.FontSize = fontSize;
                text.CharacterFormat.TextColor = fontColor;
                footerParagraph.Format.HorizontalAlignment = alignment;

                //border
                footerParagraph.Format.Borders.Top.BorderType
                    = (BorderStyle)Spire.Doc.Documents.BorderStyle.Hairline;
                footerParagraph.Format.Borders.Top.Space = (float)0.03;
                if (footerImageFile != null)
                {
                    Byte[] imagedata = System.IO.File.ReadAllBytes(footerImageFile);
                    //insert picture to footer
                    DocPicture footerPicture = footerParagraph.AppendPicture(imagedata);

                    //footer picture layout
                    footerPicture.TextWrappingStyle = TextWrappingStyle.Behind;
                    footerPicture.HorizontalOrigin = HorizontalOrigin.Page;
                    footerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left;
                    footerPicture.VerticalOrigin = VerticalOrigin.Page;
                    footerPicture.VerticalAlignment = ShapeVerticalAlignment.Bottom;

                }
                //insert page number
                footerParagraph = footer.AddParagraph();
                footerParagraph.AppendField("page number", FieldType.FieldPage);
                footerParagraph.AppendText("of");
                footerParagraph.AppendField("number of pages", FieldType.FieldNumPages);
                footerParagraph.Format.HorizontalAlignment
                    = (HorizontalAlignment)HorizontalAlignment.Right;
            }
        }

        private void SetHeader(Document document,
            Color fontColor,
            string headerImageFile = null,
            string headerText = "E-iceblue",
            string fontName = "Calibri",
            float fontSize = (float)12.0,
            HorizontalAlignment alignment = HorizontalAlignment.Center)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                HeaderFooter header = section.HeadersFooters.Header;

                //insert picture and text to header
                Paragraph headerParagraph = header.AddParagraph();
                if (headerImageFile != null)
                {
                    Byte[] imagedata = System.IO.File.ReadAllBytes(headerImageFile);
                    DocPicture headerPicture = headerParagraph.AppendPicture(imagedata);

                    //header picture layout - text wrapping
                    headerPicture.TextWrappingStyle = TextWrappingStyle.Behind;

                    //header picture layout - position
                    headerPicture.HorizontalOrigin = HorizontalOrigin.Page;
                    headerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left;
                    headerPicture.VerticalOrigin = VerticalOrigin.Page;
                    headerPicture.VerticalAlignment = ShapeVerticalAlignment.Top;
                }

                //header text
                TextRange text = headerParagraph.AppendText(headerText);
                text.CharacterFormat.FontName = fontName;
                text.CharacterFormat.FontSize = Convert.ToSingle(fontSize);
                text.CharacterFormat.TextColor = fontColor;
                headerParagraph.Format.HorizontalAlignment = alignment;

                //border
                headerParagraph.Format.Borders.Bottom.BorderType
                    = (Spire.Doc.Documents.BorderStyle)Spire.Doc.Documents.BorderStyle.Hairline;
                headerParagraph.Format.Borders.Bottom.Space = (float)0.03;

            }
        }

        private Color ConvertStrToColor(string strColor)
        {
            if (String.IsNullOrWhiteSpace(strColor))
            {
                return Color.Empty;
            }
            else
            {
                return ColorTranslator.FromHtml("#" + strColor);
            }
        }

    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples

Thursday, 19 June 2014 01:36

FindAndHighlight

Upload

Maximum file size: 1 MB. Files accepted: doc, docx, txt, rtf.

Click here to browse files.

fileerrors

Convert to

Source file:

filename

Search Text:

Demo
Java
C# source

This demo shows you how to search text in a Word document and highlight the text matched.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.documents.TextSelection;

import java.awt.*;


public class FindHighlightDemo {
    public void findHeighlight(String docFile, String findText, String resultFilePath){
        Document doc = new Document();
        doc.loadFromFile(docFile);
        TextSelection[] textSelections = doc.findAllString(findText, false, true);
		if(textSelections!=null){
			for (TextSelection selection : textSelections) {
				selection.getAsOneRange().getCharacterFormat().setHighlightColor(Color.YELLOW);
			}
		}
        doc.saveToFile(resultFilePath, FileFormat.Docx);
    }
}

using System;
using System.Drawing;
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;

namespace DemoOnlineCode
{
    class FindAndHighlight
    {
        public void demoFindAndHighlight(String docFile, String searchText)
        {
            Document document = new Document(docFile, FileFormat.Auto);

            TextSelection[] textSelections
                = document.FindAllString(searchText, false, false);
            if (textSelections != null)
            {
                foreach (TextSelection selection in textSelections)
                {
                    TextRange textMatched = selection.GetAsOneRange();
                    textMatched.CharacterFormat.HighlightColor = Color.Yellow;
                }
            }

            document.SaveToFile("demo.docx", FileFormat.Docx);
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples

Thursday, 19 June 2014 01:33

Conversion

Upload

Maximum file size: 1 MB. Files accepted: doc, docx, txt, rtf.

Click here to browse files.

fileerrors

Convert to

Source file:

filename

Target file type:

Demo
Java
C# source

This demo shows you how to convert a Word document (doc/docx) to PDF, HTML, Image, XPS, RTF and other file format.

import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import com.spire.doc.documents.ImageType;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

public class ConvertDemo {

    public void convertDemo(String filePath, String convertTo, String resultFileName) throws IOException {
        Document document = new Document();
        document.loadFromFile(filePath);
        convertion(document,convertTo,resultFileName);
    }

    private void convertion(Document doc, String convertTo, String resultFileName) throws IOException {
        switch (convertTo) {
            case "PDF":
                doc.saveToFile(resultFileName+".pdf", FileFormat.PDF);
                break;
            case "XPS":
                doc.saveToFile(resultFileName + ".xps", FileFormat.XPS);
                break;
            case "IMAGE":
                BufferedImage[] images = doc.saveToImages(ImageType.Bitmap);
                if (images != null && images.length > 0){
                    if (images.length == 1){
                        ImageIO.write(images[0],".PNG", new File(resultFileName+".png"));
                    }else {
                        for (int j = 0; j < images.length;j++){
                            String fileName = String.format("image-{0}.png",j);
                            ImageIO.write(images[j],".PNG",new File(fileName));
                        }
                    }
                }
                break;
            case "RTF":
                doc.saveToFile(resultFileName+".rtf", FileFormat.Rtf);
                break;
            case "TIFF":
                doc.saveToTiff(resultFileName +".tiff");
                break;
            case "HTML":
				doc.getHtmlExportOptions().setImageEmbedded(true);
				doc.getHtmlExportOptions().setCssStyleSheetType(CssStyleSheetType.Internal);
                doc.saveToFile(resultFileName+".html", FileFormat.Html);
                break;
        }
    }
}

using System;
using System.Drawing;
using System.Drawing.Imaging;

using Spire.Doc;
using Spire.Doc.Documents;

namespace DemoOnlineCode
{
    class Convertors
    {
        public void demoConversion(String docFile, string format)
        {
            Document document = new Document(docFile, FileFormat.Auto);
            ConvertFormat(document, format);
        }

        private void ConvertFormat(Document document, string format)
        {
            switch (format)
            {
                case "PDF":
                    document.SaveToFile("demo.pdf", FileFormat.PDF);
                    break;

                case "XPS":
                    document.SaveToFile("demo.xps", FileFormat.XPS);
                    break;

                case "Image":
                    Image[] Images = document.SaveToImages(ImageType.Bitmap);
                    if (Images != null && Images.Length > 0)
                    {
                        if (Images.Length == 1)
                        {
                            Images[0].Save("demo.bmp", System.Drawing.Imaging.ImageFormat.Bmp);
                        }
                        else
                        {
                            for (int i = 0; i < Images.Length; i++)
                            {
                                String fileName = String.Format("Image-{0}.png", i);
                                Images[i].Save(fileName, ImageFormat.Png);
                            }
                        }
                    }
                    break;
                case "RTF":
                    document.SaveToFile("demo.rtf", FileFormat.Rtf);
                    break;
                case "TIFF":
                    Image[] TiffImage = document.SaveToImages(ImageType.Bitmap);

                    Image result = JoinTiffImages(TiffImage, EncoderValue.CompressionLZW);
                    result.Save("demo.tiff", ImageFormat.Tiff);
                    break;
                case "HTML":
					document.HtmlExportOptions.CssStyleSheetType = CssStyleSheetType.Internal;
					document.HtmlExportOptions.ImageEmbedded = true;
                    document.SaveToFile("demo.html", FileFormat.Html);
                    break;
            }
        }

        private static Image JoinTiffImages(Image[] images, EncoderValue compressEncoder)
        {

            //use the save encoder
            Encoder enc = Encoder.SaveFlag;
            EncoderParameters ep = new EncoderParameters(2);
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
            ep.Param[1] = new EncoderParameter(Encoder.Compression, (long)compressEncoder);

            Image pages = null;
            int frame = 0;
            ImageCodecInfo info = GetEncoderInfo("image/tiff");

            foreach (Image img in images)
            {
                if (frame == 0)
                {
                    pages = img;
                    pages.Save("demo.tiff", 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) && frame != 0)
                {
                    //flush and close.
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.Flush);
                    pages.SaveAdd(ep);
                }

                frame++;
            }
            return pages;
        }

        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");
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.Doc Samples

Agreement Start Date:	You must select a date value.
Agreement End Date:	You must select a date value.
Agreement Extension Date:	You must select a date value.
Documentation Start Date:	You must select a date value.
Documentation End Date:	You must select a date value.
downloads