public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbook to open the file that contains a chart
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[1];
// Get the first chart in the sheet
Chart chart = worksheet.Charts[0];
// Specify the FilFormat's type to Solid Fill of the first series
chart.NSeries[0].Area.FillFormat.FillType = Aspose.Cells.Drawing.FillType.Solid;
// Get the CellsColor of SolidFill
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
// Create a theme in Accent style
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
// Apply the them to the series
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
// Save the Excel file
workbook.Save(dataDir + "output.out.xlsx");
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate the workbook to open the file that contains a chart
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[1];
//Get the first chart in the sheet
Chart chart = worksheet.Charts[0];
//Specify the FilFormat's type to Solid Fill of the first series
chart.NSeries[0].Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
//Get the CellsColor of SolidFill
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
//Create a theme in Accent style
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
//Apply the them to the series
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
//Save the Excel file
workbook.Save(dataDir + "output.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Read the source excel file
Workbook book = new Workbook(dataDir + "sourceGlowEffectColor.xlsx");
// Access first worksheet
Worksheet sheet = book.Worksheets[0];
// Access the shape
Shape shape = sheet.Shapes[0];
// Read the glow effect color and its various properties
GlowEffect effect = shape.Glow;
CellsColor color = effect.Color;
Console.WriteLine("Color: " + color.Color);
Console.WriteLine("ColorIndex: " + color.ColorIndex);
Console.WriteLine("IsShapeColor: " + color.IsShapeColor);
Console.WriteLine("Transparency: " + color.Transparency);
Console.WriteLine("Type: " + color.Type);
// ExEnd:1
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
// Read the source excel file
Workbook book = new Workbook(sourceDir + "sampleReadColorOfShapesGlowEffect.xlsx");
// Access first worksheet
Worksheet sheet = book.Worksheets[0];
// Access the shape
Shape shape = sheet.Shapes[0];
// Read the glow effect color and its various properties
GlowEffect effect = shape.Glow;
CellsColor color = effect.Color;
Console.WriteLine("Color: " + color.Color);
Console.WriteLine("ColorIndex: " + color.ColorIndex);
Console.WriteLine("IsShapeColor: " + color.IsShapeColor);
Console.WriteLine("Transparency: " + color.Transparency);
Console.WriteLine("Type: " + color.Type);
Console.WriteLine("ReadColorOfShapesGlowEffect executed successfully.\r\n");
}
public static void Run()
{
// Instantiate the workbook to open the file that contains a chart
Workbook workbook = new Workbook(sourceDir + "sampleApplyingThemesInChart.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the first chart in the sheet
Chart chart = worksheet.Charts[0];
// Specify the FilFormat's type to Solid Fill of the first series
chart.NSeries[0].Area.FillFormat.FillType = Aspose.Cells.Drawing.FillType.Solid;
// Get the CellsColor of SolidFill
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
// Create a theme in Accent style
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
// Apply the them to the series
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
// Save the Excel file
workbook.Save(outputDir + "outputApplyingThemesInChart.xlsx");
Console.WriteLine("ApplyingThemesInChart executed successfully.");
}
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
}
/// <summary>
/// 初始化excel
/// </summary>
/// <param name="isFile">是否为文件模式（对应是否为ismport）</param>
/// <param name="attachment"></param>
/// <param name="template"></param>
/// <param name="sheets"></param>
/// <param name="configs"></param>
/// <param name="selects"></param>
/// <returns></returns>
public Workbook InitExcel(bool isFile, Attachment attachment, Template template, List <TemplateSheet> sheets, List <TemplateConfig> configs, List <TemplateConfigSelect> selects)
{
ExcelEngine engine = new ExcelEngine();
Workbook wb = null;
if (!isFile)
{
wb = new Workbook();
wb.Worksheets.Clear();
}
else
{
var stream = FileUploadHelper.DownLoadFileStream(attachment.AttachmentPath, attachment.IsUseV1).ToStream();
stream.Seek(0, SeekOrigin.Begin);
wb = new Workbook(stream);
}
#region init style
StyleFlag styleFlag = new StyleFlag()
{
All = true, Borders = true
};
CellsColor bcolor = wb.CreateCellsColor();
bcolor.ColorIndex = 36;
CellsColor ccolor = wb.CreateCellsColor();
ccolor.ColorIndex = 36;
CellsColor tcolor = wb.CreateCellsColor();
tcolor.ColorIndex = 36;
Style style = wb.Styles[wb.Styles.Add()];
style.Font.Size = 18;
style.Font.Name = "微软雅黑";
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
style.ForegroundColor = System.Drawing.Color.White;
style.Pattern = BackgroundType.Solid;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
Style markStyle = wb.Styles[wb.Styles.Add()];
markStyle.Font.Size = 15;
markStyle.Font.Name = "微软雅黑";
markStyle.IsTextWrapped = true;
markStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Right;
markStyle.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
markStyle.ForegroundColor = System.Drawing.Color.Yellow;
markStyle.Pattern = BackgroundType.Solid;
markStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;
markStyle.VerticalAlignment = TextAlignmentType.Center;
markStyle.HorizontalAlignment = TextAlignmentType.Left;
Style contentStyle = wb.Styles[wb.Styles.Add()];
//contentStyle.IsLocked = false;
contentStyle.Font.Name = "Arial";
contentStyle.Font.Size = 10;
contentStyle.Pattern = BackgroundType.Solid;
//contentStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
// contentStyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.FromArgb(155, 194, 230);
contentStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
contentStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.White;
contentStyle.IsTextWrapped = true;
contentStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
contentStyle.Borders[BorderType.LeftBorder].Color = bcolor.Color;
contentStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.RightBorder].Color = bcolor.Color;
contentStyle.VerticalAlignment = TextAlignmentType.Center;
contentStyle.HorizontalAlignment = TextAlignmentType.Left;
Style contentStyle1 = wb.Styles[wb.Styles.Add()];
//contentStyle1.IsLocked = false;
contentStyle1.Font.Name = "Arial";
contentStyle1.Font.Size = 10;
//221, 235, 247
contentStyle1.ForegroundColor = ccolor.Color;
contentStyle1.Pattern = BackgroundType.Solid;
//contentStyle1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//contentStyle1.Borders[BorderType.TopBorder].Color = System.Drawing.Color.FromArgb(155, 194, 230);
contentStyle1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
contentStyle1.Borders[BorderType.BottomBorder].Color = bcolor.Color;
contentStyle1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
contentStyle1.Borders[BorderType.LeftBorder].Color = bcolor.Color;
contentStyle1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
contentStyle1.Borders[BorderType.RightBorder].Color = bcolor.Color;
contentStyle1.VerticalAlignment = TextAlignmentType.Center;
contentStyle1.HorizontalAlignment = TextAlignmentType.Left;
Style titleStyle = wb.Styles[wb.Styles.Add()];
//contentStyle.IsLocked = false;
titleStyle.Font.Size = 12;
titleStyle.Font.Name = "微软雅黑";
titleStyle.ForegroundColor = tcolor.Color;
titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Right;
titleStyle.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
titleStyle.Pattern = BackgroundType.Solid;
titleStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
titleStyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
titleStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
titleStyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
titleStyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
titleStyle.VerticalAlignment = TextAlignmentType.Center;
titleStyle.HorizontalAlignment = TextAlignmentType.Center;
#endregion
foreach (var sheet in sheets)
{
int beforeCount = wb.Worksheets.Count;
//重名异常，忽略
try
{
wb.Worksheets.Add(sheet.TemplateSheetName);
}
catch (Exception e)
{
int afterCount = wb.Worksheets.Count;
if (afterCount > beforeCount)
{
wb.Worksheets.RemoveAt(wb.Worksheets.Count - 1);
}
}
var itemConfigs = configs.Where(x => x.TemplateSheetID == sheet.ID).ToList();
//var configs = TemplateConfigOperator.Instance.GetList(sheet.TemplateID, sheet.ID);
itemConfigs = itemConfigs.OrderBy(p => p.SortIndex).ToList();
int startIndex = sheet.ColumnNum - 1;
if (!isFile)
{
Range r = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(0, startIndex, sheet.RowNum - 1, itemConfigs.Count);
r.Merge();
r[0, 0].PutValue(sheet.TemplateSheetTitle);
r.ColumnWidth = 13.88;
r.RowHeight = 48.75;
//r[0, 0].SetStyle(style);
if (sheet.TemplateSheetRemark != null && sheet.TemplateSheetRemark != "")
{
Range rr = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(11, startIndex, 5, itemConfigs.Count);
rr.Merge();
rr[0, 0].PutValue(sheet.TemplateSheetRemark);
rr[0, 0].SetStyle(markStyle);
}
}
if (itemConfigs.Count > 0)
{
Range dataRange = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(sheet.RowNum, sheet.ColumnNum - 1, 9, itemConfigs.Count);
dataRange.Name = "DataName" + sheet.TemplateSheetName;
}
foreach (var config in itemConfigs)
{
Style cStyle = new Style();
#region 格式枚举
//Value Type Format String
//0 General General
//1 Decimal 0
//2 Decimal 0.00
//3 Decimal #,##0
//4 Decimal #,##0.00
//5 Currency $#,##0;$-#,##0
//6 Currency $#,##0;[Red]$-#,##0
//7 Currency $#,##0.00;$-#,##0.00
//8 Currency $#,##0.00;[Red]$-#,##0.00
//9 Percentage 0 %
//10 Percentage 0.00 %
//11 Scientific 0.00E+00
//12 Fraction # ?/?
//13 Fraction # /
//14 Date m/ d / yy
//15 Date d-mmm - yy
//16 Date d-mmm
//17 Date mmm-yy
//18 Time h:mm AM/ PM
//19 Time h:mm: ss AM/ PM
//20 Time h:mm
//21 Time h:mm: ss
//22 Time m/ d / yy h: mm
//37 Currency #,##0;-#,##0
//38 Currency #,##0;[Red]-#,##0
//39 Currency #,##0.00;-#,##0.00
//40 Currency #,##0.00;[Red]-#,##0.00
//41 Accounting _ * #,##0_ ;_ * "_ ;_ @_
//42 Accounting _ $* #,##0_ ;_ $* "_ ;_ @_
//43 Accounting _ * #,##0.00_ ;_ * "??_ ;_ @_
//44 Accounting _ $* #,##0.00_ ;_ $* "??_ ;_ @_
//45 Time mm:ss
//46 Time h :mm: ss
//47 Time mm:ss.0
//48 Scientific ##0.0E+00
//49 Text @
#endregion
// Obtain the existing Validations collection.
ValidationCollection validations = wb.Worksheets[sheet.TemplateSheetName].Validations;
// Create a validation object adding to the collection list.
Validation validation = validations[validations.Add()];
CellArea area;
switch (config.FieldType)
{
case "Text":
cStyle.Number = 49;
break;
case "Number":
cStyle.Number = 1;
if (config.Digit != 0)
{
cStyle.Custom = "0.".PadRight(config.Digit + 2, '0');
}
// Set the validation type.
validation.Type = ValidationType.Decimal;
// Specify the operator.
validation.Operator = OperatorType.Between;
// Set the lower and upper limits.
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
// Set the error message.
validation.ErrorMessage = "";
// Specify the validation area of cells.
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
// Add the area.
validation.AreaList.Add(area);
break;
case "DateTiem":
cStyle.Number = 15;
cStyle.Custom = "yyyy-m-d";
// Set the data validation type.
validation.Type = ValidationType.Date;
// Set the operator for the data validation
validation.Operator = OperatorType.Between;
// Set the value or expression associated with the data validation.
validation.Formula1 = "1970-1-1";
// The value or expression associated with the second part of the data validation.
validation.Formula2 = "2099-12-31";
// Enable the error.
validation.ShowError = true;
// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the title of the data-validation error dialog box
validation.ErrorTitle = "r";
// Set the data validation error message.
validation.ErrorMessage = "";
// Set and enable the data validation input message.
validation.InputMessage = "";
validation.IgnoreBlank = true;
validation.ShowInput = true;
// Set a collection of CellArea which contains the data validation settings.
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
validation.AreaList.Add(area);
break;
}
styleFlag = new StyleFlag()
{
NumberFormat = true, VerticalAlignment = true, HorizontalAlignment = true, FontSize = true, FontColor = true
};
var colors = config.BGColor.Split(',');
cStyle.ForegroundColor = System.Drawing.Color.FromArgb(int.Parse(colors[0]), int.Parse(colors[1]), int.Parse(colors[2]));
cStyle.Pattern = BackgroundType.Solid;
if (!isFile)
{
wb.Worksheets[sheet.TemplateSheetName].Cells.ApplyColumnStyle(startIndex, cStyle, styleFlag);
wb.Worksheets[sheet.TemplateSheetName].Cells[0, 0].SetStyle(style);
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].PutValue(config.FieldName);
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].SetStyle(titleStyle);
}
if (config.BGColor != "255,255,255")
{
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].SetStyle(cStyle);
}
var selectedValues = selects.FindAll(x => x.TemplateConfigID == config.ID);// TemplateConfigSelectOperator.Instance.GetList(sheet.TemplateID, sheet.ID, config.ID);
if (selectedValues.Count > 0)
{
var hidSheet = wb.Worksheets["勿删除"];
if (hidSheet == null)
{
hidSheet = wb.Worksheets.Add("勿删除");
}
hidSheet.IsVisible = false;
Range range = hidSheet.Cells.CreateRange(50, itemConfigs.Count + 10 + startIndex, selectedValues.Count, 1);
range.Name = "MyRange" + sheet.TemplateSheetName + startIndex;
int i = 0;
foreach (var item in selectedValues)
{
range[i, 0].PutValue(item.SelectedValue);
i++;
}
validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.Formula1 = "=MyRange" + sheet.TemplateSheetName + startIndex;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorTitle = "Error";
validation.ErrorMessage = "";
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
validation.AreaList.Add(area);
}
startIndex++;
}
if (!isFile)
{
wb.Worksheets[sheet.TemplateSheetName].AutoFilter.SetRange(sheet.RowNum - 1, sheet.ColumnNum - 1, itemConfigs.Count - 1);
}
}
wb.FileName = template.TemplateName;
if (wb.Worksheets.Count > 0 && wb.Worksheets[0].IsVisible)
{
wb.Worksheets.ActiveSheetIndex = 0;
}
return(wb);
}
