private void UseColorUtils()
{
// Colors in excel use a double representation
// NetOffice color utils help to deal with them
if (Application.Workbooks.Count > 0 && Application.Workbooks[1].Worksheets.Count > 0)
{
Excel.Worksheet sheet = Application.Workbooks[1].Worksheets[1] as Excel.Worksheet;
double setColor = Utils.Color.ToDouble(Color.Red);
sheet.Range("A1:B4").Interior.Color = setColor;
Color getColor = Utils.Color.ToColor(sheet.Range("A1:B4").Interior.Color);
}
}
private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Datum";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Columns1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return(workSheet.Range("$B2:$E6"));
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
for (int i = 1; i <= 200; i++)
{
sheet.Cells[i, 1].Value = string.Format("Test {0}", i);
sheet.Range(string.Format("$B{0}", i)).Value = 42.3;
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
private void listViewSearchResults_DoubleClick(object sender, EventArgs e)
{
try
{
if (listViewSearchResults.SelectedItems.Count > 0)
{
Excel.Worksheet activeSheet = Addin.Application.ActiveSheet as Excel.Worksheet;
Excel.Range activeCell = Addin.Application.ActiveCell;
if (null != activeCell)
{
int rowIndex = activeCell.Row;
int columnIndex = activeCell.Column;
string targetRangeAddress = CalculateRangeArea(rowIndex, columnIndex, 7);
Customer selectedCustomer = listViewSearchResults.SelectedItems[0].Tag as Customer;
Excel.Range targetRange = activeSheet.Range(targetRangeAddress);
targetRange.Value2 = ToStringArray(selectedCustomer);
targetRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
activeSheet.Columns[targetRange.Column].AutoFit();
activeCell.Dispose();
activeSheet.Dispose();
}
}
}
catch (Exception exception)
{
MessageBox.Show(this, exception.Message, "An error occured", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;
foreach (Excel.Range item in sheet.Range("$A1:$B100"))
{
item.Value = DateTime.Now;
}
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
private static void AddDataRows(Excel.Worksheet sheet, DataSet dataset, object[,] tempArray)
{
var range = sheet.Range(sheet.Cells[1, 1],
sheet.Cells[(dataset.Tables["dataTable"].Rows.Count), (dataset.Tables["dataTable"].Columns.Count)]);
sheet.Name = "Relatório";
range.Value = tempArray;
}
static void Main(string[] args)
{
try
{
//
Console.WriteLine("NetOffice Utils Concept Test");
Console.WriteLine("0 Milliseconds trace values is not a bug - its just to fast\r\n");
NetOffice.Settings.Default.PerformanceTrace.Enabled = true;
NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
// Criteria 1
// Enable performance trace in excel generaly. set interval limit to 100 to see all actions there need >= 100 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100;
// Criteria 2
// Enable additional performance trace for all members of Range in excel. set interval limit to 20 to see all actions there need >=20 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].IntervalMS = 20;
// Criteria 3
// Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0 to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0;
// Criteria 4
// Enable additional performance trace for Range this[] indexer in excel. set interval limit to 0 to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].IntervalMS = 0;
Excel.Application application = new Excel.ApplicationClass();
application.DisplayAlerts = false;
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet;
for (int i = 1; i <= 5; i++)
{
Excel.Range range = sheet.Range("A" + i.ToString());
range.Value = "Test123";
range[1, 1].Value = "Test234";
}
application.Quit();
application.Dispose();
Console.WriteLine("\r\nTest passed");
Console.ReadKey();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
Console.ReadKey();
}
}
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);
}
private static void Thread2Method(object mre)
{
Excel.Worksheet sheet = _application.ActiveSheet as Excel.Worksheet;
foreach (Excel.Range range in sheet.Range("A1:B200"))
{
Excel.Workbook book = range.Application.ActiveWorkbook;
foreach (object item in book.Sheets)
{
Excel.Worksheet otherSheet = item as Excel.Worksheet;
Excel.Range rng = otherSheet.Cells[1, 1];
}
}
(mre as ManualResetEvent).Set();
}
//private void RemoveUserInterface()
//{
// // _excelApplication.CommandBars("Cell").Reset();
//}
#region UI Trigger
/// <summary>
/// Click event trigger from created buttons. incoming call comes from excel application thread.
/// </summary>
/// <param name="Ctrl"></param>
/// <param name="CancelDefault"></param>
private void commandBarBtn_ClickEvent(NetOffice.OfficeApi.CommandBarButton Ctrl, ref bool CancelDefault)
{
try
{
Excel.Worksheet workSheet = (Excel.Worksheet)_excelApplication.ActiveWorkbook.ActiveSheet;
workSheet.Cells[1, 1].Value = "ExcelVersion";
workSheet.Cells[1, 2].Value = _excelApplication.Version;
// create a utils instance, not need for but helpful to keep the lines of code low
NetOffice.ExcelApi.Tools.CommonUtils utils = new NetOffice.ExcelApi.Tools.CommonUtils(_excelApplication);
// 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, NetOffice.ExcelApi.Enums.XlBorderWeight.xlMedium, NetOffice.ExcelApi.Enums.XlColorIndex.xlColorIndexAutomatic);
MessageBox.Show(_excelApplication.Version, "ExcelVersion", MessageBoxButtons.OK, MessageBoxIcon.Information);
Ctrl.Dispose();
}
catch (Exception exception)
{
string message = string.Format("An error occured.{0}{0}{1}", Environment.NewLine, exception.Message);
MessageBox.Show(message, _progId, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
workSheet.Cells[1, j + 1].Value = dt.Columns[j].ColumnName;
}
//Populate rest of the data
int iRow = 2;
for (int rowNo = 0; rowNo < dt.Rows.Count; rowNo++)
{
for (int colNo = 0; colNo < dt.Columns.Count; colNo++)
{
workSheet.Cells[iRow, colNo + 1].Value = dt.Rows[rowNo][colNo].ToString();
}
iRow++;
}
return(workSheet.Range("$B2:$E6"));
}
public Xl.Worksheet WriteInterchanges(Xl.Worksheet worksheet, IEnumerable <Interchange> interchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "Interchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"PrefectureCode", "TempInterchangeId", "IC_Kana", "IC_Kanji", "Highway", "Latitude", "Longitude", "Data_Date"
};
var data = new List <List <object> > {
header
}.Concat(interchanges.Select(r =>
new List <object> {
r.PrefectureCode, r.TempInterchangeId, r.IC_Kana, r.IC_Kanji, r.HighwayDisplay, r.Latitude, r.Longitude
, DateTime.SpecifyKind(r.DataDate, DateTimeKind.Utc).ToLocalTime()
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[interchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.Interchange";
opList.TableStyle = "TableStyleLight9";
opList.ListColumns[8].DataBodyRange.NumberFormatLocal = Format_DateTime;
var colorRange = AddinContext.ExcelApp.Union(opList.ListColumns[1].DataBodyRange,
opList.ListColumns[8].DataBodyRange);
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}
private static void AddDataRows(Excel.Worksheet sheet)
{
sheet.Name = "Financial report";
var reportResult = GetData();
var value = new string[reportResult.Length, 5];
for (var row = 0; row < reportResult.Length; row++)
{
Console.WriteLine("Importing:\n---------------");
for (int col = 0; col < reportResult[row].Length; col++)
{
value[row, col] = reportResult[row][col];
Console.WriteLine(reportResult[row][col]);
}
}
var range = sheet.Range(sheet.Cells[4, 1],
sheet.Cells[reportResult.Length + 3, reportResult[1].Length]);
range.Value = value;
Console.WriteLine("Ready!!!");
}
public void Run()
{
// Enable and trigger trace alert
NetOffice.Settings.Default.PerformanceTrace.Enabled = true;
NetOffice.Settings.Default.PerformanceTrace.Alert += delegate(NetOffice.PerformanceTrace sender, NetOffice.PerformanceTrace.PerformanceAlertEventArgs args)
{
Console.WriteLine("{0} {1}:{2} in {3} Milliseconds ({4} Ticks)", args.CallType, args.EntityName, args.MethodName, args.TimeElapsedMS, args.Ticks);
};
// Criteria 1
// Enable performance trace in excel generaly. set interval limit to 100ms to see all actions there need >= 100 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100;
// Criteria 2
// Enable additional performance trace for all members of WorkSheet in excel. set interval limit to 20ms to see all actions there need >=20 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].IntervalMS = 20;
// Criteria 3
// Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0ms to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0;
// do some stuff
Excel.Application application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet;
for (int i = 1; i <= 5; i++)
{
Excel.Range range = sheet.Range("A" + i.ToString());
range.Value = "Test123";
range[1, 1].Value = "Test234";
}
application.Quit();
application.Dispose();
HostApplication.ShowFinishDialog();
}
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 Xl.Worksheet WriteHighwayInterchanges(Xl.Worksheet worksheet, IEnumerable <HighwayInterchange> highwayInterchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "HighwayInterchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"HighwayId", "Highway", "Interchange", "SortOrder", "Latitude", "Longitude"
};
var data = new List <List <object> > {
header
}.Concat(highwayInterchanges.Select(r =>
new List <object> {
r.TempHighwayId, r.HighwayKanji, r.Interchange.IC_Kanji, r.SortOrder, r.Interchange.Latitude, r.Interchange.Longitude
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[highwayInterchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.HighwayInterchange";
opList.TableStyle = "TableStyleLight9";
var colorRange = opList.ListColumns[1].DataBodyRange;
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}
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 = 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 TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off Application msg boxes
application = new Excel.Application();
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
/* some kind of numerics */
// the given thread culture in all latebinding calls are stored in NetOffice.Settings.
// you can change the culture. 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.get_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 = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = cultureInfo.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = cultureInfo.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = cultureInfo.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = cultureInfo.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = cultureInfo.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();
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.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];
// 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 static void prjTb(System.Data.DataTable fnlCns, System.Data.DataTable nwPrm, string prjNme)
{
string str;
int num;
DataRow row2;
double num2;
int num4;
double num5;
if (prjNme.Length > 0x17)
{
str = prjNme.Substring(0, 0x17);
}
else
{
str = prjNme;
}
List<string> list = new List<string>();
foreach (DataColumn column in fnlCns.Columns)
{
if (((column.ColumnName == "Prior Premium") || (column.ColumnName == "Prior Subsidy")) || (column.ColumnName == "Prior EE Contribution"))
{
list.Add(column.ColumnName);
}
if (column.ColumnName.Contains("Prior") && column.ColumnName.Contains("Surcharge"))
{
list.Add(column.ColumnName);
}
}
System.Data.DataTable table = new System.Data.DataTable();
for (num = 0; num < list.Count; num++)
{
table.Columns.Add(list[num]);
}
foreach (DataRow row in fnlCns.Rows)
{
row2 = table.NewRow();
foreach (DataColumn column in table.Columns)
{
row2[column.ColumnName] = row[column.ColumnName];
}
table.Rows.Add(row2);
}
List<double> list2 = new List<double>();
foreach (DataColumn column in table.Columns)
{
num2 = 0.0;
foreach (DataRow row in table.Rows)
{
num2 += double.Parse(row[column.ColumnName].ToString());
}
list2.Add(num2);
}
List<string> list3 = new List<string>();
foreach (DataColumn column in fnlCns.Columns)
{
if (((column.ColumnName == "New Premium") || (column.ColumnName == "New Subsidy")) || (column.ColumnName == "New EE Contribution"))
{
list3.Add(column.ColumnName);
}
if (column.ColumnName.Contains("New") && column.ColumnName.Contains("Surcharge"))
{
list3.Add(column.ColumnName);
}
}
System.Data.DataTable table2 = new System.Data.DataTable();
for (num = 0; num < list3.Count; num++)
{
table2.Columns.Add(list3[num]);
}
foreach (DataRow row in fnlCns.Rows)
{
row2 = table2.NewRow();
foreach (DataColumn column in table2.Columns)
{
row2[column.ColumnName] = row[column.ColumnName];
}
table2.Rows.Add(row2);
}
List<double> list4 = new List<double>();
foreach (DataColumn column in table2.Columns)
{
num2 = 0.0;
foreach (DataRow row in table2.Rows)
{
num2 += double.Parse(row[column.ColumnName].ToString());
}
list4.Add(num2);
}
bool flag = false;
foreach (Worksheet worksheet in oWB.Worksheets)
{
if (worksheet.Name.Equals(str + " Summary"))
{
flag = true;
}
}
if (flag)
{
throw new Exception("Projection with same name already exists");
}
oSheet = (Worksheet) shts.Add();
oSheet.Name = str + " Summary";
oSheet.Activate();
int count = 0;
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
NetOffice.ExcelApi.Range entireColumn = null;
NetOffice.ExcelApi.Range range2 = null;
for (num = 0; num < table.Columns.Count; num++)
{
oSheet.Cells[5, num + 1].Value = table.Columns[num].ColumnName;
oSheet.Cells[5, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
oSheet.Cells[6, num + 1].Value = list2[num];
oSheet.Cells[6, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
oSheet.Cells[6, num + 1].NumberFormat = "$ #,###,###.00";
range2 = oSheet.Cells[6, num + 1];
entireColumn = range2.EntireColumn;
entireColumn.AutoFit();
}
for (num = 0; num < table2.Columns.Count; num++)
{
oSheet.Cells[8, num + 1].Value = table2.Columns[num].ColumnName;
oSheet.Cells[8, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
oSheet.Cells[9, num + 1].Value = list4[num];
oSheet.Cells[9, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
oSheet.Cells[9, num + 1].NumberFormat = "$ #,###,###.00";
range2 = oSheet.Cells[6, num + 1];
entireColumn = range2.EntireColumn;
entireColumn.AutoFit();
}
oSheet.Cells[1, 1].Value = prjNme + " Summary";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts.Add();
oSheet.Activate();
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
oSheet.Name = str + " Rates";
List<int> list5 = new List<int>();
for (num = 0; num < nwPrm.Columns.Count; num++)
{
oSheet.Cells[4, num + 1].Value = nwPrm.Columns[num].ColumnName;
oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
if ((nwPrm.Columns[num].ColumnName == "AV") || (nwPrm.Columns[num].ColumnName == "Factor"))
{
list5.Add(num);
}
}
object[,] objArray = new object[nwPrm.Rows.Count, nwPrm.Columns.Count];
for (num = 0; num < nwPrm.Rows.Count; num++)
{
num4 = 0;
while (num4 < nwPrm.Columns.Count)
{
try
{
objArray[num, num4] = double.Parse(nwPrm.Rows[num][num4].ToString());
}
catch
{
objArray[num, num4] = nwPrm.Rows[num][num4];
}
num4++;
}
}
NetOffice.ExcelApi.Range range3 = oSheet.Cells[5, 1];
NetOffice.ExcelApi.Range range4 = oSheet.Cells[nwPrm.Rows.Count + 4, nwPrm.Columns.Count];
oSheet.Range(range3, range4).Value = objArray;
for (num = 0; num < nwPrm.Columns.Count; num++)
{
range2 = oSheet.Cells[4, num + 1];
try
{
num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
if ((oSheet.Cells[4, num + 1].Value.ToString() != "AV") && (oSheet.Cells[4, num + 1].Value.ToString() != "Factor"))
{
entireColumn = range2.EntireColumn;
entireColumn.NumberFormat = "$ #,###.00";
}
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
catch
{
entireColumn = range2.EntireColumn;
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
}
oSheet.Cells[1, 1].Value = prjNme + " New Rates and Contributions";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts.Add();
oSheet.Activate();
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
oSheet.Name = str + " Detail";
for (num = 0; num < fnlCns.Columns.Count; num++)
{
oSheet.Cells[4, num + 1].Value = fnlCns.Columns[num].ColumnName;
oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
}
objArray = new object[fnlCns.Rows.Count, fnlCns.Columns.Count];
for (num = 0; num < fnlCns.Rows.Count; num++)
{
for (num4 = 0; num4 < fnlCns.Columns.Count; num4++)
{
try
{
objArray[num, num4] = double.Parse(fnlCns.Rows[num][num4].ToString());
}
catch
{
objArray[num, num4] = fnlCns.Rows[num][num4];
}
}
}
range3 = oSheet.Cells[5, 1];
range4 = oSheet.Cells[fnlCns.Rows.Count + 4, fnlCns.Columns.Count];
oSheet.Range(range3, range4).Value = objArray;
for (num = 0; num < fnlCns.Columns.Count; num++)
{
range2 = oSheet.Cells[4, num + 1];
try
{
num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
entireColumn = range2.EntireColumn;
if (num > 1)
{
entireColumn.NumberFormat = "$ #,###.00";
}
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
catch
{
entireColumn = range2.EntireColumn;
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
}
oSheet.Cells[1, 1].Value = prjNme + " Detailed Census and Financial Information";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts[str + " Summary"];
oSheet.Select();
}
public static void runScenarioSummary()
{
runCalcAction(delegate {
AxCalcEngineAPI.AxCalcEngineAPI.Initialize();
shts = oWB.Worksheets;
oSheet = (Worksheet) shts["Projection Input Options"];
string ancPln = (string) oSheet.Range("anchorPlan").Value;
string option = (string) oSheet.Range("subType").Value;
string mgOpt = (string) oSheet.Range("migType").Value;
string str4 = (string) oSheet.Range("projNme").Value;
string inMode = (string) oSheet.Range("modal").Value;
string outMode = (string) oSheet.Range("modalOut").Value;
string str7 = (string) oSheet.Range("clientNme").Value;
string str8 = oSheet.Range("clientID").Value.ToString();
string str9 = "";
double modalAdj = Utility.modalConvert(inMode, outMode);
if ((string.IsNullOrEmpty(str7) || string.IsNullOrEmpty(str8)) || (str8.Length != 8))
{
throw new Exception("Enter valid client name and ID");
}
if (string.IsNullOrEmpty(str4))
{
throw new Exception("You must enter a projection name");
}
string displayName = "";
try
{
displayName = AxCalcEngineAPI.AxCalcEngineAPI._user.DisplayName;
}
catch
{
throw new Exception("Establishing a Connection");
}
if (displayName.Equals("Offline User"))
{
throw new Exception("You must be connected to the Aon Network to run Ax Model");
}
RatingGroups groups = new RatingGroups("Rating Groups", true);
PriorSurcharge surcharge = new PriorSurcharge("Rating Groups", modalAdj);
NewSurcharge surcharge2 = new NewSurcharge("Rating Groups", modalAdj);
PriorPremiums premiums = new PriorPremiums("Prior Premiums");
NewPremiums premiums2 = new NewPremiums("New Premiums");
Census census = new Census("Census");
System.Data.DataTable first = groups.RFactors;
System.Data.DataTable prm = Utility.convertListToDataTable(premiums2.NewPrmLst);
System.Data.DataTable table3 = Utility.convertListToDataTable(premiums.OldPrmLst);
prm = Utility.mdlPrm(prm, modalAdj);
table3 = Utility.mdlPrm(table3, modalAdj);
System.Data.DataTable cns = Utility.convertListToDataTable(census.CnLst);
List<System.Data.DataTable> prSurChrg = surcharge.PSCharge;
List<System.Data.DataTable> nwSurChrg = surcharge2.NSCharge;
System.Data.DataTable second = Utility.findPremiumCarrier(prm, str9);
System.Data.DataTable dt = Utility.findPremiumCarrier(prm, ancPln);
System.Data.DataTable table7 = Utility.Join(first, second);
System.Data.DataTable anc = Utility.getAnchorPlans(dt, ancPln, groups.bsCvTr);
System.Data.DataTable nwPrm = Utility.calcBaseSubsidy(table7, anc, option, modalAdj);
System.Data.DataTable fnlCns = Utility.SurChrgCombine(Utility.mrgCnsPrm(cns, table3, nwPrm, mgOpt), prSurChrg, nwSurChrg);
groups = null;
surcharge = null;
surcharge2 = null;
premiums = null;
premiums2 = null;
census = null;
prjTb(fnlCns, nwPrm, str4);
AxCalcEngineAPI.AxCalcEngineAPI.sendCalcLog(str7, str8, "http://axmodellogger.azurewebsites.net/Default.aspx", "AxModel_0.9");
});
}
public static void writeColumn(Worksheet xlWs, object[] Column, int ColumnNo, int startRow)
{
try
{
object[,] objArray = new object[Column.Length, 1];
for (int i = 0; i < Column.Length; i++)
{
objArray[i, 0] = Column[i];
}
Range range = xlWs.Cells[startRow, ColumnNo];
Range range2 = xlWs.Cells[startRow + objArray.GetUpperBound(0), ColumnNo];
xlWs.Range(range, range2).Value2 = objArray;
xlWs.DisposeChildInstances();
}
catch
{
}
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off msg boxes
application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.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;
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
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);
}
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...");
}
}
/// <summary>
/// ExcelファイルのA1セル選択処理
/// 引数１：Excelファイルパス
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public static int Main(string[] args)
{
//引数チェック
if (!CheckArgs(args))
{
return(NG);
}
//Excel起動
WriteLog("Excel起動待ち");
using (Excel.Application excelApplication = new Excel.Application())
{
WriteLog("Excel起動");
try
{
//Excel描画停止（速度改善のため）
ExcelBeginUpdate(excelApplication);
//対象ファイルを開く
WriteLog("ファイル開く");
Excel.Workbook workBook = excelApplication.Workbooks.Open(filePath);
//A1セルを選択状態にする
WriteLog($"全シート数：{workBook.Sheets.Count}");
for (int i = 1; i <= workBook.Sheets.Count; i++)
{
WriteLog($"{i}シート目処理中");
//シート取得
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[i];
WriteLog($"シート名：{sheet.Name}");
//非表示のシートは操作に失敗するので無視する
if (sheet.Visible != Excel.Enums.XlSheetVisibility.xlSheetVisible)
{
continue;
}
//シートを選択状態にする（こうしないとセル選択に失敗する）
sheet.Select();
//倍率を100%に変更
excelApplication.ActiveWindow.Zoom = 100;
//スクロールを一番先頭に変更（選択セルを変更しても、スクロールバーの位置が元のままなので、先頭に移動）
excelApplication.ActiveWindow.ScrollColumn = 1;
excelApplication.ActiveWindow.ScrollRow = 1;
//一番左上のA1セルを選択状態に変更
sheet.Range("A1").Select();
}
//一番左に存在するシートを選択状態にする
WriteLog("一番左のシートを選択");
Excel.Worksheet firstSheet = (Excel.Worksheet)workBook.Sheets[1];
firstSheet.Select();
//保存
WriteLog("保存");
workBook.Save();
workBook.Close();
//Excel終了
excelApplication.Quit();
}
catch (Exception ex)
{
//何らかのエラー発生
WriteLog("例外エラー発生", ex);
return(NG);
}
finally
{
//Excelを閉じる
try
{
excelApplication.Quit();
excelApplication.Dispose();
}
catch { } //例外発生時は無視
}
}
return(OK);
}
public void AddInventorySheetContent(string sheetHeader, string workSheetName, DataTable Tbl)
{
Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[workSheetName];
AddInventoryFileHeader(workSheet);
// column headings
for (int i = 0; i < 4; i++)
{
workSheet.Cells[7, (i + 1)].Value = Tbl.Columns[i].Caption;
workSheet.Cells[7, (i + 1)].Font.Bold = true;
workSheet.Cells[7, (i + 1)].Font.Size = 8;
workSheet.Cells[7, (i + 1)].HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Cells[7, (i + 1)].VerticalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range(workSheet.Cells[7, i + 1], workSheet.Cells[8, i + 1]).Merge();
}
workSheet.Cells[7, 13].Value = Tbl.Columns["ParcelCode"].Caption;
workSheet.Cells[7, 13].Font.Bold = true;
workSheet.Cells[7, 13].Font.Size = 8;
workSheet.Cells[7, 13].HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Cells[7, 13].VerticalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range(workSheet.Cells[7, 13], workSheet.Cells[8, 13]).Merge();
for (int i = 4; i < 12; i += 2)
{
workSheet.Cells[7, (i + 1)].Value = Tbl.Columns[i].Caption;
workSheet.Range(workSheet.Cells[7, i + 1], workSheet.Cells[7, i + 2]).Merge();
workSheet.Cells[8, (i + 1)].Value = "Số lượng";
workSheet.Cells[8, (i + 2)].Value = "Thành tiền";
}
//Excel.Range range = workSheet.Range(workSheet.Cells[4, (i + 1)], workSheet.Cells[4, (i + 1)]);
//BorderAround(range, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
var sumRemain = 0.0;
var sumImport = 0.0;
var sumExport = 0.0;
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// workSheet.Cells[(i + 9), 0].Value = i + 1;
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 9), (j + 1)].Value = Tbl.Rows[i][j];
if (j == 3 || j == 5 || j == 7 || j == 9)
{
workSheet.Cells[(i + 9), (j + 2)].NumberFormat = "#,###.0";
}
else if (j == 4 || j == 6 || j == 8 || j == 10)
{
workSheet.Cells[(i + 9), (j + 2)].NumberFormat = "#,###.0";
}
}
sumRemain += double.Parse(Tbl.Rows[i][4].ToString().Trim());
sumImport += double.Parse(Tbl.Rows[i][6].ToString().Trim());
sumExport += double.Parse(Tbl.Rows[i][8].ToString().Trim());
}
var sumRow = Tbl.Rows.Count + 10;
workSheet.Cells[sumRow, 1].Value = "TỔNG CỘNG";
workSheet.Cells[sumRow, 1].Font.Bold = true;
workSheet.Cells[sumRow, 1].Font.Size = 8;
workSheet.Cells[sumRow, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Cells[sumRow, 1].VerticalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 4]).Merge();
workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 13]).Font.Bold = true;
workSheet.Cells[sumRow, 5].Value = sumRemain;
workSheet.Cells[sumRow, 7].Value = sumImport;
workSheet.Cells[sumRow, 9].Value = sumExport;
workSheet.Cells[sumRow, 11].Value = sumRemain + sumImport - sumExport;
BorderAround(workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]), System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Font.Size = 8;
workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline;
workSheet.Range(workSheet.Cells[9, 1], workSheet.Cells[sumRow, 13]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
BorderAround(workSheet.Range(workSheet.Cells[sumRow, 1], workSheet.Cells[sumRow, 13]), System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
//format numbers
//auto fit columns
workSheet.Columns.AutoFit();
}
public void AddProductionResultSheetContent(string sheetName, List <MeasurementRecord> listRecords)
{
Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetName];
var exportTime = this.creatingTime.Split('/');
//Title
workSheet.Cells[2, 1].Value = "DivisionID";
workSheet.Cells[2, 2].Value = "DIGINET";
workSheet.Cells[3, 1].Value = "TranMonth";
workSheet.Cells[3, 2].Value = exportTime[0];
workSheet.Cells[4, 1].Value = "TranYear";
workSheet.Cells[4, 2].Value = exportTime[1];
workSheet.Range(workSheet.Cells[2, 1], workSheet.Cells[4, 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
workSheet.Cells[2, 7].Value = "PRODUCTION RESULT";
workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[2, 8]).Merge();
workSheet.Cells[3, 7].Value = "KẾT QUẢ SẢN XUẤT";
workSheet.Range(workSheet.Cells[3, 7], workSheet.Cells[3, 8]).Merge();
workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[3, 8]).Font.Bold = true;
workSheet.Range(workSheet.Cells[2, 7], workSheet.Cells[3, 8]).Font.Size = 14;
#region header
workSheet.Cells[5, 8].Value = "MASTER";
workSheet.Range(workSheet.Cells[5, 8], workSheet.Cells[5, 10]).Merge();
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[5, 14]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
workSheet.Cells[5, 23].Value = "DETAIL";
workSheet.Range(workSheet.Cells[5, 23], workSheet.Cells[5, 25]).Merge();
workSheet.Range(workSheet.Cells[5, 15], workSheet.Cells[5, 30]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan);
workSheet.Cells[5, 34].Value = "SUB INFORMATION";
workSheet.Range(workSheet.Cells[5, 34], workSheet.Cells[5, 36]).Merge();
workSheet.Range(workSheet.Cells[5, 31], workSheet.Cells[5, 40]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
workSheet.Cells[5, 45].Value = "ANALYSIS CODE";
workSheet.Range(workSheet.Cells[5, 45], workSheet.Cells[5, 47]).Merge();
workSheet.Range(workSheet.Cells[5, 41], workSheet.Cells[5, 50]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
workSheet.Cells[5, 57].Value = "RECEIPT/DELIVERY INDEX";
workSheet.Range(workSheet.Cells[5, 57], workSheet.Cells[5, 59]).Merge();
workSheet.Range(workSheet.Cells[5, 51], workSheet.Cells[5, 61]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightPink);
workSheet.Cells[5, 65].Value = "SPECIFICATION";
workSheet.Range(workSheet.Cells[5, 65], workSheet.Cells[5, 67]).Merge();
workSheet.Range(workSheet.Cells[5, 62], workSheet.Cells[5, 71]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
int col = 3;
workSheet.Cells[7, col].Value = "Loại phiếu";
workSheet.Cells[6, col++].Value = "Voucher Type";
workSheet.Cells[7, col].Value = "Loại nghiệp vụ";
workSheet.Cells[6, col++].Value = "TransType";
workSheet.Cells[7, col].Value = "Số phiếu";
workSheet.Cells[6, col++].Value = "Voucher No";
workSheet.Cells[7, col].Value = "Ngày phiếu";
workSheet.Cells[6, col++].Value = "Voucher Date";
workSheet.Cells[7, col].Value = "Diễn giải phiếu";
workSheet.Cells[6, col++].Value = "Voucher Description";
workSheet.Cells[7, col].Value = "Người lập";
workSheet.Cells[6, col++].Value = "Employee ID";
workSheet.Cells[7, col].Value = "Loại đối tượng";
workSheet.Cells[6, col++].Value = "Object Type ID";
workSheet.Cells[7, col].Value = "Mã đối tượng";
workSheet.Cells[6, col++].Value = "Object ID";
workSheet.Cells[7, col].Value = "Nhân viên KCS";
workSheet.Cells[6, col++].Value = "KCSEmployeeID";
workSheet.Cells[7, col].Value = "Ngày KCS";
workSheet.Cells[6, col++].Value = "FindDate";
workSheet.Cells[7, col].Value = "Kỳ sản xuất";
workSheet.Cells[6, col++].Value = "Prod Period";
workSheet.Cells[7, col].Value = "Loại KQSX";
workSheet.Cells[6, col++].Value = "Result Type ID";
workSheet.Cells[7, col].Value = "Mã hàng";
workSheet.Cells[6, col++].Value = "Inventory ID";
workSheet.Cells[7, col].Value = "ĐVT";
workSheet.Cells[6, col++].Value = "Unit ID";
workSheet.Cells[7, col].Value = "Số lượng";
workSheet.Cells[6, col++].Value = "Original Quantity";
workSheet.Cells[7, col].Value = "Số lượng quy đổi";
workSheet.Cells[6, col++].Value = "Converted Quantity";
workSheet.Cells[7, col].Value = "Đơn giá";
workSheet.Cells[6, col++].Value = "Unit Price";
workSheet.Cells[7, col].Value = "Thành tiền";
workSheet.Cells[6, col++].Value = "Amount";
workSheet.Cells[7, col].Value = "Ghi chú";
workSheet.Cells[6, col++].Value = "Note";
workSheet.Cells[7, col].Value = "Số lô";
workSheet.Cells[6, col++].Value = "Location No";
workSheet.Cells[7, col].Value = "Ngày sản xuất";
workSheet.Cells[6, col++].Value = "Production date";
workSheet.Cells[7, col].Value = "Ngày hết hạn";
workSheet.Cells[6, col++].Value = "Limit date";
workSheet.Cells[7, col].Value = "Mã chất lượng";
workSheet.Cells[6, col++].Value = "QualityID";
workSheet.Cells[7, col].Value = "Kế hoạch sản xuất";
workSheet.Cells[6, col++].Value = "MPSVoucherNo";
workSheet.Cells[7, col].Value = "Lệnh sản xuất";
workSheet.Cells[6, col++].Value = "ProOrderNo";
workSheet.Cells[7, col].Value = "% hoàn thành";
workSheet.Cells[6, col++].Value = "Finished Percent";
workSheet.Cells[7, col].Value = "Mã sản phẩm";
workSheet.Cells[6, col++].Value = "Product Code";
workSheet.Cells[7, col].Value = "Yếu tố chi phí";
workSheet.Cells[6, col++].Value = "Material Type";
workSheet.Cells[7, col].Value = "Số thứ 1";
workSheet.Cells[6, col++].Value = "Num 1";
workSheet.Cells[7, col].Value = "Số thứ 2";
workSheet.Cells[6, col++].Value = "Num 2";
workSheet.Cells[7, col].Value = "Số thứ 3";
workSheet.Cells[6, col++].Value = "Num 3";
workSheet.Cells[7, col].Value = "Số thứ 4";
workSheet.Cells[6, col++].Value = "Num 4";
workSheet.Cells[7, col].Value = "Số thứ 5";
workSheet.Cells[6, col++].Value = "Num 5";
workSheet.Cells[7, col].Value = "Chuỗi 1";
workSheet.Cells[6, col++].Value = "String 1";
workSheet.Cells[7, col].Value = "Chuỗi 2";
workSheet.Cells[6, col++].Value = "String 2";
workSheet.Cells[7, col].Value = "Chuỗi 3";
workSheet.Cells[6, col++].Value = "String 3";
workSheet.Cells[7, col].Value = "Chuỗi 4";
workSheet.Cells[6, col++].Value = "String 4";
workSheet.Cells[7, col].Value = "Chuỗi 5";
workSheet.Cells[6, col++].Value = "String 5";
workSheet.Cells[7, col].Value = "Khoản mục 1";
workSheet.Cells[6, col++].Value = "K-Code 01";
workSheet.Cells[7, col].Value = "Khoản mục 2";
workSheet.Cells[6, col++].Value = "K-Code 02";
workSheet.Cells[7, col].Value = "Khoản mục 3";
workSheet.Cells[6, col++].Value = "K-Code 03";
workSheet.Cells[7, col].Value = "Khoản mục 4";
workSheet.Cells[6, col++].Value = "K-Code 04";
workSheet.Cells[7, col].Value = "Khoản mục 5";
workSheet.Cells[6, col++].Value = "K-Code 05";
workSheet.Cells[7, col].Value = "Khoản mục 6";
workSheet.Cells[6, col++].Value = "K-Code 06";
workSheet.Cells[7, col].Value = "Khoản mục 7";
workSheet.Cells[6, col++].Value = "K-Code 07";
workSheet.Cells[7, col].Value = "Khoản mục 8";
workSheet.Cells[6, col++].Value = "K-Code 08";
workSheet.Cells[7, col].Value = "Khoản mục 9";
workSheet.Cells[6, col++].Value = "K-Code 09";
workSheet.Cells[7, col].Value = "Khoản mục 10";
workSheet.Cells[6, col++].Value = "K-Code 10";
workSheet.Cells[7, col].Value = "Công thức";
workSheet.Cells[6, col++].Value = "Formula";
workSheet.Cells[7, col].Value = "Chỉ số 01";
workSheet.Cells[6, col++].Value = "Num01";
workSheet.Cells[7, col].Value = "Chỉ số 02";
workSheet.Cells[6, col++].Value = "Num02";
workSheet.Cells[7, col].Value = "Chỉ số 03";
workSheet.Cells[6, col++].Value = "Num03";
workSheet.Cells[7, col].Value = "Chỉ số 04";
workSheet.Cells[6, col++].Value = "Num04";
workSheet.Cells[7, col].Value = "Chỉ số 05";
workSheet.Cells[6, col++].Value = "Num05";
workSheet.Cells[7, col].Value = "Chỉ số 06";
workSheet.Cells[6, col++].Value = "Num06";
workSheet.Cells[7, col].Value = "Chỉ số 07";
workSheet.Cells[6, col++].Value = "Num07";
workSheet.Cells[7, col].Value = "Chỉ số 08";
workSheet.Cells[6, col++].Value = "Num08";
workSheet.Cells[7, col].Value = "Chỉ số 09";
workSheet.Cells[6, col++].Value = "Num09";
workSheet.Cells[7, col].Value = "Chỉ số 10";
workSheet.Cells[6, col++].Value = "Num10";
workSheet.Cells[7, col].Value = "Quy cách 1";
workSheet.Cells[6, col++].Value = "Spec 01";
workSheet.Cells[7, col].Value = "Quy cách 2";
workSheet.Cells[6, col++].Value = "Spec 02";
workSheet.Cells[7, col].Value = "Quy cách 3";
workSheet.Cells[6, col++].Value = "Spec 03";
workSheet.Cells[7, col].Value = "Quy cách 4";
workSheet.Cells[6, col++].Value = "Spec 04";
workSheet.Cells[7, col].Value = "Quy cách 5";
workSheet.Cells[6, col++].Value = "Spec 05";
workSheet.Cells[7, col].Value = "Quy cách 6";
workSheet.Cells[6, col++].Value = "Spec 06";
workSheet.Cells[7, col].Value = "Quy cách 7";
workSheet.Cells[6, col++].Value = "Spec 07";
workSheet.Cells[7, col].Value = "Quy cách 8";
workSheet.Cells[6, col++].Value = "Spec 08";
workSheet.Cells[7, col].Value = "Quy cách 9";
workSheet.Cells[6, col++].Value = "Spec 09";
workSheet.Cells[7, col].Value = "Quy cách 10";
workSheet.Cells[6, col++].Value = "Spec 10";
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).Font.Bold = true;
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).Font.Size = 10;
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[7, 71]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[5, 71]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
workSheet.Range(workSheet.Cells[6, 3], workSheet.Cells[7, 71]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[6, 3], workSheet.Cells[7, 71]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
#endregion
for (int i = 0; i < listRecords.Count; i++)
{
var row = i + 8;
//VoucherType
workSheet.Cells[row, 3].Value = "KQT";
//TransType
workSheet.Cells[row, 4].Value = "KQSX";
//VoucherNo
var day = listRecords[i].RecordDate.Day.ToString().PadLeft(3, '0');
var month = listRecords[i].RecordDate.Month.ToString().PadLeft(2, '0');
var year = listRecords[i].RecordDate.Year.ToString().Substring(2, 2);
workSheet.Cells[row, 5].Value = day + "/KQT/" + month + "/" + year;
//Voucher Description
string desc = (listRecords[i].Material.TypeID == (int)MaterialTypeEnum.Pat ? "Nhập Pat " : "Nhập Keo ") + listRecords[i].Material.Code + " Rev " + listRecords[i].Material.Revision;
if (!listRecords[i].Description.Equals("Cân thực"))
{
desc = desc + " (Thí nghiệm)";
}
workSheet.Cells[row, 7].Value = desc;
////Prod Period
//workSheet.Cells[row, 13].Value = "KY" + this.creatingTime.ToString().PadLeft(7, '0');
//Inventory ID
workSheet.Cells[row, 15].Value = listRecords[i].Material.Code;
//Unit
workSheet.Cells[row, 16].Value = listRecords[i].WeightUnit;
//Original Quantity
workSheet.Cells[row, 17].Value = Math.Round(listRecords[i].Weight, 1);
//Converted Quantity
workSheet.Cells[row, 18].Value = Math.Round(listRecords[i].Weight, 1);
//Note
workSheet.Cells[row, 21].Value = listRecords[i].Material.TypeID == (int)MaterialTypeEnum.Pat ? "NK PAT" : "NK KEO";
//Location No
workSheet.Cells[row, 22].Value = listRecords[i].ParcelCode;
//Production date
workSheet.Cells[row, 23].Value = listRecords[i].RecordDate.ToShortDateString();
}
var row2 = 8 + listRecords.Count;
workSheet.Range(workSheet.Cells[5, 3], workSheet.Cells[row2, 30]).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[8, 3], workSheet.Cells[row2, 30]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline;
//auto fit columns
workSheet.Columns.AutoFit();
}
public InputOptionsPage(Worksheet oSheet)
{
this.ancPln = (string)oSheet.Range("anchorPlan").Value;
this.subsidyOption = (string)oSheet.Range("subType").Value;
this.mgOpt = (string)oSheet.Range("migType").Value;
this.projName = (string)oSheet.Range("projNme").Value;
this.inMode = (string)oSheet.Range("modal").Value;
this.outMode = (string)oSheet.Range("modalOut").Value;
this.clientName = (string)oSheet.Range("clientNme").Value;
this.clientID = oSheet.Range("clientID").Value.ToString();
this.subsidyValue = double.Parse(oSheet.Range("Sub").Value.ToString());
this.subsidyMin = double.Parse(oSheet.Range("Min").Value == null ? 0.ToString() : oSheet.Range("Min").Value.ToString());
this.subsidyMax = double.Parse(oSheet.Range("Max").Value == null ? 9999999999.ToString() : oSheet.Range("Max").Value.ToString());
defaultAnchorPlan = "";
this.modalAdj = Utility.modalConvert(inMode, outMode);
this.displayName = "";
this.reviewerName = (string)oSheet.Range("peerReviewer").Value;
this.finalModelIndicator = (((string)oSheet.Range("finalRun").Value).Equals("Yes", StringComparison.OrdinalIgnoreCase));
ClientNameIDCheck();
CheckProjName();
CheckValidSubsidyPercent();
SetAndCheckDisplayNameAndConnection();
}
public static Object[,] PullInExcelData(int startRow, int startColumn, Worksheet oSheet)
{
int row = oSheet.UsedRange.Rows[oSheet.UsedRange.Rows.Count].Row;
int column = oSheet.UsedRange.Columns[oSheet.UsedRange.Columns.Count].Column;
object[,] objArray = (object[,]) oSheet.Range(oSheet.Cells[startRow, startColumn], oSheet.Cells[row, column])
.get_Value(XlRangeValueDataType.xlRangeValueDefault);
return objArray;
}
private void doWork2()
{
PowerPoint.Presentation presentation = null;
PowerPoint.Application applicationPowerPoint = null;
Excel.Workbook book = null;
Excel.Application applicationExcel = null;
try
{
Dictionary <String, Excel.Shape> excelShapesDictionary = new Dictionary <string, Excel.Shape>();
Log.Information("Opening Excel Applicaiton");
applicationExcel = new Excel.Application();
applicationExcel.DisplayAlerts = false;
String xlsx = "D:\\Input\\Input.xlsx";
String output_folder = "D:\\Output";
String pptxSingle = "D:\\Input\\input.pptx";
Log.Information("Opening Excel File {0}", xlsx);
book = applicationExcel.Workbooks.Open(xlsx, true, true);
Excel.Worksheet worksheet = (Excel.Worksheet)book.Worksheets[1];
int index = 3;
List <PresentationItem> presentationItems = new List <PresentationItem>();
do
{
String s = worksheet.Range("A" + index).Value2.ToString();
Log.Information(worksheet.Name + " " + s);
PresentationItem presentationItem = new PresentationItem();
presentationItem.OutputFileName = s;
int index2 = 1;
do
{
String attr_type = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString();
String attr_name = worksheet.Range(((char)((byte)'A' + index2)).ToString() + 2).Value2.ToString();
String attr_value = worksheet.Range(((char)((byte)'A' + index2)).ToString() + index).Value2.ToString();
Log.Information("{0} {1} {2} {3}", ((char)((byte)'A' + index2)).ToString() + 1, attr_type, attr_name, attr_value);
presentationItem.Attributes.Add(new Attribute(attr_type, attr_name, attr_value));
index2++;
if (index2 == 20)
{
break;
}
} while (worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2 != null && worksheet.Range(((char)((byte)'A' + index2)).ToString() + 1).Value2.ToString() != "");
index++;
presentationItems.Add(presentationItem);
if (index == 100)
{
break;
}
} while (worksheet.Range("A" + index).Value2 != null && worksheet.Range("A" + index).Value2.ToString() != "");
Log.Information("Closing Excel File");
book.Close();
Log.Information("Opening PowerPoint Applicaiton");
applicationPowerPoint = new PowerPoint.Application();
applicationPowerPoint.DisplayAlerts = PowerPoint.Enums.PpAlertLevel.ppAlertsNone;
foreach (var presentationItem in presentationItems)
{
Dictionary <int, List <PowerPoint.Shape> > powerpointShapesSheetDictionary = new Dictionary <int, List <PowerPoint.Shape> >();
Log.Information("Opening Presentation {0}", pptxSingle);
presentation = applicationPowerPoint.Presentations.Open(pptxSingle, true, true, true);
foreach (PowerPoint.Shape shape in presentation.SlideMaster.Shapes)
{
Log.Information("\tSlide Master: Shape Name : {0} Size(w x h) : {1} x {2} Position(left x top) : {3} , {4}", shape.Name, shape.Width, shape.Height, shape.Left, shape.Top);
if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue)
{
foreach (var attribute in presentationItem.Attributes)
{
if (attribute.type == "Text")
{
string before = shape.TextFrame.TextRange.Text;
shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value);
string after = shape.TextFrame.TextRange.Text;
if (before != after)
{
Log.Information("\t\t{0} >> {1}", before, after);
}
}
}
}
}
Log.Information("Iterating all shapes in all slides and filtering shapes with name staring with \"#\"");
foreach (PowerPoint.Slide slide in presentation.Slides)
{
foreach (PowerPoint.Shape shape in slide.Shapes)
{
Log.Information("\tSlide No : {0} Shape Name : {1} Size(w x h) : {2} x {3} Position(left x top) : {4} , {5}", slide.SlideNumber, shape.Name, shape.Width, shape.Height, shape.Left, shape.Top);
if (shape.HasTextFrame == Office.Enums.MsoTriState.msoTrue && shape.TextFrame.HasText == Office.Enums.MsoTriState.msoTrue)
{
foreach (var attribute in presentationItem.Attributes)
{
if (attribute.type == "Text")
{
string before = shape.TextFrame.TextRange.Text;
shape.TextFrame.TextRange.Replace("|*" + attribute.name + "*|", attribute.value);
string after = shape.TextFrame.TextRange.Text;
if (before != after)
{
Log.Information("\t\t{0} >> {1}", before, after);
}
}
}
}
foreach (var attribute in presentationItem.Attributes)
{
if (attribute.type == "Chart" && shape.Name == "|*" + attribute.name + "*|")
{
string before = shape.Name;
shape.Name = attribute.value;
string after = shape.Name;
if (before != after)
{
Log.Information("\t\t{0} >> {1}", before, after);
}
}
}
}
Log.Information("");
}
String outputfile = Path.Combine(output_folder, new FileInfo(pptxSingle).Name);
Log.Information("Saving a copy of updated Presentation to {0}", outputfile);
presentation.SaveCopyAs(outputfile);
Log.Information("Closing Presentation");
}
presentation.Close();
Log.Information("Closing PowerPoint Applicaiton");
applicationPowerPoint.Quit();
applicationPowerPoint.Dispose();
Log.Information("Closing Excel Applicaiton");
applicationExcel.Quit();
applicationExcel.Dispose();
Log.Information("Done");
}
catch (Exception e)
{
Log.Error("Fatal Error - " + e.ToString());
thisForm.BeginInvoke((MethodInvoker)(() =>
{
MessageBox.Show(thisForm, e.ToString(), "Fatal Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}));
try
{
presentation.Close();
}
catch (Exception ee) { }
try
{
applicationPowerPoint.Quit();
applicationPowerPoint.Dispose();
}
catch (Exception ee) { }
try
{
book.Close();
}
catch (Exception ee) { }
try
{
applicationExcel.Quit();
applicationExcel.Dispose();
}
catch (Exception ee) { }
}
}
internal void CreateWorkSheet(Worksheet worksheet)
{
try
{
worksheet.DisplayRightToLeft = false;
worksheet.Name = "Loop" + channekNumber;
worksheet.Range("B1:D1").Font.Bold = true;
worksheet.Range("B1").Value = "loop" + channekNumber;
worksheet.Range("C1").Value = "x";
worksheet.Range("D1").Value = "y";
worksheet.Range("A1").EntireColumn.ColumnWidth = 8.38;
worksheet.Range("B1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("C1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("D1").EntireColumn.ColumnWidth = 11.25;
worksheet.Range("A1:D50").HorizontalAlignment = XlHAlign.xlHAlignCenter;
List<System.Windows.Point> OrderPoints = Points.OrderBy(x => x.X).ToList();
List<System.Windows.Point> OrderDetectorPoints = DetectorPoints.OrderBy(x => x.X).ToList();
worksheet.Range("C1:D" + (Freqs.Length + 1)).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDouble;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Weight = 4;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Color = ToDouble(Color.Black);
worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
int startrow = 2;
int i;
for (i = 0; i < Freqs.Length; i++)
{
worksheet.Range("A" + (i + startrow)).Value = "f" + (i + 1);
worksheet.Range("B" + (i + startrow)).Value = Freqs[i];
worksheet.Range("C" + (i + startrow)).Value = Math.Round(OrderPoints[i].X, 2);
worksheet.Range("D" + (i + startrow)).Value = Math.Round(OrderPoints[i].Y, 2);
worksheet.Range("F" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].X, 2);
worksheet.Range("G" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].Y, 2);
}
startrow = i + 3;
worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.Weight = 2d;
worksheet.Range("B" + (startrow)).Value = "Ltot";
worksheet.Range("C" + (startrow)).Value = "L";
startrow++;
for (i = 0; i < Points.Length; i++)
{
worksheet.Range("A" + (i + startrow)).Value = (i + 1);
worksheet.Range("B" + (i + startrow)).Value = Math.Round(Points[i].X, 2);
worksheet.Range("C" + (i + startrow)).Value = Math.Round(Points[i].Y, 2);
}
startrow += i + 2;
worksheet.Range("A2:A" + startrow).HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":C" + startrow).Merge();
worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction1;
startrow++;
worksheet.Range("A" + startrow).Value = "a1";
worksheet.Range("B" + startrow).Value = "b1";
worksheet.Range("C" + startrow).Value = "c1";
startrow++;
worksheet.Range("A" + startrow).Value = A1;
worksheet.Range("B" + startrow).Value = B1;
worksheet.Range("C" + startrow).Value = C1;
startrow++;
startrow++;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":C" + startrow).Merge();
worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction2;
startrow++;
worksheet.Range("A" + startrow).Value = "a2";
worksheet.Range("B" + startrow).Value = "b2";
worksheet.Range("C" + startrow).Value = "c2";
startrow++;
worksheet.Range("A" + startrow).Value = A2;
worksheet.Range("B" + startrow).Value = B2;
worksheet.Range("C" + startrow).Value = C2;
startrow++;
startrow++;
worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.Weight = 2d;
worksheet.Range("A" + startrow + ":B" + startrow).Merge();
worksheet.Range("A" + startrow + ":B" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
worksheet.Range("A" + startrow + ":B" + startrow).Value = LineFunction;
startrow++;
worksheet.Range("A" + startrow).Value = "k";
worksheet.Range("B" + startrow).Value = "l";
startrow++;
worksheet.Range("A" + startrow).Value = K;
worksheet.Range("B" + startrow).Value = L;
ChartObject chart = ((ChartObjects)worksheet.ChartObjects()).Add(300, 0, 770, 500);
chart.Chart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
chart.Chart.HasLegend = true;
chart.Chart.Legend.Position = XlLegendPosition.xlLegendPositionRight;
chart.Chart.Legend.Left = 680;
chart.Chart.Legend.Top = 10;
chart.Chart.Legend.Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.LightGray);
chart.Chart.Legend.Border.Color = ColorTranslator.ToOle(Color.DarkGray);
chart.Chart.HasTitle = true;
chart.Chart.ChartTitle.Text = "Loop " + channekNumber;
Axis axisy = (Axis)chart.Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
axisy.HasTitle = true;
axisy.AxisTitle.Text = "L Loop";
Axis axisx = (Axis)chart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
axisx.HasTitle = true;
axisx.AxisTitle.Text = "L Total";
SeriesCollection seriesCollection = (SeriesCollection)chart.Chart.SeriesCollection();
#region Calibration Point
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C2", "C9");
oSeries.Values = worksheet.get_Range("D2", "D9");
oSeries.ChartType = XlChartType.xlXYScatter;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowLabel);
oSeries.HasDataLabels = true;
DataLabels labels = (DataLabels)oSeries.DataLabels();
labels.Separator = " , ";
labels.ShowCategoryName = true;
labels.ShowValue = true;
oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
oSeries.MarkerSize = 10;
oSeries.Name = "Calibration";
}
#endregion
#region Measurements Point
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("F2", "F9");
oSeries.Values = worksheet.get_Range("G2", "G9");
oSeries.ChartType = XlChartType.xlXYScatter;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleDiamond;
oSeries.MarkerSize = 10;
oSeries.Name = "Measurements";
}
#endregion
#region Line
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C2", "C3");
oSeries.Values = worksheet.get_Range("D2", "D3");
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
oSeries.Name = LineFunctionNumber.ToString();
}
#endregion
#region F1
startrow = 100;
foreach (var item in Calc1000Ponts(A1, B1, C1, Gragh1))
{
worksheet.Range("A" + (startrow)).Value = Math.Round(item.X, 2);
worksheet.Range("B" + (startrow)).Value = Math.Round(item.Y, 2);
startrow++;
}
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("A100", "A" + startrow);
oSeries.Values = worksheet.get_Range("B100", "B" + startrow);
oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
oSeries.Name = PolinomFunction1Number.ToString();
}
#endregion
#region F1
startrow = 100;
foreach (var item in Calc1000Ponts(A2, B2, C2, Gragh2))
{
worksheet.Range("C" + (startrow)).Value = Math.Round(item.X, 2);
worksheet.Range("D" + (startrow)).Value = Math.Round(item.Y, 2);
startrow++;
}
using (Series oSeries = seriesCollection.NewSeries())
{
oSeries.XValues = worksheet.get_Range("C100", "C" + startrow);
oSeries.Values = worksheet.get_Range("D100", "D" + startrow);
oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
oSeries.Name = PolinomFunction2Number.ToString();
}
#endregion
if (!System.IO.Directory.Exists(CalibrateModel.filespath))
System.IO.Directory.CreateDirectory(CalibrateModel.filespath);
try
{
chart.Chart.Export(CalibrateModel.filespath + @"ChartLoop" + channekNumber + ".jpeg", "JPEG");
}
catch (Exception xe)
{
Console.WriteLine(xe.Message);
}
}
catch
{
releaseObject(worksheet);
}
}
public void AddExportSheetContent(string sheetName, List <MeasurementDetailRecord> listRecordDetails)
{
Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetName];
//Title
workSheet.Cells[4, 1].Value = "BÁO CÁO XUẤT NGUYÊN VẬT LIỆU";
workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[4, 13]).Merge();
workSheet.Cells[5, 1].Value = "Kỳ: Tháng " + this.creatingTime;
workSheet.Cells[5, 1].Font.Size = 10;
workSheet.Range(workSheet.Cells[5, 1], workSheet.Cells[5, 13]).Merge();
workSheet.Cells[7, 3].Value = "Nghiệp vụ(TransType)";
workSheet.Cells[7, 3].Font.Size = 10;
workSheet.Cells[7, 3].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
workSheet.Cells[7, 4].Value = "PXK";
workSheet.Cells[7, 4].Font.Size = 10;
workSheet.Cells[8, 3].Value = "Loại nghiệp vụ(TransTypeID)";
workSheet.Cells[8, 3].Font.Size = 10;
workSheet.Cells[8, 3].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
workSheet.Cells[8, 4].Value = "2";
workSheet.Cells[8, 4].Font.Size = 10;
workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[8, 13]).Font.Bold = true;
workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[5, 13]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range(workSheet.Cells[4, 1], workSheet.Cells[5, 13]).VerticalAlignment = XlHAlign.xlHAlignCenter;
#region header
workSheet.Cells[10, 2].Value = "Loại phiếu";
workSheet.Cells[11, 2].Value = "VoucherTypeID";
workSheet.Cells[12, 2].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 3].Value = "Số phiếu";
workSheet.Cells[11, 3].Value = "VoucherNo";
workSheet.Cells[12, 3].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 4].Value = "Ngày phiếu";
workSheet.Cells[11, 4].Value = "VoucherDate";
workSheet.Cells[12, 4].Value = "Kiểu ngày";
workSheet.Cells[10, 5].Value = "Diễn giải";
workSheet.Cells[11, 5].Value = "VoucherDesc ";
workSheet.Cells[12, 5].Value = "Kiểu chuỗi: Max 250";
workSheet.Cells[10, 6].Value = "Số seri";
workSheet.Cells[11, 6].Value = "SerialNo ";
workSheet.Cells[12, 6].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 7].Value = "Số hóa đơn";
workSheet.Cells[11, 7].Value = "RefNo ";
workSheet.Cells[12, 7].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 8].Value = "Loại tiền";
workSheet.Cells[11, 8].Value = "CurrencyID";
workSheet.Cells[12, 8].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 9].Value = "Tỷ giá";
workSheet.Cells[11, 9].Value = "ExchangeRate";
workSheet.Cells[12, 9].Value = "Kiểu số";
workSheet.Cells[10, 10].Value = "Loại đối tượng";
workSheet.Cells[11, 10].Value = "ObjectTypeID";
workSheet.Cells[12, 10].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 11].Value = "Đối tượng";
workSheet.Cells[11, 11].Value = "ObjectID";
workSheet.Cells[12, 11].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 12].Value = "Người lập";
workSheet.Cells[11, 12].Value = "EmployeeID";
workSheet.Cells[12, 12].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 13].Value = "Kho hàng";
workSheet.Cells[11, 13].Value = "RWareHouseID";
workSheet.Cells[12, 13].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 14].Value = "Kho xuất(VCNB)";
workSheet.Cells[11, 14].Value = "DWareHouseID";
workSheet.Cells[12, 14].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 15].Value = "Mã hàng";
workSheet.Cells[11, 15].Value = "InventoryID";
workSheet.Cells[12, 15].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 16].Value = "ĐVT";
workSheet.Cells[11, 16].Value = "UnitID";
workSheet.Cells[12, 16].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 17].Value = "TK Nợ";
workSheet.Cells[11, 17].Value = "DebitAccountID";
workSheet.Cells[12, 17].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 18].Value = "TK Có";
workSheet.Cells[11, 18].Value = "CreditAccountID";
workSheet.Cells[12, 18].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 19].Value = "Số lượng";
workSheet.Cells[11, 19].Value = "OQuantity";
workSheet.Cells[12, 19].Value = "Kiểu số";
workSheet.Cells[10, 20].Value = "Số lượng quy đổi";
workSheet.Cells[11, 20].Value = "CQuantity";
workSheet.Cells[12, 20].Value = "Kiểu số";
workSheet.Cells[10, 21].Value = "Đơn giá";
workSheet.Cells[11, 21].Value = "UnitPrice";
workSheet.Cells[12, 21].Value = "Kiểu số";
workSheet.Cells[10, 22].Value = "Thành tiền";
workSheet.Cells[11, 22].Value = "OAmount";
workSheet.Cells[12, 22].Value = "Kiểu số";
workSheet.Cells[10, 23].Value = "Thành tiền quy đổi";
workSheet.Cells[11, 23].Value = "CAmount";
workSheet.Cells[12, 23].Value = "Kiểu số";
workSheet.Cells[10, 24].Value = "Diễn giải chi tiết";
workSheet.Cells[11, 24].Value = "DetailDescription";
workSheet.Cells[12, 24].Value = "Kiểu chuỗi: Max 250";
workSheet.Cells[10, 25].Value = "Lô";
workSheet.Cells[11, 25].Value = "LocationNo";
workSheet.Cells[12, 25].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 26].Value = "Ngày hết hạn";
workSheet.Cells[11, 26].Value = "LimitDate";
workSheet.Cells[12, 26].Value = "Kiểu ngày";
workSheet.Cells[10, 27].Value = "Ngày sản xuất";
workSheet.Cells[11, 27].Value = "ProDate";
workSheet.Cells[12, 27].Value = "Kiểu ngày";
workSheet.Cells[10, 28].Value = "Tập chí";
workSheet.Cells[11, 28].Value = "PeriodID";
workSheet.Cells[12, 28].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 29].Value = "Mã sản phẩm";
workSheet.Cells[11, 29].Value = "ProductID";
workSheet.Cells[12, 29].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 30].Value = "Lệnh sản xuất";
workSheet.Cells[11, 30].Value = "ProOrderNo";
workSheet.Cells[12, 30].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 31].Value = "Khoản mục 1";
workSheet.Cells[11, 31].Value = "Ana01ID";
workSheet.Cells[12, 31].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 32].Value = "Khoản mục 2";
workSheet.Cells[11, 32].Value = "Ana02ID";
workSheet.Cells[12, 32].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 33].Value = "Khoản mục 3";
workSheet.Cells[11, 33].Value = "Ana03ID";
workSheet.Cells[12, 33].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 34].Value = "Khoản mục 4";
workSheet.Cells[11, 34].Value = "Ana04ID";
workSheet.Cells[12, 34].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 35].Value = "Khoản mục 5";
workSheet.Cells[11, 35].Value = "Ana05ID";
workSheet.Cells[12, 35].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 36].Value = "Khoản mục 6";
workSheet.Cells[11, 36].Value = "Ana06ID";
workSheet.Cells[12, 36].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 37].Value = "Khoản mục 7";
workSheet.Cells[11, 37].Value = "Ana07ID";
workSheet.Cells[12, 37].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 38].Value = "Khoản mục 8";
workSheet.Cells[11, 38].Value = "Ana08ID";
workSheet.Cells[12, 38].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 39].Value = "Khoản mục 9";
workSheet.Cells[11, 39].Value = "Ana09ID";
workSheet.Cells[12, 39].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 40].Value = "Khoản mục 10";
workSheet.Cells[11, 40].Value = "Ana10ID";
workSheet.Cells[12, 40].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 41].Value = "Quy cách 1";
workSheet.Cells[11, 41].Value = "Spec01ID";
workSheet.Cells[12, 41].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 42].Value = "Quy cách 2";
workSheet.Cells[11, 42].Value = "Spec02ID";
workSheet.Cells[12, 42].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 43].Value = "Quy cách 3";
workSheet.Cells[11, 43].Value = "Spec03ID";
workSheet.Cells[12, 43].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 44].Value = "Quy cách 4";
workSheet.Cells[11, 44].Value = "Spec04ID";
workSheet.Cells[12, 44].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 45].Value = "Quy cách 5";
workSheet.Cells[11, 45].Value = "Spec05ID";
workSheet.Cells[12, 45].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 46].Value = "Quy cách 6";
workSheet.Cells[11, 46].Value = "Spec06ID";
workSheet.Cells[12, 46].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 47].Value = "Quy cách 7";
workSheet.Cells[11, 47].Value = "Spec07ID";
workSheet.Cells[12, 47].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 48].Value = "Quy cách 8";
workSheet.Cells[11, 48].Value = "Spec08ID";
workSheet.Cells[12, 48].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 49].Value = "Quy cách 9";
workSheet.Cells[11, 49].Value = "Spec09ID";
workSheet.Cells[12, 49].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 50].Value = "Quy cách 10";
workSheet.Cells[11, 50].Value = "Spec10ID";
workSheet.Cells[12, 50].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 51].Value = "Thông tin phụ chuỗi 1";
workSheet.Cells[11, 51].Value = "VRef1";
workSheet.Cells[12, 51].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 52].Value = "Thông tin phụ chuỗi 2";
workSheet.Cells[11, 52].Value = "VRef2";
workSheet.Cells[12, 52].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 53].Value = "Thông tin phụ chuỗi 3";
workSheet.Cells[11, 53].Value = "VRef3";
workSheet.Cells[12, 53].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 54].Value = "Thông tin phụ chuỗi 4";
workSheet.Cells[11, 54].Value = "VRef4";
workSheet.Cells[12, 54].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 55].Value = "Thông tin phụ chuỗi 5";
workSheet.Cells[11, 55].Value = "VRef5";
workSheet.Cells[12, 55].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 56].Value = "Thông tin phụ số 1";
workSheet.Cells[11, 56].Value = "NRef1";
workSheet.Cells[12, 56].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 57].Value = "Thông tin phụ số 2";
workSheet.Cells[11, 57].Value = "NRef2";
workSheet.Cells[12, 57].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 58].Value = "Thông tin phụ số 3";
workSheet.Cells[11, 58].Value = "NRef3";
workSheet.Cells[12, 58].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 59].Value = "Thông tin phụ số 4";
workSheet.Cells[11, 59].Value = "NRef4";
workSheet.Cells[12, 59].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 60].Value = "Thông tin phụ số 5";
workSheet.Cells[11, 60].Value = "NRef5";
workSheet.Cells[12, 60].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 61].Value = "Thông tin phụ ngày 1";
workSheet.Cells[11, 61].Value = "DRef1";
workSheet.Cells[12, 61].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 62].Value = "Thông tin phụ ngày 2";
workSheet.Cells[11, 62].Value = "DRef2";
workSheet.Cells[12, 62].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 63].Value = "Thông tin phụ ngày 3";
workSheet.Cells[11, 63].Value = "DRef3";
workSheet.Cells[12, 63].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 64].Value = "Thông tin phụ ngày 4";
workSheet.Cells[11, 64].Value = "DRef4";
workSheet.Cells[12, 64].Value = "Kiểu chuỗi: Max 20";
workSheet.Cells[10, 65].Value = "Thông tin phụ ngày 5";
workSheet.Cells[11, 65].Value = "DRef5";
workSheet.Cells[12, 65].Value = "Kiểu chuỗi: Max 20";
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[11, 65]).Font.Bold = true;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[11, 65]).Font.Size = 10;
workSheet.Range(workSheet.Cells[12, 2], workSheet.Cells[12, 65]).Font.Italic = true;
workSheet.Range(workSheet.Cells[12, 2], workSheet.Cells[12, 65]).Font.Size = 8;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCyan);
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[12, 65]).VerticalAlignment = XlHAlign.xlHAlignCenter;
#endregion
for (int i = 0; i < listRecordDetails.Count; i++)
{
var row = i + 13;
//VoucherTypeID
workSheet.Cells[row, 2].Value = "PXK";
//VoucherNo
var day = listRecordDetails[i].MeasurementRecord.RecordDate.Day.ToString().PadLeft(3, '0');
var month = listRecordDetails[i].MeasurementRecord.RecordDate.Month.ToString().PadLeft(2, '0');
var year = listRecordDetails[i].MeasurementRecord.RecordDate.Year.ToString().Substring(2, 2);
workSheet.Cells[row, 3].Value = day + "/XKT/" + month + "/" + year;
//VourcherDesc
workSheet.Cells[row, 5].Value = listRecordDetails[i].MeasurementRecord.Material.TypeID == (int)MaterialTypeEnum.Keo ? "Xuất cán Keo" : "Xuất cán Pat";
//RWareHouse
workSheet.Cells[row, 13].Value = "PL";
//InventoryID
workSheet.Cells[row, 15].Value = listRecordDetails[i].Material.Code;
//Unit
workSheet.Cells[row, 16].Value = listRecordDetails[i].WeightUnit;
//CQuantity
workSheet.Cells[row, 20].Value = listRecordDetails[i].Weight;
//LocationNO
workSheet.Cells[row, 25].Value = listRecordDetails[i].ParcelCode;
//ProductID
workSheet.Cells[row, 29].Value = listRecordDetails[i].MeasurementRecord.Material.Code;
}
var row2 = 13 + listRecordDetails.Count;
workSheet.Range(workSheet.Cells[10, 2], workSheet.Cells[row2, 65]).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
workSheet.Range(workSheet.Cells[13, 2], workSheet.Cells[row2, 65]).Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline;
//auto fit columns
workSheet.Columns.AutoFit();
}
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));
}
}
}