private static double GetLastDayOfPreviousMonthAsDouble()
{
var previousMonth = DateTime.Now.AddMonths(-1);
var lastDay = new DateTime(
previousMonth.Year,
previousMonth.Month,
DateTime.DaysInMonth(previousMonth.Year, previousMonth.Month)
);
return(FlxDateTime.ToOADate(lastDay, false));
}
public static bool ConvertDateToNumber(string sValue, string[] DateFormats, out DateTime DateResult)
{
//Until we get a DateTime.TryParse... THIS IS *REALLY* SLOW because of exceptions. On 2.0 we should use Tryparse
#if (!FRAMEWORK20 || COMPACTFRAMEWORK)
bool IsDateTime = false;
DateResult = new DateTime(1, 1, 1);
if ((sValue != null) && (sValue.Length > 2) &&
sValue[0] >= '0' && sValue[0] <= '9'
//&&
//(sValue.IndexOf(CultureInfo.CurrentCulture.DateTimeFormat.DateSeparator) > 0 ||
//sValue.IndexOf(CultureInfo.CurrentCulture.DateTimeFormat.TimeSeparator) > 0)
) //this is a hack, but allows us to not check many strings, and speed up a lot. As numbers are already checked, we will only check for a little portions of the strings.
//Bad for strings like "july 15, 2004", but it is a price we have to pay. On framework20 there is no problem.
{
try
{
DateResult = DateTime.Parse(sValue, CultureInfo.CurrentCulture, DateTimeStyles.NoCurrentDateDefault);
IsDateTime = true;
}
catch (FormatException)
{
IsDateTime = false;
}
}
#else
DateResult = DateTime.MinValue;
bool IsDateTime = false;
if (DateFormats == null)
{
IsDateTime = (sValue != null) && (sValue.Length > 0) && DateTime.TryParse(sValue, CultureInfo.CurrentCulture, DateTimeStyles.NoCurrentDateDefault, out DateResult);
}
else
{
IsDateTime = (sValue != null) && (sValue.Length > 0) && DateTime.TryParseExact(sValue, DateFormats, CultureInfo.CurrentCulture, DateTimeStyles.NoCurrentDateDefault, out DateResult);
}
double dt;
if (IsDateTime && (!FlxDateTime.TryToOADate(DateResult, false, out dt) || !FlxDateTime.TryToOADate(DateResult, true, out dt)))
{
IsDateTime = false;
}
#endif
return(IsDateTime);
}
private void AnalizeFile(string FileName, int Row, int Col)
{
XlsFile xls = new XlsFile();
xls.Open(FileName);
int XF = 0;
MessageBox.Show("Active sheet is \"" + xls.ActiveSheetByName + "\"");
object v = xls.GetCellValue(Row, Col, ref XF);
if (v == null)
{
MessageBox.Show("Cell A1 is empty");
return;
}
//Here we have all the kind of objects FlexCel can return.
switch (Type.GetTypeCode(v.GetType()))
{
case TypeCode.Boolean:
MessageBox.Show("Cell A1 is a boolean: " + (bool)v);
return;
case TypeCode.Double: //Remember, dates are doubles with date format.
TUIColor CellColor = Color.Empty;
bool HasDate, HasTime;
String CellValue = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, ref CellColor, xls, out HasDate, out HasTime).ToString();
if (HasDate || HasTime)
{
MessageBox.Show("Cell A1 is a DateTime value: " + FlxDateTime.FromOADate((double)v, xls.OptionsDates1904).ToString() + "\n" +
"The value is displayed as: " + CellValue);
}
else
{
MessageBox.Show("Cell A1 is a double: " + (double)v + "\n" +
"The value is displayed as: " + CellValue + "\n");
}
return;
case TypeCode.String:
MessageBox.Show("Cell A1 is a string: " + v.ToString());
return;
}
TFormula Fmla = v as TFormula;
if (Fmla != null)
{
MessageBox.Show("Cell A1 is a formula: " + Fmla.Text + " Value: " + Convert.ToString(Fmla.Result));
return;
}
TRichString RSt = v as TRichString;
if (RSt != null)
{
MessageBox.Show("Cell A1 is a formatted string: " + RSt.Value);
return;
}
if (v is TFlxFormulaErrorValue)
{
MessageBox.Show("Cell A1 is an error: " + TFormulaMessages.ErrString((TFlxFormulaErrorValue)v));
return;
}
throw new Exception("Unexpected value on cell");
}
private object GetOneProperty(TOle2File Ole2File, int PropType)
{
if ((PropType & (int)TPropertyTypes.VT_VECTOR) != 0)
{
byte[] i4 = new byte[4];
Ole2File.Read(i4, i4.Length);
object[] Vector = new object[BitOps.GetInt32(i4, 0)];
for (int i = 0; i < Vector.Length; i++)
{
Vector[i] = GetOneProperty(Ole2File, PropType & ~(int)TPropertyTypes.VT_VECTOR);
}
return(Vector);
}
switch ((TPropertyTypes)(PropType & 0xFF))
{
case TPropertyTypes.VT_EMPTY:
return(null);
case TPropertyTypes.VT_I2:
byte[] i2 = new byte[2];
Ole2File.Read(i2, i2.Length);
return(BitConverter.ToInt16(i2, 0));
case TPropertyTypes.VT_UI2: //This is not really suported, but we need to convert the CodePage to a Signed int.
byte[] ui2 = new byte[2];
Ole2File.Read(ui2, ui2.Length);
return((Int32)BitConverter.ToUInt16(ui2, 0));
case TPropertyTypes.VT_I4:
byte[] i4 = new byte[4];
Ole2File.Read(i4, i4.Length);
return(BitOps.GetInt32(i4, 0));
case TPropertyTypes.VT_R4:
byte[] d4 = new byte[4];
Ole2File.Read(d4, d4.Length);
return(BitConverter.ToSingle(d4, 0));
case TPropertyTypes.VT_R8:
byte[] d8 = new byte[8];
Ole2File.Read(d8, d8.Length);
return(BitConverter.ToDouble(d8, 0));
case TPropertyTypes.VT_CY:
byte[] cy = new byte[8];
Ole2File.Read(cy, cy.Length);
return(TCompactFramework.DecimalFromOACurrency(BitConverter.ToInt64(cy, 0)));
case TPropertyTypes.VT_DATE:
byte[] dd = new byte[8];
Ole2File.Read(dd, dd.Length);
DateTime Dt;
if (!FlxDateTime.TryFromOADate(BitConverter.ToDouble(dd, 0), false, out Dt))
{
return(DateTime.MinValue);
}
return(Dt.Date);
case TPropertyTypes.VT_BSTR:
byte[] sl = new byte[4];
Ole2File.Read(sl, sl.Length);
UInt32 StrLen = BitConverter.ToUInt32(sl, 0);
if (StrLen <= 1)
{
return(String.Empty); //StrLen includes the trailing #0
}
byte[] Str = new byte[StrLen - 1];
Ole2File.Read(Str, Str.Length);
Ole2File.SeekForward(Ole2File.Position + 1); //go over the 0 byte. This is needed for vectors/arrays.
return(new TUnconvertedString(Str, false));
case TPropertyTypes.VT_BOOL:
byte[] bl = new byte[2];
Ole2File.Read(bl, bl.Length);
return(BitConverter.ToInt16(bl, 0) == 0? false: true);
case TPropertyTypes.VT_VARIANT:
byte[] VariantTypeArray = new byte[4];
Ole2File.Read(VariantTypeArray, VariantTypeArray.Length);
Int32 VariantType = BitOps.GetInt32(VariantTypeArray, 0);
return(GetOneProperty(Ole2File, VariantType));
case TPropertyTypes.VT_I8:
byte[] i8 = new byte[8];
Ole2File.Read(i8, i8.Length);
return(BitConverter.ToInt64(i8, 0));
case TPropertyTypes.VT_LPSTR:
byte[] sl2 = new byte[4];
Ole2File.Read(sl2, sl2.Length);
UInt32 StrLen2 = BitConverter.ToUInt32(sl2, 0);
if (StrLen2 <= 1)
{
return(String.Empty); //StrLen includes the trailing #0
}
byte[] Str2 = new byte[StrLen2 - 1];
Ole2File.Read(Str2, Str2.Length);
Ole2File.SeekForward(Ole2File.Position + 1); //go over the 0 byte. This is needed for vectors/arrays.
return(new TUnconvertedString(Str2, false));
case TPropertyTypes.VT_LPWSTR:
byte[] sl3 = new byte[4];
Ole2File.Read(sl3, sl3.Length);
UInt32 StrLen3 = BitConverter.ToUInt32(sl3, 0);
if (StrLen3 <= 1)
{
return(String.Empty); //StrLen includes the trailing #0
}
byte[] Str3 = new byte[(StrLen3 - 1) * 2];
Ole2File.SeekForward(Ole2File.Position + 2); //go over the 0 byte. This is needed for vectors/arrays.
Ole2File.Read(Str3, Str3.Length);
return(new TUnconvertedString(Str3, true));
case TPropertyTypes.VT_FILETIME:
byte[] ft = new byte[8];
Ole2File.Read(ft, ft.Length);
return(DateTime.FromFileTime(BitConverter.ToInt64(ft, 0)));
case TPropertyTypes.VT_BLOB:
byte[] blb = new byte[4];
Ole2File.Read(blb, blb.Length);
UInt32 BlobLen = BitConverter.ToUInt32(blb, 0);
if (BlobLen <= 0)
{
return(new byte[0]); //BlobLen does not includes trailing #0
}
byte[] Blob = new byte[BlobLen];
Ole2File.Read(Blob, Blob.Length);
return(Blob);
}
return(null); //Not a supported type.
}
private void AddChart(ExcelFile xls)
{
//This code is adapted from APIMate.
//Objects
TShapeProperties ChartOptions1 = new TShapeProperties();
ChartOptions1.Anchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, 1, 215, 1, 608, 30, 228, 17, 736);
ChartOptions1.ShapeName = "Lines of code";
ChartOptions1.Print = true;
ChartOptions1.Visible = true;
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fLockText, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.LockRotation, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fAutoTextMargin, true);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.fillColor, 134217806);
ChartOptions1.ShapeOptions.SetValue(TShapeOption.wzName, "Lines of code");
ExcelChart Chart1 = xls.AddChart(ChartOptions1, TChartType.Area, new ChartStyle(102), false);
TDataLabel Title = new TDataLabel();
Title.PositionZeroBased = null;
ChartFillOptions TextFillOptions = new ChartFillOptions(new TShapeFill(new TSolidFill(TDrawingColor.FromRgb(0x80, 0x80, 0x80)), true, TFormattingType.Subtle, TDrawingColor.FromRgb(0x00, 0x00, 0x00, new TColorTransform(TColorTransformType.Alpha, 0)), false));
TChartTextOptions LabelTextOptions = new TChartTextOptions(new TFlxChartFont("Calibri Light", 320, TExcelColor.FromArgb(0x80, 0x80, 0x80), TFlxFontStyles.Bold, TFlxUnderline.None, TFontScheme.Major), THFlxAlignment.center, TVFlxAlignment.center, TBackgroundMode.Transparent, TextFillOptions);
Title.TextOptions = LabelTextOptions;
TDataLabelOptions LabelOptions = new TDataLabelOptions();
Title.LabelOptions = LabelOptions;
ChartLineOptions ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle));
ChartFillOptions ChartFillOptions = new ChartFillOptions(new TShapeFill(new TNoFill(), false, TFormattingType.Subtle, null, false));
Title.Frame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false);
TRTFRun[] Runs;
Runs = new TRTFRun[1];
Runs[0].FirstChar = 0;
TFlxFont fnt;
fnt = xls.GetDefaultFont;
fnt.Name = "Calibri Light";
fnt.Size20 = 320;
fnt.Color = TExcelColor.FromArgb(0x80, 0x80, 0x80);
fnt.Style = TFlxFontStyles.Bold;
fnt.Family = 0;
fnt.CharSet = 1;
fnt.Scheme = TFontScheme.Major;
Runs[0].FontIndex = xls.AddFont(fnt);
TRichString LabelValue1 = new TRichString("FlexCel: Lines of code over time", Runs, xls);
Title.LabelValues = new Object[] { LabelValue1 };
Chart1.SetTitle(Title);
Chart1.Background = new TChartFrameOptions(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), 9525, TDrawingColor.FromTheme(TThemeColor.Light1), false);
TChartFrameOptions PlotAreaFrame;
ChartLineOptions = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle));
ChartFillOptions = new ChartFillOptions(new TShapeFill(new TPatternFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85)), TDrawingColor.FromTheme(TThemeColor.Light1), TDrawingPattern.ltDnDiag), true, TFormattingType.Subtle, null, false));
PlotAreaFrame = new TChartFrameOptions(ChartLineOptions, ChartFillOptions, false);
TChartPlotAreaPosition PlotAreaPos = new TChartPlotAreaPosition(true, TChartRelativeRectangle.Automatic, TChartLayoutTarget.Inner, true);
Chart1.PlotArea = new TChartPlotArea(PlotAreaFrame, PlotAreaPos, false);
Chart1.SetChartOptions(1, new TAreaChartOptions(false, TStackedMode.Stacked, null));
int LastYear = 0;
double shade = 1;
for (int i = 2; i < 190; i++)
{
ChartSeries Series = new ChartSeries(
"=" + new TCellAddress("Data", 1, i, true, true).CellRef,
"=" + new TCellAddress("Data", 2, i, true, true).CellRef + ":" + new TCellAddress("Data", 189, i, true, true).CellRef,
"=Data!$A$2:$A$189");
//We will display every year in a single color. Each month gets its own shade.
int xf = -1;
int Year = FlxDateTime.FromOADate(((double)xls.GetCellValue(2, 1, i, ref xf)), false).Year;
if (LastYear != Year)
{
shade = 1;
}
else if (shade > 0.3)
{
shade -= 0.05;
}
LastYear = Year;
TDrawingColor SeriesColor = TDrawingColor.FromTheme(TThemeColor.Accent1 + Year % 6,
new TColorTransform(TColorTransformType.Shade, shade));
ChartSeriesFillOptions SeriesFill = new ChartSeriesFillOptions(new TShapeFill(new TSolidFill(SeriesColor), true, TFormattingType.Subtle, null, false), null, false, false);
ChartSeriesLineOptions SeriesLine = new ChartSeriesLineOptions(new TShapeLine(true, new TLineStyle(new TNoFill(), null), null, TFormattingType.Subtle), false);
Series.Options.Add(new ChartSeriesOptions(-1, SeriesFill, SeriesLine, null, null, null, true));
Chart1.AddSeries(Series);
}
Chart1.PlotEmptyCells = TPlotEmptyCells.Zero;
Chart1.ShowDataInHiddenRowsAndCols = false;
TFlxChartFont AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor);
TAxisLineOptions AxisLine = new TAxisLineOptions();
AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false;
TAxisTickOptions AxisTicks = new TAxisTickOptions(TTickType.Outside, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0);
TAxisRangeOptions AxisRangeOptions = new TAxisRangeOptions(12, 1, false, false, false);
TBaseAxis CatAxis = new TCategoryAxis(0, 0, 12, TDateUnits.Days, 12, TDateUnits.Days, TDateUnits.Months, 0, TCategoryAxisOptions.AutoMin | TCategoryAxisOptions.AutoMax | TCategoryAxisOptions.DateAxis | TCategoryAxisOptions.AutoCrossDate | TCategoryAxisOptions.AutoDate, AxisFont, "yyyy\\-mm\\-dd;@", true, AxisLine, AxisTicks, AxisRangeOptions, null, TChartAxisPos.Bottom, 1);
AxisFont = new TFlxChartFont("Calibri", 180, TExcelColor.FromArgb(0x59, 0x59, 0x59), TFlxFontStyles.None, TFlxUnderline.None, TFontScheme.Minor);
AxisLine = new TAxisLineOptions();
AxisLine.MainAxis = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.MajorGridLines = new ChartLineOptions(new TShapeLine(true, new TLineStyle(new TSolidFill(TDrawingColor.FromTheme(TThemeColor.Dark1, new TColorTransform(TColorTransformType.LumMod, 0.15), new TColorTransform(TColorTransformType.LumOff, 0.85))), 9525, TPenAlignment.Center, TLineCap.Flat, TCompoundLineType.Single, null, TLineJoin.Round, null, null, null), null, TFormattingType.Subtle));
AxisLine.DoNotDrawLabelsIfNotDrawingAxis = false;
AxisTicks = new TAxisTickOptions(TTickType.None, TTickType.None, TAxisLabelPosition.NextToAxis, TBackgroundMode.Transparent, TDrawingColor.FromRgb(0x59, 0x59, 0x59), 0);
CatAxis.NumberFormat = "yyyy-mm";
CatAxis.NumberFormatLinkedToSource = false;
TBaseAxis ValAxis = new TValueAxis(0, 0, 0, 0, 0, TValueAxisOptions.AutoMin | TValueAxisOptions.AutoMax | TValueAxisOptions.AutoMajor | TValueAxisOptions.AutoMinor | TValueAxisOptions.AutoCross, AxisFont, "General", true, AxisLine, AxisTicks, null, TChartAxisPos.Left);
Chart1.SetChartAxis(new TChartAxis(0, CatAxis, ValAxis));
}
