public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source Excel file
Workbook workbook = new Workbook(dataDir + "source.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access the first sparkline group
SparklineGroup group = worksheet.SparklineGroupCollection[0];
// Add Data Ranges and Locations inside this sparkline group
group.SparklineCollection.Add("D5:O5", 4, 15);
group.SparklineCollection.Add("D6:O6", 5, 15);
group.SparklineCollection.Add("D7:O7", 6, 15);
group.SparklineCollection.Add("D8:O8", 7, 15);
// Save the workbook
workbook.Save(dataDir + "output_out_.xlsx");
// ExEnd:1
}
static void CustomizeSparklineAppearance(Workbook workbook)
{
#region #CustomizeSparklineAppearance
Worksheet worksheet = workbook.Worksheets["SparklineExamples"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a group of line sparklines.
SparklineGroup lineGroup = worksheet.SparklineGroups.Add(worksheet["G4:G7"], worksheet["C4:F4,C5:F5,C6:F6, C7:F7"], SparklineGroupType.Line);
// Customize the group appearance.
// Set the sparkline color.
lineGroup.SeriesColor = Color.FromArgb(0x1F, 0x49, 0x7D);
// Set the sparkline weight.
lineGroup.LineWeight = 1.5;
// Display data markers on the sparklines and specify their color.
SparklinePoints points = lineGroup.Points;
points.Markers.IsVisible = true;
points.Markers.Color = Color.FromArgb(0x4B, 0xAC, 0xC6);
// Highlight the highest and lowest points on each sparkline in the group.
points.Highest.Color = Color.FromArgb(0xA9, 0xD6, 0x4F);
points.Lowest.Color = Color.FromArgb(0x80, 0x64, 0xA2);
#endregion #CustomizeSparklineAppearance
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook from source Excel file
Workbook workbook = new Workbook(sourceDir + "sampleCopySparkline.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access the first sparkline group
SparklineGroup group = worksheet.SparklineGroupCollection[0];
// Add Data Ranges and Locations inside this sparkline group
group.SparklineCollection.Add("D5:O5", 4, 15);
group.SparklineCollection.Add("D6:O6", 5, 15);
group.SparklineCollection.Add("D7:O7", 6, 15);
group.SparklineCollection.Add("D8:O8", 7, 15);
// Save the workbook
workbook.Save(outputDir + "outputCopySparkline.xlsx");
Console.WriteLine("CopySparkline executed successfully.");
}
static void CreateSparklineGroups(Workbook workbook)
{
#region #CreateSparklineGroups
Worksheet worksheet = workbook.Worksheets["SparklineExamples"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a group of line sparklines.
SparklineGroup quarterlyGroup = worksheet.SparklineGroups.Add(worksheet["G4:G6"], worksheet["C4:F4,C5:F5,C6:F6"], SparklineGroupType.Line);
// Add one more sparkline to the existing group.
quarterlyGroup.Sparklines.Add(6, 6, worksheet["C7:F7"]);
// Display a column sparkline in the total cell.
SparklineGroup totalGroup = worksheet.SparklineGroups.Add(worksheet["G8"], worksheet["C8:F8"], SparklineGroupType.Column);
#endregion #CreateSparklineGroups
}
public static void Run()
{
// Instantiate a Workbook
// Open a template file
Workbook book = new Workbook(sourceDir + "sampleUsingSparklines.xlsx");
// Get the first worksheet
Worksheet sheet = book.Worksheets[0];
// Use the following lines if you need to read the Sparklines
// Read the Sparklines from the template file (if it has)
foreach (SparklineGroup g in sheet.SparklineGroupCollection)
{
// Display the Sparklines group information e.g type, number of sparklines items
Console.WriteLine("sparkline group: type:" + g.Type + ", sparkline items count:" + g.SparklineCollection.Count);
foreach (Sparkline s in g.SparklineCollection)
{
// Display the individual Sparkines and the data ranges
Console.WriteLine("sparkline: row:" + s.Row + ", col:" + s.Column + ", dataRange:" + s.DataRange);
}
}
// Add Sparklines
// Define the CellArea D2:D10
CellArea ca = new CellArea();
ca.StartColumn = 4;
ca.EndColumn = 4;
ca.StartRow = 1;
ca.EndRow = 7;
// Add new Sparklines for a data range to a cell area
int idx = sheet.SparklineGroupCollection.Add(SparklineType.Column, "Sheet1!B2:D8", false, ca);
SparklineGroup group = sheet.SparklineGroupCollection[idx];
// Create CellsColor
CellsColor clr = book.CreateCellsColor();
clr.Color = Color.Orange;
group.SeriesColor = clr;
// Save the excel file
book.Save(outputDir + "outputUsingSparklines.xlsx");
Console.WriteLine("UsingSparklines executed successfully.");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate a Workbook
//Open a template file
Workbook book = new Workbook(dataDir + "Book1.xlsx");
//Get the first worksheet
Worksheet sheet = book.Worksheets[0];
//Use the following lines if you need to read the Sparklines
//Read the Sparklines from the template file (if it has)
foreach (SparklineGroup g in sheet.SparklineGroupCollection)
{
//Display the Sparklines group information e.g type, number of sparklines items
Console.WriteLine("sparkline group: type:" + g.Type + ", sparkline items count:" + g.SparklineCollection.Count);
foreach (Sparkline s in g.SparklineCollection)
{
//Display the individual Sparkines and the data ranges
Console.WriteLine("sparkline: row:" + s.Row + ", col:" + s.Column + ", dataRange:" + s.DataRange);
}
}
//Add Sparklines
//Define the CellArea D2:D10
CellArea ca = new CellArea();
ca.StartColumn = 4;
ca.EndColumn = 4;
ca.StartRow = 1;
ca.EndRow = 7;
//Add new Sparklines for a data range to a cell area
int idx = sheet.SparklineGroupCollection.Add(SparklineType.Column, "Sheet1!B2:D8", false, ca);
SparklineGroup group = sheet.SparklineGroupCollection[idx];
//Create CellsColor
CellsColor clr = book.CreateCellsColor();
clr.Color = Color.Orange;
group.SeriesColor = clr;
//Save the excel file
book.Save(dataDir + "Book1.out.xlsx");
//ExEnd:1
}
static void RearrangeSparklines(Workbook workbook)
{
#region #RearrangeSparklines
Worksheet worksheet = workbook.Worksheets["SparklineExamples"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a group of line sparklines.
SparklineGroup lineGroup = worksheet.SparklineGroups.Add(worksheet["G4:G7"], worksheet["C4:F4,C5:F5,C6:F6, C7:F7"], SparklineGroupType.Line);
// Rearrange sparklines by grouping the second and fourth sparklines together and changing the group type to "Column".
Sparkline sparklineG5 = lineGroup.Sparklines[1];
Sparkline sparklineG7 = lineGroup.Sparklines[3];
SparklineGroup columnGroup = worksheet.SparklineGroups.Add(new List <Sparkline> {
sparklineG5, sparklineG7
}, SparklineGroupType.Column);
#endregion #RearrangeSparklines
}
static void SpecifyAxisSettings(Workbook workbook)
{
#region #SpecifyAxisSettings
Worksheet worksheet = workbook.Worksheets["SparklineExamples"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Create a group of column sparklines.
SparklineGroup columnGroup = worksheet.SparklineGroups.Add(worksheet["G4:G7"], worksheet["C4:F4,C5:F5,C6:F6, C7:F7"], SparklineGroupType.Column);
// Specify the vertical axis options.
SparklineVerticalAxis verticalAxis = columnGroup.VerticalAxis;
// Set the custom minimum value for the vertical axis.
verticalAxis.MinScaleType = SparklineAxisScaling.Custom;
verticalAxis.MinCustomValue = 0;
// Set the custom maximum value for the vertical axis.
verticalAxis.MaxScaleType = SparklineAxisScaling.Custom;
verticalAxis.MaxCustomValue = 12000;
#endregion #SpecifyAxisSettings
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 0.23;
sheet.Range["B3"].NumberValue = 0.37;
sheet.Range["B4"].NumberValue = 0.15;
sheet.Range["B5"].NumberValue = 0.25;
//Jul
sheet.Range["C1"].Value = "Jul";
sheet.Range["C2"].NumberValue = 0.1;
sheet.Range["C3"].NumberValue = 0.35;
sheet.Range["C4"].NumberValue = 0.22;
sheet.Range["C5"].NumberValue = 0.33;
//Aug
sheet.Range["D1"].Value = "Aug";
sheet.Range["D2"].NumberValue = 0.14;
sheet.Range["D3"].NumberValue = 0.36;
sheet.Range["D4"].NumberValue = 0.25;
sheet.Range["D5"].NumberValue = 0.25;
//Aug
sheet.Range["E1"].Value = "Sep";
sheet.Range["E2"].NumberValue = 0.17;
sheet.Range["E3"].NumberValue = 0.28;
sheet.Range["E4"].NumberValue = 0.39;
sheet.Range["E5"].NumberValue = 0.32;
//Style
sheet.Range["A1:E1"].Style.Font.IsBold = true;
sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;
SparklineGroup sparklineGroup
= sheet.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(sheet["B2:E2"], sheet["F2"]);
sparklines.Add(sheet["B3:E3"], sheet["F3"]);
sparklines.Add(sheet["B4:E4"], sheet["F4"]);
sparklines.Add(sheet["B5:E5"], sheet["F5"]);
workbook.SaveToFile("Sample.xlsx");
ExcelDocViewer(workbook.FileName);
}
