Thursday, 19 January 2023 08:14
C#/VB.NET: Extract Attachments from PDF
PDF attachments allow users to see more details on a particular point by visiting attachments inside the PDF. Basically, there are two types of attachments in PDF: document level attachment and annotation attachment. Below are the differences between them.
- Document Level Attachment (represented by PdfAttachment class): A file attached to a PDF at the document level won't appear on a page, but only appear in the PDF reader's "Attachments" panel.
- Annotation Attachment (represented by PdfAttachmentAnnotation class): A file that is attached to a specific position of a page. Annotation attachments are shown as a paper clip icon on the page; reviewers can double-click the icon to open the file.
In this article, you will learn how to extract these two kinds of attachments from a PDF document in C# and VB.NET using Spire.PDF for .NET.
Install Spire.PDF for .NET
To begin with, you need to add the DLL files included in the Spire.PDF for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.PDF
Extract Attachments from PDF in C# and VB.NET
The document level attachments of a PDF document can be obtained through PdfDocument.Attachments property. The following steps illustrate how to extract all document level attachments from a PDF document and save them to a local folder.
- Create a PdfDocument object.
- Load a PDF file using PdfDocument.LoadFromFile() method.
- Get the attachment collection from the document through PdfDocument.Attachments property.
- Get the data of a specific attachment through PdfAttachment.Data property.
- Write the data to a file and save to a specified folder.
- C#
- VB.NET
using Spire.Pdf;
using Spire.Pdf.Attachments;
using System.Net.Mail;
namespace ExtractAttachments
{
class Program
{
static void Main(string[] args)
{
//Create a PdfDocument object
PdfDocument doc = new PdfDocument();
//Load a PDF file that contains attachments
doc.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Attachments.pdf");
//Get the attachment collection of the PDF document
PdfAttachmentCollection attachments = doc.Attachments;
//Specific output folder path
string outputFolder = "C:\\Users\\Administrator\\Desktop\\output\\";
//Loop through the collection
for (int i = 0; i < attachments.Count; i++)
{
//Write attachment to a file
File.WriteAllBytes(outputFolder + attachments[i].FileName, attachments[i].Data);
}
}
}
}
Extract Annotation Attachments from PDF in C# and VB.NET
Annotation attachment is a page-based element. To get annotations from a specific page, use PdfPageBase.AnnotationsWidget property. After that, you’ll need to determine if a specific annotation is an annotation attachment. The follows are the steps to extract annotation attachments from a PDF document and save them to a local folder.
- Create a PdfDocument object.
- Load a PDF file using PdfDocument.LoadFromFile() method.
- Get a specific page from the document through PdfDocument.Pages[] property.
- Get the annotation collection from the page through PdfPageBase.AnnotationsWidget property.
- Determine if a specific annotation is an instance of PdfAttachmentAnnotationWidget. If yes, write the annotation attachment to a file and save it to a specified folder.
- C#
- VB.NET
using Spire.Pdf;
using Spire.Pdf.Annotations;
namespace ExtractAnnotationAttachments
{
class Program
{
static void Main(string[] args)
{
//Create a PdfDocument object
PdfDocument doc = new PdfDocument();
//Load a PDF file that contains attachments
doc.LoadFromFile("C:\\Users\\Administrator\\Desktop\\AnnotationAttachments.pdf");
//Specific output folder path
string outputFolder = "C:\\Users\\Administrator\\Desktop\\Output\\";
//Loop through the pages
for (int i = 0; i < doc.Pages.Count; i++)
{
//Get the annotation collection
PdfAnnotationCollection collection = doc.Pages[i].Annotations;
//Loop through the annotations
for (int j = 0; j < collection.Count; j++)
{
//Determine if an annotation is an instance of PdfAttachmentAnnotationWidget
if (collection[j] is PdfAttachmentAnnotationWidget)
{
//Write annotation attachment to a file
PdfAttachmentAnnotationWidget attachmentAnnotation = (PdfAttachmentAnnotationWidget)collection[j];
String fileName = Path.GetFileName(attachmentAnnotation.FileName);
File.WriteAllBytes(outputFolder + fileName, attachmentAnnotation.Data);
}
}
}
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Published in
Attachments
Tagged under
Tuesday, 13 January 2015 08:46
How to create vertical table at one side of the word document
Spire.Doc can help developers to create word table with data and format cells easily and it also supports to add text watermark into the word documents. This article will show you how to create a vertical table at one side of the word document, which looks like the vertical watermark in the word document.
Firstly, please check the effective screenshot of the vertical table at the right of the word document added by Spire.Doc:
Here comes to the steps of how to create vertical table in C#.
Step 1: Create a new document and add a section to the document.
Document document = new Document(); Section section=document.AddSection();
Step 2: Add a table with rows and columns and set the text for the table.
Table table = section.AddTable();
table.ResetCells(1, 1);
TableCell cell = table.Rows[0].Cells[0];
table.Rows[0].Height = 150;
cell.AddParagraph().AppendText("Draft copy in vertical style");
Step 3: Set the TextDirection for the table to RightToLeftRotated.
cell.CellFormat.TextDirection = TextDirection.RightToLeftRotated;
Step 4: Set the table format.
table.TableFormat.WrapTextAround = true; table.TableFormat.Positioning.VertRelationTo = VerticalRelation.Page; table.TableFormat.Positioning.HorizRelationTo = HorizontalRelation.Page; table.TableFormat.Positioning.HorizPosition = section.PageSetup.PageSize.Width- table.Width; table.TableFormat.Positioning.VertPosition = 200;
Step 5: Save the document to file.
document.SaveToFile("result.docx",FileFormat.docx2013);
Full codes in C#:
using Spire.Doc;
using Spire.Doc.Documents;
namespace CreateVerticalTable
{
class Program
{
static void Main(string[] args)
{
Document document = new Document();
Section section=document.AddSection();
Table table = section.AddTable();
table.ResetCells(1, 1);
TableCell cell = table.Rows[0].Cells[0];
table.Rows[0].Height = 150;
cell.AddParagraph().AppendText("Draft copy in vertical style");
cell.CellFormat.TextDirection = TextDirection.RightToLeftRotated;
table.Format.WrapTextAround = true;
table.Format.Positioning.VertRelationTo = VerticalRelation.Page;
table.Format.Positioning.HorizRelationTo = HorizontalRelation.Page;
table.Format.Positioning.HorizPosition = section.PageSetup.PageSize.Width - table.Width;
table.Format.Positioning.VertPosition = 200;
document.SaveToFile(""result.docx"", FileFormat.Docx2013);
}
}
}
Published in
Page Background
Tagged under
Tuesday, 04 November 2014 09:08
Insert an existing Table by cloning in C#
In some case, we need make some modifications in an existing table but don't want destroy the original data, so we would like to copy the existing table then make some changes in the new table. How could we get the copied table? The easiest method is clone. There would introduce a solution to copy table and modify some data then insert the new table after original table via Spire.Doc.
Spire.Doc for .NET, a stand-alone .NET Word component, provides a method, Table.clone() to allow users to copy an existing table.
The main steps of the solution:
Firstly: load the word document with a table.
Document doc = new Document(); doc.LoadFromFile(@"CopyTable.doc");
The original document effect screenshot:
Secondly: extract the existing table and call the table.clone () method to copy it.
Section se = doc.Sections[0]; Table original_Table =(Table) se.Tables[0]; Table copied_Table = original_Table.Clone();
Thirdly: extract the last row then traversal its cells to modify data.
string[] st = new string[] { "Guyana", "Georgetown", "South America", "214969", "800000" };
//get the last row of copied table
TableRow lastRow = copied_Table.Rows[copied_Table.Rows.Count - 1];
//change lastRow data.
lastRow.Cells[i].CellFormat.Shading.BackgroundPatternColor = Color.Gray;
for (int i = 0; i < lastRow.Cells.Count; i++)
{
lastRow.Cells[i].Paragraphs[0].Text = st[i];
}
Finally: call Section. tables.add() method to add the copied table in section and save this document.
se.Tables.Add(copied_Table);
doc.SaveToFile("result.doc", FileFormat.Doc);
The result document effect screenshot:
Full code:
using Spire.Doc;
using System.Drawing;
namespace InsertingaAnExistingTable
{
class Program
{
static void Main(string[] args)
{
//load a word document
Document doc = new Document();
doc.LoadFromFile(@"CopyTable.doc");
// extract the existing table
Section se = doc.Sections[0];
Table original_Table =(Table) se.Tables[0];
// copy the existing table to copied_Table via Table.clone()
Table copied_Table = original_Table.Clone();
string[] st = new string[] { "Guyana", "Georgetown", "South America", "214969", "800000" };
//get the last row of table
TableRow lastRow = copied_Table.Rows[copied_Table.Rows.Count - 1];
//change last row data.
lastRow.Cells[i].CellFormat.Shading.BackgroundPatternColor = Color.Gray;
for (int i = 0; i < lastRow.Cells.Count; i++)
{
lastRow.Cells[i].Paragraphs[0].Text = st[i];
}
// add copied_Table in section
se.Tables.Add(copied_Table);
doc.SaveToFile("result.doc", FileFormat.Doc);
}
}
}
Published in
Table
Tagged under
Friday, 17 October 2014 08:47
Get information of pagination in Excel document
When we print Word and PDF documents which have regular page size, we can clearly know the pagination information for Word and PDF by delimiters. Excel document is different since Excel pagination is based on its content when we print Excel document or convert to Pdf. So get Excel pagination information is important to developer. Below would introduce a solution to get pagination information in Excel document.
The solution call book.GetSplitPageInfo() method to obtain information of excel document and return this information to the List<Dictionary<int, PageColRow>> object via Spire.XLS. By the object we can get this information about: sheet count, page count and the start and end column and row of every page in excel document. Below is effect screenshots:
The main steps of the solution are:
Step 1: Create and load an excel document.
Workbook book = new Workbook(); book.LoadFromFile(@"test.xlsx");
Step 2: Call GetSplitPageInfo() method to Excel information.
List> pageInfoList = book.GetSplitPageInfo();
The full code:
[C#]
using Spire.Xls;
namespace GetPageInformation
{
class Program
{
static void Main(string[] args)
{
// create and load Excel document
Workbook book = new Workbook();
book.LoadFromFile(@"test.xlsx");
// get the Excel document information and save in pageInfoList object
var pageInfoList = book.GetSplitPageInfo();
// the sheet count of excel
int sheetCount = pageInfoList.Count;
//The page count of the first sheet
int pageCount = pageInfoList[0].Count;
book.SaveToFile("result.pdf", FileFormat.PDF);
}
}
}
[VB.NET]
Imports Spire.Xls
Module Module1
Sub Main()
'create and load Excel document
Dim book As New Workbook()
book.LoadFromFile("test.xlsx")
' get the Excel document information and save in pageInfoList object
Dim pageInfoList As List(Of Dictionary(Of Integer, PageColRow)) = book.GetSplitPageInfo()
' the sheet count of excel
Dim sheetCount As Integer = pageInfoList.Count
'The page count of the first sheet
Dim pageCount As Integer = pageInfoList(0).Count
book.SaveToFile("result.pdf", FileFormat.PDF)
End Sub
End Module
Published in
Document Operation
Tagged under
Saturday, 11 October 2014 06:28
Get alias, tag and id of content controls in a Word document in C#
Content controls provide a way for you to design documents. When you add a content control to a document, the control is identified by a border, a title, and temporary text that can provide instructions to the user. According to Microsoft, content controls mainly benefit from two features:
- Prevent users from editing or deleting protected sections of a document.
- Bind parts of a document or template to data. You can bind content controls to database fields, managed objects in the .NET Framework, XML elements that are stored in the document, and other data sources.
Therefore, it is necessary for developers to get the properties of content controls when dealing content controls at run time. This article illustrates how to get all controls and their properties including alias, id and tag via Spire.Doc.
Firstly, check the test file that contains six content controls distributed in lines and a table. By default, the border and the title of the control do not appear if we don't click the protected section.
Test File:
Main Steps:
Step 1: Create a new Word document and load the test file.
Step 2: Create two lists to store tags which are distributed in lines and a table separately. Here, each content control will be identified by tag.
Step 3: Use foreach sentence to get all tags in the Word document.
Full Code:
static void Main(string[] args)
{
using (Document document = new Document(@"..\..\TestData\test.docx"))
{
StructureTags structureTags = GetAllTags(document);
List<StructureDocumentTagInline> tagInlines = structureTags.tagInlines;
string alias = tagInlines[0].SDTProperties.Alias;
decimal id = tagInlines[0].SDTProperties.Id;
string tag = tagInlines[0].SDTProperties.Tag;
List<StructureDocumentTag> tags = structureTags.tags;
alias = tags[0].SDTProperties.Alias;
id = tags[0].SDTProperties.Id;
tag = tags[0].SDTProperties.Tag;
}
}
static StructureTags GetAllTags(Document document)
{
StructureTags structureTags = new StructureTags();
foreach (Section section in document.Sections)
{
foreach (DocumentObject obj in section.Body.ChildObjects)
{
if (obj.DocumentObjectType == DocumentObjectType.Paragraph)
{
foreach (DocumentObject pobj in (obj as Paragraph).ChildObjects)
{
if (pobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
{
structureTags.tagInlines.Add(pobj as StructureDocumentTagInline);
}
}
}
else if (obj.DocumentObjectType == DocumentObjectType.Table)
{
foreach (TableRow row in (obj as Table).Rows)
{
foreach (TableCell cell in row.Cells)
{
foreach (DocumentObject cellChild in cell.ChildObjects)
{
if (cellChild.DocumentObjectType == DocumentObjectType.StructureDocumentTag)
{
structureTags.tags.Add(cellChild as StructureDocumentTag);
}
else if (cellChild.DocumentObjectType == DocumentObjectType.Paragraph)
{
foreach (DocumentObject pobj in (cellChild as Paragraph).ChildObjects)
{
if (pobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
{
structureTags.tagInlines.Add(pobj as StructureDocumentTagInline);
}
}
}
}
}
}
}
}
}
return structureTags;
}
public class StructureTags
{
List<StructureDocumentTagInline> m_tagInlines;
public List<StructureDocumentTagInline> tagInlines
{
get
{
if (m_tagInlines == null)
m_tagInlines = new List<StructureDocumentTagInline>();
return m_tagInlines;
}
set
{
m_tagInlines = value;
}
}
List<StructureDocumentTag> m_tags;
public List<StructureDocumentTag> tags
{
get
{
if (m_tags == null)
m_tags = new List<StructureDocumentTag>();
return m_tags;
}
set
{
m_tags = value;
}
}
}
Effect Screenshot:
Content controls in lines
Content controls in table
Published in
Text
Friday, 26 September 2014 08:01
Convert XLS to XLSM and Maintain Macro in C#, VB.NET
A file with the XLSM extension is an Excel Macro-Enabled Workbook file. For security reasons, XLS file or XLSX file does not enable macros by default. Thus, if you want to execute macros in Excel file, you need to convert XLS or XLSX to XLSM at the first place. In this article, I’ll introduce you how to convert XLS to XLSM with the macro maintained using Spire.XLS.
Here is the method:
Step 1: Create a new instance of Spire.Xls.Workbook class.
Workbook workbook = new Workbook();
Step 2: Load the test file and imports its data to workbook.
workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);
Step 3: Save the workbook as a new XLSM file.
workbook.SaveToFile("result.xlsm", FileFormat.Version2007);
Full Code:
[C#]
using Spire.Xls;
namespace Convert
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);
workbook.SaveToFile("result.xlsm", FileFormat.Version2007);
}
}
}
[VB.NET]
Imports Spire.Xls
Namespace Convert
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003)
workbook.SaveToFile("result.xlsm", FileFormat.Version2007)
End Sub
End Class
End Namespace
Test File:
As is shown in the picture, Excel automatically disables macro in XLS file.
Result:
No security warning in the converted XLSM file.
Published in
Conversion
Tagged under
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);
}
}
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);
}
}
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
- 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);
}
}
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