public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example01{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example05{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
bool isFailed = false;
string workbookFile = null;
Excel.Application excelApplication = null;
try
{
// start excel and turn off msg boxes
excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = false;
// create a utils instance, not need for but helpful to keep the lines of code low
Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
XlFileFormat fileFormat = GetFileFormat(excelApplication);
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
workBook.SaveAs(workbookFile, fileFormat);
}
catch (System.Runtime.InteropServices.COMException throwedException)
{
isFailed = true;
HostApplication.ShowErrorDialog("VBA Error", throwedException);
}
finally
{
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
if ((null != workbookFile) && (!isFailed))
{
HostApplication.ShowFinishDialog(null, workbookFile);
}
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
//Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Workbook workBook = excelApplication.Workbooks.Open(@"C:\Users\Gilbert Perlaza\Dropbox\HPC\MttoApp\Docs\FORMATO EWO EN BLANCO UNIFICADO OT.XLSX");
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
workSheet.Cells[1, 1].Value = "NetOffice Excel Example 04";
// create a star
Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
Excel.Shape rb = workSheet.Shapes.AddFormControl(Excel.Enums.XlFormControl.xlOptionButton, 100, 100, 100, 100);
var sh = workSheet.Shapes;
foreach (var item in sh)
{
if (item.Name.Equals("Option Button 1"))
{
item.ControlFormat.Value = 1;
}
}
rb.TextFrame.Characters().Text = "Hola prueba";
rb.ControlFormat.Value = 0;
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);
// create text effect
Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
string workbookFile = null;
if (workSheet.EntityIsAvailable("ExportAsFixedFormat"))
{
// save the sheet as PDF
workbookFile = System.IO.Path.Combine(HostApplication.RootDirectory, "Example10.pdf");
workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, workbookFile, XlFixedFormatQuality.xlQualityStandard);
}
else
{
// we are sorry - pdf export is not supported in Excel 2003 or below
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example10", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
}
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
workSheet.Cells[1, 1].Value = "NetOffice Excel Example 04";
// create a star
Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);
// create text effect
Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public static void Main()
{
// start excel and turn off msg boxes
NetOffice.ExcelApi.Application excelApplication = new NetOffice.ExcelApi.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
NetOffice.ExcelApi.Workbook workBook = excelApplication.Workbooks.Add();
NetOffice.ExcelApi.Worksheet workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// we need some data to display
NetOffice.ExcelApi.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
NetOffice.ExcelApi.ChartObject chart = ((NetOffice.ExcelApi.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = Path.Combine(Directory.GetCurrentDirectory(), string.Format("Example01{0}", fileExtension));
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
workSheet.Cells[1, 1].Value = "these sample shapes was dynamicly created by code.";
// create a star
Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);
// create text effect
Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example04{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
try { File.Delete(fileName); }
catch (Exception ex) { }
try
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
//TODO Log Error Helper
MessageBox.Show("The Excel file could not be created. Please check that you have Microsoft Excel 2003 or Higher Installed", "IQTools", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public string CreateExcel()
{
string returnString = "";
try
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
//Add Extension Here
fileName += GetDefaultExtension(excelApplication);
try { File.Delete(fileName); }
catch (Exception ex) { returnString = ex.Message.ToString(); }
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
Excel.Range dataRange = PutSampleData(workSheet);
// save the book
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
return("success");
}
catch (Exception ex)
{
returnString += ex.Message.ToString();
return(returnString);
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = COMObject.Create <Excel.Application>();
excelApplication.DisplayAlerts = false;
// create a utils instance, no need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and call the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// save the book - utils want build the filename for us
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example01", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public static void Export <T>(
this ICollectionView collectionView,
Dictionary <string, Model.DataCellFormats> fieldsAndFormats,
string reportTitle,
string reportDescription,
Func <T, string, string, object> getValueDelegate, Action <string> callBack = null)
{
if (getValueDelegate == null || fieldsAndFormats == null || reportTitle == null)
{
return;
}
const string commentFieldName = nameof(Model.Meter.Коментарий);
bool hasCommentColumn = fieldsAndFormats.ContainsKey(commentFieldName);
IEnumerable <T> collection = null;
Application.Current.Dispatcher.Invoke(() =>
{
collection = collectionView.Cast <T>();
});
int numberOfRows = collection.Count();
// +1 т.к. первый столбец номер по порядку
int numberOfColumns = fieldsAndFormats.Count + 1;
callBack?.Invoke("чтение данных");
object[,] output = BuildDataArray();
object[,] outputWithTwoRowPerRecord = null;
if (hasCommentColumn)
{
outputWithTwoRowPerRecord = BuildDataArrayWithTwoRowPerRecord();
}
callBack?.Invoke("поиск MS Excel");
string fileName = System.IO.Path.GetTempFileName();
fileName = System.IO.Path.ChangeExtension(fileName, "xlsx");
System.Globalization.CultureInfo defaultCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
// HACK: Workaround for Excel bug on machines which are set up in the English language, but not an English region.
System.Globalization.CultureInfo enusCultureInfo = System.Globalization.CultureInfo.GetCultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = enusCultureInfo;
Excel.Application excelApplication = null;
Excel.Workbook xlWorkbook = null;
Excel.Worksheet xlWorksheet = null;
Excel.Worksheet xlWorksheet2 = null;
NetOffice.OfficeApi.Tools.Contribution.CommonUtils utils = null;
Exception exception = null;
Process(output, outputWithTwoRowPerRecord);
OpenCreatedWorkBook();
// возвращает значение указанного поля записи
object GetValueOfField(T item, string fieldName)
{
object value = string.Empty;
if (string.IsNullOrWhiteSpace(fieldsAndFormats[fieldName].ExcelFormat) == false)
{
value = getValueDelegate(item, string.Empty, fieldName);
}
else
{
value = getValueDelegate(item, fieldsAndFormats[fieldName].ContentDisplayFormat, fieldName);
}
if (value is DateOnly dateOnlyValue)
{
value = dateOnlyValue.ToDateTime(TimeOnly.MinValue);
}
return(value);
}
// создание массива данных
object[,] BuildDataArray()
{
// +1 т.к. первый столбец номер по порядку
int countOfColumns = fieldsAndFormats.Count + 1;
// +1 т.к. первая строка шапка
object[,] output = new object[numberOfRows + 1, countOfColumns];
// for (int i = 0; i < numberOfRows + 1; i++)
// output[i] = new object[numberOfColumns];
output[0, 0] = "№ п/п";
int ind = 1;
foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats)
{
output[0, ind++] = Utils.ConvertFromTitleCase(field.Key);
}
callBack?.Invoke("заполнение таблицы");
int rowIndex = 1;
foreach (T item in collection)
{
output[rowIndex, 0] = rowIndex;
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
output[rowIndex, ind++] = GetValueOfField(item, field);
}
rowIndex++;
}
return(output);
}
// создание массива данных со второй строкой в каждой записи где указан комментарий
object[,] BuildDataArrayWithTwoRowPerRecord()
{
// -1 т.к. исключен столбец Комментарий
// +1 т.к. первый столбец номер по порядку
int countOfColumns = fieldsAndFormats.Count - 1 + 1;
int numberOfRecords = collection.Count();
// +1 т.к. первая строка шапка
object[,] output = new object[(2 * numberOfRecords) + 1, countOfColumns];
// for (int i = 0; i < numberOfRows + 1; i++)
// output[i] = new object[numberOfColumns];
output[0, 0] = "№ п/п";
int ind = 1;
foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats)
{
if (field.Key == commentFieldName)
{
continue;
}
else
{
output[0, ind++] = Utils.ConvertFromTitleCase(field.Key);
}
}
callBack?.Invoke("заполнение таблицы");
int rowIndex = 1;
int recordIndex = 1;
foreach (T item in collection)
{
output[rowIndex, 0] = recordIndex;
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
if (field == commentFieldName)
{
continue;
}
output[rowIndex, ind++] = GetValueOfField(item, field);
}
string commentValue = GetValueOfField(item, commentFieldName).ToString().Trim().Replace('\n', '\t');
output[rowIndex + 1, 1] = commentValue;
rowIndex += 2;
recordIndex++;
}
return(output);
}
void ApplyDataFormatForSheet(Excel.Worksheet xlWorksheet, Excel.Range rangeToSetData, bool thisWorkSheetIsForPrint)
{
// -1 т.к. исключен столбец Комментарий
// +1 т.к. первый столбец номер по порядку
int countOfColumns = hasCommentColumn ? fieldsAndFormats.Count - 1 + 1 : fieldsAndFormats.Count + 1;
callBack?.Invoke("установка формата данных");
int rowIndex = 2; // 1 - шапка таблицы
int ind = 1;
int recordIndex = 1;
foreach (T item in collection)
{
rangeToSetData[rowIndex, 0 + 1].NumberFormat = "0";
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
if (thisWorkSheetIsForPrint && field == commentFieldName)
{
continue;
}
try
{
object value = string.Empty;
if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) == false)
{
string format = string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) ? "General" : fieldsAndFormats[field].ExcelFormat;
rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = format;
}
else if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ContentDisplayFormat) == false)
{
rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = "General";
}
}
catch (Exception e)
{
#if DEBUG
App.ToDebug(e);
#endif
}
finally
{
ind++;
}
}
if (thisWorkSheetIsForPrint && hasCommentColumn)
{
// ячейка с номером по порядку
Excel.Range rng = rangeToSetData[rowIndex, 1];
rng = rng.Resize(2, 1);
rng.Merge();
rng.HorizontalAlignment = XlHAlign.xlHAlignLeft;
rng.VerticalAlignment = XlVAlign.xlVAlignCenter;
// ячейка с комментарием
rng = rangeToSetData[rowIndex + 1, 2].Resize(1, countOfColumns - 1);
rng.Merge();
rng.WrapText = true;
rng.NumberFormat = "@";
rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns);
rng.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
rng.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Gray);
rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
rng.Borders[XlBordersIndex.xlInsideVertical].Color = utils.Color.ToDouble(System.Drawing.Color.Gray);
if (recordIndex % 2 == 0)
{
rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns);
rng.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.WhiteSmoke);
}
rowIndex += 2;
}
else
{
rowIndex++;
}
recordIndex++;
}
}
void Process(object[,] outputData1, object[,] outputData2)
{
try
{
callBack?.Invoke("создание книги MS Excel");
excelApplication = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false,
};
utils = new NetOffice.OfficeApi.Tools.Contribution.CommonUtils(excelApplication);
xlWorkbook = excelApplication.Workbooks.Add();
xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Range all = xlWorksheet.Range("A1");
Excel.Range header = xlWorksheet.Range("A1");
header.WrapText = true;
header.Resize(1, numberOfColumns).Merge();
using (Excel.Font font = header.Font)
{
font.Size = 14;
font.Bold = true;
}
header.HorizontalAlignment = XlHAlign.xlHAlignCenter;
header.VerticalAlignment = XlVAlign.xlVAlignCenter;
double oneRowHeight = (double)header.RowHeight;
int rowsCount = reportTitle.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length;
header.RowHeight = oneRowHeight * rowsCount * 1.1;
header.Value2 = reportTitle;
Excel.Range description = xlWorksheet.Range("A2");
description.Resize(1, numberOfColumns).Merge();
description.WrapText = true;
using (Excel.Font font = description.Font)
{
font.Size = 12;
font.Italic = true;
}
description.HorizontalAlignment = XlHAlign.xlHAlignLeft;
description.VerticalAlignment = XlVAlign.xlVAlignCenter;
oneRowHeight = (double)description.RowHeight;
rowsCount = reportDescription.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length;
description.RowHeight = oneRowHeight * rowsCount * 1.1;
description.Value2 = reportDescription;
callBack?.Invoke("настройка книги MS Excel");
if (hasCommentColumn)
{
// создание копии листа
xlWorksheet.Copy(xlWorksheet);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
xlWorksheet2 = (Excel.Worksheet)xlWorkbook.Sheets[2];
xlWorksheet.Name = "Данные";
xlWorksheet2.Name = "Для печати";
Excel.Range data2 = xlWorksheet2.Range("A3").Resize((2 * numberOfRows) + 1, numberOfColumns - 1);
data2.VerticalAlignment = VerticalAlignment.Center;
data2.NumberFormat = "@";
data2.Value = outputData2;
ApplyDataFormatForSheet(xlWorksheet2, data2, true);
Excel.Range tableHeader = xlWorksheet2.Range("A3").Resize(1, numberOfColumns - 1);
tableHeader.WrapText = true;
using (Excel.Font font = tableHeader.Font)
{
font.Size = 12;
font.Bold = true;
}
header.HorizontalAlignment = XlHAlign.xlHAlignCenter;
header.VerticalAlignment = XlVAlign.xlVAlignCenter;
tableHeader.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.LightGray);
tableHeader.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Black);
}
Excel.Range data = xlWorksheet.Range("A3").Resize(numberOfRows + 1, numberOfColumns);
data.NumberFormat = "@";
data.Value = outputData1;
ApplyDataFormatForSheet(xlWorksheet, data, false);
xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, data,
Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "DataTable";
xlWorksheet.ListObjects["DataTable"].TableStyle = "TableStyleMedium6";
SetupWorkSheet(xlWorksheet, numberOfColumns);
if (hasCommentColumn)
{
SetupWorkSheet(xlWorksheet2, numberOfColumns - 1);
}
callBack?.Invoke("сохранение книги MS Excel");
xlWorkbook.SaveAs(fileName);
xlWorkbook.Close(false);
callBack?.Invoke("завершение");
logger?.Info($"Export >> файл сформирован и сохранен: '{fileName}'");
}
catch (Exception e)
{
exception = e;
#if DEBUG
App.ToDebug(e);
#endif
App.ShowError("Произошла ошибка:\n" + App.GetExceptionDetails(e));
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = defaultCulture;
excelApplication.Quit();
excelApplication.ScreenUpdating = true;
excelApplication.DisplayAlerts = true;
if (exception != null)
{
if (excelApplication.Workbooks.Any())
{
foreach (Excel.Workbook workbook in excelApplication.Workbooks.Where(x => !x.IsDisposed))
{
workbook.Close(false, System.Reflection.Missing.Value, Missing.Value);
workbook.Dispose();
}
}
if (excelApplication.IsDisposed == false)
{
excelApplication.Quit();
excelApplication.Dispose();
}
}
}
}
void SetupWorkSheet(Excel.Worksheet xlWorksheet, int numberOfColumns)
{
foreach (int i in Enumerable.Range(1, numberOfColumns))
{
xlWorksheet.Columns[i].AutoFit();
}
Excel.PageSetup ps = xlWorksheet.PageSetup;
ps.PaperSize = XlPaperSize.xlPaperA4;
ps.Orientation = XlPageOrientation.xlLandscape;
ps.Zoom = false;
ps.FitToPagesWide = 1;
ps.FitToPagesTall = false;
ps.PrintTitleRows = "$3:$3";
ps.LeftMargin = excelApplication.CentimetersToPoints(1.0);
ps.RightMargin = excelApplication.CentimetersToPoints(1.0);
ps.TopMargin = excelApplication.CentimetersToPoints(2.0);
ps.BottomMargin = excelApplication.CentimetersToPoints(1.0);
ps.HeaderMargin = excelApplication.CentimetersToPoints(0.6);
ps.FooterMargin = excelApplication.CentimetersToPoints(0.6);
ps.CenterHorizontally = true;
ps.RightHeader = DateTime.Now.ToString("D", defaultCulture);
ps.CenterFooter = "Страница &P / &N";
ps.PrintArea = xlWorksheet.Range("A1").Resize(numberOfRows + 3, numberOfColumns).Address;
}
void OpenCreatedWorkBook()
{
try
{
callBack?.Invoke("открытие созданной книги MS Excel");
using System.Diagnostics.Process p = new System.Diagnostics.Process
{
StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true,
},
};
p.Start();
// System.Diagnostics.Process.Start(fileName);
}
catch (Exception e)
{
#if DEBUG
App.ToDebug(e);
#endif
App.ShowError("Произошла ошибка при открытии файла:\n" + App.GetExceptionDetails(e));
}
}
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// font action
workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.Range("A1").Font.Name = "Arial";
workSheet.Range("A1").Font.Size = 8;
workSheet.Range("A1").Font.Bold = true;
workSheet.Range("A1").Font.Italic = true;
workSheet.Range("A1").Font.Underline = true;
workSheet.Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.Range("A3").Value = "Times New Roman Size:10";
workSheet.Range("A3").Font.Name = "Times New Roman";
workSheet.Range("A3").Font.Size = 10;
workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.Range("A5").Font.Name = "Comic Sans MS";
workSheet.Range("A5").Font.Size = 12;
workSheet.Range("A5").WrapText = true;
workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.Range("A7").Value = "xlHAlignLeft";
workSheet.Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 25;
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example02{1}", _hostApplication.RootDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
_hostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn Application msg boxes
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// the given thread culture in all NetOffice calls are stored in NetOffice.Settings.
// you can change the culture of course. Default is en-us.
CultureInfo cultureInfo = NetOffice.Settings.Default.ThreadCulture;
string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);
workSheet.Range("A1").Value = "Type";
workSheet.Range("B1").Value = "Value";
workSheet.Range("C1").Value = "Formatted " + Pattern1;
workSheet.Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.Range("A3").Value = "Integer";
workSheet.Range("B3").Value = integerValue;
workSheet.Range("C3").Value = integerValue;
workSheet.Range("C3").NumberFormat = Pattern1;
workSheet.Range("D3").Value = integerValue;
workSheet.Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.Range("A4").Value = "double";
workSheet.Range("B4").Value = doubleValue;
workSheet.Range("C4").Value = doubleValue;
workSheet.Range("C4").NumberFormat = Pattern1;
workSheet.Range("D4").Value = doubleValue;
workSheet.Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.Range("A5").Value = "float";
workSheet.Range("B5").Value = floatValue;
workSheet.Range("C5").Value = floatValue;
workSheet.Range("C5").NumberFormat = Pattern1;
workSheet.Range("D5").Value = floatValue;
workSheet.Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.Range("A6").Value = "Decimal";
workSheet.Range("B6").Value = decimalValue;
workSheet.Range("C6").Value = decimalValue;
workSheet.Range("C6").NumberFormat = Pattern1;
workSheet.Range("D6").Value = decimalValue;
workSheet.Range("D6").NumberFormat = Pattern2;
workSheet.Range("A9").Value = "DateTime";
workSheet.Range("B10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = NetOffice.Settings.Default.ThreadCulture.DateTimeFormat.ShortTimePattern;
// string
workSheet.Range("A14").Value = "String";
workSheet.Range("B14").Value = "This is a sample String";
workSheet.Range("B14").NumberFormat = "@";
// number as string
workSheet.Range("B15").Value = "513";
workSheet.Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
// save the book
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example03", Excel.Tools.DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void RunExample()
{
// start excel and turn off msg boxes
Excel.Application excelApplication = COMObject.Create <Excel.Application>();
excelApplication.DisplayAlerts = false;
// create a utils instance, no need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// font action
workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.Range("A1").Font.Name = "Arial";
workSheet.Range("A1").Font.Size = 8;
workSheet.Range("A1").Font.Bold = true;
workSheet.Range("A1").Font.Italic = true;
workSheet.Range("A1").Font.Underline = true;
workSheet.Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.Range("A3").Value = "Times New Roman Size:10";
workSheet.Range("A3").Font.Name = "Times New Roman";
workSheet.Range("A3").Font.Size = 10;
workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.Range("A5").Font.Name = "Comic Sans MS";
workSheet.Range("A5").Font.Size = 12;
workSheet.Range("A5").WrapText = true;
workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.Range("A7").Value = "xlHAlignLeft";
workSheet.Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 25;
// save the book - utils want build the filename for us
string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example02", DocumentFormat.Normal);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show end dialog
HostApplication.ShowFinishDialog(null, workbookFile);
}
public void OpenExcelFile()
{
if (excelApp == null || excelApp.IsDisposed)
{
excelApp = new NetOffice.ExcelApi.Application();
fileExtension = GetDefaultExtension(excelApp);
workbookFile = string.Format("{0}FreqResult{1}", filespath, fileExtension);
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excelApp.DefaultSheetDirection = 0;
if (excelApp == null)
{
Console.WriteLine("EXCEL could not be started. aCheck that your office installation and project references are correct.");
return;
}
excelApp.AlertBeforeOverwriting = false;
excelApp.DisplayAlerts = false;
excelApp.Visible = false;
if (!System.IO.File.Exists(workbookFile))
{
workBook = excelApp.Workbooks.Add();
workBook.Worksheets.Add();
((Worksheet)workBook.Worksheets[1]).Name = "Loop 1";
((Worksheet)workBook.Worksheets[2]).Name = "Loop 2";
((Worksheet)workBook.Worksheets[3]).Name = "Loop 3";
((Worksheet)workBook.Worksheets[4]).Name = "Loop 4";
workBook.SaveAs(workbookFile);
}
else
{
workBook = excelApp.Workbooks.Open(workbookFile, updateLinks: 0, readOnly: false);
}
}
for (int i = 0; i < LoopWorksheet.Length; i++)
{
LoopWorksheet[i] = ((Worksheet)workBook.Worksheets[i + 1]);
LoopWorksheet[i].Range("A1:D1000").Clear();
if (((ChartObjects)LoopWorksheet[i].ChartObjects()).Count > 0)
((ChartObjects)LoopWorksheet[i].ChartObjects()).Delete();
}
}
private void ExecuteEvents(Timeline timeline, TimelineHandler handler)
{
try
{
foreach (TimelineEvent timelineEvent in handler.TimeLineEvents)
{
try
{
_log.Trace($"Excel event - {timelineEvent}");
WorkingHours.Is(handler);
if (timelineEvent.DelayBefore > 0)
{
Thread.Sleep(timelineEvent.DelayBefore);
}
if (timeline != null)
{
var pids = ProcessManager.GetPids(ProcessManager.ProcessNames.Excel).ToList();
if (pids.Count > timeline.TimeLineHandlers.Count(o => o.HandlerType == HandlerType.Excel))
{
return;
}
}
// start excel and turn off msg boxes
Excel.Application excelApplication = new Excel.Application
{
DisplayAlerts = false,
Visible = true
};
try
{
excelApplication.WindowState = XlWindowState.xlMinimized;
foreach (Excel.Workbook item in excelApplication.Workbooks)
{
item.Windows[1].WindowState = XlWindowState.xlMinimized;
}
}
catch (Exception e)
{
_log.Trace($"Could not minimize: {e}");
}
// create a utils instance, not need for but helpful to keep the lines of code low
CommonUtils utils = new CommonUtils(excelApplication);
_log.Trace("Excel adding workbook");
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
_log.Trace("Excel adding worksheet");
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium,
XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5")
.Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5")
.Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.Weight = 4;
workSheet.Range("$D2:$D5")
.Borders[(Excel.Enums.XlBordersIndex)XlBordersIndex.xlInsideHorizontal]
.Color = utils.Color.ToDouble(Color.Black);
var writeSleep = ProcessManager.Jitter(100);
Thread.Sleep(writeSleep);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
string rand = RandomFilename.Generate();
string dir = timelineEvent.CommandArgs[0].ToString();
if (dir.Contains("%"))
{
dir = Environment.ExpandEnvironmentVariables(dir);
}
if (Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string path = $"{dir}\\{rand}.xlsx";
//if directory does not exist, create!
_log.Trace($"Checking directory at {path}");
DirectoryInfo f = new FileInfo(path).Directory;
if (f == null)
{
_log.Trace($"Directory does not exist, creating directory at {f.FullName}");
Directory.CreateDirectory(f.FullName);
}
try
{
if (File.Exists(path))
{
File.Delete(path);
}
}
catch (Exception e)
{
_log.Error($"Excel file delete exception: {e}");
}
_log.Trace($"Excel saving to path - {path}");
workBook.SaveAs(path);
FileListing.Add(path);
Report(handler.HandlerType.ToString(), timelineEvent.Command,
timelineEvent.CommandArgs[0].ToString());
if (timelineEvent.DelayAfter > 0)
{
//sleep and leave the app open
_log.Trace($"Sleep after for {timelineEvent.DelayAfter}");
Thread.Sleep(timelineEvent.DelayAfter - writeSleep);
}
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
excelApplication = null;
workBook = null;
workSheet = null;
try
{
Marshal.ReleaseComObject(excelApplication);
}
catch { }
try
{
Marshal.FinalReleaseComObject(excelApplication);
}
catch { }
GC.Collect();
}
catch (Exception e)
{
_log.Error($"Excel handler exception: {e}");
}
finally
{
Thread.Sleep(5000);
}
}
}
catch (Exception e)
{
_log.Error(e);
}
finally
{
KillApp();
_log.Trace($"Excel closing...");
}
}
public ExportBuildingDataAssessorFileResponse ExportBuildingDataAssessorFile(ExportBuildingDataAssessorFileRequest request) {
ExportBuildingDataAssessorFileResponse response = null;
Excel.Application excelApplication = null;
try {
//Directory.CreateDirectory(Path.GetDirectoryName(request.DataFilePath));
response = new ExportBuildingDataAssessorFileResponse();
//write to excel
using (excelApplication = new Excel.Application()) {
excelApplication.DisplayAlerts = false;
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];
workSheet.Name = string.Format("{0} {1}","BldgData Assessor", DateTime.Now.ToString("yyyyMMdd"));
int counter = 0;
foreach (BuildingDataAssessor buildingDataAssessor in request.BuildingDataAssessors) {
counter++;
if (counter.Equals(1)) {
//add headers
continue;
}
//TODO: add column fields from incoming file
//add details
workSheet.Cells[counter, 1].Value = "hello 33";
workSheet.Cells[counter, 2].Value = "world";
}
//save excel
workBook.SaveAs(request.SourceFilePath);
//quit excel
excelApplication.Quit();
}
//set result
if (File.Exists(request.SourceFilePath)) {
response.Result = new Result() {
IsSuccess = true,
Message = string.Format("{0}\n{1}",
"The data is exported successfully at the following path:",
request.SourceFilePath) };
}
else {
response.Result = new Result() {
IsSuccess = false,
Message = string.Format("{0}\n{1}",
"Fail in exporting data to:",
request.SourceFilePath) };
}
return response;
}
finally {
response = null;
if (excelApplication != null)
excelApplication.Dispose();
excelApplication = null;
}
}