Spire.XLS (5)
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
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
- 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
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; } } }
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