Java: Remove Duplicate Values When Merging Cells in Word
Spire.Doc offers Table.applyVerticalMerge() method to merge table cells vertically and Table.applyHorizontalMerge() method to merge table cells horizontally. By default, the merged cells will have repeated values if the cells to be merged contain the same value. This article will demonstrate how to remove repeated values in the merged cells using a customized method with Spire.Doc for Java.
Install Spire.Doc for Java
First of all, you're required to add the Spire.Doc.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.doc</artifactId> <version>12.4.6</version> </dependency> </dependencies>
Remove Duplicate Values When Merging Cells
The following are the steps to remove the duplicate values in the merged cells in a Word table.
- Create an object of Document class and load the sample document using Document.loadFromFile() method.
- Use Document.getSections() method to get the section collection, and then get the specific section using SectionCollection.get() method.
- Use Section.getTables() method to get the table collection, and then get the desired table using TableCollection.get() method
- Invoke mergeCell(Table table, boolean isHorizontalMerge, int index, int start, int end) method to merge table cells vertically or horizontally. This method will determine whether the cells to be merged have the same value, and will preserve only one value in the merged cell.
- Save the document to file using Document.saveToFile() method.
- Java
import com.spire.doc.*; import com.spire.doc.interfaces.ITable; public class MergeCells { public static void main(String[] args) throws Exception { //Create an object of Document class and load the sample document. Document document = new Document(); document.loadFromFile("Sample.docx"); //Get the first section Section section = document.getSections().get(0); //Get the first table Table table = section.getTables().get(0); //Invoike mergeCell()method to merge cells vertically mergeCell(table, false, 0, 1, 3); //Invoike mergeCell()method to merge cell horizontally mergeCell(table, true, 0, 4, 5); //Save the document to file document.saveToFile("MergeTable.docx",FileFormat.Docx_2013); } //Customize a mergeCell() method to remove the duplicate values while merging cells public static void mergeCell(Table table, boolean isHorizontalMerge, int index, int start, int end) { if (isHorizontalMerge) { //Get a cell from table TableCell firstCell = table.get(index, start); //Invoke getCellText() method to get the cell’s text String firstCellText = getCellText(firstCell); for (int i = start + 1; i <= end; i++) { TableCell cell1 = table.get(index, i); //Check if the text is the same as the first cell if (firstCellText.equals(getCellText(cell1))) { //If yes, clear all the paragraphs in the cell cell1.getParagraphs().clear(); } } //Merge cells horizontally table.applyHorizontalMerge(index, start, end); } else { TableCell firstCell = table.get(start, index); String firstCellText = getCellText(firstCell); for (int i = start + 1; i <= end; i++) { TableCell cell1 = table.get(i, index); if (firstCellText.equals(getCellText(cell1))) { cell1.getParagraphs().clear(); } } //Merge cells vertically table.applyVerticalMerge(index, start, end); } } public static String getCellText(TableCell cell) { StringBuilder text = new StringBuilder(); //Traverse all the paragraphs of a cell for (int i = 0; i < cell.getParagraphs().getCount(); i++) { //Get every paragraph’s text and append it to StringBuilder text.append(cell.getParagraphs().get(i).getText().trim()); } return text.toString(); } }
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.
Spire.XLS 11.10.5 supports multiple new charts
We are happy to announce the release of Spire.XLS 11.10.5. This version supports some new charts, such as Waterfall, Pareto and Histogram. More details are listed below.
Here is a list of changes made in this release
Category | ID | Description |
New feature | - | Supports Waterfall, Pareto, Histogram, BoxAndWhisker, TreeMap, SunBurst and Funnel charts.
Workbook workbook = new Workbook(); workbook.LoadFromFile("waterfall_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as waterfall officeChart.ChartType = ExcelChartType.WaterFall; //Set data range to the chart from the worksheet officeChart.DataRange = sheet["A2:B8"]; //Data point settings as total in chart officeChart.Series[0].DataPoints[3].SetAsTotal = true; officeChart.Series[0].DataPoints[6].SetAsTotal = true; //Showing the connector lines between data points officeChart.Series[0].Format.ShowConnectorLines = true; //Set the chart title officeChart.ChartTitle = "Company Profit (in USD)"; //Formatting data label and legend option officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = true; officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8; officeChart.Legend.Position = LegendPositionType.Right; workbook.SaveToFile("waterfall_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("Pareto_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as Pareto officeChart.ChartType = ExcelChartType.Pareto; //Set data range in the worksheet officeChart.DataRange = sheet["A2:B8"]; //Set category values as bin values officeChart.PrimaryCategoryAxis.IsBinningByCategory = true; officeChart.PrimaryCategoryAxis.OverflowBinValue = 5; officeChart.PrimaryCategoryAxis.UnderflowBinValue = 1; //Formatting Pareto line officeChart.Series[0].ParetoLineFormat.LineProperties.Color = System.Drawing.Color.Blue; //Gap width settings officeChart.Series[0].DataFormat.Options.GapWidth = 6; //Set the chart title officeChart.ChartTitle = "Expenses"; //Hiding the legend officeChart.HasLegend = false; workbook.SaveToFile("Pareto_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("Histogram_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as histogram officeChart.ChartType = ExcelChartType.Histogram; //Set data range in the worksheet officeChart.DataRange = sheet["A1:A15"]; //Category axis bin settings officeChart.PrimaryCategoryAxis.BinWidth = 8; //Gap width settings officeChart.Series[0].DataFormat.Options.GapWidth = 6; //Set the chart title and axis title officeChart.ChartTitle = "Height Data"; officeChart.PrimaryValueAxis.Title = "Number of students"; officeChart.PrimaryCategoryAxis.Title = "Height"; //Hiding the legend officeChart.HasLegend = false; workbook.SaveToFile("Histogram_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("Boxandwhisker_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set the chart title officeChart.ChartTitle = "Yearly Vehicle Sales"; //Set chart type as Box and Whisker officeChart.ChartType = ExcelChartType.BoxAndWhisker; //Set data range in the worksheet officeChart.DataRange = sheet["A1:E17"]; //Box and Whisker settings on first series var seriesA = officeChart.Series[0]; seriesA.DataFormat.ShowInnerPoints = false; seriesA.DataFormat.ShowOutlierPoints = true; seriesA.DataFormat.ShowMeanMarkers = true; seriesA.DataFormat.ShowMeanLine = false; seriesA.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian; //Box and Whisker settings on second series var seriesB = officeChart.Series[1]; seriesB.DataFormat.ShowInnerPoints = false; seriesB.DataFormat.ShowOutlierPoints = true; seriesB.DataFormat.ShowMeanMarkers = true; seriesB.DataFormat.ShowMeanLine = false; seriesB.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.InclusiveMedian; //Box and Whisker settings on third series var seriesC = officeChart.Series[2]; seriesC.DataFormat.ShowInnerPoints = false; seriesC.DataFormat.ShowOutlierPoints = true; seriesC.DataFormat.ShowMeanMarkers = true; seriesC.DataFormat.ShowMeanLine = false; seriesC.DataFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian; workbook.SaveToFile("Boxandwhisker_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("treemap_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as TreeMap officeChart.ChartType = ExcelChartType.TreeMap; //Set data range in the worksheet officeChart.DataRange = sheet["A2:C11"]; //Set the chart title officeChart.ChartTitle = "Area by countries"; //Set the Treemap label option officeChart.Series[0].DataFormat.TreeMapLabelOption = ExcelTreeMapLabelOption.Banner; //Formatting data labels officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8; workbook.SaveToFile("treemap_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("Sunburst_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as Sunburst officeChart.ChartType = ExcelChartType.SunBurst; //Set data range in the worksheet officeChart.DataRange = sheet["A1:D16"]; //Set the chart title officeChart.ChartTitle = "Sales by annual"; //Formatting data labels officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8; //Hiding the legend officeChart.HasLegend = false; workbook.SaveToFile("Sunburst_chart.xlsx"); Workbook workbook = new Workbook(); workbook.LoadFromFile("Funnel_sample.xlsx"); var sheet = workbook.Worksheets[0]; var officeChart = sheet.Charts.Add(); //Set chart type as Funnel officeChart.ChartType = ExcelChartType.Funnel; //Set data range in the worksheet officeChart.DataRange = sheet.Range["A1:B6"]; //Set the chart title officeChart.ChartTitle = "Funnel"; //Formatting the legend and data label option officeChart.HasLegend = false; officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = true; officeChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8; workbook.SaveToFile("Funnel_chart.xlsx"); |
Java: Add Data Bars in Excel
Data bars are a type of conditional formatting Microsoft Excel offers for visualizing the values in Excel cells. They can help you compare the values quickly because a cell with a longer bar represents a larger value, while a cell with a shorter bar represents a smaller value. This article will introduce how to add data bars in a range of cells using Spire.XLS for Java.
Install Spire.XLS for Java
First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls</artifactId> <version>14.4.1</version> </dependency> </dependencies>
Add Data Bars in Excel
The following are steps to add data bars in a range of Excel cells through conditional formatting:
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the worksheets collection using Workbook.getWorksheets() method, and then get the first worksheet using WorksheetsCollection.get() method.
- Get a specific cell range using Worksheet.getCellRange() method.
- Add a new conditional formatting to the cell range using ConditionalFormats. addCondition(), and then set the type of the new conditional formatting to DataBar using ConditionalFormatWrapper.setFormatType() method.
- Set the color of the data bar using DataBar.setBarColor() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*; import com.spire.xls.core.*; import java.awt.*; public class applyDataBars { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\test.xlsx"); //Get the first worksheet. Worksheet sheet = workbook.getWorksheets().get(0); //Get the specific cell range CellRange range = sheet.getCellRange("B2:B13"); //Add the conditional formatting of data bars in the cell range IConditionalFormat format = range.getConditionalFormats().addCondition(); format.setFormatType( ConditionalFormatType.DataBar); //Set color for the data bars format.getDataBar().setBarColor( Color.GREEN); //Save to file workbook.saveToFile("ApplyDataBars.xlsx", ExcelVersion.Version2013); } }
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.
Spire.Presentation 6.10.2 supports getting the target slide of a hyperlink
We are glad to announce the release of Spire.Presentation 6.10.2. This version supports getting the target slide of a hyperlink. In addition, it enhances the conversion from shape to image and fixes some known issues. More details are listed below.
Here is a list of changes made in this release
Category | ID | Description |
New feature | SPIREPPT-1698 | Supports getting the target slide of a hyperlink.
Presentation ppt = new Presentation(); ppt.LoadFromFile(inputFile); IAutoShape shape = ppt.Slides[1].Shapes[0] as IAutoShape; if (shape.Click.ActionType == HyperlinkActionType.GotoSlide) { ISlide targetSlide = shape.Click.TargetSlide; Console.WriteLine("index = " + targetSlide.SlideNumber); } |
Bug | SPIREPPT-1684 | Fixes the issue that the OLE object couldn't be opened after loading and saving the file. |
Bug | SPIREPPT-1700 | Fixes the issue that the content was cut off after converting shape to images. |
Bug | SPIREPPT-1702 | Fixes the issue that it didn't get the correct text color. |
Bug | SPIREPPT-1703 | Fixes the issue that it didn't get the correct font size of the table text. |
Bug | SPIREPPT-1704 | Fixes the issue that it didn't get the correct fill color for the chart series. |
Bug | SPIREPPT-1705 | Fixes the issue that it didn't get the correct duration for the slide show transition. |
Bug | SPIREPPT-1706 | Fixes the issue that part of the content was missing after converting equations to images. |
Spire.Doc 9.10.9 enhances the conversion from Word to PDF
We are happy to announce the release of Spire.Doc 9.10.9. This version enhances the conversion from Word to PDF and also fixes the issues occurred in the course of loading HTML and Word files. More details are listed below.
Here is a list of changes made in this release
Category | ID | Description |
Bug | SPIREDOC-5938 | Fixes the issue that it didn't find all matches when using regular expression to find text. |
Bug | SPIREDOC-6435 | Fixes the issue that the application threw the error "ArgumentOutOfRangeException" when loading an HTML file. |
Bug | SPIREDOC-6545 SPIREDOC-6785 |
Fixes the issue that there were extra blank pages after converting Word to PDF. |
Bug | SPIREDOC-6597 | Fixes the issue that line breaks were incorrect after converting Word to PDF. |
Bug | SPIREDOC-6635 | Fixes the issue that the image position was changed after converting Word to PDF. |
Bug | SPIREDOC-6710 | Fixes the issue that the text overlapped after converting Word to PDF. |
Bug | SPIREDOC-6718 | Fixes the issue that the application threw the error "Collection was modified; enumeration operation may not execute" when comparing two Word documents. |
Bug | SPIREDOC-6746 SPIREDOC-6772 SPIREDOC-6820 |
Fixes the issue that the application threw the error "Object reference not set to an instance of an object" when converting Word to PDF. |
Bug | SPIREDOC-6771 SPIREDOC-6793 |
Fixes the issue that the application threw the error "Object reference not set to an instance of an object" when getting MergeGroupNames. |
Bug | SPIREDOC-6773 | Fixes the issue that the Thai characters were garbled after converting Word to PDF. |
Bug | SPIREDOC-6774 | Fixes the issue that the application threw the error "Zip exception" when loading a Word document. |
Spire.Office for Java 4.10.5 is released
We are excited to announce the release of Spire.Office for Java 4.10.5. This version brings some new features. For instance, Spire.XLS for Java supports setting the marker color for the SparkLine as well as supports printing comments when converting Excel to images; Spire.Doc for Java supports loading and saving .wps and .wpt files; Spire.Presentation for Java enhances the conversion from PowerPoint to images. Meanwhile, a lot of bugs have been successfully fixed. More details are listed as follow.
Here is a list of changes made in this release
Spire.XLS for Java
Category | ID | Description |
New feature | SPIREXLS-3391 | Supports setting the marker color for the SparkLine.
Workbook book = new Workbook(); book.loadFromFile("D:/SparkLine.xlsx"); Worksheet worksheet = book.getWorksheets().get(0); SparklineGroupCollection sparklineGroup = worksheet.getSparklineGroups(); sparklineGroup.get(1).setMarkersColor(Color.YELLOW); book.saveToFile("D:/result.xlsx", FileFormat.Version2016); |
New feature | SPIREXLS-3424 | Supports printing comments when converting Excel to images.
worksheet.getPageSetup().setPrintComments(PrintCommentType.InPlace); |
New feature | SPIREXLS-3451 | Supports loading and saving .et/.ett files.
Workbook workbook = new Workbook(); workbook.loadFromFile(inputFile); workbook.saveToFile(etFile, FileFormat.ET); workbook.saveToFile(ettFile, FileFormat.ETT); |
Bug | SPIREXLS-3334 | Fixes the issue that the application threw the error "NullPointerException" when converting Excel to PDF. |
Bug | SPIREXLS-3388 | Fixes the issue that the application threw the error "Invalid MsoLineDashStyle string val" when converting Excel to PDF. |
Bug | SPIREXLS-3394 | Fixes the issue that part of the cell borders was not displayed after adding a hyperlink. |
Bug | SPIREXLS-3439 | Fixes the issue that the content was incorrect after converting Excel to HTML. |
Bug | SPIREXLS-3446 | Fixes the issue that it returned the wrong count when getting the cell count of the named ranges. |
Bug | SPIREXLS-3447 | Fixes the issue that it returned the wrong value when checking if a named range was visible. |
Bug | SPIREXLS-3448 | Fixes the issue that link address was incorrect after converting Excel to HTML. |
Bug | SPIREXLS-3454 | Fixes the issue that the application threw the error "Exception in thread" when creating Workbook objects using multiple threads. |
Bug | SPIREXLS-3462 | Fixes the issue that the application threw the error "NullPointerException" when getting the chart title. |
Bug | SPIREXLS-3479 | Fixes the issue that the content was incorrect after converting Excel to PDF. |
Bug | SPIREXLS-3490 | Fixes the issue that the date format was incorrect after converting Excel to PDF. |
Spire.Doc for Java
Category | ID | Description |
New feature | SPIREDOC-6743 | Supports loading and saving .wps and .wpt files.
Document document = new Document(); document.loadFromFile(inputFile, FileFormat.WPS); //document.loadFromFile(inputFile, FileFormat.WPT); document.saveToFile(outputFile, FileFormat.WPS); //document.saveToFile(outputFile, FileFormat.WPT); |
Bug | SPIREDOC-5839 | Fixes the issue that the line break was incorrect after converting Word to PDF. |
Bug | SPIREDOC-6107 SPIREDOC-6572 SPIREDOC-6671 SPIREDOC-6715 |
Fixes the issue that the comparison result was incorrect after comparing two Word documents. |
Bug | SPIREDOC-5768 SPIREDOC-5768 |
Fixes the issue that the text across paragraphs could not be found when using regular expressions. |
Bug | SPIREDOC-6171 | Fixes the issue that the charts were not rendered correctly after converting Word to PDF. |
Bug | SPIREDOC-6379 SPIREDOC-6745 |
Fixes the issue that the field was not updated correctly after mail merge. |
Bug | SPIREDOC-6391 | Fixes the issue that the image position was incorrect after converting Word to PDF. |
Bug | SPIREDOC-6428 | Fixes the issue that it failed to set the value of the DocProperty field in the header. |
Bug | SPIREDOC-6462 SPIREDOC-6559 SPIREDOC-6620 |
Fixes the issue that the content was incorrect after converting Word to PDF. |
Bug | SPIREDOC-6546 | Fixes the issue that table border was not displayed correctly when opening the result file in WPS. |
Bug | SPIREDOC-6552 | Fixes the issue that the changes markup was incorrect after converting Word to HTML and back to Word. |
Bug | SPIREDOC-6581 | Fixes the issue that the image was missing after converting Word to PDF. |
Bug | SPIREDOC-6583 SPIREDOC-6661 SPIREDOC-6767 SPIREDOC-6769 |
Fixes the issue that the content was incorrect after accepting the changes. |
Bug | SPIREDOC-6589 | Fixes the issue that the application threw the error "IllegalArgumentException" when loading a Word document. |
Bug | SPIREDOC-6614 | Fixes the issue that the text overlapped after converting Word to PDF. |
Bug | SPIREDOC-6631 | Fixes the issue that the application threw the error "An element with the same key already exists in the dictionary" when merging Word files. |
Bug | SPIREDOC-6647 | Fixes the issue that the application threw the error "ArrayIndexOutOfBoundsException" when accepting changes. |
Bug | SPIREDOC-6662 | Fixes the issue that the application threw the error "NullPointerException" when getting bookmarks after accepting changes. |
Bug | SPIREDOC-6663 | Fixes the issue that the table borders were missing when opening result file in WPS after converting Doc to Docx. |
Bug | SPIREDOC-6669 | Fixes the issue that the application threw the error "IllegalArgumentException" when converting Word to HTML. |
Bug | SPIREDOC-6670 | Fixes the issue that there was extra image after converting Word to HTML and back to Word. |
Bug | SPIREDOC-6672 | Fixes the issue that the result file couldn't be printed in Mac OS after converting Word to PDF. |
Bug | SPIREDOC-6702 | Fixes the issue that the application threw the error "Value was either too large or too small for an Int32" when loading a Word document. |
Bug | SPIREDOC-6703 | Fixes the issue that the table width was changed after converting Word to PDF. |
Bug | SPIREDOC-6704 | Fixes the issue that there were extra images after converting Word to PDF. |
Bug | SPIREDOC-6705 | Fixes the issue that the application threw the error "'span' is expected Line 1, position 67" when loading a Word document. |
Bug | SPIREDOC-6712 | Fixes the issue that the application threw the error "StackOverFlowError" when updating the TOC. |
Bug | SPIREDOC-6753 | Fixes the issue that the application threw the error "Input String was not in the correct format" when updating the TOC. |
Bug | SPIREDOC-6792 | Fixes the issue that the application threw the error "No have this value 104" when loading a Word document. |
Bug | SPIREDOC-6797 | Fixes the issue that the application threw the error "No have this value 5" when loading a Word document. |
Spire.Presentation for Java
Category | ID | Description |
Bug | SPIREPPT-1688 | Fixes the issue that the application threw an error “Invalid format string” when loading a PPT file. |
Bug | SPIREPPT-1689 | Fixes the issue that the application hanged when converting a PPT to images. |
Spire.XLS for Java 4.10.1 supports setting the marker color for the SparkLine
We are happy to announce the release of Spire.XLS for Java 4.10.1. This version supports setting the marker color for the SparkLine as well as supports printing comments when converting Excel to images. In addition, it enhances the conversions from Excel to PDF/HTML and also fixes the issue occurred in the process of adding hyperlinks to files. More details are given as follows.
Here is a list of changes made in this release
Category | ID | Description |
New feature | SPIREXLS-3391 | Supports setting the marker color for the SparkLine.
Workbook book = new Workbook(); book.loadFromFile("D:/SparkLine.xlsx"); Worksheet worksheet = book.getWorksheets().get(0); SparklineGroupCollection sparklineGroup = worksheet.getSparklineGroups(); sparklineGroup.get(1).setMarkersColor(Color.YELLOW); book.saveToFile("D:/result.xlsx", FileFormat.Version2016); |
New feature | SPIREXLS-3424 | Supports printing comments when converting Excel to images.
worksheet.getPageSetup().setPrintComments(PrintCommentType.InPlace); |
New feature | SPIREXLS-3451 | Supports loading and saving .et/.ett files.
Workbook workbook = new Workbook(); workbook.loadFromFile(inputFile); workbook.saveToFile(etFile, FileFormat.ET); workbook.saveToFile(ettFile, FileFormat.ETT); |
Bug | SPIREXLS-3334 | Fixes the issue that the application threw the error "NullPointerException" when converting Excel to PDF. |
Bug | SPIREXLS-3388 | Fixes the issue that the application threw the error "Invalid MsoLineDashStyle string val" when converting Excel to PDF. |
Bug | SPIREXLS-3394 | Fixes the issue that part of the cell borders was not displayed after adding a hyperlink. |
Bug | SPIREXLS-3439 | Fixes the issue that the content was incorrect after converting Excel to HTML. |
Bug | SPIREXLS-3446 | Fixes the issue that it returned the wrong count when getting the cell count of the named ranges. |
Bug | SPIREXLS-3447 | Fixes the issue that it returned the wrong value when checking if a named range was visible. |
Bug | SPIREXLS-3448 | Fixes the issue that link address was incorrect after converting Excel to HTML. |
Bug | SPIREXLS-3454 | Fixes the issue that the application threw the error "Exception in thread" when creating Workbook objects using multiple threads. |
Bug | SPIREXLS-3462 | Fixes the issue that the application threw the error "NullPointerException" when getting the chart title. |
Bug | SPIREXLS-3479 | Fixes the issue that the content was incorrect after converting Excel to PDF. |
Bug | SPIREXLS-3490 | Fixes the issue that the date format was incorrect after converting Excel to PDF. |
C#/VB.NET: Copy Cell Ranges Between Different Workbooks
When you're dealing Excel documents, it is a common task that you may need to copy data from a main workbook and paste into a separate workbook. You can copy either a selected cell range or an entire worksheet between different workbooks. This article demonstrates how to copy a selected cell range from one workbook to another by using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.
- Package Manager
PM> Install-Package Spire.XLS
Copy a Cell Range Between Different Workbooks
Spire.XLS offers the Worksheet.Copy() method to copy data from a source range to a destination range. The destination range can be a cell range inside the same workbook or from a different workbook. The following are the steps to copy a cell range from a workbook to another.
- Create a Workbook object to load the source Excel document.
- Get the source worksheet and the source cell range using Workbook.Worksheets property and Worksheet.Range property respectively.
- Create another Workbook object to load the destination Excel document.
- Get the destination worksheet and cell range.
- Copy the data from the source range to the destination range using Worksheet.Copy(CellRange source, CellRange destRange).
- Copy the column widths from the source range to the destination range, so that the data can display properly in the destination workbook.
- Save the destination workbook to an Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace CopyCellRange { class Program { static void Main(string[] args) { //Create a Workbook object Workbook sourceBook = new Workbook(); //Load the source workbook sourceBook.LoadFromFile(@"C:\Users\Administrator\Desktop\source.xlsx"); //Get the source worksheet Worksheet sourceSheet = sourceBook.Worksheets[0]; //Get the source cell range CellRange sourceRange = sourceSheet.Range["A1:E4"]; //Create another Workbook objecy Workbook destBook = new Workbook(); //Load the destination workbook destBook.LoadFromFile(@"C:\Users\Administrator\Desktop\destination.xlsx"); //Get the destination worksheet Worksheet destSheet = destBook.Worksheets[0]; //Get the destination cell range CellRange destRange = destSheet.Range["B2:F5"]; //Copy data from the source range to the destination range sourceSheet.Copy(sourceRange, destRange); //Loop through the columns in the source range for (int i = 0; i < sourceRange.Columns.Length; i++) { //Copy the column widths also from the source range to destination range destRange.Columns[i].ColumnWidth = sourceRange.Columns[i].ColumnWidth; } //Save the destination workbook to an Excel file destBook.SaveToFile("CopyRange.xlsx"); } } }
Imports Spire.Xls Namespace CopyCellRange Class Program Shared Sub Main(ByVal args() As String) 'Create a Workbook object Dim sourceBook As Workbook = New Workbook() 'Load the source workbook sourceBook.LoadFromFile("C:\Users\Administrator\Desktop\source.xlsx") 'Get the source worksheet Dim sourceSheet As Worksheet = sourceBook.Worksheets(0) 'Get the source cell range Dim sourceRange As CellRange = sourceSheet.Range("A1:E4") 'Create another Workbook objecy Dim destBook As Workbook = New Workbook() 'Load the destination workbook destBook.LoadFromFile("C:\Users\Administrator\Desktop\destination.xlsx") 'Get the destination worksheet Dim destSheet As Worksheet = destBook.Worksheets(0) 'Get the destination cell range Dim destRange As CellRange = destSheet.Range("B2:F5") 'Copy data from the source range to the destination range sourceSheet.Copy(sourceRange, destRange) 'Loop through the columns in the source range Dim i As Integer For i = 0 To sourceRange.Columns.Length- 1 Step i + 1 'Copy the column widths also from the source range to destination range destRange.Columns(i).ColumnWidth = sourceRange.Columns(i).ColumnWidth Next 'Save the destination workbook to an Excel file destBook.SaveToFile("CopyRange.xlsx") End Sub End Class End Namespace
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.
Spire.XLS 11.10.4 enhances the conversion from XLS to PDF
We are excited to announce the release of Spire.XLS 11.10.4. This version enhances the conversion from XLS to PDF. In addition, it fixes the issue that the application threw an error when loading an .xlsx file. More details are listed below.
Here is a list of changes made in this release
Category | ID | Description |
Bug | SPIREXLS-3386 SPIREXLS-3470 |
Fixes the issue that the content was incorrect when converting Excel to PDF. |
Bug | SPIREXLS-3396 | Fixes the issue that the application threw an error "Invalid LegendPositionType string val" when loading an .xlsx file. |
Bug | SPIREXLS-3422 | Fixes the issue that the combination chart type was not got incorrectly. |
Bug | SPIREXLS-3425 | Fixes the issue that the application threw the System.ArgumentOutOfRangeException when copying worksheets. |
Bug | SPIREXLS-3426 | Fixes the issue that the lines lost when converting Excel to PDF. |
Bug | SPIREXLS-3438 | Fixes the issue that the application hanged when loading an .xls file. |
Bug | SPIREXLS-3449 | Fixes the issue that setting the end of the chart's error line did not work. |
Bug | SPIREXLS-3455 SPIREXLS-3460 |
Fixes the issue that the application threw an error "Cannot read that as a ZipFile" when loading an HTML file. |
Bug | SPIREXLS-3458 SPIREXLS-3472 |
Fixes the issue that the application threw an error "You cannot Write() data for an entry that is a directory" when saving to an .xlsm file. |
Bug | SPIREXLS-3459 | Fxies the issue that the application threw an error 'Invalid MsoLineDashStyle string val' when loading an .xlsx file. |
Bug | SPIREXLS-3464 | Fixes the issue that some characters lost when converting an .xlsx to PDF. |
Bug | SPIREXLS-3471 | Fixes the issue that that the application threw an error "Object reference is not set to an instance of an object." when converting an .xlsx to PDF. |
Bug | SPIREXLS-3480 | Fixes the issue that the max rows and columns obtained were wrong after adding some new data. |
Bug | SPIREXLS-3481 | Fixes the issue that the application hanged when loading an .ods file. |
Bug | SPIREXLS-3482 | Fixes the issue that setting the transparent background color of the cell did not take effect |
Java: Convert Word to XPS, XML, RTF, TXT and SVG
In daily work, converting a Word document to other formats can be extremely frequent. For example, sometimes you may need to convert a Word document to XML to store and organize data; on some occasions, you may also need to convert Word to SVG for sharing graphics contents on the Internet. In this article, you will learn how to convert Word to XPS, XML, RTF, TXT and SVG using Spire.Doc for Java.
Install Spire.Doc for Java
First of all, you're required to add the Spire.Doc.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.doc</artifactId> <version>12.4.6</version> </dependency> </dependencies>
Convert Word to XPS, XML, RTF, TXT and SVG
The following are the main steps to convert Word to XPS, XML, RTF, TXT and SVG.
- Create a Document object.
- Load the document using Document.loadFromFile() method.
- Use Document.saveToFile() method to save the document as SVG, RTF, XPS, XML and TXT respectively.
- Java
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 ConvertWordToOtherFormats { public static void main(String[] args) throws IOException { //Create a Document object. Document doc = new Document(); //Load the Word document. doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.docx"); //Save Word as SVG. doc.saveToFile("output/ToSVG.svg",FileFormat.SVG); //Save Word as RTF. doc.saveToFile("output/ToRTF.rtf",FileFormat.Rtf); //Save Word as XPS. doc.saveToFile("output/ToXPS.xps",FileFormat.XPS); //Save Word as XML. doc.saveToFile("output/ToXML.xml",FileFormat.Xml); //Save Word as TXT. doc.saveToFile("output/ToTXT.txt",FileFormat.Txt); } }
The original Word file:
The generated XPS file:
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.