public static void Main(string[] args)
{
Application
ExcelApp = null;
Workbook
Workbook = null;
Sheets
Sheets = null,
Charts = null;
Worksheet
Sheet = null;
Chart
Chart = null;
Series
Series = null;
Axes
Axes = null;
Axis
Axis = null;
Range
Range = null,
Range2 = null;
object
tmpObject;
try
{
try
{
string
CurrentDirectory = System.IO.Directory.GetCurrentDirectory();
CurrentDirectory = CurrentDirectory.Substring(0, CurrentDirectory.LastIndexOf("bin", CurrentDirectory.Length - 1));
string
//InputFileName = CurrentDirectory + "test.xls",
InputFileName = "d:\\result.xlsx",
OutputDbf = CurrentDirectory + "xls2dbf.dbf";
try
{
ExcelApp = (Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException eException)
{
if (eException.ErrorCode == -2147221021)
{
ExcelApp = new Application();
}
}
ExcelApp.Visible = true;
ExcelApp.DisplayAlerts = false;
ExcelApp.UserControl = false;
#if TEST_DATE
Workbook = ExcelApp.Workbooks.Add(Type.Missing);
Sheet = (Worksheet)Workbook.ActiveSheet;
Sheet.Columns.get_Range("A1", "A1").ColumnWidth = 3;
Sheet.get_Range("A1", Type.Missing).Value = "01.01.2001";
tmpObject = Sheet.get_Range("A1", Type.Missing).Value;
Workbook.Close(Type.Missing, Type.Missing, Type.Missing);
#endif
Workbook = ExcelApp.Workbooks.Open(InputFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#if TEST_SAVE_AS
if (File.Exists(OutputDbf))
{
File.Delete(OutputDbf);
}
Workbook.SaveAs(OutputDbf, XlFileFormat.xlDBF4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
#endif
Sheets = Workbook.Worksheets;
Sheet = (Worksheet)Workbook.ActiveSheet;
double
points = ExcelApp.CentimetersToPoints(1.93d);
points = ExcelApp.CentimetersToPoints(2.70d);
points = ExcelApp.CentimetersToPoints(3.55d);
int
MaxRow = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row,
MaxCol = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column;
Range = Sheet.get_Range("A1", Type.Missing);
Range = Range.get_End(XlDirection.xlToRight);
Range = Range.get_End(XlDirection.xlDown);
string
CurrAddress = Range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
Range = Sheet.get_Range("A1", CurrAddress);
CurrAddress = ColNoToName(MaxCol) + Convert.ToString(MaxRow);
Range = Sheet.get_Range("A1", CurrAddress);
object[,]
values = (object[, ])Range.Value2;
int
MaxI = values.GetLength(0), // Row
MaxJ = values.GetLength(1); // Col
string
tmpString = string.Empty;
for (int i = 1; i <= MaxI; ++i) // Row
{
for (int j = 1; j <= MaxJ; ++j) // Col
{
tmpString += "[" + i + "," + j + "]=" + (values[i, j] != null ? values[i, j] : "null");
if (j <= MaxJ - 1)
{
tmpString += "\t";
}
}
tmpString += Environment.NewLine;
}
Console.WriteLine(tmpString);
Range = Sheet.get_Range("A10", "E18");
//Range.Formula=values;
Range.Value = values;
Range = Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[10, 10]);
tmpObject = ((Range)Range.get_Item(1, 1)).Value;
Sheet.get_Range("A23", Type.Missing).Value = "A23";
tmpString = Convert.ToString(Sheet.get_Range("A23", Type.Missing).Value);
Sheet.get_Range("A24", "C24").Merge(true);
for (int _i = 1; _i <= 10; ++_i)
{
Sheet.get_Range("F" + _i, Type.Missing).Value = _i;
}
Sheet.get_Range("F11", Type.Missing).Formula = "=яслл(F1:F10)";
Sheet.get_Range("F11", Type.Missing).Orientation = 30;
Sheet.get_Range("F11", Type.Missing).Font.Bold = true;
Sheet.get_Range("F11", Type.Missing).Font.Size = 25;
Range = Sheet.get_Range("F2", Type.Missing);
tmpObject = -4121; // 0xFFFFEFE7; /* xlShiftDown */
Range.Rows.Insert(tmpObject);
Range.Rows.Insert(Type.Missing);
Sheet.get_Range("F2", Type.Missing).Value = 999;
Sheet = (Worksheet)Sheets.get_Item(2);
Sheet.Activate();
string
CellDirect,
CellObject;
#region bool
CellDirect = "E1";
CellObject = "E2";
tmpString = "bool";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F1";
CellObject = "F2";
Sheet.get_Range(CellDirect, Type.Missing).Value = true;
bool
tmpBool = true;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpBool;
#endregion
#region byte
CellDirect = "E3";
CellObject = "E4";
tmpString = "byte";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F3";
CellObject = "F4";
Sheet.get_Range(CellDirect, Type.Missing).Value = 255;
byte
tmpByte = 255;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpByte;
#endregion
#region sbyte
CellDirect = "E5";
CellObject = "E6";
tmpString = "sbyte";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F5";
CellObject = "F6";
Sheet.get_Range(CellDirect, Type.Missing).Value = 127;
sbyte
tmpSByte = 127;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpSByte;
#endregion
#region short
CellDirect = "E7";
CellObject = "E8";
tmpString = "short";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F7";
CellObject = "F8";
Sheet.get_Range(CellDirect, Type.Missing).Value = 32767;
short
tmpShort = 32767;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpShort;
#endregion
#region ushort
CellDirect = "E9";
CellObject = "E10";
tmpString = "ushort";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F9";
CellObject = "F10";
Sheet.get_Range(CellDirect, Type.Missing).Value = 65535;
ushort
tmpUShort = 65535;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpUShort;
#endregion
#region int
CellDirect = "E11";
CellObject = "E12";
tmpString = "int";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F11";
CellObject = "F12";
Sheet.get_Range(CellDirect, Type.Missing).Value = 2147483647;
int
tmpInt = 2147483647;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpInt;
#endregion
#region uint
CellDirect = "E13";
CellObject = "E14";
tmpString = "uint";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F13";
CellObject = "F14";
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)4294967295U;
uint
tmpUInt = 4294967295U;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpUInt;
#endregion
#region long
CellDirect = "E15";
CellObject = "E16";
tmpString = "long";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F15";
CellObject = "F16";
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999999999L;
//Sheet.get_Range(CellDirect,Type.Missing).Value=9223372036854775807L;
long
tmpLong = 999999999999999L; //9223372036854775807L;
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpLong;
#endregion
#region ulong
CellDirect = "E17";
CellObject = "E18";
tmpString = "ulong";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F17";
CellObject = "F18";
//Sheet.get_Range(CellDirect,Type.Missing).Value=18446744073709551615UL;
ulong
tmpULong = 18446744073709551615UL;
//Sheet.get_Range(CellObject,Type.Missing).Value=tmpULong;
#endregion
#region DateTime
CellDirect = "E19";
CellObject = "E20";
tmpString = "DateTime";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F19";
CellObject = "F20";
Sheet.get_Range(CellDirect, Type.Missing).Value = DateTime.Now;
DateTime
tmpDateTime = DateTime.Now;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
#endregion
#region TimeSpan
CellDirect = "E21";
CellObject = "E22";
tmpString = "TimeSpan";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F21";
CellObject = "F22";
tmpDateTime = new DateTime(0L);
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpDateTime + (new TimeSpan(1, 2, 3));
TimeSpan
tmpTimeSpan = new TimeSpan(1, 2, 3);
// !!!
tmpDateTime = new DateTime(0L);
tmpDateTime += tmpTimeSpan;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
#endregion
#region float
CellDirect = "E23";
CellObject = "E24";
tmpString = "float";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F23";
CellObject = "F24";
Sheet.get_Range(CellDirect, Type.Missing).Value = 9999.9999F;
float
tmpFloat = 9999.9999F;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpFloat;
#endregion
#region double
CellDirect = "E25";
CellObject = "E26";
tmpString = "double";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F25";
CellObject = "F26";
Sheet.get_Range(CellDirect, Type.Missing).Value = 1.7E+3;
double
tmpDouble = 1.7E+3;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpDouble;
#endregion
#region decimal
CellDirect = "E27";
CellObject = "E28";
tmpString = "decimal";
Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;
CellDirect = "F27";
CellObject = "F28";
// !!!
Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999.99m;
decimal
tmpDecimal = 999999999.99m;
// !!!
Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpDecimal;
#endregion
Sheet = (Worksheet)Sheets.get_Item(3);
Range = Sheet.get_Range("A1", "A5");
Range.Value = "Some Value";
Range2 = Sheet.get_Range("A11", "A15");
Range.Copy(Range2);
Range = Sheet.get_Range("B1", "B5");
Range.Value = "Some Value";
Range.Copy(Type.Missing);
Range2 = Sheet.get_Range("B11", "B15");
Sheet.Paste(Range2, Type.Missing);
Range2 = Sheet.get_Range("B20", Type.Missing);
Sheet.Activate();
Range2.Select();
Sheet.Paste(Type.Missing, Type.Missing);
#if TEST_CHART
Sheet = (Excel.Worksheet)Sheets.get_Item(3);
Sheet.Activate();
for (int Col = 1; Col <= 20; ++Col)
{
Sheet.get_Range("A" + Col, Type.Missing).Value = Col;
Sheet.get_Range("B" + Col, Type.Missing).Formula = "=sin(A" + Col + ")";
Sheet.get_Range("C" + Col, Type.Missing).Formula = "=cos(A" + Col + ")";
Sheet.get_Range("D" + Col, Type.Missing).Formula = "=B" + Col + "+C" + Col;
}
Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Charts = ExcelApp.Charts;
Chart = (Excel.Chart)Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Chart.Type=65; // xlLineMarkers
Chart.Type = 4; // xlLine
Chart.SetSourceData(Sheet.get_Range("B1", "C20"), 2 /*xlColumns*/);
Chart = Chart.Location(XlChartLocation.xlLocationAsObject, "кХЯР4");
Series = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).NewSeries();
MaxI = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).Count;
Range = Sheet.get_Range("A1", "A20");
for (int i = MaxI; i > 0; --i)
{
((Excel.Series)Chart.SeriesCollection(i)).XValues = Range;
switch (i)
{
case 1:
{
((Excel.Series)Chart.SeriesCollection(i)).Name = "sin";
break;
}
case 2:
{
((Excel.Series)Chart.SeriesCollection(i)).Name = "cos";
break;
}
}
}
Range = Sheet.get_Range("D1", "D20");
Series.Values = Range;
Series.Name = "sin+cos";
Chart.HasTitle = true;
Chart.ChartTitle.Caption = "Name of Chart";
Axis = (Excel.Axis)Chart.Axes(1 /*xlCategory*/, XlAxisGroup.xlPrimary);
Axis.HasTitle = true;
Axis.CategoryNames = "Name of Category";
Axis = (Excel.Axis)Chart.Axes(2 /*xlValue*/, XlAxisGroup.xlPrimary);
Axis.HasTitle = true;
Chart.HasLegend = true;
Chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;
#endif
ExcelApp.Quit();
}
catch (COMException eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "ErrorCode: " + eException.ErrorCode + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
catch (ArgumentException eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "ParamName: " + eException.ParamName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
catch (Exception eException)
{
string
tmp = eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;
Console.WriteLine(tmp);
}
}
finally
{
if (Axes != null)
{
Marshal.ReleaseComObject(Axes);
Axes = null;
}
if (Axis != null)
{
Marshal.ReleaseComObject(Axis);
Axis = null;
}
if (Series != null)
{
Marshal.ReleaseComObject(Series);
Series = null;
}
if (Chart != null)
{
Marshal.ReleaseComObject(Chart);
Chart = null;
}
if (Charts != null)
{
Marshal.ReleaseComObject(Charts);
Charts = null;
}
if (Range2 != null)
{
Marshal.ReleaseComObject(Range2);
Range2 = null;
}
if (Range != null)
{
Marshal.ReleaseComObject(Range);
Range = null;
}
if (Sheets != null)
{
Marshal.ReleaseComObject(Sheets);
Sheets = null;
}
if (Sheet != null)
{
Marshal.ReleaseComObject(Sheet);
Sheet = null;
}
if (Workbook != null)
{
Marshal.ReleaseComObject(Workbook);
Workbook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
}
//GC.Collect();
GC.GetTotalMemory(true);
}
}